Question

3. (25 points) In order to be ready for the upcoming holiday season, BTS Toy Company (BTC) designed a new doll called The V that teaches children how to dance. The fixed cost to produce the doll is $105,000. The variable cost, which includes material, labor, and shipping costs, is $35 per doll. During the holiday selling season, FTC will sell the dolls for $45 each. If BTC overproduces the dolls, the excess dolls will be sold in January through a distributor who has agreed to pay BTC $15 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,500. The normal probability distribution is assumed to be a good description of the demand. BTC 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 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 corresponding to average demand (60,000 units)? b. Modeling demand as a normal random variable with a mean of 60,000 and a standard deviation of 15,500, simulate the sales of The V 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? How does this compare to the profit corresponding to the average demand (as computed in part a)? 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 mean profit associated with each? d. Compare the four production quantities (50,000; 60,000; 70,000 and 80,000) using all these factors. What trade-offs occur? What is your recommendation?

Please provide solution through excel with formulas please.

Thank You!

Answer 1

a) Spreadsheet model is as follow:

D 105,000 1 What-if spreadsheet model 2 Fixed cost 3 Variable cost 4 Selling price 5 Discounted price 15 60,000 15,500 7 Mean Sales Forecast 8 Std Dev of Sales Forecast 9 10 Production Quantity 60,000 60,000 12 Sales (Quantity) 13 Amount of Surplus 14 Revenue from Sales 15 Revenue from Sale of Surplus 16 Total Cost S =MIN{Q,u} V=S-Q S*p1 V*p2 Q*c+F 2,700,000 2,205,000 Net Profit S*p1+V*p2-(Q*c+F) 495,000

EXCEL FORMULA:

105000 А 1 What-if spreadsheet model 2 Fixed cost 3 Variable cost 4 Selling price 5 Discounted price 45 15 7 Mean Sales Forecast 8 Std Dev of Sales Forecast 60000 15500 60000 10 Production Quantity 11 12 Sales (Quantity) 13 Amount of Surplus 14 Revenue from Sales 15 Revenue from Sale of Surplus 16 Total Cost SEMIN{Q,u} V=S-Q S*p1 V*p2 Q*c+F =MIN(C10,c7) =C10-C12 =C12*04 =C13*C5 =C10*C3+C2 18 Net Profit S*p1+V*p2-(Q*c+F) =C14+C15-C16

------------------------------------------------------------------------------------------------------------------------------

b)

Simulation model is as follows:

Simulation results 105,000 A B C 1 Fresh Toy Company (FTC) 2 Parameters 3 Fixed cost 4 Variable cost 5 Selling price 6 Discounted price 7 8 Mean Sales Forecast 9 Std Dev of Sales Forecast 35 Average Net Profit 308,294 45 Probability of shortage 0.50 Profit Standard Deviation 269241 60,000 15,500 10 Maximum Net Profit 495000 1 60,000! 11 Production Quantity 12 13 Simulation Probability of loss 0.138 Trial Demand Sales Surplus Total Cost Net Profit Shortage Yes=1, No=0 14 15 16 17 18 1 2 3 V=MAX(O,Q-D) 10,303 0 0 20 21 5 6 49,697 65,393 67,815 61,229 54,909 58,781 57,317 45,365 67,792 91,391 90,005 69 693 SEMIND,Q) 49,697 60,000 60,000 60,000 54,909 58,781 57,317 45,365 60,000 60,000 60,000 60 000 5,091 1,219 2,683 14,635 Revenue Revenue from from Sales Sales of Surplus S*p1 2,236,353 1,54,549 2,700,000 0 2,700,000 2,700,000 2,470,926 76,358 2,645,125 18,292 2,579,268 40,244 2,041,429 2,19,524 2,700,000 2,700,000 2,700,000 2 700 000 Q*c+F 2,205,000 2,205,000 2,205,000 2,205,000 2,205,000 2,205,000 2,205,000 2,205,000 2,205,000 2,205,000 2,205,000 2.295.000 S*p1+V*p 2-Q*c+F) 185,902 495,000 495,000 495,000 342,284 458,417 414,512 55,953 495,000 495,000 495,000 495.000 24 25 26 27 9 10 11 12

EXCEL FORMULA:

