A planner has developed an aggregate forecast for demand for the next 6 months as shown below:
| Month |
Jan |
Feb |
March |
Apr |
May |
June |
| Forecast |
6000 |
5000 |
6000 |
5500 |
6000 |
7000 |
Use the following information to develop aggregate plans:
Regular production cost: $10.00 per case
Regular production capacity: 5500 cases/month
Overtime production cost: $15.00 per case
Subcontracting cost: $20.00 per case
Holding/Carrying cost: $2.00 per case per month
Develop an aggregate production plan for each of the following guidelines and compute the total cost for each plan. Note: backlogs are not allowed.
Use level-capacity production and supplement with inventory and subcontracting as needed.
Use a combination of inventory, regular production, overtime (maximum 200 cases per month), and subcontracting (maximum 300 cases per month) as needed.
Use regular production, inventory and overtime (500 cases maximum per month).
Use "Solver" to set up and answer all three parts. Save all parts in three separate worksheets (e.g., part 1, part 2 and part 3) in a single spreadsheet file (make sure that your Excel file name includes your last name).
Moreover, please make sure that that all Solver parameters inputs (e.g., "Set objective cell", "By Changing Variable cells", and constraints) are all reflected in each corresponding Excel worksheet.
Once you set the problem in a spreadsheet, parts 2 and 3 should be quite easy to complete as the only variations will entail different decision variables and constraints.