It should be easy to display actuals from the beginning of the year to the current reporting month – then add the remaining months from the budget or previous forecast - to create a new forecast, shouldn’t it?
Well, apparently not! I don’t know if you have tried this in Excel before. I have, and it's not easy. Mostly people do a manual copy and paste, and then add macros, lots of Excel functions and lookups. It’s a little messy.
Then you need to change all your reports. From some of the stories we've been told, it's not uncommon to take two weeks to make these changes only to find out they need to change the data structure!
No - I don’t mean a colossal Excel workbook. You can use free stuff like PowerPivot. It can be a little fiddly, but it works for smaller scale reporting.
I prefer to use cube based technology. Cubes are perfect for slicing and dicing your data in moments, similar to cubes little brother, in memory tables, but also excellent for capturing data instantly. What I mean is you enter data directly into your cube through your report. Yep, your reports can be input screens too. That makes life easy doesn’t it? Yes, security is in place won’t let you overwrite actuals or locked in forecasts and budgets.
This is a huge time-saver and headache preventer. With formula driven tools, you can lay your report out any way you like. There are no limits!
Templated reports means no hard coded or keyed data reports. The number of times I see this is incredible. I was reconciling a report yesterday and couldn’t work out why there was a difference between my CALUMO report to the Excel one. In the Excel report, someone had entered a formula in the cell that was something like =34324+664355. Now just where did those numbers come from? Using formula based Excel reports that link to a central database means you have one version of the truth and all reports display the same data. Please people! When your report becomes a template, you end up with one report template for each report type. For example, you would end up with 1 Summary Income Statement by month, and assuming you can drill down, this is automatically a detailed income statement by month as well.
Do what now? In your database or cube, you need a field or dimension with Actual data, Forecast data (and probably others like budget, target, etc.) and Actual/Forecast. 20 years ago we called this a hybrid scenario. The Actual/Forecast scenario is a combination of Actuals up to the current reporting month and then forecast from there. And it moves or slides as the months roll on. For example, if the current reporting month is July, the Actual/Forecast scenario displays Actual data up till July. From August, the Actual/Forecast scenario displays Forecast data. Depending on your database, you might use a calculation to display the data from Actual and Forecast. When you build you report, ensure you select the Actual/Forecast scenario, perhaps with months across the page and you won’t have to update a report every again!
If you want more information about this, email me – firstname.lastname@example.org