Question

A new edition of a very popular textbook will be published a year from now. The publisher currently has 1,000 copies on hand and is deciding whether to do another printing before the new edition comes out. The publisher estimates that demand for the book during the next year is governed by the probability distribution in the file P10_31.xlsx. (See Demand and Probability Table Below)A production run incurs a fixed cost of $15,000 plus a variable cost of $20 per book printed. Books are sold for $190 per book. Any demand that cannot be met incurs a penalty cost of $30 per book, due to loss of goodwill. Up to 1,000 of any leftover books can be sold to Barnes and Noble for $45 per book. The publisher is interested in maximizing expected profit. The following print-run sizes are under consideration: 0 (no production run) to 16,000 in increments of 2,000. What decision would you recommend? Use simulation with 1,000 replications.
Select02,0004,0006,0008,00010,00012,00014,00016,000

For your optimal decision, the publisher can be 90% certain that the actual profit associated with remaining sales of the current edition will be between what two values? If needed, round your answers to whole dollar amounts.
$ and $   

Demand	Probability
3000	0.20
4000	0.35
5000	0.25
6000	0.10
8000	0.05
10000	0.05

Answer 1

To simulate the demand, we get the following cumulative distribution and the random number intervals

			Random number interval
Demand	Probability	Cumulative probability	From	To
3000	0.2	0.2	0	0.2
4000	0.35	0.55	0.2	0.55
5000	0.25	0.8	0.55	0.8
6000	0.1	0.9	0.8	0.9
8000	0.05	0.95	0.9	0.95
10000	0.05	1	0.95	1

To simulate demand we use the following

• generate a random number from uniform distribution in the interval (0,1) using RAND()
• Pick the demand corresponding to the random number interval in which the random number lies
  • For example if the random number generated is 0,60, it lies in the interval 0.55 to 0.80 and hence the demand is 5000

Quantity sold = minimum(demand,print run)

Revenue = quantity sold * 190

Unsold quantity = print run - quantity sold

Revenue from unsold= min(unsold quantity,1000)*45

Cost of printing = 15000+print run * 20

Penalty = (demand - quantity sold) *30

Profit = (revenue+revenue from unsold) - (cost of printing +penalty)

= expected profit = average profit of 1000 simulations

s= sample standard deviation of profit

is the standard error of mean

90% confidence interval indicates a level of significance

Since the sample size is 1000 and it is greater than 30, using CLT, we can use normal distribution as the sampling distribution of mean.

The right tail critical value is

Using the standard normal table (or Excel function =NORM.INV(0.95,0,1)) we get

The 90% confidence interval for actual mean profit is

Prepare the following

copy the rows to make 1000 trials

Paste as values to avoid changes

Get this

Prepare a data table as below

get this

select the data table, then use data-->what if analysis--->data table to set

get this

The following print-run sizes are under consideration: 0 (no production run) to 16,000 in increments of 2,000. What decision would you recommend?

ans: 6000

Enter 6000 as the print run size and get the 90% confidence interval

For your optimal decision, the publisher can be 90% certain that the actual profit associated with remaining sales of the current edition will be between what two values?

ans: $761,921 and $784,149