please help me to do spreadsheet of Mountain man case with using this info. if you need whole case study let me know. thank you

i have submiited screenshot of spreed sheet but that is just example. my teacher said do the spreedsheet like this.

also submiited informations

thank you

FINDINGS, ANALYSIS AND RECOMMENDATION ON THE MOUNTAIN MAN'S CASE PREAMBLE Based on our understanding on the case, we discovered that there we 3 astiots from which MMBC can choose The first option is to introduce light beer under Mountain Man Brand name the under listed will be an advantage to the company for this option: 1. Gain more opportunity in the fast-growing category. MMBC may has a 0.25% yearly growth it. Expand customer groups amongst the young consumers and women II. Gain share in onpromise locations iv. A chance to establish brand awareness among young cudemes and give larger a lift However, MMBC will have to consider the following disadvantages if it decides to introduce light beer under Mountain Man Brand name: I. Add burden to the cost structure, more inventory, packaging the SG&A (5900,000) til. May alienate the core customer base and crode brand equity. i. Light will occupy the facing of Lager, therefore, lager has a potential of losing revenue iv. Faced with fierce intensive marketing fee. . Cos will increase to $1.650,000, keeping price at 97 con may decrease profit margin The second option is to introduce light beer under different brand name. with this, we foresee the following advantages Increase the revenue due to the growth of selling light beer ii. Low advertising costs ii, They can cater untapped market under a new name This option may as well bring about the disadvantages listed below 1. Product cannibalization. It will be hard to combine the new product with the old one. ii. Brand crosion Loss of core customers The third option for MMBC is to not introduce light beer. Linder this option, because there is no action to improve the business and the traditional market is going down, MMBC will keep losing yearly 2% of its revenue in the future. It will be hard to keep the company profitable, which may eventually lead to winding up of the business FINDINGS The potential growth for Mountain Man in Light Beer market is 0.25% cach year, and market for Light beer is 18.7 million barrels. Because MMBC only has one type of beer, it is hard to compete with the other competitors. People always prefer new entrants in the market. A new product could be a way to get some of old customers back and attract new customers Therefore, the suggestion is to expand the product line. Besides the lager beer that satisfies current customers, the company needs to release light beer to attract young customers to be able to increase the market occupation and increase the revenue It is important to give a SWOT analysis of MMBC as we see it as a clue to know the direction of the company; however, below is the SWOT Analysis of the MMBC. Strengths Weaknesses S W Opportunities Threats towards comary boond to coba con copery T BREAK-EVEN ANALYSIS As per our calculation (see below) assume the profit margin is $25.38 (597-871.62). In 2006 we need about 48,735 barrels sales of light and a sale of 101,369 barrels in 2007. In either case or perhaps in worse scenario, the production is expected to cover all its investment cost and become profitable after 2007 RECOMMENDATION It is potentially a great risk for companies to introduce a new product with the threat of forgoing sales of its existing flagging product. But the market trends and customers behaviors towards a specific product and their lifestyle demand the alteration in MMBC product line. Therefore, we recommend that; Since there is a possibility to loss sales on Lager, and the cost will increase, MMBC should increase the price of light beer to cover the higher cost. Also, for protecting the core brand and expanding the sale of new product, MMBC should pay more attention on advertising of light beer to create wider awareness to distinguish light beer with lager. CONCLUSION Base on the foregoing, we are of YES opinion and therefore recommend Chris to launch Mountain Man Lite Beer. Even if Chris launches lite beer, in the first year, 2006, Chris would have little bit more net revenue than if he does not launch, according to the worst scenario (20% cannibalization) and Chris' assumption on Lite beer sales. With the best scenario (5% cannibalization), Chris would have $2.3 million more net revenue in 2006. And every year Mountain Man Lite Beer would cause more net revenue. Maharitraa - Autole Home D Fage Form De e Share Comments O 8A*** Aye & $% Sort A Formatting C G H M N o P Inaeloftro . 2006 2007 2000 SST 00 000 10 11 300 000 300 000 000 200.000 000 14 65,012 35,461 35,461 578000 5100340 35,461 373843 2371 17. Ce to the 18 Ledelach 20 57651 330647 5721120 2004.05 21 Twee Tapeta and al Pection of B + 95,200 2 185766 11,735 65,046 153,800 64,454 151,433 18.13 63,874 149,114 2002 2011 Type here to search GMM U14201 Maharitraa - Autole Home D Fage Form De e Share Comments O 8A*** Aye & $% Sort A Formatting C G H M N o P Inaeloftro . 2006 2007 2000 SST 00 000 10 11 300 000 300 000 000 200.000 000 14 65,012 35,461 35,461 578000 5100340 35,461 373843 2371 17. Ce to the 18 Ledelach 20 57651 330647 5721120 2004.05 21 Twee Tapeta and al Pection of B + 95,200 2 185766 11,735 65,046 153,800 64,454 151,433 18.13 63,874 149,114 2002 2011 Type here to search GMM U14201 Home Page Layout Heves View Hele Share Comments Al Wat aan Murpe Cerme Sete- om Condom Cel Formatting Ty Sort A B D E o R 12.00 income Chris Prats de rensein 2000 2001 SOOD 499 400 419 420 2008 120.000 2010 470.00 1000 3109 1904 550 440 000 14.03.600 1836.00 9,583.600 141229 400 151320 4.731.600 1.57210 DATO 549 01 200 11 442.575 5787372 546.534 250 545.628765 10720 33,023 372.76 12.101732499.00 15262 101/1997 21 16 785.134 750,000 795.40 706702 56247 300340 6727026 5 atm CS TG More SOLA 9 O OD 10 11 O 12 income for 13 Prosine 14 Nico 16 16 Price 17 UVC 24 0 Tapet Operating Protet 205. Eating Pole 591 REN Income Statement Act 11,206 Type here to search GE PM 414201 2069 Mountain Man Brewing Company: Bringing the Brand to Light Exhibit 1 Mountain Man 2005 Income Statement Net Revenues $50,440,000 100.00 COCS 34.803,600 Gross Margin 15,636,400 31.0% SGRA 9,583,600 19.0% Other Operating Expenses 1,412,320 28% Operating Margin 4.640.480 Other Income 151,320 Net Income Before Taxes 4.791 SO Provision for Income Taxes 1,677,130 Net Income After Taxes $3.114.670 Advertising expenses were $1.35 million annually or 2.7% of total revenues Advertising expenses included radio, print, and outdoor advertising, sponsorship well as costs to produce these media Riojor Post Exhibit 2 Profile of Beer Drinkers by Beer Type by Key Demographics, 2005 Domestic Light Beer Gender Male 58 Female Mountain Man Lager 81% Age 21-24 25-34 35-44 45-54 55-66 65+ 23% 15% 19% 32% 14% 13 under $25 14% 16 Do No 21% 16% 15 Source: First two colume of data extracted from Mintet/Simmons NCS 2005 report. figure 67 BRIEF CASES I HARVARD BUSINESS SCHOOL PUBLISHING Merentain Man Brewing Company. Hringing the brand to Light | 2002 cs Exhibit 3 Competitive Market Shares in Barrels by Brewer Anheuser Busch Miller Coors Other 2nd tier Premium & Popular Brewers Craft/Specialty Brewers Imports Total East Central Region 15.620,252 8,553,948 3,347,197 4.668. 557.866 4.462.929 37,191,0877 Note Sales in barrels of wholesale shipments Exhibit 4 Beer Consumption by State, 2000 to 2005 (shipments in barrels) STATE 2000 2002 2003 2001 2004 2005 Illinois 9,038.323 Indiana 3,954,209 Kentucky 2,517894 Michigan 6,761.561 8,493,144 West Virginia 1,274,626 Wisconsin 4.741,019 East Central Region 36,780,776 TOTAL U.S. 197,609,645 Source Beer Institute data. 9,165,351 3,917 46 2.466731 5665 8.601.04 2,311838 4784791 26.15 25 2364,013 6851064 3.680331 59 4.890,122 9,10,157 3,905,265 2,490,828 6.774.702 8.760,272 1,348,527 4.855,313 9,032.851 3,993,643 2.591.949 6,746,578 8,702.382 1,373.205 4.877.662 9/1X3.267 3,998,855 2.555,739 6,700,174 8.584,283 1,359,231 4,929,529 Ohio 36.983,456 37.640.992 37,318,269 37 191,077 37,243,163 202,586,016 202.605,292 204,318, Do Notice of 203,515,145 HARVARD BUSINESS SCHOOL PUBLISHING I BRIEF CASES 2000 | Mountain Man Brewing Company Bringing the rand to Light Exhibits Consumption by Type of Beer and by Origin/Packaging, 2005 A Consumption by Type of Beer EAST CENTRAL % Total 6-year CAGR REGION 18,744.103 7,326,642 4,351,356 Light Beer Premium Beer Popular Imported Premium Superpremium (craft and high-end domestica) Total Barrels 1975 11.7% 12.0% 2.305847 37,191,077 100.0 B. Consumption by Origin/Packaging EAST CENTRAL REGION Imported 4,462.929 Domestic Packaged 29,618,974 Domestic - Draught Total Barrels Do Not Com r Post 10 BRIEF CASES I HARVARD BUSINESS SCHOOL PUBLISHING Copying or posting is an infringement of copyright. Permissions@hbsp.harvard.edu or 617-783-7860. Mountain Man Brewing Company: Bringing the Brand to Light | 2069 Exhibit 6 Light Beer Market Shares and Dominant Brands A. Light Beer Competitive Market Shares East Central Region 2005 Market Share Anheuser-Busch Miller Coors 14% Other brands Imports Total Light Beer 100% B. Leading Domestic Light Beer Brands East Central Region Bud Light Miller Lite Coors Light Natural Light Busch Light Micheleb Ultra Milwaukee's Best Light Other domestic brands Do Wot Copy or Post Total 100 c. Leading Imported Light Beer Brands Brand 2005 Market Share Corona Light Amstel Light Labatt Blue Light Other imported brands Total 100% Note: Market share calculations based on wholesale barrel sales HARVARD BUSINESS SCHOOL PUBLISHING I BRIEF CASES 2069 Mountain Man Brewing Company: Bringing the Brand to Light Exhibit 7 U.S. Beer Advertising Expenditures by Medium (in millions of dollars), 2005 Medium 2005 Network television Cable television Spot television Syndicated television Spot radio Network radio S8823 72.1 144.3 5.5 224 12 Total Broadcast $627.5 232 6.6 Magazines Newspapers Newspaper supplements Outdoor 51.5 Total Print $81.3 TOTAL $709.1 Do Not Copy or Post FINDINGS, ANALYSIS AND RECOMMENDATION ON THE MOUNTAIN MAN'S CASE PREAMBLE Based on our understanding on the case, we discovered that there we 3 astiots from which MMBC can choose The first option is to introduce light beer under Mountain Man Brand name the under listed will be an advantage to the company for this option: 1. Gain more opportunity in the fast-growing category. MMBC may has a 0.25% yearly growth it. Expand customer groups amongst the young consumers and women II. Gain share in onpromise locations iv. A chance to establish brand awareness among young cudemes and give larger a lift However, MMBC will have to consider the following disadvantages if it decides to introduce light beer under Mountain Man Brand name: I. Add burden to the cost structure, more inventory, packaging the SG&A (5900,000) til. May alienate the core customer base and crode brand equity. i. Light will occupy the facing of Lager, therefore, lager has a potential of losing revenue iv. Faced with fierce intensive marketing fee. . Cos will increase to $1.650,000, keeping price at 97 con may decrease profit margin The second option is to introduce light beer under different brand name. with this, we foresee the following advantages Increase the revenue due to the growth of selling light beer ii. Low advertising costs ii, They can cater untapped market under a new name This option may as well bring about the disadvantages listed below 1. Product cannibalization. It will be hard to combine the new product with the old one. ii. Brand crosion Loss of core customers The third option for MMBC is to not introduce light beer. Linder this option, because there is no action to improve the business and the traditional market is going down, MMBC will keep losing yearly 2% of its revenue in the future. It will be hard to keep the company profitable, which may eventually lead to winding up of the business FINDINGS The potential growth for Mountain Man in Light Beer market is 0.25% cach year, and market for Light beer is 18.7 million barrels. Because MMBC only has one type of beer, it is hard to compete with the other competitors. People always prefer new entrants in the market. A new product could be a way to get some of old customers back and attract new customers Therefore, the suggestion is to expand the product line. Besides the lager beer that satisfies current customers, the company needs to release light beer to attract young customers to be able to increase the market occupation and increase the revenue It is important to give a SWOT analysis of MMBC as we see it as a clue to know the direction of the company; however, below is the SWOT Analysis of the MMBC. Strengths Weaknesses S W Opportunities Threats towards comary boond to coba con copery T BREAK-EVEN ANALYSIS As per our calculation (see below) assume the profit margin is $25.38 (597-871.62). In 2006 we need about 48,735 barrels sales of light and a sale of 101,369 barrels in 2007. In either case or perhaps in worse scenario, the production is expected to cover all its investment cost and become profitable after 2007 RECOMMENDATION It is potentially a great risk for companies to introduce a new product with the threat of forgoing sales of its existing flagging product. But the market trends and customers behaviors towards a specific product and their lifestyle demand the alteration in MMBC product line. Therefore, we recommend that; Since there is a possibility to loss sales on Lager, and the cost will increase, MMBC should increase the price of light beer to cover the higher cost. Also, for protecting the core brand and expanding the sale of new product, MMBC should pay more attention on advertising of light beer to create wider awareness to distinguish light beer with lager. CONCLUSION Base on the foregoing, we are of YES opinion and therefore recommend Chris to launch Mountain Man Lite Beer. Even if Chris launches lite beer, in the first year, 2006, Chris would have little bit more net revenue than if he does not launch, according to the worst scenario (20% cannibalization) and Chris' assumption on Lite beer sales. With the best scenario (5% cannibalization), Chris would have $2.3 million more net revenue in 2006. And every year Mountain Man Lite Beer would cause more net revenue. Maharitraa - Autole Home D Fage Form De e Share Comments O 8A*** Aye & $% Sort A Formatting C G H M N o P Inaeloftro . 2006 2007 2000 SST 00 000 10 11 300 000 300 000 000 200.000 000 14 65,012 35,461 35,461 578000 5100340 35,461 373843 2371 17. Ce to the 18 Ledelach 20 57651 330647 5721120 2004.05 21 Twee Tapeta and al Pection of B + 95,200 2 185766 11,735 65,046 153,800 64,454 151,433 18.13 63,874 149,114 2002 2011 Type here to search GMM U14201 Maharitraa - Autole Home D Fage Form De e Share Comments O 8A*** Aye & $% Sort A Formatting C G H M N o P Inaeloftro . 2006 2007 2000 SST 00 000 10 11 300 000 300 000 000 200.000 000 14 65,012 35,461 35,461 578000 5100340 35,461 373843 2371 17. Ce to the 18 Ledelach 20 57651 330647 5721120 2004.05 21 Twee Tapeta and al Pection of B + 95,200 2 185766 11,735 65,046 153,800 64,454 151,433 18.13 63,874 149,114 2002 2011 Type here to search GMM U14201 Home Page Layout Heves View Hele Share Comments Al Wat aan Murpe Cerme Sete- om Condom Cel Formatting Ty Sort A B D E o R 12.00 income Chris Prats de rensein 2000 2001 SOOD 499 400 419 420 2008 120.000 2010 470.00 1000 3109 1904 550 440 000 14.03.600 1836.00 9,583.600 141229 400 151320 4.731.600 1.57210 DATO 549 01 200 11 442.575 5787372 546.534 250 545.628765 10720 33,023 372.76 12.101732499.00 15262 101/1997 21 16 785.134 750,000 795.40 706702 56247 300340 6727026 5 atm CS TG More SOLA 9 O OD 10 11 O 12 income for 13 Prosine 14 Nico 16 16 Price 17 UVC 24 0 Tapet Operating Protet 205. Eating Pole 591 REN Income Statement Act 11,206 Type here to search GE PM 414201 2069 Mountain Man Brewing Company: Bringing the Brand to Light Exhibit 1 Mountain Man 2005 Income Statement Net Revenues $50,440,000 100.00 COCS 34.803,600 Gross Margin 15,636,400 31.0% SGRA 9,583,600 19.0% Other Operating Expenses 1,412,320 28% Operating Margin 4.640.480 Other Income 151,320 Net Income Before Taxes 4.791 SO Provision for Income Taxes 1,677,130 Net Income After Taxes $3.114.670 Advertising expenses were $1.35 million annually or 2.7% of total revenues Advertising expenses included radio, print, and outdoor advertising, sponsorship well as costs to produce these media Riojor Post Exhibit 2 Profile of Beer Drinkers by Beer Type by Key Demographics, 2005 Domestic Light Beer Gender Male 58 Female Mountain Man Lager 81% Age 21-24 25-34 35-44 45-54 55-66 65+ 23% 15% 19% 32% 14% 13 under $25 14% 16 Do No 21% 16% 15 Source: First two colume of data extracted from Mintet/Simmons NCS 2005 report. figure 67 BRIEF CASES I HARVARD BUSINESS SCHOOL PUBLISHING Merentain Man Brewing Company. Hringing the brand to Light | 2002 cs Exhibit 3 Competitive Market Shares in Barrels by Brewer Anheuser Busch Miller Coors Other 2nd tier Premium & Popular Brewers Craft/Specialty Brewers Imports Total East Central Region 15.620,252 8,553,948 3,347,197 4.668. 557.866 4.462.929 37,191,0877 Note Sales in barrels of wholesale shipments Exhibit 4 Beer Consumption by State, 2000 to 2005 (shipments in barrels) STATE 2000 2002 2003 2001 2004 2005 Illinois 9,038.323 Indiana 3,954,209 Kentucky 2,517894 Michigan 6,761.561 8,493,144 West Virginia 1,274,626 Wisconsin 4.741,019 East Central Region 36,780,776 TOTAL U.S. 197,609,645 Source Beer Institute data. 9,165,351 3,917 46 2.466731 5665 8.601.04 2,311838 4784791 26.15 25 2364,013 6851064 3.680331 59 4.890,122 9,10,157 3,905,265 2,490,828 6.774.702 8.760,272 1,348,527 4.855,313 9,032.851 3,993,643 2.591.949 6,746,578 8,702.382 1,373.205 4.877.662 9/1X3.267 3,998,855 2.555,739 6,700,174 8.584,283 1,359,231 4,929,529 Ohio 36.983,456 37.640.992 37,318,269 37 191,077 37,243,163 202,586,016 202.605,292 204,318, Do Notice of 203,515,145 HARVARD BUSINESS SCHOOL PUBLISHING I BRIEF CASES 2000 | Mountain Man Brewing Company Bringing the rand to Light Exhibits Consumption by Type of Beer and by Origin/Packaging, 2005 A Consumption by Type of Beer EAST CENTRAL % Total 6-year CAGR REGION 18,744.103 7,326,642 4,351,356 Light Beer Premium Beer Popular Imported Premium Superpremium (craft and high-end domestica) Total Barrels 1975 11.7% 12.0% 2.305847 37,191,077 100.0 B. Consumption by Origin/Packaging EAST CENTRAL REGION Imported 4,462.929 Domestic Packaged 29,618,974 Domestic - Draught Total Barrels Do Not Com r Post 10 BRIEF CASES I HARVARD BUSINESS SCHOOL PUBLISHING Copying or posting is an infringement of copyright. Permissions@hbsp.harvard.edu or 617-783-7860. Mountain Man Brewing Company: Bringing the Brand to Light | 2069 Exhibit 6 Light Beer Market Shares and Dominant Brands A. Light Beer Competitive Market Shares East Central Region 2005 Market Share Anheuser-Busch Miller Coors 14% Other brands Imports Total Light Beer 100% B. Leading Domestic Light Beer Brands East Central Region Bud Light Miller Lite Coors Light Natural Light Busch Light Micheleb Ultra Milwaukee's Best Light Other domestic brands Do Wot Copy or Post Total 100 c. Leading Imported Light Beer Brands Brand 2005 Market Share Corona Light Amstel Light Labatt Blue Light Other imported brands Total 100% Note: Market share calculations based on wholesale barrel sales HARVARD BUSINESS SCHOOL PUBLISHING I BRIEF CASES 2069 Mountain Man Brewing Company: Bringing the Brand to Light Exhibit 7 U.S. Beer Advertising Expenditures by Medium (in millions of dollars), 2005 Medium 2005 Network television Cable television Spot television Syndicated television Spot radio Network radio S8823 72.1 144.3 5.5 224 12 Total Broadcast $627.5 232 6.6 Magazines Newspapers Newspaper supplements Outdoor 51.5 Total Print $81.3 TOTAL $709.1 Do Not Copy or Post