Bonus Assignment_DCF Valuation_Piravin.P

xlsm

School

Toronto Metropolitan University *

*We aren’t endorsed by this school

Course

514

Subject

Finance

Date

Jan 9, 2024

Type

xlsm

Pages

Uploaded by LieutenantCaribou430

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 % 16.4% % 47.8% % 138.8% % 10.9% % 27.8% % 11.2% $mm 0.0 $mm 830.5 $mm (830.5) mm 1,600.0 $ (0.5)

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.0% 23.0% 25.0% Output Scenario 1 Base Case + 10% 16.4% 47.8% 138.8% 2 Base Case 14.2% 48.3% 135.7% 3 Base Case - 10% 12.1% 48.6% 134.1% 4 Sales Volumn Growth (Flat) - Low 14.2% 48.1% 135.0% 5 Sales Volumn Growth (Rolled) - Low 14.2% 48.2% 135.6% 6 Gross Profit Margin - Low 12.1% 48.1% 140.1% 7 Dividend Payout Ratio - Low 14.2% 48.2% 132.6% 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

8 Days Sales Outstanding - Low 14.2% 48.7% 134.7% 9 Inventory Turns - Low 14.2% 47.9% 136.9% 10 Days Payables Outstanding - Low 14.2% 48.3% 135.7% 11 Capital expenditures as % of sales - Low 14.2% 49.1% 133.5% 12 WACC (stable period) - Low 14.2% 48.3% 135.7% 13 ROIC (stable period) - Low 14.2% 48.3% 135.7% 14 Growth Rate (stable period) - Low 14.2% 48.3% 135.7% 15 Sales Volumn Growth (Flat) - High 14.2% 48.5% 136.5% 16 Sales Volumn Growth (Rolled) - High 14.2% 48.3% 135.8% 17 Gross Profit Margin - High 16.4% 48.3% 132.2% Incremental Downside and Upside Sensitivity Equity Value per Share Parameter Low-Base High-Base Total Sales Growth Rate -100.00% -10.17% -110.17% Gross Profit Margin -100.00% -11.98% -111.98% Dividend Payout Ratio -100.00% -11.62% 111.62% Capital expenditures as % of sales #VALUE! -9.66% #VALUE! WACC (stable period) -2.82% 0.00% 2.82% ROIC (stable period) -11.05% -14.18% 25.23% Growth Rate (stable period) -20.11% -11.05% 31.16% #VALUE! Err:502 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 0.00% 0.00% 0.00% 0.00% 0.0 Chart Ti Low-Base H

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.0% 7.5% 7.5% 2.0% Reinvestment Rate (K) Equity Value 10.9% 27.8% 11.2% 1,626.8 1,716.5 171.6 9.3% 27.8% 9.7% 1,481.4 1,571.1 157.1 7.9% 27.8% 8.3% 1,321.5 1,411.2 141.1 9.2% 27.8% 9.7% 1,469.8 1,559.5 156.0 9.3% 27.8% 9.7% 1,480.1 1,569.8 157.0 8.1% 27.8% 8.3% 1,209.7 1,299.4 129.9 9.1% 27.8% 9.7% 1,480.0 1,569.7 157.0 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

