Using the data provided in question $1,$ create an aggregate plan analysis in Excel using the following $4$ production strategies:

a$)$ Produce exactly to meet demand; vary workforce $($assuming workforce equal to first month$$s requirements$)$

b$)$ Constant workforce based on average production over the $6$th months using stock outs and inventory only $($calculate the workforce: total demand$/$total work hours per $1$ employee$)$

c$)$ Constant low workforce based on production requirements for lowest month, use subcontracting to meet demand

d$)$ Constant workforce of $2$ less workers than the workforce required in part b$,$ use overtime to meet demand

You must also provide a table comparing the costs calculated from your analysis that looks like this:

Cost Comparison

Cost Plan a Plan b Plan c Plan d

Hiring

Layoff

Inventory Holding

Stockout

Overtime

Straight time

Total

Upload a single Excel file here.

FORMATTING:

$1.$ Set the decimals to zero $(0)$ so that the numbers appear as whole numbers in your cells.

$2.$ Provide all parts of the analysis on a single sheet.

GRADING:

$1.$ Aggregate Production Planning Requirements $[3$ marks$]$

$2.$ Production Plan a $[5$ marks$]$

$3.$ Production Plan b $[5$ marks$]$

$4.$ Production Plan c $[5$ marks$]$

$5.$ Production Plan d $[5$ marks$]$

$6.$ Comparison of All Plans $[3$ marks$]$

Total: $26$ marks