ORSA TAC

About This Presentation

Title:

ORSA TAC

Description:

w/ Built in Excel Tools Specify the Simulation Settings _at_Risk Sim - SIMTEX Prod. Dev. Click on the Simulations Settings button. Click on the Iterations ... – PowerPoint PPT presentation

Number of Views:174

Avg rating:3.0/5.0

Slides: 70

Provided by: ALMC1

Category:

more less

Transcript and Presenter's Notes

Title: ORSA TAC

1
Part 1 Simulation Modeling w/ Built in Excel
Tools
2
Walton Bookstore

In August, Walton Bookstore must decide how many
of next years nature calendars to order.
Each calendar costs the bookstore 7.50 and is
sold for 10.
After February 1 all unsold calendars are
returned to the publisher for a refund of 2.50
per calendar.
Walton believes that the number of calendars it
can sell by February 1 follows this probability
distribution.

MyWalton1.xls

Walton wants to simulate 1000 replications for
order quantities
100, 125, 150, , 300 to determine the
quantity to order so as to maximize the expected
profit from calendar sales.

3
Step 1 Identify Inputs Walton Bookstore 1

Constant Inputs (No Uncertainty)
Unit Cost (B4) 7.50
Unit Price (B5) 10.00
Unit Refund (B6) 2.50
Order Quantity (B9) 200

Note Named Cells

Random Inputs (Probability Distribution)
(D5) 0
(D6D9) D5F5
Random (B19) Rand() Excel
MathTrig function
Demand (C19) VLOOKUP(B19,Lookup,2)
Excel
LookupReference function

4
Step 2 Build Basic Model Logic to Convert
Inputs into Outputs

Revenue (D19) UnitPriceMIN(C19,OrderQuan)
Min
is Excel Statistical function

Cost (E19) UnitCostOrderQuan

Refund (F19) UnitRefundMAX(OrderQuan-C19,0)
Max
is Excel Statistical function

Profit (G19) D19-E19F19

Copy (B19G19) to (B19G1018)

Name (G19G1018) Profits

5
Step 3 Create Summary Statistics Walton
Bookstore 1

Average Profit (B12) AVERAGE(Profits)

Stdev Profit (B13) STDEV(Profits)

Minimum Profit (B14) MIN(Profits)

Maximum Profit (B15) MAX(Profits)

95 Confidence Interval

Lower limit (E12) AvgProfit-NORMSINV(0.975)

StdevProfit/SQRT(1000)
Upper limit (E13) AvgProfitNORMSINV(0.975)

StdevProfit/SQRT(1000)
6
Step 4 Determine the Best Order Quantity
One-Way Data Table MethodWalton Bookstore 1

Identify Table Output (B1022)
AvgProfit

Select range (A1022B1031)

Select Data What If Analysis DataTable

Set the Column Input cell to B9

7
Step 5 Graph the Results Walton Bookstore 1

Select Insert Column Chart Clustered Column
Choose Select Data
Add Series (Series Name B1021, Series Values
B1023B1031)
Edit Horizontal Category Axis Labels (Label Range
A1023A1031)

8
Two-Way Data Table MethodWalton3.xls

Note the change in the basic model.

MyWalton3.xls
Demand (A19) VLOOKUP(RAND(),Lookup,2).
9
Create the Two-Way Data TableWalton3.xls

Identify Table Output (A23) Profit
Select range A23F1023 for the Data Table
Select Data What If Analysis DataTable
For the Row input cell enter B9
For the Column Input cell enter G23 or any
other blank cell you choose.

10
Part 2 Intro to Simulation Modeling with _at_Risk
11
Walton Bookstore RevisitedWalton4.xls

Recall that Walton Bookstore buys calendars for
7.50, sells them at a regular price of 10, and
gets a refund for all calendars that cannot be
sold.
The company does not know exactly how many
calendars its customers will demand, but it does
have historical data on demands for similar
calendars in previous years. Walton wants to use
these historical data to determine a reasonable
probability distribution for next years demand
for calendars.
Walton wants to use this probability
distribution, together with _at_Risk, to simulate
the profit for any particular order quantity.
Walton eventually wants to find the best order
quantity.

MyWalton4.xls
12
Solution Approach Walton Bookstore 4

Use BestFit to identify demand probability
distribution.
Use _at_Risk to Simulate 1000 runs for each
potential order quantity.
Use _at_Risk RiskSimTable function to determine the
best order quantity.
(does the work of the Data Table)

