Financial Modeling

profilesatzmar19
BetaValuationforDr.Marone.xlsx
Home>Business & Finance homework help>Financial Modeling

Model

Beta Manufacturing, LLC
Financial Projection Model
($000s)
Historical Financials Five-Year Projections
2014 2015 2016 2017 2018 2019 2020 2021
Income Statement
Revenue $ 1,500 $ 1,600 $ 1,700 $ 1,789 $ 1,934 $ 2,092 $ 2,262 $ 2,447
Revenue Growth Rate 6.7% 6.3% 5.2% 8.2% 8.2% 8.2% 8.1%
Cost of Goods Sold 633 770 820 949 1,026 1,110 1,200 1,298
COGS % of Revenue 42.2% 48.1% 48.2% 53.0% 53.0% 53.0% 53.0% 53.0%
Gross Profit 867 830 880 840 908 982 1,062 1,149
Gross Margin (%) 57.8% 51.9% 51.8% 47.0% 47.0% 47.0% 47.0% 47.0%
Selling General & Administrative 550 650 740 715 774 837 905 979
SG&A % of Revenue 36.7% 40.6% 43.5% 40.0% 40.0% 40.0% 40.0% 40.0%
Operating Income 317 180 140 124 135 145 157 170
Operating Margin (%) 21.1% 11.3% 8.2% 7.0% 7.0% 7.0% 7.0% 7.0%
Interest Expense 0 (10) (9) (9) (8) (7) (7) (6)
Interest Income 0 1 1 2 2 2 3 3
Net Interest Expense 0 (10) (8) (7) (6) (5) (4) (2)
Pretax Income 317 170 132 117 128 140 154 168
Provision For Taxes 100 60 50 41 45 49 54 59
Effective Tax Rate (%) 32% 35% 38% 35% 35% 35% 35% 35%
Net Income 217 110 82 76 83 91 100 109
Net Margin (%) 14.5% 6.9% 4.8% 4.3% 4.3% 4.4% 4.4% 4.5%
Balance Sheet 2014 2015 2016 2017 2018 2019 2020 2021
Assets
Current Assets
Cash 50 113 152 173 209 250 298 351
Accounts Receivable 200 220 230 248 269 291 314 340
Inventory 120 130 140 166 180 194 210 227
Prepaid Expenses 250 280 325 322 348 377 407 440
Total Current Assets 620 743 847 909 1,005 1,112 1,229 1,359
Property, Plant & Equipment 900 980 1,065 1,154 1,251 1,356 1,469 1,591
Accumulated Depreciation 150 198 249 303 361 423 491 565
Net PP&E 750 782 816 852 890 932 978 1,026
Amortizable Intangibles 0 0 20 16 12 8 4 0
Goodwill 150 150 150 150 150 150 150 150
Total Assets 1,520 1,675 1,833 1,927 2,058 2,202 2,361 2,535
Liabilities
Current Liabilities
Accounts Payable 200 250 300 316 342 370 400 433
Accrued Liabilities 280 320 340 351 379 410 443 480
Other Liabilities 140 145 150 161 174 188 204 220
Total Current Liabilities 620 715 790 828 895 968 1,047 1,132
Revolving Credit Facility 77 0 0 0 0 0 0 0
Term Notes 100 100 100 80 60 40 20 0
Unsecured Notes 50 50 50 50 50 50 50 50
Total Liabilities 847 865 940 958 1,005 1,058 1,117 1,182
Shareholders' Equity
Common Stock 673 700 700 700 700 700 700 700
Retained Earnings 0 110 193 269 352 444 543 653
Total Shareholders' Equity 673 810 893 969 1,052 1,144 1,243 1,353
Total Liabilities and Shareholders' Equity 1,520 1,675 1,833 1,927 2,058 2,202 2,361 2,535
Check 0 0 0 0 0 0 0 0
Cash Flow Statement 2014 2015 2016 2017 2018 2019 2020 2021
Cash Flow from Operations
Net Income na 110 82 76 83 91 100 109
Depreciation na 48 51 54 58 63 68 73
Amortization na 0 0 4 4 4 4 4
Changes in Working Capital
Accounts Receivable na (20) (10) (18) (20) (22) (24) (26)
Inventory na (10) (10) (26) (14) (15) (16) (17)
Prepaid Expenses na (30) (45) 3 (26) (28) (31) (33)
Accounts Payable na 50 50 16 26 28 30 33
Accrued Liabilities na 40 20 11 29 31 33 36
Other Current Liabilities na 5 5 11 13 14 15 17
Total Cash Flow from Operations na 193 143 130 153 166 180 196
Cash Flow from Investing Activities na
Capital Expenditure na (80) (85) (89) (97) (105) (113) (122)
Intangible Assets na 0 (20) 0 0 0 0 0
Total Cash Flow from Investing Activities na (80) (105) (89) (97) (105) (113) (122)
Cash Flow from Financing Activities
Change in Revolver na (77) 0 0 0 0 0 0
Change in Term Loan na 0 0 (20) (20) (20) (20) (20)
Change in Unsecured Notes na 0 0 0 0 0 0 0
Change in Common Stock na 27 0 0 0 0 0 0
Total Cash Flow from Financing Activities na (50) 0 (20) (20) (20) (20) (20)
Net Change in Cash Flows na 63 38 21 36 41 47 54
Beginning Cash na 50 113 152 173 209 250 298
Ending Cash Flow na 113 152 173 209 250 298 351
Cash Before Revolver na 190 152 173 209 250 298 351
Minimum Cash on Hand na 75 75 75 75 75 75 75
Cash Excess (Requirement) na 115 77 98 134 175 223 276
Debt and Interest Schedule
Beginning Revolver Balance 0 77 0 0 0 0 0 0
Drawdown (Paydown) 0 (77) 0 0 0 0 0 0
Ending Revolver Balance 0 0 0 0 0 0 0 0
Revolver Interest Rate 3.5% 3.5% 3.5% 3.8% 4.0% 4.3% 4.5% 4.8%
Revolver Interest Paid 0 1 0 0 0 0 0 0
Beginning Term Loan Balance 0 100 100 100 80 60 40 20
Term Loan Amortization 0 0 0 (20) (20) (20) (20) (20)
Ending Term Loan Balance 0 100 100 80 60 40 20 0
Term Loan Interest Rate 4.0% 4.0% 4.0% 4.3% 4.5% 4.8% 5.0% 5.3%
Term Loan Interest Paid 0 4 4 4 3 2 1 1
Beginning Unsecured Note Balance 0 50 50 50 50 50 50 50
Unsecured Note Amortization 0 0 0 0 0 0 0 0
Ending Unsecured Note Balance 0 50 50 50 50 50 50 50
Unsecured Note Interest Rate 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0%
Unsecured Note Interest Paid 0 5 5 5 5 5 5 5
Total Interest Expense 0 10 9 9 8 7 7 6
Total Cash Interest Income 0 1 1 2 2 2 3 3

