Image by Editor
Monte Carlo simulation is one of the easiest and most practical ways to explore uncertainty and randomness. It allows you to model random variation and estimate probable outcomes through repeated random sampling. While it is often done in R, Python, or specialized software, Google Sheets can serve as a lightweight simulation platform. Its RAND() and ARRAYFORMULA() functions make it possible to run hundreds or even thousands of trials instantly, all inside your browser. In this tutorial, we will show you how to build a simple Monte Carlo simulation in Google Sheets.
What Is a Monte Carlo Simulation?
A Monte Carlo simulation is a computational method that uses random sampling to approximate uncertain outcomes. Instead of solving equations analytically, it generates many random scenarios called trials and observes their results. By averaging those outcomes, you can estimate probabilities, expected values, or risks.
The simulation technique gets its name from the famous Monte Carlo Casino in Monaco, symbolizing randomness and chance. Simulation-based reasoning helps you estimate results that depend on randomness. It’s widely used in fields like finance (to forecast investment returns), supply chain management (to estimate delivery delays), and project planning (to model completion times).
Instead of relying on one deterministic value, you can model each uncertain input as a random variable, run many trials, and observe the distribution of possible results. The more simulations you run, the closer your estimates come to the real-world probability distribution.
1. Building a Simulation for Dice Roll to Estimate a Dice Game’s Average
Step 1: Set Up a Simulation for Dice Roll
Let’s build a simple simulation, rolling a fair six-sided die. The goal is to estimate the average value of the roll using repeated random trials.
To begin, open a blank Google Sheet and prepare a simple layout for your simulation. In cell A1, type “Trial”. In cell B1, type “Roll”.
Now, select cell A2 and insert the following formula.
=SEQUENCE(1000)
This formula creates 1,000 trials automatically.
Next, select cell B2 and insert the following formula.
=ARRAYFORMULA(ROUNDUP(RANDARRAY(1000,1)*6))
This automatically generates 1,000 random integers between 1 and 6, simulating die rolls for each trial. Each time the sheet recalculates (for example, when you edit a cell), the random values change, giving you a new sample of possible outcomes.
Step 2: Estimate a Dice Game’s Average from Random Outcomes
Now that you’ve created 1,000 random rolls, let’s estimate the average result of all trials.
To analyze the average outcome, select a cell and insert the following formula.
=AVERAGE(B2:B1001)
This formula returns the average of all rolls. You’ll notice, as the sheet recalculates, the average fluctuates around 3.5, which is the theoretical mean for a fair die.
What you’re witnessing is convergence under the law of large numbers: the more trials you run, the closer your simulated result gets to the expected value.
2. Applying Monte Carlo Simulation to a Business Scenario
Let’s build a model where you want to estimate daily revenue for an online store. Imagine you expect between 20 and 40 customers each day, and each customer spends between \$50 and \$150. You want to estimate what your total daily revenue might look like under random conditions.
Step 1: Set Up the Business Scenario
First, create a trial list in column A. In cell A1, type “Trial”. In cell A2, insert the following formula to create 2,000 trials.
=SEQUENCE(2000)
In column B, simulate the number of customers per day. In cell B1, type “Customers per Day”. In cell B2, insert the following array formula to generate random integers between 20 and 40 (inclusive) for all 2,000 trials.
=RANDBETWEEN(20,40)
This formula fills the entire B column range for 2,000 trials with uniform random integers.
In column C, simulate average sales per customer. In cell C1, type “Average Sales per Customer”. In cell C2, insert the following formula to generate whole-dollar amounts between \$50 and \$150 for all 2,000 trials.
=RANDBETWEEN(50,150)
This formula populates column C with uniformly distributed per-customer spend values from $50 to $150.
In column D, multiply both random factors to get the total daily revenue. In cell D1, type “Total Revenue”. In cell D2, insert the following formula.
=ARRAYFORMULA(B2:B2001*C2:C2001)
This formula computes total revenue for each trial.
Step 2: Compute Summary Metrics
To summarize the results, use the following formulas.
Average Daily Revenue:
=AVERAGE(D2:D2001)
Minimum Possible Revenue:
=MIN(D2:D2001)
Maximum Possible Revenue:
=MAX(D2:D2001)
These calculations give you a range of likely daily outcomes. By analyzing these results, you get an intuitive way to quantify risk and uncertainty in business projections. Every recalculation shows a new possible revenue distribution, allowing you to visualize the uncertainty behind your business projections.
Step 3: Refine the Simulation Using Probability Distributions
You can refine the model by using probability distributions, as real-world data rarely follows a simple uniform pattern. Some days might have many low-spending customers, while others have a few high spenders. To represent this variability more accurately, you can switch to a normal distribution using NORMINV().
In column C, replace your average sales per customer’s previous formula with the following formula.
=ARRAYFORMULA(NORMINV(RANDARRAY(2000,1),100,20))
This formula assumes an average sale of \$100 with a standard deviation of \$20.
Now the daily revenue reflects a more realistic variation, clustering around a typical value but occasionally producing unusually high or low totals.
Step 4: Visualize the Simulation
You can visualize the simulation as a histogram chart to show how likely different outcomes are.
- Select the Revenue column
- Go to the Insert tab → select Chart
- Select Setup from Chart editor → select Histogram from Chart type
The shape of this histogram represents the simulated revenue distribution, giving you an intuitive sense of how likely different outcomes are.
Once the foundation is clear, you can expand this framework to model investment returns, queueing times, product defects, or project completion delays. Using RAND(), NORMINV(), or LOGNORMINV() functions, you can introduce custom probability distributions instead of simple uniform ones.
Conclusion
By following these steps, you can build a simple Monte Carlo simulation in Google Sheets. Monte Carlo simulation doesn’t require advanced software; it only needs randomness, repetition, and simple arithmetic. Google Sheets provides all of that through its dynamic recalculation engine and powerful array formulas. It bridges the gap between static estimates and probabilistic thinking. By modeling random events and aggregating their outcomes, you can uncover insights about risk, probability, and expected performance. Once you understand this foundation, you can extend the same logic to model investments, inventory, or even scientific experiments.
