The Appalachian Herbal Company wants to begin introduction of a new herbal balm. The company needs a break-even analysis and answers to what-if scenarios. Data: If they go ahead with the balm, a fixed monthly cost (operating cost) of $1200 is incurred.

The variable cost is $2.50 per jar of balm produced.

Each jar of balm sold would generate $15 in revenue.

A sales forecast (number that can be sold) will be obtained.

e.g suppose the sales forecast is 150 jars of balm per month.

Questions: 1. Set up an Excel sheet for this situation and state the profit on selling 150 jars per month.

2. Use Goal Seek to determine the monthly break-even volume.

3. Use Goal Seek to determine the price per jar (selling 150 jars per month) that will give a profit of $1,500 per month.

4. Suppose a new piece of equipment has to be purchased that will raise operating costs to $2,000 per month. What would you advise?

5. How large can the variable cost be before the balm ceases to be profitable? (Keep fixed cost and price the same).

6. How large can the fixed cost be before the balm ceases to be profitable? (Keep the marginal cost and price the same).

7. Suppose the company want to run a special and sell the product at $10. What would you advise?

8. Create a Data Table for Profit vs Price; Use values for price ranging from $5 to $25 in steps of $5.

9. Create a Data Table for Profit vs Variable Cost; Use values for variable cost ranging from $1 to $12 in increments of $1.

10. Create a Two-Way Data Table of Profit versus both Price & Variable Cost. Use horizontal values for price ranging from $5 to $17.50 in increments of $2.50 and vertical values of variable cost ranging from $0.50 to $3.00 in increments of $.50.