Bonus Assignment - DCF Valuation - Start

xlsm

School

Toronto Metropolitan University *

*We aren’t endorsed by this school

Course

631

Subject

Finance

Date

Feb 20, 2024

Type

xlsm

Pages

Uploaded by ElderWater15776

IGM Control Panel Key Model Drivers: Key Performance Indicators: Year of Interest: Sales Growth Rate 7.0% Gross Profit Margin 23.0% Net Profit Margin (NI/S) Dividend Payout Ratio 25.0% Asset Turnover (S/A) Capital expenditures as % of sales 5.0% Equity Multiplier (A/E) Terminal Value Parameters Return on Equity WACC (stable period) 7.50% Reinvestment Rate (K) ROIC (stable period) 7.50% Return on Invested Capital Growth Rate (stable period) 2.00% Valuation as of: 9/30/2015 Enterprise Value Less Net Debt Equity Value Shares Outstanding Equity Value per Share

2016P % 7.7% % 103.4% % 259.2% % 20.7% % 49.2% % 12.5% $mm 390,217.8 $mm 50,110.3 $mm 340,107.5 mm 1,600.0 $ 212.57

Scenario Analysis 2 Scenario 1 Base Case + 10% 7.7% 25.3% 27.5% 2 Base Case 7.0% 23.0% 25.0% 3 Base Case - 10% 6.3% 20.7% 22.5% 4 Sales Growth Rate - Low 6.3% 23.0% 25.0% 5 Gross Profit Margin - Low 7.0% 20.7% 25.0% 6 Dividend Payout Ratio - Low 7.0% 23.0% 22.5% 7 Capital expenditures as % of sales - Low 7.0% 23.0% 25.0% 8 WACC (stable period) - Low 7.0% 23.0% 25.0% 9 ROIC (stable period) - Low 7.0% 23.0% 25.0% 10 Growth Rate (stable period) - Low 7.0% 23.0% 25.0% 11 Sales Growth Rate - High 7.7% 23.0% 25.0% 12 Gross Profit Margin - High 7.0% 25.3% 25.0% 13 Dividend Payout Ratio - High 7.0% 23.0% 27.5% 14 Capital expenditures as % of sales - High 7.0% 23.0% 25.0% 15 WACC (stable period) - High 7.0% 23.0% 25.0% 16 ROIC (stable period) - High 7.0% 23.0% 25.0% 17 Growth Rate (stable period) - High 7.0% 23.0% 25.0% 2 Base Case 7.00% 23.00% 25.00% Output Scenario 1 Base Case + 10% 0.079121 1.04096786 2.5939834 2 Base Case 0.077276 1.03440041 2.5919679 3 Base Case - 10% 0.075405 1.02795613 2.5904584 4 Sales Growth Rate - Low 0.078002 1.02871407 2.5881212 5 Gross Profit Margin - Low 0.074696 1.03564389 2.6023538 6 Dividend Payout Ratio - Low 0.077276 1.03233744 2.5837646 7 Capital expenditures as % of sales - Low 0.077276 1.03440041 2.5919679 8 WACC (stable period) - Low 0.077276 1.03440041 2.5919679 Case Number: Case Number Sales Growth Rate Gross Profit Margin Dividend Payout Ratio Case Number Net Profit Margin (NI/S) Asset Turnover (S/A) Equity Multiplier (A/E)

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

9 ROIC (stable period) - Low 0.077276 1.03440041 2.5919679 10 Growth Rate (stable period) - Low 0.077276 1.03440041 2.5919679 11 Sales Growth Rate - High 0.076559 1.04007482 2.5958179 12 Gross Profit Margin - High 0.079855 1.03315992 2.5816891 13 Dividend Payout Ratio - High 0.077276 1.03647164 2.6002567 14 Capital expenditures as % of sales - High 0.077276 1.03440041 2.5919679 15 WACC (stable period) - High 0.077276 1.03440041 2.5919679 16 ROIC (stable period) - High 0.077276 1.03440041 2.5919679 17 Growth Rate (stable period) - High 0.077276 1.03440041 2.5919679 Incremental Downside and Upside Sensitivity Equity Value per Share Parameter Low-Base High-Base Total Sales Growth Rate 0.011915 -0.0122157 0.0241308 Gross Profit Margin -0.128467 0.13776024 0.2662275 Dividend Payout Ratio -0.000529 0.0005291 0.0010581 Capital expenditures as % of sales 0.01076 -0.0107935 0.0215533 WACC (stable period) 0.152662 -0.1160233 0.2686856 ROIC (stable period) -0.039065 0.03196235 0.0710274 Growth Rate (stable period) -0.001896 0.00189581 0.0037916 Dividend Payout Ratio Growth Rate Capital Expediture as % of Sales Sales Growth Rate ROIC Gross Profit Margin WACC -0.15 -0.1 -0.05 Tornado Diagram: Impact on Share Low-Base Hi

