please solve using excel and show all steps/ equations!!!!

Read the Mintendo Game Girl on page 257 of the course text. Sandra is the head of operations at Mintendo, the manufacturer of Mintendo Game Girl toy and Bill is the head of sales at We "R" Toys, a retailer of Mintendo Game Girl toy. The two have come up with a joint forecast for the toy and are deciding when to offer a promotion on the toy. Sandra wants to offer the promotion in September while Bill wants to offer it in the month of November. Your job is to assist Sandra and Bill to decide on the timing of the promotion. There are three promotion plans to be analyzed. Promotion plan 1, lowers the price of the toy by $5; promotion plan 2, calls for a $10 reduction on the price of the toy and promotion plan 3, deals with offering a $5 discount while raising the cost of subcontracting from $18 per unit to $22 per unit. There are two deliverables for this case study. The first is a written report of maximum three pages. The second deliverable is the excel model developed for the case study. In the report, want you introduce the case, identify and discuss any assumptions you make, discuss your case analysis approach and then make recommendation on the thee promotion plans discussed above. Please upload the two Cost Mintendo Game Girl It is late June, and Sandra Huchim, head of operations at Mintendo, and Bill Smith, head of sales of We "R" Toys, TABLE 9-8 Costs for Mintendo/We "R" Toys are about to get together to discuss production and mar. Item 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 $10/unit/month stores. The second half of the year is critical to Game Marginal cost of a stockout $3,000/worker Girl's success, because a majority of its sales occur dur Hiring and training costs 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 Overtime cost $22.50/hour are expected to increase, and she has been trying to keep 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 Girl 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 Questions TABLE 9-7 Demand for Game Girls Month Demand Forecast 1. Which option delivers the maximum profit for the supply chain: Sandra's plan, Bill's plan, or no promotion plan at July 100,000 all? Assure starting and ending inventory of 0. August 110,000 2. How does the answer change if a discount of $10 must be September given to reach the same level of impact that the S5 discount 130,000 received? October 180,000 3. Suppose Sandra's fears about increasing outsourcing costs November 250,000 come to fruition and the cost rises to $22/unit for subcon- tracting. Does this change the decision when the discount December 300,000 is SS