Prospective Analysis: Guidelines for Forecasting Financial Statements

Appendices

Appendix A: A Summary of the Chapter

• 1. The analyst should gather from the firm, the following information:

  • a. Historical financial statements

    • i. To calculate indexes such as gross margin, operational margin, or net margin. This will be useful to double-check if deviations from the historical average are substantial.

    • ii. To calculate the indexes that reveal policies of the firm like

      • 1. Accounts receivable

      • 2. Accounts payable

      • 3. Inventories

      • 4. Payment of dividends

      • 5. Investment in fixed assets

      • 6. Investment of cash excesses

      • 7. Cash required by operations held by the firm

  • b. Information about quantities, prices, product mix, changes in the amount of fixed costs due to changes in the level of operations and the like. This information allows the analyst to

    • i. Calculate price increases

    • ii. Calculate real growth of units sold

    • iii. Analyze the behavior of some of these items and to explain any unusual behavior (i.e., very high or very low growths rates). For instance, we might examine the behavior of items such as

      • 1. Amount of sales revenues

      • 2. Labor expenses

      • 3. Overhead expenses

      • 4. Cost of goods sold

      • 5. After we analyze these items, we can compare growth with historical inflation rates and try to explain what happened. For instance, if the firm shows a growth in sales revenues of 102% and inflation rate was 5%, we have to find out the causes of the other 92.38% ((1 + 102%)/(1 + 5%) − 1 = 92.38%).

• 2. The analyst should keep in mind some ideas:

  • a. The growth in some items has three components. For instance, the growth in sales revenues comprises inflation rate, real growth (in units), and a real increase in prices.

  • b. Some variables are a measure of activity. Hence, their growth is just that: an increase in volume, in units. For instance, the growth in the number of people attending a theater. This is different from the growth in sales revenues in dollars that includes inflation, increase in units, and real increase in prices.

• 3. The analyst should gather some initial information such as

  • a. Initial amount of some items such as overhead expenses, labor costs, and the like.

  • b. Accounts receivable, accounts payable, debt balances (find out how is the schedule of repayment of those debt balances).

• 4. Information that usually is not found in the historical financial statements.

  • i. Historical selling prices of products/services not sold by the firm

  • ii. Historical price of inputs

  • iii. Historical inflation rate

  • iv. Historical minimum wage

  • v. With these data, we can estimate the real increase in selling and purchasing prices. In some countries, the increase in salary is determined by the increase in the minimum wage increase. On the basis of this information, we can estimate the real increase in prices and combine them with the expected inflation to estimate the nominal increase in prices. The estimates for some variables are available with the Central Bank.

  • vi. Interest rates charged by the bank. However, in the body of the chapter we have shown an approach to estimate rates from the financial statements.

• 5. Gather data on sales forecasts, ST, and LT budgets.

• 6. Keep this in mind while dealing with firms with multiple products:

  • a. Group products that use the same raw material. We can identify a common raw material or unit of measure to estimate their sales revenues: gallons of beer or soft drinks, juices, tons of paper, feet of lumber or wood, tons of bronze, hours invoiced, minutes or seconds invoiced, and so on. Based on the sales revenues and cost of sales from an IS estimate the selling price or the cost per unit. All this depends on the availability of information in the firm.

  • b. Use Pareto's law: 20% of the causes are responsible for 80% of the effects. Twenty percent of the products of the firm are responsible of 80% of the cost or the sales revenues.

  • c. Identify products and/or services with largest volume of sales. Measure its growth and/or the increase in prices. Use this as a proxy for the growth in units for the whole firm or the real price increase.

• 7. Identify the “driver” of the demand of the product or service. For instance, if the firm manufactures clothes for babies, the growth of the units sold will be associated with the population of 0–3 olds. If the firm manufactures wooden doors for housing, the growth would be determined by the growth of construction industry.

• 8. Avoid the use of plugs. The plug is a formula that calculates the difference between total liabilities and equity minus all the lines in the BS except cash.

• 9. With all this information, we can construct consistent and flexible financial statements.

• 10. Once we have constructed the financial statements, we can proceed to calculate cash flows for valuation purposes.

Appendix B. Example for Estimating Nominal and Real Rates

• 1. Gather historical series of

  • 1.1. Prices of inputs and products of the company or of similar products

  • 1.2. Different typical expenses of the company or from similar companies (general expenses, rent, wages, etc.)

  • 1.3. Risk-free interest rates

  • 1.4. Inflation rate of and

  • 1.5. Interest rates charged by the banks.

  Assume that we have the historical value of minimum wage and inflation rates for the last 10 years (shown in Table B1).¹⁸

