The 3 Types Of Financial Models You Can Ace With Excel
By Afia Ahmad
The applications of MS Excel are vast in number, and of course a well-known application is to create great financial models. There are various kinds of financial models one can create, but there are certain models in the industry which are exclusively created on Excel.
These models are widely in use throughout the industry, and if you are new to them, you can learn more about them through an online course or an Excel Tutorial.
Now to give you an idea of how excel is used in the industry to create financial models, here are three types of financial models you can ace with excel.
Company Financial Models
As you might know, a sell-side analyst or an Equity Research Analyst is one who evaluates companies and estimates future earnings, growth, and other investment criteria. These tough and complex decisions are made based on a collection of the company’s models which are nothing but excel sheets with the required data.
Here the x-axis is used to denote time i.e quarters and years and the y-axis is used for breaking down the results by line-item this could be revenue, cost of goods, etc. Most times there is also a second sheet that keeps track of units-sold-and-estimated selling, for small companies and a revenue estimate for large companies. These models can be very detailed or fairly simple but the basic format does not change and all you need is the simple excel sheet. The only thing that changes is the guess work made by the sell-side analyst.
Once the required estimates are fed in, the mathematical formulas need to be checked and then you’re done. With just this simple model as a base, you can build sophisticated and interconnected models for income statements, cash flow statement and much more.
Picture Courtesy - 1global
If you don’t have the need or desire to make company models, then why not build a valuation model. You can use metrics like price-earnings, price-earnings-growth or EV/EBITDA. If this is not what you need or if you’re looking for something more complex, look at a discounted cash flow model.
Picture Courtesy – busysoftorder
Discounted Cash Flow (DCF)
As you know a DCF or Discounted Cash Flow model is used to understand the attractiveness of an investment opportunity. In this model one row consists of year-by-year cash flow estimates, while rows/columns beneath can hold the growth estimates, discount rate, shares outstanding and cash/debt balance.
You will also need to have an estimate for year 1, estimate growth rates, add a discount rate, net cash, shares, and debt balance. Now use the NPV (net present value) function in excel to process your cash flow estimates and discount rate into an estimated NPV. Now you can add or subtract the net cash and debt, and then divide by the shares, you also need to factor in a terminal value.
Picture Courtesy - Financewalk
With these three models, you can achieve a lot and the best part is you don’t need to invest lots in Professional Courses, you can pick up these excel skills from Online Training Programs and Online Certification Training options, sign up and enhance your skills today.