Combine Actuals, Forecasts and Budgets into an automated report pack

A very common issue in business is how to combine Actuals, Forecasts and Budgets into an automated report pack (easily). Some clients were taking 2 weeks to get everything together even though the information was available 5 days after month end. The reality is that combining data generated from systems should take hours, not days!

In some recent assignments we have had to assist in combining:

Actual results to date where
- operations are sent by monthly csv from an external service provider in database format (see below what we mean)

Head office accounts come from an internal system like Pastel or Syspro, but in report format (see below what we mean by report format)

Updated forecasts received on a monthly basis in csv form but crucially in report format and not in a database format
Budget created in an Excel spreadsheet at the beginning the year and used as a constant reference (but the updating forecasts were the main reporting data)

Actuals, Forecasts and Budgets Solution

Ideally all this information sits within a single accounting system and MS Excel is not needed. However, this is less common than it should be and especially the Budget and Forecasts are generally in MS Excel format.

Our solution allowed for the following (using just MS Excel tools that are already available in the software and with NO VBA):

Importing of the source data from csv’s and Excel files on a monthly basis
Converting the reporting format data into a database format for easy use and comparisons (month to month, quarter to quarter etc)
Selection of which month is the current month to inform the reports up to where the actuals are pulled through, after which the revised forecast fill in the rest of the year. As a result you have an increasingly accurate view of what year end will look like.
Creation of a single database which contains all the information combined to allow for customised reports via pivot tables.

Automated management reports built in MS Excel without needing Pivot Tables (Pivot tables are hard to format in exactly the way management want the report to look).

Creation of a detailed variance report on a month and year to date basis comparing actuals versus budgets vs forecasts as well as on an investment by investment basis.