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)
(p.s a step bu step with excel would help me a lot thank you!)