\/ Insert Name of Firm \/
Financial Projection Model
YEAR OF FIRST INPUT DATA SALES (LAST YEAR) SALES GROWTH (YEARS +1 TO +5) COGS/SALES (% OF SALES) SALES EXPENSE (% OF SALES) ADMINIST. EXPENSE (% OF SALES) DEPRECIATION EXPENSE (% FIXED/ASSETS) INTEREST INCOME LAST YEAR ($) INTEREST EXPENSE LAST YEAR ($) INTEREST RATE - DEBT (%) INTEREST RATE - INVESTMENTS (%) TAX RATE (% OF B/T INCOME) LAST PREFERRED STOCK DIVIDEND ($) FUTURE PREFERRED STOCK DIVIDENDS ($) LAST COMMON STOCK DIVIDEND ($) FUTURE COMMON DIVIDENDS ($) BEGINNING CASH BALANCE MINIMUM CASH BALANCE (% SALES) BEGIN MARKETABLE SECURITIES ACCOUNTS RECEIVABLE (% SALES) INVENTORIES (% SALES) OTHER CURR. ASSETS (% SALES) BEGIN FIXED ASSETS DOLLAR AMOUNT CHANGE IN FIXED ASSETS YEAR +1 CHANGE IN FIXED ASSETS YEAR +2 CHANGE IN FIXED ASSETS YEAR +3 CHANGE IN FIXED ASSETS YEAR +4 ACCUMULATED DEPRECIATION ACCOUNTS PAYABLE (% SALES) TAXES PAYABLE & OTHER ACCRUALS BEGINNING SHORT TERM DEBT & NOTES SHORT-TERM LOANS - DO NOT ENTER --> OTHER CURR. LIABS. (% SALES) LONG-TERM DEBT & LIABILITIES NEW LONG TERM DEBT YEAR +1 NEW LONG TERM DEBT YEAR +2 NEW LONG TERM DEBT YEAR +3 NEW LONG TERM DEBT YEAR +4 PREFERRED STOCK STARTING COMMON STOCK STARTING RETAINED EARNINGS STOCK ISSUE YEAR +1 STOCK ISSUE YEAR +2 STOCK ISSUE YEAR +3 STOCK ISSUE YEAR +4 EXTRAORDINARY GAIN / (LOSS) YEAR 0 EXTRAORDINARY GAIN / (LOSS) YEAR +1 EXTRAORDINARY GAIN / (LOSS) YEAR +2 EXTRAORDINARY GAIN / (LOSS) YEAR +3 EXTRAORDINARY GAIN / (LOSS) YEAR +4 INFORMATION FOR VALUATION OF FIRM BETA OF FIRM'S STOCK (EXISTING) (BL) ASSUMED RATE OF GROWTH RISK FREE RATE (T - BOND)
INPUT DATA INPUT DATA INPUT FORMAT FUTURE YEARS LAST YEAR 1983 $6,469 88.00% 7.00% 0.00% 5.00% $50 $140 6.00% 87.00% 7.00% 0.00% 5.00% YEAR (LAST COMPLETED YEAR) FIXED DOLLAR AMOUNT (LAST YEAR) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT (LAST YEAR) PERCENTAGE - ANNUAL RATE (DECIMAL) PERCENTAGE - ANNUAL RATE (DECIMAL) PERCENTAGE (DECIMAL) FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT (LAST YEAR) PERCENTAGE (DECIMAL) FIXED DOLLAR AMOUNT (LAST YEAR) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) FIXED DOLLAR AMOUNT (LAST YEAR) DOLLAR AMOUNT DOLLAR AMOUNT DOLLAR AMOUNT DOLLAR AMOUNT FIXED DOLLAR AMOUNT (LAST YEAR) PERCENTAGE (DECIMAL) PERCENTAGE (DECIMAL) FIXED DOLLAR AMOUNT (LAST YEAR) COMPUTED BY SPREADSHEET PERCENTAGE (DECIMAL) FIXED DOLLAR AMOUNT (CONSTANT) FIXED DOLLAR AMOUNT (CONSTANT) FIXED DOLLAR AMOUNT (CONSTANT) FIXED DOLLAR AMOUNT (CONSTANT) FIXED DOLLAR AMOUNT (CONSTANT) FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT (LAST YEAR) FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT FIXED DOLLAR AMOUNT NUMBER PERCENTAGE - KD MUST > GROWTH
36.00% $0 $86
8.50% 6.00% 36.00% $0 $90
$100 2.00% $300 10.00% 13.00% 0.00% $3,463 10.00% 13.00% 0.00% $338 $31 $42 $10 $0 10.00% 6.00% $0 PLUG 7.40% $1,523 10.00% 6.00% 0 PLUG 8.70% $0 $0 $0 $0 $0 $2,242 $0 $0 $0 $0 $0 ($118) $0 $0 $0 $0 1.11 5.00% 8.49%
NOTE: ALL DATA BELOW ARE COMPUTER GENERATED
INCOME STATEMENT SALES INTEREST ON MARK. SECS. & CASH COGS GROSS PROFIT SALES/DISTRIBUTION EXPENSE ADMINISTRATIVE EXPENSE DEPRECIATION EXPENSE EXTRAORDINARY GAIN (LOSS) EBIT INTEREST EXPENSE (S/T & L/T DEBT) PROFIT BEFORE TAX TAX EXPENSE PROFIT AFTER TAX PREFERRED STOCK DIVIDENDS COMMON STOCK DIVIDENDS EARNINGS RETAINED BALANCE SHEET CASH MARKETABLE SECURITIES EXCESS CASH ACCOUNTS RECEIVABLE INVENTORIES OTHER CURRENT ASSETS FIXED ASSETS ACCUMULATED DEPRECIATION TOTAL ASSETS ACCOUNTS PAYABLE TAXES PAYABLE & OTHER ACCRUALS SHORT TERM LOANS & NOTES (With Interest) NEW SHORT-TERM LOANS REQUIRED OTHER CURRENT LIABILITIES LONG-TERM DEBT/LIABILITIES PREFERRED STOCK COMMON STOCK RETAINED EARNINGS TOTAL LIABILTIES AND EQUITY ESTIMATE DEBT/EXCESS CASH TRIAL ASSETS TRIAL LIABILITIES AND EQUITY PLUG: DEBT (EXCESS CASH) PERFORMANCE MEASURES ROE ROA RETURN ON SALES TIMES INTEREST EARNED TOTAL DEBT TO ASSETS LONG TERM DEBT TO ASSETS SHORT TERM DEBT TO ASSETS DEBT TO EQUITY EARNINGS PER SHARE CURRENT RATIO QUICK RATIO ACCOUNTS RECEIVABLE TURNOVER AVERAGE COLLECTION PERIOD INVENTORY TURNOVER FIXED ASSET TURNOVER CASH FLOW RETURN ON ASSETS
1983 LAST YEAR $6,469 $50 $5,693 $826 $453 $0 $173 ($118) $318 $140 $178 $64 $114 $0 $86 $28
1984 YEAR +1 $6,857 $26 $5,966 $918 $480 $0 $190 $0 $248 $146 $102 $37 $65 $0 $90 ($25)
1985 YEAR +2 $7,269 $27 $6,324 $972 $509 $0 $192 $0 $271 $132 $139 $50 $89 $0 $90 ($1)
1986 YEAR +3 $7,705 $36 $6,703 $1,037 $539 $0 $193 $0 $305 $129 $175 $63 $112 $0 $90 $22
1987 YEAR +4 $8,167 $50 $7,105 $1,112 $572 $0 $196 $0 $344 $129 $215 $77 $137 $0 $90 $47
1988 YEAR +5 $8,657 $67 $7,532 $1,192 $606 $0 $194 $0 $392 $129 $263 $95 $168 $0 $90 $78
$100 $300 $0 $647 $841 $0 $3,463 $0 $5,351 $647 $388 $0 $72 $479 $1,523 $0 $2,242 $0 $5,351
$137 $300 $0 $686 $891 $0 $3,801 ($190) $5,625 $686 $411 $0 $191 $597 $1,523 $0 $2,242 ($25) $5,625
$145 $300 $0 $727 $945 $0 $3,832 ($382) $5,567 $727 $436 $0 $33 $632 $1,523 $0 $2,242 ($26) $5,567
$154 $300 $139 $770 $1,002 $0 $3,874 ($575) $5,664 $770 $462 $0 $0 $670 $1,523 $0 $2,242 ($4) $5,664
$163 $300 $371 $817 $1,062 $0 $3,884 ($771) $5,826 $817 $490 $0 $0 $711 $1,523 $0 $2,242 $44 $5,826
$173 $300 $642 $866 $1,125 $0 $3,884 ($965) $6,025 $866 $519 $0 $0 $753 $1,523 $0 $2,242 $122 $6,025
$5,351 $5,279 $72
$5,625 $5,434 $191
$5,567 $5,534 $33
$5,525 $5,664 ($139)
$5,455 $5,826 ($371)
$5,383 $6,025 ($642)
5.09% 2.13% 1.76% 2.27 29.81% 28.46% 1.35% 0.71 $0.51 1.19 0.66 10.00 36.50 6.77 1.87 ----
2.94% 1.16% 0.95% 1.70 30.48% 27.07% 3.40% 0.77 $0.29 1.07 0.60 10.00 36.50 6.69 1.90 1.29%
4.01% 1.60% 1.22% 2.05 27.95% 27.36% 0.59% 0.70 $0.40 1.16 0.64 10.00 36.50 6.69 2.11 7.24%
5.01% 1.98% 1.46% 2.35 26.89% 26.89% 0.00% 0.68 $0.50 1.24 0.72 10.00 36.50 6.69 2.34 7.51%
6.01% 2.36% 1.68% 2.66 26.14% 26.14% 0.00% 0.67 $0.61 1.34 0.82 10.00 36.50 6.69 2.62 8.54%
7.11% 2.79% 1.94% 3.03 25.28% 25.28% 0.00% 0.64 $0.75 1.45 0.93 10.00 36.50 6.69 2.97 9.16%
FREE CASH FLOW STATEMENT
(INDIRECT METHOD) Statement is Computer Generated from Financial Projections 1983 This is the basis for the Earnings Before indirect method's Interest & Taxes calculation of the FCF (EBIT) Depreciation is a noncash expense and therefore is added back to calculate the cash vlow $318 1984 $248 1985 $271 1986 $305 1987 $344 1988 $392
PLUS Depreciation
N/A
$190
$192
$193
$196
$194
Deducted since this is cash that cannot freely MINUS Net be used to pay Increase in dividends, buy back Property, Plant shares, or repay loans, & Equipment at and therefore is Cost deducted from CFO to arrive at FCF The increase in A/R represents sales that MINUS Increase have not as yet been in Accounts collected and therefore Receivable do not produce a cash inflow The increase in inventory has not been recognized as part of MINUS Increase COGS but was fully in Inventories paid for and therefore is deducted from the cash flow The increase in A/P represents costs that PLUS Increase in have not as yet been Accounts paid and therefore is Payable added back to the cash flow Like the increase in A/P these are taxes PLUS Increase in that have not as yet Taxes Payable been paid CASH FROM OPERATIONS Tax as % EBIT EQUALS = Cash from Operations LESS: Tax as % EBIT
N/A
$338
$31
$42
$10
$0
N/A
$39
$41
$44
$46
$49
N/A
$50
$53
$57
$60
$64
N/A
$39
$41
$44
$46
$49
N/A
$23
$25
$26
$28
$29
N/A N/A
$72 $89
$403 $98
$425 $110
$498 $124
$552 $141
This is the cash that the firm can use to EQUALS = Free distribute to any and Cash Flow or all of its suppliers of Unlevered Free capital such as Cash Flow shareholders and debt holders
N/A
($17)
$305
$316
$374
$411
VALUATION OF THE FIRM USING DISCOUNTED FREE CASH FLOW
WACC METHOD
Analysis is Computer Generated UNLEVER BETA & CALCULATE WACC = Existing Beta of Firm's Stock LEVERED BETA 1.11 = Existing Debt / Equity Ratio EXISTING D/E RATIO 0.71 UNLEVERED BETA ASSUMED MARKET RISK PREMIUM (Enter Value) COST OF UNLEVERED EQUITY CALCULATION OF WACC WACC AT EXISTING D/E D/E = .111 D/E = .428 D/E = 1.00 D/E = 2.33 D/E = 9.00 12.67% 13.70% 13.40% 13.53% 14.71% 17.81% 0.65 8.40% = BL / ( 1+ D/E ) Note: D/E = DEBT to EQUITY
= Assumed Historical Market Risk Premium (Note: RM-RF)
13.94% = RSU = RF + (BUL*Market Risk Premium) WACC = [(% Debt) * (KD) * (1 - T)] + [(% Equity) * (RF +(BL * (MRP))] % DEBT % EQUITY 41.6% 58.4% 10.00% 90.00% 30.00% 70.00% 50.00% 70.00% 90.00% 50.00% 30.00% 10.00% BETA 1.11 0.72 0.93 1.30 2.16 6.49 KD 8.50% Note: Cost of Debt (before 9.49% tax) increases as D/E ratio 10.49% increases, from RF + 1% to 11.99% RF + 5%. 14.99% 19.99%
CASH FLOW TO CAPITAL
YEAR +1 ($17)
YEAR +2 $305
TERMINAL VALUE - WITH GROWTH TERMINAL VALUE - NO GROWTH PV FACTOR AT EXISTING WACC NPV PV FLOWS TO CAPITAL AT ASSUMED GROWTH RATE AFTER LAST YEAR PV FLOWS TO CAPITAL AT NO GROWTH AFTER LAST YEAR $4,157 0.8875
DCF YR. 1
Assumed YEAR +3 YEAR +4 YEAR +5 YEAR 6 With $316 $374 $411 Growth $431 (D1 / k - g) No $5,906 Growth $5,357 $411
(D1 / k - g)
0.7877
DCF. YR. 2
0.6992
DCF YR. 3
0.6205
DCF YR. 4
0.5508
DCF YR. 5 + DCF TV
($15)
$241
$221
$232
$3,479
DCF YR. 5 + DCF TV
NPV $3,855
DCF YR. 1
DCF. YR. 2
DCF YR. 3
DCF YR. 4
($15)
$241
$221
$232
$3,176
TOTAL VALUE OF FIRM AT ASSUMED GROWTH RATE LESS DEBT TOTAL VALUE OF FIRM WITH NO GROWTH LESS DEBT
$2,083
DCF Value of Firm Minus Existing Debt
$1,781
DCF Value of Firm Minus Existing Debt
VALUE OF FIRM & VALUE OF EQUITY AT VARIOUS D/E RATIOS D / E RATIOS 0.111 0.428 1.000 2.330 WACC 13.70% 13.40% 13.53% 14.71% TOTAL VALUE OF FIRM AT ASSUMED GROWTH $3,985 $4,033 $4,012 $3,823 RATE AFTER TERMINAL YEAR VALUE OF FIRM AT ASSUMED RATE OF $1,911 $1,959 $1,938 $1,749 GROWTH LESS EXISTING DEBT
9.000 17.81% $3,376
$1,302
INSTRUCTIONS AND INFORMATION ABOUT FINANCIAL PROJEC TION MODEL
NOTE: MUST SET NUMBER OF ITERATIONS ON EXCEL. (TOOLS: OPTIONS: SET NUMBER (50); .99 CHANGE LEVEL ) NOTE: ONLY CHANGE 1 VARIABLE AT A TIME; ENTER THE DATA, HIT RETURN/ENTER, AND ALLOW COMPUTER TO PROCESS THE CHANGE. AFTER THE CHANGE HAS BEEN PROCESSED, ANOTHER VARIABLE MAY BE CHANGED. This Sheet: Allows for New Stock Issues Allows for New Long-Term Debt Shows L/T & S/T Debt Ratios Allows for Changes in Fixed Assets The Marketable Securities and Short-Term Loan Accounts are "Plug" Accounts; they are used to balance the Balance Sheet. Interest Income is credited for Cash, Marketable Securities, and Excess Cash at the Interest Rate for Investments Interest Expense is Charged on Long-Term Debt and Short-Term Loans at the Cost of Debt Interest Rate
Page 5