Overhead Allocation in Excel
Costing systems tend to be seen as having some sort of black box which you submit your data to and it gives you back patient level costings.
One part of a costing system that is given this treatment is overhead allocation and with the attached spreadsheet and below webinar I’ve tried to unveil the hidden mathematics behind this particular part of costing.
Off the back of the Excel 365 Array Formulas section of the webinar where I introduce several new formulas to Excel 365, in this part I delve deeper into how array operations work in Excel and I use the new array formulas to do a task that traditionally would seem like it can only be done in a costing system.
Repeated Distribution
This sheet lays down the foundation of the question that this spreadsheet is trying to resolve. How can we accurately allocate overheads given several overhead/service/support cost centres that you want to allocate out to operating/production/specialty cost centres when you know that each overhead/service/support cost centre is related to each other along with the operating/production/specialty cost centres?
The method used on this sheet is the repeated distribution method which is a long winded way of finding the total overhead allocation but it lacks any sort of detail as to how much of each overhead/service/support cost centre costs are split between the resulting operating/production/specialty cost centres.
Draft Workings
This sheet shows the alternative, more accurate and efficient way of allocating overheads and is the way that costing systems would work to output how it’s allocated overheads to operating cost centres.
Matrix Algebra
This sheet is quite important to understand if you want to advance your knowledge and utilisation of array formulas in Excel beyond their straightforward duties.
It shows what a matrix is and how they can be added, subtracted, multiplied and inverted.
Departments & Cost Allocation
These next two sheets look to bring together the mathematics of the Draft Workings sheet into one all-encompassing formula with a little bit of work on making it dynamic too.
Depts & Splits & Costs
These sheets take the idea of producing a formula that does all your overhead cost allocation to the real world.
The Depts sheet lays the foundation of all the actual Service and Production cost centres that exist in a particular model used at Alder Hey, matching them to real methods of allocation such as by floor space or headcount but all with randomised values. Without the randomised values I can tell you that the output on the Costs sheet matched perfectly to the output of our costing system.
The splits sheet works out the % of the overall method of allocation for that particular cost centre to give a split out of 100% just like what we had on the Repeated Distribution sheet with Finance being split 10% in Finance, 25% in HR, 40% in Estates, 15% in Cardio and 10% in Podiatry (obviously all made up splits for the purposes of this demo).
The Costs sheet then brings all of what we’ve learnt together.
Attention: This is a very niche topic which is why I decided to split it apart from the more general Excel 365 Array Formulas part of the webinar but as you’ll see in the webinar linked below, the two parts are in the same video so you’ll have to skip ahead to 00:56:30.
https://youtu.be/lp-jRmwfIvw?t=3390