Wall Street has always loved its toys. While there is very little that I miss about being an analyst on either the buy-side or sell-side, I will freely admit that there are days when I miss having a Bloomberg terminal or access to a Factset database. However, for all of the expensive subscriptions and analytics programs, a huge amount of the work that Wall Street analysts and managers do is done on the same software that you have on your own computer.

With just a little bit of effort, you too can create a variety of financial and analytical models, and investing the additional time and energy to learn about macros can give you even more options.

SEE: Buy Side Vs. Sell Side Analysts

Company Financial Models
The core of what every sell-side analyst (and many buy-side analysts) does is his or her collection of a company financial models. These are simply spreadsheets that hold (and help form) the analyst's views on the likely financial results for the company in question. They can be incredibly detailed and complex, or relatively simplistic, but the model will never be any better than the quality of the work that goes into forming the estimates. In other words, elaborate guesswork is still just guesswork.

SEE: The Impact Of Sell-Side Research

Financial Models are usually built with the x-axis serving as the time (quarters and full years) and the y-axis breaking down the results by line-item (i.e., revenue, cost of goods sold, etc.) It is not at all uncommon to have a separate sheet generating the revenue estimate; whether that is a per-segment basis for a large conglomerate like United Technologies or General Electric, or a more simple units-sold-and-estimated selling price for a smaller, simpler company.

Building these models is relatively straightforward. The model-builder needs to input estimates for certain items (i.e., revenue, COGS/gross margin, SG&A/sales) and then make sure that the mathematical formulas are correct. From this simple base, it is also possible to build sophisticated and interconnected models for the income statement, balance sheet and cash flow statement, as well as build macros that allow investors to create "bull/bear/base" scenarios that can be changed with a simple click or two.

Valuation Models
Although most would deny it, surprisingly few buy-side analysts actually build their own company models from scratch, in my experience. Instead, they will essentially copy the models built by sell-side analysts and "stress test" them to see how the numbers respond to a variety of circumstances.

Even if you don't build your own company models, you should seriously consider building your own valuation models. Now, some investors are content with using simple metrics like price-earnings, price-earnings-growth or EV/EBITDA, and if that works for you then there's no reason to change. Investors who want a more rigorous approach, though, ought to consider a discounted cash flow model.

Discounted Cash Flow (DCF)
DCF modeling is pretty much the gold standard for valuation, and plenty of books have been written on how free cash flow (operating cash flow minus capital expenditures, at its simplest level) is the best proxy for corporate financial performance.

Building a cash flow model is surprisingly easy. One row will serve to hold the year-by-year cash flow estimates, while rows/columns beneath can hold the growth estimates, discount rate, shares outstanding and cash/debt balance.

Clearly there needs to be a starting estimate for "Year 1" and that estimate can come from your own company financial model or sell-side analyst models. Estimating the growth rates is the next step; you can create individual year-by-year estimates if you like, or simply use "bulk estimates" that apply the same growth rate for Years 2 to 5, 6 to 10, 10 to 15 and so on. You then need to input a discount rate (a number that you can calculate with the CAPM model or some other method) in a separate cell, as well as the shares outstanding and net cash/debt balance (all in separate cells).

Once this is done, use your spreadsheet's NPV (net present value) function to process your cash flow estimates and discount rate into an estimated NPV, to which you can add/subtract the net cash/debt, and then divide by the shares outstanding. As part of this process, do not forget to calculate and include a terminal value (most analysts calculate explicit cash flows for 10 or 15 years and then apply a terminal value).

The Bottom Line
Investors must remember that detailed or sophisticated modeling is no substitute for judgment and discretion. All too often, analysts lean too heavily on their models and forget to do the occasional "reality check" regarding their core assumptions. Nevertheless, building your own models can teach you a lot about what a particular company must do to grow, what that growth is worth and what the Street already expects from a particular company. Accordingly, the relatively modest amount of time it takes to build these models can often pay for itself many times over, by leading you to better investment decisions.

SEE: Style Matters In Financial Modeling

Related Articles
  1. Stock Analysis

    Fortinet: A Great Play on Cybersecurity

    Discover how a healthy product mix, large-business deal growth and the boom of the cybersecurity industry are all driving Fortinet profits.
  2. Stock Analysis

    2 Catalysts Driving Intrexon to All-Time Highs

    Examine some of the main reasons for Intrexon stock tripling in price between 2014 and 2015, and consider the company's future prospects.
  3. Charts & Patterns

    Understand How Square Works before the IPO

    Square is reported to have filed for an IPO. For interested investors wondering how the company makes money, Investopedia takes a look at its business.
  4. Technical Indicators

    4 Ways to Find a Penny Stock Worth Millions

    Thinking of trading in risky penny stocks? Use this checklist to find bargains, not scams.
  5. Professionals

    Chinese Slowdown Affects Iron Ore Market

    The Chinese economy's ongoing slowdown is having a major impact on iron ore demand.
  6. Investing Basics

    Why do Debt to Equity Ratios Vary From Industry to Industry?

    Obtain a better understanding of the debt/equity ratio, and learn why this fundamental financial metric varies significantly between industries.
  7. Investing

    What’s Holding Back the U.S. Consumer

    Even as job growth has surged and gasoline prices have plunged, U.S. consumers are proving slow to respond and repair their overextended balance sheets.
  8. Credit & Loans

    What's a Nonperforming Loan?

    A nonperforming loan is any borrowed sum where the borrower has failed to pay scheduled payments for at least 90 days.
  9. Economics

    Understanding Cost of Revenue

    The cost of revenue is the total costs a business incurs to manufacture and deliver a product or service.
  10. Economics

    Understanding Cash and Cash Equivalents

    Cash and cash equivalents are items that are either physical currency or liquid investments that can be immediately converted into cash.
RELATED TERMS
  1. Gross Profit

    A company's total revenue (equivalent to total sales) minus the ...
  2. Receivables Turnover Ratio

    An accounting measure used to quantify a firm's effectiveness ...
  3. International Financial Reporting ...

    A set of international accounting standards stating how particular ...
  4. Revenue

    The amount of money that a company actually receives during a ...
  5. Balance Sheet

    A financial statement that summarizes a company's assets, liabilities ...
  6. Equity

    The value of an asset less the value of all liabilities on that ...
RELATED FAQS
  1. What is the formula for calculating compound annual growth rate (CAGR) in Excel?

    The compound annual growth rate, or CAGR for short, measures the return on an investment over a certain period of time. Below ... Read Full Answer >>
  2. What are some examples of general and administrative expenses?

    In accounting, general and administrative expenses represent the necessary costs to maintain a company's daily operations ... Read Full Answer >>
  3. What is the difference between called-up share capital and paid-up share capital?

    The difference between called-up share capital and paid-up share capital is investors have already paid in full for paid-up ... Read Full Answer >>
  4. Why can additional paid in capital never have a negative balance?

    The additional paid-in capital figure on a company's balance sheet can never be negative because companies do not pay investors ... Read Full Answer >>
  5. When does the fixed charge coverage ratio suggest that a company should stop borrowing ...

    Since the fixed charge coverage ratio indicates the number of times a company is capable of making its fixed charge payments ... Read Full Answer >>
  6. What is the difference between the return on total assets and an interest rate?

    Return on total assets (ROTA) represents one of the profitability metrics. It is calculated by taking a company's earnings ... Read Full Answer >>

You May Also Like

Trading Center
×

You are using adblocking software

Want access to all of Investopedia? Add us to your “whitelist”
so you'll never miss a feature!