PLEASE PROVIDE FORUMLAS.
The ATV Corporation makes three models of all-terrain vehicles: Model A, Model B, and Model C. Model A uses a 0.4-liter engine, Model uses a 0.5-liter engine, and Model C uses a 0.6-liter engine. The aggregate production plan is the twelve-month plan that combines all three models together in total monthly production. The planning horizon is twelve months. The APP determines the size of the workforce, which is the constrained resource. Assume that the beginning inventory for January is 100 units (30 units each of Model A and Model B, and 40 units of Model C). The firm desires to have an ending inventory of 140 units at the end of the year. On average, one unit of ATV requires eight labor hours to produce, and a worker contributes 160 hours (8 hours x 5 days x 4 weeks) per month. The data has been collected in the Microsoft Excel Online file below. Use the Microsoft Excel Online file below to develop the level production strategy and answer the following questions. Open spreadsheet Questions 1. What are the totals of the forecast demand (including inventory adjustment for December), production, hours and workers associated with aggregate production plan? (Hint: find the total number of workers as the sum of workers required during each month.) Round your answers to the nearest whole number. Forecast Demand Production Capacity Needed Hours Workers (Units) (Units) Production Forecast Demand (Units) Capacity Needed Hours Workers (Units) Excel template Saved Simplified Ribbon File Home Insert Formulas Data Review View Help Tell me what you want to do Editing Comments Arial v 10 B Nov v ake General 60 .00 00 0 Ev 47 v Ov F9 fx B C D E F G H 1 L M N O P 3 4 5 6 140 8 160 Desired End of Year Inventory Worker hours needed per unit Worker hours per month (8 hrs x 5 days x 4 weeks) Ending Inventory Last December December Inventory Adjustment Desired Monthly Production 100 7 8 9 Formulas #N/A #N/A 10 11 Capacity Needed (Labor) Capacity Needed (Labor) Total Forecast Demand (Units)** Production (Units) Ending Inventory Backlog (Units) Hours Workers Ending Inventory/ Backlog (Units) #N/A #N/A #N/A #N/A #N/A 12 Period 13 January 14 February 15 March 16 April 17 May 18 June 19 July 20 August 21 September 22 October 23 November 24 December 25 TOTALS Forecast Demand (Units)* 140 100 280 460 580 700 820 680 580 600 260 280 5,480 Total Forecast Demand (Units)** #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Production (Units) #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Hours #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Workers #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A * to.Adina The ATV Corporation makes three models of all-terrain vehicles: Model A, Model B, and Model C. Model A uses a 0.4-liter engine, Model uses a 0.5-liter engine, and Model C uses a 0.6-liter engine. The aggregate production plan is the twelve-month plan that combines all three models together in total monthly production. The planning horizon is twelve months. The APP determines the size of the workforce, which is the constrained resource. Assume that the beginning inventory for January is 100 units (30 units each of Model A and Model B, and 40 units of Model C). The firm desires to have an ending inventory of 140 units at the end of the year. On average, one unit of ATV requires eight labor hours to produce, and a worker contributes 160 hours (8 hours x 5 days x 4 weeks) per month. The data has been collected in the Microsoft Excel Online file below. Use the Microsoft Excel Online file below to develop the level production strategy and answer the following questions. Open spreadsheet Questions 1. What are the totals of the forecast demand (including inventory adjustment for December), production, hours and workers associated with aggregate production plan? (Hint: find the total number of workers as the sum of workers required during each month.) Round your answers to the nearest whole number. Forecast Demand Production Capacity Needed Hours Workers (Units) (Units) Production Forecast Demand (Units) Capacity Needed Hours Workers (Units) Excel template Saved Simplified Ribbon File Home Insert Formulas Data Review View Help Tell me what you want to do Editing Comments Arial v 10 B Nov v ake General 60 .00 00 0 Ev 47 v Ov F9 fx B C D E F G H 1 L M N O P 3 4 5 6 140 8 160 Desired End of Year Inventory Worker hours needed per unit Worker hours per month (8 hrs x 5 days x 4 weeks) Ending Inventory Last December December Inventory Adjustment Desired Monthly Production 100 7 8 9 Formulas #N/A #N/A 10 11 Capacity Needed (Labor) Capacity Needed (Labor) Total Forecast Demand (Units)** Production (Units) Ending Inventory Backlog (Units) Hours Workers Ending Inventory/ Backlog (Units) #N/A #N/A #N/A #N/A #N/A 12 Period 13 January 14 February 15 March 16 April 17 May 18 June 19 July 20 August 21 September 22 October 23 November 24 December 25 TOTALS Forecast Demand (Units)* 140 100 280 460 580 700 820 680 580 600 260 280 5,480 Total Forecast Demand (Units)** #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Production (Units) #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Hours #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Workers #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A * to.Adina
