Using Microsoft Excel, prepare an initial aggregate plan, and then an alternative plan, and answer the questions listed:
Problem description:
Given the projected demands for the next six months, prepare an initial aggregate production plan that uses only regular time. Then develop an alternative aggregate production plan that also shows overtime production is utilised to fulfill backorders. Regular time is 150 units per month. Overtime is a maximum of 20 units per month. Overtime cost is $30 per unit, backorder cost is $20 per unit, inventory holding cost is $5 per unit, regular time cost of $20 per unit, and beginning inventory is zero.
Month Forecast
1 180
2 170
3 140
4 150
5 130
6 150
Requirements:
Prepare an initial plan