Improve your Forecast Reporting - Integrate your Forecast Details!

Nearly all businesses spend considerable time and effort in preparing an annual budget or forecast.  Generally, time spent in looking forward at what is expected to happen is a good thing, for a variety of reasons.

The problem with Budgets or Forecasts is that at some point you compare it with actuals.

Excellent, all good...or is it?

So, from the above picture, should a user be happy that things are going to plan? Revenue down, but operating costs down, slightly higher maintenance bit overall profit up.  Looks ok, right?

Well, it could be ok, or it could be not.  The main issue is that this picture alone doesn't tell you context - are those variances simply timing differences (ie, actions that were done at a different time to the budget) or permanent (ie actions that weren't forecast/budgeted at all).

Being a high level view, another issue is that large offsetting variances in a line item net off.  So, in operating costs, significant overruns in salaries might be offset by lower materials costs (due to poor accruals.

What is a business to do?


Current Forecast Reporting practices:

1. Commentary : Generally, most management/board reports require insightful, accurate commentary to provide readers with the context - hence helping the users to understand what the numbers actually mean.
   
   The problem is, of course, that commentary is generally not insightful and doesn't help the user understand the key issues.
   
   The other problem is that insightful commentary takes time to produce, particularly when trying to work out what happened compared to a forecast/budget.
2. Add Detail:  Reports get bigger as managers seek to eliminate one source of confusion (that of offsetting variances).  Of course, for each detailed report, insightful commentary is required.

It's obvious then that commentary is really important for a proper understanding of a report.  The difficulty that then arises is that commentary can take a really long time to prepare if the forecast and the actuals are difficult to match up.  Also, much commentary isn't particularly insightful.

What if there were forecast details that could be compared to actuals?

Now clearly, no business is going to even try and forecast individual transactions - that isn't what I'm getting at.  No, what I'm saying is that individual forecast lines that are ALREADY included in the business unit forecast model are made available in comparison reports.

Let's consider an example - Travel expenses.

In order to forecast travel expenses, many business units will have a spreadsheet that estimates the number of trips (sometimes by person), then calculates airfares, accommodation, meals etc.

In budgets/forecasts travel is often justified on a trip by trip basis, with explanations for each type of trip (eg sales, training etc).

This detail is usually lost when the forecast/budget in loaded into the financial system - often the financial systems only allow one number to be entered against a particular GL code/cost centre.  It is this loss of detail that can make it hard to compare actuals to budget/forecasts, particularly if:

• The person who developed the forecast left;
• There were multiple versions;
• The budget/forecast was arbitrarily reduced;
• etc.

Now, travel is not, normally, the biggest business expense.  The same principle, though, can be applied to operational areas - keep as much budget/forecast detail as possible and embed it in the reporting system.  In businesses with maintenance operations, keep detail of the maintenance activities forecasted.

Generally, detailed summarised out

Advantages of keeping the detail!

There are numerous advantages to keeping the detail from the underlying forecast models.  These include:

1)  The forecast/budget uses the business area detail forecasts, not a summary version specifically created for corporate needs.  This means that the business areas are more familiar with the forecast, plus generally detailed forecasts (particularly production plans, maintenance schedules etc) are generally updated more regularly.  Importantly, this also means that the critical non financial information is also available!

2)  The detailed lines show up as "transactions" in the reporting system.  So, you can see not only the actuals, but the forecast information and amounts for the month.  Obviously, the level of detail will vary, but any detail is better than a single number.

3)  By keeping detailed forecast information in the reporting system (particularly non financial information), you can then more meaningfully compare budget/forecast versions.  

4)  It is far easier to do commentary on a monthly basis, as the particular forecast "transactions" for the month are visible (as noted above) but also the forecast transactions for the rest of the period are also able to be reported easily.  This makes it a lot easier to identify timing differences.

5)  Using the business area detailed operational forecasts makes it far easier for the business to forecast.  Each individual business area tends to also be more invested in their operational forecasts (as they develop them!).  

6)  There is hugely increased transparency (no interim "corporate" versions and all business area forecasts visible) which is hugely helpful in getting a coherent business focussed forecast together.  For example, all areas can see what other areas are forecasting at a detailed level.  This makes it more likely that the budget/forecast will make sense overall (so, no high production months when the plant is shut down for maintenance etc).

Everyone can see and pull reports at a detailed level

As the list of benefits is so long, the obvious question is "Why doesn't everyone do this?".  

There is one small pre-requisite....

You need some form of business intelligence reporting system to make this work.  It doesn't have to be a complex one (I've done these with Microsoft Access), but you do need one.

Remember that a business intelligence system is simply a set of databases you can report from.  There are plenty of free (or cheap) reporting apps and keeping it simple makes it easy to get data into your databases.   I've created suitable BI systems using Access, Microsoft SQL Server (which just about everyone has!).  At a pinch, Excel is possible, but not recommended.

Practical Tips for capturing forecast details

1. Use the lowest level business unit forecast they routinely update.
2. Don't worry about summarising.  The BI system will do that.  Just assign GL/Cost centre codes against the detail lines in the forecast.
3. Don't add detail to the business unit forecasts.  Chances are the business unit already uses the most logical level of detail (balance between detail and time to update).
4. Do create forecast version identifiers - this enables you to compare different versions.
5. Bring in non-financial information and at least one description field.