Funny budget numbers

I’m back on that project where we are asset manager of a bunch of failing properties that were under bankruptcy. I think we were brought in as part of the deal between one of the investors and the owner of the properties. The first go round, which was back in November, was to review some budgets to make sure they made sense and were reasonable. We had to review 30+ properties in what?, two weeks? Those budgets were high level budgets and we thought they were painful to go through.

But those budgets were for 6 months only and now, in preparation for more funding, we need to review more detailed budgets. We just got them and they are due next Friday.

I have to say, it’s actually hard reviewing budgets. I am more used to helping managers develop them rather than reviewing a bunch of unfamiliar budgets.

But one thing I must say, somebody on their end, whether the CFO or her assistant, needs to review them before sending them to us. These budgets are funny! You know something is wrong when the budget projects occupancy rates from 40% in January to 95% in December. What a turnaround! Or how about, even funnier, a 26% occupancy rate in December and 55% in January, a month later. One of our supervisors who has walked the site has said the site is probably more than half boarded up.

How am I digging into these budgets? Well, I couldn’t do it without Excel and its pivot tables. I retrieved my file with actuals that I’ve been using since the last budget review and set about merging in the budgets. The budgets were not set in a format conducive for pivot tables so I used my macro to convert the data into “flatfile” format and then added that budget data to the table of actuals.

Another problem I encountered was mapping the GLs between the actual data and the budget data: I couldn’t line up the budget data with the actuals properly. So actual labor costs did not line up with the budget labor costs; it would fall on a separate line. It turned out that the GLs were a mixture of value and text. What I did was convert all GLs into text, both the GLs in the actuals data (yes, it was mixed too) and GLs in the budget data. Once I did that, I was able to line up the actuals and budget in a time series to see if anything stuck out in the run rates. I also developed some charts for the major expense categories and occupancy rates to see if anything popped up.

And boy did they ever!

Having Excel do the charting and the manual work for you helps speed up things. I don’t know why more finance folks don’t try to automate some of this stuff. I’m lucky my boss is comfortable with using automated tools such as macros, but she’s a unique bird.

We’ll see how the week goes. It should be interesting.