WACC (stable period) 5.5% 8.3% 8.3% 2.2% 5.0% 7.5% 7.5% 2.0% 4.5% 6.8% 6.8% 1.8% 5.0% 7.5% 7.5% 2.0% 5.0% 7.5% 7.5% 2.0% 5.0% 7.5% 7.5% 2.0% 4.5% 7.5% 7.5% 2.0% 5.0% 6.8% 7.5% 2.0% 5.0% 7.5% 6.8% 2.0% 5.0% 7.5% 7.5% 1.8% 5.0% 7.5% 7.5% 2.0% 5.0% 7.5% 7.5% 2.0% 5.0% 7.5% 7.5% 2.0% 5.5% 7.5% 7.5% 2.0% 5.0% 8.3% 7.5% 2.0% 5.0% 7.5% 8.3% 2.0% 5.0% 7.5% 7.5% 2.2% 5.00% 7.50% 7.50% 2.00% Reinvestment Rate (K) Equity Value 0.2136479669 0.553611269916539 0.127197775 396834.6981 346724.41006 216.70276 0.2071859182 0.492413781048809 0.125145386 390217.7862 340107.49816 212.56719 0.2007950145 0.42868631623826 0.123042596 386165.4666 336055.17863 210.03449 0.2076745869 0.468257795149899 0.125805566 394270.2042 344159.91619 215.09995 0.2013142892 0.509725456218964 0.121548998 346525.1215 296414.83352 185.25927 0.2061182979 0.492413781048809 0.125145386 390037.8647 339927.5767 212.45474 0.2071859182 0.437377450902024 0.126013308 393877.292 343767.00398 214.85438 0.2071859182 0.492413781048809 0.125145386 442139.3638 392029.07578 245.01817 Capital expenditures as % of sales ROIC (stable period) Growth Rate (stable period) Return on Equity Return on Invested Capital Enterprise Value Equity Value per Share Run All Scenarios Set up Model for Given Case Reset Model with Base Case Parameters

0.2071859182 0.492413781048809 0.125145386 376931.4565 326821.16854 204.26323 0.2071859182 0.492413781048809 0.125145386 389573.0084 339462.7204 212.1642 0.206696825 0.516684845843356 0.124489003 386063.142 335952.85399 209.97053 0.2129969507 0.476058011301776 0.128744331 437071.0778 386960.78982 241.85049 0.2082646558 0.492413781048809 0.125145386 390397.738 340287.44997 212.67966 0.2071859182 0.547450111195594 0.124289338 386546.8377 336436.54975 210.27284 0.2071859182 0.492413781048809 0.125145386 350757.3872 300647.09918 187.90444 0.2071859182 0.492413781048809 0.125145386 401088.4195 350978.13149 219.36133 0.2071859182 0.492413781048809 0.125145386 390862.5639 340752.27592 212.97017 Small Parameter Low-Base High-Base Match Description 0.0010581162 1 -0.00052901 0.000529103 3 Dividend Pa 0.0037916116 2 -0.00189581 0.001895806 7 Growth Rate 0.0215533449 3 0.01075985 -0.010793495 4 Capital Exped 0.0241307888 4 0.011915109 -0.012215679 1 Sales Growt 0.0710274342 5 -0.03906509 0.031962345 6 ROIC 0.2662274628 6 -0.12846722 0.137760243 2 Gross Profit 0.2686855688 7 0.152662255 -0.116023314 5 WACC 0 0.05 0.1 0.15 0.2 e Price (Base Case: $212.57) igh-Base

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

