if you want to solve with excel

4. FCC Company manufactures boats and distributes them across the country. Part 1E-365 is the most expensive part used in boat production so that its production should be carefully planned. The table below shows the demand forecast for this part for the next 12 months. Labor cost of producing one unit at regular time is $40. Inventory holding and backordering costs are $2 and $4 per unit per month, respectively. Month 1 2 3 4 5 6 7 8 9 10 11 12 Demand (units) 300 600 300 400 800 900 1600 1800 800 800 500 800 (a) Suppose that 20 workers are employed in each month, and the monthly output on regular time per person is 50 units. Determine the number of units left in inventory at the end of each month. Calculate the total labor cost, total inventory holding cost, total backordering cost, and the total cost of this constant production plan. Determine the month having the maximum end-of-month inventory. Also, determine the month having the maximum backorder quantity. (b) Using the Microsoft Excel, plot the cumulative demand and the cumulative production over time for the production plan developed in part (a). (C) Suppose that the company do not want to keep no units in inventory at the end of the planning horizon. Determine the constant number of workers to be employed in each month. Determine the number of units left in inventory at the end of each month. Calculate the total labor cost, total inventory holding cost, total backordering cost, and the total cost of this constant production plan. (d) Using the Microsoft Excel, plot the cumulative demand and the cumulative production over time for the production plan developed in part (c). (e) Suppose that the company may vary the work force to meet demand with no units in inventory at the end of each month. Each increase or decrease in the workforce level has a cost of $500 per worker. For this production plan, determine the number of units produced in each month, and the number of workers employed in each month. Calculate the total labor cost, including the cost of change in work force levels, and the total cost of this constant production plan. ( Which of the plans in parts (a), (c) and (e) do you select for implementation? Why