Working from the 2020 plan data, we need to complete a number of items to create new forecast scenarios which reflect the COVID impacts.

The 2020 plan was produced through detailed interaction with the field, building a bottom-up forecast. However, with limited time available to produce the new scenarios, the FD has decided to take a more higher level approach.

The grain that is being forecasted is at account and month level so, after the Plan data has been loaded into AFC, we have the plan available to XLCubed and we can build out an Excel model to create the new scenario.

Firstly, we create an XLCubed Grid showing the lowest members of the Account dimension on rows and the Months on columns for each of the companies, bringing in the member key as a member property (for loading back into AFC later).

 

XLcubed grid of account data

 

We want to specifically add some new data to identify furlough costs so we will need to add some new rows to the data to break down the employee costs. These are shown highlighted below with new member keys.

Data table

 

We can now create a simple high level model. To enable us to do it as rapidly as possible, we create a matrix to affect either All months or individual months.

 

 

We can then create a result matrix which uses an Excel formula to check if the individual month percentage impact is blank and, if it is, use the default to create the new plan.

 

 

In an AFC Load sheet we can easily map these values into the format AFC wants to consume by simple formulas. By saving the workbook into the LOAD directory, these will be automatically loaded into AFC and appear as a new scenario in the cube.

Subscribe to the XLCubed newsletter.
  • Latest features
  • Version releases
  • News and events
Privacy Policy