In this article we will talk about:
- What is the Monte Carlo simulation?
- How to do Monte Carlo simulation?
- Simulation of Monte Carlo in an Economic Feasibility Study Worksheet
- Monte Carlo Simulation in Excel
- A tool for you to put the method into practice right now
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.
- Finance: analysis of stocks, future options, macroeconomic series, etc.
- Other Areas: computer graphics, varied analysis, geology, etc.
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.
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.
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:
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:
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:
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:
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.
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:
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
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:
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:
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.
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)
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)
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.
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:
7. Results of Experiments
Finally, you can check whether the random values obtained will have profit or loss for each of the 10 experiments.
Every time we perform an action in Excel it recalculates the effect of randomness by modifying our result:
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.
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.
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: