This Excel equity valuation model accepts user input from the income statement and balance sheet portions of a firm's 10-K report and the practitioner's views on the direction of the stock market in order to calculate a per share equity value.
Discounted cash flow analysis is bolstered by a comprehensive analysis of performance ratios that are calculated based on the user's inputs to the model.
The worksheets contained in the Equity Valuation Model for Excel include:
- Income Statement - Revenues, Cost of Goods Sold, Selling, General and administrative expenses, Depreciation, Amortization of goodwill, Interest expense, net, Total costs and expenses, Provision for income taxes , Income before extraordinary items, Extraordinary items, Net income, Shares outstanding, Earnings per share (EPS), Dividends, Exercise of Stock Options, Shares issued, Foreign Currency Adjustment, plus Common-Size Statement (% of Revenues).
- Balance Sheet - Assets, Operating cash, Accounts receivable, Inventories, Other current assets, Total current assets, Gross property, plant and equipment, Accumulated depreciation, Net property, plant and equipment, Other assets, Goodwill, Discontinued operations, Total Assets, Liabilities and Shareholders' Equity, Short-term debt and current portion of long-term debt, Accounts payable, Accrued liabilities, Total current liabilities, Long-term debt, Other long-term liabilities, Deferred income taxes, Total liabilities, Total shareholders' equity, Total liabilities and equity, plus Common-Size Balance Sheet Data.
- WACC Calculation - Expected return on stock market, Risk free rate of return, Beta, Total Debt, Total Equity, Debt (percent of total capitalization), Equity (percent of total capitalization), Cost of debt, Cost of equity , Weighted average cost of capital (WACC).
- DCF Valuation - Cash flow, Discounted cash flow (at WACC), Perpetual future growth rate, PV of terminal value, Present value of firm, Less long-term debt, Value of equity, Per share equity value.
- Performance Ratios - Ending share price, Price/Earnings ratio, Price/Book ratio, Price/Sales ratio, Price/EBIT ratio, Asset turnover ratio, Equity capital turnover ratio, Sales per share, Operating cash flow, Cash flow per share, Earnings per share (EPS).
A separate spreadsheet to simply calculate Cost of Equity and Weighted Average Cost of Capital (WACC) for a business is also available here.Get It (Pricing is available on the next page)(Updated on 2023-04-01) |