more-arw search

Q&A Forum

Merging Actual vs Forecast Data In Excel

 

"One of the biggest challenges in updating an Excel-based budget is the "dovetail" between actual and forecast data over time. Suggestions?"

This question was asked at a recent webinar, now available on-demand:

"Budgeting in Excel"

Please add your thoughts about it below. Thanks!

Answers

Simon Turner
Title: Vice President, Finance
Company: International Finance and Operations Exe..
LinkedIn Profile
(Vice President, Finance, International Finance and Operations Executive) |

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.

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

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.

Michael Stimson
Title: Director
Company: GIDE International Limited
(Director , GIDE International Limited) |

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.

Rudy Fischer
Title: Partner
Company: RKFischer & Associates
(Partner, RKFischer & Associates) |

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.

Bob Low
Title: Principal
Company: Perron & Low
(Principal, Perron & Low) |

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.

Sajan Sadhwani, CPA CMA
Title: C2C Specialist
Company: Ernst & Young
(C2C Specialist, Ernst & Young) |

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.

Topic Expert
Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

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.

9365 views
Topics
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.