Mountain Man Excel
| | | | | | | | | | | | | | | | | | Mountain Man Brewing Company | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Financial Projections| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 2005| 2006| 2007| 2008| 2009| 2010| Assumptions| | | | | | | | | | | Revenues from MM beer| $50,440,000. 00| $ 49,431,200. 00 | $ 48,442,576. 00 | $ 47,473,724. 48 | $ 46,524,249. 99 | $ 45,593,764. 9 | 2% Decrease in Market Size | | | | | | | | | Contribution (Gross Margin)| $ 15,636,400. 00 | $15,323,672. 00| $15,017,198. 56| $14,716,854. 59| $14,422,517. 50| $14,134,067. 15| Exhibit 1| | | | | | | | | | | Loss of Contribution| n. a. | $ 766,183. 60 | $ 750,859. 93 | $ 735,842. 73 | $ 721,125. 87 | $ 706,703. 36 | 5% Loss due to light beer entry| | | | | | | | Launch Advertising Costs| n. a. | $ 750,000. 0 | | | | | Section Challenges Ahead| | | | | | | | | Incremental SG&A expenditures| n. a. | $ 900,000. 00 | $ 900,000. 00 | $ 900,000. 00 | $ 900,000. 00 | $ 900,000. 00 | Section Challenges Ahead| | | | | | | | | Incremental costs associated with light product| $ 2,416,183. 60 | $ 1,650,859. 93 | $ 1,635,842. 73 | $ 1,621,125. 87 | $ 1,606,703. 36 | | | | | | | | | | | | | 34803600| $ 34,107,528. 00 | $ 33,425,377. 4 | $ 32,756,869. 89 | $ 32,101,732. 49 | $ 31,459,697. 84 | COGS 69%| | | | | | | | | | E. C. Light Beer Market| | | | | | | | | | | | | | | | | Assumed market for Light Beer| 18744303| $ 19,494,075. 12 | $ 20,273,838. 12 | $ 21,084,791. 65 | $ 21,928,183. 32 | $ 22,805,310. 65 | 4% Market Growth| | | | | | | | | | Market Share for Mountain Man Light| n. a. | 0. 25%| 0. 50%| 0. 75%| 1. 00%| 1. 25%| From section Chris’s Decision. | | | | | | | | | Barrels Sold of Mountain Man Light| n. . | $ 48,735. 19 | $ 101,369. 19 | $ 158,135. 94 | $ 219,281. 83 | $ 285,066. 38 | | | | | | | | | | | | Contribution of Light Product| n. a. | $ 1,236,899. 07 | $ 2,572,750. 06 | $ 4,013,490. 09 | $ 5,565,372. 93 | $ 7,234,984. 80 | Hint: Get the contribution per barrel of Mountain Man Light In the second work sheet| | | Net Change in Contribution| n. a. | $ (1,179,284. 53)| $ 921,890. 13 | $ 2,377,647. 6 | $ 3,944,247. 05 | $ 5,628,281. 45 | | | | | | | | | | | | Discount Factor | 1. 12| 1. 20| 1. 40| 1. 57| 1. 76| 12% (Footnote #6)| | | | | | | | | | Present Value| $ (1,052,932. 62)| $ 768,241. 78 | $ 1,692,362. 43 | $ 2,506,640. 31 | $ 3,193,638. 04 | | | | | | | | | | | | 5 Year NPV (2006-2010)| | | | | $ 7,107,949. 93 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
