Build a Revenue Model through Simulation
Revenue prediction and forecasts are typically done to be too right. They create a false sense of precision that ultimately falls short and does a disservice to managers who need accurate forecasts for planning. Even meteorologists equipped with voluminous historical data and supercomputers cannot get it right all the time. Simple revenue model simulation walks you through building a small stochastic system having a random probability distribution which may be analyzed statistically but may not be predicted precisely.
Why Simulation?
Often,we do not find a mathematical technique that can solve a model. Simulations can build good probability models which give us a better insight into the future. There could be thousands of potential variables involved in monthly sales, and we cannot manage them all. Simulation can help emulate real world combinations with the help of few variables. Remember it is not a magic bullet to create forecasts. For products that have sale history and seasonality , ARIMA and Time Series techniques can be applied.
Simple Revenue Model Simulation using Triangular Distribution
The example in this post is driven by stochastic inputs. The stochastic input includes units sold and sales price for a product without history. Since these parameters are non-deterministic, the user is asked to guess the best, worst, and most likely cases. A triangular random variable traverses the values that may occur between the best case and worst case values. For this revenue prediction program, we want to generate a random number from the triangular distribution for a given probability of this input. Hence, the triangular distribution is modeled with the following parameters:
- a (worst case value)
- b (best case value)
- m (most likely value) or (a ≤ m ≤ b)
Both a and b serve as the boundary parameters.
Probability Density Function PDF is defined as,
The inverse triangular distribution function is defined as,
These PDFs are used to create values for the stochastic input such as units sold during the simulation runs. In every single run, these values are updated and recorded on a simulation calculation. We use the above inverse cumulative method for generating a random number.
Build Simulation Model in Excel
The spreadsheet is quite straightforward. Although few features require manual setup. You can begin entering best, worst and most likely (mode) of units sold and sale prices in columns C5 through E6. Enter total trials or simulation runs in column C10. I’ve simulated the revenue model using 5000 runs.
Next step is to start the simulation. Higher number of runs makes your data set more realistic but it comes at the expense of computing speed and processor limitations. Cells B13 through E5012 are output of the simulation. TraingleDist() function takes best case, worst case, and most likely values from the above input and outputs a random number that is found from this triangular distribution for a given probability.
These VBA lines perform simulation from a triangular distribution behind the scenes. The histogram setup requires a manual step. If you plan on simulating 5000 trials, you can ignore this step. There are 16 bins that represent the revenue data intervals. For number of trials other than 5000, simply change the following formula in cell K13.
=COUNTIFS($E$13:$E$5012,”>=”&J13,$E$13:$E$5012,”<“&J14)
If you are to simulate for 2000 runs, change the formula to
=COUNTIFS($E$13:$E$2012,”>=”&J13,$E$13:$E$2012,”<“&J14) and drag it all through K28.
Limitations of a Simulation Model
The predicted revenue peaks around 22K. 5000 simulations may seem unrealistic or not enough to get a good sample set. But the simulation approach is much effective than averages and plain guesses. In this model, I have excluded other parameters such as Fixed Overheads, and Depreciation to keep the model simple.
The limitations of running huge Monte Carlo Simulations are mainly due to memory, and processor speed. You must plan for long tail events. Questions to be addressed are – how to deal with just 16K revenue or how can a business support unexpected growth of 36K in revenue?
[sociallocker]Download Simple Revenue Model Simulation Spreadsheet[/sociallocker]