9.3% 27.8% 9.9% 1,496.9 1,586.6 158.7 9.3% 27.8% 9.6% 1,465.0 1,554.7 155.5 9.3% 27.8% 9.7% 1,471.5 1,561.2 156.1 9.3% 27.8% 9.9% 1,506.0 1,595.7 159.6 9.3% 27.8% 9.7% 1,676.6 1,766.3 176.6 9.3% 30.9% 9.7% 1,426.2 1,515.9 151.6 9.3% 25.0% 9.7% 1,481.4 1,571.1 157.1 9.4% 27.8% 9.8% 1,493.2 1,582.9 158.3 9.3% 27.8% 9.8% 1,482.8 1,572.5 157.2 10.5% 27.8% 11.2% 1,752.5 1,842.2 184.2 Small Parameter Low-Base High-Base Err:502 #VALUE! 0.00% 0.00% Err:502 16 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 15 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 14 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 13 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 12 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 00% 0.00% 0.00% 0.00% 0.00% itle High-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

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 138,120.1 Cost of Goods Sold $mm 102,311.2 106,352.5 Gross Profit $mm 25,577.8 31,767.6 Selling, General and Admin $mm 6,414.2 7,596.6 Operating Income Before Deprec. (EBITDA) $mm 19,163.6 24,171.0 Depreciation $mm 2,247.8 3,214.6 Operating Profit (EBIT) $mm 16,915.8 20,956.4 Interest Income $mm 834.2 393.5 Interest Expense $mm 4,000.5 3,489.2 Pretax Income (EBT) $mm 13,749.5 17,860.7 Income Tax $mm 4,537.3 7,144.3 Net Income $mm 9,212.2 10,716.4 Dividends $mm 1,842.4 2,679.1 Additions to Retained Earnings $mm 7,369.7 8,037.3 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,143.6 Excess Cash and Markatable Securities $mm 16,183.2 9,554.3 Accounts Receivable $mm 31,972.3 27,624.0 Inventory $mm 21,104.1 25,524.6 Other Current Assets $mm 1,230.1 1,381.2 Total Current Assets $mm 73,979.5 68,227.7 Gross Plant, Property & Equipment $mm 84,439.6 91,345.6 Accumulated Depreciation $mm 23,786.9 Net Plant, Property & Equipment $mm 60,652.7 91,345.6 Total Assets $mm 134,632.2 159,573.3

Liabilities and Equity Accounts Payable $mm 21,741.1 19,143.4 Revolver (Short Term Debt) $mm 0.0 0.0 Total Current Liabilities $mm 21,741.1 19,143.4 Long Term Debt $mm 69,783.3 62,283.3 Total Liabilities $mm 91,524.4 81,426.7 Common Stock / Paid in Capital $mm 31,260.0 31,260.0 Retained Earnings $mm 11,847.7 19,885.1 Total Common Equity $mm 43,107.7 51,145.1 Total Liabilities & Equity $mm 134,632.2 132,571.8 Balance Check 0.0 27,001.5 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,716.4 Add Depreciation Expense $mm 2,247.8 3,214.6 Less Increase in Operating Cash $mm 39.9 653.8 Less Increase in Account Receivables $mm 832.3 (4,348.2) Less Increase in Inventories $mm (3,694.8) 4,420.5 Less Increase in Other Current Assets $mm 0.0 151.1 Add Increase in Accounts Payable $mm (1,925.2) (2,597.7) Total Cash Flows from Operations $mm 12,357.3 10,456.2 Cash Flows from Investing Activities Less Capital Expenditures $mm 4,300.0 6,906.0 Total Cash Flows from Investing $mm 4,300.0 6,906.0 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,679.1 Total Cash Flows from Financing $mm 5,657.6 (10,179.1) Net Change in Excess Cash and Markatable Securities $mm 13,714.9 (6,628.9) 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,628.9) Ending Excess Cash and Marketable Securities $mm 16,183.2 9,554.3

Fiscal year 2015A 2016P Fiscal year end date Units 6/30/2015 6/30/2016 Investment Module Required Investments Increase in Operating Cash $mm 39.9 653.8 Increase in Accounts Receivable $mm 832.3 (4,348.2) Increase in Inventory $mm (3,694.8) 4,420.5 Increase in Other Current Assets $mm 0.0 151.1 Increase in Total Current Assets $mm (2,822.6) 877.2 CapEx (i.e. Increase in Fixed Assets) $mm 4,300.0 6,906.0 Total Requied Investment in Assets $mm 1,477.5 7,783.2 Financing Module Spontaneous Financing Increase in Accounts Payable $mm (1,925.2) (2,597.7) Total Spontaneous Financing $mm (1,925.2) (2,597.7) Internal Financing Net Income $mm 9,212.2 10,716.4 Add Depreciation $mm 2,247.8 3,214.6 Less Minimum Dividends $mm 1,842.4 2,679.1 Total Internal Financing $mm 9,617.5 11,251.9 Total Spontaneous and Internal Financing 7,692.3 8,654.2 Required Financing (6,214.9) (871.1) 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,628.9 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,628.9 Excess Cash and Mar. Sec. Available to Paydown Revolver $mm 9,554.3 Revolver Beginning of period $mm 7.0 Less min(Cash Available, Revolver (BOP)) $mm (99.5) End of period $mm 0.0 106.5

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 Fiscal year end date Units 6/30/2015 6/30/2016 Ratios Net Profit Margin (NI/S) % 7.20% 7.76% Asset Turnover (S/A) % 0.95 0.87 Equity Multiplier (A/E) % 3.12 3.12 Return on Equity % 21.37% 20.95% Reinvestment Rate (K) % Return on Invested Capital %