Assumptions

Beta Manufacturing, LLC
Model Assumptions
($000s)
Income Statement Assumptions 2014 2015 2016 2017 2018 2019 2020 2021
Number of Units Sold 500 525 551 579 608 638 670 704
Unit sales expected to grow 5% per year 5% 5% 5% 5% 5%
Price Per Unit $ 3.00 $ 3.00 $ 3.00 $ 3.09 $ 3.18 $ 3.28 $ 3.38 $ 3.48
Price according to cost increases below 3% 3% 3% 3% 3%
Number of Units Produced 1,500 1,500 1,500 1,500 1,500 1,500 1,500 1,500
Assume 0% growth in units produced 0% 0% 0% 0% 0%
Cost per Unit $ 1.50 $ 1.55 $ 1.59 $ 1.64 $ 1.69 $ 1.74 $ 1.79 $ 1.84
Costs expected to increase 3% per year 3% 3% 3% 3% 3%
COGS as % of Revenue 42% 48% 48% 53% 53% 53% 53% 53%
Calculate COGS as % of Revenue from Income Statement
SG&A as % of Revenue 37% 41% 44% 40% 40% 40% 40% 40%
Use 3-year average SG&A as % of Revenue from Income Statement
Depreciation as % of Revenue 3% 3% 3% 3% 3% 3% 3% 3%
Use 3-year average Depreciation as % of Revenue from Cash Flow Statement
Tax Rate 32% 35% 38% 35% 35% 35% 35% 35%
Use 3-year average Provision for Taxes as a % of Pre-Tax Income
Balance Sheet Assumptions 2014 2015 2016 2017 2018 2019 2020 2021
Minimum Cash Requirement Minimum cash of $75M in any given year na $ 75 $ 75 $ 75 $ 75 $ 75 $ 75 $ 75
Accounts Receivable Days Select reasonable assumtion for projection period 48 50 49 50 50 50 50 50
Inventory Days Select reasonable assumtion for projection period 68 61 61 63 63 63 63 63
Prepaid Expenses as % of Revenue Select reasonable assumtion for projection period 17% 18% 19% 18% 18% 18% 18% 18%
Capital Expenditure as % of Revenue Select reasonable assumtion for projection period 5% 5% 5% 5% 5% 5% 5% 5%
Amortizable Intangibles Use straight-line (even) depreciation over 5 years $ - 0 $ - 0 $ 20 $ 4 $ 4 $ 4 $ 4 $ 4
Goodwill Assume no new acquisitions $ 150 $ 150 $ 150 $ 150 $ 150 $ 150 $ 150 $ 150
Days Accounts Payable Select reasonable assumtion for projection period 114 117 132 120 120 120 120 120
Accrued Liabilities as % of Revenue Select reasonable assumtion for projection period 18.7% 20.0% 20.0% 20% 20% 20% 20% 20%
Other Current Liabilities as % of Revenue Select reasonable assumtion for projection period 9.3% 9.1% 8.8% 9% 9% 9% 9% 9%
Interest Rates on Debt
LIBOR Caclulate based on annual LIBOR increase 1.50% 1.50% 1.50% 1.75% 2.00% 2.25% 2.50% 2.75%
Annual LIBOR Increase Assume annual LIBOR increase of 25 basis points 0.00% 0.00% 0.25% 0.25% 0.25% 0.25% 0.25%
Revolving Credit Facility Calculate rate based on LIBOR+Spread Input LIBOR Spread 2.00% 3.50% 3.50% 3.50% 3.75% 4.00% 4.25% 4.50% 4.75%
Term Notes Calculate rate based on LIBOR+Spread Input LIBOR Spread 2.50% 4.00% 4.00% 4.00% 4.25% 4.50% 4.75% 5.00% 5.25%
Unsecured Notes 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00% 10.00%
Term Loan Amortization Assume Term Loan Amortization of $20M/year starting 2017 0 0 0 20 20 20 20 20
Unsecured Note Amortization Assume Unsecured Note Amortization of $0M/year starting 2017 0 0 0 0 0 0 0 0
Cash Interest Rate Select reasonable assumtion for projection period 1.00% 1.00% 1.00% 1.00% 1.00% 1.00% 1.00% 1.00%

