Fall-2008_3-Mountain-Man spreadsheet

Fall-2008_3-Mountain-Man spreadsheet - Instructions This...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Instructions This Spreadsheet contains the Exhibits from the Mountain Man Case Study and an interactive forecasting model which allows users to evaluate the effects of launching the MM Light Product. Alternative scenarios can be evaluated by changing the assumption cells directly. Assumptions Used In this Scenario: (Note that you may not agree with these assumptions and/or probe the sensitivity of the results to these.) (However, all should use the 12% discount rate.) Case Facts Used in this Scenario Source Revenues from MM Beer in 2005 $50,440,000 Exhibit 1 Contribution Margin 31.0% Exhibit 1 Annual Change in MM Beer Revenues -2.0% Case Fact Page 6 East Central Light Beer Market in 2005 18,744,303 Exhibit 5 Annual Change East Central Light Beer Market 4.0% Exhibit 5 # Barrels of MM Beer 520,000 Case Fact Page 3 Variable Cost per Barrel MM Beer $66.93 Case Fact Page 6 Exhibit 1 Increase in Cost of MM Light Beer (If launched) $4.69 Case Fact Page 6 Mountain Man 2005 Income Statement Assumptions Used In this Scenario Net Revenues $50,440,000 100.0% Cost of Goods Sold $34,803,600 69.0% Is MM Light Actually Launched? Gross Margin $15,636,400 31.0% Loss of Contribution (only applies if MM Light Launched) Case Fact From Page 7 $9,583,600 19.0% Advertising Cost of MM Light (only applies if MM Light is Launched) NO Fact From Page 6 Other Operating Expenses $1,412,320 2.8% 0.00% Fact From Page 6 Operating Margin $4,640,480 9.2% Initial Market Share for MM Light in East Central Light Beer Market 0 Fact From Page 6 $151,320 0.3% Growth in Market Share for MM Light in East Central Light Beer Mkt. 0 Fact From Page 6 Net Income Before Taxes $4,791,800 9.5% Discount Rate 12.00% 0.00% Fact, Footnote p. 7 Provision for Income Taxes $1,677,130 3.3% Perpetuity Growth Rate for 'Net Change in Contribution' 3.00% 0.00% Assumption Net Income After Taxes $3,114,670 6.2% 12.00% 3.00% Exhibit 3 Calculation of Contribution per Barrel of MM Light Sold Competitive Market Shares in Barrels by Brewer East Central Region Revenue received in 2005 $50,440,000 # Barrels of MM Beer 520,000 Anheuser-Busch 15,620,252 42.0% Revenue per Barrel of MM Beer $97.00 Miller 8,553,948 23.0% Variable Cost per Barrel MM Beer $66.93 Coors 3,347,197 9.0% Contribution per Barrel of MM Beer $30.07 4,648,885 12.5% Increase in Cost of MM Light Beer (If launched) $4.69 Contribution per Barrel of MM Light $25.38 Craft / Specialty Brewers 557,866 1.5% Imports 4,462,929 12.0% Total 37,191,077 100% Calculation of Break Even Volumes Required - First Year Breakeven *** = annual change Exhibit 4 Current Revenues of MM Beer $50,440,000 Assuming a Beer Consumption by State (2000 to 2005 Shipments in Barrels) Proj. Revenues Next Year of MM Light IF sales prediction comes true $49,431,200 *** -2.0% STATE 2000 2001 2002 2003 2004 2005 Projected Contribution from MM Beer $15,323,672 in MM Beer Sales Next Year
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 5

Fall-2008_3-Mountain-Man spreadsheet - Instructions This...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online