SUMIF X ✓ fx =COUNTIF(H16:H1015,"<"&0)/COUNT(H16:H1015) Simulation results ДА 1 Fresh Toy Company (FTC) 2 Parameters 3 Fixed cost 4 Variable cost 5 Selling price 6 Discounted price 7 8 Mean Sales Forecast 9 Std Dev of Sales Forecast Average Net Profit =AVERAGE(H16:H1015) ='FTC(a)'!C2 ='FTC(a)'!C3 ='FTC(a)'!C4 ='FTC(a)'!CS Probability of Shortage =AVERAGE(116:11015) Profit Standard Deviation =STDEV(H16:H1015) ='FTC(a)'!C7 ='FTC(a)'!C8 10 Maximum Net Profit =MAX(H16:H1015) Q 50000 11 Production Quantity 12 13 Simulation =COUNTIF( H16:H1015,"<"&0)/COUNT( H16:H1015) 14 Trial Demand Sales Surplus Total Cost Net Profit Shortage 16 1 17 2 18 3 19 4 20 5 21 6 22 7 23 8 =NORMINV(RAND().$E$ 8,$E$9) =NORMINV(RANDO),$E$ 8,$E$9) =NORMINV(RANDO),$E$ 8,$E$9) =NORMINV(RANDO),$E$8,$E$9) =NORMINV(RAND().$E$8,$E$9) =NORMINV(RAND(),$E$ 8,$E$9) =NORMINV(RANDO),$E$8,$E$9) ENORMINV(RAND() $E$8,$E$9) ENORMINV(RAND().$E$8,$E$9) =NORMINV(RANDO),$E$8,$E$9) =NORMINV(RAND() SE$8,$E$9) SEMIN(D,Q) =MIN(B16,$E$11) =MIN(B17,$E$11) =MIN(B18,$E$11) =MIN(B19,$E$11) =MIN(B20,$E$11) =MIN(B21, $E$11) =MIN(B22,$E$11) =MIN(B23,$E$11) =MIN(B24,$E$11) =MIN(B25,$E$11) =MIN(B26,$E$11) V=MAX(0,Q-D) =MAX(0,$E$11-B16) =MAX(0,$E$ 11-B17) =MAX(0,$E$11-B18) =MAX(0,$E$11-819) =MAX(0,$E$11-B20) =MAX(0,$E$11-B21) =MAX(0,$E$11-B22) =MAX(0,$E$11-B23) =MAX(0,$E$11-B24) =MAX(0,$E$11-B25) =MAX(0,$E$11-B26) Revenue from Sales S*p1 =C16*$E$5 =C17*$E$5 =C18*$E$5 =C19*$E$5 =C20*$E$5 =C21*$E$5 =C22*$E$5 =C23*$E$5 =C24*$E$5 =C25*$E$5 =C26*$E$5 -27ESE Revenue from Sales of Surplus V*p2 =D16*$E$6 =D17*$E$6 =D18*$E$6 =D19*$E$6 =D20*$E$6 =D21*$E$6 =D22*$E$6 =D23*$E$6 =D24*$E$6 =D25*$E$6 =D26*$E$6 - ES Q*c+F =$E$11*$E$4+$E$3 =$E$11*$E$4+$E$3 =$E$11*$E$4+$E$3 =$E$11*$E$4+$E$3 =$E$11*$E$4+$E$3 =$E$11*$E$4+$E$3 =$E$11*$E$4+$E$3 =$E$11*$E$4+$E$3 =$E$11*$E$4+$E$3 =$E$11*$E$4+$E$3 =$E$11*$E$4+$E$3 S*p1+V*p2-Q*c+F). Yes=1, No=0 =E16+F16-G16 =IF(C16<B16,1,0) =E17+F17-G17 =IF(C17<B17,1,0) =E18+F18-G18 =IF(C18<B18,1,0) =E19+F19-619 =IF(C19<B19,1,0) =E20+F20-G20 =IF(C2O<B20,1,0) =E21+F21-G21 =IF(C21<B21,1,0) =E22+F22-G22 =IF(C22<B22,1,0) =E23+F23-G23 =IF(C23<B23,1,0) =E24+F24-G24 =IF(C24<B24,1,0) =E25+F25-G25 =IF(C25<B25,1,0) =E26+F26-G26 =IF(C26<B26,1,0) -5715zz 249 25 10 26 11 12 -NODMANINDANDI ECO ECO -MAND7C1 -NAAVIA SEC11P1 -CC11*0 CAICO - E z202710

Comparing the two profits, this profit is lesser than the profit as computed in part a

