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?

order

Why Us

  • Free bibliography page
  • Free outline
  • 915+ certified ENL and ESL writers to choose from
  • Original, fully referenced and formatted writing
  • On-time delivery, or you get a refund
  • Writer is fully qualified in your area of study
  • Writer has your degree level or higher
  • Communicate with your essay writer, a true academic expert, directly
  • Unlimited revision requests within 14 days
  • Plagiarism report to make sure the work is 100% plagiarism free
  • 24/7 instant support by phone, live chat, email, and messaging