Question

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

            President’s 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)?

Answer 1