Find the total monthly profit with production quantity

Assignment Help Financial Management

Reference no: EM131881733

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!)

Reference no: EM131881733

Questions Cloud

Discuss the realtive riskiness of the three firms earnings : Given the following information, calculate the expected value for Firm C's EPS. Discuss the realtive riskiness of the three firms earnings.

What will katherine employers have to prove in court : Genrapay, a finance firm based in the U.S., hired Charles and Katherine as front desk receptionists. Six months into the job, Charles received a raise.

What amount of cash was paid for rent : Jarem Company showed $189,000 in prepaid rent on December 31, 2015. What amount of cash was paid for rent in 2016?

What was the amount of the revised estimated total project : What was the amount of the revised estimated total project construction costs at the end of 2015?

Find the total monthly profit with production quantity : Set up a spreadsheet model to calculate total monthly profit with production quantity as the decision variable - assuming that every case will be sold.

Determine the cost of goods sold and ending inventory : Determine the cost of goods sold and ending inventory for the month using specific identification.

What role do payables and accruals play in the cash cycle : What is the relationship between days sales outstanding (DSO) and total accounts receivable turnover? Which of balance sheet?

Determine the amount of budgeted cash payments for january : Determine the amount of budgeted cash payments for January selling and administrative expenses.

How much do the parents need to save annually at year : How much do the parents need to save annually at year end through the education of the youngest child to pay all college costs?

User Account

All Pages