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?