Golden Star Winery produces midlevel wines consumed primarily in North America. Given below is the projected income statement for the company for 2011.
Projected Income Statement (2011) Sales (
100,000
cases at $7 per case)
$700,000
Cost of goods sold:
Materials
$1
80,000
Labor
$225,000
Fixed manufacturing expenses
$45,000
Administrative and selling expenses:
Delivery
$30,000
Commissions
$50,000
Advertising
$10,000
Travel
$5,000
Fixed administrative and selling expenses
$15,000
Total expenses
$560,000
Net income before taxes
$140,000
Create a report answering the following questions:
- Complete the following table in a fully functional Microsoft Excel spreadsheet.
Price |
Quantity |
Total Revenue |
Total Variable Cost |
Total Fixed Cost |
Total Cost |
Profit |
$8.00 |
65,000 |
|||||
$7.75 |
75,000 |
|||||
$7.50 |
||||||
$7.25 |
90,000 |
|||||
$7.00 |
||||||
$6.75 |
115,000 |
|||||
$6.50 |
120,000 |
- Using Excel, prepare a graph showing the breakeven point and any profit or loss at the current price of $7. Explain to the Golden Star management the implications of this analysis.
- What is the elasticity coefficient for each price between $6.50 and $7.50? Is the demand elastic or inelastic at these points? How can this information be useful to management in its pricing and output decisions?
- On the basis of your calculations and the information above, what recommendations would you make to Golden Star in terms of price and output levels?
- Using Excel Format ONly Please