Hi!

Life is pretty good here in Boulder. I’m grateful for all the folks I work with, both long-term repeat clients (I’m currently working on three clients where the relationships go back at least ten years) as well as new ones who keep finding me--often from your referrals. Thank you for that.

This newsletter continues exploring some innovations from a recent model. While I find forecasting relatively simple, I have long struggled with the interplay between the forecast I expected and how actual results come in. There’s a lot that can go wrong there! So this newsletter focuses on one particular problem and an elegant solution.

Best regards,

David

Having trouble with timing of different events in a model?

I recently created a new Master Calendar methodology for a client which has greatly simplified how they did their financial forecasting.

Curious? Contact me for a demo.

Constraining Actuals+Forecast Results With Guardrails

Forecasting models are great, and on the whole easy to manage when you can make up every number. But sometimes the planning model lives on and becomes part of regular company management and reporting. In that case, the model must forecast the future and track actuals. Things get harder when you introduce actuals into the mix. This newsletter describes one of the problems that occurs and a solution.

Let’s start by defining the problem.

1. First, we Forecast the total cost for a line-item over the life of the project. We did lots of work to a) get a Total amount, which I’ve set here to $100. We then told the model when to start spending that money (based on a “Start Date” defined elsewhere plus or minus a number of months) and the number of months of which the expense should be spread. That data is shown here, where the forecast calls for spending $50 per month over the next two months.
   
   
2. Next, the general ledger accounting system created an electronic file which is read into the model. For this example we only care about the data for this one line item. To keep things simple, imagine actuals exist for only one period as shown below.
   
   
3. The model has a timing input which lets the model know how any months of actuals have been loaded into the model:
   
   
4. In our first iteration of the model we had simple logic which said “if it’s an actuals period, grab the actuals data, otherwise grab the forecast.”
   
   
5. The problem was that, in this example, expenses began sooner than the forecast. The forecast expected expenses to occur in April and May, but fully 70% of the expenses happened in March. Given the standard logic in the model, the model was giving results like this, showing $120 in cumulative expenses in April and $170 by May. Since we had a tightly tuned budget of $100, this variance was throwing the model off badly.
   

Solution

We named our solution “Guardrails.” Guardrails have two inputs for any given line item:

1. Enabling or disabling the Guardrails method, and
2. Setting a tolerance level. Recall we set a budget that we really believe is accurate. The idea of Guardrails is that spending stays within the specified parameters and cannot exceed the budget past the input tolerance parameter. A typical setting for our tolerance level was 0%, which is to say that spending in the model cannot exceed the budget of $100. If the tolerance level was 10%, spending could go up to $110.