Complete the exercise in this assignment using RStudio. You will write and edit scripts for this lab assignment. An R script is a file containing a record of the R expressions you want to run. The file name of the script ends with .R and behaves like a program that you can run at the command prompt. Or you can run the commands one at a time. When you have completed the lab, paste your R script and output into a Word document and submit to Harvey. It may help to run “Source with Echo” in the editor window and copy/paste the interpreter output into a Word file.

The purpose of this assignment is to use analytics techniques to analyze a case problem. Part 1Read Case Study Case 15.2 “Ebony Bath Soap” from the textbook, and then complete the following items.For Questions 1 and 2 of the case, use the Palisade DecisionTools Excel software to set up a simulation model and run a simulation with 500 trials for the case. Ensure that all Palisade software output is included in your files and that only one Excel file is open when running a simulation. Use the "Topic 3 Case Study Template" file as a starting point. Hint: The RiskSimtable function was be helpful for running the simulations.Respond to Question 3 as written in the problem. Ignore the confidence interval portion of the question.Respond to Question 4 as written in the problem.To receive full credit on the assignment, complete the following. ALL WORK MUST BE ORIGINAL!!! Ensure that the Palisade software output is included with your submission.Ensure that Excel files include the associated cell functions and/or formulas if functions and/or formulas are used.Include a written response to all narrative questions presented in the problem by placing it in the associated Excel file.Include screenshots of all simulation distribution results for output variables.Place each problem in its own Excel file. Ensure that your first and last name are in your Excel file names. Part 2In a 500-750-word summary to company management, address the following. Include relevant charts and graphs within your summary, as needed.Describe the case specific business requirements and how they can be communicated across all levels of the organization.Based on the simulation results, discuss the Annual Cost output statistical distributions. Assume that your audience as minimal background in statistics.Discuss which Annual Cost output probability distribution has the most dispersion, and explain why this is so. Explain the descriptive, predictive, and prescriptive analytics that have been used to formulate the solutions to the business needs.Based on the Annual Cost output statistical distributions and other information gleaned from your analysis, discuss the specific prescribed course of action you would recommend to company management and justify your recommendations. Include discussion of how the proposed analytics solutions can optimize organizational performance and effectiveness.While APA style is not required for the body of this assignment, solid academic writing is expected, and documentation of sources should be presented using APA formatting guidelines, which can be found in the APA Style Guide, located in the Student Success Center.This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion. You are required to submit this assignment to LopesWrite.15.2 SIMULATING WITH EXCEL ONLY AT WALTON BOOKSTORE Recall that Walton Bookstore must decide how many of next year’s nature calendars to order. Each calendar costs the bookstore $7.50 and sells for $10. After January 1, all unsold calendars will be returned to the publisher for a refund of $2.50 per calendar. In this version, we assume that demand for calendars (at the full price) is given by the probability distribution shown in Table 15.1. Walton wants to develop a simulation model to help it decide how many calendars to order.Objective To use built-in Excel tools—including the RAND function and data tables, but no add-ins—to simulate profit for several order quantities and ultimately choose the “best” order quantity.Where Do the Numbers Come From?Where Do the Numbers Come From?The numbers in Table 15.1 are the key to the simulation model. They are discussed in more detail next.SolutionWe first discuss the probability distribution in Table 15.1. It is a discrete distribution with only five possible values: 100, 150, 200, 250, and 300. In reality, it is clear that other values of demand are possible. For example, there could be demand for exactly 187 calendars. In spite of its apparent lack of realism, we use this discrete distribution for two reasons. First, Table 15.1 Probability Distribution of Demand for Walton ExampleDemandProbability1000.301500.20 its simplicity is a nice feature to get you started with simulation modeling. Second, discrete distributions are often used in real business simulation models. Even though the discrete distribution is only an approximation to reality, it can still provide important insights into the actual problem.As for the probabilities listed in Table 15.1, they are typically drawn from historical data or (if historical data are lacking) educated guesses. In this case, the manager of Walton Bookstore has presumably looked at demands for calendars in previous years, and he has used any information he has about the market for next year’s calendars to estimate, for example, that the probability of a demand for 200 calendars is 0.30. The five probabilities in this table must sum to mustmust1. Beyond this requirement, they should be as reasonable and consistent with reality as possible.It is important to realize that this is really a decision problem under uncertainty. Walton must choose an order quantity before knowing the demand for calendars. Unfortunately, Solver cannot be used because of the uncertainty.7 knowing the demand for calendars. Unfortunately, knowing the demand for calendars. Unfortunately, Therefore, we develop a simulation model for any fixed order quantity. Then we run this simulation model with various order fixedfixedquantities to see which one appears to be best.Developing the Simulation ModelNow we discuss the ordering model. For any fixed order quantity, we show how Excel can be used to simulate 1000 replications (or any other number of replications). Each replica-tion is an independent replay of the events that occur. To illustrate, suppose you want to simulate profit if Walton orders 200 calendars. Figure 15.26 illustrates the results obtained by simulating 1000 independent replications for this order quantity. (See the file Ordering Calendars - Excel Only 1.xlsx.) Note that there are many hidden rows in Figure 15.26. To develop this model, use the following steps.1.Inputs. Enter the cost data in the range B4:B6, the probability distribution of demand in the range E5:F9, and the proposed order quantity, 200, in cell B9. Pay particular attention to the way the probability distribution is entered (and compare to the Discrete sheet in the Probability Distributions.xlsx file). Columns E and F contain the possible demand values and the probabilities from Table 15.1. It is also necessary (see step 2 for the reasoning) to have the cumulative probabilities in column D. To obtain these, first enter the value 0 in cell D5. Then enter the formula=F5+D5in cell D6 and copy it to the range D7:D9.2.Generate random demands. The key to the simulation is the generation of a cus-tomer demand in column C from a random number generated by the RAND func-tion in column B and the probability distribution of demand. Here is how it works. The interval from 0 to 1 is split into five segments: 0.0 to 0.3 (length 0.3), 0.3 to 0.5(length 0.2), 0.5 to 0.8 (length 0.3), 0.8 to 0.95 (length 0.15), and 0.95 to 1.0 (length 0.05). Note that these lengths are the probabilities of the various demands. Then a demand is associated with each random number, depending on which interval the random number falls in. For example, if a random number is 0.5279, this falls in the third interval, so it is associated with the third possible demand value, 200.To implement this procedure, you use a VLOOKUP function based on the range D5:F9 (named LookupTable). This table has the cumulative probabilities in column D and the possible demand values in column E. In fact, the whole purpose of the cumulative probabilities in column D is to allow the use of the VLOOKUPfunction. To generate the simulated demands, enter the formula=VLOOKUP(RAND(),LookupTable,2)in cell C19. This formula compares any RAND value to the values in D5:D9 and returns the appropriate demand from E5:E9. (In the file, you will note that random cells are colored green. This coloring convention is not required, but we use it con-sistently to identify the random cells.)This step is the key to the simulation, so make sure you understand exactly what it entails. The rest is bookkeeping, as indicated in the following steps.3.Revenue. Once the demand is known, the number of calendars sold is the smaller of the demand and the order quantity. For example, if 150 calendars are demanded, 150will be sold. But if 250 are demanded, only 200 can be sold (because Walton orders only 200). Therefore, to calculate the revenue in cell D19, enter the formula=Unit_price*MIN(C19,Order_quantity)4.Ordering cost. The cost of ordering the calendars does not depend on the demand; it is the unit cost multiplied by the number ordered. Calculate this cost in cell E19 with the formula=Unit_cost*Order_quantity5.Refund. If the order quantity is greater than the demand, there is a refund of $2.50for each calendar left over; otherwise, there is no refund. Therefore, calculate the refund in cell F19 with the formula=Unit_refund*MAX(Order_quantity-C19,0) For example, if demand is 150, then 50 calendars are left over, and this MAX is 50, the larger of 50 and 0. However, if demand is 250, then no calendars are left over, and this MAX is 0, the larger of −50 and 0. (This calculation could also be accomplished with an IF function instead of a MAX function.)6.Profit. Calculate the profit in cell G19 with the formula=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 random numbers very easily by copying down. Copy row 19 down to row 1018 to generate 1000 replications.8.Summary measures. Each profit value in column G corresponds to one randomly generated demand. You usually want to see how these vary from one replication to another. First, calculate the average and standard deviation of the 1000 profits in cells B12 and B13 with the formulas=AVERAGE(G19:G1018)and=STDEV.S(G19:G1018)Similarly, calculate the smallest and largest of the 1000 profits in cells B14 and B15 with the MIN and MAX functions.9.Distribution of simulated profits. There are only three possible profits, −$250, $125, or $500 (depending on whether demand is 100, 150, or at least 200—see the following discussion). You can use the COUNTIF function to count the number of times each of these possible profits is obtained. To do so, enter the formula=COUNTIF($G$19:$G$1018,I19) in cell J19 and copy it down to cell J21.Checking Logic with Deterministic InputsIt can be difficult to check whether the logic in your model is correct, because of the ran-dom numbers. The reason is that you usually get different output values, depending on the particular random numbers generated. Therefore, it is sometimes useful to enter well-chosen fixed values 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 than the order quantity and at least one of which is greater than the order quantity. 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’t get these values, there must be a logic error in your model that has nothing to do with random numbers or simulation. Of course, you should fix any such logical errors before reentering the random demand and running the simulation.You can make a similar check by keeping the random demand, repeatedly pressing the F9 key, and watching the outputs for the different random demands. For example, if the refund is not $0 every time demand exceeds the order quantity, you know you have a logical error in at least one formula. The advantage of deterministic checks is that you can compare your results with those of other users, using agreed-upon test values of the ran-dom quantities. You should all get exactly the same outputs.Discussion of the Simulation ResultsAt this point, it is a good idea to stand back and see what you have accomplished. First, in the body of the simulation, rows 19 through 1018, you randomly generated 1000 possible demands and the corresponding profits. 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 these rows, including the hidden rows, indicates that there are 299 rows with profit 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 their standard deviation is $328.04. (Again, however, remember that your answers will probably differ from these because of different random numbers.)Typically, a simulation model should capture one or more output variables, such as profit. These output variables depend on random inputs, such as demand. The goal is to estimate the probability distributions of the outputs. In the Walton simulation the estimated probability distribution of profit isP(Profit=−$250)=299/1000=0.299P(Profit=$125)=191/1000=0.191P(Profit=$500)=510/1000=0.510The estimated mean of this distribution is $204.13 and the estimated standard deviation is $328.04. It is important to realize that if the entire simulation is run again with differentrandom numbers (such as the ones you might have generated on your PC), the answers will probably be slightly different. For illustration, we pressed the F9 key five times and 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.” Notes about Confidence IntervalsIt is common in computer simulations to estimate the mean of some distribution by the average of the simulated observations. The usual practice is then to accompany this esti-mate with a confidence interval, which indicates the accuracy of the estimate. You should recall from Chapter 8 that to obtain a confidence interval for the mean, you start with the estimated mean and then add and subtract a multiple of the standard error of the estimated standard errorstandard errormean. If the estimated mean (that is, the average) is X, the confidence interval is given in the following formula.Confidence Interval for the MeanX±Multiple×Standard Error of XWe repeat these basic facts about confidence intervals from Chapter 8 here for your convenience.The confidence interval provides a measure of accuracy of the mean profit, as estimated from the simulation.The standard error of X is the standard deviation of the observations divided by the square root of n, the number of observations: Here, s is the symbol for the standard deviation of the observations. You can obtain it with the STDEV.S function in Excel.The multiple in the confidence interval formula depends on the confidence level and the number of observations. If the confidence 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 of the average to obtain an approximate 95% confidence interval for the mean.The idea is to choose the number of iterations large enough so that the resulting confidence interval will be sufficiently narrow.Approximate 95% Confidence Interval for the MeanX±2s/!n!!!Sample Size Determinationn=4×(Estimated standard deviation)2B2Standard Error of Xs/!n!!!Analysts often plan a simulation so that the confidence interval for the mean of some important output will be sufficiently narrow. The reasoning is that narrow confidence inter-vals imply more precision about the estimated mean of the output variable. If the confi-dence level is fixed at some value such as 95%, the only way to narrow the confidence interval is to simulate more replications. Assuming that the confidence level is 95%, the following value of n is required to ensure that the resulting confidence interval will have a half-length approximately equal to some specified value B:This formula requires an estimate of the standard deviation of the output variable. For example, in the Walton simulation the 95% confidence interval with n= 1000 has half- 15-4Simulation with Built-in Excel Tools789Here, s is the symbol for the standard deviation of the observations. You can obtain it with the STDEV.S function in Excel.The multiple in the confidence interval formula depends on the confidence level and the number of observations. If the confidence 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 of the average to obtain an approximate 95% confidence interval for the mean.The idea is to choose the number of iterations large enough so that the resulting confidence interval will be sufficiently narrow.Approximate 95% Confidence Interval for the MeanX±2s/!n!!!Sample Size Determinationn=4×(Estimated standard deviation)2B2Standard Error of Xs/!n!!!Analysts often plan a simulation so that the confidence interval for the mean of some important output will be sufficiently narrow. The reasoning is that narrow confidence inter-vals imply more precision about the estimated mean of the output variable. If the confi-dence level is fixed at some value such as 95%, the only way to narrow the confidence interval is to simulate more replications. Assuming that the confidence level is 95%, the following value of n is required to ensure that the resulting confidence interval will have a half-length approximately equal to some specified value B:This formula requires an estimate of the standard deviation of the output variable. For example, in the Walton simulation the 95% confidence interval with n= 1000 has half-length ($224.46−$183.79)/2= $20.33. Suppose that you want 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, to obtain the required confidence interval half-length B, you need to simulate n replications, wheren=4(328.04)212.502≈2755(When this formula produces a noninteger, it is common to round upward.) The claim, then, is that if you rerun the simulation with 2755 replications rather than 1000 replica-tions, the half-length of the 95% confidence interval for the mean profit will be close to $12.50.Finding the Best Order QuantityWe are not yet finished with the Walton example. So far, the simulation has been run for only a single order quantity, 200. Walton’s ultimate goal is to find the best order quanbestbest-tity. Even this statement must be clarified. What does “best” mean? As in Chapter 6, one possibility is to use the expected profit—that is, EMV—as the optimality criterion, expectedexpectedbut other characteristics of the profit distribution could influence the decision. You c obtain the required outputs with a data table. Specifically, you can use a data table to rerun the simulation for other order quantities. This data table and a corresponding chart are shown in Figure 15.27 To create this table, enter the trial order quantities shown in the range M20:M28, enter the link =B12 to the average profit in cell N19, and select the data table range M19:N28. Then select Data Table from the What-If Analysis dropdown list on the Data ribbon, specifying that the column input cell is B9. (See Figure 15.26.) Finally, construct a column chart of the average profits in the data table. Note that an order quantity of 150appears to maximize the average profit. Its average profit of $258.00 is slightly higher than the average profits from nearby order quantities and much higher than the profit gained from an order of 200 or more calendars. However, again keep in mind that this is a simula-tion, so that all of these average profits depend on the particular random numbers gener-ated. If you rerun the simulation with different random numbers, it is conceivable that some other order quantity could be best.Excel Tip: Calculation Settings with Data TablesSometimes you will create a data table and the values will be constant 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 the Formulas ribbon and click the Calculation Options dropdown arrow. If it isn’t Automatic (the default setting), you need to click the Calculate Now (or Calculate Sheet) button or press the F9 key to make the data table calculate correctly. (The Calculate Now and F9 key recalculate everything in your workbook. The Calculate Sheet option recalculates only the active sheet.) Note that the Automatic Except for Data Tables setting is there for a reason. Data tables, especially those based on complex simulations, can take a lot of time to recalcu-late, and with the default setting, this recalculation occurs every time anything changes in your workbook. So the Automatic Except for Data Tables setting is handy to prevent data tables from recalculating until you force them to by pressing the F9 key or clicking one of the Calculate buttons.Using a Data Table to Repeat SimulationsThe Walton simulation is a particularly simple one-line simulation model. All of the logic—generating a demand and calculating the corresponding profit—can be captured in a single row. Then to replicate the simulation, you can simply copy this row down as far as you like. Many simulation models are significantly more complex and require more than one row to capture the logic. Nevertheless, they still result in one or more output quantities (such as profit) that you want to replicate. We now illustrate another method of replicating with Excel tools only that is more general (still using the Walton example). It uses a data table to generate the replications. Refer to Figure 15.28 and the file Ordering Calendars - Excel Only 2.xlsx. Through row 19, the only difference between this model and the previous model is that the RAND function is embedded in the VLOOKUP function for demand in cell B19. This makes the model slightly more compact. As before, it uses 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 not copy row 19 down. Instead, you create a data table in the range A23:B1023 to replicate the basic simulation 1000 times. In column A, you list 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 row for use in the data table. Then you create a data table and enter any blank cell (such as C23) as the column input cell. (No row input cell any blank cellany blank cellis necessary, so its box should be left empty.) This tricks Excel into repeating the row 19 calculations 1000 times, each time with a new random number, and reporting the profits in column B of the data table. (If you wanted to see other simulated quantities, such as rev-enue, for each replication, you could add extra output columns to the data table.) Using a Two-Way Data TableYou can carry this method one step further to see how the profit depends on the order quantity. Here you use a two-way data table with the replication number along the side and possible order quantities along the top. See Figure 15.29 and the file Ordering Calendars - Excel Only 3.xlsx. Now the data table range is A23:J1023, and the driving formula in cell A23 is again the link =F19. The column input cell should again be any blank cell, and the row input cell should be B9 (the order quantity). Each cell in the body of the data table shows a simulated profit for a particular replication and a particular order quantity, and each is based on a different random demand.differentdifferentBy averaging the numbers in each column of the data table (see row 14 in the finished version of the file), you can see which is the best order quantity. It is also helpful to con-struct a column chart of these averages, as in Figure 15.30. Now, however, assuming you have not frozen anything, the data table and the corresponding chart will change each time you press the F9 key. To see whether 150 is always the best order quantity, you can press the F9 key and see whether the bar above 150 continues to be the highest. (It usually is, but not always.)■ AND QUESTION 1- Before computers were widespread, almost all risk analysis was done without simulation. Therefore, only a handful of scenarios could be formulated to understand the risk of a decision. Typically, a best-case and worst-case scenario was determined and decisions were based on these two scenarios. What are some of the drawbacks of this decision-making approach? Specifically, how does the capability to summarize 1,000s of simulated 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 is unknown, so it must be assumed. One argument against using simulations to perform risk analysis is that there is no real benefit because the set of assumptions is simply shifted from assumed parameter values to assumed distributions of parameters. Comment on this argument and justify your opinions with reasons, facts, and examples.

This assignment has two different questions, which I will like the two to be the answer separately one has the template, and if you have an issue in downloading the template, please message me, so I can download and upload it thank you