How to Build Your Own Cost Analysis Monte Carlo Simulation in Excel or Google Spreadsheets

How to Build Your Own Cost Analysis Monte Carlo Simulation in Excel or Google Spreadsheets

AUTHOR

chiefstaff

TIME TO READ

minutes 

UPDATED ON

January 8, 2019

hudgeon12By Doug Hudgeon 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 fire walled 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 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:

  1. Activity time in minutes
  2. Annual resource cost
  3. 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 Actiity1 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.


Copyright - These concepts, frameworks and ideas are copyright of GLOBAL SUPPLY CHAIN GROUP from the time of their creation. Do NOT copy these without permission and proper attribution.

Notes:

  1. These ideas and concepts will be usually expressed by our thought leaders in multiple forums - conferences, speeches, books, reports, workshops, webinars, videos and training. You may have heard us say the same thing before.
  2. The date shown above the article refers to the day when this article was updated. This blog post or article may have been written anytime prior to that date. 
  3. All anecdotes are based on true stories to highlight the key points of the article - some details are changed to protect identification of the parties involved. 
  4. You are encouraged to comment below - your real identity and email will not be revealed when your comment is displayed.  Insightful comments will be  featured, and will win a copy of one of our books. Please keep the comments relevant, decorous and respectful of everyone. All comments represent opinions of the commentators.

chiefstaff

Our Quick Notes On Five Flows Of Supply Chain Management

Part of our new “Quick Notes” series – this report answers your most pertinent questions of the topic.

  • What are the five flows of SCM?
  • Why are they important TO YOU?
  • How can you map, track, and optimise these flows to serve YOU?
  • What is the importance of difference between "Supply Chain" and "Value Chain"?
  • What are the stellar case studies of each of the five flows?

LIMITED TIME 

USD 20

FREE

MORE INTERESTING READING
Logistics, Procurement, Production Planning, Warehousing, Transportation – Where Do The Most Effective Supply Chain Leaders Come From?
Which jobs or careers will grow in value and demand in the wake of COVID-19?
How to Reduce Complexity in Supply Chain Post COVID-19?
  • Great concept to analyze cost on a spreadsheet. Key Points for the cost analysis are as follow:
    Cost-benefit analysis is a relatively straightforward tool for deciding whether to pursue a project.
    To use the tool, first list all the anticipated costs associated with the project, and then estimate the benefits that you’ll receive from it.
    Where benefits are received over time, work out the time it will take for the benefits to repay the costs.
    You can carry out an analysis using only financial costs and benefits. However, you may decide to include intangible items within the analysis. As you must estimate a value for these items, this inevitably brings more subjectivity into the process.
    I prefer the above method to do analysis by now. Thanks

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    FOR SENIOR EXECUTIVES AND DIRECTORS

    YOUR HIGHEST VALUE ADDED IN YOUR COMPANY