Comp Summary

Beta Manufacturing, LLC
Comparable Public Companies Analysis
($000s)
Comparable Companies
Multiples Analysis
Share
Price % off 52- Market Enterprise TEV / Revenue TEV / EBITDA Price / Earnings
Company Name 12/31/16 week High Capitalization Value Cash Debt LTM FYE+1 LTM FYE+1 LTM FYE+1
Alpha Co. $48.24 1.5% 31,075 41,869 274 -0 11,068 2.2x 2.2x 12.2x 12.6x 21.5x 19.8x
Gamma Holdings 60.70 1.5% 15,213 18,700 529 -0 4,017 1.4x 1.2x 9.7x 9.0x 16.4x 16.3x
Theta Inc. 30.50 2.3% 21,784 26,256 1,118 -0 5,590 2.2x 2.2x 12.9x 12.8x 19.0x 19.0x
Sigma Co. 45.90 1.7% 31,272 39,705 816 -0 9,249 2.3x 2.3x 11.0x 11.7x 16.4x 17.6x
Median 1.6% 26,429 ERROR:#NUM! 32,981 ERROR:#NUM! 672 -0 7,420 ERROR:#NUM! 2.2x 2.2x 11.6x 12.2x 17.7x 18.3x
Mean 1.7% 24,836 ERROR:#DIV/0! 31,633 ERROR:#DIV/0! 684 -0 7,481 ERROR:#DIV/0! 2.0x 2.0x 11.5x 11.5x 18.3x 18.2x
Margins Analysis
Revenue Growth EBITDA Growth EPS Growth CapEx % of Rev EBIT Margin EBITDA Margin Net Margin
Company Name FYE 2015 FYE 2016 FYE 2015 FYE 2016 FYE 2015 FYE 2016 LTM LTM FYE+1 LTM FYE+1 LTM FYE+1
Alpha Co. 4.8% 3.1% -4.7% 3.2% -24.9% 9.0% 2.2% 16.1% 15.2% 18.0% 17.1% 7.6% 8.1%
Gamma Holdings 7.8% 17.8% -2.9% 24.1% -5.5% 24.0% 2.6% 11.2% 10.4% 13.9% 13.2% 6.7% 5.9%
Theta Inc. 8.8% 1.5% 3.7% 1.2% 7.1% 4.7% 3.6% 14.4% 14.4% 17.4% 17.3% 9.8% 9.6%
Sigma Co. 11.9% 4.8% 2.1% 3.0% -9.4% 8.0% 3.9% 17.9% 16.2% 21.2% 19.5% 11.0% 10.1%
Median 8.3% ERROR:#NUM! 4.0% ERROR:#NUM! -0.4% ERROR:#NUM! 3.1% ERROR:#NUM! -7.4% ERROR:#NUM! 8.5% ERROR:#NUM! 3.1% ERROR:#NUM! 15.2% 14.8% 17.7% 17.2% 8.7% 8.9%
Mean 8.3% ERROR:#DIV/0! 6.8% ERROR:#DIV/0! -0.5% ERROR:#DIV/0! 7.9% ERROR:#DIV/0! -8.2% ERROR:#DIV/0! 11.4% ERROR:#DIV/0! 3.1% ERROR:#DIV/0! 14.9% 14.0% 17.6% 16.8% 8.8% 8.4%

