DCF Model with Bear and Bull Scenarios in Excel

A DCF model built for public equity valuations, with a bull and bear scenario analysis to evaluate upside/downside risk

by Colin Ferrian
Co-Founder & CEO at deepgreen

8 reviews1,050 views|Start the discussion!|Bookmark

Description
What: A DCF model built for public equity valuations, with a bull and bear scenario analysis to evaluate upside/downside risk

Why: The model was originally built for a publicly traded technology-licensing company. It includes the three financial statements and DCF model. The DCF Discounted Cash Flow model in Excel also has scenario analysis to evaluation upside and downside risk on an investment.

Notes:
- The DCF page includes both a terminal value calculation, as well as a line-itemed cost of equity analysis, with the goal being that all variables can be altered as easily as possible
- There is intensive detail among each of the statements, so be careful to only alter the variables or else you'll risk #REF'ing the model.
- All inputs will bring the user a set of key outputs, including:
1. Premium(discount) to current share price
2. Years to earn market cap (EBITDA)
3. Premium and discount to bear and bull scenario stock prices
4. Operating margin and margin leverage given various scenarios

This model has 6 tabs:

- Discounted Cash Flow Valuation
- Balance Sheet
- Income Statement
- Statement of Cash Flows
- Bear Case
- Bull Case

Please let me know on any feedback for improvement, and I'm happy to help with questions on the model!

- Colin

This business tool includes
1 Excel Model