ayout Ratio e diture as % of Sales th Rate t Margin

Inglis' Gold Mines Company Name Inglis' Gold Mines Ticker IGM Latest Fiscal year end date 6/30/2015 Income Statement Fiscal year 2015A 2016P Fiscal year end date Units 6/30/2015 6/30/2016 Sales $mm 127,889.0 136,841.2 Cost of Goods Sold $mm 102,311.2 105,380.5 Gross Profit $mm 25,577.8 31,460.7 Selling, General and Admin $mm 6,414.2 7,526.3 Operating Income Before Deprec. (EBITDA) $mm 19,163.6 23,934.4 Depreciation $mm 2,247.8 3,214.6 Operating Profit (EBIT) $mm 16,915.8 20,719.8 Interest Income $mm 834.2 393.5 Interest Expense $mm 4,000.5 3,489.2 Pretax Income (EBT) $mm 13,749.5 17,624.1 Income Tax $mm 4,537.3 7,049.7 Net Income $mm 9,212.2 10,574.5 Dividends $mm 1,842.4 2,643.6 Additions to Retained Earnings $mm 7,369.7 7,930.9 Number of shares Outstanding (million) mm 1,600.0 1,600.0 Tax Rate % 33.0% 40.0% Balance Sheet Fiscal year 2015A 2016P Fiscal year end date Units 6/30/2015 6/30/2016 Assets Required Operating Cash $mm 3,489.8 4,105.2 Excess Cash and Markatable Securities $mm 16,183.2 9,877.0 Accounts Receivable $mm 31,972.3 27,368.2 Inventory $mm 21,104.1 25,291.3 Other Current Assets $mm 1,230.1 1,368.4 Total Current Assets $mm 73,979.5 68,010.2 Gross Plant, Property & Equipment $mm 84,439.6 91,281.7 Accumulated Depreciation $mm 23,786.9 27,001.5 Net Plant, Property & Equipment $mm 60,652.7 64,280.2 Total Assets $mm 134,632.2 132,290.4 Liabilities and Equity Accounts Payable $mm 21,741.1 18,968.5

