Financial Statement Analysis

1. Similar to problem 8-6, caclulate the value of the firm using the assumptions and format below. Answers should be provided in Excel and use formula and cell references.
Cells with numbers rather than formula will not receive credit. Assume 2026 is the terminal year and all assumptions stay the same.
2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026
Sales growth rate 7.00% 6.00% 7.00% 6.50% 6.00% 5.00% 4.50% 4.00% 4.00% 3.00% 3.00%
NOPAT margin 8.00% 8.00% 8.00% 8.00% 8.00% 7.50% 7.50% 7.00% 7.00% 6.00% 6.00%
WC to sales 1.00% 1.50% 2.00% 2.00% 2.00% 1.50% 1.50% 1.00% 1.00% 1.00% 1.00%
LT assets to sales 20.00% 21.00% 22.00% 23.00% 25.00% 25.00% 24.00% 23.00% 22.00% 21.00% 21.00%
Debt ratio 60.00% 60.00% 60.00% 60.00% 60.00% 60.00% 60.00% 60.00% 60.00% 60.00% 60.00%
After tax cost of debt 3.00% 3.00% 3.00% 3.00% 3.00% 3.00% 3.00% 3.00% 3.00% 3.00% 3.00%
2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026
Beginning Balance Sheet
Beg. Net working capital
Beg. Net long-term assets
Net operating assets
Net debt
Preferred stock
Common stock
Net capital
Income Statement
Sales 30,000
Net operating profit after tax
Net interest expense after tax
Net Income
Preferred dividends
Net income to common
2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026
Valuation
Cost of Equity 8% 8% 8% 8% 8% 8% 8% 8% 8% 8% 8%
Abnormal Earnings
Discount Factor
Present Value of Abnormal Earnings
Beginning Book Value
PV Abnormal Earnings 2016-2025
PV Abnormal Earnings 2026 on
Total Value of Equity
2. Repeat question 1 on a separate tab, but replace your assumptions with the following. Sales for 2016 will remain $30,000.
2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026
Sales growth rate 7.00% 6.00% 7.00% 6.50% 6.00% 5.00% 4.50% 4.00% 4.00% 3.00% 3.00%
NOPAT margin 8.00% 8.00% 8.00% 8.00% 8.00% 7.50% 7.00% 6.00% 5.00% 4.00% 4.00%
WC to sales 1.00% 1.50% 2.00% 2.00% 2.00% 1.50% 1.50% 1.00% 1.00% 1.00% 1.00%
LT assets to sales 20.00% 21.00% 22.00% 23.00% 25.00% 25.00% 24.00% 23.00% 22.00% 21.00% 21.00%
Debt ratio 60.00% 60.00% 60.00% 60.00% 60.00% 60.00% 60.00% 60.00% 60.00% 60.00% 60.00%
After tax cost of debt 3.00% 3.00% 3.00% 3.00% 3.00% 3.00% 3.00% 3.00% 3.00% 3.00% 3.00%
3. Using the values from question 2, assume that the NOPAT margin changes so that there are no abnormal earnings in 2026 and beyond.
What is the new value of the firm?