Somebody asked me about building a small business Monte Carlo simulation worksheet.
They’d seen the earlier blog post we’d done about and that had led to the question.
I don’t want to fall down into the same rabbit hole that Alice in Wonderland did.
But you doing a Monte Carlo simulation of small business entrepreneurship outcomes seems like a pretty good idea.
A Monte Carlo simulation probably highlights the risks you face as a small business owner and entrepreneur. And such a simulation probably helps set more reasonable expectations as to financial outcomes.
But enough dithering. Let’s get into the details.
Grabbing the Small Business Monte Carlo Simulation Worksheet
In the earlier blog post about stock market Monte Carlo simulations, I described the steps for building an actual workbook.
This time I’m not going to do that. Instead I’m just going to provide you with a link you can use to grab a Google Sheets spreadsheet that does the simulation:
Note: To build your own small business Monte Carlo simulation workbook, refer to the original blog post about building a simulation model for the stock market. The small business version of the spreadsheet is the same spreadsheet with two minor tweaks. First, I shortened the length of the forecast from 40 years to 20 years. Second, I expanded the workbook to model 25 scenarios a time.
Tip: To work with the spreadsheet using Microsoft Excel, first open the spreadsheet as a Google Sheet spreadsheet by clicking the link. Then while displaying the spreadsheet in the web browser window, choose the File menu’s Download As Microsoft Excel workbook command.
Using Small Business Monte Carlo Simulation
To use the small business Monte Carlo simulation workbook, you input three values:
- Standard deviation percentage,
- Nominal arithmetic average return percentage, and
- Initial investment dollar amount.
The Google Sheets spreadsheet then forecasts end-of-year future values 20 years into the future and for 25 different simulations.
The figure below shows the spreadsheet fragment where you enter these inputs. I also plopped a little summary area beneath the input area, as shown in the fragment below.
But where the real action occurs is in the spreadsheet ranges that report on the simulations.
I show a fragment of this part of the workbook below. Note that each year’s end-of-year investment value equals the starting investment value for year adjusted for an annual return that arithmetically averages the percentage return shown in cell B3 (20% annually in my example) but which shows the variability quantified with the standard deviation in cell B2 (40% annually in my example).
Good Inputs for Your Monte Carlo Simulations
You’re going to need to come up with appropriate values for the arithmetic mean return and the standard deviation.
But as starting guesses, using 20% as the arithmetic average return and 40% as the standard deviation probably work pretty well.
Those values mesh with the long-run returns and variability of microcap stocks (the very smallest of which are not that much larger than some “big” small businesses.)
The values also make sense in light of the rates of return that commonly you hear angel investors and venture capitalists talk about.
Tip: If you have access to the , look there for good microcap return and standard deviation information since these values provide a common-sensed starting point for your spreadsheet inputs.
The initial investment value just equals the capital the entrepreneur contributes at the start of the venture. You can set this value to anything. I plopped in $25,000 as the starting value suggesting the small business owner starts out by investing $25,000.
Understanding Small Business Monte Carlo Simulation Results
Okay, obviously, the simulations become more interesting when you plug in your own inputs.
But even with the “plugged” 20% return and 40% standard deviation, the worksheet provides interesting data and maybe some actionable insights.
First, look at the median return your simulation calculated and compare it to the arithmetic average return.
This median return, also known as a internal rate of return (IRR) or a compound average growth rate (CAGR), falls way short of the arithmetic average return.
That’s not really a surprise if you’ve studied much about investing. Volatility in the year-to-year average returns drags down the effective long-run average returns. But what maybe is a surprise is how big this shortfall is, how big the so-called volatility drag is. Yikes.
Note: US public company stocks show a standard deviation of maybe 15% to 20% depending on the time frame you use for your calculations.
A second, related point about the median return: You and I get many good things from owning our own small business. But if reality looks at all like the small business Monte Carlo simulation models, many small business owners are not going to earn returns on their small businesses that impressively beat what’s been available in recent decades from big pubic company stocks.
Don’t get me wrong, I think the small business thing is a great option for many people. But that said, small business returns in many cases fail to beat the returns available from, say, an equity index fund. Further, you may be limited in how much you can invest in a small business and earn the higher small business return available.
Note: I’ve talked about this issue before in relationship to and suggested that surely other small business owners commonly experience the mediocre results that many small CPA firms deliver to their owners.
A third observation: Look at the worst and best outcomes. Even over a forecasting horizon of just two decades, you see wide, wide variability in outcomes.
Note: If the minimum outcome shows as a negative value, that indicates a scenario where the entrepreneur lost everything… and then some. You can’t calculate a rate of return for this scenario, so the spreadsheet returns the #NUM error.
Charting the Simulation Results
The small business Monte Carlo Simulation worksheet includes a chart to visually depict outcomes, and that chart highlights additional interesting insights (shown below).
The chart, for example, shows the wide dispersion of outcomes. (Most of the simulations’ charted data show up in that colorful braid of nearly flat lines that run almost parallel to the horizontal axis.)
The chart also shows the minority of outcomes that reflect game-changing results. Regularly you see simulations that produce a 30% to 35% return for 20 straight years. (That line that skyrockets exponentially at the end, for example? That’s the guy you know from high school.)
Furthermore, do enough simulations (like five to ten thousand?) and you’ll find a simulation that produces venture capital type annual returns over twenty years: 40%, 50% or 60% annually. Those sorts of returns may create a billionaires of course.
But the insight here?
The small business Monte Carlo simulation suggests this about windfall type outcomes: Hard work and a high IQ–while surely necessary for enjoying a windfall outcome–are apparently not sufficient.
Random chance or dumb luck or whatever you want to call it matters for the truly unusual sequences of returns.
Four Closing Comments
Four, quick closing comments to end this foray into weeds of the Monte Carlo method.
First, while maybe absurdly theoretical, a small business Monte Carlo simulation, I think correctly, suggests that entrepreneurs and small business owners experience a wide range of outcomes.
Second, note that an over-simplification “error” appears in this simulation. You and I typically don’t get to hold our small business investment for twenty years and then enjoy decades of steady compound interest.
Rather, we more likely invest some money at the beginning (like $25,000 or whatever) and then periodically receive a chunk of the return at various points along the journey: $40,000 in extra profits in year 6, $20,000 of extra profits in year 11, and so on. The trickle of returns means we need to be good about saving that money and not spending it on consumption along the way.
Another way to say this same thing: Our profits probably come in lumps over the years we operate. You and I really want to save these lumps.
Third, a related housekeeping matter: We should not rely on a big windfall event due to small business ownership as the way we make retirement work financially. We need to have robust traditional retirement savings plan in place just like everybody who works a regular job.
Fourth, unlike investing in large public companies, I think you and I can improve our entrepreneurial outcomes. We can work harder and smarter and get a better outcome or get a lower risk ride than someone who doesn’t work as hard or as smart.
To restate this in terms of the simulation worksheet’s inputs, I believe you can nudge up a bit your average annual return by making smarter decisions. And I believe you can dial down a bit your risks–the standard deviation–through cautiousness and prudence. And these actions can impact your financial outcome.
Other Resources You Find Also Find Relevant
A while back, we did a blog post that described why business failures often aren’t really failures. You might find this useful if you’re here:
You might also find this blog post about the categories that many small business ventures seem to “fall into” interesting:
If you’re still at the “thinking” stage of entrepreneurship, this essay might provide some useful context: And this one too:
Finally, these discussions should provide some comfort to people already on the road of small business ownership: and
The post appeared first on .