Answer To: ACC 345 Business Valuation Report Template Summary Business Valuation Report of Company ABC, Inc....
Nitish Lath answered on Sep 10 2022
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 for
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 number
you will add back depreciation since it's a non-cash item and then subtract expected capital expenditures or
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 referring 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-recurring 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.
balance sheet
Amazon Inc.
Balance Sheets (in millions)
December 31, 2017 through 2021
Common-size analysis
2017 2018 2019 2020 2021 2017 2018 2019 2020 2021
Assets
Current Assets
Cash and cash equivalents $ 30,986 $ 41,250 $ 55,021 $ 84,396 $ 96,049 23.6 % 25.4 % 24.4 % 26.3 % 22.8 %
Accounts receivable, net and others 13,164 16,677 20,816 24,542 32,891 10.0 10.3 9.2 7.6 7.8
Inventory 16,047 17,174 20,497 23,795 32,640 12.2 10.6 9.1 7.4 7.8
Other current assets - - - - - - 0 - 0 - 0 - 0 - 0
Total current assets 60,197 75,101 96,334 132,733 161,580 45.8 46.3 42.7 41.3 38.4
Property, plant & equipment, net 48,866 61,797 72,705 113,114 160,281 37.2 38.0 32.3 35.2 38.1
Other assets
Operating leases - - 25,141 37,553 56,082 - 0 - 0 11.2 11.7 13.3
Goodwill 13,350 14,548 14,754 15,017 15,371 10.2 8.9 6.6 4.7 3.7
Other assets 8,897 11,202 16,314 22,778 27,235 6.8 6.9 7.2 7.1 6.5
Total other assets 22,247 25,750 56,209 75,348 98,688 17.0 15.8 25.0 23.5 23.5
Total Assets $ 131,310 $ 162,648 $ 225,248 $ 321,195 $ 420,549 100.0 % 100.1 % 100.0 % 100.0 % 100.0 %
Liabilities and Stockholders' Equity
Current Liabilities
Accounts payable $ 34,616 $ 38,192 $ 47,183 $ 72,539 $ 78,664 26.4 % 23.5 % 20.9 % 22.6 % 18.7 %
Accrued expenses & other current liabilities 18,170 23,663 32,439 44,138 51,775 13.8 14.5 14.4 13.7 12.3
Unearned revenue 5,097 6,536 8,190 9,708 11,827 3.9 4.0 3.6 3.0 2.8
Total current liabilities 57,883 68,391 87,812 126,385 142,266 44.1 42.0 38.9 39.3 33.8
Long-Term Liabilities
Long-term debt and lease obligations 24,743 33,145 63,205 84,389 116,395 18.8 20.4 28.1 26.3 27.7
Other long-term liabilities 20,975 17,563 12,171 17,017 23,643 16.0 10.8 5.4 5.3 5.6
Total long-term liabilities 45,718 50,708 75,376 101,406 140,038 34.8 31.2 33.5 31.6 33.3
Total Liabilities 103,601 119,099 163,188 227,791 282,304 78.9 73.2 72.4 70.9 67.1
Stockholders' Equity
Common stock, less treasury (1,832) (1,832) (1,832) (1,832) (1,832) (1.4) (1.1) (0.8) (0.6) (0.4)
Additional paid in capital 21,389 26,791 33,658 42,865 55,538 16.3 16.5 14.9 13.3 13.2
Retained earnings 8,636 19,625 31,220 52,551 85,915 6.6 12.1 13.9 16.4 20.4
Other comprehensive income (loss) (484) (1,035) (986) (180) (1,376) (0.4) (0.6) (0.4) (0.1) (0.3)
Total Stockholders' Equity 27,709 43,549 62,060 93,404 138,245 21.1 26.9 27.6 29.0 32.9
$ 131,310 $ 162,648 $ 225,248 $ 321,195 $ 420,549 100.0 % 100.1 % 100.0 % 99.9 % 100.0 %
0.00 0.00 0.00 0.00 0.00
$ - $ - $ - $ - $ -
*Key Assumptions:
income statement
Amazon Inc.
Statements of Income (in millions)
December 31, 2017 through 2021
Common-size analysis
2017 2018 2019 2020 2021 2017 2018 2019 2020 2021
Sales $ 177,866 $ 232,887 $ 280,522 $ 386,064 $ 469,822 100.0 % 100.0 % 100.0 % 100.0 % 100.0 %
Cost of Sales 111,934 139,156 165,536 233,307 272,344 62.9 59.8 59.0 60.4 58.0
Gross Profit 65,932 93,731 114,986 152,757 197,478 37.1 40.2 41.0 39.6 42.0
General, administrative and
non-operating expenses 61,826 81,310 100,445 129,858 172,599 34.8 34.9 35.8 33.6 36.7
Operating Income 4,106 12,421 14,541 22,899 24,879 2.3 5.3 5.2 6.0 5.3
Other Income (Expense)
Interest (expense) (646) (977) (768) (1,092) (1,361) (0.4) (0.4) (0.3) (0.3) (0.3)
Gain (loss) on sale of assets - - - - - - 0 - 0 - 0 - 0 - 0
Other 346 (183) 203 2,371 14,633 0.2 (0.1) 0.1 0.6 3.1
(300) (1,160) (565) 1,279 13,272 (0.2) (0.5) (0.2) 0.3 2.8
Normalization adjustments
Non-recurring 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 $ 3,806 $ 11,261 $ 13,976 $ 24,178 $ 38,151 2.1 % 4.8 % 5.0 % 6.3 % 8.1 %
*Key Assumptions:
ratios
Amazon Inc.
Financial and Operating Ratios
December 31, 2017 through 2021
2017 2018 2019 2020 2021
Liquidity Ratios
Current Ratio 1.04 1.10 1.10 1.05 1.14
Quick Ratio 0.76 0.85 0.86 0.86 0.91
Working Capital $ 2,314 $ 6,710 $ 8,522 $ 6,348 $ 19,314
Activity Ratios
Receivable Turns 13.51 15.61 14.96 17.02 16.36
Days in Receivables 27.00 23.40 24.40 21.40 22.30
Revenues/Working Capital 76.87 34.71 32.92 60.82 24.33
Revenues/Fixed Assets 3.64 3.77 3.86 3.41 2.93
Revenues/Total Assets 1.35 1.43 1.25 1.20 1.12
Inventory Turns 11.08 13.56 13.69 16.22 14.39
Days in Inventory 32.90 26.90 26.70 22.50 25.40
Payables Turns 3.23 3.64 3.51 3.22 3.46
Days in Payables 112.90 100.20 104.00 113.50 105.40
Coverage/Leverage Ratios
Fixed Assets/Equity 1.76 1.42 1.17 1.21 1.16
Profitability Ratios
Return on Equity 0.1 % 0.3 % 0.2 % 0.3 % 0.3
Return on Total Assets 0.0 % 0.1 % 0.1 % 0.1 % 0.1
Net Profit on Revenues 2.1 % 4.8 % 5.0 % 6.3 % 8.1
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
Amazon Inc.
Projected Income Statement (In millions)
2022 2023 2024 2025 2026 Terminal
Revenue $ 540,300 $ 621,300 $ 714,500 $ 821,700 $ 945,000 $ 1,086,800
Growth 15.0% 15.0% 15.0% 15.0% 15.0% 15.0%
Gross profit 189,105 217,455 250,075 287,595 330,750 380,380
Percentage of revenue 35.0% 35.0% 35.0% 35.0% 35.0% 35.0%
Operating expenses 162,090 186,390 214,350 246,510 283,500 326,040
Percentage of revenue 30.0% 30.0% 30.0% 30.0% 30.0% 30.0%
Other income (expense)
Interest income (expense) (1,361) (1,361) (1,361) (1,361) (1,361) (1,361)
Other - 0 - 0 - 0 - 0 - 0 - 0
(1,361) (1,361) (1,361) (1,361) (1,361) (1,361)
Percentage of revenue -0.3% -0.2% -0.2% -0.2% -0.1% -0.1%
Net income $ 25,654 $ 29,704 $ 34,364 $ 39,724 $ 45,889 $ 52,979
*Key Assumptions:
discount rate
Amazon 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
Amazon Inc.
Discounted Cash Flow Method (In millions)
Projected for Years Ending December 31,
Terminal
2022 2023 2024 2025 2026 Value
Forecasted Net Income $ 25,654 $ 29,704 $ 34,364 $ 39,724 $ 45,889 $ 52,979
Plus:
Depreciation 11,000 11,550 12,128 12,734 13,371 14,039
Less:
Capital expenditures (1,500) (1,500) (1,500) (1,500) (1,500) (1,500)
Debt reduction (2,000) (2,000) (2,000) (2,000) (2,000) (2,000)
Net Cash Flow $ 33,154 $ 37,754 $ 42,992 $ 48,958 $ 55,760 $ 63,518
Present value of cash flows $ 29,314 $ 29,515 $ 29,716 $ 29,921 $ 30,131
Discount rate: 13.1%
Terminal period cash flows $ 63,518
Capitalization rate: 10.6% ÷ 10.6%
Capitalized terminal cash flow $ 599,227
Net present value of terminal cash flow, discounted into perpetuity $ 323,800
Net present value - five years ending YE: 2026 $ 148,600
Net present value of terminal cash flow 323,800
Total indication of value (rounded) $ 472,400
1 2 3 4 5
valuation summary
Amazon Inc.
Final Computation of Value
As of December 31, 2021
Income Approach: Discounted Cash Flow Method
Indicated Value of Equity $ 472,400
Weight 100 %
Weighted Value (rounded) $ 472,400
Indicated value with voting rights $ 472,400
Less: DLOC (Discount for Lack of Control) 15.0% (70,860)
Marketable, minority value 401,540
Less: DLOM (Discount for Lack of Marketability) 25.0% (100,385)
Nonmarketable, minority value $ 301,155
Value of a one-percent interest (in millions) $ 3,012
*if valuing an interest greater than 50% then the DLOC will not...