1. EOQ for Multiple Products: In anotherlocation, the distributor of the previous problem stocks fourdifferent items in common warehouse space. Each item is describedby an annual demand rate, a fixed cost per order, a holding costper year, a unit purchase cost, and a space requirement. The datain the following table describe the four products.

Item	1	2	3	4
Demand	5000	10,000	30,000	300
Fixed cost	400	700	100	250

Holding cost	50	25	8	100
Purchase cost	500	250	80	1000
Space (sq. ft)	12	25	5	10

Because all of the required analysis will result in purchasingthe full annual demand, I suggest that you ignore the cost topurchase the items. It will not change. Including the $7,700,000purchasing cost in all of the scenarios will only hide the analysisof the inventory costs which is key to this problem. Provide thefollowing analysis:

1. Create an Excel Spreadsheet with Solver to answer the followingquestions.

2. Provide tables and/or graphs that show the EOQ for eachproduct and grand total inventory cost for various percentages forthe holding cost.

3. Build a Solver model that considers the problem as a whole. Youwill want Solver to find the order quantities under additionalconstraints. What is the minimum total annual cost for the fourproducts if the average space taken up must be no more than 12,000sq. ft? You will need to start Solver with non-zeroquantities in the decision variables. I also recommend that you adda constraint forcing all decision variables to be >= 1. You wantto finish with the words "optimality conditions are satisfied." Ifyou don't see these, run Solver again without changing the valuesin the decision variables.

4. What is the minimum total annual cost for the four products ifthe average space taken up must be no more than 12,000 sq. ft andthe number of orders per year must be no more than 65? What happensif you not only need to limit the average space used but also findthat the Purchasing Department cannot process more than 65 ordersper year?
5. The Director of the Purchasing Department comes to you tellingyou that these four Products all come from the same supplier. S/hewould like to know the total inventory cost if all four productsare ordered at the same time, that is, synchronize the orders. Thespace limitation needs to remain in this model.
6. Provide one additional scenario/question/what-if analysis thatyou define.

Answer rating: 100% (QA)

Answer Solution Explanation Item 1 Item 2 Item 3 Item 4 ... View the full answer