(If a picture is blurred, click on it to watch
it in full size)
A) The traditional approach:
Brad wants to bake and
sell a special type of bread, and he calls the project "Brad's
bread". He believes he can bake and sell a hundred per day,
which means 25,000 during a year, at a unit price of $3.20. This
will generate a revenue of $80,000.
Ingredients (flour, eggs etc.) cost $0.75 for each bread he bakes. This is variable cost per unit (VCU). If no units are produced, there also are no variable costs in total. If he makes 25,000 breads, the variable costs are $18,750 in total.
Like anyone else, Brad wants to get paid for what he does, this is a basic precondition. He therefore calculates his minimum wages into the fixed costs. In addition, every cost that is running at the same level whether he produces much or little (rent, heat etc.) is included in the total fixed costs (TFC).
The Break even point is calculated by dividing the total fixed costs (TFC) with the contribution margin per unit (CPU), and tells us how many units must be made and sold before profit starts being generated (as a bonus in addition to the wages Brad has already calculated into the fixed costs). If he sells more than 20.408 breads, he will have more in return than he presumed as a minimum.
But this traditional approach has some disadvantages. Brad may fool himself if the numbers are unlikely. It is difficult to look into the future. Brad cannot be sure whether his presumptions are correct, so there is some insecurity about the whole calculation and what actions it should initiate. May it be wrong to set ones future at stake on the basis of just one scenario noone can be sure of?
B) The BC/WC approach:
Maybe he will get only $3.00 per bread instead of $3.20, and maybe the ingredients rise to become more expensive than he thought, e.g. $1.00. Heat and rent may rise, and his own presumed minimum wages may also be too low to pay his personal bills, which gives him an impression that fixed costs should be set to $70,000 as a worst case. This sad picture comes up:
Under the worst case presumptions, this project does not look tempting at all.
But it may also come out better than expected!
If he is lucky, he may get $4.00 for each bread, and it may cost only $0.5 to make one. And he may also get better deals on rent and heat, making the total fixed costs to decrease to $30,000. The break even point will then be 8,571 units sold, which is significantly lower than the in worst case, and much better than in the more typical scenario he first calculated.
In addition, he may be able to sell 35,000 units. The best case scenario will generate a profit of 92,500. It is not likely that all these lucky events will take place during the same year, but it is relevant to consider the possibilities in the optimistic end of the scale too. And after all, this is just as likely as the pessimistic view!
This looks better, but also
more confusing. The truth may lie somewhere inbetween.
Or does it?
Should he expect a profit in the middle? Or a break even in the middle? Or sales?
What if the SPU, VPU, Units sold and TFC were in the middle of the assumed interval? Would the profit then be in the middle? The answer is NO:
If Brad assumes a profit lying in the middle between Ė44,000 and 92,500, it will be 24,250, which is better than the 16,000 above. But a break even lying in the middle between 35,000 and 8,571 is 21,785.5, which is worse (because it is higher) than the 18,182 above.
In general, the fact that "the truth" lies in the middle, is a
myth. The middle of what?
Consider this example: Brad makes better bread than anyone, and people go crazy and want to buy all he produces, and he throws all his competitors into bankrupcy. In theory this may be possible, though not likely. In that case he may sell 50,000 breads a day in his home town, which would add up to about 12.5 millions units per year. It is also theoretically possible to sell as little as 0 breads a day, although this is rather unlikely too. Should Brad then expect to sell 6.25 million a year, since this value is "in the middle of the road"?
C) The pitfalls of a traditional approach
The traditional approach of calculating a project has its disadvantages, as shown in the example above. In order to generate reliable decision data, we need very reliable data into the model. This is the main weakness of any budget and scenario analysis. Crap into a calculation model means crap out of it.
Using Monte Carlo simulation is less vulnerable to unreliable data in as it provides the opportunity to use whole intervals of values, not only single figures. You donít have to pick one particular value to use for an input in your model, you simply use all of them (in a probability distribution) if they make sense. Also, the method in itself forces the user to make up her mind whether values are likely, and how their probability is distributed.
YourSim is not the most advanced model on the market, but it is an easily graspable decision tool, easy to use for those who are not mathematicians, statisticians or finance analysts. It is convenient for entrepreneurs who want a wider view on risk, fortune, scenarios and possibilities, as it provides the probability for generating a loss (or negative profit), and graphs and numbers to describe two important dependent variables break even and profit.
By excluding depreciation and including all expenditure
(morgage loans etc.) in the total fixed costs, the outgoing data
will reveal the risk of running short of cash (going bankrupt)
instead of generating a loss (due to accounting terms).
D) Correlation between
price, costs and demand
In the example above, in the WC (Worst Case) and BC (Best Case) perspectives, no input variables correlate. For instance, low demand and low price are combined. How likely is this to appear in real life?
In Bradís case, it is not likely. If his breads are cheap, customers will come running, leaving their former bread suppliers to get a better deal at Bradís. This means that the demand will rise. In fact, in an open and free market where suppliers of equal (or substitutable) products compete on price, the correlation between price and demand is always negative, meaning that low price triggers high demand and vice versa, if other relevant factors are alike between competitors. That is, the consumer thinks that the competing products are of equal use to them. Then they compare prices before they go shopping and run away from the expensive suppliers. The dependence between supply and demand is well known in economic theory.
Also, in the same market environment or region, costs tend to correlate. Think of it, when times are bad, everything tends to become cheaper (wages, rent, food) to meet a shrinking demand from all sides of the community. (The fact that an increasing number of people still canít afford necessaries is a different issue.) In good times, the opposite is the case, and prices tend to rise. Statistics over consumer price indexes coupled with historical data over a nations conjunctures illustrate this.
But many companies spread their activity over multiple regions, under conditions that are totally different than for local businesses like the one in Bradís case. YourSim takes into account that boundaries and conditions are depending on the cultural, geografical and market presumptions relevant to the entrepreneur.
Important issues to be considered:
According to these market and organisational factors, five scenarios are possible in the model:
These 5 alternatives may not cover all situations, but they make the user adapt the simulation method to most actual marketing strategies and concepts.
The correlation between the four factors Units sold, Sales
price per unit (SPU), Variable costs per unit (VCU) and Total
fixed costs (TFC) in the 5 different market scenarios of choice
in YourSim are as follows, with numbers according to the
The YourSim approach of calculating
Now back to the "Brads bread" example.
According to the description of 5 main settings above, the
most convenient one is alternative 1, presuming that there is
some competition from other bread suppliers in the market, and
that Brad buys his ingredients locally.
The data below are due to such presumptions, and they
therefore differ from the data in the traditional calculation
above, where nothing was presumed considering correlation
between the four in-data factors (and then look more alike the
5th scenario alternative, discussed later).
It is also possible with other alternatives: Brad may have a unique receipt for his bread, making it something special for his customers, who can not find substitutes for his bread anywhere. He becomes a local monopolist, and his project then fits more into alternative 2 above. The outcome from the five different alternatives using the same data are revealed later in this chapter. For now, market scenario alternative 1 is chosen:
In addition to the values Brad expects for the four variables sold units, sales price, variable cost per unit and total fixed costs for the actual period of time (e.g. 1 month, 1 year, 2 years etc.), he also considered a worst case and a best case scenario that were equally probable. The latter is of great importance, the limits of a given interval should be of equal probability when used in a MC model.
YourSim does not ask for WC and BC scenarios, it simply asks
for minimum and maximum values (if any) for each of the four
variables, and then the user has to decide how the values are
distributed (choosing numbers 1 to 4 in the right column).
By moving the cursor to the red corner of some of the cells, explanation, help and advice is revealed:
The distribution choices are explained to the right of the
The number 1 makes it possible to use numbers that are not probability distributed but single and sure figures in combination with the other variables probability distributed. Triangular distributions (3) may be asymmetrical (skewed) or not depending on the data input, while (2) and (3) are defined as symmetrical distributions.
The user can control that the data going into the model has
the intended distribution shape by going to the sheet "In",
where graphs of the in-data are found:
By repressing the <F9> key the shapes can be improved.
Despite using 3000 replications (simulation of 3000 different
scenarios) the Excel graphs may vary to some extent, although
the out-data are fairly consistent.
The distributions shown in the sheet "In" have properties
listed in this table in the sheet "Data":
The sheet "Data" also has a table on the dependent data coming
out of the calculations:
Out-data are revealed in charts in the sheet "Out":
In the lower right-hand corner of this screenshot, a table showing the 95% confidence intervals is found. Inturpitation of these data may be difficult, but the user finds support below the table:
In the sheets "Profit" and "Break even" charts over cumulative probability on the according output variables are found:
Back to the calculation example, all in all the data generated by YourSim provides Brad with a less naiv analysis of his bread project than the traditional methods. The following conclutions can be drawn, given that the presumptions he made were reliable:
This is all due to the presumed market situation and the choice of alternative 1 under "Competition and organisation" in YourSim. As said earlier, other market scenarios may call for other presumptions, making the user choose one of the other alternatives. These generate different data, shown in the tables below:
Limitations in YourSim and Excel
Alternative 5 generates the largest spread in profit, as it allows data combinations of all kinds, also great demand with high unit price and low costs, and the total opposite as well. Yet, the upper and lower limits for profit are not even close to the theoretical BC and WC scenarios in chapter B above. This means that is highly unlikely that such extreme scenarios may occur. The chance (probability value) is less than 1/3000, since not one out of 3000 simulations was near neither WC (loss of 44,000) nor BC (profit of 92,500), which also illustrates that Monte Carlo simulation brings us closer to the truth than the traditional approach, which only describes the theoretical extremes.
YourSim was made for Excel 97 and is thus meant to work on soft- and hardware regarded as pretty old in the computer world of today. Spreadsheet models of YourSimís size lean heavily on system performance, so there are always compromises to be made regarding the number of simulations and how many variables, charts and tables should be generated.
A file can not be very big before it becomes sluggish to download on the internet. YourSim is a compromise between size and performance also regarding the deployment aspect.
Being based on Excel, YourSim depends of the performance of the different versions of the spreadsheet software and the interface they provide for the user. Diagrams and charts may appear strange sometimes.
This version of YourSim deals with only one product to sell. In the real world though, businesses usually lean on a wide number of products and services.
Although 3,000 replications is more than sufficient to generate reliable output data from a Monte Carlo simulation, results differ slightly from time to time when you press <F9> to recalculate. This is the way it should be, and even if the model used 100,000 replications the numbers would change each time. That is the nature of randomness. The consistency of the output should however be good enough to provide the user with a reliable image of the risk involved, given that the data going into the model are reliable.
Sometimes the use of normal distributions in one or more of the four in-data (independent) variables cause strange results, e.g. negative break even, which is impossible to achieve both in theory and reality. That is because this distribution has tails going infinitely out to each side, meaning it may contain negative values. This in turn may put negative values into the break even calculation, which comes up with a negative value because either the divisor or the dividend becomes negative (Break even = TFC/(SPU-VCU)), that is
Tips and advice
(Back to main page)