Economics

profileStudy Hustler
Valuation2.xlsx
Home>Business & Finance homework help>Economics homework help>Economics

Content

Chapter 8 Prospective analysis: Valuation implementation
Table 1 Forecasting (Table 8.1- 8.3)
Table 2 Cost of Capital
Table 3 Terminal values for under various assumptions (Table 8.4)
Table 4 Valuation summary (Table 8.6)
Enter name of your company in Cell B8 TWE Wines
Entre most recent year of financial data (e.g. 2019) in Cell C9 2019

Table 1

Prospective analysis for TWE Wines
Forecast year 2019.0 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2029.0
Table 8.1 Forecasting assumptions Scenario 1
Sales growth rate 0.0% 3.0% 3.0% 3.0% 2.5% 2.5% 2.5% 2.0% 2.0% 2.0% 3.0% 3.0%
NOPAT margin 0.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0%
Beginning net operating working capital to sales 0.0% 9.0% 9.0% 9.0% 9.0% 9.0% 9.0% 9.0% 9.0% 9.0% 9.0% 9.0%
Beginning net operating long-term assets to sales 0.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0% 25.0%
Debt ratio 0.0% 25.5% 26.0% 26.5% 27.0% 27.5% 28.0% 28.5% 29.0% 29.5% 30.0% 30.0%
After tax cost of debt 0.0% 4.9% 4.9% 4.9% 4.9% 4.9% 4.9% 4.9% 4.9% 4.9% 4.9% 4.9%
Dividend rate % 0.0% 0.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0%
Table 8.2 Forecasted financial statement for Bega Cheese
Income statement ($'000)
Sales 0 0 0 0 0 0 0 0 0 0 0 0 0
- Net operating profit after tax 0 0 0 0 0 0 0 0 0 0 0 0
- Net interest expense after tax 0 0 0 0 0 0 0 0 0 0 0 0
= Net income 0 0 0 0 0 0 0 0 0 0 0 0
- Preferred dividends 0 0 0 0 0 0 0 0 0 0 0 0
= Net income to common 0 0 0 0 0 0 0 0 0 0 0 0
Beginning balance sheet ($'000)
Beginning net working capital 0 0 0 0 0 0 0 0 0 0 0 0 0 0
+ Beginning net long-term assets 0 0 0 0 0 0 0 0 0 0 0 0 0 0
= Net operating assets 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Net debt 0 0 0 0 0 0 0 0 0 0 0 0 0
+ Preference shares 0 0 0 0 0 0 0 0 0 0 0 0
Opening equity 0 0 0 0 0 0 0 0 0 0 0 0
Equity injections needed 0 0 0 0 0 0 0 0 0 0 0 0
= Net capital (beginning) 0 0 0 0 0 0 0 0 0 0 0 0 0
+ Profit 0 0 0 0 0 0 0 0 0 0 0 0
Dividend distributions 0 0 0 0 0 0 0 0 0 0 0 0
Equity issues 0 0 0 0 0 0 0 0 0 0 0 0 0
+ Closing equity 0 0 0 0 0 0 0 0 0 0 0 0 0
= Net capital (closing) 0 0 0 0 0 0 0 0 0 0 0 0 0
Ratios
Operating return on assets (%) ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Return on equity (%) ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Book value of assets growth (%) ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Book value of equity growth (%) ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Net operating assets turnover ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Cash flows ($'000)
Net income 0 0 0 0 0 0 0 0 0 0 0 0
- Change in net working capital 0 0 0 0 0 0 0 0 0 0 0 0
- Change in net long-term assets 0 0 0 0 0 0 0 0 0 0 0 0
+ Change in net debt 0 0 0 0 0 0 0 0 0 0 0 0
= Free cash flow to equity 0 0 0 0 0 0 0 0 0 0 0 0
Net operating profit after tax 0 0 0 0 0 0 0 0 0 0 0 0
- Change in net working capital 0 0 0 0 0 0 0 0 0 0 0 0
- Change in net long-term assets 0 0 0 0 0 0 0 0 0 0 0 0
= Free cash flow to capital 0 0 0 0 0 0 0 0 0 0 0 0
Table 8.3 Valuation
Forecast year 1 2 3 4 5 6 7 8 9 10 11
2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 Terminal year
Equity valuation
Cost of equity 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Abnormal earnings 0 0 0 0 0 0 0 0 0 0 0
Abnormal ROE (%) ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Free cash flows to equity 0 0 0 0 0 0 0 0 0 0 0
Asset Valuation
WACC 0.0% 1.2% 1.3% 1.3% 1.3% 1.3% 1.4% 1.4% 1.4% 1.4% 1.5%
Abnormal NOPAT - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0
Abnormal operating ROA ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Free cash flow to capital - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0
Discount factors
Equity discount factor 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000
Assets 1.0000 0.9755 0.9627 0.9497 0.9364 0.9228 0.9090 0.8950 0.8807 0.8663 0.8517
Growth factors
Equity ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Assets ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
PV of abnormal earnings 0 0 0 0 0 0 0 0 0 0 0
PV Abn. ROE ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Scaled Abnormal ROE ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
PV Scaled Abnormal ROE ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Pv Free cash flows to equity 0 0 0 0 0 0 0 0 0 0 0
PV Abn. NOPAT - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0
PV Abn. ROA ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
PV free cash flows to capital 0 0 0 0 0 0 0 0 0 0 0
PV of forecast period (abn earnings) 0
PV of forecast period (abn ROE) ERROR:#DIV/0!
PV of forecast period (free cash flows to equity) 0
PV of forecast period (abn NOPAT) 0
PV of forecast period (abn ROA) ERROR:#DIV/0!
PV of forecast period (free cash flows to capital) 0
Terminal values (abn earnings) 0
Terminal values (abn ROE) ERROR:#DIV/0!
Terminal values (free cash flows to equity) 0
Terminal values (abn NOPAT) 0
Terminal values (abn ROA) ERROR:#DIV/0!
Terminal values (free cash flows to capital) 0

Table 2

Cost of capital
Cosf of debt
After-tax cost of debt 0.00%
Cost of equity Use this summary table to the left to calculate your initial Cost of equity for the first forecast year
Treasury bond rate (risk free rate) 0%
Beta 0.00
Risk premium 0%
Cost of equity 0.00%
Summary of equity costs each year
2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2029.0
Capital structure
% net debt 0.0% 25.5% 26.0% 26.5% 27.0% 27.5% 28.0% 28.5% 29.0% 29.5% 30.0%
% equity 100.0% 74.5% 74.0% 73.5% 73.0% 72.5% 72.0% 71.5% 71.0% 70.5% 70.0%
Cost of capital
Debt % 0.00% 4.90% 4.90% 4.90% 4.90% 4.90% 4.90% 4.90% 4.90% 4.90% 4.90%
Equity % 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
WACC % 0.00% 1.25% 1.27% 1.30% 1.32% 1.35% 1.37% 1.40% 1.42% 1.45% 1.47%

Table 3

Terminal values for under various assumptions (using abnormal earnings methodology)
Scenario number Approach Scenario Terminal sales growth Terminal NOPAT margin Value beyond forecast horizon (terminal value) $000
1 Persistent abnormal performance Sales growth and margins based on detailed analysis and forecast 0.0% 0.0% 0
2 Abnormal returns on constant sales (real terms) Sales growth at the rate of inflation, margins maintained 0.0% 0.0% 0
3 Abnormal returns on constant sales (nominal terms) Essentially zero sales growth, margins maintained 0.0% 0.0% 0
4 Competitive equilibrium Margins reduced so no abnormal earnings 0.0% 0.0000% 0
Use this table to summarise some of the results you achieve under various approaches
NOTE: This table is a 'summary table'. It does not have any formula references. You will need to manually enter variables and output data each time you re-run Table 1 as required.

Table 4

Valuation summary using various methodologies under varying scenarios
Beginning book value Value of forecast period Value beyond forecast horizon Total value equity Value per share
Scenario 1 - Persistent abnormal performance
Abnormal earnings 0 0 0 0 ERROR:#DIV/0! Enter the results of your calculations of various scenarios in this summary page
Abnormal ROE 0 0 0 0 ERROR:#DIV/0!
Free cash flows to equity n/a 0 0 0 ERROR:#DIV/0! Alternatively save multiple workbooks for each scenario investigated
Scenario 2 - Abnormal returns on constant sales (real terms)
Abnormal earnings 0 0 0 0 ERROR:#DIV/0!
Abnormal ROE 0 0 0 0 ERROR:#DIV/0!
Free cash flows to equity 0 0 0 0 ERROR:#DIV/0!
Scenario 3 - Abnormal returns on constant sales (nominal terms)
Abnormal earnings ERROR:#DIV/0!
Abnormal ROE ERROR:#DIV/0!
Free cash flows to equity ERROR:#DIV/0!
Scenario 4 - Persistent abnormal performance
Abnormal earnings ERROR:#DIV/0!
Abnormal ROE ERROR:#DIV/0!
Free cash flows to equity ERROR:#DIV/0!
Weighted average number of shares (Annual report Note) 0
NOTE: This table is a 'summary table'. It does not have any formula references. You will need to manually enter variables and output data each time you re-run Table 1 as required.