Tutorial 3a - Simulating Profit of the e-Commerce Plan
Make sure that XLSim is loaded before proceeding.
- Open the
file PLAN.xls and investigate the formulas, which are annotated on the
right side of the sheet.
- We will
model uncertain demand as a normal distribution. Since the average or best
guess in this case is 2,000,000, that will be the mean of the
distribution. Because we are 95% confident that demand is within 1,000,000
either side of 2,000,000, we will use a standard deviation of 500,000 (see
section II.) Because this model has been previously set up you can simply use the Simulate Thaw command to reactivate the random number generator in cell B8. However, to accomplish this from scratch as you will need to do with your own models, place the cursor in cell B8 and click on
the function wizard . The function menu should appear as shown below.
Statistical from the left hand menu and gen_Normal from the right hand
menu as shown, then click OK.
- Fill in a
mean of 2000000 and standard deviation of 500000 then click OK. Your
worksheet should appear as shown below.
- Press the F9
key a few times to make sure the random number generator is working. This
is a great way to get an intuitive feeling of the uncertainty in inherent
in your business plan.
- Invoke the
Simulate, Simulation command. The Simulation Settings dialog box will
- Click the
Add Outputs button. Specify cell B16 as the output cell and
"Profit" as the output name as shown below, then click OK.
- Specify the number of trials on the Simulation Settings box, then click OK.
- The statistics of the simulation appear in
Simstats.xls. This indicates that the average profit is only around
$200,000, roughly half the profit of $400,000 associated with the average
demand of 2,000,000 units.
What happened? The histogram helps explain what is
- Create a
histogram of profit with 5 bins and zero decimals as shown below.
This clearly shows that if demand were to deviate below the average of
2,000,000, that profit would decrease below $400,000. On the other hand,
if demand were to deviate above the average of 2,000,000 there is no
corresponding increase in profit because of the limited capacity of your
server. Thus any demand of 2,000,000 or more results in a profit of
exactly $400,000. This is evident as the tall bar at the right of the
What's the point?
The average demand of 2,000,000 resulted in a profit
that was NOT the average profit. The answer to Puzzle 3 is c, $400,000 is the
maximum possible profit, not the average.
More generally we can say that:
of uncertain inputs don't always* result in average
This is the strong
The Flaw of
In the example above the average output was lower
than that associated with the average input, but there are examples where it
goes the other way. This is subject of what is known by statisticians as Jensen's Inequality.
* The only time that
average inputs are guaranteed to result in average outputs is when the spreadsheet
model is linear in all uncertain variables. Even if you know what
linear means, you would be hard pressed to tell if a large spreadsheet model
had this property. The best advice is to steer clear of average inputs, or point
estimates, as they are called euphemistically.
- Take a look at the cumulative
graph of profit as shown below. Notice that there is just over a 20%
chance of losing money. Also, there is a 5% chance of losing about
$400,000. That is, the Value at Risk at 5% is $400,000. This
can also be confirmed in the percentiles section of Simstats.xls.
- Save the
file with the gen_Normal formula in the demand cell, as you will use it in
the next tutorial.
A Sobering Example of the Flaw of Averages
taken from Dr. Savage's INSIGHT.xla.
Consider the state of a drunk, wandering around on a busy highway.
His average position is the centerline, so........
(Illustration from "INSIGHT.xla: Business Analysis Software
for Microsoft Excel,"
1st edition, by S.L. Savage, copyright 1998.
Reproduced with permission of Brooks/Cole Publishing,
a division of
International Thomson Publishing. FAX 800-730-2215.)
e-Commerce example with Optimal Investment
In the plan discussed above, the profit based on average
demand was $400,000, while the average profit turned out
to be only about $200,000. Furthermore there was a 5% chance of losing $400,000.
This hardly looks as attractive as it did at first. But can we do better? The
answer is yes. By experimenting with different amounts to invest we will find
that there is an optimal strategy. In tutorial 3b you will use parameterized
simulation to run 5 different experiments for different investment amounts.
Before proceeding to Tutorial 3b, try Puzzle 4.
PUZZLE 4: Previously you planned to invest $1,600,000 because this
provided a capacity equal to average demand. Should you increase or decrease your
investment, or leave it the same?
Tutorial 3b - Using Parameterized Simulation to Optimize Your Investment
- Make sure
that your simulation software is loaded and retrieve Plan.xls. Press the
F9 key a few times to check that the random number generator in cell B8 is
- Next we
will place 5 investment amounts to test in blank cells, for example,
F5:F9. Try $1,000,000, $1,200,000, $1,400,000, $1,600,000 and $1,800,000.
- Invoke the Simulate,
Parameterized Sim command. The Parameterized Simulation Settings dialog
box will appear. Fill in the appropriate values as shown below.
- B16, Profit, is the
- Specify the
number of trials.
- The Seed, discussed
in more detail below should be set to any positive integer.
- B4, the amount
invested is the parameter to be experimented with.
- The suggested
Parameter Name is Investment.
- The Parameter Values
are in F5:F9.
simulation results shown below will vary depending on what seed you used.
If you used the number 2 and 1000 trials, you should get identical results.
Notice that the maximum average profit of $255,000
occurs at an investment of $1,200,000. This is more than 25% better than the
average profit associated with the original investment of $1,600,000. But this
is less than half the story as you will see in the next step.
- Invoke the
Simulate, Common Graphs command. Click Select All on the Outputs tab so
that your graph will include all the possible investment levels. Then move
to the Series tab and click the Series Graph button. You should see the
graph below. The pink line shows the average profit for each investment
level, while the dark blue line shows the 95th percentile.
But the big news is the yellow line that shows the
5th percentile, or equivalently the 5% Value at Risk. This shows that at the
original investment level of $1,600,000 there was a 5% chance of losing over
$400,000. By reducing the investment to $1,200,000 we not only increased
average profit, we reduced the 5% VaR to less than $50,000!
Look back at the Percentiles section of the
Statistics results sheet to see the VaR values at different levels for each of