The Cost Reduction Tip After posting yesterday’s tip, I’ve received a few requests to explain how the Cost Analysis Monte Carlo spreadsheet works. I hope you find this post useful. Note: For those who are firewalled from Google Docs, please click this link for an Excel version of the Cost Analysis Monte Carlo Spreadsheet Background
The Cost Analysis Monte Carlo spreadsheet
It calculates the range of monthly resource costs for a seven-step activity – in this case the monthly cost of processing purchase orders – with 90% probability. This means that 9 out of 10 times, your monthly PO costs will fall within the range. Image 1 In the above screenshot, the total monthly costs for processing POs will be between $172K and $255K in 9 out of 10 months. Setting the inputs The spreadsheet calculates this by taking the inputs below for the seven activities: Image 2 Three types of inputs are required for each activity:
Activity time in minutes
Annual resource cost
Number of units per month
1. Activity time in minutes In activity 1 in the above example, creating a requisition, users take between 1 and 3 minutes to create a requisition in 9 out of 10 requisitions. The outlier will take less than 1 minute or greater than 3 minutes to create. 2. Annual resource cost In activity 1, the fully loaded annual resource cost for staff creating requisitions is between $90K and $110K. More accurately, the salary of 9 out 10 staff who create requisitions will fall between $90K and $110K. 3. Number of units per month In activity 1, the number of POs created each month ranges from 10K to 20K in 9 out of 10 months. Running the Monte Carlo Simulation on the inputs The Monte Carlo simulation spreadsheet takes these inputs and generates hundreds or thousands of random values for each activity.
The key to getting this right is that the random values are normally distributed between the low and high range i.e. the random numbers create a bell curve. The more values generated in the spreadsheet, the closer the fit of the data to the bell curve.*
To generate random values, I inserted a worksheet corresponding to each activity. I unimaginatively named the worksheets Activity1 through to Activity7. Each worksheet links to the inputs for that Activity: Image 3 You can see that column A has turned 1-3 minutes into 60-180 seconds. Otherwise, the values are identical to those in Image 2 shown above. Now, here’s the tricky bit: you need to take each of those values and generate a normally distributed random value between 60 and 180 seconds.
I use the following formula to do this: =min(A$2:A$3)+(max(A$2:A$3)-min(A$2:A$3))*NORMSDIST(SQRT(-2*LN(RAND()))*SIN(2*PI()*RAND()))
I realise this looks daunting but it’s not that bad. The first section, highlighted in red, simply takes the minimum of the input values, in this case, “60″. The second section, highlighted in green, subtracts the minimum input value from the maximum input value, in this case, totalling “120″. The blue is a piece of magic I have taken from the excellent Excel User website. The piece of magic is known as the Box-Muller Transformation and you can easily test it by pasting this code into thousands of rows of an excel spreadsheet and sorting and charting the result to show a clear normal distribution of values. Adding the above formula into cell A4 and spreading it down 500 rows and then dragging the formula right into columns B and C gives you a worksheet like the one below. Here you have 500 rows of data with a normally distributed random value in each cell.
Each of these rows takes the inputs and gives you a normally distributed random sample for Activity 1. This sample can easily be turned into a cost figure using the following formula: =A4/(60*60*HrsPerMth*12)*B4*C4 This formula takes seconds in column A and calculates it as a percentage of an entire year’s output from one of your staff who is performing this work. In the example below, it calculates 172.74 seconds as a percentage of the annual time worked by a staff member with a fully loaded cost of $108,864.84. This dollar figure is then multiplied by the monthly volume of 11,662.99 to get a monthly cost of $39,053.25.
Image 4 Only one step now remains. Calculating the Percentile Distributions Each worksheet contains a calculation of the 5th and 95th percentile values in the Monthly cost column (Column D). The formula for calculating the Percentile is provide in both Excel and Google Spreadsheets: =PERCENTILE($D$4:$D$103,.05) =PERCENTILE($D$4:$D$103,.95) Image 5 These values then appear in the 5th and 95th percentile shown for Activity 1 on the front worksheet in the workbook. Image 6 You’ll note that the Totals in bold in Image 5 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. This calculation is performed on the AmalgamatedResults worksheet which contains the totals of all activities, combines each row of activities into a single total cost for the transaction. Image 7 You use the same Percentile formulas to calculate the percentile figure for each transaction. In the example above, the 5th percentile is $172,921.53 and the 95th percentile is $255,358.43.
Tips on building your own If you download the Cost Analysis Monte Carlo spreadsheet, the areas you’ll likely need to modify are changing the number of activities and increasing the number of rows in the Activity1 through Activity7 sheets.
If you do so and improve on my version, I’d greatly appreciate receiving a copy. My email address can be found in the icons on the top left of this site: * In the Cost Analysis Monte Carlo spreadsheet, I have only put 100 rows of random data. If you find that your cost analysis varies greatly every time you recalculate your spreadsheet then add some more rows. Some commentators recommend no less than 5,000 rows but for my purposes I’ve never needed to go above 500 rows to get consistent results with each recalculation.
Doug Hudgeon who is lawyer and vendor management professional who has branched into finance and accounting shared services management.
In summary, the leaders have started Green Supply Chain projects by asking some fundamental questions. The answers are illuminating their way toward innovation, profitability,and sustainability. As is the case in all path-breaking endeavors, the first-mover advantage is enormous, as are the challenges.
