Please solve this in excel and show formulas as well as solutions
Question 11 4 pts I strongly suggest using Excel to setup the aggregate plan associated with these questions. A key hospital supplier, IVs Plus (IVP) located in Salina, KS sells IV tubing and stands to hospitals and clinics. Sales have picked up ever since they introduced their newest Squeaky Clean IV stand, which eliminates all oils and germs left behind by users. Though IVP sells these stands all year long, they sell the most during the summer months, when end-of- fiscal year purchases are at a peak. The demand over the next 12 months is shown in the table below. Use the demand forecasts and determine the lowest cost production plan. Month Month Demand Forecast Demand Forecast January 133,067 July 251,630 February 155,026 August 249,630 March 168,200 September 200,312 April 173,890 October 160,830 May 202,759 November 145,266 June 260,842 December 128,900 Regular production cost $80 per unit Holding cost $23 per unit per month based on ending inventory Backorder cost $35.00 per unit per month based on ending inventory Beginning Inventory 650,000 units Beginning workforce 21 employees Regular production rate 5,600 units per employee per month Hiring cost $9,000 per worker Firing cost $12,000 per worker Produce at a level rate using regular time production only. Backlogs are allowed in any month except December. Ending inventory is allowed in any month. Ending inventory for December should be as low as possible. What is the hiring or firing cost in the first month following the level production strategy? Question 11 4 pts I strongly suggest using Excel to setup the aggregate plan associated with these questions. A key hospital supplier, IVs Plus (IVP) located in Salina, KS sells IV tubing and stands to hospitals and clinics. Sales have picked up ever since they introduced their newest Squeaky Clean IV stand, which eliminates all oils and germs left behind by users. Though IVP sells these stands all year long, they sell the most during the summer months, when end-of- fiscal year purchases are at a peak. The demand over the next 12 months is shown in the table below. Use the demand forecasts and determine the lowest cost production plan. Month Month Demand Forecast Demand Forecast January 133,067 July 251,630 February 155,026 August 249,630 March 168,200 September 200,312 April 173,890 October 160,830 May 202,759 November 145,266 June 260,842 December 128,900 Regular production cost $80 per unit Holding cost $23 per unit per month based on ending inventory Backorder cost $35.00 per unit per month based on ending inventory Beginning Inventory 650,000 units Beginning workforce 21 employees Regular production rate 5,600 units per employee per month Hiring cost $9,000 per worker Firing cost $12,000 per worker Produce at a level rate using regular time production only. Backlogs are allowed in any month except December. Ending inventory is allowed in any month. Ending inventory for December should be as low as possible. What is the hiring or firing cost in the first month following the level production strategy
