Solution is generated using Excel Solver as follows:
Solver parameters:
After creating the excel model using the given formulas and data, and entering the solver parameters, Click Solve button to generate optimal solution:
EXCEL FORMULAS:
Excel Formulas: |
C3 =B11 |
C4 =C2-C3+B12 |
C5 =C4/$I$4 |
C6 =B6+C13-C14 |
C7 =C6*$I$2*$I$3*$I$4 |
C9 =C8*$I$4 |
C10 =C7+C9 |
C17 =$B17*C6*$I$2*$I$3 |
C18 =C8*$B18 |
C19 =C11*$B19 |
C20 =C12*$B20 |
C21 =C13*$B21 |
C22 =C14*$B22 |
C23 =SUM(C17:C22) |
C24 =B11-B12-C2+C10-C11+C12 |
Copy the above formulas upto May month. (column F) |
F26 =SUM(C23:F23) |
Total cost = $ 1,190,248
Plan production for a four-month period: February through May. For February and March, you should produce...
Plan production for a four-month period: February through May. For February and March, you should produce to exact demand forecast. For April and May, you should use overtime and inventory with a stable workforce; stable means that the number of workers needed for March will be held constant through May. However, government constraints put a maximum of 5,000 hours of overtime labor per month in April and May (zero overtime in February and March). If demand exceeds supply, then backorders...
Problem 8-8 Plan production for a four-month period: February through May. For February and March, you should produce to exact demand forecast. For April and May, you should use overtime and inventory with a stable workforce; stable means that the number of workers needed for March will be held constant through May. However, government constraints put a maximum of 5,000 hours of overtime labor per month in April and May (zero overtime in February and March). If demand exceeds supply,...
Plan production for the next year. The demand forecast is: spring, 19,500; summer, 9,400; fall, 15,000; winter, 18,800. At the beginning of spring, you have 66 workers and 990 units in inventory. The union contract specifies that you may lay off workers only once a year, at the beginning of summer. Also, you may hire new workers only at the end of summer to begin regular work in the fall. The number of workers laid off at the beginning of...
Develop a production schedule to produce the exact production requirements by varying the workforce size for the following problem The monthly forecasts for Product X for January February, and March 1010, 1510 and 1180 respectively. Safety stock policy recommends that half of the forecast for that month be defined as safety stock. There are 22 working days in January, 19 in February, and 21 in March. Beginning inventory is 530 units Manufacturing cost is $180 por unit, storage cost is...
Problem 8-14 (Algo) Develop a production schedule to produce the exact production requirements by varying the workforce size for the following problem. The monthly forecasts for Product X for January, February, and March are 1,010, 1,540, and 1,180, respectively. Safety stock policy recommends that half of the forecast for that month be defined as safety stock. There are 22 working days in January, 19 in February and 21 in March. Beginning inventory is 530 units. Manufacturing cost is $180 per...
Develop a production plan and calculate the annual cost for a firm whose demand forecast is fall, 11,000; winter, 7,700; spring, 6,700; summer, 13,000. Inventory at the beginning of fall is 550 units. At the beginning of fall you currently have 30 workers, but you plan to hire temporary workers at the beginning of summer and lay them off at the end of summer. In addition, you have negotiated with the union an option to use the regular workforce on...
Problem 8-7 Develop a production plan and calculate the annual cost for a firm whose demand forecast is fall, 10,700; winter, 8,300; spring, 6,900; summer, 12,700. Inventory at the beginning of fall is 535 units. At the beginning of fall you currently have 35 workers, but you plan to hire temporary workers at the beginning of summer and lay them off at the end of summer. In addition, you have negotiated with the union an option to use the regular...
Complete the level production plan, using the following information. The only costs you need to consider here are layoff, hiring, and inventory costs. If you complete the plan correctly, your hiring, layoff, and inventory costs should match those given here. E: Click the icon to view the costs table. Click the icon to view the forecasted sales. Fill in the production plan table below (enter your responses rounded to the nearest whole number). Month Forecasted sales Sales in worker hours...
A disk drive manufacturer is in need of an aggregate plan for July to December. The company has gathered the following data: Month Demand Month Demand July 400 Oct 700 Aug 500 Nov 800 Sep 550 Dec 700 Item Cost Materials $35/disk Inventory $8/disk/month Subcontracting $80/disk Stock-out $15/disk Regular time labor $12/hour Overtime labor $18/hour (above 8 hrs) Hiring cost $40/worker Layoff cost $80/worker Other data include: Item Data Current workforce (June) 8 people Labor hours per disk 4 hrs...
3. Jason Enterprises (JE) is producing video telephones for the home market. Quality is not quite as good as it could be at this point, but the selling price is low and Jason can study market response while spending more time on R&D. At this stage, however, JE needs to develop an aggregate production plan for the six months from January through June. As you can guess, you have been commissioned to create the plan. Assume a starting workforce of...