1. Using Excel and Solver create a spreadsheet to determine what product mix should be manufactured to achieve maximum profit.

A shoe company is planning on making two new products: sandals and boots. The unit profit for a pair of sandals is $25.00 and the unit profit for a pair of boots is $45.00.

There are 2 factories available to make the products.

Factory 1 cuts the material for each item and requires 3.5 minutes to make a pair of sandals and 4 minutes to make a pair of boots. It has 3000 minutes available.

Factory 2 puts the pieces together to create the final product and requires 1.5 minute to make a pair of sandals and 4.5 minutes to make pair of boots. It has 2300 minutes available.

Factory 1 needs at least 3 employees to produce sandals and 1 employee for boots. The total minimum employees required is 75.

Factory 2 needs at least 2 employees to produce sandals and 3 employees for boots. The total minimum employees required is 45.

There is only enough material to make a maximum of 70 pairs of boots.

2. Run a sensitivity report.

Answer the following on a Word document:

3. What is the allowable range for sandals?

4. What is the allowable range for boots?

5. Without using Excel, explain what would happen to the optimal solution if the profit for sandals increased by $20.

6. Without using Excel, explain what would happen to the optimal solution if the profit for boots decreased by $30.

7. Explain what the shadow price for employees needed in Factory 1 means.

8. Describe a real life scenario (outside of manufacturing) where What If Analysis should be used.

9. Why is What If Analysis important for managers to understand in the decision making process?

One person should submit both the Excel file and the Word doc