Monte Carlo Simulation

7
23523
Excel Spreadsheets

In this article we will talk about:

What is the Monte Carlo simulation?

The Monte Carlo simulation or Monte Carlo method (MMC) is a statistical methodology that relies on a large amount of random sampling to get close results to actual results. Translating into good Portuguese, it allows you to test variables in a large enough number of times to more accurately predict the chance of some outcome happening.

Economic Feasibility Study SheetTaking the Monte Carlo method for real cases, you can apply the simulation to:

Excel Spreadsheets
  • Finance: analysis of stocks, future options, macroeconomic series, etc.
  • Other Areas: computer graphics, varied analysis, geology, etc.

Name origin

O Monte Carlo name arose from the allusion of one of its main characteristics, the randomness with what occurs in casinos (such as Monte Carlo) daily in games such as roulette.

How to Make a Monte Carlo Simulation

In practice, whenever you come across situations with some level of uncertainty and want to use the Monte Carlo simulation you will have to go through 4 steps:

  • Step 1 - Model the problem
  • Step 2 - Generate random values ​​for the uncertainties of the problem
  • Step 3 - Replace the uncertainties by values ​​to calculate the result
  • Step 4 - Get an estimate for problem resolution

Because it is a very mathematical method and demands specific software for the large number of simulations, I believe that it is possible to simplify the method to obtain practical results and without having a very large work.

Economic Feasibility Study Sheet

Monte Carlo in an Economic Feasibility Study Worksheet

See an example application of a variation of the Monte Carlo simulation on the Economic Feasibility Study Sheet from LUZ. Here you can work on making future projections of revenues, expenses and investments, as shown below:

Monte Carlo Simulation in Excel - Projection

Once you have filled in all the forecast data about your business, you can visualize your projected scenario, which would be a possibility of actual outcome (which may actually happen in the future). Look:

Monte Carlo Simulation in Excel - Economic Feasibility Study

It turns out that, as we know, this is a projection and, as the name itself says, it may not materialize. It is a clear case of uncertainty and that as hard as you try and study the market, you can get confused.

At that time, we adopted a test of optimistic and pessimistic scenarios to work different possibilities of final result. Here comes the simplified Monte Carlo simulation application I was talking about. Below I show the same projected scenario, only now with a positive percent change that you can modify:

Monte Carlo Simulation in Excel - Scenarios

Note that when you make a test by changing the "Revenue prediction greater than" field from 10% to 20%, the gross revenue results for the worksheet are modified as well:

Monte Carlo Simulation in Excel - 1 Scenarios

This is a way to practice a large number of samples and see all possible possibilities for your business. Notice that by making more modifications (this time I modified the 3 items), the results will change and your profit free as well.

Monte Carlo Simulation in Excel - 2 Scenarios

At the end of the day, this is a simplified way of applying monte carlo simulation to an economical feasibility study sheet in Excel. If you liked it click on the image below and know a demo version of the worksheet:

Economic Feasibility Study Sheet

Monte Carlo Simulation in Excel

Although the above example was done in an Excel spreadsheet, it is a simplification of the Monte Carlo simulation. Now I will make another simplification (with few experiments), but that can give you the complete notion of the randomness factor that the MMC has.

Let's make use of the Monte Carlo Method to calculate the probability of having a financial analysis of a company that sells computers. The main variables we will use are:

  • Sale price
  • Direct cost

These 2 variables may oscillate over time, in which case you must set a minimum and a maximum value for each of them

  • Cost of suppliers

This item will vary according to the vendor used. In our case I created a table with a percentage distribution of purchase divided into 4 suppliers

  • Demand

It should follow a historical pattern. In our case we will take into account a demand for 15.000 per month, which can vary (standard deviation) up to 3.000 up or down.

  • Fixed cost

This is a constant item, so it will always have the same value. This is a simplification taking into account that the company will not have large structural changes.

Below I show the values ​​I used directly in the worksheet:

Monte Carlo Simulation in Excel - Variables

From the definition of the variables, we can begin to develop our table of experiments. As I said at the beginning of this article, Monte Carlo simulation involves performing thousands of tests. In our case, I will only do 10, which is statistically little, but that is enough for you to understand the use of MMC. This will be our table:

Monte Carlo Simulation in Excel - Experiments

Economic Feasibility Study Sheet

Now we will see how to do each of the functions and formulas to arrive at our desired result, which is to discover the probability of this business having detriment. For this, we have to calculate the probability randomly for each experiment. In this way:

1. Sale price

