Use Monte Carlo Simulations to Quickly Model Costs and Influence Stakeholders
By Doug Hudgeon
The Cost Reduction Tip
Taking your business down to its lowest possible operating cost typically involves changing (usually simplifying) processes. When preparing a business case for changing your current processes, it is usually easy to nail down system costs but people costs are almost always problematic. Even if you believe you can calculate them accurately, your stakeholders will each have their own views on the assumptions underlying your cost model and this can result in an impasse.
I like to use a Monte Carlo simulation to model people costs. It enables you to quickly calculate a range of costs for activities that can encompass the conflicting views of your stakeholders.
Here’s how I do it:
I’ve prepared a sample Monte Carlo simulation in Google Spreadsheets that you can review online and download as an Excel file.* Because the values in this file may change as I run different scenarios, I have taken a screenshot of the current settings and labelled the components of the spreadsheet. Please click on the thumbnail image to follow the discussion below but refer to the Monte Carlo Simulation Google spreadsheet to explore how it works in detail.
Note that I have prepared a subsequent post that takes you through the spreadsheet in some detail and, for those who are fire walled from Google Docs, please click this link for an Excel version of the Cost Analysis Monte Carlo Spreadsheet
Monte Carlo Simulations The first column (1.) shows each of the activities that will be modelled. In this case, I have set out seven steps involved in manually processing a PO and paying the resulting invoice. The next three columns (labelled 2, 3, and 4 in the thumbnail image) allow you to enter assumptions against the time taken to perform each activity
(2.), the fully loaded cost of the resource performing the work
(3.), and the number of transactions per month
(4.).In the above screenshot, you can see that Activity “1. Create requisition” takes between 1 and 3 minutes to complete and the fully loaded cost of the resource creating the requisition is $90K to $110K (assuming some pretty significant overheads for this resource!). Each month, the organisation prepares 10K to 20K purchase orders.Running a Monte Carlo simulation over these variables (FTE utilisation: 130 hours per month) results in this activity costing somewhere between $12,382.44 and $35,681.69 with a 90% confidence level
(5.).The last 5 words of that sentence are pretty important. When you are setting your variables for activity time, resource cost and number of transactions per month, you want to set the minimum number so that 95% of the values will be above that number and the maximum so that 95% of the values will be below that number. For example, in activity 1 when I said that the activity takes between 1 and 3 minutes what I mean is that 90% of the transactions I observed take between 1 and 3 minutes i.e. 95% of the transactions took 1 minute or more and 95% of the transactions took 3 minutes or less. If I am confident that this is correct for each of the values in the yellow highlighted area of the spreadsheet then I can expect that 90% of the time, my conclusions will be correct.**
Now, back to our stakeholder question: When you are speaking with your stakeholders with your Monte Carlo simulation in hand, you can explicitly discuss each assumption and, where the stakeholder has better information than you, immediately incorporate their information into the model and see the impact on the business case. If your business case still stacks up after this process then you can proceed confidently knowing that your stakeholders understand the numbers and have had their input incorporated.
* A Monte Carlo Simulation is an approach whereby you nominate an upper and lower limit for each activity and generate random results (in this case normally distributed)
** You’ll note that the Totals (6.) do not equal the sum of the 5th percentile or the 95th percentile. This is because simply taking the sum of all of the 5th percentile activities does not give you the 5th percentile overall – it gives you lowest cost for activity 1 plus the lowest cost for activity 2 etc. The value displayed in the spreadsheet is the 5th percentile of all 7 activities combined into a single transaction.
Doug Hudgeon who is lawyer and vendor management professional who has branched into finance and accounting shared services management.