This question from Danielle Stein Fairhurst reminded me of a solution from way back I implemented at WSP, from an idea by Tom Bower, Commercial Director.
First, the forecast data for the group is in a database, located external to the spreadsheets that the people work with, and centrally accessible. (I have demonstrated the benefits of this architecture elsewhere) At WSP it was a SQL Server, but it can be Access, or a SQL Server on Azure. The methodology is identical.
On the monthly reporting dashboard of the group, the forecast looks like this.
[IMAGE: Forecast section from monthly report]
The detailed forecast is 6 months. While the constantly-updated forecast is kept on spreadsheets, the data is saved in tblForecast by the user clicking a PUT button whenever their forecast is revised.
[IMAGE: tblForecast]
The headings on the monthly report change as appropriate for the particular reporting month. For example, in January it will be the six months Jan, Feb .. etc and then Aug-Dec as the RestOfTheYear column, making up the year to the year-end of December.
This continues, so in February we have the six months Feb to Aug, and then Sep-Dec as the RestOfTheYear column. So, the RestOfTheYear column will be zero in the August reporting.
The column headings are created dynamically from tblForecastHeadings, and is driven by the reporting month.
[IMAGE : tblForecastHeadings]
This is what happens when the report is refreshed by clicking the GET button.
{IMAGE: diagram of how they fit in (the ‘PLAY’)]
The RestOfTheYear column is presented to the user prorata’ed from the previous month. The prorata percentage is part of the tblForecastHeadings, as you see above. They can revise the RestOfTheYear by overwriting this. When the PUT button is clicked the backend table tblForecast is updated.
Of course, this methodology can be adapted to the 3 years + 2 years scenario posed in Danielle’s question. The key is to store the data in an external table, and make the structure of the forecast data-driven.
Indeed, the problem posed in the question (though common) only exists because we insist on storing the primary forecast data on the spreadsheet. How can you consolidate hundreds of operating units when you do it that way? (without creating an almighty mess?)
Consolidation
The group structure at WSP at the time was 400 operating units. These are in a hierarchy of 4 levels that roll up to the group. (of course, being data-driven, the model’s structure remains the same regardless of how the group structure changes over the years)
The monthly report reports on any of these sub-group levels, selectable by a cascading dropdown. The dropdown is populuted dynamically, driven by the access rights (permissions) of the user. That is, only the permitted parts of the group hierarchy are selectable in the dropdown.
This is only a quick overview. I realise, when described in text (as here) the solution looks more complex than it is 🙁 Actually, it’s both simple and robust (ie. no moving parts).
It’s explained in greater detail in the EXCEL CONTACTLESS PLAYBOOK.
Add comment