Please present your answers in Excel in a professional format using good titles and proper use of formulas where necessary.

Pratt, Inc. roasts premium, organic coffee for high-end restaurants. They have three major blends of coffee from the following origins; Ecuador, Peru, and Kenya. They sell the coffee in 10 pound bags and had the following sales prices and volume of sales last year:

Type of coffee Sales price Sales volume

Ecuador = $ 23.00 / bag 60,000 bags

Peru = $ 24.50 / bag 100,000 bags

Kenya = $ 27.25 / bag 40,000 bags

Each bag of coffee requires 12.5 pounds of green coffee beans (unroasted). The roasting process strips away additional moisture, leaving 10 pounds as the end product. Last year, Pratt paid the following prices for green (raw) coffee beans and required the following labor hours for roasting one 10 pound bag of coffee (Pratt pays $17 / hour for roasting):

Type of coffee Price of beans Roasting hours / bag

Ecuador = $ 0.75 / pound 0.5 hours

Peru = $ 0.85 / pound 0.75 hour

Kenya = $ 0.65 / pound 0.75 hour

After roasting the beans, Pratt then packages each 10 pound bag in its signature paper bags, which cost $0.75 per bag. The packaging process is handled by a packaging machine which Pratt rents for $ 55,000 per year. During the last year, Pratt also incurred the following expenses:

Type of expense Amount Comments

Presidents Salary $ 85,000 Paid by salary

Quality control personnel $ 68,000 Paid by salary

Utilities $ 29,500 See comments below (*)

Insurance $ 12,000 Paid annually

Facility rent $115,000 Paid annually

Advertising $ 35,000 See contractual terms below (**)

*Utilities Management ran a linear regression for the past five years of utilities expenses (dependent variable) regressed against sales dollars (independent variable). The adjusted R²= 0.05.

**Advertising Pratt outsources their advertising and marketing campaigns. They pay the marketing agency an annual flat fee plus $0.05 per bag of coffee sold. The $35,000 is an estimate based on sales of 250,000 bags.

Required:

Please write the profit function for this business before tax.

What is the contribution margin for each type of coffee? Contribution margin % for each type of coffee? Weighted average contribution margin % for all types?

What is the sales mix (in %)?

What is the breakeven point in units and dollars of sales for Pratt (assume sale mix remains constant)?

If Pratt wants a before-tax profit of $500,000 (assuming constant sales mix), what should be his top-line revenue? How many bags will he need to sell?

If Pratt wants an after-tax profit of $250,000 (assuming a tax rate of 30%), how many bags of coffee does he need to sell (assuming constant sales mix)?