In this Excel tutorial you will teach yourself how to perform Monte Carlo simulation in Excel.

How to use Monte Carlo simulation?

Monte Carlo simulation is a technique used to model and analyze the impact of uncertain variables on the outcomes of a system or process. Excel is a popular tool for performing Monte Carlo simulations because it provides many built-in statistical functions and tools that can be used to generate random numbers and analyze data.

Here are the steps to perform a Monte Carlo simulation in Excel:

1. Define the input variables: Start by defining the input variables that affect the outcome of your system or process. These variables can be defined using the “Data” tab in Excel.
2. Generate random values: Use the Excel “RAND()” function to generate random values for each of the input variables. You can generate as many random values as you need by copying and pasting the formula.
3. Define the output equation: Define the equation that determines the output of your system or process. This equation should include references to the input variables and the random values generated in step 2.
4. Calculate the output: Use the Excel “SUM()” function or other appropriate function to calculate the output for each set of random values generated in step 2.
5. Analyze the results: Use Excel’s statistical functions to analyze the results of your simulation. You can use tools like histograms, scatter plots, and summary statistics to better understand the distribution of the output.
6. Repeat the simulation: Repeat the simulation with different random values to get a sense of the range of possible outcomes.

Overall, performing a Monte Carlo simulation in Excel requires a basic understanding of Excel’s built-in functions and the ability to use them to analyze data. With a little bit of practice, you can use Excel to simulate a wide range of systems and processes and gain insights into how they might behave under different conditions.

Example of the Monte Carto Simulation

Let’s say you rule the company. The business is tough so risk of your business is high. You need to be extremely careful.

The task is to check if it is a good idea to introduce a new product to the market.

Perform Monte Carlo simulation to calculate profitability of a new product and calculate probability of profit.

You had a talking with your experts so you know some data:

• fixed costs $80,000-$110,000 depends on resources costs
• number of clients 400-500
• unit price $215

To perform Monte Carlo simulation of introducing a new product to the market in Excel first you need spreadsheet with data.

Columns are:

• lp – just for a reference – let’s prepare 10,000 simulations – the more the better
• revenue – number of clients multiplied by unit price to calculate how much money the new product will earn
• costs – 80,000 – 110,000 as we got the info from our resources department
• financial result to check what is the difference between revenue and costs – this we will need to calculate average profit
• profit/loss to see if the financial result is above zero – this we will need to calculate probability of profit

Monte Carlo simulation is all about random numbers. We don’t know costs and clients so we need to generate random numbers in Excel to perform the simulation. Luckily there is RAND function in Excel which will generate random numbers for us.

To understand the base of simulation you need to understand how Excel RAND function works:

• RAND function is randomly generating the number between 0 and 1.
• to get the number between 0 and 100 you need just to multiply the result of RAND function by 100. The formula will be =RAND()*100
• to simulate 200 – 300 range just add 200 for that using formula =RAND()*100+200

I hope you understand that. Better read these points a few times to fully understand the clue of that to fully understand Monte Carlo simulation.

You can use RANDBETWEEN Excel function if you don't care about forecast accuracy so much. It will generate integers in given range when RAND is generating decimals.

Monte Carlo calculations

Let’s move on with calculations.

To calculate revenue I used =(400+RAND()*100)*215 formula:

• remember that number of clients is 400-500
• 400+RAND()*100 will generate random number between 400 – 500
• unit price is 215 and I need to multiply random number of clients in range between 400 and 500 to get total sales

Apply formula to entire column.

Next one is cost. Simuilarly formula is =(80000+RAND()*30000) to generate random number in range 80,000 – 110,000.

To calculate financial results you just need to know how to subtract in Excel. The formula is =B2-C2.

For the next Profit/Loss column we need to know if it is true or not. Let’s agree that for profit we will see 1 and 0 will be for loss. IF Excel function will do the job!

The formula is =IF(D2>0,1,0)

Now we are able to calculate average of expected profit. Of course there is dedecated function for that. Average Excel function will calculate it.

The formula is =AVERAGE($D$2:$D$10001)

To calculate probability it is enough to sum column E. Excel Sum function will do the job.

The formula is =SUM($E$2:$E$10001)/10000

Hit F9 keyboard key several times. Please notice that the numbers change slightly. The more rows the smaller the changes will be.

Monte Carlo simulation is finished. Now based on the number you can decide if it is worth to take the risk and introduce the product into the market.

Excel did the job, now it is on you to take a call.

You can download the free Monte Carlo simulation spreadsheet from here.

Related posts:

How to Stop Excel from Changing Numbers to Dates Modifying a calculated field in a pivot table How to create IN and OUT Inventory System in Excel?