The Economic Order Quantity $(EOQ)$ model $is$ a classical model used for controlling inventory and satisfying demand. Costs included $in$ the model are holding cost per unit, ordering cost, and the cost $of$ goods ordered. The assumptions for that model are that only a single item $is$ considered, that the entire quantity ordered arrives $at$ one time, that the demand for the item $is$ constant over time, and that $no$ shortages are allowed.

Suppose $we$ relax the first assumption and allow for multiple items that are independent except for a restriction $on$ the amount $of$ space available $to$ store the products. The following model describes this situation:

Let $Dj=$annual demand for item $jCj=$unit cost $of$ item $jSj=cost$ per order placed for item $ji=$inventory carrying charge $as$ a percentage $of$ the cost per unitW $=$the maximum amount $of$ space available for all goodswj $=$space required for item $j$

The decision variables are $Qj,$ the amount $of$ item $jto$ order. The model $is$:

$In$ the objective function, the first term $is$ the annual cost $of$ goods, the second $is$ the annual ordering cost $(D\frac{j}{Q}jis$ the number $of$ orders$),$ and the last term $is$ the annual inventory holding cost $(Qj\frac{?}{2}is$ the average amount $of$ inventory$).$

Set $up$ a spreadsheet model for the following data:

Item $1$Item $2$Item $3$Annual Demand$2,0002,0001,500$Item Cost $($$$)1005080$Order Cost $($$$)150135125$Space Required $(sq.$ feet$)502540$

$W=5,000$

$i=0\mathrm{.}2$

Solve the problem using Excel Solver. Hint: You will need $to$ start with decision variable values that are greater than $0$ for Solver $to$ find a solution.

$If$ required, round your answers $to$ two decimal places.

Optimal Solution:

$Q1=$

$Q2=$

$Q3=$

$If$ required, round your answer $to$ the nearest dollar. $Do$ not round intermediate calculations.

Total cost $=$ $

ordered arrives $at$ one time, that the demand for the item $is$ constant over time, and that $no$ shortages are allowed.

Suppose $we$ relax the first assumption and allow for multiple items that are independent except for a restriction $on$ the amoun