Can you include the solver steps and the formulas used in each cell to calculate the answer?

CASE STUDY Mintendo Game Girl It is late June, and Sandra Huchim, head of operations at TABLE 9-8 Costs for Mintendo/We "R" Toys Mintendo, and Bill Smith, head of sales of We "R" Toys, are about to get together to discuss production and mar- Item Cost keting plans for the next 6 months. Mintendo is the man- Material cost $12/unit ufacturer of the popular Game Girl handheld electronic Inventory holding cost $2/unit/month game that is sold exclusively through We "R" Toys retail Marginal cost of a stockout $10/unit/month stores. The second half of the year is critical to Game Girl's success, because a majority of its sales occur dur- Hiring and training costs $3,000/ worker ing the holiday shopping period. Layoff cost $5,000/worker Sandra is worried about the impact that the upcom- Labor hours required 0.25/unit ing holiday surge in demand will have on her production Regular-time cost $15/hour line. Costs to subcontract assembly of the Game Girls are expected to increase, and she has been trying to keep $22.50/hour Overtime cost costs down, given that her bonus depends on the level of Cost of subcontracting $18/unit production costs. Bill is worried about competing toy stores gaining overtime per month. The various costs are shown in share in the handheld electronic game market during the Table 9-8. Christmas buying season. He has seen many companies Sandra, concerned about controlling costs during lose their share by failing to keep prices in line with the the periods of surging demand over the holidays, pro- performance of their products. He would like to maxi- poses to Bill that the price be lowered by $5 for the mize the Game Gid market share in the handheld elec- month of September. This would likely increase Septem- tronic game market. ber's demand by 50 percent due to new customers being Both Sandra's and Bill's teams produce a joint attracted to Game Girl. In addition, 30 percent of each of forecast of demand over the next six months, as shown the following two months of demand would occur in in Table 9-7. September as forward buys. She believes strongly that We "R" Toys sells Game Girls for $50 apiece. At this leveling of demand will help the company. the end of June, the company has an inventory of 50,000 Bill counters with the idea of offering the same Game Girls. Capacity of the production facility is set promotion in November, during the heart of the buying purely by the number of workers assembling the Game season. In this case, the promotion increases Novem- Girls. At the end of June, the company has a workforce ber's demand by 50 percent, owing to new customers of 300 employees, each of whom works 8 hours of regu- being attracted to Game Girl. Additionally, 30 percent of lar time at $15/hour for 20 days each month. Work rules December's demand would occur in November as for- require that no employee work more than 40 hours of ward buying. Bill wants to increase revenue and sees no better way to do this than to offer a promotion during the peak season. TABLE 9-7 Month July August September October November December Demand for Game Girls Demand Forecast 100,000 110,000 130,000 180,000 250,000 300,000 Questions 1. Which option delivers the maximum profit for the supply chain: Sandra's plan, Bill's plan, or no promotion plan at all? Assume starting and ending inventory of O. 2. How does the answer change if a discount of $10 must be given to reach the same level of impact that the $5 discount received? 3. Suppose Sandra's fears about increasing outsourcing costs come to fruition and the cost rises to $22/unit for subcon- tracting. Does this change the decision when the discount is $5? Demand Forecast Aggregate Planning Demand Forecast Month January February March April May June Costs Item Materials cost/unit Inventory holding cost/unit/month Marginal cost of stockout/unit/month Hiring and training cost/worker Layoff cost/worker Labor hours required/unit Regular time cost/hour Over time cost/hour Marginal subcontracting cost/unit Cost Aggregate Plan Decision Variables | Ht Lt Wt Period # Hired # Laid off # Workforce Ot It Overtime Inventory St Stockout Ct Pt Subcontract Production Demand Price Aggregate Plan Costs Period Hiring Lay off Regular time Overtime Inventory Stockout Subcontract Material NO Total Cost = Total Revenue = Profit =