Three products Product1 and Product2 and Product3 are produc

Three products, Product1 and Product2 and Product3, are produced in annual amounts A1, A2 and A3 respectively. Producti (for each i = 1, 2, 3) has annual demand Ai (as above), fixed cost per order ki, unit cost per item ci, annual holding cost hi, and order quantity Qi; where we shall sometimes write the optimal order quantity Q*i simply as Qi (i = 1, 2, 3).

Upon being produced and delivered, each Qi of each product Producti is made into a circle, which has radius ri = Qi /(2?). Due to a misunderstanding in business negotiations and specifications (regarding the area of land that length Qi can enclose), the profit Profiti given will equal the area of this circle, ? ri2 or, equivalently, ? [Qi /(2?)]2, or equivalently, Qi2/(4?).

We know that ki/(2? ci hi) = (2ki/(ci hi))/(4?) takes the following values:

k1/(2? c1 h1) = $35, k2/(2? c2 h2) = $30, and k3/(2? c3 h3) = $32.

Due to conflicting demand for rival products, we have the following constraints on the annual demands Ai for the products Producti whose profits (as above) are Qi2/(4?). Here are the relevant constraints:

9 A1 + 5 A2 + 8 A3 <= 3132

A1 + A2 + A3 <= 400

12 A1 + 17 A2 + 13 A3 <= 5760.

a) Formulate this problem appropriately.

b) Create a spreadsheet model for this problem.

c) Are there any other constraints that should be stated?

If so, give these constraints

d) Solve the problem - using Microsoft Excel Solver, generating the minimum cost to satisfy the constraints.

What is the minimum cost?

e) Keeping all variables fixed except k3, Q3 and A3, by what proportion or factor would k3 have to change in order to change Q3 and A3?

Solution

In corporate finance, economic order quantity (EOQ) is the order quantity that minimizes the total holding costs and ordering costs. It is one of the oldest classical production scheduling models. The framework used to determine this order quantity is also known as Wilson EOQ Model.

By taking the first derivative of the function we find the following equation for minimum cost

EOQ = SQRT(2 × Quantity × Cost Per Order / Carrying Cost Per Order)

There are primarily two ways to implement EOQ. Both methods obviously require that you have already determined the associated costs. The simplest method is to set up your calculation in a spreadsheet program, manually calculate EOQ one item at a time, and then manually enter the order quantity into your inventory system. If your inventory has fairly steady demand and costs and you have less than one or two thousand SKUs you can probably get by using this method once per year. If you have more than a couple thousand SKUs and/or higher variability in demand and costs you will need to program the EOQ formula into your existing inventory system. This allows you to quickly re-calculate EOQ automatically as often as needed. You can also use a hybrid of the two systems by downloading your data to a spreadsheet or database program, perform the calculations and then update your inventory system either manually or through a batch program. Whichever method you use you should make sure to follow the following steps:

A related calculation is the Total Annual Cost calculation. This calculation can be used to prove the EOQ calculation.

This formula is also very useful when comparing quotes where vendors offer different minimum order quantities, price breaks, lead times, transportation costs.