13
Opening an Existing _at_Risk File

Open _at_Risk for Excel
Open the file MyWalton4.xls
Use File Save As to save this file under a
different name (such as Class MyWalton 4)

14
Fitting a Probability Distribution

The historical demand data is on the Data tab of
Walton4.
The hard part is to find historic data that is
appropriate for estimating the probability
distribution of demand for next years calendars.
To select a probability distribution to match
the histogram well, we can use _at_Risks fitting
ability.

15
3. Copy and Paste Data into the FitTab Fitting a
Probability Distribution

Click on the Show Excel Window button
Select the range A7A121.
Click on the copy button.
Click on the Show _at_RISK-Model window button.
Select Edit Paste from the Menu Bar

16
4. Select Candidate Distributions Fitting a
Probability Distribution
To see the candidate probability distributions
from which to choose, click on the
Specify-Distributions-to-Fit button from the tool
bar.

You can check as many of the candidates as
you like.
Stick with familiar
distributions such as the
normal and triangular.
Clicked on OK which
accepts the defaults .

17
5. Do the Fitting Fitting a Probability
Distribution
Click on the Fit-Distributions-to-Input-Data
button in the tool bar .

Note the distributions are
ranked by the Chi-Sq test.
Change Rank by to K-S.
The Weibull is better than
the Normal.
Change Rank by to A-D.
The Normal is better than
the Weibull

For Normal ? ? 168.1, ? ? 57.6
18
Solution Approach Walton Bookstore 4

Use BestFit to identify demand probability
distribution.
Use _at_Risk to Simulate 1000 runs for each
potential order quantity.
Use _at_Risk RiskSimTable function to determine the
best order quantity.
(does the work of the Data Table)

19
Step 1 Identify the Input Cell(s) Creating the
_at_Risk Simulation Model

Enter the values for the mean and standard
deviation estimated by BestFit!
Mean 168.1 in cells E4
StDev 57.6 in cells E5.
In cell A13, use the _at_RISK
Distribution function
RiskNormal within the
Excel Math Trig function
ROUND to enter
the formula