2017P 2018P 2019P 2020P Forecast 6/30/2017 7/1/2017 7/2/2017 7/3/2017 Factor Comment 149,169.7 161,103.3 173,991.6 187,910.9 8% % growth 114,860.7 124,049.5 133,973.5 144,691.4 34,309.0 37,053.8 40,018.1 43,219.5 23.0% % of Sales 8,204.3 8,860.7 9,569.5 10,335.1 5.5% % of Sales 26,104.7 28,193.1 30,448.5 32,884.4 4,841.3 5,236.6 5,663.5 6,124.6 5.3% of last year's Net Plant, Property and Equipm 21,263.4 22,956.5 24,785.0 26,759.8 274.0 180.8 96.8 22.7 2.0% Rate earned on last year's Required Cash an 3,114.2 2,744.5 2,368.1 1,991.6 5.0% Rate paid on last year's Revolver and LTD 18,423.2 20,392.8 22,513.7 24,790.9 7,369.3 8,157.1 9,005.5 9,916.3 40.0% % of EBT, but never negative 11,053.9 12,235.7 13,508.2 14,874.5 2,763.5 3,058.9 3,377.1 3,718.6 25.0% % of Net Income, but is never negative 8,290.4 9,176.7 10,131.2 11,155.9 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,475.1 4,833.1 5,219.7 5,637.3 3.0% % of Sales 4,565.1 7.9 (4,085.5) (7,684.3) Plug 29,833.9 32,220.7 34,798.3 37,582.2 20.0% % of Sales 27,566.6 29,771.9 32,153.6 34,725.9 24.0% % of COGS 1,491.7 1,611.0 1,739.9 1,879.1 1.0% % of Sales 67,932.4 68,444.6 69,826.1 72,140.3 98,804.1 106,859.3 115,558.8 124,954.4 5.0% CapEx as % of Sales 98,804.1 106,859.3 115,558.8 124,954.4 166,736.5 175,303.9 185,384.9 197,094.6

20,674.9 22,328.9 24,115.2 26,044.5 18.0% % of COGS 106.5 78.1 49.0 19.2 Plug 20,781.4 22,407.0 24,164.2 26,063.7 54,783.3 47,283.3 39,783.3 32,283.3 (7,500.0) Decreases by $7500 each year according to d 75,564.7 69,690.3 63,947.5 58,347.0 31,260.0 31,260.0 31,260.0 31,260.0 Remains Constant 28,175.5 37,352.2 47,483.4 58,639.3 59,435.5 68,612.2 78,743.4 89,899.3 135,000.2 138,302.6 142,690.9 148,246.3 31,736.3 37,001.3 42,694.0 48,848.4 2017P 2018P 2019P 2020P 6/30/2017 7/1/2017 7/2/2017 7/3/2017 11,053.9 12,235.7 13,508.2 14,874.5 4,841.3 5,236.6 5,663.5 6,124.6 331.5 358.0 386.6 417.6 2,209.9 2,386.7 2,577.7 2,783.9 2,042.0 2,205.3 2,381.8 2,572.3 110.5 119.3 128.9 139.2 1,531.5 1,654.0 1,786.3 1,929.2 12,732.8 14,056.9 15,483.2 17,015.4 7,458.5 8,055.2 8,699.6 9,395.5 7,458.5 8,055.2 8,699.6 9,395.5 106.5 (28.4) (29.1) (29.8) (7,500.0) (7,500.0) (7,500.0) (7,500.0) 0.0 0.0 0.0 0.0 2,763.5 3,058.9 3,377.1 3,718.6 (10,157.0) (10,587.3) (10,906.2) (11,248.4) (4,882.6) (4,585.6) (4,122.6) (3,628.6) 9,554.3 4,671.6 86.0 (4,036.5) (4,882.6) (4,585.6) (4,122.6) (3,628.6) 4,671.6 86.0 (4,036.5) (7,665.1)

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

