Consider the Economic Order Quantity (EOQ) Model for Multiple Products

Essay

Consider the economic order quantity (EOQ) model for multiple products that are independent except for a budget restriction. The following model describes this situation
Let D_k = annual demand for product k
C_k = unit cost of product k
S_k = cost per order placed for product k
i = inventory carrying charge as a percentage of the cost per unit
B = the maximum amount of investment in goods
N = number of products
The decision variables are Q_k, the amount of product k to order. The model is: $$\text { Minimize } \sum _ { k = 1 } ^ { N } \left[ C _ { k } Q _ { k } + \frac { S _ { k } D _ { k } } { Q _ { k } } + i C _ { k } \frac { Q _ { k } } { 2 } \right]$$ s.t. $\sum _ { k = 1 } ^ { N } C _ { k } Q _ { k } \leq B$ $Q _ { k } \geq 0 \text { for } k = 1,2 , \ldots N$
a. Set up a spreadsheet model and for the following data: $$\begin{array} { | l | c | c | c | } \hline & \text { Product 1 } & \text { Product 2 } & \text { Product 3 } \\\hline \text { Annual Demand } & 1250 & 1550 & 1450 \\\text { Product Cost } & \$ 120 & \$ 90 & \$ 105 \\\text { Order Cost } & \$ 110 & \$ 175 & \$ 140 \\B & \$ 30,000 & & \\i & 0.3 & & \\\hline\end{array}$$
b. Solve the problem using Excel Solver. (Hint: For Solver to find a solution, you need to start with decision variable values that are greater than 0.)