The purpose of this assignment is to use analytics techniques toanalyze a case problem.

Part 1

Read Case Study Case 15.2 “Ebony Bath Soap” from thetextbook, and then complete the following items.

1. For Questions 1 and 2 of the case, use the PalisadeDecisionTools Excel software to set up a simulation model and run asimulation with 500 trials for the case. Ensure that all Palisadesoftware output is included in your files and that only one Excelfile is open when running a simulation. Use the "Topic 3 CaseStudy Template" file as a starting point.Hint: The RiskSimtable function was be helpful forrunning the simulations.
2. Respond to Question 3 as writtenin the problem. Ignore the confidence interval portion of thequestion.
3. Respond to Question 4 as written in theproblem.

To receive full credit on the assignment, complete the following. ALL WORK MUST BE ORIGINAL!!!

1. Ensure that the Palisade software output is included with yoursubmission.
2. Ensure that Excel files include the associatedcell functions and/or formulas if functions and/or formulas areused.
3. Include a written response to all narrative questionspresented in the problem by placing it in the associated Excelfile.
4. Include screenshots of all simulation distributionresults for output variables.
5. Place each problem in its ownExcel file. Ensure that your first and last name are in your Excelfile names.

Part 2

In a 500-750-word summary to company management, address thefollowing. Include relevant charts and graphs within your summary, as needed.

1. Describe the case specific business requirements and how theycan be communicated across all levels of the organization.
2. Based on the simulation results, discuss the Annual Cost outputstatistical distributions. Assume that your audience as minimalbackground in statistics.
3. Discuss which Annual Cost outputprobability distribution has the most dispersion, and explain whythis is so.
4. Explain the descriptive, predictive, andprescriptive analytics that have been used to formulate thesolutions to the business needs.
5. Based on the Annual Costoutput statistical distributions and other information gleaned fromyour analysis, discuss the specific prescribed course of action youwould recommend to company management and justify yourrecommendations. Include discussion of how the proposed analyticssolutions can optimize organizational performance andeffectiveness.

While APA style is not required for the body of this assignment,solid academic writing is expected, and documentation of sourcesshould be presented using APA formatting guidelines, which can befound in the APA Style Guide, located in the Student Success Center.

This assignment uses a rubric. Please review the rubric prior tobeginning the assignment to become familiar with the expectations forsuccessful completion.

You are required to submit this assignment to LopesWrite.

