Please fill out all 3 charts and provide excel formulas! :) The answer key is givin! Just need the charts filled out

Scenario 1 - Aggregate planning Consider the following 12-month forecast for bicycle sales at Ogden Cycles: Jan Forecast 144 Apr Feb 140 Mar 142 May 185 Jun 182 Jul 191 Aug 170 Sep Oct 139 Nov 123 Dec 156 170 152 . . . . . Ogden Cycles manufacturing plant works 20 days per month The production department currently employs 7 workers. The average pay in the production department is $150 per day. Current labor productivity is 1 bike per worker per day. It costs $600 to hire a new production worker and $1,500 to let one go. inventory holding costs are $7 per bike per month Stockout costs are twice as much as holding costs. Overtime is paid at 1.5 times the normal rate but is limited to at most 10 days per worker per month, For quality reasons. Ogden Cycle does not hire temporary workers . . Hints . When should you hire and when should you use overtime? A new hire costs a flat fee of $600 and a labor cost of $3,000 for a total of $3,600 for the first month. Rather than hire someone, the company could use overtime if the overtime cost is less than $3.600, which is 80% of $4,500, the fultime monthly overtime cost (1,5-3,000 = 4,500). Therefore, you should hire if the need exceeds a workor otherwise, use overtime When should you lay off a workor? A layoff costs a flat fee of $1,500 or 5 month of production for one worker. Therefore, you should lot an employee go only if the excess production capacity is greater than 5 month of production; otherwise, koop the workor. . 1.1 Using the Excel spreadsheet provided, calculate the total cost of a chase strategy including labor, hiring, layoff, overtime, inventory holding and backorder costs. 1.2 Calculate the total cost of a level strategy including all relevant costs 1.3 Which strategy would you recommend? Scenaria 2- Scheduling Consider the following sequence of jobs at Elinchbauati Manufacturing: Job arrival sequence . B 000 UI Processing time (hours) Due date (hours from now) 8 48 10 24 4 30 16 60 12 36 2 54 6 18 14 42 E G H 2.1. Calculate the on-time delivery performance under the first-como, first-serve priority rule. (Show your work in a Gantt Chart) 2.2 Calculate the on-time delivery performance under the earliest due date priority rule. (Show your work in a Gantt Chart) 23 Calculate the on-time delivery performance under the shortest processing time priority rule. (Show your work in a Gantt Chart) Answer key 1.1. $297,675 1.2. $290,910 1.3. The level strategy should be selected. 2.1. 62.5% 2.2. 50% 23 50% 1 A C J K L M N 0 1 D E F G H Total cost of a chase strategy Jan May Jun Jul Feb 140 Mar 142 144 Apr 172 Aug 170 Sep 152 Oct 139 Nov 123 Dec 156 185 182 191 Total 1,896 0.00 0.00 2 3 4 Forecast 5 Production 6 Workers needed 7 Hires 8 Layoffs 9 Workers on payroll 10 Overtime 11 Ending Inventory 12 Back orders 13 14 Labor costs 15 Hiring costs 16 Layoff costs 17 Overtime costs 18 Inventory holding costs 19 Backorder costs 20 Total costs 21 22 23 Orden Cycle manufacturing plant works 20 days per month The production department ouvelly employs 7 workers The angepay in the production department is 150 per day Current lahor practivity is biker worker per day It couls 5600 to hire New production worker and 31.500 to let Lavey holding code $7 perbike por month Stockout Corts are wice u holding Overtime is paid 15 times the role but is led to 10 days per worker per now For quality room, Oden Cycle does not hire repay work M N o 1 Total cost of a level strategy Jan 144 Feb 140 Mar 142 Oct Apr 172 May 185 Jun 182 Aug Jul 191 Sep Dec 156 Total 1,896 170 152 139 123 0.00 0.00 2 3 4 Forecast 5 Production 6 Workers needed 7 Hires 8 Layoffs 9 Workers on payroll 10 Overtime 11 Ending Inventory 12 Back orders 13 14 Labor costs 15 Hiring costs 16 Layoff costs 17 Overtime costs 18 Inventory holding costs 19 Backorder costs 20 Total costs 21 22 23 24 Ogden Cycle manufacturing plant works 20 days per month The production department currently oploys 7 workers The average pay in the production department is $150 per day Current labor productivity is I bike per worker per day It cost $600 tone w production worker and $1.500 to lote 20 Inventory holding costs are $7 per bike per month Stockout costs are twice as much as holding cost Overtime paid at times the borrate but is listed to a 10 days por worker pronth For quality reasons, Oyden Cycle del tuttopory workerk A1 Xfx ABCDEFGHII KLMNOPQRSTUVWX Y Z AA AB AC AD AE AF AG AHN AJ AK ALAMAN AD AP AQ 1 Job arrivalsedence Processing time hours Due date hours from now! 2 First come first serve A A 3 8 10 24 4 Job BLOG MASON C 4 30 5 ID 16 60 5 F 12 36 7 2 54 8 G 6 18 9 TH 14 42 10 11 12 11 14 Cartiest Due Date 15 16 lobi DODO 12 18 19 20 21 22 23 24 25 26 Sharest processing time 27 2016 DOGAN 29 30 31 22 33 14 35 16 17