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

question 10

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

---

Definitions:

Limited Liability Company

A business structure that combines the pass-through taxation of a partnership or sole proprietorship with the limited liability of a corporation.

Partnership

An unincorporated business form consisting of two or more persons conducting business as co-owners for profit.

Corporation

A legal entity that is separate and distinct from its owners, who are shareholders; it has its own rights, privileges, and liabilities independent of its shareholders.

American Red Cross

A humanitarian organization that provides emergency assistance, disaster relief, and education inside the United States.