Revolver (Short Term Debt) $mm 0.0 0.0 Total Current Liabilities $mm 21,741.1 18,968.5 Long Term Debt $mm 69,783.3 62,283.3 Total Liabilities $mm 91,524.4 81,251.8 Common Stock / Paid in Capital $mm 31,260.0 31,260.0 Retained Earnings $mm 11,847.7 19,778.6 Total Common Equity $mm 43,107.7 51,038.6 Total Liabilities & Equity $mm 134,632.2 132,290.4 Balance Check 0.0 0.0 Cash Flow Statement Fiscal year 2015A 2016P Fiscal year end date Units 6/30/2015 6/30/2016 Cash Flows from Operating Activities Net Income $mm 9,212.2 10,574.5 Add Depreciation Expense $mm 2,247.8 3,214.6 Less Increase in Operating Cash $mm 39.9 615.4 Less Increase in Account Receivables $mm 832.3 (4,604.0) Less Increase in Inventories $mm (3,694.8) 4,187.2 Less Increase in Other Current Assets $mm 0.0 138.3 Add Increase in Accounts Payable $mm (1,925.2) (2,772.6) Total Cash Flows from Operations $mm 12,357.3 10,679.5 Cash Flows from Investing Activities Less Capital Expenditures $mm 4,300.0 6,842.1 Total Cash Flows from Investing $mm 4,300.0 6,842.1 Cash Flows from Financing Activities Add Issuance/(Repayment) of Revolver $mm 0.0 0.0 Add Issuance/(Repayment) of Long-Term Debt $mm 7,500.0 (7,500.0) Add Issuance/(Repayment) of Equity $mm 0.0 0.0 Less Cash Dividends Paid to Shareholders $mm 1,842.4 2,643.6 Total Cash Flows from Financing $mm 5,657.6 (10,143.6) Net Change in Excess Cash and Markatable Securities $mm 13,714.9 (6,306.2) Beginning Excess Cash and Marketable Securities $mm 2,468.3 16,183.2 Net Change in Excess Cash and Markatable Securities $mm 13,714.9 (6,306.2) Ending Excess Cash and Marketable Securities $mm 16,183.2 9,877.0 Fiscal year 2015A 2016P Fiscal year end date Units 6/30/2015 6/30/2016 Investment Module

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Required Investments Increase in Operating Cash $mm 39.9 615.4 Increase in Accounts Receivable $mm 832.3 (4,604.0) Increase in Inventory $mm (3,694.8) 4,187.2 Increase in Other Current Assets $mm 0.0 138.3 Increase in Total Current Assets $mm (2,822.6) 337.0 CapEx (i.e. Increase in Fixed Assets) $mm 4,300.0 6,842.1 Total Requied Investment in Assets $mm 1,477.5 7,179.0 Financing Module Spontaneous Financing Increase in Accounts Payable $mm (1,925.2) (2,772.6) Total Spontaneous Financing $mm (1,925.2) (2,772.6) Internal Financing Net Income $mm 9,212.2 10,574.5 Add Depreciation $mm 2,247.8 3,214.6 Less Minimum Dividends $mm 1,842.4 2,643.6 Total Internal Financing $mm 9,617.5 11,145.5 Total Spontaneous and Internal Financing 7,692.3 8,372.8 Required Financing (6,214.9) (1,193.8) Fiscal year 2015A 2016P Fiscal year end date Units 6/30/2015 6/30/2016 Increase in Long Term Debt (Exogenously determined) $mm (7,500.0) Increase in Equity (Exogenousy determined) $mm 0.0 Required Financing after accounting for LTD and Equity $mm 6,306.2 Model Plugs: Revolver and Excess Cash & Mar. Sec. Excess Cash & Mar. Sec. Excess Cash & Mar. Sec Beginning of Period $mm 16,183.2 Less Required Financing after LTD and Equity $mm 6,306.2 Excess Cash and Mar. Sec. Available to Paydown Revolver $mm 9,877.0 Revolver Beginning of period $mm 0.0 Less min(Cash Available, Revolver (BOP)) $mm 0.0 End of period $mm 0.0 0.0 Fiscal year 2015A 2016P Fiscal year end date Units 6/30/2015 6/30/2016 Ratios Net Profit Margin (NI/S) % 7.20% 7.73% Asset Turnover (S/A) % 0.95 1.03

Equity Multiplier (A/E) % 3.12 2.59 Return on Equity % 21.37% 20.72% Reinvestment Rate (K) % 9.84% 49.24% Return on Invested Capital % 12.51%

2017P 2018P 2019P 2020P Forecast 6/30/2017 7/1/2017 7/2/2017 7/3/2017 Factor Comment 146,420.1 156,669.5 167,636.4 179,370.9 7% % growth 107,723.5 109,072.6 109,092.2 107,361.6 38,696.7 47,596.9 58,544.2 72,009.3 23.0% % of Sales 8,053.1 8,616.8 9,220.0 9,865.4 5.5% % of Sales 30,643.5 38,980.1 49,324.2 62,143.9 3,406.8 3,614.3 3,837.9 4,078.7 5.3% of last year's Net Plant, Property and Equipm 27,236.7 35,365.8 45,486.3 58,065.2 279.6 227.9 244.6 347.9 2.0% Rate earned on last year's Required Cash an 3,114.2 2,739.2 2,364.2 1,989.2 5.0% Rate paid on last year's Revolver and LTD 24,402.2 32,854.5 43,366.7 56,423.9 9,760.9 13,141.8 17,346.7 22,569.6 40.0% % of EBT, but never negative 14,641.3 19,712.7 26,020.0 33,854.4 3,660.3 4,928.2 6,505.0 8,463.6 25.0% % of Net Income, but is never negative 10,981.0 14,784.5 19,515.0 25,390.8 1,600.0 1,600.0 1,600.0 1,600.0 1600 Assume no change 40.0% 40.0% 40.0% 40.0% 2017P 2018P 2019P 2020P Forecast 6/30/2017 7/1/2017 7/2/2017 7/3/2017 Factor 4,392.6 4,700.1 5,029.1 5,381.1 3.0% % of Sales 7,004.3 7,528.9 12,366.7 22,655.2 Plug 29,284.0 31,333.9 33,527.3 35,874.2 20.0% % of Sales 25,853.6 26,177.4 26,182.1 25,766.8 24.0% % of COGS 1,464.2 1,566.7 1,676.4 1,793.7 1.0% % of Sales 67,998.8 71,307.0 78,781.6 91,471.1 98,602.7 106,436.1 114,818.0 123,786.5 5.0% CapEx as % of Sales 30,408.3 34,022.6 37,860.6 41,939.3 68,194.3 72,413.5 76,957.4 81,847.2 136,193.1 143,720.5 155,739.0 173,318.3 19,390.2 19,633.1 19,636.6 19,325.1 18.0% % of COGS

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

