Part 1: Simulation Modeling w/ Built in Excel Tools

1. Part 1: Simulation Modeling w/ Built in Excel Tools

2. Calendars Demanded 100 150 200 250 300 Probability 0.30 0.20 0.30 0.15 0.05 Walton Bookstore • In August, Walton Bookstore must decide how many of next year’s 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 InputsWalton Bookstore 1 Note Named Cells • Constant Inputs (No Uncertainty): • Unit Cost (B4):$7.50 • Unit Price (B5):$10.00 • Unit Refund (B6): $2.50 • Order Quantity (B9): 200 • Random Inputs (Probability Distribution): • (D5):0 • (D6:D9):=D5+F5 • Random # (B19):=Rand() Excel Math&Trig function • Demand (C19):=VLOOKUP(B19,Lookup,2) • Excel Lookup&Reference function

4. Step 2: Build Basic ModelLogic to Convert Inputs into Outputs • Revenue (D19): =UnitPrice*MIN(C19,OrderQuan) • Min is Excel Statistical function • Cost (E19): =UnitCost*OrderQuan • Refund (F19): =UnitRefund*MAX(OrderQuan-C19,0) • Max is Excel Statistical function • Profit (G19): =D19-E19+F19 • Copy (B19:G19) to (B19:G1018) • Name (G19:G1018) “Profits”

5. Step 3: Create Summary StatisticsWalton 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): =AvgProfit+NORMSINV(0.975) *StdevProfit/SQRT(1000)

6. Step 4: Determine the “Best” Order QuantityOne-Way Data Table MethodWalton Bookstore 1 • Identify Table Output (B1022): • =AvgProfit • Select range (A1022:B1031) • Select: Data + What If Analysis + DataTable • Set the Column Input cell toB9

7. Step 5: Graph the ResultsWalton Bookstore 1 • Select Insert + Column Chart + Clustered Column • Choose Select Data • Add Series (Series Name: $B$1021, Series Values $B$1023:$B$1031) • Edit Horizontal Category Axis Labels (Label Range $A$1023:$A$1031)

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 A23:F1023 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 @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 year’s demand for calendars. • Walton wants to use this probability distribution, together with @Risk, to simulate the profit for any particular order quantity. • Walton eventually wants to find the “best” order quantity. MyWalton4.xls

12. Solution ApproachWalton Bookstore 4 • Use BestFit to identify demand probability distribution. • Use @Risk to Simulate 1000 runs for each potential order quantity. • Use @Risk RiskSimTable function to determine the “best” order quantity. • (does the work of the Data Table)

13. Opening an Existing @Risk File • Open @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 year’s calendars. • To select a probability distribution to match the histogram well, we can use @Risk’s fitting ability.

15. 3. Copy and Paste Data into the FitTabFitting a Probability Distribution • Click on the “Show Excel Window” button • Select the range A7:A121. • Click on the copy button. • Click on the “Show @RISK-Model window” button. • Select Edit + Paste from the Menu Bar

16. 4. Select Candidate DistributionsFitting 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 FittingFitting 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 ApproachWalton Bookstore 4 • Use BestFit to identify demand probability distribution. • Use @Risk to Simulate 1000 runs for each potential order quantity. • Use @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 @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 @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 ModelCreating the @Risk Simulation Model • Revenue (B13): • =UnitPrice*MIN(OrderQuan,Demand). • Cost (C13): • =OrderQuan*UnitCost • Refund (D13): • =UnitRefund*MAX(OrderQuan-Demand,0) Still the Hardest Part and the Heart of Simulation

21. Step 3: Identify the Output Cell(s)Creating the @Risk Simulation Model • In cell E13 enter the formula for Profit: • =B13+D13-C13 • Designate cell E13 as an @Risk output cell by clicking on the • the Add Output Cell button on the @Risk toolbar. • =RiskOutput() + B13+D13-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 • @Risk toolbar to check the list at any time.

22. Step 4: Create Summary Statistics on the Output Cell(s)Creating the @Risk Simulation Model • In cell B16, use the @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 SettingsCreating the @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 SettingsCreating the @Risk Simulation Model • Click on the “Report Settings” button. • For At the End of Each @RISK Simulation: • Check Show Interactive @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 @Risk SimulationCreating the @Risk Simulation Model • To run the simulation, Click on the “Start Simulation” button. • In the @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 OutputWalton Bookstore 4 @Risk generates a large number of output measures. • Summary Report. Assuming that the top box was checked in the @Risk Reports dialog box, we are immediately transferred to the @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: Insert+Graph+Histogram Note the 27.4% chance of losing money