DCF

Beta Manufacturing, LLC
Discounted Cash Flow Valuation
Financial Projections
Fiscal Year Ending ($MMs) 12/31/17 12/31/18 12/31/19 12/31/20 12/31/21
Revenue $1,788.5 $1,934.3 $2,091.9 $2,262.4 $2,446.8
EBIT 124.4 134.5 145.5 157.4 170.2
Depreciation and Amortization 57.7 62.0 66.8 71.9 77.4
EBITDA $182.0 $196.6 $212.3 $229.2 $247.6
Capital Expenditures (89.4) (96.7) (104.6) (113.1) (122.3)
Working Capital Requirements 24.4 28.7 33.4 38.6 44.2
Discounted Cash Flow (DCF) Analysis - Beta Manufacturing, LLC, a private company Key Assumptions
12/31/17 12/31/18 12/31/19 12/31/20 12/31/21 Discount Rate and Tax Rate Assumptions 8.5% Stub Period
Discount Rate 8.1%
Revenue $151.90 $1,934.3 $2,091.9 $2,262.4 $2,446.8 Tax Rate 35.0% 11/30/17 Transaction/Valuation Date
35.0% Tax Rate
EBIT $10.6 $134.5 $145.5 $157.4 $170.2
Less: Taxes (3.7) (47.1) (50.9) (55.1) (59.6) Terminal Value Assumptions 11.6x Terminal EBITDA Multiple
Net Income $6.9 $87.4 $94.6 $102.3 $110.6 Terminal Year EBITDA $248 8.1% Discount Rate (levered)
Less: Capital Expenditures (89.4) (96.7) (104.6) (113.1) (122.3) Terminal EBITDA Multiple 11.62 2.5% Gordon Terminal Growth Rate
Less: Working Capital Requirements (24.4) (28.7) (33.4) (38.6) (44.2)
Plus: Depreciation and Amortization 57.7 62.0 66.8 71.9 77.4 Terminal Value $2,876 N Gordon Growth Approach (Y/N)? If no, then it's the Terminal Multiple Method
Free Cash Flow ($49.3) $24.1 $23.3 $22.4 $21.5 Discount Period 4.08
Discount Period 0.08 1.08 2.08 3.08 4.08 Discount Factor @ 8.1% 0.73 0.5x Terminal Multiple Increment
Discount Factor @ 8.1% 0.99 0.92 0.85 0.79 0.73 PV of Terminal Value $2,089 0.5% Discount Rate Increment
PV of Cash Flow $46.5 ($13.3) ($19.8) ($23.8) ($26.4) 0.5% Terminal FCF Growth Increment
Distribution of Value
Total NPV Valuation, Terminal Multiple Method $2,052.59 Period Cash Flow
Terminal Cash Flow
Total 0.0%
Implied Analyses
LTM EBITDA Multiple
Forward EBITDA Multiple

