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.)

Question

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.)

Quizplus · Accepted Answer

The Answer of 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.)

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