Spreadsheet should calculate minimum total cost by adding regular cost, overtime cost, and holding cost. similar to the problem and spreadsheet pictured here. I can solve parts after A but can't figure out how to set up the spreadsheet.

1. Power House, Inc. manufactures power generators among other products. The sales department has the following forecast of power generators for the first 6 months of next year as follows. January February March April May June 3000 2200 1800 2700 3300 1500 The division responsible for producing power generators is planning a production plan. As an assistant to the division VP, you are assigned to complete an initial plan for the management to review. Production cost per unit during regular time is $300 per unit. The overtime cost is $400 per unit. The cost to hold one unit of the product from one month to the next is $30 on average. The regular production capacity per month is 2,500 units; any additional production in a month is considered as overtime, which cannot be more than 500 units. The inventory level at the beginning of January is expected to be 500 units. The minimum inventory at the end of June must be 600 units. a) Formulate the problem as a linear program on a spreadsheet to find the optimal production plan that minimizes the total production and inventory costs. Use proper colors and range names. b) Solve the problem optimally. What is the total cost? What is the total overtime cost? What is the total inventory cost? c) Create a table with the regular production, overtime production, initial and ending inventory of each month d) Summarize the production and inventory quantities and corresponding costs in March in a table e) Formulate the problem as a mathematical (algebraic) model to find the optimal production plan that minimizes total production and inventory costs. Define the decision variables. Describe constraints, and the objective function for the problem. A manufacturer employs skilled workers to make its wooden product. At the beginning of January, it has 15 skilled workers. Each worker can work 200 hours per month. The labor hours required for the next 12 month are as follows 1 2 3 4 5 6 17 8 9 10 11 12 (Jan) 3000 3200 2400 2000 1800 2000 3000 3500 4000 4200 3800 3200 Each skilled worker costs $4,000 per month. Hiring a new worker as a trainee costs $3,000. In addition, a trainee is paid $2,000 for the first month and is not involved in production. A trainee becomes a skilled worker starting in month 2. The manufacturer can lay off skilled workers at the beginning of a month, but must pay $3,000 for severance. The union contract allows no more than 10% of the skilled workers to be laid off in any month. How many trainees should be hired and how many skilled workers should be laid off each month to minimize the total cost? a) Formulate the problem as a linear program on the spreadsheet. 10 1 dan) 3 1.000 100 5,000 33.000 bons DO DOSYE pos 00 Layoff Hi Cosi Regular Pay Train Pay 33.000 51.000 $4.000 Total 515,000 524 000 579,000 516,000 Ho 5400 H.000 oors Total Cow 55100 10an) 4 10 4.00 Her Required 10 TO 1200 Heurs Avaliable 00 20 M NE WE DO 00 ANO 4200 1600 16 19 13 15 16 15 15 16 15 14 15 14 14 Sated Workers Layoff Silled Workers After Max laul Wuchen Landel Trained 14 14 14 15 15 15 IN IN IN 21 19 20 20 2 21 61 . 1 1 0 0 2 1 D 1. Power House, Inc. manufactures power generators among other products. The sales department has the following forecast of power generators for the first 6 months of next year as follows. January February March April May June 3000 2200 1800 2700 3300 1500 The division responsible for producing power generators is planning a production plan. As an assistant to the division VP, you are assigned to complete an initial plan for the management to review. Production cost per unit during regular time is $300 per unit. The overtime cost is $400 per unit. The cost to hold one unit of the product from one month to the next is $30 on average. The regular production capacity per month is 2,500 units; any additional production in a month is considered as overtime, which cannot be more than 500 units. The inventory level at the beginning of January is expected to be 500 units. The minimum inventory at the end of June must be 600 units. a) Formulate the problem as a linear program on a spreadsheet to find the optimal production plan that minimizes the total production and inventory costs. Use proper colors and range names. b) Solve the problem optimally. What is the total cost? What is the total overtime cost? What is the total inventory cost? c) Create a table with the regular production, overtime production, initial and ending inventory of each month d) Summarize the production and inventory quantities and corresponding costs in March in a table e) Formulate the problem as a mathematical (algebraic) model to find the optimal production plan that minimizes total production and inventory costs. Define the decision variables. Describe constraints, and the objective function for the problem. A manufacturer employs skilled workers to make its wooden product. At the beginning of January, it has 15 skilled workers. Each worker can work 200 hours per month. The labor hours required for the next 12 month are as follows 1 2 3 4 5 6 17 8 9 10 11 12 (Jan) 3000 3200 2400 2000 1800 2000 3000 3500 4000 4200 3800 3200 Each skilled worker costs $4,000 per month. Hiring a new worker as a trainee costs $3,000. In addition, a trainee is paid $2,000 for the first month and is not involved in production. A trainee becomes a skilled worker starting in month 2. The manufacturer can lay off skilled workers at the beginning of a month, but must pay $3,000 for severance. The union contract allows no more than 10% of the skilled workers to be laid off in any month. How many trainees should be hired and how many skilled workers should be laid off each month to minimize the total cost? a) Formulate the problem as a linear program on the spreadsheet. 10 1 dan) 3 1.000 100 5,000 33.000 bons DO DOSYE pos 00 Layoff Hi Cosi Regular Pay Train Pay 33.000 51.000 $4.000 Total 515,000 524 000 579,000 516,000 Ho 5400 H.000 oors Total Cow 55100 10an) 4 10 4.00 Her Required 10 TO 1200 Heurs Avaliable 00 20 M NE WE DO 00 ANO 4200 1600 16 19 13 15 16 15 15 16 15 14 15 14 14 Sated Workers Layoff Silled Workers After Max laul Wuchen Landel Trained 14 14 14 15 15 15 IN IN IN 21 19 20 20 2 21 61 . 1 1 0 0 2 1 D