Problem $3[26$ Marks$]$

The management of Zahret Company is trying to determine the amount of each of two

products to produce over the coming planning period. The following information concerns

labour availability, labour utilisation, and product profitability.

Table $1$

Labor$-$Hours Required $($hours$/$unit$)$

Department Product $1$ Product $2$ Hours Available

A $1\mathrm{.}000\mathrm{.}35100$

B $0\mathrm{.}300\mathrm{.}2036$

C $0\mathrm{.}200\mathrm{.}5050$

Profit contribution$/$unit $30\mathrm{.}0015\mathrm{.}00$

a$.$ Develop a linear programming model formulation of the Zahret Company problem. Solve

the model using Solver and determine the optimal production quantities of products $1$ and

$2.[8]$

b$.$ In computing the profit contribution per unit, management does not deduct labour costs

because they are considered fixed for the upcoming planning period. However, suppose

that overtime can be scheduled in some of the departments. Which departments would

you recommend scheduling for overtime? How much would you be willing to pay per hour

of overtime in each department? $[5]$

c$.$ Suppose that $10,6,$ & $8$ hours of overtime may be scheduled in departments A$,$ B$,$ and C$,$

respectively. The cost per hour of overtime is $18$ in department A$,22\mathrm{.}50$ in department

B$,$ and $12$ in department C$.$ Formulate and solve a linear programming model that can be

used to determine the optimal production quantities if overtime is made available. What

are the optimal production quantities, and what is the revised total contribution to profit?

How much overtime do you recommend using in each department? What is the increase

in the total contribution to profit if overtime is used? $[8]$

d$.$ In the report, summarise answers to the above questions and discuss any additional insights

that would support decision making. $[5]--------------------------------$ Give me a full answer with excel solution too.