Golden Star

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.

 

     

     

80,000     

     

100,000     

     

     

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