Highlights on recent analytical project

About two weeks ago I was working on a project where we had to analyze the budgets for 30 properties to make sure they were realistic and contained nothing unusual. This was very sudden and we had about a week to go through all of the files and make sense of the historical data as well as the budget data. On top of that, my boss and I already had other projects to work on.

In a project like this, when reviewing other people’s budget, especially when you are not familiar with their business, it appeared to me to first look at the history to see where they have been. The customer gave us access to their historical files through the Egnyte cloud system so I went ahead and dumped all of their files onto our network drive, in case we later lost access to the files – which we did.

1. So point one would be to save files onto your drive or network or dropbox in case you later lose access (but you still need the files to do your work).

I did an inventory of what kind of information were provided and I found that there were quite a few PDF files. Once I had an idea of what kind of information were provided and since I knew my boss would not have time to look at these files, I proceeded to develop a “database” so that we could do pivot tables and slice and dice the data. A database and pivot table rather than maintaining the separate files and linking to them would enable a lot faster work . Keeping the static form that the customer had provided did not provide flexibility to rearrange the layout for better analysis of information. Pivot tables allow you to look at information in different ways.

2. Gather the information into a database for easier querying, whether by pivot tables or some other database tool.

Some of the data provided in Excel were laid out with GL accounts running down the left hand column and the months running horizontally across the top. This layout is not conducive for use in pivot tables, so I developed a macro to rearrange the information – an example is shown below. Other data were found in PDF which necessitated a different attack. I wrote a post about it, titled “Hard Problems”.

Finally, I endeavored to look at the budget to see what they had pulled together. The first thing I noticed was that the budget was done at a very high level, very similar to the high level groupings found in the historical files, so I used the pivot table to show just the high level groupings and to enable the capability to drill down to the detailed GL level via the +/- button. To get the high level categories, you just assign a category next to the GL, either manually or via VLOOKUP. Once I had the pivot table set up, I just copied over the budget and placed it side by side next to the actuals. I could have arranged the budget data into a database, just like the historical actuals , but I was running out of time. I needed to begin analysis. Besides, the numbers were likely to change.

3.Create your pivot table for easy comparison against the budget or whatever you need to work on.

For analysis, I decided to use average run rate on both the actuals and the budget. I had 9 months of data for the actuals so I created the 9 month average and the last quarter average for each high level category in the budget. For the budget I created the budget average. From there, it was easy to spot unusual increases or decreases and use these as a point of discussion to make sure nothing as amiss or nothing was missed in the budget. The drill down feature of pivot via the +/- button enabled us to discern the possible trends in certain metrics.

4. Apply your metrics, rather averages, variances, or whatever is appropriate.

The result of this exercise was that my boss was able to go through each property and quickly pinpoint items to question the client. After first confirming the goal of the client, we went through 30 properties in a couple of hours with the client, providing inputs and suggestions on the budget.

5. I should have put this as a second point: Find out what the main goal of the exercise as soon as you can.

Although we had a fairly good idea of what the goal was, we wanted to confirm before delving into the details and providing suggestions.

All in all, it felt like a successful project because we were able to provide good feedback in the relatively short time we were given to absorb all of the information. We’ll see how it goes going forward.

[divider] Sample macro used to create flat file for pivot table[/divider]

Yes, this program uses Select which I’ve read is in poor form but for right now I’m still learning macro programming and I find Select useful for telling me where the cursor is residing.

Sub CreateFlatFile()