Discussion Brief #2: Forecasting Exercises

The following assignment asks you to work through some of the elements of our forecasting problem so that we can discuss how to build a set of connected forecasts next week.

It is meant to be used with the spreadsheet that accompanies this assignment, but may also be worked by hand using the data in Appendix 1&2.

Questions:

1. One of the most important aspects of financial modeling is understanding how the financial statements connect together.

This exercise asks you to use information from both the income statement & balance sheet to build a cash flow forecast.

1. Use the forecasts provided for 2025 to estimate this companys operating cash flow using the formula: Operating Cash Flow = EBIT * (1-t) + Depreciation & Amortization.

Show your work below.

2. Use the forecasts provided for 2025 to estimate this companys investment spending, by calculating both Net Working Capital Investment & Capital Investment.

Show your work below.

3. Use your answers to questions (1a) & (1b) to estimate Free Cash Flow to the Firm in 2020 and comment on whether this firm appears to need any outside financing.

Show your work below.

4. Now estimate Cash Paid to Creditors: what are this firms after-tax interest expenses, and how has Net Debt Issued (or Retire affected its cash flow?

Show your work below.

5. Use your answers to questions (1c) & (1d) to estimate Free Cash Flow Available to Shareholders in 2020.

Show your work below.

2. Connecting the model:

Notice that you now have a decision to make! There are three things that this firm can do with Free Cash Flow Available to Shareholders: it can pay it out as a dividend; use it to buy back shares; or save it for later.

1. First, notice what happens in this model if their firm chooses NOT to pay out a dividend and also chooses NOT to buy back any stock.
   1. How will this assumption affect our forecast for the firms cash balance, and how will this affect our balance sheet projections for 2025?
   2. How will this assumption affect our forecast for shareholders equity, and can you explain WHY it has or has not changed from year to year?
2. Now lets look at what happens if this company pays out 100% of FCF Available to Shareholders as a dividend.
   1. How will this assumption affect our forecast for the firms cash balance, and how will this affect our balance sheet projections for 2025?
   2. How will this assumption affect our forecast for shareholders equity, and can you explain WHY it has or has not changed from year to year?
3. Now lets look at what happens if this company uses 100% of FCF Available to Shareholders to buy back stock.
   1. How will this assumption affect our forecast for the firms cash balance, and how will this affect our balance sheet projections for 2025?
   2. How will this assumption affect our forecast for shareholders equity, and can you explain WHY it has or has not changed from year to year?
   3. How will this assumption affect our forecast for the firms share count, and how will it affect our forecasts for both Earnings Per Share & the Dividend Per Share?

Note: you may assume that this firm can buy back its shares at a market price of $50 per share.

3. Take a minute to look at what this model predicts:
   1. Have we predicted that this firms ROIC will change or stay the same?

Use the DuPont Formulas to explain what weve predicted about this firms asset efficiency, profit margins, and tax rate.

2. Does this firm have the ability to increase its dividend if it wants to?
3. What is this firms Times Interest Earned Ratio (Operating Profit/Interest Expense), and are we predicting that its credit quality will change or stay the same?
4. What do YOU think this firm ought to do with its free cash flow, and why?

Note: We will be using the concepts in this exercise to build forecasts during class this week. Feel free to note any questions that you have about how we connect our projections in different financial statements here.

CASH FLOW [Non-GAAP] 2019 2020 Step 1: Estimate Operating Cash Flow EBIT * (1-tax rate) Plus Depreciation & Amortization Operating Cash Flow 100.0 60.0 160.0 Step 2: Estimate Investment Spending Net Working Capital Investment Capital Investment (CAPX) Total Investment Spending (10.0) (90.0) (100.0) Step 3: Estimate FCF to the Firm Step 4: Estimate Cash paid to Creditors Net Debt Issued (or Retired) Less After-Tax Interest Expenses Cash From Creditors (25.0) (25.0) Step 5: Estimate FCF Available to Shareholders 35.0 Step 6: Estimate Cash paid to Shareholders Less Dividends on Common Stock Cash From Shareholders (30.0) (30.0) Step 7: Change in Cash Balance Actual INCOME STATEMENT ($m) Forecast 2020 2019 525.0 131.3 Revenues Operating Profit Interest Expense Pretax Profit 500.0 125.0 25.0 100.0 25.0 106.3 Income Taxes Net Income average tax rate 20.0 80.0 20% 21.3 85.0 20% Average shares outstanding (m) 20.0 20.0 Earnings Per Share Dividend Per Share 4.00 1.50 Key Non-GAAP Measures EBIT EBITDA 125 185 BALANCE SHEET($m) 2019 2020 Key Non-GAAP Measures Cash & Cash Equivalents Net Working Capital (Excluding Cash) Total Net Working Capital 50.0 150.0 200.0 50.0 165.0 215.0 Gross PP&E Accumulated Depreciation Net Property, Plant & Equipment 1,200.0 (400.0) 800.0 1,300.0 (465.0) 835.0 TOTAL: Net Assets 1,000.0 1,050.0 500.0 Interest Bearing Debt Common Shareholder's Equity Invested Capital (est) 475.0 525.0 1,000.0 Actual INCOME STATEMENT ($m) Forecast 2020 2019 525.0 131.3 Revenues Operating Profit Interest Expense Pretax Profit 500.0 125.0 25.0 100.0 25.0 106.3 Income Taxes Net Income average tax rate 20.0 80.0 20% 21.3 85.0 20% Average shares outstanding (m) 20.0 20.0 Earnings Per Share Dividend Per Share 4.00 1.50 Key Non-GAAP Measures EBIT EBITDA 125 185 BALANCE SHEET($m) 2019 2020 Key Non-GAAP Measures Cash & Cash Equivalents Net Working Capital (Excluding Cash) Total Net Working Capital 50.0 150.0 200.0 50.0 165.0 215.0 Gross PP&E Accumulated Depreciation Net Property, Plant & Equipment 1,200.0 (400.0) 800.0 1,300.0 (465.0) 835.0 TOTAL: Net Assets 1,000.0 1,050.0 500.0 Interest Bearing Debt Common Shareholder's Equity Invested Capital (est) 475.0 525.0 1,000.0 CASH FLOW [Non-GAAP] 2019 2020 Step 1: Estimate Operating Cash Flow EBIT * (1-tax rate) Plus Depreciation & Amortization Operating Cash Flow 100.0 60.0 160.0 Step 2: Estimate Investment Spending Net Working Capital Investment Capital Investment (CAPX) Total Investment Spending (10.0) (90.0) (100.0) Step 3: Estimate FCF to the Firm Step 4: Estimate Cash paid to Creditors Net Debt Issued (or Retired) Less After-Tax Interest Expenses Cash From Creditors (25.0) (25.0) Step 5: Estimate FCF Available to Shareholders 35.0 Step 6: Estimate Cash paid to Shareholders Less Dividends on Common Stock Cash From Shareholders (30.0) (30.0) Step 7: Change in Cash Balance Actual INCOME STATEMENT ($m) Forecast 2020 2019 525.0 131.3 Revenues Operating Profit Interest Expense Pretax Profit 500.0 125.0 25.0 100.0 25.0 106.3 Income Taxes Net Income average tax rate 20.0 80.0 20% 21.3 85.0 20% Average shares outstanding (m) 20.0 20.0 Earnings Per Share Dividend Per Share 4.00 1.50 Key Non-GAAP Measures EBIT EBITDA 125 185 BALANCE SHEET($m) 2019 2020 Key Non-GAAP Measures Cash & Cash Equivalents Net Working Capital (Excluding Cash) Total Net Working Capital 50.0 150.0 200.0 50.0 165.0 215.0 Gross PP&E Accumulated Depreciation Net Property, Plant & Equipment 1,200.0 (400.0) 800.0 1,300.0 (465.0) 835.0 TOTAL: Net Assets 1,000.0 1,050.0 500.0 Interest Bearing Debt Common Shareholder's Equity Invested Capital (est) 475.0 525.0 1,000.0 Actual INCOME STATEMENT ($m) Forecast 2020 2019 525.0 131.3 Revenues Operating Profit Interest Expense Pretax Profit 500.0 125.0 25.0 100.0 25.0 106.3 Income Taxes Net Income average tax rate 20.0 80.0 20% 21.3 85.0 20% Average shares outstanding (m) 20.0 20.0 Earnings Per Share Dividend Per Share 4.00 1.50 Key Non-GAAP Measures EBIT EBITDA 125 185 BALANCE SHEET($m) 2019 2020 Key Non-GAAP Measures Cash & Cash Equivalents Net Working Capital (Excluding Cash) Total Net Working Capital 50.0 150.0 200.0 50.0 165.0 215.0 Gross PP&E Accumulated Depreciation Net Property, Plant & Equipment 1,200.0 (400.0) 800.0 1,300.0 (465.0) 835.0 TOTAL: Net Assets 1,000.0 1,050.0 500.0 Interest Bearing Debt Common Shareholder's Equity Invested Capital (est) 475.0 525.0 1,000.0