Transparency in the Year End Forecast - The Cascading Forecast

Let me start out by admitting guilt. I have been overly optimistic when forecasting. After actualizing the first few months of a new year, and seeing red to the original budget numbers, I have tweaked up room nights, average rates, restaurant covers, and average meal checks in the remaining months' forecast so that the year end forecast would still be in line with budget. The thinking was that we could just make up the shortfall in later months. The year end forecast was becoming biased with optimism.

Once a forecast becomes overly optimistic, the complacency shortens the opportunity to initiate cost containment measures and revenue driving strategies to make up for the shortfall.

Forecasting bias can cut both ways. Just as a forecast can be embedded with too much optimism, also known as "blue-skying", there can also be bias leading to under forecasting, commonly known as "sand-bagging". This second scenario can wreak havoc on how advertising dollars are spent and lead to incentives paid when not truly deserved.

Weeding Out the Bias Infiltrating the Forecast

Best Practice Solution

Lock down your re-forecast after each monthly closing. This will provide a basis for measuring accuracy over time.

Be transparent and rate your forecasting accuracy. This will support the reliability of your forecasting and gauge your forecasting practices and methodology.

The Cascading Forecast

Below is a spreadsheet model designed to lock each forecast and rate accuracy over time. I call it a Cascading Forecast, but it could just as well be called a Forecasting Accuracy Report. The variables here are for Revenue, but actually any metric, EBITDA, N.O.I., etc. could be used. It has turned out to be some of the most valuable tools in my spreadsheet toolbox.

In the example here, the budget for the year totals $992,400

Actualized monthly revenues are represented in the blue boxes and have variances to the budgeted month month numbers noted with green up and red down arrows.

Once the actual was entered for a given month, the remaining months were forecasted through the end of the year. The Year Fcst. column shows the locked down forecast.

Comments on this forecast

Although Q 1 and Q 2 had a drop in revenue of $34 K and $36 K respectively, the year end forecast still hovered at 98.4% achievement, showing a drop in revenue of only $15 K. The net of $55 K was moved into future months for future capture. This would have been the right thing to do if the revenue materialized. However, the $976 K estimate of the year end forecast at Q 2 was 5.2% off from the actual year end revenue of $928 K ,so it rated with an out of range Star.

In July, there was a major "true-up" of the year end forecast with an 8% drop from the prior month. Although the year end forecast was now 90.2% of budgeted revenue, the months of July through December had higher Star ratings as they aligned closer to the year actual achievement. Now that the forecast became a wake-up call, the months July through December captured at least slight amounts over budget, and without the significant drops.

The graph shows a delayed reaction of the forecast drop. This is lost opportunity to pursue mitigating strategies.


Analyzing your forecasting accuracy is key to improving your forecasting accuracy.

Accurate forecasting has endless benefits including:

1) Better cash flow management

2) Better target marketing

3) Improved inventory and cost management

4 ) Improved staff management

5) Higher employee and guest satisfaction.

And of course, a better bottom line. Being reactive always costs more than being proactive.

1 view