Scenario & Sensitivity Analysis Model
Table of Contents
DCF & Sensitivity Model
Additional Assumptions
Notes
This Excel model is for educational purposes only and should not be used for any other reason.
All content is Copyright material of CFI Education Inc.
[Link]
© 2019 CFI Education Inc.
All rights reserved. The contents of this publication, including but not limited to all written material, content layout, images, formulas, and code, are protected under international copyright and
Strictly Confidential
otected under international copyright and trademark laws.
© Corporate Finance Institute. All rights reserved. Historical Results Forecast Period
FINANCIAL STATEMENTS 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022
Balance Sheet Check OK OK OK OK OK OK OK OK OK OK
Live Case 1 Share Price $34.85
Assumptions
Income Statement
Balance Sheet
Cash Flow Statement
Supporting Schedules
DCF Model
Sensitivity Analysis
0% 200% 400% 600% 800% 1000% 1200%
2018 2019
Base Case
Revenue Growth (% Change) 10.0% 10.0%
Cost of Goods Sold (% of Revenue) 42.0% 47.0%
Salaries and Benefits (% of Revenue) 17.0% 17.0%
Rent and Overhead ($000's) 15,000 15,000
Depreciation & Amortization (% of PP&E) 35.0% 35.0%
Interest (% of Debt) 10.0% 10.0%
Tax Rate (% of Earnings Before Tax) 28.0% 28.0%
Accounts Receivable (Days) 18 18
Inventory (Days) 80 90
Accounts Payable (Days) 37 37
Capital Expenditures ($000's) 15,000 15,000
Debt Issuance (Repayment) ($000's) - -
Equity Issued (Repaid) ($000's) - -
Downside Case
Revenue Growth (% Change) 5.0% 4.5%
Cost of Goods Sold (% of Revenue) 37.0% 37.0%
Salaries and Benefits (% of Revenue) 18.0% 18.0%
Rent and Overhead ($000's) 10,000 10,000
Depreciation & Amortization (% of PP&E) 40.0% 40.0%
Interest (% of Debt) 5.0% 5.0%
Tax Rate (% of Earnings Before Tax) 28.0% 28.0%
Accounts Receivable (Days) 18 18
Inventory (Days) 73 73
Accounts Payable (Days) 37 37
Capital Expenditures ($000's) 20,000 20,000
Debt Issuance (Repayment) ($000's) - -
Equity Issued (Repaid) ($000's) - -
Upside Case
Revenue Growth (% Change) 12.0% 12.0%
Cost of Goods Sold (% of Revenue) 37.0% 36.0%
Salaries and Benefits (% of Revenue) 16.0% 16.0%
Rent and Overhead ($000's) 10,000 10,000
Depreciation & Amortization (% of PP&E) 40.0% 40.0%
Interest (% of Debt) 5.0% 5.0%
Tax Rate (% of Earnings Before Tax) 28.0% 28.0%
Accounts Receivable (Days) 18 18
Inventory (Days) 73 73
Accounts Payable (Days) 37 37
Capital Expenditures ($000's) 15,000 15,000
Debt Issuance (Repayment) ($000's) - -
Equity Issued (Repaid) ($000's) - -
2020 2021 2022
Cases
10.0% 10.0% 10.0% 1
50.0% 36.0% 35.0% 2
17.0% 17.0% 17.0% 3
15,000 15,000 15,000
35.0% 35.0% 35.0%
10.0% 10.0% 10.0%
28.0% 28.0% 28.0%
18 18 18
100 100 100
37 37 37
15,000 15,000 15,000
(20,000) - -
- - -
4.0% 3.5% 3.0%
36.0% 36.0% 35.0%
18.0% 18.0% 18.0%
10,000 10,000 10,000
40.0% 40.0% 40.0%
5.0% 5.0% 5.0%
28.0% 28.0% 28.0%
18 18 18
73 73 73
37 37 37
20,000 20,000 20,000
(20,000) - -
- - -
12.0% 12.0% 12.0%
35.0% 34.0% 34.0%
16.0% 16.0% 16.0%
10,000 10,000 10,000
40.0% 40.0% 40.0%
5.0% 5.0% 5.0%
28.0% 28.0% 28.0%
18 18 18
73 73 73
37 37 37
15,000 15,000 15,000
(20,000) - -
- - -