The Grind of Analysis

Today’s show is a personal story of an inner struggle.

I’m in the middle of a project and I’m in the process of updating the financial projections based on a revised construction budget, new information about the debt structure, and a more accurate view of the schedule.

In the process of updating the spreadsheet I discovered that there were a number of places where numbers had been hard coded into cells, rather than being calculated from variables. 

I’ll give you a couple of examples. When you model the effects of inflation on both income and expenses, you could take the numbers from year 1 and increase them by 3% in year 2, or you could increase them by a variable inflation rate. So that if you were to change the inflation rate assumption, you would change a single number and all the inflation adjusted numbers would be updated automatically. 

You would want to do the same thing with interest rates for loans, any fees that are calculated as a percentage of another expense and so on. 

The other major change was to telescope in on the first three years of the project and model them on a monthly basis rather than an annual basis. There is too much change happening during the first few years of the project for a yearly projection to be accurate. 

Needless to say, a lot of the spreadsheet was affected by these changes. The additional layers of complexity come from ensuring that the loan to cost ratio, the loan to value ratio, the debt coverage ratio, and the numerous other constraints of the loan agreement are properly modelled. 

These changes took an entire day to implement. I sent the spreadsheet out for review and within a few short minutes, my partner had found a schedule mistake in the spreadsheet.

I set about fixing the mistake which meant moving about 20 numbers by 5 months. That’s nearly 100 items that moved in the spreadsheet. At the end of that process, the financial rate of return forecast to our investors looked surprisingly poor. 

How could a shift of a few months destroy the financial viability of the project? It didn’t make any sense. My partner and I had numerous phone calls throughout the day to discuss various solutions to the problem. At one point, we were even talking about whether we should sell the project.

I spent the next 7 hours pouring over all 10 pages in the spreadsheet, checking formulas, refining estimates, experimenting with different possible solutions.

After dinner, I went back to work. After a few more hours I noticed that on one of the pages, inflows of cash used positive numbers, and outflows used negative numbers. But I wasn’t consistent in that convention. On another page they were all positive numbers, and the expenses were subtracted from the income make it all work. 

But in the process of converting the spreadsheet to use more formulas, some of the negative numbers started to appear on pages that had previously only had positive numbers. It was at that point when I saw the error in the rate of return calculation. Negative numbers represent an investment of cash and positive numbers represent cash flow back to the investors. Well, my spreadsheet was showing 5 years of negative cash flow to investors. The minus sign was a complete mistake. No wonder the rate of return looked so terrible. 

As soon as the minus signs flipped and became pluses, a sense of calm washed over my home, the tightness in my back relaxed, and I could take a full deep breath again. The rate of return to the investors was in the expected range.

I sat back and reflected on the past 13 hours spent diligently working on the spreadsheet. I asked myself what would have happened if I had given up after 8 hours of trying to solve the problem.