Round(RiskNormal (MeanDem,StdevDem).
20
Step 2 Build the Basic Model Creating the _at_Risk
Simulation Model

Revenue (B13)
UnitPriceMIN(OrderQuan,Demand).
Cost (C13)
OrderQuanUnitCost
Refund (D13)
UnitRefundMAX(OrderQuan-Demand,0)

Still the Hardest Part and the Heart of Simulation
21
Step 3 Identify the Output Cell(s) Creating the
_at_Risk Simulation Model

In cell E13 enter the formula for Profit
B13D13-C13
Designate cell E13 as an _at_Risk output cell by
clicking on the
the Add Output Cell button on the _at_Risk
toolbar.
RiskOutput() B13D13-C13
Any number of cells can be designated in this
way as output cells.
They are typically bottom line values of
primary interest.
Click on the Display List of Outputs Inputs
button on the
_at_Risk toolbar to check the list at any time.

22
Step 4 Create Summary Statistics on the Output
Cell(s) Creating the _at_Risk Simulation Model

In cell B16, use the _at_RISK Statistics function
RiskMin(Profit)
In cell B17, enter
RiskMax(Profit)
In cell B18, enter
RiskMean(Profit)
In cell B19, enter
RiskStdDev(Profit)

23
Step 5 Specify the Simulation Settings Creating
the _at_Risk Simulation Model

Click on the Simulations Settings button.
Click on the Iterations tab in the Simulation
Settings dialog box.
Set Iterations to 1000.
Set Simulations to 1.
Check Update Display.
Click on the Sampling tab in the Simulation
Settings dialog box.
Set Sampling Type to Latin Hypercube .
Set Standard Recalc to Monte Carlo .
Set Random Generator Seed to Choose Randomly .
Set Collect Distribution Samples to All .

24
Step 6 Specify the Report Settings Creating the
_at_Risk Simulation Model

Click on the Report Settings button.
For At the End of Each _at_RISK Simulation
Check Show Interactive _at_RISK
Results Window.
Check Generate Excel Reports
Selected Below.
For Excel Reports
Check Simulation Summary .
Check Detailed Statistics.
For Excel Reports
Check Active Workbook.

25
Step 6 Run the _at_Risk Simulation Creating the
_at_Risk Simulation Model

To run the simulation, Click on the Start
Simulation button.
In the _at_Risk Results window
To see Summary Statistics, use the Summary
Statistics Window button.
To see Detailed Statistics, use the Detailed
Statistics Window button.

26
Analyzing the Output Walton Bookstore 4

_at_Risk generates a large number of output
measures.

Summary Report. Assuming that the top box was
checked in the _at_Risk Reports dialog box, we are
immediately transferred to the _at_Risk Results
window. This window contains the summary results
shown here.
27
Detailed Statistics Analyzing the Output
All of the information in the Summary Report is
here, plus some.
28
Charts Analyzing the Output
To create a histogram of the 1,000 profits In
the left pane of the Results window, click on
Profits From the menu bar select
InsertGraphHistogram
Note the 27.4 chance of losing money
29
Solution Approach Walton Bookstore

Use BestFit to identify demand probability
distribution.
Use _at_Risk to Simulate 1000 runs for each
potential order quantity.
Use _at_Risk RiskSimTable function to determine the
best order quantity.
(does the work of the Data Table)

30
Using RISKSIMTABLEWalton Bookstore 5

Waltons ultimate goal is to choose an order
quantity that provides a large average profit.
We could rerun the simulation model several
times, each time with a different order quantity
in the OrderQuan cell, and compare the results.
The RISKSIMTABLE function in _at_Risk enables us to
obtain a fair comparison quickly and easily.
There are two modifications to the previous
model.
We will create a list of order quantities to
test.
Instead of entering a number in cell B9 (the
Order Quantity), we will use the _at_RISK function
RISKSIMTABLE( ).

MyWalton5.xls
31
_at_Risk Simulation Walton Bookstore 5
Step 1 Identify Input Cell(s) (A13)
ROUND(RiskNormal(MeanDem,StdevDem),0)
Step 2 Build the Basic Model (D9L9)
add 9 order quantities 100, 125, 150, , 300
Step 4 Instead of entering a number in cell B9,
enter RiskSimtable(OrderQuanList) Make sure
cells D9L9 are named OrderQuanList
32
Step 5 Specify the Simulation Settings Walton
Bookstore 5

Click on the Simulations Settings button.
Click on the Iterations tab in the Simulation
Settings dialog box.
Set Iterations to 1000.
Set Simulations to 9.
Check Update Display.
Click on the Sampling tab in the Simulation
Settings dialog box.
Set Sampling Type to Latin Hypercube .
Set Standard Recalc to Monte Carlo .
Set Random Generator Seed to Choose Randomly .
Set Collect Distribution Samples to All .

9 Order Quantities
33
Step 6 Specify the Report Settings Walton
Bookstore 5

Click on the Report Settings button.
For At the End of Each _at_RISK Simulation
Check Show Interactive _at_RISK
Results Window.
Check Generate Excel Reports
Selected Below.
For Excel Reports
Check Simulation Summary .
Check Detailed Statistics.
For Excel Reports
Check Active Workbook.

34
Step 6 Run the _at_Risk Simulation Walton
Bookstore 5

To run the simulation, Click on the Start
Simulation button.
In the _at_Risk Results window
To see Summary Statistics, use the Summary
Statistics Window button.
To see Detailed Statistics, use the Detailed
Statistics Window button.

35
Multiple Sources of UncertaintyWalton Bookstore 6

As in previous examples, Walton needs to place an
order for next years calendar. We continue to
assume that the calendars will sell for 10 and
customer demand for the calendars at this price
is normally distributed with mean 168.1 and
standard deviation 57.6. However, there are now
two other sources of uncertainty.
First, the maximum number of calendars Waltons
supplier can supply is uncertain and is modeled
with a triangular distribution. Its parameters
are 125, 250, and 200. Once Walton places an
order, the supplier will charge 7.50 per
calendar if he can supply the entire Walton
order. Otherwise, he will charge only 7.25 per
calendar.
Second, unsold calendars can no longer be
returned to the supplier for a refund. Instead,
Walton will put them on sale for 5 each after
Feb 1. At that price, Walton believes the demand
for leftover calendars is normally distributed
with mean 50 and standard deviation 10. Any
calendars still left over after March 1 will be
thrown away.
Walton plans to order 200 calendars and wants to
use simulation to analyze the resulting profit.

MyWalton6.xls
36
Part 3 _at_Risk Simulation Modeling An Example
37
Drug Production Model with Uncertain
YieldsTrying to Meet an Order Due Date at Wozac

Wozac is a drug manufacturing company. It has
recently accepted an order from its best customer
for 8,000 ounces of a new miracle drug, and wants
to plan its production schedule to meet the
customers promised delivery date of December 1,
2000.
There are three sources of uncertainty that make
planning difficult.

38
Sources of UncertaintyWozac Drug Company

First, the drug must be produced in batches, and
there is uncertainty about the time required to
produce a batch, which could be anywhere from 5
to 11 days. This uncertainty is described by the
discrete distribution of this table.

39
Sources of UncertaintyWozac Drug
CompanyContinued
My Wozac Drugs.xls (Incomplete)
Wozac Drugs.xls (Complete)

Second, the yield (usable quantity) from any
batch is uncertain. Based on historical data,
Wozac believes the yield can be modeled by a
triangular distribution with parameters 600,
1000, 1100.
Third, all batches must go through a rigorous
inspection once they are completed. The
probability that a typical batch passes this
inspection is only 0.8. Therefore, the
probability is 0.2 that the batch fails
inspection and none of it can be used to help
fill the order.

Wozac wants to use simulation to help decide how
many days prior to the due date it should begin
production.
40
Building the Basic Model Wozac Drug Company

Batch Index Limit of 25 by trial error. Big
enough.
Days (for this batch)(B25B48)
IF(OR(F24Yes,F24), ,RiskDiscrete(Day,
Probs))

Batch Yield (C25C48)

IF(OR(F24Yes,F24), ,RiskTriang(D19,E1
9,F19))

Pass Inspection? (D25D48)

IF(OR(F24Yes,F24), ,IF(Rand()ltPrPass,Yes
,No))
RiskDiscrete RiskTriang are _at_Risk Distrib.
functions
41
Is the Order Filled? Building the Basic Model
Col. EF

Enough? (Is the order filled) (F25F48)

IF(OR(F24Yes,F24), ,IF(E25gtAmtReqd,Yes
,Not yet))
Enough? Yes or is blank
IF
THEN
Leave blank which acts as 0
ELSE
IF CumYieldgt cell B5
Then Yes the order is filled
Else No, we must do next row
42
Run Summary Measures Building the Basic Model

Batches required (I23) COUNT(B24B48)
(count the cells that are not blank)

Days to complete (I24) SUM(B24B48)
(blanks count as 0)

Day to start (I25) DueDate-DaysReqd
Cell formatted for Date
Assumes 7 day production week

I23 I24 are _at_Risk Output cells, but well
handle that later
43
_at_Risk Summary MeasuresWozac Drug Company
For 1,000 runs, we want _at_Risk to Report

Max batches reqd (I28) RiskMax(I23)

How long does it take?
Avg days reqd (I30) Int(RiskMean(DaysReqd))
Min days reqd (I31) RiskMin(DaysReqd)
Max days reqd (I32) RiskMax(DaysReqd)
5th perc days reqd (I33) RiskPercentile(DaysReq
d,0.05)
95th perc days reqd (I34) RiskPercentile(DaysReq
d,0.95)

Prob of meeting any given due date (I37)

RiskTarget(DaysReqd,DueDate-H37)
RiskMax, RiskMean, etc., are _at_Risk Statistics
functions
44
Identify Output Cells Wozac Drug Company

Select Batches required, cell I23
Click on the Add Output button.

RiskOutput()COUNT(B24B48)

Select Days to complete, cell I24
Click on the Add Output button.

RiskOutput() SUM(B24B48)
45
Specify the Simulation Settings Wozac Drug
Company

Click on the Simulations Settings button.
Click on the Iterations tab in the Simulation
Settings dialog box.
Set Iterations to 1000.
Set Simulations to 1.
Check Update Display.
Click on the Sampling tab in the Simulation
Settings dialog box.
Set Sampling Type to Latin Hypercube .
Set Standard Recalc to Monte Carlo .
Set Random Generator Seed to Choose Randomly .
Set Collect Distribution Samples to All .

46
Specify the Report Settings Wozac Drug Company

Click on the Report Settings button.
For At the End of Each _at_RISK Simulation
Check Show Interactive _at_RISK
Results Window.
Check Generate Excel Reports
Selected Below.
For Excel Reports
Check Simulation Summary .
Check Detailed Statistics.
For Excel Reports
Check Active Workbook.

47
Run the _at_Risk Simulation Wozac Drug Company

To run the simulation, Click on the Start
Simulation button.
In the _at_Risk Results window
To see Summary Statistics, use the Summary
Statistics Window button.
To see Detailed Statistics, use the Detailed
Statistics Window button.

48
Part 4 Using TopRank with _at_Risk for Powerful
Modeling
49
New Product DevelopmentAt SIMTEX

SimTex, a pharmaceutical company, is in the early
stages of developing a new drug called Biathnon.
As with most new drugs, the future of Biathnon is
highly uncertain. For example, its introduction
into the market could be delayed, pending tests
by the FDA. Also, its market could be diminished
by a potential rival product from SimTexs
competition.
SimTex has identified a number of key inputs
that will affect Biathnons future profitability

50
Key Inputs Affecting Profitability SIMTEX
Product Development

Number of years after product is developed until
it is produced (due to potential FDA delays).
Number of years for which the product sells.
Initial cost incurred in developing the product.
Salvage value obtained from equipment after
production of the product has been discontinued.
Fixed production cost incurred during years in
which the product is manufactured.
Unit cost of producing the product.
Unit price for the product.
Initial demand for the product during first year
it is sold.
Annual percentage growth in demand for the
product.
Percentage of demand for the product that is lost
to the competition.
Discount rate used to discount cash flows from
the product.

51
Key Questions SIMTEX Product Development

How do changes in the inputs affect the key
output, the Net Present Value of Biathnon over
its lifetime?
Which inputs have a major affect on the Net
Present Value of Biathnon over its lifetime?
How can SimTex use TopRank and _at_Risk to analyze
this problem?

SimTex1.xls
52
Understanding SIMTEX1 Cell B29
The Key to understanding SIMTEX1 is the TIMING in
Row 29
(Last Prod Yr)
B13
B13B14
B29IF(AND(B27gtDelay,B27lt(DelayLife)),
Yes,No)
53
Financial Formulas (C30C36) Understanding
SIMTEX1
C30 Fixed Cost
54
Financial Formulas (continued)
C36 Net Profit -C28-C30-C33C34C35
55
Calculate the Net Present Value
B38NPV(DiscRate,C36AF36)B36
Or
B38NPV(DiscRate,Profits)B36
Where
Profits C36AF36
56
Evaluating Profitability SIMTEX Product
Development

Now that the model is developed we can
Use trial and error to see how the NPV reacts to
changes in the inputs.
Use data tables to see how the NPV reacts to
changes in the inputs.
However, TopRank does this easily.

57
Evaluating Uncertainty SIMTEX Product Development

Change the input section to reflect uncertainty

SimTex2.xls
58
TopRanks RISKVARY function

Modify the range B13B23 to use
RISKVARY(Expected value, low range, high range,
Range type, Steps)

where Expected value is the base case Low
range is the smallest possible value for the
input High range is the largest possible value
for the input Range type is 0,1, or 2 and
determines the way minimum and maximum should be
entered Steps is the number of values from
minimum to maximum to use for this input
59
Using TopRank

To use TopRank, we proceed in three steps very
much like in _at_Risk
use the Change Settings button
use the Add Output Cells button to select one or
more output cells
use the Run What-if Analysis button to perform
the calculations.

60
Using TopRank Step 1Avoid Lots of Results You
Probably Dont Want

Click on the Input ID tab

Then UNCHECK the Automatically Insert AutoVary
Functions box.

Click on OK

61
Using TopRank Step 2Identify Output Cells

Select the NPV cell (B38)
Click on the Add Output Cells button.

62
Using TopRank Step 3Run the Program

Finally, run the analysis by clicking on the Run
What-if Analysis button.

TopRank then varies each input cell from its
minimum to maximum, using the number of steps you
specified and keeping the other inputs at their
base levels, and keeps track of all the NPVs.
63
Tornado Charts Interpreting TopRank Results

Perhaps the best way to understand TopRank
results is with a tornado chart.
To create a tornado chart
Click on the Graph button in the TopRank screen.
Choose tornado

64
_at_Risk Simulation SIMTEX Product Development

We will run an _at_Risk simulation to estimate the
distribution of NPV earned by Biathnon.
We will keep all inputs other than the five key
inputs fixed at their base values.
We will use a triangular distribution for each of
the random inputs product lifetime, unit price,
unit cost and initial demand
We will vary the discount rate systematically
with the RISKSIMTABLE function.

65
Modify the TopRank Model _at_Risk Sim - SIMTEX
Prod. Dev.
SimTex3.xls