TCOM 546

1
TCOM 546

• Session 8

2
Corrected Output of Model
Year Sales Capex Cost 1 Cost2 Cash Flow Cum CF DCF Cum DCF
1 0 -1000 -100 0 -1100 -1100 -1100 -1100
2 100 -2000 -300 0 -2200 -3300 -2000 -3100
3 1000 -1000 -400 -200 -600 -3900 -495.868 -3595.87
4 3000 -500 -500 -600 1400 -2500 1051.841 -2544.03
5 5000 -500 -1000 3500 1000 2390.547 -153.48
6 4000 -500 -800 2700 3700 1676.488 1523.008
7 3200 -500 -640 2060 5760 1162.816 2685.824
8 2560 -500 -512 1548 7308 794.3688 3480.193
9 2048 -500 -409.6 1138.4 8446.4 531.072 4011.265
10 1638.4 -500 -327.68 810.72 9257.12 343.8244 4355.089
3
Recap

• Last week, we started to look at financial models
• Pro Forma balance sheet and income statements
• Time value of money
• Cost models

4
Using Pro Forma Models for Financial Projections

• Project future financing needs
• How does debt vary with sales?
• Change model parameters
• Target debt/equity ratio
• Free cash flow
• Sensitivity analyses
• Interest rates, dividend payout ratio, etc.

5
Recall Initial Example

• Current Year 0 sales 1000
• Grow at 10 per year
• Ratio of assets to sales 15
• Ratio of current liabilities to sales 8
• Ratio of net fixed assets to sales 77
• Expenses (excl. interest and depreciation) 65
  of sales
• Depreciation 10 year straight line

6
Recall Initial Example (Continued)

• New fixed assets purchased at beginning of year
• Depreciation based on full book value
• New debt borrowed at beginning of year
• Based on balance sheet debt
• Interest rate is 10
• Debt Total assets Current liabilities Stock
  Retained earnings

7
Initial Parameters
Initial sales 1,000
Sales growth 10
Current assets/Sales 15
Current liabilities/Sales 8
Net fixed assets/Sales 77
Costs of goods sold/Sales 65
Depreciation rate 10
Interest 10.00
Tax rate 40
Dividend payout ratio 70
8
0 1 2 3 4 5
Income Statement
Sales 1,000 1,100 1,210 1,331 1,464 1,611
Costs of goods sold (715) (787) (865) (952) (1,047)
Interest payments (28) (33) (39) (45) (53)
Depreciation (127) (151) (178) (209) (245)
Profit before tax 229 239 249 258 266
Taxes (92) (96) (100) (103) (106)
Profit after tax 138 144 149 155 159
Dividends (96) (100) (105) (108) (112)
Retained earnings 41 43 45 46 48
9
Balance Sheet
Current assets 150 165 182 200 220 242
Fixed assets
At cost 1,070 1,274 1,510 1,781 2,093 2,451
Depreciation (300) (427) (578) (757) (966) (1,211)
Net fixed assets 770 847 932 1,025 1,127 1,240
Total Assets 920 1,012 1,113 1,225 1,347 1,482
Current liabilities 80 88 97 106 117 129
Debt 240 283 332 389 454 530
Stock 450 450 450 450 450 450
Accumulated retained earnings 150 191 234 279 326 373
Total Liabilities and equity 920 1,012 1,113 1,225 1,347 1,482
10
Financial Analysis

• Examine how debt changes with sales
• Vary sales growth rate over the range 4 per year
  to 20 per year
• Tabulate results

11
How Does Debt Vary With Sales?
12
Change the Model Parameters

• Change
• Ratio of net fixed assets to sales from 77 to
  70
• Cost of goods sold from 65 of sales to 50
• Future sales growth 2 per year not 10