2017P 2018P 2019P 2020P 6/30/2017 7/1/2017 7/2/2017 7/3/2017 331.5 358.0 386.6 417.6 2,209.9 2,386.7 2,577.7 2,783.9 2,042.0 2,205.3 2,381.8 2,572.3 110.5 119.3 128.9 139.2 4,693.9 5,069.4 5,474.9 5,912.9 7,458.5 8,055.2 8,699.6 9,395.5 12,152.4 13,124.5 14,174.5 15,308.5 1,531.5 1,654.0 1,786.3 1,929.2 1,531.5 1,654.0 1,786.3 1,929.2 11,053.9 12,235.7 13,508.2 14,874.5 4,841.3 5,236.6 5,663.5 6,124.6 2,763.5 3,058.9 3,377.1 3,718.6 13,131.7 14,413.4 15,794.7 17,280.5 14,663.2 16,067.4 17,581.0 19,209.7 (2,510.9) (2,942.8) (3,406.5) (3,901.2) 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 4,989.1 4,557.2 4,093.5 3,598.8 9,554.3 4,565.1 7.9 (4,085.5) 4,989.1 4,557.2 4,093.5 3,598.8 4,565.1 7.9 (4,085.5) (7,684.3) 106.5 78.1 49.0 19.2 28.4 29.1 29.8 19.2 78.1 49.0 19.2 0.0

2017P 2018P 2019P 2020P 6/30/2017 7/1/2017 7/2/2017 7/3/2017 7.41% 7.59% 7.76% 7.92% 0.89 0.92 0.94 0.95 2.81 2.55 2.35 2.19 18.60% 17.83% 17.15% 16.55%

ment nd Excess Cash and Markatable Securities

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

debt contracts.

Inglis' Gold Mines Company Name Inglis' Gold Mines Ticker IGM Latest Fiscal year end date 6/30/2015 Valuation Date 8/30/2015 Circuit Breaker ON 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 830.5 788.0 776.4 764.5 752.4 751.4 Book Value Equity Value $mm 1330.9 1384.1 1454.3 1526.3 1600.0 1675.6 Book Value Debt / Equity Ratio % 62.4% 56.9% 53.4% 50.1% 47.0% 44.8% Using book beta (levered) 0.09 0.08 0.08 0.08 0.08 0.08 b(L) = b(U)(1+D/E(1-T)) Cost of Equity (CAPM) % 0.2% 0.2% 0.2% 0.2% 0.2% 0.2% r = rf+b(L)(rm-rf) Weighted Average Cost of Capital Net Debt Weight (% of total) % 38.4% 36.3% 34.8% 33.4% 32.0% 31.0% Debt/(Debt+Equity) Equity Weight (% ot total) % 61.6% 63.7% 65.2% 66.6% 68.0% 69.0% Equity/(Debt+Equity) WACC % 12.8% 14.6% 14.0% 13.5% 12.9% 12.5% Debt Weight*After-Tax Cost of Debt+ Equity Weight*Required Return on Equity

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 $mm 16915.8 20956.4279 21263.386 22956.462 24784.985 26759.789 0 Tax Rate % 33.0% 40.0% 40.0% 40.0% 40.0% 40.0% 0.0% EBIT(1-T) $mm 11,333.6 12,573.9 12,758.0 13,773.9 14,871.0 16,055.9 Add Deferred Tax $mm (72.3) 1.8 8.6 8.8 9.1 9.3 NOPLAT $mm 11,261.3 12,575.7 12,766.6 13,782.7 14,880.0 16,065.2 % growth % 11.7% 1.5% 8.0% 8.0% 8.0% Add Depreciation Expense $mm 2,247.8 3,214.6 4,841.3 5,236.6 5,663.5 6,124.6 Gross Cash Flow $mm 13,509.1 15,790.3 17,607.9 19,019.3 20,543.6 22,189.8 Less Increase in Account Receivables $mm (4,348.2) 3.0 3.1 3.2 3.3 Less Increase in Inventories $mm 30.6 4,420.5 4.1 4.3 4.4 4.5 Less Increase in Other Current Assets $mm 5.4 151.1 3.9 4.0 4.2 4.3 Add Increase in Accounts Payable $mm (5.8) (2,597.7) 4.8 4.9 5.1 5.2 Add Increase in Accured Exp. & Other Current Liab $mm 103.6 (2,597.7) 2.7 2.7 2.8 2.9 Increase in Non-Cash Working Capital $mm (61.8) 5,418.7 3.7 3.8 3.9 4.0 Less CapEx $mm 4,300.0 6,906.0 7,458.5 8,055.2 8,699.6 9,395.5 Less Investment in Other Long Term Assets $mm 161.1 82.0 84.0 86.0 88.2 90.4 Total Capital Investments $mm 4,461.1 6,988.0 7,542.5 8,141.2 8,787.8 9,485.9 Total Reinvestment in Firm $mm 4,399.3 12,406.7 7,546.1 8,145.0 8,791.6 9,489.9 Reinvestment Rate (K) % 19.1% 73.1% 21.2% 21.1% 21.0% 0.0% Return on Invested Capital % 581.8% 587.7% 617.9% 649.6% 682.9% Free Cash Flow to Firm $mm 9,109.8 3,383.6 10,061.8 10,874.3 11,752.0 12,699.9

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

