The Hottentot Sauce Company is a small manufacturer of several different sauces to use in food products. One of the sauces is a blended mix that is sold to retail outlets.
The general manager must decide how many cases of this mix to manufacture each month Three ingredients are used in the sauce: hot chili peppers which cost $4.35 per case, mild peppers at a cost of $2.50 per case and a blend of spices which cost $2.33 per case.
Labor costs are estimated at about $4.21 per case and fixed monthly cost are about $2,000. The company would sell each case of sauce for $45.00.
a. Set up a spreadsheet model to calculate total monthly profit with production quantity as the decision variable - assuming that every case will be sold. Use this model for the questions below. Do not set up multiple models.
i. Use the Goal Seek function in Excel to determine the break-even quantity.
ii. With production/sales quantity set at 100, create a one-way table, using Excel's Data/What-If/ Data Table tool, to demonstrate the sensitivity of profit to changes in selling price per pie. Use prices of 35, 40, 45, 50, 55, 60 in your table
iii. Create a graph of the sensitivity results from part ii. (scatterplot)
b. Set up a new spreadsheet model (on a separate worksheet) to calculate profit with the same input data, however, the assumption that every case produced cannot be sold. Unfortunately, any cases not sold by the end of the month are of no value, due to spoilage.
Demand is an unknown variable and Production Quantity is a separate (unknown) decision variable.
Note: You must use the Excel IF function in this model.
i. Create a two-way table, using Excel's Data/What-If/ Data Table tool, to demonstrate the sensitivity of profit to changes in demand and production quantity.
Make Demand the row variable, Production Quantity the column variable. Use values of 50, 100, 150, 200, 250, and 300 for both the demand and production quantities in the table.