Windy City Cheese Company produces two cheese spreads by blending Mild Cheddar Cheese and Spicy Cheddar Cheese. The cheese spreads are packaged in 9-ounce containers and sold to distributors throughout the Midwest. The Regular blend contains 80% Mild and 20% Spicy. The Smoking Hot blend contains 20% Mild and 80% Spicy. A dairy cooperative across the border in Wisconsin can provide up to 5000 pounds of Mild Cheddar Cheese for $1.20 per pound and up to 4000 pounds of Spicy Cheddar Cheese for $1.75 per pound. The cost to blend and package the cheese spreads (excluding the cost of the cheese) is $0.25 per container. Each Regular container is sold for $3.75. Each Smoking Hot container is sold for $4.25 per container.
Formulate the linear programming model within your Word Report: Define the decision variables, the objective function, and the constraints. You should show any extra work required for constraints and the net profit per unit for each blend in your Excel spreadsheet.
Create the Solver model for this problem. Run the model. Select the Sensitivity Report also. Submit the Excel spreadsheet with your Solver model and Sensitivity Report to the dropbox. How many of each blend should Windy City Cheese produce next period? What is the estimated profit for next period? Show your answers in your Word document.
Copy the Sensitivity Report into your Word document. Analyze the change below by referring back to your original model.
Using the Sensitivity Report only, what happens to Net Profit if the dairy cooperative can provide a maximum of 2,950 pounds of Spicy Cheddar Cheese next period? Show your calculations in both the Word document and the Excel file.