------------------------------------------------------------------------------------------------------------------------------

c)

In the simulation model, change the production quantity in cell E11 to 50,000 and 70,000 one by one. and note down the average profit, as it appears in cell H4

Simulation results A B C 1 Fresh Toy Company (FTC) 2 Parameters 3 Fixed cost 4 Variable cost 5 Selling price 6 Discounted price 105,000 Average Net Profit 313,513 45 15 Probability of shortage 0.73 Profit Standard Deviation 186406 60,000 15,500 8 Mean Sales Forecast 9 Std Dev of Sales Forecast 10 11 Production Quantity Maximum Net Profit 395000 50,000 12 Probability of loss 0.075 13 Simulation 14 Trial Demand Sales Surplus Total Cost Net Profit Shortage V=MAX(O,Q-D) Yes=1, No=0 15 16 1 17 2 18 3 4 O 0 O SEMIN(D,Q) 50,000 50,000 50,000 50,000 49,042 43,131 50,000 50,000 50,000 50,000 47,238 Revenue Revenue from from Sales Sales of Surplus S*p1 v*p2 2,250,000 0 2,250,000 0 2,250,000 2,250,000 2,206,908 14,364 1,940,881 1,03,040 2,250,000 2,250,000 2,250,000 0 2,250,000 2,125,697 41,434 D 54,273 75,473 63,675 68,793 49,042 43,131 83,094 60,572 72,682 52,149 47,238 17200 958 6,869 Q*c+F 1,855,000 1,855,000 1,855,000 1,855,000 1,855,000 1,855,000 1,855,000 1,855,000 1,855,000 1,855,000 1,855,000 S*p1+V*p 2-Q*c+F) 395,000 395,000 395,000 395,000 366,272 188,921 395,000 395,000 395,000 395,000 312,131 21 22 6 7 8 0 2,762 212 47200 1221 2013 1 OFF 21 coz

Simulation results А в с 1 Fresh Toy Company (FTC) 2 Parameters 3 Fixed cost 4 Variable cost 5 Selling price 6 Discounted price 105,000 Average Net Profit 217,721 45 15 Probability of shortage 0.24 Profit Standard Deviation 355433 60,000 15,500 Maximum Net Profit 595000 8 Mean Sales Forecast 9 Std Dev of Sales Forecast 10 11 Production Quantity 12 13 Simulation 70,000 Probability of loss 0.268 14 Trial Demand Sales Surplus Total Cost Net Profit Shortage Yes=1, No=0 V=MAX(0,Q-D) 2,383 27,427 4,665 6,317 15 16 1 17 2 3 19 4 20 5 21 6 7 23 8 24 9 25 10 26 11 27 12 D 67,617 74,348 42,573 65,335 63,683 79,869 62,778 72,099 60,807 67,891 60,555 55 135 SEMIN(D,Q) 67,617 70,000 42,573 65,335 63,683 70,000 62,778 70,000 60,807 67,891 60,555 55 135 Revenue Revenue from from Sales Sales of Surplus S*p1 V*p2 3,042,780 35,740 3,150,000 1,915,789 4,11,404 2,940,056 69,981 2,865,715 94,762 3,150,000 0 2,825,007 1,08,331 3,150,000 0 2,736,300 1,37,900 3,055,083 31,639 2,724,993 1,41,669 2181 084 222 972 Q*c+F 2,555,000 2,555,000 2,555,000 2,555,000 2,555,000 2,555,000 2,555,000 2,555,000 2,555,000 2,555,000 2,555,000 S*p1+V*p 2-Q*c+F) 523,520 595,000 -227,807 455,038 405,477 595,000 378,338 595,000 319,200 531,722 311,662 149 056 22 7 7,222 9,193 2,109 9,445 14 865 2 555 non

Mean profit associated with production quantity of:

50,000 units : $ 313,513

60,000 units : $ 308,294

70,000 units : $ 217,721

------------------------------------------------------------------------------------------------------------------------------

d)

The other trade-off factors are Probability of shortage and probability of loss.

Table showing all the factors is following:

Production quantity	Expected profit	Probability of shortage	Probability of loss
50,000	313513	0.73	0.075
60,000	308294	0.50	0.138
70,000	217721	0.24	0.268

Looking at the above report, we observe that expected profit is highest for production quantity of 50,000 units.

Also probability of loss is the lowest for this quantity.

Therefore, it is recommended to produce 50,000 units