Question

(All answers were generated using 1,000 trials and native Excel functionality.)

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 uncertain. The normal probability distribution with an average of 60,000 dolls and a standard deviation of 15,000 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)	Create a what-if spreadsheet model using formulas that relate the values of production quantity, demand, sales, revenue from sales, amount of surplus, revenue from sales of surplus, total cost, and net profit. What is the profit when demand is equal to its average (60,000 units)?
	$
(b)	Modeling demand as a normal random variable with a mean of 60,000 and a standard deviation of 15,000, simulate the sales of The Dougie doll using a production quantity of 60,000 units. What is the estimate of the average profit associated with the production quantity of 60,000 dolls? Round your answer in whole dollar.
	$
	How does this compare to the profit corresponding to the average demand (as computed in part a)?
	The input in the box below will not be graded, but may be reviewed and considered by your instructor.
(c)	Before making a final decision on the production quantity, management wants an analysis of a more aggressive 70,000-unit production quantity and a more conservative 50,000-unit production quantity. Run your simulation with these two production quantities. What is the average profit associated with each? Round your answer in whole dollar.
	When ordering 50,000 units, the average profit is approximately $ When ordering 70,000 units, the average profit is approximately $
(d)	Besides average profit, what other factors should FTC consider in determining a production quantity? Compare the four production quantities (40,000; 50,000; 60,000; and 70,000) using all these factors. What trade-offs occur?
	If required, round Probability of a Loss to three decimal places and Probability of a Shortage to two decimal places. Round your answer in whole dollar.
	ProductionQuantityAverageNet ProfitProfit StandardDeviationMaximumNet ProfitProbability ofa LossProbability ofa Shortage40,000$$$50,000$$$60,000$$$70,000$$$

Answer 1

(a) Spreadhseet model is as given below

(b) Estimated Average profit associated with production of 60,000 dolls = $ 192,014

(c) Estimated profit with 70000 units of production = 45,547

Estimated profit with 50000 units of production = 224,676

Formulae B14-SD$10 C14 NORMINV(RAND().SDŚ7,SD$8) D14 MIN(B14,C14) E14 B14-D14 F14 D14*SDŚ3 G14 E14*SD$5 H14-SD$6 114-B14*SD$4 J14-H14+114 K14 -F14+G14-J14 Input uput Production Average Demand- Average Sales- Average Surplus - Average Revenue from Sales Average Revenue from Surplus Sale- Total Cost- Average Profit elling Price- Variable Cost- Surplus Selling price- Fixed Cost - 34 10 100,000 Average Demand 60,000 15,000 60,000 60,755 54,125 5,875 2,273,269 58,746 2,140,000 192,014 4 SD of Demand - 10 Production 60,000 12 Revenue Revenue from Variable 13 Trial Production Demand Sales Surplus from Sales Sale of Surplus Fixed Cost Cost 14 1 60,000 50,788 50,788 9,2122,133,112 15 2 60,000 53,446 53,446 6,554 2,244,731 16 3 60,000 44,959 44,959 15,041 1,888,274 17 460,000 51,653 51,653 8,347 2,169,438 18 5 60,000 62,70360,000 02,520,000 19 6 60,000 75,183 60,000 02,520,000 20 7 60,000 65,50460,000 02,520,000 21 860,000 67,480 60,000 02,520,000 22 9 Total Cost Profit 92,116 65,540 150,411 83,467 0 0 0 0 223,116 100,000 2,040,000 2,140,000 85,229 100,000 2,040,000 2,140,000 170,272 100,000 2,040,000 2,140,000 -101,315 100,000 2,040,000 2,140,000 112,905 100,000 2,040,000 2,140,000 380,000 100,000 2,040,000 2,140,000 380,000 100,000 2,040,000 2,140,000 380,000 100,000 2,040,000 2,140,000 380,000 100,000 2,040,000 2,140,000 -333,973 60,000 37,688 37,688 22,312 1,582,911

