Style in financial modeling is important

Suppose you are reading a wanted advertisement and found an advertisement for a stock analyst. The salary is high; there are opportunities for travel. Looks very suitable for you. Going through the list of qualifications, you have checked each item in your mind:

  • Bachelor of Engineering or Mathematics-Check
  • Master of Economics or Business Administration-Check
  • Curious, creative thinker-check
  • Can explain financial statements-check
  • Powerful technical analysis capabilities-inspection
  • Modeling experience is required-check, don’t wait, it is better to make some 8×10 gloss.

The truth is that when companies want their stock analysts to have modeling experience, they don’t care about their degree of photogenicity. This term refers to an important and complex part of equity analysis, called financial modeling. In this article, we will explore what a financial model is and how to create a financial model.

Financial Modeling Definition

In theory, a financial model is a set of assumptions about future business conditions that drive the company’s revenue, earnings, cash flow, and balance sheet account forecasts.

In fact, financial models are spreadsheets (usually in Microsoft’s Excel software) that analysts use to predict the company’s future financial performance. It is important to correctly predict future earnings and cash flow, because the intrinsic value of stocks depends to a large extent on the prospects of the issuing company’s financial performance.

Financial model spreadsheets usually look like financial data tables organized by fiscal quarter and/or year. Each column of the table represents the balance sheet, income statement, and cash flow statement for the next quarter or year. The rows of the table represent all the line items of the company’s financial statements, such as income, expenses, number of shares, capital expenditures, and balance sheet accounts. As with financial statements, people usually read models from top to bottom or earn income through earnings and cash flow.

Each quarter is embedded with a set of assumptions for the period, such as revenue growth rate, gross margin assumptions, and expected tax rate. These assumptions are the driving factors that drive the output of the model-usually earnings and cash flow data used to value the company or help the company make financing decisions.

READ ALSO:   U-3 and U-6 unemployment rates: what is the difference?

History as a guide

When trying to predict the future, the best starting point is the past. Therefore, the first step in building a model is to fully analyze a set of historical financial data and link the forecast with the historical data as the basis of the model. If a company’s gross profit margin has been between 40% and 45% in the past ten years, it can be assumed that this level of profit margin will be sustainable in the future under the same other conditions.

Therefore, the historical record of gross profit margin can be the basis for future revenue forecasts. Before attempting to predict financial results into the future, analysts are always smart to check and analyze historical trends in revenue growth, expenses, capital expenditures, and other financial indicators. For this reason, financial model spreadsheets usually contain a set of historical financial data and related analysis measures from which analysts can draw assumptions and forecasts.

Revenue forecast

The income growth rate assumption may be one of the most important assumptions in the financial model. Small differences in revenue growth can mean huge differences in earnings per share (EPS) and cash flow, which can lead to huge differences in stock valuations. For this reason, analysts must pay great attention to obtaining correct top-line forecasts. A good starting point is to look at the history of income. Maybe the income is stable year by year. Perhaps it is sensitive to changes in national income or other economic variables over time. Maybe growth is accelerating, or maybe the opposite is true. It is important to understand the factors affecting income in the past in order to make correct assumptions about the future.

Once you have checked the historical trends, including the situation in the most recently reported quarter, it is wise to check whether the management has given revenue guidance, which is the management’s outlook for the future. Based on a comprehensive analysis of the business, analyze whether the prospects are reasonably conservative or optimistic.

READ ALSO:   hell or high water contract

Revenue forecasts for the coming quarters are usually driven by formulas in the worksheet, for example:

R 1 = R 0 × (1 + g) where: R 1 = future income R 0 = current income g = percentage growth rate begin{aligned} &R_1=R_0 times (1 + g) \ &textbf{where :}\ &R_1=text{Future Revenue}\ &R_0=text{Current Revenue}\ &g=text{Percentage Growth Rate}\ end{aligned}

resistance1=resistance×(1+G)Where:resistance1=Future incomeresistance=Current incomeG=Percentage growth rate

Operating expenses and profit margin

Likewise, historical trends are a good starting point when forecasting costs. Acknowledging that there is a big difference between fixed and variable costs incurred by companies, analysts are smart in considering the dollar amount of costs and their proportion of revenue over a period of time. If sales, general, and administrative (SG&A) expenses accounted for between 8% and 10% of revenue in the past ten years, it is likely to fall into this range in the future. This may be the basis of the forecast-again affected by the guidance of management and the overall business outlook. If the business is improving rapidly, reflected in the assumption of revenue growth, then the fixed cost elements of SG&A may be distributed on a larger revenue basis, and the proportion of SG&A expenses next year will be smaller than it is now. This means that profit margins may increase, which may bode well for stock investors.

The expense line assumption is usually reflected as a percentage of revenue, and spreadsheet cells containing expense items usually have the following formula:

E 1 = R 1 × p where: E 1 = expenses R 1 = period income p = period expenses as a percentage of income begin{aligned} &E_1=R_1 times p \ &textbf{where:} &E_1= text{Expenses}\ &R_1=text{Current income}\ &p=text{Percentage of expenses of current income}\ end{aligned}

Second1=resistance1×pWhere:Second1=costresistance1=Current incomep=Current expenses as a percentage of revenue

Non-operating expenses

For industrial companies, non-operating expenses are mainly interest expenses and income tax. The important thing to remember when forecasting interest expense is that it is part of the debt and has no clear connection to the operating income stream. An important analytical consideration is the company’s current total debt level. Taxes are usually not related to income, but to pre-tax income. The tax rate a company pays can be affected by many factors, such as the number of countries in which it operates. If a company is purely a domestic company, it may be safe for analysts to use state tax rates as a good assumption in the forecast. Once again, it is useful to use the history of these line items as a guide for the future.

READ ALSO:   China's economic indicators

Earnings and earnings per share

The estimated net income available to common shareholders is estimated income minus estimated expenses.

Estimated earnings per share (EPS) is that number divided by the expected number of fully diluted shares outstanding. Earnings and earnings per share forecasts are often considered the main results of financial models because they are often used to value stocks or generate target prices for stocks.

To calculate the target price for a year, the analyst can simply look at the model to find the earnings per share figure for the next four quarters and multiply it by the assumed multiple of the price-to-earnings ratio. The expected return of the stock (excluding dividends) is the percentage difference between the target price and the current price:

Estimated return = (T − P) T where: T = target price P = current pricebegin{aligned} &text{Projected return}=frac{(TP)}{T} \ &textbf{where: }\ &T=text{target price}\ &P=text{current price}\ end{align}

Expected return=Ton(Tonphosphorus)Where:Ton=Target pricephosphorus=Current price

Now analysts have a simple basis to make investment decisions-the expected return on stocks.

Bottom line

Since the present value of stocks is inseparable from the issuer’s financial performance prospects, investors wisely create some form of financial forecast to evaluate equity investments. Examining the past in an analytical context is only half (or less) of the story. Understanding the future performance of a company’s financial statements is usually the key to equity valuation.


Share your love