Hello, I want a tutor who can help me with solver.

The question is in the attached file.

I found solution in coursehero without solver in excel. I want you to use solver to do this. Thanks

It is late June, and Sandra, head of operations at Mintendo, and Bill, head of sales of We \"R\" Toys, are about to get together to discuss production and marketing plans for the next six months. Mintendo is the manufacturer of the popular Game Girl hand-held electronic game that is sold exclusively through We \"R\" Toys retail stores. The second half of the year is critical to Game Girl's success, because a majority of its sales occur during the holiday shopping period. Sandra is worried about the impact that the upcoming holiday surge in demand will have on her production line. Costs to subcontract assembly of the Game Girls are expected to increase, and she has been trying to keep costs down given that her bonus depends on the level of production costs. Bill is worried about competing toy stores gaining share during the Christmas buying season. He has seen many companies lose their share by failing to keep prices in line with the performance of their products. He would like to maximize the Game Girl market share. Both Sandra and Bill's teams produce the following joint forecast of demand for the next six months: TABLE 1 Month July August September October November December Demand for Game Girls Demand Forecast 100,000 120,000 140,000 160,000 180,000 200,000 We \"R\" Toys sells Game Girls for $50 apiece. At the end of June, the company has an inventory of 50,000 Game Girls. Capacity of the production facility is set purely by the number of workers assembling the Game Girls. At the end of June, the company has a workforce of 299 employees, each of whom works eight hours of regular time (non-overtime) at $15/hour for 20 days each month. Work rules require that no employee work more than 40 hours of overtime per month. The various costs are shown below: TABLE 2 Costs for Mintendo/We \"R\" Toys Item Cost Material cost $11.55/unit Inventory holding cost $4/unit/month Backlog cost $10/unit/month Hiring and training costs $3,000/worker Layoff cost $5,000/worker Labor hours required 0.25/unit Regular-time cost $15/hour Overtime cost $22.50/hour Cost of subcontracting $18/unit Sandra, concerned about controlling costs during the periods of surging demand over the holidays, proposes to Bill that the price be lowered by $5 for the month of September. This would likely increase September's demand by 50 percent due to new customers being attracted to Game Girl. Additionally, 30 percent of each of the following two months of demand would occur in September as forward buys. She believes strongly that this leveling of demand will help the company. Bill counters with the idea of offering the same promotion in November, during the heart of the buying season. In this case, the promotion increases November's demand by 50 percent due to new customers being attracted to Game Girl. Additionally, 30 percent of December's demand would occur in November as forward buying. Bill wants to increase revenue and sees no better way to do this than to offer a promotion during the peak season. QUESTIONS 1. Which option delivers the maximum profit for the supply chain: Sandra's plan, Bill's plan, or no promotion plan at all? 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? Month June July Aug Sep Oct Nov Dec No promotion Plan Forecast Produced SP/Unit Revenue Holding/unit Holding costs Material/unit material costs Labor hrs rq/unitLabor hours cost/hr 50000 50 2 12 0.25 100000 50000 50 2500000 2 100000 12 600000 0.25 12500 110000 110000 50 5500000 2 220000 12 1320000 0.25 27500 130000 130000 50 6500000 2 260000 12 1560000 0.25 32500 180000 180000 50 9000000 2 360000 12 2160000 0.25 45000 250000 250000 50 12500000 2 500000 12 3000000 0.25 62500 300000 300000 50 15000000 2 600000 12 3600000 0.25 75000 Total 51000000 Profit Labor costs Con/Unit 15 15 15 15 15 15 15 187500 412500 487500 675000 937500 1125000 Contracting costs Total costs 18 100000 18 900000 1787500 18 1980000 3932500 18 2340000 4647500 18 3240000 6435000 18 4500000 8937500 18 5400000 10725000 Total 36565000 14435000 Sandra's proposition Month June July Aug Sep Oct Nov Dec Forecast Produced SP/Unit 50000 100000 50000 110000 110000 324000 324000 126000 126000 175000 175000 300000 300000 Total Revenue Holding/unit Holding costs Material/unit material costs Labor hrs rq/unitLabor hours cost/hr 50 2 100000 12 0.25 50 2500000 2 100000 12 600000 0.25 12500 50 5500000 2 220000 12 1320000 0.25 27500 45 14580000 2 648000 12 3888000 0.25 81000 50 6300000 2 252000 12 1512000 0.25 31500 50 8750000 2 350000 12 2100000 0.25 43750 50 15000000 2 600000 12 3600000 0.25 75000 52630000 Profit Labor costs Con/unit 15 15 15 15 15 15 15 187500 412500 1215000 472500 656250 1125000 18 18 18 18 18 18 18 Contracting costs Total costs 100000 900000 1787500 1980000 3932500 5832000 11583000 2268000 4504500 3150000 6256250 5400000 10725000 Total 38888750 13741250 Bill's proposition Month June July Aug Sep Oct Nov Dec Forecast Produced SP/Unit 50000 100000 50000 110000 110000 324000 324000 126000 126000 465000 465000 210000 210000 Total Revenue 50 50 50 50 45 50 Holding/unit Holding costs Material/unit material costs Labor hrs rq/unitLabor hours cost/hr 2 100000 15 0.25 2500000 2 100000 15 750000 0.25 12500 5500000 2 220000 15 1650000 0.25 27500 16200000 2 648000 15 4860000 0.25 81000 6300000 2 252000 15 1890000 0.25 31500 20925000 2 930000 15 6975000 0.25 116250 10500000 2 420000 15 3150000 0.25 52500 61925000 Profit 12031250 Labor costs Con/unit 15 15 15 15 15 15 15 187500 412500 1215000 472500 1743750 787500 Contracting costs Total costs 18 100000 18 900000 1937500 18 1980000 4262500 18 5832000 12555000 18 2268000 4882500 18 8370000 18018750 18 3780000 8137500 Total 49893750 Note The 'no promotion strategy' provides the perfect value proposition for the company because it earns the highest profits relative to the other plans. b) Discount of 10 given Sandra's proposition Month June July Aug Sep Oct Nov Dec Forecast Produced SP/Unit 50000 100000 50000 110000 110000 324000 324000 126000 126000 175000 175000 300000 300000 Total Revenue 50 50 50 40 50 50 50 Holding/unit Holding costs Material/unit material costs Labor hrs rq/unitLabor hours cost/hr 2 100000 12 0.25 2500000 2 100000 12 600000 0.25 12500 5500000 2 220000 12 1320000 0.25 27500 12960000 2 648000 12 3888000 0.25 81000 6300000 2 252000 12 1512000 0.25 31500 8750000 2 350000 12 2100000 0.25 43750 15000000 2 600000 12 3600000 0.25 75000 51010000 Profit Labor costs Con/unit 15 15 15 15 15 15 15 187500 412500 1215000 472500 656250 1125000 Contracting costs Total costs 18 100000 18 900000 1787500 18 1980000 3932500 18 5832000 11583000 18 2268000 4504500 18 3150000 6256250 18 5400000 10725000 Total 38888750 12121250 Bill's proposition Month June July Aug Sep Oct Nov Dec Forecast Produced SP/Unit 50000 100000 50000 110000 110000 324000 324000 126000 126000 465000 465000 210000 210000 Total Revenue 50 50 50 50 40 50 Holding/unit Holding costs Material/unit material costs Labor hrs rq/unitLabor hours cost/hr 2 100000 15 0.25 2500000 2 100000 15 750000 0.25 12500 5500000 2 220000 15 1650000 0.25 27500 16200000 2 648000 15 4860000 0.25 81000 6300000 2 252000 15 1890000 0.25 31500 18600000 2 930000 15 6975000 0.25 116250 10500000 2 420000 15 3150000 0.25 52500 59600000 Profit 9706250 Labor costs Con/unit 15 15 15 15 15 15 15 187500 412500 1215000 472500 1743750 787500 Contracting costs Total costs 18 100000 18 900000 1937500 18 1980000 4262500 18 5832000 12555000 18 2268000 4882500 18 8370000 18018750 18 3780000 8137500 Total 49893750 Note When a $ 10 is given, it does not affect the no promotion plan, so the profits remain the same. However, reducing the price per unit affects both Sandra's and Bills plans in that it impacts on the profits of company. It scales down the profits as shown from the calculations above. Thus, the decision not to conduct any promotions still remains to be the most viable. c) increase in contracting costs Sandra's proposition Month June July Aug Sep Oct Nov Dec Forecast Produced SP/Unit 50000 100000 50000 110000 110000 324000 324000 126000 126000 175000 175000 300000 300000 Total Revenue 50 50 50 40 50 50 50 Holding/unit Holding costs Material/unit material costs Labor hrs rq/unitLabor hours cost/hr 2 100000 12 0.25 2500000 2 100000 12 600000 0.25 12500 5500000 2 220000 12 1320000 0.25 27500 12960000 2 648000 12 3888000 0.25 81000 6300000 2 252000 12 1512000 0.25 31500 8750000 2 350000 12 2100000 0.25 43750 15000000 2 600000 12 3600000 0.25 75000 51010000 profit Labor costs Con/unit 15 15 15 15 15 15 15 187500 412500 1215000 472500 656250 1125000 Contracting costs Total costs 22 100000 22 1100000 1987500 22 2420000 4372500 22 7128000 12879000 22 2772000 5008500 22 3850000 6956250 22 6600000 11925000 Total 43228750 7781250 Bill's proposition Month June July Aug Sep Oct Nov Dec Forecast Produced SP/Unit 50000 100000 50000 110000 110000 324000 324000 126000 126000 465000 465000 210000 210000 Total Revenue 50 50 50 50 45 50 Holding/unit Holding costs Material/unit material costs Labor hrs rq/unitLabor hours cost/hr 2 100000 15 0.25 2500000 2 100000 15 750000 0.25 12500 5500000 2 220000 15 1650000 0.25 27500 16200000 2 648000 15 4860000 0.25 81000 6300000 2 252000 15 1890000 0.25 31500 20925000 2 930000 15 6975000 0.25 116250 10500000 2 420000 15 3150000 0.25 52500 61925000 Profit 6891250 Labor costs Con/unit 15 15 15 15 15 15 15 187500 412500 1215000 472500 1743750 787500 Contracting costs Total costs 22 100000 22 1100000 2137500 22 2420000 4702500 22 7128000 13851000 22 2772000 5386500 22 10230000 19878750 22 4620000 8977500 Total 55033750 Note From the above, it can be deduced that the increase in contracting costs scales down the profits of the two plans.However, Sandra's plan seems to supercede Bill's as far as profits are concerned. It is also imperative to note that as a result, the company should always pick Sandra's plan.This is because it yields higher profits at less costs as well. It appears that the strategy works out succintly to ignite the necessary demand while keeping costs down.it still remains the best the company can undertake. However, with the option no to conduct promotions still applicable. A 1 B C D E F G H I J Aggregate Plan Decision Variables KL M N O P Aggregate Plan Constraints 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Period 0 1 2 3 4 5 6 7 8 9 10 11 12 Ht Hires Lt Layoffs 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Wt Workers 1250 1250 1250 1250 1250 1250 1250 1250 1250 1250 1250 1250 1250 Ot Overtime 0 0 25000 25000 25000 25000 25000 0 0 0 25000 25000 It Inventory 50000 50,000 150,000 500,000 650,000 500,000 250,000 0 0 50,000 450,000 400,000 50,000 St Stockout Ct Subcontract Pt Production 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1,000,000 1,200,000 1,350,000 1,350,000 1,350,000 1,350,000 1,350,000 900,000 1,150,000 1,200,000 1,350,000 1,350,000 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Aggregate Plan Costs Unit Cost $99,999 Period Hiring 1 $0 2 $0 3 $0 4 $0 5 $0 6 $0 7 $0 8 $0 9 $0 10 $0 11 $0 12 $0 Subtotal $0 $99,999 Layoff $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 34 35 Total Cost $360,400,000 $3,200 Regular Time $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $48,000,000 $30 Overtime $0 $0 $750,000 $750,000 $750,000 $750,000 $750,000 $0 $0 $0 $750,000 $750,000 $5,250,000 $3 Inventory $150,000 $450,000 $1,500,000 $1,950,000 $1,500,000 $750,000 $0 $0 $150,000 $1,350,000 $1,200,000 $150,000 $9,150,000 $99,999 Stockout $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $99,999 Subcontract $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $20 Material $20,000,000 $24,000,000 $27,000,000 $27,000,000 $27,000,000 $27,000,000 $27,000,000 $18,000,000 $23,000,000 $24,000,000 $27,000,000 $27,000,000 $298,000,000 Demand 1,000,000 1,100,000 1,000,000 1,200,000 1,500,000 1,600,000 1,600,000 900,000 1,100,000 800,000 1,400,000 1,700,000 14,900,000 `=` Workforce 0 0 0 0 0 0 0 0 0 0 0 0 ` >= ` Production 200000 0 0 0 0 0 0 300000 50000 0 0 0 Units/Month Units/Hour 960 6 OT Limit 20 `=` ` >= ` Inventory Overtime 0 25000 0 25000 0 0 0 0 0 0 0 0 0 0 0 25000 0 25000 0 25000 0 0 -4.7E-010 0 A 1 B C D E F G H I J Aggregate Plan Decision Variables KL M N O P Aggregate Plan Constraints 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Period 0 1 2 3 4 5 6 7 8 9 10 11 12 Ht Hires Lt Layoffs 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Wt Workers 1250 1250 1250 1250 1250 1250 1250 1250 1250 1250 1250 1250 1250 Ot Overtime 0 0 0 0 50000 50000 50000 0 0 0 8333 50000 It Inventory 50000 0 0 200,000 200,000 200,000 100,000 0 0 0 400,000 250,000 50,000 St Stockout Ct Subcontract Pt Production 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 950,000 1,100,000 1,200,000 1,200,000 1,500,000 1,500,000 1,500,000 900,000 1,100,000 1,200,000 1,250,000 1,500,000 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Aggregate Plan Costs Unit Cost $99,999 Period Hiring 1 $0 2 $0 3 $0 4 $0 5 $0 6 $0 7 $0 8 $0 9 $0 10 $0 11 $0 12 $0 Subtotal $0 $99,999 Layoff $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 34 35 Total Cost $356,450,000 $3,200 Regular Time $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $4,000,000 $48,000,000 $30 Overtime $0 $0 $0 $0 $1,500,000 $1,500,000 $1,500,000 $0 $0 $0 $250,000 $1,500,000 $6,250,000 $3 Inventory $0 $0 $600,000 $600,000 $600,000 $300,000 $0 $0 $0 $1,200,000 $750,000 $150,000 $4,200,000 $99,999 Stockout $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $99,999 Subcontract $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $20 Material $19,000,000 $22,000,000 $24,000,000 $24,000,000 $30,000,000 $30,000,000 $30,000,000 $18,000,000 $22,000,000 $24,000,000 $25,000,000 $30,000,000 $298,000,000 Demand 1,000,000 1,100,000 1,000,000 1,200,000 1,500,000 1,600,000 1,600,000 900,000 1,100,000 800,000 1,400,000 1,700,000 14,900,000 `=` Workforce 0 0 0 0 0 0 0 0 0 0 0 0 ` >= ` Production 250000 100000 0 0 0 0 0 300000 100000 0 0 0 Units/Month Units/Hour 960 6 OT Limit 40 `=` ` >= ` Inventory Overtime 0 50000 0 50000 0 50000 0 50000 0 0 0 0 0 0 0 50000 0 50000 0 50000 0 41666.667 0 0 A 1 B C D E F G H I J Aggregate Plan Decision Variables KL M N O P Aggregate Plan Constraints 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Period 0 1 2 3 4 5 6 7 8 9 10 11 12 Ht Hires Lt Layoffs 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Wt Workers 1300 1300 1300 1300 1300 1300 1300 1300 1300 1300 1300 1300 1300 Ot Overtime 0 0 0 0 47333 52000 52000 0 0 0 0 52000 It Inventory 50000 0 0 0 48,000 80,000 40,000 0 0 0 342,000 190,000 50,000 St Stockout Ct Subcontract Pt Production 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 950,000 1,100,000 1,000,000 1,248,000 1,532,000 1,560,000 1,560,000 900,000 1,100,000 1,142,000 1,248,000 1,560,000 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Aggregate Plan Costs Unit Cost $99,999 Period Hiring 1 $0 2 $0 3 $0 4 $0 5 $0 6 $0 7 $0 8 $0 9 $0 10 $0 11 $0 12 $0 Subtotal $0 $99,999 Layoff $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 34 35 Total Cost $356,270,000 $3,200 Regular Time $4,160,000 $4,160,000 $4,160,000 $4,160,000 $4,160,000 $4,160,000 $4,160,000 $4,160,000 $4,160,000 $4,160,000 $4,160,000 $4,160,000 $49,920,000 $30 Overtime $0 $0 $0 $0 $1,420,000 $1,560,000 $1,560,000 $0 $0 $0 $0 $1,560,000 $6,100,000 $3 Inventory $0 $0 $0 $144,000 $240,000 $120,000 $0 $0 $0 $1,026,000 $570,000 $150,000 $2,250,000 $99,999 Stockout $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $99,999 Subcontract $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $20 Material $19,000,000 $22,000,000 $20,000,000 $24,960,000 $30,640,000 $31,200,000 $31,200,000 $18,000,000 $22,000,000 $22,840,000 $24,960,000 $31,200,000 $298,000,000 Demand 1,000,000 1,100,000 1,000,000 1,200,000 1,500,000 1,600,000 1,600,000 900,000 1,100,000 800,000 1,400,000 1,700,000 14,900,000 `=` Workforce 0 0 0 0 0 0 0 0 0 0 0 0 ` >= ` Production 298000 148000 248000 0 0 0 0 348000 148000 106000 0 0 Units/Month Units/Hour 960 6 OT Limit 40 `=` ` >= ` Inventory Overtime 0 52000 0 52000 0 52000 1.96E-010 52000 3.35E-010 4666.6667 0 0 0 0 0 52000 0 52000 0 52000 0 52000 0 0 (a) Worksheet provides the solution to this problem and the corresponding aggregate plan. The total cost of the plan is $360,400,000. (b) If the number of overtime hours per employee were increased from 20 to 40 it would result in decreasing the total cost to $356,450,000. So, it is advantageous to do it. (c) If the number of employees is decreased to 1200 and the overtime hours per employee are held at 20 and 40 then the total costs of the plan are $363,324,000 and $357,422,000, respectively. If the number of employees is increased to 1300 and the overtime hours per employee are held at 20 and 40 then the total costs of the plan are $358,790,000 and $356,270,000, respectively. So, the value of additional overtime increases as workforce size decreases. Starting Workforce 20-hr limit 40-hr limit Difference 1200 1300 $363,324,000 $358,790,000 $357,422,000 $356,270,000 $5,902,000 $2,520,000