29. Solution ApproachWalton Bookstore • Use BestFit to identify demand probability distribution. • Use @Risk to Simulate 1000 runs for each potential order quantity. • Use @Risk RiskSimTable function to determine the “best” order quantity. • (does the work of the Data Table)

30. Using RISKSIMTABLEWalton Bookstore 5 • Walton’s 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 @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 @RISK function RISKSIMTABLE( ). MyWalton5.xls

31. Step 3: Identify Output Cell(s): (E13): =B13-C13+D13 Click:=RiskOutput() + B13-C13+D13 @Risk SimulationWalton Bookstore 5 Step 1: Identify Input Cell(s): (A13): =ROUND(RiskNormal(MeanDem,StdevDem),0) Step 2: Build the Basic Model: (D9:L9): add 9 order quantities 100, 125, 150, …, 300 Step 4: Instead of entering a number in cell B9, enter =RiskSimtable(OrderQuanList) Make sure cells D9:L9 are named OrderQuanList

32. Step 5: Specify the Simulation SettingsWalton 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 SettingsWalton Bookstore 5 • Click on the “Report Settings” button. • For At the End of Each @RISK Simulation: • Check Show Interactive @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 @Risk SimulationWalton Bookstore 5 • To run the simulation, Click on the “Start Simulation” button. • In the @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 year’s 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 Walton’s supplier can supply is uncertain and is modeled with a triangular distribution. It’s 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: @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 customer’s promised delivery date of December 1, 2000. • There are three sources of uncertainty that make planning difficult.

38. Distribution of Days to Complete a Batch Days 5 6 7 8 9 10 11 Probability 0.05 0.10 0.20 0.30 0.20 0.10 0.05 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. The formula means: IF Enough? = Yes or is blank THEN Leave blank which acts as 0 ELSE RiskDiscrete(Day, Probs)) Building the Basic ModelWozac Drug Company • Batch Index: Limit of 25 by trial & error. Big enough. • Days (for this batch):(B25:B48):=IF(OR(F24=“Yes”,F24=“”),” “,RiskDiscrete(Day, Probs)) • Batch Yield: (C25:C48): • =IF(OR(F24=“Yes”,F24=“”),” “,RiskTriang($D$19,$E$19,$F$19)) • Pass Inspection? (D25:D48): =IF(OR(F24=“Yes”,F24=“”),” “,IF(Rand()<PrPass,”Yes”,”No”)) RiskDiscrete & RiskTriang are @Risk Distrib. functions

41. CumYield (cumulative usable product) (E25:E48): =IF(OR(F24=“Yes”,F24=“”),” “,IF(D25=“Yes”,C25+E24,E24)) IF Enough? = Yes or is blank Leave blank which acts as 0 THEN IF this batch passed ELSE Then Add this batch to sum Else Use previous sum Is the Order Filled?Building the Basic Model Col. E&F • Enough? (Is the order filled) (F25:F48): =IF(OR(F24=“Yes”,F24=“”),” “,IF(E25>=AmtReqd,”Yes”,”Not yet”)) IF Enough? = Yes or is blank Leave blank which acts as 0 THEN IFCumYield>= cell B5 ELSE Then Yes the order is filled Else No, we must do next row

42. Run Summary MeasuresBuilding the Basic Model • Batches required (I23): =COUNT(B24:B48) • (count the cells that are not blank) • Days to complete (I24): =SUM(B24:B48) • (blanks count as 0) • Day to start (I25): =DueDate-DaysReqd • Cell formatted for Date • Assumes 7 day production week I23 & I24 are @Risk Output cells, but we’ll handle that later

43. @Risk Summary MeasuresWozac Drug Company For 1,000 runs, we want @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(DaysReqd,0.05) • 95th perc days reqd (I34): =RiskPercentile(DaysReqd,0.95) • Prob of meeting any given due date (I37) : =RiskTarget(DaysReqd,DueDate-H37) RiskMax, RiskMean, etc., are @Risk Statistics functions

44. Identify Output CellsWozac Drug Company • Select “Batches required”, cell I23 • Click on the “Add Output” button. =RiskOutput()+COUNT(B24:B48) • Select “Days to complete”, cell I24 • Click on the “Add Output” button. =RiskOutput()+ SUM(B24:B48)

45. Specify the Simulation SettingsWozac 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 SettingsWozac Drug Company • Click on the “Report Settings” button. • For At the End of Each @RISK Simulation: • Check Show Interactive @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 @Risk SimulationWozac Drug Company • To run the simulation, Click on the “Start Simulation” button. • In the @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 @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 SimTex’s competition. • SimTex has identified a number of key inputs that will affect Biathnon’s future profitability:

50. Key Inputs Affecting ProfitabilitySIMTEX 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.