• 2. Calculate the nominal rates of increase of prices. The nominal increase in minimum wages can be calculated as follows:

  Table B1 Minimum wage and inflation

  Table B2 Minimum wage and nominal increase

  To calculate the nominal increase, we use the following formulation:

  $${\rm Nominal \ increase}_t = \frac{{\rm Wage}_t}{{\rm Wage}_{t-1}}-1.$$

  For year 2005

  $${\rm Nominal \ increase} = \frac{381,500}{358,000}-1=6.56{\%}.$$

• 3. Calculate

  • 3.1. Real rates of increase in prices (deflating the nominal rates)

  • 3.2. Real rates of interest (deflating the risk-free rate of interest)

  • 3.3. Average the real rates and use that average as input for the forecast

  • 3.4. Calculate the risk premium banks charge customers (K _d) as the difference between historical Kd and the historical risk-free rates. Average these results and use the average for the forecast.

  In Our example, as we know the nominal increase and the inflation rate, we can calculate the real increase as follows.

  $$\rm{Real increase} = \frac{1+\rm{nominal increase}}{1+\rm{inflation rate}}-1.$$

  For instance, for year 2005, we have

  $$\rm{Real increase} = \frac{1+6.56{\%}}{1+4.85{\%}}-1=1.63{\%}.$$

  In the case of the minimum wage, we have

  Table B3 Nominal and real increases

  The average of the real increase since 1997 is 2.18% and this value might be used in the forecast as the real increase.

• 4. Find a reliable forecast for inflation.

  Assume the following inflation forecast:

• 5. With the real rates and the inflation forecast

  • 5.1. Calculate Nominal rates

  • 5.2. Calculate risk-free rates

  • 5.3. With the forecasted risk-free rate use CAPM to find the forecasted K _d.

  Table B4 Forecasted inflation

  The calculation of nominal increase in minimum wage increase is shown in Table B5.

  Table B5 Forecasted nominal increase in minimum wage

  We can construct the nominal increase using Fisher equation:

  Nominal increase = (1 + inflation rate) × (1 + real increase) −1.

  For year 2012,

  Nominal increase = (1 + 3.00%) × (1 + 2.18%) − 1 = 5.24%.

• 6. Apply the nominal increases to the values that you have found for today (purchase and sale prices, general expenses, etc.).

  • This is shown in Table B6.

  • The minimum wage for 2006 = 381,500.00 × (1 + 6.78%) = 407,354.79.

• 7. Estimate the real growth rate of your products or services (in units). In order to evaluate if it is reasonable, compare it with the real growth rate of the economy or the industry where the company or project is located. Another benchmark to check this increase in volume is the growth rate of the driver of the demand. For instance, the population between some given ages, the forecasted growth or the relevant industry associated with the product or service that we plan to sell, and so on.

• 8. With the rate of real increase (rate of increase in volume or units of product or service), forecast the units to sell in the future.

• 9. After these steps, you must have nominal increases in prices and expenses, nominal risk-free rates and risky interest rates, volumes of sales in units, forecasted inflation.

Table B6 Forecasted minimum wage

Appendix C: Example of Using the GDP Implicit Deflator

• 1. Find historical series for

  • 1.1. Sales revenues and costs for the firm or for a similar firm

  • 1.2. GDP at nominal and real prices or directly the series for the implicit deflator

  • 1.3. Producer price index (PPI) or its change

  Assume that we identify the sales revenues for the firm, the nominal, and real GDP:

• 2. Using the previous table, we calculate the deflator for the GDP and the inflation rate (increase in nominal prices) measured by the deflator.

  Table C2. Inflation, sales revenues and nominal increase in sales revenues

  Table C1 Nominal and constant (real) dollar GDP and sales revenues

  Table C2 Inflation, sales revenues and nominal increase in sales revenues

  To calculate the deflator, we use the following relation:

  $${\rm Deflator}_t =\frac{G\rm{DP nominal}_t }{G\rm{DP real}_{t-1} }.$$

  For year 2003

  $${\rm Deflator}_{2003} =\frac{10,983.9}{10,397.2}=1.0564.$$

  To calculate the inflation using the deflator, we use the following expression:

  $${\rm Inflation}_t =\frac{{\rm Deflator}_t }{{\rm Deflator}_{t-1} }-1.$$

  For year 2003

  $${\rm Inflation}_{2003} =\frac{1.0564}{1.0395}-1=1.63{\%}.$$

  To calculate the increase in sales revenues, we use

  $$\rm{Increase in sales revenues}_t =\frac{{\rm Sales \ revenues}_t }{{\rm Sales \ revenues}_{t-1} }-1.$$

  For 2003

  $$\rm{Increase in sales revenues}_{2003} =\frac{77,833.6}{75,530.6}-1=3.05{\%}.$$

