Skip to main content

Applied Valuation of a Company using Excel

This topic focuses on the practical application of valuation principles, using Excel to perform a comprehensive company valuation. It incorporates elements from previous topics, demonstrating how to build a financial model, forecast cash flows, estimate discount rates, and calculate enterprise and equity values.

I. Setting up the Excel Model:

  1. Gather Historical Financial Data:
    • Collect historical financial statements (Income Statement, Balance Sheet, Cash Flow Statement) for the past 3-5 years.
    • Organize the data in separate Excel sheets.
    • Ensure consistency in accounting practices and data presentation.
  2. Input Assumptions Sheet:
    • Create a separate sheet for all key assumptions:
      • Revenue growth rates
      • Operating margins (EBIT margin, EBITDA margin)
      • Tax rate
      • Capital expenditure assumptions (as % of revenue)
      • Working capital assumptions (receivables, inventory, payables as % of revenue)
      • Debt assumptions (interest rate, debt levels)
      • Discount rate (WACC or Cost of Equity)
      • Terminal value growth rate or exit multiple
  3. Historical Analysis Sheet:
    • Calculate key financial ratios and metrics from the historical data:
      • Growth rates (revenue, earnings)
      • Profitability ratios (gross margin, operating margin, net margin, ROE, ROA)
      • Efficiency ratios (asset turnover, inventory turnover, receivables turnover)
      • Leverage ratios (debt-to-equity, debt-to-assets)
    • Analyze trends in these ratios to inform future forecasts.

II. Forecasting Financial Statements:

  1. Revenue Forecast:
    • Project revenue growth based on historical trends, industry outlook, and company-specific factors.
    • Use growth rates from the Assumptions sheet and apply them to the historical revenue data.
  2. Income Statement Forecast:
    • Forecast cost of goods sold (COGS) and operating expenses as a percentage of revenue, based on historical averages and any expected changes in efficiency.
    • Calculate EBIT (Earnings Before Interest and Taxes).
    • Project interest expense based on debt assumptions and interest rates.
    • Calculate taxable income and income tax expense.
    • Calculate net income.
  3. Balance Sheet Forecast:
    • Forecast current assets (accounts receivable, inventory) and current liabilities (accounts payable) as a percentage of revenue, based on historical averages.
    • Project capital expenditures based on assumptions about growth and asset replacement.
    • Forecast depreciation expense based on historical depreciation rates.
    • Project debt levels based on financing assumptions.
    • Calculate retained earnings.
    • Ensure that the balance sheet balances (Assets = Liabilities + Equity). Use a plug variable (e.g., cash) to force the balance sheet to balance.
  4. Cash Flow Statement Forecast:
    • Use the forecasted income statement and balance sheet data to prepare a projected cash flow statement.
    • Calculate cash flow from operations, cash flow from investing, and cash flow from financing.

III. Valuation Calculations:

  1. Calculate Free Cash Flow:
    • Based on your choice, calculate FCFF or FCFE based on the projections. Use the formulas from previous sections.
  2. Estimate the Discount Rate:
    • Calculate the WACC or Cost of Equity (Ke), as discussed in previous topics. The specific formulas will depend on the data available.
  3. Determine the Terminal Value:
    • Use the Gordon Growth Model or an Exit Multiple approach to estimate the terminal value.
  4. Discount Cash Flows:
    • Discount the forecasted free cash flows and the terminal value back to the present using the appropriate discount rate.
  5. Calculate Enterprise Value:
    • Sum the present values of the forecasted free cash flows and the present value of the terminal value to arrive at the enterprise value of the company.
  6. Calculate Equity Value (if desired):
    • Subtract the market value of debt from the enterprise value to arrive at the equity value.
  7. Sensitivity Analysis:
    • Use Excel's data tables or scenario manager to perform sensitivity analysis.
    • Vary key assumptions (e.g., revenue growth rate, discount rate, terminal value growth rate) to see how they impact the valuation.

IV. Key Excel Functions:

  • Basic Arithmetic Operations: +, -, *, /
  • SUM: Calculates the sum of a range of cells.
  • AVERAGE: Calculates the average of a range of cells.
  • PV (Present Value): Calculates the present value of a future cash flow.
  • FV (Future Value): Calculates the future value of an investment.
  • RATE: Calculates the interest rate per period of an annuity.
  • NPV (Net Present Value): Calculates the net present value of a series of cash flows.
  • IF: Performs a logical test and returns one value if the test is true and another value if the test is false.
  • VLOOKUP/HLOOKUP: Searches for a value in a table and returns a corresponding value.
  • INDEX/MATCH: A more flexible alternative to VLOOKUP/HLOOKUP.
  • Data Tables: Perform sensitivity analysis by varying one or two inputs and observing the impact on the output.
  • Scenario Manager: Create and analyze different scenarios by changing multiple input values.

V. Tips for Building a Robust Excel Model:

  • Keep it Organized: Use clear and consistent formatting, labels, and color-coding.
  • Document Your Assumptions: Clearly state all assumptions and their rationale.
  • Use Formulas: Avoid hardcoding numbers directly into formulas.
  • Check for Errors: Use Excel's error-checking tools to identify and correct errors in the model.
  • Test the Model: Test the model with different inputs to ensure that it is working correctly.
  • Be Flexible: Design the model to be easily updated and modified.

Conclusion:

Applied valuation using Excel requires a combination of financial knowledge, modeling skills, and attention to detail. By following these steps and using Excel's powerful tools, you can build a robust and reliable valuation model that provides valuable insights into a company's worth. This is the culmination of all previous skills.

Back to top