Problem $1.$ Linear Programming Problem $$ Use Excel Solver

A company that makes capacitors has three $8-$hour manufacturing shifts during the day:

Shift $1$: $8$ am $4$ pm

Shift $2$: $4$ pm $$ midnight

Shift $3$: midnight $8$ am

The company wants to maximize profit, and hires you to determine how many workers to

employ in each shift. The company gives you the following information:

$$ It is estimated that $1$ worker would make $10$ capacitors per shift;

$$ The workers$$ hourly salaries are $$12,$ $$16,$ and $$20$ for Shift $1,2,$ and $3,$ respectively;

$$ It is determined that the average number of defects per capacitor varies with shift as

follows: $4$ defects$/$capacitor during Shift $1,3$ defects$/$capacitor during Shift $2,$ and $2$

defects$/$capacitor during Shift $3$;

$$ Due to these defects, the capacitors made in the different shifts will be sold at different

prices: $$18/$capacitor made in Shift $1,$ $$22/$capacitor made in Shift $2,$ and $$24/$capacitor

made in Shift $3.$

The company has the following constraints:

$1.$ They have a total of $25$ workers $($and they do not want to hire more$)$;

$2.$ They want to schedule no more than $10$ workers per shift;

$3.$ The average number of defects per capacitor for a day of production cannot exceed $3$;

$4.$ At most $250$ capacitors can be sold each day.

a$)$ Setup the linear programming problem:

$1.$ List the decision variables

$$ Hint: there are $3$ decision variables;

$2.$ Define the objective function

$$ Hint: the profit per shift will be based on the sales price of capacitors made on a

particular shift minus the total $8-$hr $$ cost for the workers in that shift;

$3.$ Define the constraints

$$ Hint $1$: there are $4$ constraints,

$$ Hint $2$: defects per capacitor per day can be calculated by total defects on all

shifts$/$total capacitors produced that day $$ and must be at most $3.$

b$)$ Using Excel Solver, determine the optimal number of workers in each shift to maximize

profit. Turn in your Excel file