Input Selling Price Variable Cost - Surplus Selling price- Fixed Cost - Average Demand60,000 SD of Demand - uput Production Average Demand- Average Sales Average Surplus - Average Revenue from Sales Average Revenue from Surplus Sale- Total Cost- Average Profit 70,000 59,393 57,017 12,983 2,394,718 129,829 2,480,000 44,547 Formulae B14-SD$10 C14 NORMINV(RAND().SDŚ7,SD$8) D14 MIN(B14,C14) E14 B14-D14 F14 D14*SDŚ3 G14 E14*SD$5 H14-SD$6 114-B14*SD$4 J14 -H14+114 K14-F14+G14-J14 4 10 100,000 15,000 70,000 10 Production 12 Revenue Revenue from Variable 13 Trial Production Demand Sales Surplus from Sales Sale of Surplus Fixed Cost Cost 14 1 70,000 34,841 34,841 35,159 1,463,307 15 2 70,000 69,386 69,386 614 2,914,193 16 3 70,000 48,739 48,739 21,261 2,047,046 17 4 70,000 70,285 70,000 02,940,000 18 5 70,000 87,451 70,000 02,940,000 19 6 70,000 51,58551,585 18,415 2,166,574 20 7 70,000 40,649 40,649 29,351 1,707,237 Total Cost Profit 351,594 6,144 212,608 0 0 184,149 293,515 100,000 2,380,000 2,480,000 -665,100 100,000 2,380,000 2,480,000 440,338 100,000 2,380,000 2,480,000 -220,346 100,000 2,380,000 2,480,000 460,000 100,000 2,380,000 2,480,000 460,000 100,000 2,380,000 2,480,000 -129,277 100,000 2,380,000 2,480,000 -479,248 100 0n0 2 380 000 70 00n 87 620 70 00O 940 000 480 000 460 00n

Input Selling Price Variable Cost - Surplus Selling price Fixed Cost - Average Demand SD of Demand - uput Production Average Demand- Average Sales Average Surplus - Average Revenue from Sales Average Revenue from Surplus Sale- Total Cost- Average Profit 50,000 59,310 47,646 2,354 2,001,138 23,539 1,800,000 224,676 Formulae B14-SD$10 C14 NORMINV(RAND().SDŚ7,SD$8) D14 MIN(B14,C14) E14 B14-D14 F14 D14*SDŚ3 G14 E14*SD$5 H14-SD$6 114-B14*SD$4 J14 -H14+114 K14-F14+G14-J14 4 10 100,000 60,000 15,000 10 Production 50,000 12 Revenue Revenue from Variable 13 Trial Production Demand Sales Surplus from Sales Sale of Surplus Fixed Cost Cost 14 1 50,000 54,637 50,000 02,100,000 15 2 50,000 56,103 50,000 02,100,000 16 3 50,000 44,859 44,859 5,141 1,884,094 17 4 50,000 59,504 50,000 02,100,000 18 5 50,000 36,108 36,108 13,892 1,516,527 19 6 50,000 68,548 50,000 02,100,000 20 7 50,000 60,18250,000 02,100,000 21 8 50,000 73,07750,000 02,100,000 22 950,000 56,662 50,000 02,100,000 23 10 50,000 70,853 50,000 02,100,000 24 11 Total Cost Profit 0 100,000 1,700,000 1,800,000 300,000 100,000 1,700,000 1,800,000 300,000 100,000 1,700,000 1,800,000 135,500 100,000 1,700,000 1,800,000 300,000 100,000 1,700,000 1,800,000 -144,550 100,000 1,700,000 1,800,000 300,000 100,000 1,700,000 1,800,000 300,000 100,000 1,700,000 1,800,000 300,000 100,000 1,700,000 1,800,000 300,000 100,000 1,700,000 1,800,000 300,000 100 0n0 1 700 000 51,406 138,922 0 50 000 82 038 50 00O 2 100 000 1 800 000 300 00n