Please use the solver function of excel ( explain the detail after running the solver and how you get the answer), thank you!

Q1) The S&OP team at Kansas Furniture, has received the following estimates of demand require a) Assuming one-time stockout costs for lost sales of $100 per unit, inventory carrying costs of $25 per unit per month, and zero beginning and ending inventory, evaluate these two plans on an incremental cost basis:

? Plan A: Produce at a steady rate (equal to minimum requirements) of 1,000 units per month and subcontract additional units at a $60 per unit premium cost.

? Plan B: Vary the workforce, to produce the prior month's demand. The firm produced 1,300 units in June. The cost of hiring additional workers is $3,000 per 100 units produced.

The cost of layoff s is $6,000 per 100 units cut back.

Note: Both hiring and layoff costs are incurred in the month of the change, (i.e. going from production of 1,300 in July to 1,000 in August requires a layoff (and related costs) of 300 units in August, just as going from production of 1,000 in August to 1,200 in September requires hiring (and related costs) of 200 units in September).

b) Which plan is best and why? comments:

S25 X V fx A B C D E F G H I J K L M N 0 P Q R S T U V Plan A: Produce at a steady rate (equal to minimum requirements) of 1,000 units per month and subcontract additional units at a $60 per unit premium cost Plan B: Vary the workforce, to produce the prior month's demand. The firm produced 1,300 units in June. The cost of hiring additional workers is $3,000 per 100 units produced. 10 The cost of layoff s is $6,000 per 100 units cut back. 12 Note: Both hiring and layoff costs are incurred in the month of the change, (i.e. going from production of 1,300 in July to 1,000 in August 13 requires a layoff (and related costs) of 300 units in August, just as going from production of 1,000 in August to 1,200 in September requires hiring (and related costs) of 200 units in September). 16 ) Which plan is best and why?comments: 17 18 19 Plan A: Minimum rate of 1,000/month, subcontract for additional 20 21 Period Demand Production Inventorry (ending) Subcontract Extra cost 22 Jul 1000 1000 23 Aug 1200 1000 24 Sep 1400 1000 25 Oct 1800 1000 26 Nov 1800 1000 27 Dec 1800 1000 28 0 Total Cost 29 30 Plan B: Vary workforce to match prior month' s demand. 31 32 Demand Production Hire (units) @ 3000/100 Layoff (units) @ 3000/100 Inventory (units) @$100 EA Shotage (units) @ 100 Ea 33 Jul 1000 130 34 Aug 1200 1000 35 Sep 1400 1200 36 Oct 1800 1400 37 Nov 1800 1800 38 Dec 1800 1800 39 Total 0 40 41 b ) 42 43 44 45 46 47 48 49 50 Q1 (2) 01 + + 100%