Please help me solve this (use excel and show formulas), "Develop an S&OP with the level strategy using overtime, undertime, and vacations as the only supply options. Use the maximum amount of overtime so as to minimize undertime. What is the total cost of this plan?

I would greatly appreciate it. Hint: USE THE LEVEL STRATEGY.

Below is the table that I need to complete. Please USE EXCEL.

Assignment Content The Kool King Company has followed a policy of no layoffs for most of the manufacturer's life, even though the demand for its air conditioners is highly seasonal. Management wants to evaluate the cost effectiveness of this policy. Competitive pressures are Increasing, and ways need to be found to reduce costs. The following demand (expressed in employee-month equivalents) has been forecast for next year: C . 70 Jan. Feb. Sept. Oct. G May June July Aug. 90 100 100 130 170 170 150 110 60 20 40 Mar Nov. Apr. Dec Additional planning data follow, with costs, inventory, and backorders expressed in employee-month equivalents: $1,500 Hire cost $500/person Regular-time production cost Overtime production cost Subgontracting 150% of regular- time production cost Layoff cost $2,000/person regeringen 10 0 hveplovy Tooling cest Backender coet $, Maximum 20% of regular- overtime time capacity Current backorders Current inventory Desired ending inventory Current employment 0 130 employees Hiring costs are lower than layoff costs because the facility is located near a technical training school. Undertime is paid at the rate equivalent to regular-time production. Each employee who has been with the company for at least 1 year also received 0.5 month of paid vacation. All 130 employees currently employed qualify for vacations next year, assuming that they remain on the workforce. Answer the following questions using an Excel spreadsheet that you developed on your own. Course Learning Outcomes: L06 . Consider the initial backorder and vacation time but ignore inventory and subcontracting!! . All excess demand will be handled using overtime. (No backorder is used.) You need to submit a written report along with an Excel file that shows your work. So, you don't need to provide answers below again. Question 1 10 Points Develop an S&OP with the level strategy using overtime, undertime, and vacations as the only supply options. Use the maximum amount of overtime so as to minimize undertime. What is the total cost of this plan? Use the editor to format your answer Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total 70 90 100 100 130 170 170 150 110 60 20 40 1210 130 10 Time Period Inputs Forecasted demand Workforce level Undertime Overtime Vacation time Backorders Derived Utilized time Hires Layoffs Calculated Utilized time cost Undertime cost Overtime cost Vacation time cost Backorder cost Hires cost Layoffs cost Total Cost Assignment Content The Kool King Company has followed a policy of no layoffs for most of the manufacturer's life, even though the demand for its air conditioners is highly seasonal. Management wants to evaluate the cost effectiveness of this policy. Competitive pressures are Increasing, and ways need to be found to reduce costs. The following demand (expressed in employee-month equivalents) has been forecast for next year: C . 70 Jan. Feb. Sept. Oct. G May June July Aug. 90 100 100 130 170 170 150 110 60 20 40 Mar Nov. Apr. Dec Additional planning data follow, with costs, inventory, and backorders expressed in employee-month equivalents: $1,500 Hire cost $500/person Regular-time production cost Overtime production cost Subgontracting 150% of regular- time production cost Layoff cost $2,000/person regeringen 10 0 hveplovy Tooling cest Backender coet $, Maximum 20% of regular- overtime time capacity Current backorders Current inventory Desired ending inventory Current employment 0 130 employees Hiring costs are lower than layoff costs because the facility is located near a technical training school. Undertime is paid at the rate equivalent to regular-time production. Each employee who has been with the company for at least 1 year also received 0.5 month of paid vacation. All 130 employees currently employed qualify for vacations next year, assuming that they remain on the workforce. Answer the following questions using an Excel spreadsheet that you developed on your own. Course Learning Outcomes: L06 . Consider the initial backorder and vacation time but ignore inventory and subcontracting!! . All excess demand will be handled using overtime. (No backorder is used.) You need to submit a written report along with an Excel file that shows your work. So, you don't need to provide answers below again. Question 1 10 Points Develop an S&OP with the level strategy using overtime, undertime, and vacations as the only supply options. Use the maximum amount of overtime so as to minimize undertime. What is the total cost of this plan? Use the editor to format your answer Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total 70 90 100 100 130 170 170 150 110 60 20 40 1210 130 10 Time Period Inputs Forecasted demand Workforce level Undertime Overtime Vacation time Backorders Derived Utilized time Hires Layoffs Calculated Utilized time cost Undertime cost Overtime cost Vacation time cost Backorder cost Hires cost Layoffs cost Total Cost