use analytics techniques to analyze a case problem
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.