Our first activity was to understand the detail of the furlough schemes in operation in the UK, Germany and France. Indeed, it now appears post June that the UK government will incorporate some of the concepts already in place in Germany/France i.e allow part time furlough.
Our observations with respect to either our own direct operations in the UK and France, that of our extensive global partners (including large German partners) and those within our network, was that most/if not all companies have looked at this in detail and are using a variety of levers to assess cost reduction vs capacity impacts in relation to reduced incomes.
The base input data was a list of employees, their salary, department and seniority level.
The major considerations for each scenario were
- For each country, identifying which departments and seniority levels could not be furloughed for operational reasons, and if specific individuals could not be furloughed.
- Determining if redundancies were in play in a scenario – for the purposes of the model built it was decided there would be no redundancies in 2020.
- The percentage of staff that would be furloughed based on salary cross sections and when it would start and finish – each government has set different limits as to the max amount they will support and the percentage of support.
- Whether the company would top up the salary and to what level.
- Whether a temporary percentage salary decrease would be implemented and when it would start or finish.
The scenario input worksheet was setup as below:
This allows the end user to modify the non shaded cells to vary the many different inputs into the model that can then be saved for analysis.
As each input is changed Excel calculates the resulting cost savings.
A calculation worksheet was implemented to carry out the individual calculations. The challenge set was to be able to use standard Excel formulas to achieve the results of the scenario being modelled without reverting to VBA. Many vlookups to the assumptions and the scenarios were used, plus some complexity to ensure the right number of staff are being furloughed – determined by ensuring the sort order of the data was in country/salary order - and our goal was achieved.
This entailed a validation activity to ensure that the formulas were creating the right result as it's all too easy with Excel for an errant formula to throw things out. We can’t stress enough how many times we have seen errant Excel formulas create mistakes – just because it produces a number it doesn’t mean its right – so an external checking process is something we always recommend.
We wanted to be able to compare scenarios and also to see the monthly effect of the cost savings and we did have to revert to VBA for that, creating a scenario sheet saving the scenario data and calculating the monthly impacts. This was also useful for generating the final scenarios for transfer to AFC where at an employee level the decision of who was to be furloughed was made.
Having constructed the Financial Model we wanted to enhance it using XLCubed so that comparisons and more detail analysis could easily be carried out. Our view is that having direct high quality visualisations available to the end user whilst building scenarios and being able to compare saved scenarios side by side adds value.
To do this we created two XLCubed Pivotviews, analogous to PivotTables (and indeed you can create them from a PivotTable), as all you do is point them to an Excel Range to set them up.
What is being created under the covers is a mini-cube which understands OLAP queries, meaning that all XLCubed capability is available to the user.
We wanted to build a visualisation that updated dynamically as the user changed the scenario so we choose this dynamic chart to show the breakdown:
These dynamic charts are all completely drillable and the underlying data can also we viewed easily.
A normal XLCubed grid showing employees ranked by the amount of salary being lost can easily be constructed which shows how the calculation capabilities in XLCubed can be extended without having to go back to the Excel range.We decided to link this grid back to the chart – such that when the user click on a country and department the grid is filtered – allowing the end user to rapidly analyse the data.
We also wanted to give the user the ability to view saved scenarios and compare them, so constructed another dynamic chart comparing the cost savings vs the capacity loss. As a new scenario is saved the new scenario is added to the chart:
All XLCubed capabilities to rank or filter the scenarios are available to the end user. These can be used directly within the XLCubed UI or can be exposed as simple buttons. Additionally, the user can rapidly change the view of the chart and indeed the chart type within the dynamic chart task pane.
Adding an equivalent chart to the working scenario chart such that the user can contrast his working model to one of the scenario’s is also simple.
We also built a link to monthly comparison of scenarios using the XL3Link formula to jump to another sheet in context of the scenarios to show an IBCS variance chart.
And finally, as we wanted to be able to share this with other executives, we published this to XLCubed Web Edition such that people could use it on tablets and mobiles using an XLCubed dashboard sheet.