WACC

Beta Manufacturing, LLC
Weighted Average Cost of Capital (WACC) Analysis
($000s)
Comparable Public Companies
Market Debt to Preferred to Equity to
Preferred Value of Total Debt to Total Total Total
Debt Stock Equity Capitalization Total Equity Capitalization Capitalization Capitalization
Alpha Co. $11,067.8 $0.0 $31,075.2 $41,868.8 35.6% 26.4% 0.0% 74.2%
Gamma Holdings $4,016.5 0.0 15,212.7 18,700.0 26.4% 21.5% 0.0% 81.4%
Theta Inc. $5,590.4 0.0 21,783.8 26,256.2 25.7% 21.3% 0.0% 83.0%
Sigma Co. $9,249.2 0.0 31,272.0 39,705.4 29.6% 23.3% 0.0% 78.8%
Median $7,419.8 $0.0 $26,429.5 $32,980.8 28.0% 22.4% 0.0% 80.1%
Mean $7,481.0 $0.0 $24,835.9 $31,632.6 29.3% 23.1% 0.0% 79.3%
Equity
Levered Unlevered Risk Cost of Cost of Cost of
Beta Beta Premium (1) Equity Debt Preferred WACC
Alpha Co. 1.50 0.72 8.1% 14.5% 8.8% 0.0% 12.26%
Gamma Holdings 0.60 0.31 8.1% 7.2% 4.9% 0.0% 6.53%
Theta Inc. 0.92 0.48 8.1% 9.8% 7.0% 0.0% 9.08%
Sigma Co. 0.71 0.36 8.1% 8.1% 5.5% 0.0% 7.20%
2.3%
Median 0.82 0.42 8.9% 6.3% 0.0% 8.1%
Mean 0.93 0.46 9.9% 6.6% 0.0% 8.8%
Weighted Average Cost of Capital (WACC) Analysis (cont.)
Project Beta Manufacturing, LLC
Market Assumptions Beta Assumptions Capital Structure Assumptions
10-Year Treasury Bond Yield 2.33% Selected Adjusted Unlevered Beta 0.42
Equity Risk Premium 8.10% Levered Beta 0.49 Debt to Enterprise Value 22.4%
Tax Rate 35.0% Equity to Enterprise Value 80.1%
Company-Specific Risk Premium 0.0% Cost of Debt (Pre-tax) 9.6%
Cost of Preferred 0.0%
Cost of Equity 4.0%
Concluded Weighted Average Cost of Capital

Valuation Summary

Beta Manufacturing, LLC
Valuation Summary
($000s)
Private Co. Multiples Range Latest Quarter (1) Implied TEV Range
Methodology Rev, EBITDA, Net Inc. Low -- High Cash Total Debt Low -- High
Comparable Public Companies
Revenue Multiple: TEV / Revenue
LTM $1,700.0 1.35x -- 2.33x $151.80 $7,480.99 2,300.7 -- 3,957.2
FYE + 1 $1,788.5 1.19x -- 2.28x $172.59 7,480.99 2,133.3 -- 4,081.1
EBITDA Multiple: TEV / EBITDA
LTM $191.0 9.7x -- 12.9x $151.80 7,481.0 1,860.6 -- 2,469.2
FYE + 1 $182.0 9.0x -- 12.8x $172.59 7,481.0 1,642.4 -- 2,323.3
Market Multiple: Price / Earnings
LTM $82.3 16.4x -- 21.5x $151.80 150.0 1,347.4 -- 1,769.8
FYE + 1 $76.2 16.3x -- 19.8x $172.59 150.0 1,220.1 -- 1,484.0
Precedent Transactions (Controlling Interest Value)
LTM Revenue $1,700.0 2.25x -- 2.50x 3,825.0 -- 4,250.0
LTM EBITDA 191.0 12.0x -- 12.5x 2,292.0 -- 2,387.5
Discounted Cash Flow (Controlling Interest Value), Terminal Multiple Approach EBITDA Multiple 0.5x 27.9
Gordon Growth 0.5 2.50%
Minority Interest Value
Median $1,751.5 -- $2,396.3
Mean $1,750.7 -- $2,680.8
Controlling Interest Value
Median $2,292.0 -- $1,207.7
Mean $1,529.5 -- $1,666.4
(1) Beta Manufacturing Co. does not have preferred stock or minority interest on its balance sheet.