Develop a spreadsheet model and use Excel add$-$in Solver when needed to analyze the following three strategies and compare their associated costs. It is highly recommended you create a separate worksheet for each of the following strategies for clarity.

Optimal strategy: consider all possible variables $($hiring$,$ laying off, regular time, overtime, and spot market$)$ under your control, what is the best solution you can come up with?

Analyze your problem by using the cost presented in the following table, which may vary from the ones presented in the case. Note: in your analysis, simply ignore lead time for harvesting and shipping, which was one month as indicated in the case. Also ignore shipping capacity.

Cost Factor

Cost $($$$)$

Regular time

$200$ per thousand board feet

Over time

$250$ per thousand board feet

Holding

$25$ per thousand board feet

Hiring cost

$4,000$ per employee

Laying off cost

$2,000$ per employee

Hints:

Given monthly demand, there are three ways to meet the demand $($related to decision variables$)$:

Adjust the number of employees $($and hence production capacity$)$ by hiring or laying off at the beginning of each month.

Let the employees work overtime $($note: no more than $25\%$ of normal working hours$)$

Purchase from the spot market.

You should consider five types of cost when comparing the costs associated with different strategies.

Regular time labor cost $($note: each employee should be paid in full amount even if they don$$t work full $160$ hours$)$

Overtime labor cost $($this is based on the actual overtime hours each employee works$).$

Hiring and laying off costs. $($Only if you need to hire new employees or lay off current employees$).$

Inventory holding costs $($if at the end of a month, there is remaining inventory that needs to be carried over to the next month, there is a cost incurred$).$

Purchasing cost from spot market $($Note: producing in Chile incurs shipping cost which is $50$ per $1000$ board feet. Purchasing from the spot market does not incur such costs. In this case, we can ignore shipping cost and treat the spot market price as $$400-$$$50=$$$350$ per $1000$ board feet$).$