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

$$

$$

$=$

$$annual demand for item

$$

$$

$$

$=$

$$unit cost of item

$$

$$Sj

$=$

$$cost per order placed for item

$$

$$I inventory carrying charge as a percentage of the cost per unit

$$

$=$

$$the maximum amount of space available for all goods wJ

$=$

$$space required for item j The decision variables are

$$

$,$

$$the amount of item

$$

$$to order. The model is: Minimize

$$

$$

$=$

$1$

$$

$[$

$$

$$

$$

$$

$+$

$$

$$

$$

$$

$$

$$

$+$

$$

$$

$$

$$

$$

$2$

$]$

$$s

$.$

t

$,$

$$

$$

$-$

$1$

$$

$$

$$

$$

$$

$<=$

$$

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

$\backslash$

table

$[$

$[$

$,$

Item

$1$

$,$

Item

$2$

$,$

Item

$3$

$]$

$,$

$[$

Annual Demand,

$3$

$,$

$0$

$0$

$0$

$,$

$3$

$,$

$0$

$0$

$0$

$,$

$2$

$,$

$0$

$0$

$0$

$]$

$,$

$[$

Item Cost

$($

$

$)$

$,$

$9$

$0$

$,$

$5$

$0$

$,$

$7$

$0$

$]$

$,$

$[$

Order Cost

$($

$

$)$

$,$

$1$

$5$

$0$

$,$

$1$

$3$

$5$

$,$

$1$

$2$

$5$

$]$

$,$

$[$

Space Required

$($

sq

$.$

$$feet

$)$

$,$

$5$

$0$

$,$

$2$

$5$

$,$

$4$

$0$

$]$

$]$

$$

$=$

$5$

$,$

$0$

$0$

$0$

$$

$=$

$0$

$.$

$3$

$$Solve the problem using Excel Solver. Hint: You will need to start with decision variable values that are greater than of for Solver to find a solution. If required, round your answers to two decimal places. Optimal Solution:

$$

$1$

$=$

$$

$2$

$=$

$$

$3$

$=$

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

student submitted image, transcription available