0.0 0.0 0.0 0.0 Plug 19,390.2 19,633.1 19,636.6 19,325.1 54,783.3 47,283.3 39,783.3 32,283.3 (7,500.0) Decreases by $7500 each year according to 74,173.5 66,916.4 59,419.9 51,608.4 31,260.0 31,260.0 31,260.0 31,260.0 Remains Constant 30,759.6 45,544.1 65,059.1 90,449.9 62,019.6 76,804.1 96,319.1 121,709.9 136,193.1 143,720.5 155,739.0 173,318.3 0.0 0.0 0.0 0.0 2017P 2018P 2019P 2020P 6/30/2017 7/1/2017 7/2/2017 7/3/2017 14,641.3 19,712.7 26,020.0 33,854.4 3,406.8 3,614.3 3,837.9 4,078.7 287.4 307.5 329.0 352.0 1,915.8 2,049.9 2,193.4 2,346.9 562.3 323.8 4.7 (415.3) 95.8 102.5 109.7 117.3 421.7 242.9 3.5 (311.5) 15,608.6 20,786.2 27,224.7 35,220.6 7,321.0 7,833.5 8,381.8 8,968.5 7,321.0 7,833.5 8,381.8 8,968.5 0.0 0.0 0.0 0.0 (7,500.0) (7,500.0) (7,500.0) (7,500.0) 0.0 0.0 0.0 0.0 3,660.3 4,928.2 6,505.0 8,463.6 (11,160.3) (12,428.2) (14,005.0) (15,963.6) (2,872.7) 524.6 4,837.9 10,288.5 9,877.0 7,004.3 7,528.9 12,366.7 (2,872.7) 524.6 4,837.9 10,288.5 7,004.3 7,528.9 12,366.7 22,655.2 2017P 2018P 2019P 2020P 6/30/2017 7/1/2017 7/2/2017 7/3/2017

287.4 307.5 329.0 352.0 1,915.8 2,049.9 2,193.4 2,346.9 562.3 323.8 4.7 (415.3) 95.8 102.5 109.7 117.3 2,861.2 2,783.7 2,636.7 2,400.9 7,321.0 7,833.5 8,381.8 8,968.5 10,182.2 10,617.1 11,018.6 11,369.5 421.7 242.9 3.5 (311.5) 421.7 242.9 3.5 (311.5) 14,641.3 19,712.7 26,020.0 33,854.4 3,406.8 3,614.3 3,837.9 4,078.7 3,660.3 4,928.2 6,505.0 8,463.6 14,387.8 18,398.8 23,352.9 29,469.5 14,809.6 18,641.7 23,356.4 29,158.0 (4,627.3) (8,024.6) (12,337.9) (17,788.5) 2017P 2018P 2019P 2020P 6/30/2017 7/1/2017 7/2/2017 7/3/2017 (7,500.0) (7,500.0) (7,500.0) (7,500.0) 0.0 0.0 0.0 0.0 2,872.7 (524.6) (4,837.9) (10,288.5) 9,877.0 7,004.3 7,528.9 12,366.7 2,872.7 (524.6) (4,837.9) (10,288.5) 7,004.3 7,528.9 12,366.7 22,655.2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 2017P 2018P 2019P 2020P 6/30/2017 7/1/2017 7/2/2017 7/3/2017 10.00% 12.58% 15.52% 18.87% 1.08 1.09 1.08 1.03

