An okra farm anticipates highly seasonal demand for their product, tender pods of

okra that can be made into the new drink sensation$,$ the okra colada. Their estimate

of the demand profile appears below. This forecast is based on the demand profile

of last year$$s drink, the tuna colada. Once everyone in the test market had actually

sampled the drink, demand fell to zero.

Month Jan Feb Mar Apr May June

Forecast $12002400360048002200200$

The costs for the managerial levers appear below.

Materials cost$/$unit: $$10$

Inventory holding cost$/$unit$/$month: $$2$

Marginal cost of stockout$/$unit$/$month: $$5$

Hiring and training cost$/$worker: $$300$

Layoff cost$/$worker: $$500$

Labor hours required$/$unit: $4$

Regular time cost$/$hour: $$4$

Over time cost$/$hour: $$6$

Beginning inventory equals $1000$

Ending inventory greater than $100$

Marginal subcontracting cost$/$unit: $$30$

The base price per okra colada is $$40$ per unit and there is no promotion, but management is seriously considering different promotional plans. The beginning workforce level is $80$ workers.

$($a$)$ Define and introduce decision variables when using linear programming to

solve this sales and operations planning problem? How many are they?

$3$

$($b$)$ Define and introduce constraints.

$($c$)$ Define and introduce the objective function.

$4$

$($d$)$ Solve this problem using the Excel solver. You may use and modify the file,

Chapter$89-$ examples.xlsx posted in Britespace. Submit your Excel file in

Britespace.

$($e$)$ What is the ideal workforce level throughout January$-$June?

$($f$)$ What is the optimal total cost?

$($g$)$ What is the optimal profit?

$($h$)$ Which of the following statements is true when using linear programming to

solve this sales and operations planning problem?

i$.$ More workers are hired than laid off.

ii$.$ More units are built using subcontracting than using overtime.

iii. The number of stockouts exceeds the number of units in ending inventory

from January$-$May.

iv$.$ The number of units subcontracted exceeds the number of stockouts that

occur.

$($i$)$ What is the minimum value for the cost of a single stockout to ensure that there

are no stockouts during the six$-$month planning period? Use an integer value

in your answer. Show me in excel file