FINANCIAL MODELS MADE EASY

Since analytics and subscriptions are quite expensive, it is good to know that a huge chunk of work done by experts can be done through Excel, which is one click away on your own computer. Using this simple office application, you too can create your financial drafts. If you have extra time to spare, spending it on learning macros will also provide you with more options.

Sell-side analysts always have a collection of these models created in spreadsheets that serves as their basis in forming their opinion on a certain company’s outlook. However, no matter how detailed or complex it may be, it is always important to keep in mind that these projections are still an outcome of guesswork, which may go wrong any time.

Typically, these structures are comprised of an X and Y axis in excel, wherein the former serves as the time, while the latter represents the detailed results by line. This includes revenue, earnings, costs, and so on. Often, there is a separate sheet for estimates and it can either be a per-segment system or a more simple version depending on the business size.

After the input of necessary items, make sure the mathematical formulas are correct and from the results, you may now generate an interlinked model of income and cash flow statement, as well as balance sheet. For those familiar with macros, you may incorporate bull/bear/base outlines that can easily be altered.

Valuation models are always a must. For a simpler method, you may use the standard ratios such as P/E or EBITDA and if this works out just fine, there is no need to go for a more complicated one. In case you want a more meticulous way, you may opt for a DCF model.

DCF, or discounted cash flow modelling, is considered the gold standard for valuations. In using this style, there should be a row for the annual cycle of funds while columns below should have the growth estimates, discount rate, shares, and debt. You need to have a starting point for the initial year, and create the next ones either per year or in bulk wherein you apply the same progress rate for the succeeding years.

Once you have placed all the needed items mentioned earlier, it is time to make use of your spreadsheet's net present value function to process your estimates, wherein you may now deduct or add debts, and divide by shares. Lastly, do no neglect to put in the terminal value, which will represent all future cash flows in a valuation model.