Global Supply Chain Group

Menu
Edit

How To Build Your Own Cost Analysis Monte Carlo Simulation In Excel Or Google Spreadsheets

Share
Share
Share
Google Spreadsheets

Share:

How To Build Your Own Cost Analysis Monte Carlo Simulation In Excel Or Google Spreadsheets

By Doug Hudgeon

Global Supply Chain Group - ddc

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.

Share Generously :

LinkedIn
Twitter
Facebook
WhatsApp

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.

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.

LIMITED TIME
USD 20
FREE

. 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?

MORE INTERESTING READING

Leave a Comment

Your email address will not be published.

Table of Contents

ABOUT THE PRINCIPAL AUTHOR

Today, Vivek and his partners are among 20-30 people on the planet earth who have this deep understanding of supply chain systems, practices and tools. CEOs, COOs, executives and Boards call them in most challenging situations once they know the full potential of supply chain based transformations. Following are key milestones in Vivek's journey:

  • Started in 1983 as a merchant navy cadet at 18 years age, worked his way to qualify as a Captain – qualified to take command of any merchant ship, worldwide.
  • Earned a top tier MBA from UNSW at the top of his class.
  • Joined highly regarded strategy consulting firm Booz Allen & Hamilton, consulting to the CEOs, Boards and senior management of global corporations within Australia.
  • To learn and specialise in supply chain – against all odds, sought out the co-inventor of supply chain in Germany and convinced him to be a partner in his firm, GLOBAL SUPPLY CHAIN GROUP, launched in January 2000.
  • More than 500 successful blue chip projects with high impact business transformations in large corporations using the full power of SUPPLY CHAIN MANAGEMENT.
  • 4 Seminal and path breaking business books IN SUPPLY CHAIN MANAGEMENT – these are available in bookstores and universities and libraries worldwide.

Limited Time

FREE

This offer expires in

Our Quick Notes On Five Flows Of Supply Chain Management

US$20

USE CODE "FREETODAY"

WHAT OTHERS ARE SAYING

Our Clients say it better than we ever could:

TRENDING POSTS

OUR CLIENTS

Our Clients come from a variety of industries – yet they have a common element. They rarely rest on their laurels, and are always looking to do better.

OUR PROJECTS - EFFECTIVE TRANSFORMATIONS

In the last 20 years we have completed more than 500 projects. Click below to see a sample of our projects.

OUR TESTIMONIALS

RELATED POSTS

Our Books

5.0
5/5

THE 5-STAR BUSINESS NETWORK

If you are deeply passionate about the world of business and supply chain networks as I am, and enjoy digging answers to critical questions that will help build and steer your business with wisdom, then join me. This book is a journey of exploration through the world of business networks that run along the veins of today’s commercial world.

4.3
4.3/5

OUTPERFORM OUTSOURCE OUTPROFIT

The trend of outsourcing continues to grow unabated with the whole gamut of services, from simple to mission-critical tasks. There is not a single company on earth that does not outsource anything. It is not just about cost arbitrage, it is also a finer expression of division of labour at the organisational level. Like all leverage, outsourcing is a double-edged sword too. On one hand, it allows you to do more, faster. On the other hand, if it goes bad, it can easily kill your business. If you do not believe that is possible – you can google the Fox Meyer saga from the 90s and see for yourself.

4.3
4.3/5

UNCHAIN YOUR CORPORATION

Businesses Are Chained By Unseen Chains. If You Are Looking For Ways To “Unchain Your Corporation” A Successful Business Transformation Is Required.

Successful Business Transformations Are Difficult, Yet Rewarding.

Business Transformation Is Fast Becoming A Question Of Survival In The Modern Globalised Era.

Modern Supply Chains Integrate Businesses And Economies Faster By Systematic Information Sharing From Internal And External Sources.

Companies Can Multiply Profits By Progressively Ramping Up Cohesion And Collaboration Of All Moving Parts In B2B Network To Achieve Tighter Integration.

4.3
4.3/5

GREEN SUPPLY CHAIN – AN ACTION MANIFESTO

It is generally accepted that environmental consciousness is now changing to environmental proactiveness as organizations are discovering that it makes good commercial sense.

Boards are asking the management to review their policies related to environmental norms, not only to bolster their corporate social responsibility aims, but also because consumers are asking for greener supply chains

It is also widely agreed that consumers will increasingly prefer to buy more and even pay more for products or services provided in an environmentally sound manner.

FOR SENIOR EXECUTIVES AND DIRECTORS

YOUR HIGHEST VALUE ADDED IN YOUR COMPANY

Scroll to Top