In: Operations Management
Can all parts to this be answered using excel?
Problem 16-11 (Algorithmic)
In preparing for the upcoming holiday season, Fresh Toy Company (FTC) designed a new doll called The Dougie that teaches children how to dance. The fixed cost to produce the doll is $100,000. The variable cost, which includes material, labor, and shipping costs, is $34 per doll. During the holiday selling season, FTC will sell the dolls for $42 each. If FTC overproduces the dolls, the excess dolls will be sold in January through a distributor who has agreed to pay FTC $10 per doll. Demand for new toys during the holiday selling season is extremely uncertain. Forecasts are for expected sales of 60,000 dolls with a standard deviation of 15,000. The normal probability distribution is assumed to be a good description of the demand. FTC has tentatively decided to produce 60,000 units (the same as average demand), but it wants to conduct an analysis regarding this production quantity before finalizing the decision.
a) What-if spreadsheet model is following:
EXCEL FORMULAS:
E14 =MIN(E12,E10)*E4-E10*E3+(E10-MIN(E12,E10))*E5-E2
Average profit = $ 380,000
--------------------------
b) Simulation model is following:
EXCEL FORMULAS:
Parameters | Simulation results | ||||||
Fixed cost | F | 100000 | |||||
Variable cost | c | 34 | Average Profit | =AVERAGE(H15:H1014) | |||
Selling price | p | 42 | |||||
Discounted price | v | 10 | |||||
Mean Sales Forecast | μ | 60000 | |||||
Std Dev of Sales Forecast | σ | 15000 | |||||
Production Quantity | Q | 60000 | |||||
Simulation | |||||||
Trial | Demand | Sales | Surplus | Revenue from Sales | Revenue from Sales of Surplus | Total Cost | Net Profit |
D | S=MIN(D,Q) | V=MAX(0,Q-D) | S*p | V*p | Q*c+F | S*p+V*p-(Q*c+F) | |
1 | =NORMINV(RAND(),$E$7,$E$8) | =MIN(B15,$E$10) | =MAX(0,$E$10-B15) | =C15*$E$4 | =D15*$E$5 | =$E$10*$E$3+$E$2 | =E15+F15-G15 |
2 | =NORMINV(RAND(),$E$7,$E$8) | =MIN(B16,$E$10) | =MAX(0,$E$10-B16) | =C16*$E$4 | =D16*$E$5 | =$E$10*$E$3+$E$2 | =E16+F16-G16 |
3 | =NORMINV(RAND(),$E$7,$E$8) | =MIN(B17,$E$10) | =MAX(0,$E$10-B17) | =C17*$E$4 | =D17*$E$5 | =$E$10*$E$3+$E$2 | =E17+F17-G17 |
4 | =NORMINV(RAND(),$E$7,$E$8) | =MIN(B18,$E$10) | =MAX(0,$E$10-B18) | =C18*$E$4 | =D18*$E$5 | =$E$10*$E$3+$E$2 | =E18+F18-G18 |
5 | =NORMINV(RAND(),$E$7,$E$8) | =MIN(B19,$E$10) | =MAX(0,$E$10-B19) | =C19*$E$4 | =D19*$E$5 | =$E$10*$E$3+$E$2 | =E19+F19-G19 |
6 | =NORMINV(RAND(),$E$7,$E$8) | =MIN(B20,$E$10) | =MAX(0,$E$10-B20) | =C20*$E$4 | =D20*$E$5 | =$E$10*$E$3+$E$2 | =E20+F20-G20 |
7 | =NORMINV(RAND(),$E$7,$E$8) | =MIN(B21,$E$10) | =MAX(0,$E$10-B21) | =C21*$E$4 | =D21*$E$5 | =$E$10*$E$3+$E$2 | =E21+F21-G21 |
8 | =NORMINV(RAND(),$E$7,$E$8) | =MIN(B22,$E$10) | =MAX(0,$E$10-B22) | =C22*$E$4 | =D22*$E$5 | =$E$10*$E$3+$E$2 | =E22+F22-G22 |
9 | =NORMINV(RAND(),$E$7,$E$8) | =MIN(B23,$E$10) | =MAX(0,$E$10-B23) | =C23*$E$4 | =D23*$E$5 | =$E$10*$E$3+$E$2 | =E23+F23-G23 |
Repeat the simulation formulas for 1000 trials
Average profit = $ 172,377
Average profit is less than the profit corresponding to average demand of 60,000 units.
----------------------------
c)
Simulation using 50,000 units is following:
Simulation using 70,000 units is following:
50,000-unit production quantity: $
231,059
70,000-unit production quantity: $ 65,392
----------------------------
d)
Net profit with production quantity of 50,000 units is the maximum.
Therefore, it is recommended to produce 50,000 units