• 3. With the increase in sales revenues and the change in the deflator, we can calculate an estimate for the “real” growth of the industry to be used as the growth for the firm. We say “real” growth because that growth has in it the real increase in prices.

  Table C3 “Real” growth calculation

  To estimate the real growth, we use

  $$\rm{"Real" growth}_t =\frac{1+\rm{increase in sales revenues}}{1+{\rm inflation}}-1$$

  For instance, for 2003 we have

  $$\rm{"Real" growth}_t =\frac{1+0.0305}{1+0.0163}-1=1.4{\%}.$$

  Calculate the average of these growth rates. In this example, it is 1.9%. Use this average as the forecast.

• 4. Forecast the inflation for the deflator for future years.

• 5. Now we have enough information to forecast the sales revenues: future inflation, estimated real growth that is composed by the growth in units and the real increase in prices. When we combine these two inputs we can calculate the increase in sales revenues and we apply it to the last historical sales revenues for 2003.

Table C4 Forecasted inflation

Increase in sales revenues _t = (1 + inflation _t ) × (1 + real growth _t ) − 1

Table C5 Forecasted nominal increase in sales and sales levels

In a similar fashion, we could use the inflation estimate from the PPI or the CPI. The advantage in using this procedure is that we do not use an increase in sales revenues based on the historical increases. For instance, in this case the average increase in sales revenues was 5.0%. This estimate is not consistent with the inflation perspectives and would overstate the increase in sales revenues for years 2004–2008. Besides, the procedure allows us to perform an enriched sensitivity analysis.

It is to be understood that the real growth is a mixture of real increase in prices and growth in units. The relationship between these two components is not additive but multiplicative, as follows:

$${\rm Real \ growth}=(1+{\rm Increase \ in \ volume}) \times (1 + {\rm Increase \ in \ real \ prices})-1$$

Knowing this relationship we could explore several combinations of increase in volume and/or increase in real prices. The combination of these two variables should always give the 1.9% on the average. For instance,

Table C6 Splitting the “real” growth between real increase in price and growth in units

Now we have a wide range of values for g or Rrp to choose from. One way to pick a proper value is to compare growth in units with the industry GDP growth or any other real measure of growth.

Appendix D: An Example of Plugs and Their Problems

In the following tables, we show a simple example of how the “plug” is calculated. In the first table, we show a simplified IS and a BS. In the subsequent tables, we also show how using arbitrary values for items other than the plug we keep the keep the BS balanced.

Table D1 Income statement and balance sheet using ST debt as plug

In the BS, we have ST debt as the plug as follows:

$${\rm ST \ debt} = {\rm Total \ assets} - {\rm LT \ debt} - {\rm Equity \ investment} - {\rm Retained \ earnings}.$$

((D1))

If we arbitrarily change any number from the right-hand side of (D1), the balancing is maintained. This means that the analyst could make any mistake and the balance sheet still balances.

In the next table, we show this for two results: one is the value of the plug itself and the other is for the checking of the balance sheet. In panel A, we have the value of the plug. We arbitrarily change cash and total fixed assets. As can be seen the balancing is maintained. In panel B, we arbitrarily change retained earnings, Cash, dividends payments and equity. The same: the checking of the balancing is kept.

Table D2 Using items from the BS

The danger of using plugs is that we could even make accounting mistakes and the model will not identify that the mistake.

Suppose we arbitrarily select cash as 5 and total fixed assets as 20. The financial statements would be

Table D3 Matching of the financial statements with plug

Notice that we have changed total assets (cash = 5 and fixed assets = 20) and hence the plug shows that balancing is correct.

Observe that using plugs the analyst lose control over what is happening in the model. For instance, when we arbitrarily changed the level of cash, the model, to keep the balancing, changed the amount of ST debt, and converted it to a higher ST investment (a more negative debt)! The IS for year 1 does not change because decisions made in year 1 (regarding debt, for instance), will affect the IS for year 2.

What happens if we have a negative debt? We can think that negative debt is investment. Will the firm receive a return equal to the cost of debt? As the interest charges in the following month will be negative, will this mean an income? To avoid these inconsistencies, the model needs some logical statements in the spreadsheet.

In the previous tables, we arbitrarily changed items from the BS itself. Now we can see that even changing items in the IS we keep celebrating the balancing of the BS. In this case we arbitrarily change interest payments, return on ST investment, net income and retained earnings.

Table D4 Using items from the IS

A conclusion from this simple example is that you can even make ANY mistake and you will never realize.

Appendix E: Constructing the Financial Model

In this appendix, we present a guide to construct the model used in the body of the chapter. We indicate the formulas that have to be utilized in the construction of the financial model. We have constructed the formulas in such a way that they can be used to construct either a part of or the complete model.

Table E1 shows the input data.

Table E1 Input data

In this section, we show intermediate tables that are required to construct the financial statements.

Table E2 Intermediate tables

This table shows the forecast for the cash budget

Table E3 Forecasted cash budget

In the next table, we show the forecast for the IS

Table E4 Forecast for the income statement

Table E5 Balance sheet

Finally we show the balance sheet in the next table