As I said, the sale price ranges from the lowest possible price (R $ 100) to the highest possible price (R $ 200). To arrive at this calculation it is necessary to add the lowest price with the difference between the highest and the lowest and multiply this by a random factor. In this way:

= $ C $ 3 + ($ D $ 3- $ C $ 3) * RANDOM ()

2. Direct Costs

This variable will have the same logic as above, see:

= $ C $ 6 + ($ D $ 6- $ C $ 6) * RANDOM ()

Note that the results will always vary between $ 40 and $ 60 for that case and every time I put a new image they will be different. This is due to the randomness generated in the worksheet.

Monte Carlo Simulation in Excel - Direct Costs

3. Expenditures with Suppliers

This time the logic is a bit different. Since we have a number of suppliers (which also comes as a direct cost) we have to calculate the probability of buying from each one of them. For this we use a table with the lower and upper probability limit and apply a PROCV to know the values ​​in this way:

= PROCV (RANDOM (); $ C $ 9: $ E $ 13; 3; TRUE)

Monte Carlo Simulation in Excel - Suppliers

4. Demand

For the calculation of demand we have to use the inverse function of Normal, which asks for probability (we are using the RAND function to generate random values ​​between 0 and 1 - 0% and 100%), the average demand and standard deviation that we already had:

= NORM INV. (RANDOM (); $ C $ 4; $ C $ 5)

Monte Carlo simulation in Excel - demand

5. Fixed cost

This is one of the few constant values, so in this case we will not apply any randomness and leave it always equal to R $ 500.000.

Monte Carlo Simulation in Excel - Fixed Costs

6. Profit or Loss

This is a very simple calculation of profit or loss. Just find out your contribution margin (MC), multiply by demand and subtract from the fixed cost. As we all know, MC is its sales price subtracted from direct and vendor costs. The function will look like this:

=(H4-I4-J4)*K4-L4

Monte Carlo Simulation in Excel - Profit or Loss

7. Results of Experiments

Finally, you can check whether the random values ​​obtained will have profit or loss for each of the 10 experiments.

Monte Carlo Simulation in Excel - 1 Test Experiments

Every time we perform an action in Excel it recalculates the effect of randomness by modifying our result:

Monte Carlo Simulation in Excel - 2 Test Experiments

See what we have positive (profit) and negative (loss). That means that, depending on the forecast we make, this business can be a bad investment. Now comes the time we need to identify how bad or good this investment can be with most 3 indicators:

  • Average Profit

You can reach the average profit using the AVERAGE function of the profit or loss range for the 10 experiments:

= AVERAGE (M4: M13)

  • Standard deviation

The standard deviation is calculated in the same way

= DEVICE (M4: M13)

Whenever the standard deviation is greater, equal or close to the average profit, this means that you have a greater risk of loss. Anyway, the most important indicator that you have to look at is:

  • Probability of having less than zero profit

In our worksheet the result was like this. That is, for this particular test with 10 experiments, we have a probability of 21% of having detriment.

Monte Carlo simulation in Excel - result

This is the indicator that will give you the degree of risk of entering into this business or not. Obviously because 10 has only adopted experiments, the variations of probability turns out to be very large from one random test to another and this is not ideal.

To give you an idea by doing a series of tests, I had this indicator ranging from 12% to 32% of profit probability less than zero. Now, by expanding my sample to 1000 experiments, the variation ranged from 21% to 23%, meaning a more assertive value closer to reality.

How about using the Monte Carlo Method now?

See how the result can be cool and help you make business decisions? If you want to enjoy our economic feasibility study sheet which has the simplified MMC click the image below:

Economic Feasibility Study Sheet
Economic Feasibility Study Sheet
Excel Spreadsheets

7 COMMENTS

  1. Thanks so much for the info!
    My simulation is for another area, but it helped a lot!

  2. Hi Eduardo, I never did not, but if you give me a briefing of what needs to be done, we can analyze the possibility of developing

  3. Good morning, Rafael.

    Have you ever set up any spreadsheet for ALM application, marrying financial assets (investment portfolio) compared to the actuarial liability (series of social security payments) for the mass of beneficiaries?

    If yes, how can I get it and what is its price?

    You can reply me by the email below.

    Thankful,

    Eduardo Toledo

  4. Hi Igor, if I'm not mistaken you need to do the percentage calculation of how many times the profit appears below zero in the calculations. That way, the more tests you do, the more real the probability will be.

LEAVE AN ANSWER

Please, write your comment
Please enter your name