- The traditional approach
- The BC/WC approach
- The pitfalls of a traditional approach
- Correlation between price, costs and demand
- The YourSim approach of calculating
- Limitations in YourSim and Excel
- Tips and advice

(If a picture is blurred, click on it to watch
it in full size)

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?

** **

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:

"Mean" is a statistic term for the average of values in a distribution. (Also check the mean example page.)

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:

- If there
*are other substitutes*in the market, there will be price competition. For each supplier goes that the lower the unit price is, the higher the demand becomes, if other factors (e.g. marketing) are constant. - If there
*are no substitutes*, the supplier is a monopolist and can set the price of the product freely. This may be the case if the product is unique, e.g. a new and very useful invention. Still, the demand will possibly rise if the price decreases, but this will not have a significant effect compared to in a competitive situation. - If the organisation is located all in the
*same*market (e.g. locally), variable and fixed costs will correlate. - If the organisation is located in
*different*markets (e.g. with the sales administration in the USA and supplies from a company in China), there will be no correlation between fixed (adressed to the administration) and the variable (adressed to the production) costs.

According to these market and organisational factors, five scenarios are possible in the model:

- If a business organisation is locally situated and supplies the local market with products that compete with other local products (because they are equally important to the customer), it has to compete on price. In such a case (like Brad’s) demand (units sold) correlate negatively with unit price. Fixed costs (e.g. wages) tend to rise and fall with variable costs in the same market.
- If a locally situated business has a unique product to which there are no substitutes, it may set the unit price according to fluctuations in costs (which is not automatically the case in a more competitive environment). This means correlation between unit price and variable costs. It is also presumed that changes in unit price will have no effect on demand, due to lack of competition. Being locally situated also makes it reasonable to presume that fixed and variable costs correlate.
- This is like number 1 competition-wise, but for an organisation that is spread (e.g. over different countries, regions or market cultures). Costs are also spread the same way. Then there is no reason to presume correlation between fixed and variable costs.
- This alternative is like number 2 competition-wise, and like 3 in organisation. It may be an international company with a unique product, or someone who buys bits and parts from all over the world to combine it into a rare product.
- This alternative presumes no correlation among any of the four factors going into the model. This may be advisable when planning is at an early stage, e.g. when location is not decided, or when market research still gives an incomplete insight in the competition that may or may not exist. Simulations under this scenario use all thinkable combinations of the given in-data and generate out-data of the largest spread.

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
description above:

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
input table:

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:

- Although it is likely that this becomes a profitable project, there still is a 24% chance of a loss (if 4 similar projects were launched under the same conditions, 1 of them would not reach break even, or generate enough income to cover the running costs).
- He may lose maximum $28,332 if all conditions are at their worst simultaneously, but it is only 5% probable that he will lose more than $11,698, if anything at all.
- Most likely profit will be somewhere around $14,000 (the expected value is $13,797). However, the chart over profit distribution (in the sheet "Out") shows that on both sides of the mean the graph is not very steep, e.g. other values may be almost as probable as the mean.

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:

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.

System limitations

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.

Internet boundaries

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.

Software boundaries

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.

Model limitations

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.

Distribution problems

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

- if TFC is normally distributed, or
- if SPU is normally distributed, or
- if SPU and VCU have overlapping intervals, causing SPU sometimes to be smaller than VCU, which returns a negative value in the divisor of the break even calculation.

- Perform some kind of market analysis before you make up your mind what to put into "Sold units during period" (demand) and "Sales price per unit (SPU)". It lies outside the scope of this article to go into this issue, but you should definitely dive into the subject.

- Do some precalculations before you put data into VCU and TFC. These to variables contain several factors that in turn need to be calculated first etc.

- Play with different numbers. This will broaden your mind about the project, i.e. by performing what-if analysis or sensitivity analysis. Doing the latter, check how sensitive or vulnerable profit is to changes in the different input data.

- Recalculate several times with the same in-data. If values coming out vary much, you can note the different values and then e.g. calculate the average.

- If you want to sell more than one product, you will have to share the fixed costs between these in a sensible way. You may also use average values for input. A future version of YourSim may have the possibility of calculating more than one product into the simulations.

- Accountants distinguish between profit and cash
flow. In fact you may have gained profit although the cash
flow is negative, i.e. if you invest in something that
gaines value after you bought it. However, too little cash
may make your business bankrupt nevertheless! Cash is the
blood in the veins of your business. To analyze
*cash build-up*in a simple way, redefine the total fixed costs to be total fixed cash flow. You do this by letting out depreciation from the TFC input and letting in downpayment on loans. The loss probability is now the probability for running short of cash, not for losing value.

(Back to main page)