Create a graph of the sensitivity results

Assignment Help Operation Management

Reference no: EM131818724

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.

Reference no: EM131818724

Questions Cloud

How would you classify the nurses salary : The student health center employs one doctor, three nurses, and several other employees. How would you classify the nurse's salary

Establish a control chart for monitoring the average weights : Establish a control chart for monitoring the average weights of the bowling balls in which the upper and lower control limits are each two standard deviations.

How can improve return on investment in a business setting : Develop a 7-12 min. PowerPoint Presentation. What is critical thinking and how can it improve Return on Investment in a business setting.

Accounts receivable are estimated to be uncollectible : accounts receivable are estimated to be uncollectible. Accounts Receivable has a balance of

Create a graph of the sensitivity results : Set up a spreadsheet model to calculate total monthly profit with production quantity as the decision variable.

Provide a brief overview of strategic communications plan : Provide a brief overview of your Strategic Communications Plan. Include a short description for each of the following in bullet point format.

How do you feel about net neutrality : How do you feel about net neutrality ?Do you believe heavier bandwidth users should pay for more bandwidth?

How do information systems provide competitive advantages : How does competitive strategy determine business process and structure of information systems?

Indicate the three ways in which the hmo act of 1973 made it : Indicate the three (3) main ways in which the HMO Act of 1973 made it possible for Managed Health Insurance plans to spread beyond their regional strong holds.

User Account

All Pages