Scribd Logo
Upload

Welcome to Scribd!

  • 720 views

    Correction Mountain Man Brewing Company - Group2 Sec H 2014

    Uploaded by

    Tatsat Pandey
    The document provides an income statement forecast for Mountain Man's beer business from 2005-2010. It forecasts revenues, costs of goods sold, gross margins, operating expenses, operating margins, taxes, and profits for their lager beer and a new light beer. It also provides a combined forecast taking into account potential cannibalization of lager sales by the new light beer. The net present value of the six year forecast was calculated to be $13,344,509.94 using a 12% discount rate, indicating the new light beer is financially viable.

    Copyright:

    © All Rights Reserved
    Download as XLS, PDF, TXT or read online from Scribd

    Correction Mountain Man Brewing Company - Group2 Sec H 2014

    Uploaded by

    Tatsat Pandey
    720 views12 pages
    The document provides an income statement forecast for Mountain Man's beer business from 2005-2010. It forecasts revenues, costs of goods sold, gross margins, operating expenses, operating margins, taxes, and profits for their lager beer and a new light beer. It also provides a combined forecast taking into account potential cannibalization of lager sales by the new light beer. The net present value of the six year forecast was calculated to be $13,344,509.94 using a 12% discount rate, indicating the new light beer is financially viable.

    Original Description:

    MK

    Original Title

    Correction Mountain Man Brewing Company_Group2 Sec H 2014

    Copyright

    © © All Rights Reserved

    Available Formats

    XLS, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides an income statement forecast for Mountain Man's beer business from 2005-2010. It forecasts revenues, costs of goods sold, gross margins, operating expenses, operating margins, taxes, and profits for their lager beer and a new light beer. It also provides a combined forecast taking into account potential cannibalization of lager sales by the new light beer. The net present value of the six year forecast was calculated to be $13,344,509.94 using a 12% discount rate, indicating the new light beer is financially viable.

    Copyright:

    © All Rights Reserved
    Download as XLS, PDF, TXT or read online from Scribd
    Download as xls, pdf, or txt
    720 views12 pages

    Correction Mountain Man Brewing Company - Group2 Sec H 2014

    Uploaded by

    Tatsat Pandey
    The document provides an income statement forecast for Mountain Man's beer business from 2005-2010. It forecasts revenues, costs of goods sold, gross margins, operating expenses, operating margins, taxes, and profits for their lager beer and a new light beer. It also provides a combined forecast taking into account potential cannibalization of lager sales by the new light beer. The net present value of the six year forecast was calculated to be $13,344,509.94 using a 12% discount rate, indicating the new light beer is financially viable.

    Copyright:

    © All Rights Reserved
    Download as XLS, PDF, TXT or read online from Scribd
    Download as xls, pdf, or txt
    You are on page 1of 12

    Revenues

    COGS
    Gross Margin
    SG&A Non Adv
    SG&A Adv
    Other Expenses
    Operating Margin
    Other Income
    PBT
    Income Tax
    PAT
    Profit Percentage

    Income Statement Forecast for 2006-2010 (all figures in $)


    (When Mountain Man produces only the Lager Beer)
    2005
    2006
    2007
    50440000
    49,431,200
    48,442,576
    34803600
    34,107,528
    33,425,377
    15636400
    15323672
    15017198.56
    8,221,720
    8,221,720
    8,221,720
    1,361,880
    1,334,642
    1,307,950
    1412320
    1384073.6
    1356392.128
    4,640,480
    4,383,236
    4,131,137
    151,320
    148,294
    145,328
    4,791,800
    4,531,530
    4,276,465
    1677130
    1586035.36
    1496762.613
    3,114,670
    2,945,494
    2,779,702
    6.18
    5.96
    5.74
    (When Mountain Man produces only the Light Beer)

    Selling Price/lager beer


    Base Market Share
    Market Increase
    Total Market Revenue of Light
    Mkt Share Increase
    Sales of Light Beer
    Revenues Total (Light)

    Revenues
    COGS
    Gross Margin
    SG&A Non Adv
    SG&A Adv
    Other Expenses
    Operating Margin
    PBT
    Income Tax
    PAT
    Profit Margin

    97

    18744303.00

    0
    0

    97
    46860.76
    4.00%
    19494075.12
    0.25%

    97
    4.00%
    20273838.12
    0.50%

    48,735
    4,727,313

    101,369
    9,832,811

    Income Statement Forecast for 2006-2010 (all figures in $)


    2005
    2006
    2007
    0
    4,727,313
    9,832,811
    0
    3,488,757
    7,256,615
    0
    1,238,556
    2,576,197
    0
    900,000
    900,000
    0
    0
    0
    0
    0
    0

    750,000
    132,365
    -543,809
    -529,627
    0
    -529,627
    -11.20

    265,486
    275,319
    1,135,392
    1,164,890
    407711.64
    757,179
    7.70

    Income Statement Forecast for 2006-2010 (all figures in $) after considering cannibalisation of lager in first
    After considering cannibalisation of lager in first year (12.5%)
    Revenue from Light
    4727313.22
    9832811.49
    Revenue from Lager without canb
    50440000.00
    49431200.00
    48442576.00

    Cannibalization
    Revenue from Lager WITH canb

    50440000.00

    12.50%
    47979613.22

    10.00%
    53431129.89

    2005

    2006

    2007

    50,440,000

    47,979,613

    53,431,130

    COGS
    Gross Margin
    SG&A Non Adv
    SG&A Adv
    Other Expenses
    Operating Margin
    Other Income
    PBT
    Income Tax
    PAT
    Profit Percentage

    34,803,600
    15,636,400
    8,221,720
    1,361,880
    1412320
    4,640,480
    151,320
    4,791,800
    1677130
    3,114,670
    6.18

    33,105,933
    14,873,680
    9,121,720
    1,295,450
    1343429.17
    3,113,081
    143,939
    3,257,020
    1139957.073
    2,117,063
    4.41

    36,867,480
    16,563,650
    9,121,720
    1,442,641
    1496071.637
    4,503,218
    160,293
    4,663,512
    1632229.029
    3,031,282
    5.67

    Total Revenue
    Total PAT (Light +Lager)
    Total Profit Percentage

    50,440,000
    3,114,670
    6.18

    47,979,613
    2,117,063
    4.41

    53,431,130
    3,031,282
    5.67

    PAT(Lager only)
    PAT(Light only)
    PAT(Combination)

    2005
    3,114,670
    0
    3,114,670

    2006
    2,945,494
    -529,627
    2,117,063

    2007
    2,779,702
    757,179
    3,031,282

    Total Revenues

    7,000,000
    6,000,000
    5,000,000
    4,000,000
    3,000,000
    2,000,000
    1,000,000
    0
    2004
    -1,000,000

    2005

    2006

    2007

    2008

    2009

    10 (all figures in $)
    the Lager Beer)
    2008
    47,473,724
    32,756,870
    14716854.59
    8,221,720
    1,281,791
    1329264.285
    3,884,080
    142,421
    4,026,501
    1409275.321
    2,617,226
    5.51

    2009
    46,524,250
    32,101,732
    14422517.5
    8,221,720
    1,256,155
    1302679
    3,641,964
    139,573
    3,781,536
    1323537.774
    2,457,999
    5.28

    2010
    45,593,765
    31,459,698
    14134067.15
    8,221,720
    1,231,032
    1276625.42
    3,404,690
    136,781
    3,541,471
    1239514.979
    2,301,956
    5.05

    the Light Beer)


    97

    97

    97

    4.00%
    21084791.65
    0.75%

    4.00%
    21928183.32
    1.00%

    4.00%
    22805310.65
    1.25%

    158,136
    15,339,186

    219,282
    21,270,338

    285,066
    27,651,439

    10 (all figures in $)
    2008
    15,339,186
    11,320,319
    4,018,867
    900,000

    2009
    21,270,338
    15,697,509
    5,572,829
    900,000

    2010
    27,651,439
    20,406,762
    7,244,677
    900,000

    414,158
    429,497
    2,275,211
    2,321,229
    812430.17
    1,508,799
    9.84

    574,299
    595,569
    3,502,960
    3,566,771
    1248369.83
    2,318,401
    10.90

    746,589
    774,240
    4,823,848
    4,906,802
    1717380.78
    3,189,421
    11.53

    ering cannibalisation of lager in first year (12.5%)


    in first year (12.5%)
    15339185.93
    21270337.82
    27651439.16
    47473724.48
    46524249.99
    45593764.99

    8.00%
    59015012.45

    6.00%
    65003132.81

    5.00%
    70965515.90

    2008

    2009

    2010

    59,015,012

    65,003,133

    70,965,516

    40,720,359
    18,294,654
    9,121,720
    1,593,405
    1652420.349
    5,927,108
    177,045
    6,104,153
    2136453.624
    3,967,700
    6.72

    44,852,162
    20,150,971
    9,121,720
    1,755,085
    1820087.719
    7,454,079
    195,009
    7,649,088
    2677180.892
    4,971,907
    7.65

    48,966,206
    21,999,310
    9,121,720
    1,916,069
    1987034.445
    8,974,487
    212,897
    9,187,383
    3215584.086
    5,971,799
    8.42

    59,015,012
    3,967,700
    6.72

    65,003,133
    4,971,907
    7.65

    70,965,516
    5,971,799
    8.42

    2008
    2,617,226
    1,508,799
    3,967,700

    2009
    2,457,999
    2,318,401
    4,971,907

    2010
    2,301,956
    3,189,421
    5,971,799

    PAT(Lager only)
    PAT(Light only)
    PAT(Combination)

    2010

    2011

    References

    Exhibit 1 Page 8. 2006 onwards 2% decrease in revenue per year


    Assuming 69% of revenue is COGS (66.93/97=0.69) pg8, Exhibit 1
    Gross Margin = Revenues - COGS
    Given non adv SG&A remain constant
    Assuming 2.7% of revenue - pg8, Exhibit 1
    Assuming 2.8% of revenue - pg8, Exhibit 1
    Operating Margin = Gross Margin - SG&A - Other Expenses
    Assuming 0.3% of revenue - pg8, Exhibit 1
    PBT = Operating Margin + Other Income
    Assuming 35% of PBT (2005 Data of Exhibit1 page8)
    PAT = PBT - Income Tax

    (PAT*100)/Revenue

    Revenues = 50440000 (Exhibit 1), Volume = 520,000 (pg2.para3), SP = Revenue/Volume


    18744303 Exhibit 5A Page 10 and Base mkt share = .25% (Page7, para2 line6)
    Page7 para2 line5
    Page7 para2 line6. Also Increasing @ 0.25% every year
    4% . 2006 onwards mkt grows at 4%. Product growth increases at .25%.
    Revenue= Sales Volume * SP

    As Calculated above in RevenuesTotal


    Assuming 73.8% COGS (as % revenue): (66.93+4.69)/97 = 0.738
    Gross Margin = Revenues - COGS
    Fixed Non SG&A of 900000 - pg6 para4
    Adv. Expense of 2.7% of revenue after first year. 1st year = 750000 - pg6
    para 4
    Exhibit1 Page8 (2.8% of Revenue)
    Operating Margin = Gross Margin - SG&A - Other Expenses
    PBT = Operating Margin +Other Income
    Assuming 35% of PBT
    PAT = PBT - Income Tax

    (PAT*100)/Revenue

    From Revenue of Only Ligh Case Above


    From Revenue of Only Lager Case Above

    Page6 para3 line5 (5+20)/2=12.5%. Then assuming lesser cannib


    in further years
    Rev from Lager * (100 - Canb. Rate)

    Assuming 2% decrease in revenue per year; 2006 cannibalisatino of


    12.5%. Cannibalization Rate reduced each year.
    Assuming 69% of revenue is COGS (66.93/97=0.69) pg8, Exhibit 1
    Gross Margin = Revenues - COGS
    Given non adv SG&A remain constant
    Assuming 2.7% of revenue - pg8, Exhibit 1
    Assuming 2.8% of revenue - pg8, Exhibit 1
    Operating Margin = Gross Margin - SG&A - Other Expenses
    Assuming 0.3% of revenue - pg8, Exhibit 1
    PBT = Operating Margin +Other Income
    Assuming 35% of PBT
    PAT = PBT - Income Tax

    Fixed cost
    Advertising
    SG&A
    Total fixed cost
    Variable cost/barrel
    Variable cost
    Additional VC
    Total variable cost/barrel
    price/barrel
    Breakeven (no. of light barrels)

    Breakeven analysis (No Cannibalization)


    $
    References
    750000 Line 8, Para 4, Page 6,
    900000 Line 9, para 4, page 6
    1650000

    66.93 Line 12, para 4, page 6


    4.699 Line 12, para 4, page 6
    71.629
    Net Revenue (Exhibit 1)/ 520000 (line2, para3,
    97 page2)
    65034.88 TFC/(SP-TVC)

    Demand
    No of barrels of Light

    =SUM(PAT!C24:D24) Sum of sales volumes of


    150,104 2005 & 2006

    As Breakeven barrels is less than the demand, it can be conculded that the Light beer will be able to recover its fixed costs

    Assumption: price is same for Light and lager (page 6, para 4, line 11
    )

    ble to recover its fixed costs within two years which was consistent with the company's expectations

    Cash flow
    12%
    -1,650,000
    3,114,670
    2,117,063
    3,031,282
    3,967,700
    4,971,907
    5,971,799

    Year
    Annual Discount rate
    Initial Investment
    Cash flow from Year 1
    Cash flow from Year 2
    Cash flow from Year 3
    Cash flow from Year 4
    Cash flow from Year 5
    Cash flow from Year 6

    $13,344,509.94 =A4+NPV(A3,A5,A6,A7,A8,A9,A10)

    Formula for NPV

    Reference
    Footnote 6, Page 7
    Line 8 &9, Para 4, Page 6
    PAT from Combination 2005
    PAT from Combination 2006
    PAT from Combination 2007
    PAT from Combination 2008
    PAT from Combination 2009
    PAT from Combination 2010

    Rate(r )
    Year(Y)
    0
    1
    2
    3
    4
    5
    6

    0.12
    Discount factor (D)
    1
    0.892857143
    0.797193878
    0.711780248
    0.635518078
    0.567426856
    0.506631121

    NPV

    Footnote 6, Page 7
    PV = D*Y
    -1650000
    2780955.357 D = 1 / (1.12)^Y
    1687709.771
    2157606.997
    2521544.817
    2821193.767
    3025499.231

    13344509.94

    P=D*Y

    You might also like