An example case, "Brad's bread"    (Back to main page)


    (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:



"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:

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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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:




E)  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 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:

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.



F)  Limitations in YourSim and Excel

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



G.  Tips and advice

 


(Back to main page)