Assignment $2$: Atlantic Instruments has three manufacturing plants

located in Saint John, Moncton and Halifax. Product demand varies

considerably from month to month, causing the company extreme

difficulty in workforce scheduling. Recently, Atlantic Instruments

started hiring temporary workers supplied by Smith Limited, a

company that specializes in providing temporary employees for firms.

Smith Limited offered to provide temporary employees under four

contract options that differ in terms of the length of employment and

the cost. The four options are summarized:

Option Length of Employment Cost

$1$ One Month $ $2450$

$2$ Two Months $ $5650$

$3$ Three Months $ $7885$

$4$ Four Months $ $9250$

The longer contract periods are more expensive because Atlantic

Instruments experiences greater difficulty finding temporary workers

who are willing to commit to longer work assignments.

Over the next eight months, Atlantic Instruments projects the following

needs for additional employees :

Each Month Employees Needed:

January: $25$

February: $35$

March: $5$

April: $35$

May: $20$

June: $37$

July: $26$

August: $18$

Each month, Atlantic Instruments can hire as many temporary

employees as needed under each of the four options. For instance, if

Atlantic Instruments hires four employees in January under Option $2,$

Smith Limited will supply Atlantic Instruments with four temporary

workers who will work for two months: January and February.

For these workers, Atlantic Instruments will have to pay

$4($$$5650)=$$$22,600.$ Because of some merger negotiations under way,

Atlantic Instruments does not want to commit to any contractual

obligations for temporary employees that extend beyond August.

Atlantic Instruments $$ quality control program requires each temporary

employee to receive training at the time of hire. The training program is

required even if the person worked for Atlantic Instruments in the past.

Atlantic Instruments estimates that the cost of training is $ $1100$ each

time a temporary employee is hired. Thus, if a temporary employee is

hired for one month, Atlantic Instruments will incur a training cost of

$$1100,$ but will incur no additional training cost if the employee is on a

longer contracts.

$($A$)$ Formulate an appropriate optimization model that can be used to

determine the number of temporary employees Atlantic Instruments

should hire to meet the projected needs at a minimum total cost. Show

your work. $(4$ Marks$)$

$($B$)$ Solve your developed optimization model using Excel$-$Solver and

obtain the optimal solution. $(2$ Marks$)$

$($C$)$ Suppose the manager decided to hire only from one of the three

options $1,2,$ and $3$ in April, if needed. Develop appropriate

constraint$($s$)$ for this situation. Show your work. $(2$ Marks$)$

$*$Please answer A$,$B$,$C and include picture of excel sheet showing formulas as well as solver tab showing formulas$*$