2.20 1.87 1.62 1.42 23.61% 25.67% 27.01% 27.82% 37.12% 30.41% 25.09% 20.81% 15.50% 18.97% 22.99% 27.66%

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

ment nd Excess Cash and Markatable Securities

o debt contracts.

Inglis' Gold Mines Company Name Inglis' Gold Mines Ticker IGM Latest Fiscal year end date 6/30/2015 Valuation Date 9/30/2015 Circuit Breaker Assumptions 1) Changing Capital Structure across the 5 explicit forecast years 2) Book Value of Debt 3) Book Value of Equity Weighted Average Cost of Capital Fiscal year 2015A 2016P 2017P 2018P 2019P 2020P 2021P Fiscal year end date Units 6/30/2015 6/30/2016 6/30/2017 6/30/2018 6/30/2019 6/30/2020 6/30/2021 Cost of Debt Before Tax Required Return on Debt % 5.0% 5.0% 5.0% 5.0% 5.0% 5.0% Tax Rate % 33% 40% 40% 40% 40% 40% After Tax Rquired Return on Debt % 3.4% 3.0% 3.0% 3.0% 3.0% 3.0% Cost of Equity Risk Free Interst Rate % 2.0% 2.0% 2.0% 2.0% 2.0% 2.0% 10-year US T-Bond yield Market Risk Premium % 5.5% 5.5% 5.5% 5.5% 5.5% 5.5% Average of [ long term geometric return on S&P500, Implied Rate on S&P500] beta (unlevered) 1.20 1.20 1.20 1.20 1.15 1.10 Assumed beta of the unlevered firm (i.e. beta of assets) Net Debt $mm 50,110.3 48,301.1 43,386.4 35,054.4 22,387.5 4,246.9 Book Value Equity Value $mm 43,107.7 51,038.6 62,019.6 76,804.1 96,319.1 121,709.9 Book Value Debt / Equity Ratio % 1.16 0.95 0.70 0.46 0.23 0.03 Using book beta (levered) 2.13 1.88 1.70 1.53 1.31 1.12 b(L) = b(U)(1+D/E(1-T)) Cost of Equity (CAPM) % 13.7% 12.3% 11.4% 10.4% 9.2% 8.2% r = rf+b(L)(rm-rf) Weighted Average Cost of Capital Net Debt Weight (% of total) % 53.8% 48.6% 41.2% 31.3% 18.9% 3.4% Debt/(Debt+Equity) Equity Weight (% ot total) % 46.2% 51.4% 58.8% 68.7% 81.1% 96.6% Equity/(Debt+Equity) WACC % 8.15% 7.80% 7.92% 8.09% 8.04% 8.00% Debt Weight*After-Tax Cost of Debt+ Equity Weight*Required Return on Equity

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Fiscal year 2015A 2016P 2017P 2018P 2019P 2020P 2021P Fiscal year end date Units 6/30/2015 6/30/2016 6/30/2017 6/30/2018 6/30/2019 6/30/2020 6/30/2021 Free Cash Flow to Firm EBIT 16,915.8 20,719.8 27,236.7 35,365.8 45,486.3 58,065.2 Tax Rate 0.3 0.4 0.4 0.4 0.4 0.4 EBIT(1-T) 11,333.6 12,431.9 16,342.0 21,219.5 27,291.8 34,839.1 NOPLAT 11,333.6 12,431.9 16,342.0 21,219.5 27,291.8 34,839.1 % growth Add Depreciation Expense 2,247.8 3,214.6 3,406.8 3,614.3 3,837.9 4,078.7 Gross Cash Flow 13581.39 15646.49 19748.87 24833.76 31129.67 38917.85 Less Increase in Account Receivables 832.35 -4604.004 1915.77722 2049.88163 2193.37334 2346.90947 Less Increase in Inventories -3694.8 4187.22864 562.302355 323.802671 4.70001283 -415.346665 Less Increase in Other Current Assets 0 138.3123 95.788861 102.494081 109.668667 117.345474 Add Increase in Accounts Payable -1925.194 -2772.63352 421.726766 242.852003 3.52500962 -311.509999 Increase in Non-Cash Working Capital -937.256 2494.17046 2152.14167 2233.32637 2304.21701 2360.41828 Less CapEx 4300 6842.0615 7321.00581 7833.47621 8381.81955 8968.54691 Total Capital Investments 4300 6842.0615 7321.00581 7833.47621 8381.81955 8968.54691 Total Reinvestment in Firm 3362.744 9336.23196 9473.14747 10066.8026 10686.0366 11328.9652 Reinvestment Rate (K) 9.84% 49.24% 37.12% 30.41% 25.09% 20.81% Return on Invested Capital 12.51% 15.50% 18.97% 22.99% 27.66% Free Cash Flow to Firm 10218.64 6310.26 10275.72 14766.95 20443.63 27588.89 27588.89 % growth -38.25% 62.84% 43.71% 38.44% 34.95%