15.2SIMULATING WITH EXCEL ONLY AT WALTON BOOKSTORE Recall that WaltonBookstore must decide how many of next year’s nature calendars to order.Each calendar costs the bookstore $7.50 and sells for $10. AfterJanuary 1, all unsold calendars will be returned to the publisher for arefund of $2.50 per calendar. In this version, we assume that demand forcalendars (at the full price) is given by the probability distributionshown in Table 15.1. Walton wants to develop a simulation model to helpit decide how many calendars to order.Objective To use built-in Exceltools—including the RAND function and data tables, but no add-ins—tosimulate profit for several order quantities and ultimately choose the“best” order quantity.Where Do the Numbers Come From?Where Do theNumbers Come From?The numbers in Table 15.1 are the key to thesimulation model. They are discussed in more detail next.SolutionWefirst discuss the probability distribution in Table 15.1. It is adiscrete distribution with only five possible values: 100, 150, 200,250, and 300. In reality, it is clear that other values of demand arepossible. For example, there could be demand for exactly 187 calendars.In spite of its apparent lack of realism, we use this discretedistribution for two reasons. First, Table 15.1 Probability Distributionof Demand for Walton ExampleDemandProbability1000.301500.20 itssimplicity is a nice feature to get you started with simulationmodeling. Second, discrete distributions are often used in real businesssimulation models. Even though the discrete distribution is only anapproximation to reality, it can still provide important insights intothe actual problem.As for the probabilities listed in Table 15.1, theyare typically drawn from historical data or (if historical data arelacking) educated guesses. In this case, the manager of Walton Bookstorehas presumably looked at demands for calendars in previous years, andhe has used any information he has about the market for next year’scalendars to estimate, for example, that the probability of a demand for200 calendars is 0.30. The five probabilities in this table must sum tomustmust1. Beyond this requirement, they should be as reasonable andconsistent with reality as possible.It is important to realize that thisis really a decision problem under uncertainty. Walton must choose anorder quantity before knowing the demand for calendars. Unfortunately,Solver cannot be used because of the uncertainty.7 knowing the demandfor calendars. Unfortunately, knowing the demand for calendars.Unfortunately, Therefore, we develop a simulation model for any fixedorder quantity. Then we run this simulation model with various orderfixedfixedquantities to see which one appears to be best.Developing theSimulation ModelNow we discuss the ordering model. For any fixed orderquantity, we show how Excel can be used to simulate 1000 replications(or any other number of replications). Each replica-tion is anindependent replay of the events that occur. To illustrate, suppose youwant to simulate profit if Walton orders 200 calendars. Figure 15.26illustrates the results obtainedbysimulating 1000 independent replications for this order quantity. (Seethe file Ordering Calendars – Excel Only 1.xlsx.) Note that there aremany hidden rows in Figure 15.26. To develop this model, use thefollowing steps.1.Inputs. Enter the cost data in the range B4:B6, theprobability distribution of demand in the range E5:F9, and the proposedorder quantity, 200, in cell B9. Pay particular attention to the way theprobability distribution is entered (and compare to the Discrete sheetin the Probability Distributions.xlsx file). Columns E and F contain thepossible demand values and the probabilities from Table 15.1. It isalso necessary (see step 2 for the reasoning) to have the cumulativeprobabilities in column D. To obtain these, first enter the value 0 incell D5. Then enter the formula=F5+D5in cell D6 and copy it to the rangeD7:D9.2.Generate random demands. The key to the simulation is thegeneration of a cus-tomer demand in column C from a random numbergenerated by the RAND func-tion in column B and the probabilitydistribution of demand. Here is how it works. The interval from 0 to 1is split into five segments: 0.0 to 0.3 (length 0.3), 0.3 to 0.5(length0.2), 0.5 to 0.8 (length 0.3), 0.8 to 0.95 (length 0.15), and 0.95 to1.0 (length 0.05). Note that these lengths are the probabilities of thevarious demands. Then a demand is associated with each random number,depending on which interval the random number falls in. For example, if arandom number is 0.5279, this falls in the third interval, so it isassociated with the third possible demand value, 200.To implement thisprocedure, you use a VLOOKUP function based on the range D5:F9 (namedLookupTable). This table has the cumulative probabilities in column Dand the possible demand values in column E. In fact, the whole purposeof the cumulative probabilities in column D is to allow the use of theVLOOKUPfunction. To generate the simulated demands, enter theformula=VLOOKUP(RAND(),LookupTable,2)in cell C19. This formula comparesany RAND value to the values in D5:D9 and returns the appropriate demandfrom E5:E9. (In the file, you will note that randomcellsare colored green. This coloring convention is not required, but we useit con-sistently to identify the random cells.)This step is the key tothe simulation, so make sure you understand exactly what it entails. Therest is bookkeeping, as indicated in the following steps.3.Revenue.Once the demand is known, the number of calendars sold is the smaller ofthe demand and the order quantity. For example, if 150 calendars aredemanded, 150will be sold. But if 250 are demanded, only 200 can be sold(because Walton orders only 200). Therefore, to calculate the revenuein cell D19, enter theformula=Unit_price*MIN(C19,Order_quantity)4.Ordering cost. The cost ofordering the calendars does not depend on the demand; it is the unitcost multiplied by the number ordered. Calculate this cost in cell E19with the formula=Unit_cost*Order_quantity5.Refund. If the order quantityis greater than the demand, there is a refund of $2.50for each calendarleft over; otherwise, there is no refund. Therefore, calculate therefund in cell F19 with theformula=Unit_refund*MAX(Order_quantity-C19,0)Forexample, if demand is 150, then 50 calendars are left over, and thisMAX is 50, the larger of 50 and 0. However, if demand is 250, then nocalendars are left over, and this MAX is 0, the larger of −50 and 0.(This calculation could also be accomplished with an IF function insteadof a MAX function.)6.Profit. Calculate the profit in cell G19 with theformula=D19+F19-E197.Copy to other rows. This is a “one-line”simulation, where all of the logic is cap-tured in a single row, row 19.For one-line simulations, you can replicate the logic with new randomnumbers very easily by copying down. Copy row 19 down to row 1018 togenerate 1000 replications.8.Summary measures. Each profit value incolumn G corresponds to one randomly generated demand. You usually wantto see how these vary from one replication to another. First, calculatethe average and standard deviation of the 1000 profits in cells B12 andB13 with the formulas=AVERAGE(G19:G1018)and=STDEV.S(G19:G1018)Similarly,calculate the smallest and largest of the 1000 profits in cells B14 andB15 with the MIN and MAX functions.9.Distribution of simulated profits.There are only three possible profits, −$250, $125, or $500 (dependingon whether demand is 100, 150, or at least 200—see the followingdiscussion). You can use the COUNTIF function to count the number oftimes each of these possible profits is obtained. To do so, enter theformula=COUNTIF($G$19:$G$1018,I19)incell J19 and copy it down to cell J21.Checking Logic with DeterministicInputsIt can be difficult to check whether the logic in your model iscorrect, because of the ran-dom numbers. The reason is that you usuallyget different output values, depending on the particular random numbersgenerated. Therefore, it is sometimes useful to enter well-chosen fixedvalues for the random inputs, just to see whether your logic is correct.We call fixedfixedthese deterministic checks. In the present example,you might try several fixed demands, at least one of which is less thanthe order quantity and at least one of which is greater than the orderquantity. For example, if you enter a fixed demand of 150, the revenue,cost, refund, and profit should be $1500, $1500, $125, and $125,respectively. Or if you enter a fixed demand of 250, these outputs are$2000, $1500, $0, and $500. There is no random-ness in these values;every correct model should get these same values. If your model doesn’tget these values, there must be a logic error in your model that hasnothing to do with random numbers or simulation. Of course, you shouldfix any such logical errors before reentering the random demand andrunning the simulation.You can make a similar check by keeping therandom demand, repeatedly pressing the F9 key, and watching the outputsfor the different random demands. For example, if the refund is not $0every time demand exceeds the order quantity, you know you have alogicalerror in at least one formula. The advantage of deterministic checks isthat you can compare your results with those of other users, usingagreed-upon test values of the ran-dom quantities. You should all getexactly the same outputs.Discussion of the Simulation ResultsAt thispoint, it is a good idea to stand back and see what you haveaccomplished. First, in the body of the simulation, rows 19 through1018, you randomly generated 1000 possible demands and the correspondingprofits. Because there are only five possible demand values (100, 150,200, 250, and 300), there are only five possible profit values: −$250,$125, $500, $500, and $500. Also, note that for the order quantity 200,the profit is $500 regardless of whether demand is 200, 250, or 300.(Make sure you understand why.) A tally of the profit values in theserows, including the hidden rows, indicates that there are 299 rows withprofit equal to −$250 (demand 100), 191 rows with profit equal to $125(demand 150), and 510 rows with profit equal to $500 (demand 200, 250,or 300). The average of these 1000 profits is $204.13, and theirstandard deviation is $328.04. (Again, however, remember that youranswers will probably differ from these because of different randomnumbers.)Typically, a simulation model should capture one or more outputvariables, such as profit. These output variables depend on randominputs, such as demand. The goal is to estimate the probabilitydistributions of the outputs. In the Walton simulation the estimatedprobability distribution of profitisP(Profit=−$250)=299/1000=0.299P(Profit=$125)=191/1000=0.191P(Profit=$500)=510/1000=0.510Theestimated mean of this distribution is $204.13 and the estimatedstandard deviation is $328.04. It is important to realize that if theentire simulation is run again with differentrandom numbers (such as theones you might have generated on your PC), the answers will probably beslightly different. For illustration, we pressed the F9 key five timesand got the following average profits: $213.88, $206.00, $212.75,$219.50, and $189.50. So this is truly a case of “answers will vary.”Notesabout Confidence IntervalsIt is common in computer simulations toestimate the mean of some distribution by the average of the simulatedobservations. The usual practice is then to accompany this esti-matewith a confidence interval, which indicates the accuracy of theestimate. You should recall from Chapter 8 that to obtain a confidenceinterval for the mean, you start with the estimated mean and then addand subtract a multiple of the standard error of the estimated standarderrorstandard errormean. If the estimated mean (that is, the average) isX, the confidence interval is given in the following formula.ConfidenceInterval for the MeanX±Multiple×Standard Error of XWe repeat thesebasic facts about confidence intervals from Chapter 8 here for yourconvenience.The confidence interval provides a measure of accuracy ofthe mean profit, as estimated from the simulation.The standard error of Xis the standard deviation of the observations divided by the squareroot of n, the number of observations:Here,s is the symbol for the standard deviation of the observations. You canobtain it with the STDEV.S function in Excel.The multiple in theconfidence interval formula depends on the confidence level and thenumber of observations. If the confidence level is 95%, for example, themultiple is very close to 2, so a good guideline is to go out twostandard errors on either side of the average to obtain an approximate95% confidence interval for the mean.The idea is to choose the number ofiterations large enough so that the resulting confidence interval willbe sufficiently narrow.Approximate 95% Confidence Interval for theMeanX±2s/!n!!!Sample Size Determinationn=4×(Estimated standarddeviation)2B2Standard Error of Xs/!n!!!Analysts often plan a simulationso that the confidence interval for the mean of some important outputwill be sufficiently narrow. The reasoning is that narrow confidenceinter-vals imply more precision about the estimated mean of the outputvariable. If the confi-dence level is fixed at some value such as 95%,the only way to narrow the confidence interval is to simulate morereplications. Assuming that the confidence level is 95%, the followingvalue of n is required to ensure that the resulting confidence intervalwill have a half-length approximately equal to some specified valueB:This formula requires an estimate of the standard deviation of theoutput variable. For example, in the Walton simulation the 95%confidence interval with n= 1000 has half-15-4Simulationwith Built-in Excel Tools789Here, s is the symbol for the standarddeviation of the observations. You can obtain it with the STDEV.Sfunction in Excel.The multiple in the confidence interval formuladepends on the confidence level and the number of observations. If theconfidence level is 95%, for example, the multiple is very close to 2,so a good guideline is to go out two standard errors on either side ofthe average to obtain an approximate 95% confidence interval for themean.The idea is to choose the number of iterations large enough so thatthe resulting confidence interval will be sufficientlynarrow.Approximate 95% Confidence Interval for the MeanX±2s/!n!!!SampleSize Determinationn=4×(Estimated standard deviation)2B2Standard Error ofXs/!n!!!Analysts often plan a simulation so that the confidenceinterval for the mean of some important output will be sufficientlynarrow. The reasoning is that narrow confidence inter-vals imply moreprecision about the estimated mean of the output variable. If theconfi-dence level is fixed at some value such as 95%, the only way tonarrow the confidence interval is to simulate more replications.Assuming that the confidence level is 95%, the following value of n isrequired to ensure that the resulting confidence interval will have ahalf-length approximately equal to some specified value B:This formularequires an estimate of the standard deviation of the output variable.For example, in the Walton simulation the 95% confidence interval withn= 1000 has half-length ($224.46−$183.79)/2= $20.33. Suppose that youwant to reduce this half-length to $12.50—that is, you want B= $12.50.You do not know the exact standard deviation of the profit distribution,but you can estimate it from the simulation as $328.04. Therefore, toobtain the required confidence interval half-length B, you need tosimulate n replications, wheren=4(328.04)212.502≈2755(When this formulaproduces a noninteger, it is common to round upward.) The claim, then,is that if you rerun the simulation with 2755 replications rather than1000 replica-tions, the half-length of the 95% confidence interval forthe mean profit will be close to $12.50.Finding the Best OrderQuantityWe are not yet finished with the Walton example. So far, thesimulation has been run for only a single order quantity, 200. Walton’sultimate goal is to find the best order quanbestbest-tity. Even thisstatement must be clarified. What does “best” mean? As in Chapter 6, onepossibility is to use the expected profit—that is, EMV—as theoptimality criterion, expectedexpectedbut other characteristics of theprofit distribution could influence the decision. You cobtain the required outputs with a data table. Specifically, you can usea data table to rerun the simulation for other order quantities. Thisdata table and a corresponding chartare shown in Figure 15.27Tocreate this table, enter the trial order quantities shown in the rangeM20:M28, enter the link =B12 to the average profit in cell N19, andselect the data table range M19:N28. Then select Data Table from theWhat-If Analysis dropdown list on the Data ribbon, specifying that thecolumn input cell is B9. (See Figure 15.26.) Finally, construct a columnchart of the average profits in the data table. Note that an orderquantity of 150appears to maximize the average profit. Its averageprofit of $258.00 is slightly higher than the average profits fromnearby order quantities and much higher than the profit gained from anorder of 200 or more calendars. However, again keep in mind that this isa simula-tion, so that all of these average profits depend on theparticular random numbers gener-ated. If you rerun the simulation withdifferent random numbers, it is conceivable that some other orderquantity could be best.Excel Tip: Calculation Settings with DataTablesSometimes you will create a data table and the values will beconstant the whole way down. This could mean you did something wrong,but more likely it is due to a calculation setting. To check, go to theFormulas ribbon and click the Calculation Options dropdown arrow. If itisn’t Automatic (the default setting), you need to click the CalculateNow (or Calculate Sheet) button or press the F9 key to make the datatable calculate correctly. (The Calculate Now and F9 key recalculateeverything in your workbook. The Calculate Sheet option recalculatesonly the active sheet.) Note that the Automatic Except for Data Tablessetting is there for a reason.Datatables, especially those based on complex simulations, can take a lotof time to recalcu-late, and with the default setting, thisrecalculation occurs every time anything changes in your workbook. Sothe Automatic Except for Data Tables setting is handy to prevent datatables from recalculating until you force them to by pressing the F9 keyor clicking one of the Calculate buttons.Using a Data Table to RepeatSimulationsThe Walton simulation is a particularly simple one-linesimulation model. All of the logic—generating a demand and calculatingthe corresponding profit—can be captured in a single row. Then toreplicate the simulation, you can simply copy this row down as far asyou like. Many simulation models are significantly more complex andrequire more than one row to capture the logic. Nevertheless, they stillresult in one or more output quantities (such as profit) that you wantto replicate. We now illustrate another method of replicating with Exceltools only that is more general (still using the Walton example). Ituses a data table to generate the replications. Refer to Figure 15.28and the file Ordering Calendars – Excel Only 2.xlsx.Throughrow 19, the only difference between this model and the previous modelis that the RAND function is embedded in the VLOOKUP function for demandin cell B19. This makes the model slightly more compact. As before, ituses the given data at the top of the spreadsheet to construct a typical“prototype” of the simulation in row 19. This time, however, you do notcopy row 19 down. Instead, you create a data table in the rangeA23:B1023 to replicate the basic simulation 1000 times. In column A, youlist the repli-cation numbers, 1 to 1000. Next, you enter the formula=F19 in cell B23. This forms a link to the profit from the prototype rowfor use in the data table. Then you create a data table and enter anyblank cell (such as C23) as the column input cell. (No row input cellany blank cellany blank cellis necessary, so its box should be leftempty.) This tricks Excel into repeating the row 19 calculations 1000times, each time with a new random number, and reporting the profits incolumn B of the data table. (If you wanted to see other simulatedquantities, such as rev-enue, for each replication, you could add extraoutput columns to the data table.)Usinga Two-Way Data TableYou can carry this method one step further to seehow the profit depends on the order quantity. Here you use a two-waydata table with the replication number along the side and possible orderquantities along the top. See Figure 15.29 and the file OrderingCalendars – ExcelOnly3.xlsx. Now the data table range is A23:J1023, and the driving formulain cell A23 is again the link =F19. The column input cell should againbe any blank cell, and the row input cell should be B9 (the orderquantity). Each cell in the body of the data table shows a simulatedprofit for a particular replication and a particular order quantity, andeach is based on a different random demand.differentdifferentByaveraging the numbers in each column of the data table (see row 14 inthe finished version of the file), you can see which is the best orderquantity. It is also helpful to con-struct a column chart of theseaverages, as in Figure 15.30. Now, however, assuming you have not frozenanything, the data table and the corresponding chart will change eachtime you press the F9 key. To see whether 150 is always the best orderquantity, you can press the F9 key and see whether the bar above 150continues to be the highest. (It usually is, but not always.)■AND QUESTION 1-

Beforecomputers were widespread, almost all risk analysis was done withoutsimulation. Therefore, only a handful of scenarios could be formulatedto understand the risk of a decision. Typically, a best-case andworst-case scenario was determined and decisions were based on these twoscenarios. What are some of the drawbacks of this decision-makingapproach? Specifically, how does the capability to summarize 1,000s ofsimulated scenarios improve the approach? AND QUESTION 2- By definition, simulations require a distribution to be specified (e.g.,normal, Poisson). Many times, the exact distribution to be used isunknown, so it must be assumed. One argument against using simulationsto perform risk analysis is that there is no real benefit because theset of assumptions is simply shifted from assumed parameter values toassumed distributions of parameters. Comment on this argument andjustify your opinions with reasons, facts, and examples.