13
Revised Parameters
Initial sales 1,000
Sales growth 2
Current assets/Sales 15
Current liabilities/Sales 8
Net fixed assets/Sales 70
Costs of goods sold/Sales 50
Depreciation rate 10
Interest 10.00
Tax rate 40
Dividend payout ratio 70
14
0 1 2 3 4 5
Income Statement
Sales 1,000 1,020 1,040 1,061 1,082 1,104
Costs of goods sold (510) (520) (531) (541) (552)
Interest payments (12) (6) (1) 4 10
Depreciation (113) (127) (142) (160) (179)
Profit before tax 386 387 387 386 382
Taxes (154) (155) (155) (154) (153)
Profit after tax 231 232 232 231 229
Dividends (162) (163) (163) (162) (161)
Retained earnings 69 70 70 69 69
15
Balance Sheet

Current assets 150 153 156 159 162 166
Fixed assets
At cost 1,000 1,127 1,268 1,425 1,600 1,794
Depreciation (300) (413) (539) (682) (842) (1,021)
Net fixed assets 700 714 728 743 758 773
Total Assets 850 867 884 902 920 938

Current liabilities 80 82 83 85 87 88
Debt 170 116 62 8 (45) (97)
Stock 450 450 450 450 450 450
Accumulated retained earnings 150 219 289 359 428 497
Total Liabilities and equity 850 867 884 902 920 938
16
Differences

• Firm produces more cash, profits
• Debt becomes negative
• That is, cash assets
• These are assumed to produce interest payments at
  the same rate that the firm pays on its debt
• Not realistic
• Next, assume we translate negative debt into a
  cash and marketable securities account
• Interest rate different from debt

17
Revised Equations

• Debt
• IF (Current assets Net fixed assets Current
  liabilities Stock Accumulated retained
  earnings gt 0)
• THEN (Debt Current assets Net fixed assets
  Current liabilities Stock Accumulated
  retained earnings)
• ELSE (Debt 0)
• Cash and marketable assets Total liabilities
  (Current assets Net fixed assets Current
  liabilities Stock Accumulated retained
  earnings)

18
Another Change

• Target debt/equity ratio
• Change equations
• Debt Target debt/equity ratio(Stock Retained
  earnings)
• Stock Total assets Current liabilities Debt
  Accumulated retained earnings

19
Note Changes in Parameters from Baseline
Initial sales 1,000
Sales growth 7
Current assets/Sales 25
Current liabilities/Sales 8
Net fixed assets/Sales 75
Costs of goods sold/Sales 85
Depreciation rate 10
Interest 10.00
Tax rate 40
Dividend payout ratio 70
20
0 1 2 3 4 5
Income Statement
Sales 1,000 1,070 1,145 1,225 1,311 1,403
Costs of goods sold (910) (973) (1,041) (1,114) (1,192)
Interest payments (33) (34) (36) (37) (38)
Depreciation (123) (142) (165) (190) (219)
Profit before tax 5 (5) (17) (31) (47)
Taxes (2) 2 7 12 19
Profit after tax 3 (3) (10) (18) (28)
Dividends (2) 2 7 13 20
Retained earnings 1 (1) (3) (5) (8)
21
Balance Sheet

Current assets 250 268 286 306 328 351
Fixed assets
At cost 1,050 1,225 1,424 1,648 1,903 2,191
Depreciation (300) (423) (565) (730) (920) (1,139)
Net fixed assets 750 803 859 919 983 1,052
Total Assets 1,000 1,070 1,145 1,225 1,311 1,403

Current liabilities 80 86 92 98 105 112
Debt 320 328 342 355 368 382
Stock 450 505 562 625 696 776
Accumulated retained earnings 150 151 150 147 142 133
Total Liabilities and equity 1,000 1,070 1,145 1,225 1,311 1,403

Target Debt/Equity ratio 50 48 46 44 42
22
Debt Repayment Schedules

• The Financial Statement model can also be used to
  analyze e.g.,
• Impact of debt repayment
• In this case, the plug used to balance the
  accounts is the cash and marketable securities
  account
• Cash flow

23
Cash Flow

• Cash flow Profit after taxes
• Depreciation
• Net after-tax interest payments
• - Increase in current assets
• Increase in current liabilities
• - Increase in fixed assets at cost

24
Homework

• Read Benninga, Chapter 2
• Note In second edition, this is Chapter 4
• Set up Benningas initial financial model as an
  Excel spreadsheet
• Change parameters as follows
• Sales growth 5
• Interest 7
• Dividend payout ratio 80