Financing Cash Flows Increase in Cash and Markatable Securities $ mm 13,754.8 (5,690.8) (2,585.3) 832.0 5,166.9 10,640.5 Less After Tax Interest Income (Int. Inc. (1-Tc)) $ mm 558.9 236.1 167.8 136.8 146.7 208.7 Add After Tax Interest Expense (Int. Exp. (1-Tc)) $ mm 2,680.3 2,093.5 1,868.5 1,643.5 1,418.5 1,193.5 Less Net New Debt $ mm 7,500.0 (7,500.0) (7,500.0) (7,500.0) (7,500.0) (7,500.0) Add Dividends $ mm 1,842.4 2,643.6 3,660.3 4,928.2 6,505.0 8,463.6 Less Net New Shares $ mm 0.0 0.0 0.0 0.0 0.0 0.0 Total Financing Cash Flows $ mm 10,218.6 6,310.3 10,275.7 14,767.0 20,443.6 27,588.9

Fiscal year Valuation Date 2016P 2017P 2018P 2019P 2020P 2021P Fiscal year end date Units 9/30/2015 6/30/2016 6/30/2017 6/30/2018 6/30/2019 6/30/2020 6/30/2021 Valuation Discount Period years 0.000 0.750 1.750 2.750 3.750 4.750 Length of each period 0.75 1.00 1.00 1.00 1.00 FCFF 6,310.3 10,275.7 14,767.0 20,443.6 27,588.9 26,059.7 Enterprise Value (End of Period) $mm 390,217.8 407,538.5 429,061.4 448,297.4 464,103.3 473,811.9 Assumptions Assumed Growth Rate of NOPLAT in Continuing Value Period % 2.00% Assumed ROIC % 7.50% WACC % 7.50% Implied Reinvestment Rate % 26.7% Normalized Cash Flows NOPLAT in last Forecast Period, t $mm 34,839.1 Normalized FCFF in last Forecast Period, t $mm 25,548.7 NOPLAT (t+1) $mm 35535.9 Normalized FCFF in first continuing value Period, t+1 $mm 26,059.7 Net Debt Revolver $mm 0.0 0.0 0.0 0.0 0.0 0.0 Long Term Debt $mm 69,783.3 62,283.3 54,783.3 47,283.3 39,783.3 32,283.3 Less Required Operating Cash $mm 3,489.8 4,105.2 4,392.6 4,700.1 5,029.1 5,381.1 Less Excess Cash and Marketable Securities $mm 16,183.2 9,877.0 7,004.3 7,528.9 12,366.7 22,655.2 Net Debt $mm 50,110.3 48,301.1 43,386.4 35,054.4 22,387.5 4,246.9 Equity Value Enterprise Value $mm 390,217.8 407,538.5 429,061.4 448,297.4 464,103.3 473,811.9 Less Net Debt $mm 50,110.3 48,301.1 43,386.4 35,054.4 22,387.5 4,246.9 Equity Value $mm 340,107.5 359,237.5 385,675.0 413,243.0 441,715.9 469,565.0 Shares Outstanding mm 1600 1600 1600 1600 1600 1600 Equity Value per Share $ 212.57 224.52 241.05 258.28 276.07 293.48

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version