ACC 345 Business Valuation Model
Summary- Start Here
To begin follow these steps:
1. Enter the numbers from your company's balance sheet and income statement for each year, starting with
the most recent year through the prior five years (Example: if the most recent is 2017 then go back through
2013).
2. Ratios auto-calculate but you may wish to make an adjustment if necessary. You will only use the ratios for
explanatory or analysis purposes in your report. There is nothing more to do with them in this workbook.
3. Create your prospective analysis by changing the growth rate for Revenue, the percentage of Revenue fo
Gross Profit and Operating expenses, and then add Other Income or expense items. You can do this in the
cells highlighted in yellow. This will give you your projected net income, which you will then use to discount
to present value on the "dcf" tab later. The default number for Revenue in the prospective analysis is the
most recent year's Revenue number plus 2.5%. You may change it.
4. On the "discount rate" tab you are welcome to leave the number as is or go through and make adjustments.
In most cases you will need access to data that is unavailable or requires a paid subscription, which is why
you're allowed to keep the default values. If you're able to obtain any of those figures then you may use them.
The detail was provided to expose you to the concepts, but not actually require the research since it may be
cost prohibitive.
5. The "dcf" tab feeds your projected Net Income figures from the "prospective analysis" tab. To that numbe
you will add back depreciation since it's a non-cash item and then subtract expected capital expenditures o
and planned debt reductions. You may estimate these if you're unable to find any projection by the company.
It is not required that these be fully accurate since you don't have access to management's plans. Enter those
numbers in the yellow highlighted cells. You shouldn't have to change any other cells in that tab.
6. On the last tab, "valuation summary", the only values you need to change are the cells in yellow for the DLOC
and the DLOM. You may leave these as the default values since these also require access to data that may
be only acquired via subscription or purchase. If you're able to find material supporting a change in those
values then you're free to do so. The goal in introducing them in this manner is to get you exposed to the
concepts, not the actual calculation as that is beyond the scope of this course.
Consider these factors when working through the model:
1. The financial statements you encounter in the annual report will look differently than they do in this model.
Categories will be different than what you find in the annual report, so just use your best judgement when
classifying them and if you need to lump certain costs together then do so. (Example: your company
shows Cost of Sales of $100k, G&A of $50k, and Marketing expense of $10k. Combine the G&A and
Marketing in the single line on the income statement called "General, Administrative and other non-operating
expenses" in the amount of $60k. This places Marketing into the "Other" catch all category.
2. You may insert any "Key Assumptions" that you want to convey using the space below the balance sheet,
income statement, or prospective analysis. This could be anything from combining certain line items to
explaining apparent anomalies.
3. Make sure to net your interest income and expense on the income statement. So in some years you may have
a positive balance and a negative in others.
4. The "Normalization adjustments" listed on the "income statement" tab are refe
ing to the adjustments discussed
in module three. To recap - Normalization adjustments are changes that you as an analyst can make in order to
"normalize" any anomalies or non-recu
ing items that may have been reported in the financial statements. For
example, if your company was exposed to a natural disaster and you know management does not expect that
type of major expense in the future then you can add it back under this section. Another example would be
a class-action lawsuit that resulted in a major settlement. While companies are always subject to lawsuits, one that
results in a material settlement may be removed if it's unexpected to occur again in the near future.
alance sheet
Company ABC Inc.
Balance Sheets (in millions)
December 31, 2014 through 2018
Common-size analysis
2013 2014 2015 2016 2017 2013 2014 2015 2016 2017
Assets
Cu
ent Assets
Cash and cash equivalents $ - $ - $ - $ - $ - - 0 % - 0 % - 0 % - 0 % - 0 %
Accounts receivable, net - - - - - - 0 - 0 - 0 - 0 - 0
Inventory - - - - - - 0 - 0 - 0 - 0 - 0
Other cu
ent assets - - - - - - 0 - 0 - 0 - 0 - 0
Total cu
ent assets - - - - - - 0 - 0 - 0 - 0 - 0
Property, plant & equipment, net - - - - - - 0 - 0 - 0 - 0 - 0
Other assets
Intangibles - - - - - - 0 - 0 - 0 - 0 - 0
Other assets - - - - - - 0 - 0 - 0 - 0 - 0
Total other assets - - - - - - 0 - 0 - 0 - 0 - 0
Total Assets $ - $ - $ - $ - $ - - 0 % - 0 % - 0 % - 0 % - 0 %
Liabilities and Stockholders' Equity
Cu
ent Liabilities
Accounts payable $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 - 0 % - 0 % - 0 % - 0 % - 0 %
Accrued expenses & other cu
ent liabilities - - - - - - 0 - 0 - 0 - 0 - 0
Cu
ent portion of debt and leases - - - - - - 0 - 0 - 0 - 0 - 0
Total cu
ent liabilities - - - - - - 0 - 0 - 0 - 0 - 0
Long-Term Liabilities
Long-term debt and lease obligations - - - - - - 0 - 0 - 0 - 0 - 0
Other long-term liabilities - - - - - - 0 - 0 - 0 - 0 - 0
Total long-term liabilities - - - - - - 0 - 0 - 0 - 0 - 0
Total Liabilities - - - - - - 0 - 0 - 0 - 0 - 0
Stockholders' Equity
Common stock, less treasury - - - - - - 0 - 0 - 0 - 0 - 0
Additional paid in capital - - - - - - 0 - 0 - 0 - 0 - 0
Retained earnings - - - - - - 0 - 0 - 0 - 0 - 0
Other comprehensive income (loss) - - - - - - 0 - 0 - 0 - 0 - 0
Total Stockholders' Equity - - - - - - 0 - 0 - 0 - 0 - 0
$ - $ - $ - $ - $ - - 0 % - 0 % - 0 % - 0 % - 0 %
0.00 0.00 0.00 0.00 0.00
$ - $ - $ - $ - $ -
*Key Assumptions:
income statement
Company ABC Inc.
Statements of Income (in millions)
December 31, 2014 through 2018
Common-size analysis
2013 2014 2015 2016 2017 2013 2014 2015 2016 2017
Sales $ - $ - $ - $ - $ - - 0 % - 0 % - 0 % - 0 % - 0 %
Cost of Sales - - - - - - 0 - 0 - 0 - 0 - 0
Gross Profit - - - - - - 0 - 0 - 0 - 0 - 0
General, administrative and
non-operating expenses - - - - - - 0 - 0 - 0 - 0 - 0
Operating Income - - - - - - 0 - 0 - 0 - 0 - 0
Other Income (Expense)
Interest (expense) - - - - - - 0 - 0 - 0 - 0 - 0
Gain (loss) on sale of assets - - - - - - 0 - 0 - 0 - 0 - 0
Other - - - - - - 0 - 0 - 0 - 0 - 0
- - - - - - 0 - 0 - 0 - 0 - 0
Normalization adjustments
Non-recu
ing items - - - - - - 0 - 0 - 0 - 0 - 0
Legal settlements - - - - - - 0 - 0 - 0 - 0 - 0
Other - - - - - - 0 - 0 - 0 - 0 - 0
- - - - - - 0 - 0 - 0 - 0 - 0
Net income, before tax $ - $ - $ - $ - $ - - 0 % - 0 % - 0 % - 0 % - 0 %
*Key Assumptions:
atios
Company ABC Inc.
Financial and Operating Ratios
December 31, 2014 through 2018
2013 2014 2015 2016 2017
Liquidity Ratios
Cu
ent Ratio - 0 - 0 - 0 - 0 - 0
Quick Ratio - 0 - 0 - 0 - 0 - 0
Working Capital $ - $ - $ - $ - $ -
Activity Ratios
Receivable Turns - 0 - 0 - 0 - 0 - 0
Days in Receivables - 0 - 0 - 0 - 0 - 0
Revenues/Working Capital - 0 - 0 - 0 - 0 - 0
Revenues/Fixed Assets - 0 - 0 - 0 - 0 - 0
Revenues/Total Assets - 0 - 0 - 0 - 0 - 0
Inventory Turns - 0 - 0 - 0 - 0 - 0
Days in Inventory - 0 - 0 - 0 - 0 - 0
Payables Turns - 0 - 0 - 0 - 0 - 0
Days in Payables - 0 - 0 - 0 - 0 - 0
Coverage/Leverage Ratios
Fixed Assets/Equity - 0 - 0 - 0 - 0 - 0
Profitability Ratios
Return on Equity - 0 % - 0 % - 0 % - 0 % - 0
Return on Total Assets - 0 % - 0 % - 0 % - 0 % - 0
Net Profit on Revenues - 0 % - 0 % - 0 % - 0 % - 0
N/A - Not applicable
Change in sales ERROR:#DIV/0! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! -100.00%
prospective analysis
Company ABC Inc.
Projected Income Statement (In millions)
2018 2019 2020 2021 2022 Terminal
Revenue $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Growth 2.5% 2.5% 2.5% 2.5% 2.5% 2.5%
Gross profit - 0 - 0 - 0 - 0 - 0 - 0
Percentage of revenue 7.0% 7.0% 7.0% 7.0% 7.0% 7.0%
Operating expenses - 0 - 0 - 0 - 0 - 0 - 0
Percentage of revenue 5.0% 5.0% 5.0% 5.0% 5.0% 5.0%
Other income (expense)
Interest income (expense) - 0 - 0 - 0 - 0 - 0 - 0
Other - 0 - 0 - 0 - 0 - 0 - 0
- 0 - 0 - 0 - 0 - 0 - 0
Percentage of revenue 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Net income $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
*Key Assumptions:
discount rate
Company ABC Inc.
Development of Discount Rate and Capitalization Rate
Rate Note
Risk-free long term U.S. Government bond rate 2.6 % (A)
Equity risk premium 6.0 (B)
Industry premium estimate 1.5 (C)
Specific company risk 3.0 (D)
Cost of equity (Discount rate) 13.1 Sum of (A) - (D)
Less: Long-term sustainable growth rate (2.5) (E)
Capitalization rate 10.6 %
(A) Yield on the twenty-year U.S. Treasury bond as of December 31, 20XX, per the U.S. Treasury
(B) Long-horizon expected return of large stocks over risk free securities, U.S. Equity Risk Premium (6.0%)
(C) SIC code XX, 1.5%
(D) Appraiser's judgement concerning company-specific risk
(E) Estimated long-term growth rate based on inflation, Federal Reserve Bank of Philadelphia
Sources:
United States Treasury
***You may use other sources to update any of these values; list the applicable source if used. Existing
values are actual figures obtained from sources used in prior years. You may use these as default
values since a detailed development of the discount rate is beyond the scope of this class.
dcf
Company ABC Inc.
Discounted Cash Flow Method (In millions)
Projected for Years Ending December 31,
Terminal
2018 2019 2020 2021 2022 Value
Forecasted Net Income $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Plus:
Depreciation - 0 - 0 - 0 - 0 - 0 - 0
Less:
Capital expenditures - 0 - 0 - 0 - 0 - 0 - 0
Debt reduction - 0 - 0 - 0 - 0 - 0 - 0
Net Cash Flow $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Present value of cash flows $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Discount rate: 13.1%
Terminal period cash flows $ - 0
Capitalization rate: 10.6% ÷ 10.6%
Capitalized terminal cash flow $ - 0
Net present value of terminal cash flow, discounted into perpetuity $ - 0