% growth % -62.9% 197.4% 8.1% 8.1% 8.1% Financing Cash Flows Increase in Cash and Markatable Securities $ mm 13,754.8 (5,975.1) (4,551.1) (4,227.6) (3,735.9) (3,211.0) Less After Tax Interest Income (Int. Inc. (1-Tc)) $ mm 558.9 236.1 164.4 108.5 58.1 13.6 Add After Tax Interest Expense (Int. Exp. (1-Tc)$ mm 2,680.3 2,093.5 1,868.5 1,646.7 1,420.8 1,195.0 Less Net New Debt $ mm 7,500.0 (7,500.0) (7,393.5) (7,528.4) (7,529.1) (7,529.8) Add Dividends $ mm 1,842.4 2,679.1 2,763.5 3,058.9 3,377.1 3,718.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,061.4 7,310.0 7,897.9 8,533.0 9,218.8

Fiscal year Valuation Date 2016P 2017P 2018P 2019P 2020P 2020P Fiscal year end date Units 8/30/2015 6/30/2016 6/30/2017 6/30/2018 6/30/2019 6/30/2020 6/30/2021 Valuation Discount Period years 115.67 116.50 117.50 118.50 119.50 120.50 Length of each period Length of each p 0.83 1.00 1.00 1.00 1.00 FCFF 3,383.6 10,061.8 10,874.3 11,752.0 16,065.2 Enterprise Value (End of Period) $mm 0.0 0.0 0.0 0.0 0.0 0.0 Assumptions Assumed Growth Rate of NOPLAT in Continuin% 2.00% Assumed ROIC % 7.50% WACC % 7.50% Implied Reinvestment Rate % 26.7% Normalized Cash Flows NOPLAT in last Forecast Period, t $mm 16,065.2 Normalized FCFF in last Forecast Period, t $mm 16,065.2 NOPLAT (t+1) $mm 17270.1 Normalized FCFF in first continuing value Perio$mm 17,270.1 Net Debt Revolver $mm 346.5 348.3 356.9 365.7 374.8 384.1 Long Term Debt $mm 632.6 562.62678815 545.46237 527.89178 509.91181 502.90844 Less Required Operating Cash $mm 0.0 0.0 0.0 0.0 0.0 0.0 Less Excess Cash and Marketable Securities $mm 148.6 122.9 126.0 129.1 132.3 135.6 Net Debt $mm 830.525 788.0 776.4 764.5 752.4 751.4 Equity Value Enterprise Value $mm 0.0 0.0 0.0 0.0 0.0 0.0 Less Net Debt $mm 830.525 788.0 776.4 764.5 752.4 751.4 Equity Value $mm (830.5) (788.0) (776.4) (764.5) (752.4) (751.4) Shares Outstanding mm 1600 1600 1600 1600 1600 1600 Equity Value per Share $ $ (0.52) $ (0.49) $ (0.49) $ (0.48) $ (0.47) $ (0.47)

Bonus Assignment_DCF Valuation_Piravin.P

Related Documents