Q$1.$ Please use the trial and error method described by textbook $($pp$.458-462,$

Stevenson$$s $7$th Canadian Edition$).$ 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

$1180$

$2170$

$3140$

$4150$

$5130$

$6150$

Requirements:

$($a$).$ Prepare an initial plan

$($b$)$ Prepare an alternative plan

$($c$)$ Which plan is better $($a$)$ or $($b$)?$ Why?

$($d$)$ How many units should be produced using overtime hours in the alternative plan?

What is the adequate timing for overtime production? Explain.