"One of the biggest challenges in updating an
This question was asked at a recent webinar, now available on-demand:
Please add your thoughts about it below. Thanks!
Merging Actual vs Forecast Data In Excel
Answers
Depending on the size of the data you are dealing with, build three tables or worksheets. One will contain your actual data, the second your forecast data and the third will present the data to the user. You can use a drop down menu (Data, Data Validation, Allow List and reference two cells with Actual and Forecast in them) for each column so that you can select either Actual or forecast. In the cells you can then use a formula that will go to the table or worksheet, depending on the value selected.
Assuming you've budgeted at the GL account level, set up a budget table and an actual table, each with Month, Account, and Amount. Then, next to a column of GL account numbers, use the SUMIFS function to return a column of actual amounts by account for a specific date. And then do the same for a column of budget amounts.
Any spreadsheet solution for this is risky, slow and prone to errors.
Use a modern, in-memory calculation tool that;
- enables fast integration to financial and operational systems,
- provides a collaborative approach so that users and stakeholders can adjust any future data
- provides rolling forecasting, and for more than only 12 months
- runs quickly and safely and can be interrogated and analysed.
- And all for a reasonable price, with rapid implementation.
You could create three sheets 'act', 'bud' & 'fcst'. The 3 sheets must be identical in structure. I usually create one, then copy sheet twice. In the 'fcst' sheet you use "If" statements to determine which of the other two sheet you will pull data from. If data exists in the 'act' sheet, them pull from it, if not, then pull from 'bud' sheet. That way you always get a full sheet of act/bud data.
The scenario I'm used to is having a forecast model for the year (or further) and then need to update monthly actuals as they become available. Export your TB to Excel and add a column showing what line in your forecast model you want each account to roll up into. Then use a sumif (or sumifs) in the forecast to pick up the actuals. Repeat each month adding the new month's actuals to the same sheet so you can reuse the logic.
Been there done that. This is not a fun task. You should speak to the stakeholders/users to see what their needs are. In addition to the 3, budget, actual and forecast, you'll also need variances to each. If there are too many columns/numbers the users won't look at the reports and/or become confused. Its worse than not having reports.
At the start of the FY, forecast isn't very important, actual/budget are the key. By FYE, its usually flipped and the focus is on next year's budgets.
As has been pointed out you could have different data sheets and then pull information. I've done this and its worked. The challenge is that some people only want to see summaries, some want GL level detail. This is why you need to speak to your stakeholders to find out what they want first so that you don't over/under design something.
I do something similar to Simon's suggestion and Charley's suggestion.
My BvA report contains two selectors, one with a month and one with a YTD toggle.
The client selects the desired month as well as whether the BvA report should be only for that month or since January.
The Budget and Actual figures on that page update instantly an the Variance column just subtracts the two.
The two underlying reports are monthly.
The formulas on the BvA page depend on what month it is and what the YTD toggle says.
As a bonus, the reports header is also linked to those two selectors, so it will automatically say "July 2015" or "For the 7 Months Ended July 2015" accordingly.
Filed Under:
FP&A