Long-Term Financial Statements Forecasting: Reinvesting Retained Earnings

Long-Term Financial Statements Forecasting: Reinvesting Retained Earnings by Sergei Vasilievich Cheremushkin* Chair of Public and Local Administration, Economic Department of the Mordovian State University named after N.P. Ogaryov Saransk, Republic of Mordovia, Russia Draft: to be further developed Suggestions and Comments Welcome September 18, 2008 © Copyright 2008 Sergei V. Cheremushkin Electronic copy available at: http://ssrn.com/abstract=1286542 Abstract: One of the most intricate issues in the long-term financial statements forecasting concerns the employment of accumulated retained earnings for a profitable firm. Reinvesting retained earnings is a strategic choice of far-reaching consequences. Actually, optimistic forecast may imply exponential growth of income through reinvestment of retain earnings in business. If the retained earnings are kept as cash, that will result in decreasing return on capital, not to mention this to be unrealistic scenario. In a growing market the firm usually expands its business, acquiring new long-lived assets, increasing capacity and sales. So, the main task in the accurate forecast is to find the reliable relationship between capital expenditures and sales, taking proper account of operating margin of sales, generated by new assets. The basic forecast, given the fixed or even neutral (only replacement of amortizable assets) investment policy, usually consists in finding the additional funds needed or excess cash amount. The case of negative cash balance is rather uninteresting. It requires the managers to take financial policy decisions to raise money needed. The implications of those decisions, assuming lack of changes in investment policy and capacity, are regular interest payments. However, the case of positive cash balance requires managers to decide how to place the excess cash. There are several possible ways to effectively use cash: share buyback, dividends, new investments in long-lived assets, and as a result in expanding business, investments in marketable securities or financial assets, investment property. The most complicated and the most realistic case is new investments in longlived assets and strategic decisions. There may be alternatives - the firm may invest to increase sales of existing product or products or it may invest in developing new product(s). The paper considers different approaches to the problem in order to construct adequate financial model. Keywords: financial statements forecasting, retained earnings, plugs, firm's valuation, financial modeling, business planning, additional funds needed, excess cash, reinvestments, sensitivity analysis, Monte-Carlo simulation, probabilistic analysis . JEL Classifications: D61, G31, H43, M40, M41, M46, D92, E22, E3 Working Paper Series New York: Social Science Electronic Publishing, Inc. *E-mail address: [email protected] Electronic copy available at: http://ssrn.com/abstract=1286542 Introduction Financial statements forecasting either in a full-scale or in a reduced form is an indispensable tool for estimation both financial and non-financial activities. The financial statements forecasts are often used for different financial applications: capital budgeting, equity valuation, financial strategy development, financing decisions, value-based management etc. For instance, the business valuation inputs are often broken down into two categories: the nearest predictable future variables (invested capital, cash flows, growth rate) and terminal market mean reverted variables (fading rate or terminal growth rate, terminal cash flow perpetuity etc.). The evaluation of business plans and investment projects also benefits considerably from the financial statements forecasts, since the without and with project comparisons are possible only when full-scale financial forecasting is implemented. Even the commercial business planning software lacks for the needed functions to fulfill the financial statements forecasting properly. Particularly it ignores the necessity to make assumptions and determine policies for the without project scenario. Sometimes (as is in the Russian business planning software Project Expert, which is announced to be comply with the UNIDO guidelines on the industrial feasibility study) it just copies the last year‟s figures for the future and the projects figures are simply added to the last year‟s statements to produce future financial estimates and analysis. Such an analysis cannot be correct in principle. The financial statement is just a snapshot of a moving object (the firm) and the figures in the financial statements will move mechanically and cannot stay put in the future. The spreadsheet modeling is much flexible and powerful tool that allows to work out customizable models. The DCF valuation also requires the financial statements analysis, because cash flows are hardly predictable. The best thing to do is to scrutinize the historic financial statements, work out the prospective statement of financial position, the prospective income statement and eventually to calculate the statement of cash flows, which may be used for DCF valuation. Another alternative is the Residual Income Valuation, which is also call for detailed prospective information. Velez-Pareja and Tham (2008) noted that “for most firms it is vital to have a financial model that allows management to control value creation”. Since value is estimation of future benefits, the financial forecasting is a powerful alternative to a simplified value driver tree approach, which is mechanical and inflexible. The financial model, being all-inclusive prospective information, provides integrity and allows sensitivity analysis and probabilistic simulation. The DCF valuation models or relative valuation models may be easily built over to connect the financial figures with the stock market. Financial statements forecasting may be also of great value for non-financial activities outcomes and impacts appraising and evaluation. For example, the valuation of brands, knowledge, IT-investments, customer relationships, employee trainings are regarded as extremely hard to quantify. However they eventually pursue financial outcomes. Financial impacts of different organizational activities have a limited set of basic shapes:  increase in incomes and receipts;  reduction in operating costs and cash outflows;  reduction in overhead expenses;  capital outlays savings;  increase in generating capacity due to various improvements (labor productivity, resource saving);  increase in capital turnover;  decrease in the non-cash working capital (inventory, accounts payable etc.);  decrease in cost of capital (because of cheap financing);  decrease in uncertainty and risks;  increase in the life of business. There may be different driver for these changes in the firm‟s financial variables. The clue to estimation of non-financial activities, intangible assets is to identify their links to above-mentioned financial outputs. It may require a long logical chain, but it should be useful to notice that such activities often lead to simultaneous or successive changes in different financial outputs. And only the financial statements forecasting can help to determine their interaction. For example, the investment in customer relationships may lead to increase in commercial expenses, inventory and overhead expenses and to increase in current and remote incomes, decrease in risks of cash receipts from customers, may also have Electronic copy available at: http://ssrn.com/abstract=1286542 other consequences. The valuation of this investment should involve interaction with other financial variables. One of the most intricate issues in the long-term financial statements forecasting concerns the employment of accumulated retained earnings for a profitable firm. Reinvesting retained earnings is a strategic choice of far-reaching consequences. It is directly connected to the earnings growth rate, which is a key input in all valuation models. Actually, optimistic forecast may imply exponential growth of income through reinvestment of retain earnings in business. The small firms often develop swiftly and in fact demonstrate the exponential growth in sales and earnings. The large and huge firms meet much modest investment opportunities and cannot grow at high rates. So they cannot effectively utilize internally generated cash and should carefully consider the retention/distribution option to maximize shareholders‟ wealth. If the retained earnings are kept as cash, that will result in decreasing return on capital, not to mention this to be unrealistic scenario. In a growing market the firm usually expands its business, acquiring new long-lived assets, increasing capacity and sales. So, the main task in the accurate forecast is to find the reliable relationship between capital expenditures and sales, taking proper account of operating margin of sales, generated by new assets. Since long-lived assets are usually highly expensive, the company has to save up money during several periods until it reaches the amount needed to acquire new plant and equipment. The other way is to address to external financing. The problem of lumpy assets was studied by Ehrhardt and Brigham (2006). They noted that fixed assets may perform at different capacity and that the step function is usually appropriate to link fixed assets and sales. In practice the easiest way to forecast the necessary amount of fixed assets to generate given volume of sales is to consider the capacity and the composition of fixed assets. The step function may be used only for single product sales or for fixed set of multiple products, and such an assumption is unlikely to hold in reality. Another problem arises because of inflation. Since fixed assets are accounted for at historic cost and sales are recognized at current prices, the ratio Fixed Assets/Sales changes. The basic forecast, given the fixed or even neutral (only replacement of amortizable assets) investment policy, usually consists in finding the additional funds needed or excess cash amount. The case of negative cash balance is rather uninteresting. It requires the managers to take financial policy decisions to raise money needed. The implications of those decisions, assuming lack of changes in investment policy and capacity, are regular interest payments. However, the case of positive cash balance requires managers to decide how to place the excess cash. There are several possible ways to effectively use cash: share buyback, dividends, new investments in long-lived assets, and as a result in expanding business, investments in marketable securities or financial assets, investment property. The most complicated and the most realistic case is new investments in long-lived assets and strategic decisions. There may be alternatives – the firm may invest to increase sales of existing product or products or it may invest in developing new product(s). So there may be three approaches to the financial forecasting: the sales-driven approach (suitable for mature firms), the capital-driven approach (convenient for high growth and emerging firms) and the mixed approach (partly sales-driven and partly capital-driven and appropriate for both mature and developing firms with investment in new product lines and businesses). The sales-driven approach is prevalent in financial research, while the other two approaches were disregarded yet. This paper intends to fill that gap. It also considers some sales-driven forecasting nuances that may be helpful in different financial applications. Financial Forecasting Basics The financial statements forecasting involve numerous and complex intertwined calculations. This worsens the readability of the whole model and its separate components. So the design of the financial model is not the last thing to pay attention to. The model design concerns the two primary points. The first one is the convenient location of the assumptions, forecasts and modules within the spreadsheet or worksheet. There are several possible layout designs that improve understanding of the model:  semantic blocks by sheets allocation;  rows grouping within one sheet;  hypertext references between blocks and sheets;  successive logical blocks with interlinks. The rows grouping design is illustrated in figure 1. Figure 1. Grouped rows layout design The most convenient approach to work out a model is to use a single sheet layout to allow quick access to formulas and data. When the model is accomplished the block may be moved to other sheets (by dragging the selection with the pushed “Alt” key in Excel to move the formulas without changing the references to cells). The major building blocks of the financial model usually include:  user inputs (fields should be accompanied by explanations and data validation – consistency checking and error alerts);  calculations for reasoning the inputs (space should be reserved);  user assumptions, grouped by categories;  primary forecast statements;  intermediary tables;  notes to the assumptions and calculations;  modeling results;  graphs;  summary (most significant figures calculated, such as ROI, NPV etc.). Highlights (color selections, signs, formatting etc.) are also desirable to attract attention to key points in the model. These blocks may be further divided in order to provide intuitive and convenient interface for changing the model inputs and to conceive the model results and logic. The nature of the financial forecasts is that the assumptions are ubiquitous. So they should be carefully substantiated and explained to the user. In financial statements forecasting all items may be broken down into three major classes: 1) automatic, standalone items, whose dynamics is fully or partially beyond manager‟s control (for example, sales, interest rates, prices of resources etc.); 2) policies-driven items, which are linked to accepted rules and practices (accounts payable, account receivable, inventory turnover, cash requirements, dividend distribution, debt extinguishment and so on); 3) discretionary items, which are constantly revised or require special managerial decisions for every period. Direct inputs are reliable data; assumptions involve some degree of guess, while policies require the fixation of activities, which in reality may be flexible and changeable. Policies-driven items may follow a sophisticated scheme under the complex set of math and logical rules. Surely, the policies may be changed, but that will be the question of scenarios and discretional decision, whose implications should be considered separately. Discretionary Policies Table should be used to customize the financial model. Policies may be relaxed through predetermined patterns or customization block on a period-byperiod basis. The policies may be classified as explicit and implicit. Switches are desirable to test different scenarios. Distinguishing forecasting purposes substantially favors the consistency of model as well. Theory tells the difference between exploratory and normative (target) forecasting. Exploratory forecasting usually reduces to extrapolation of existent tendencies. It answers the question about what will be with the entity without changing its policies and management intervention. Normative forecasting pursued predetermined target and answers whether the chosen policies will achieve the desired outcomes or searches the ways, scenarios to achieve desired outcomes. So the normative forecasting in contrast to exploratory forecasting supposes the active role and intervention of the management in future events. Financial statement forecasting is generally intended as normative, especially if it is concerns strategy development. But it may be implemented only in a two-level exercise. The first level is the exploratory basic forecast that makes clear the future under the constant policies adopted in an organization. And the second level investigates changes in policies and decisions that may be jointly described as strategy scenario. Changes in policies or acceptance of economic projects are also a part of discretionary matter. The “with and without project (strategy)” comparison may be useful to evaluate them. The difference in financial outcomes, such as ROCE, ROE, Fundamental Wealth Added1 etc. reveals the expediency and the effectiveness of discretionary measures. Sales-driven forecast This type of forecast is seemed to be quite easy, but it often involves different complications. The primary financial driver is current sales and sales growth rate. An alternative approach addresses the life I use the term “Fundamental Wealth Added” to distinguish between the market value, which, I believe, contains a significant speculative component and the economic value, established by the rational assessment of the firm‟s performance. It may be expressed as an incremental project NPV or APV. One should also distinguish between wealth added and value added. Wealth as a point indicator is usually measured as current value, but change in value doesn‟t represent change in wealth, because value increases with time not changing owner‟s wealth. 1 cycle and technological considerations when forecasting the sales pattern for a long-term horizon. In effect, there may be two types of future sales estimates: the expected values or most likely values (under the normal distribution assumption these values coincide). After the sales are determined the next step is to find out the profitability ratios and the ratios of assets to sales. The regression analysis may also be used and is preferable if there are enough data points and the regression‟s goodness-of-fit is well. The common problem in forecasting is that the statistics is used to generate out-of-sample estimates, whereas the statistical results are valid only for in-sample ranges. Sometimes the regression between sales and long-lived assets (R-square) is strong enough to be sure to hold in future. However, this regression may also be week. This also comes true for net PPE/Sales ratio, the validity of which may be tested by means of the standard deviation from the sample average. There is no one for all cases approach to follow. The analysis must be flexible and consider additional issues to find the steady-state estimates. The other problem concerns the use of nominal net PPE/Sales ratio vs. more relevant real net PPE/Sales ratio. It‟s known that the PPE is measured at historic cost and the current book value often differ considerably from the true value of invested capital in the present value of money. So the inflation adjustment is preferable. It should also be noticed that the price indexes for sales and for asset groups may be quite different. This may be illustrated with the following example. The Russian oil-company Lukoil in 2002-2007 demonstrated the ratios shown in figures 2-6. 35000 30000 Net PPE 25000 y = 0,268x + 7354 R² = ,9 20000 15000 10000 5000 0 0 20000 40000 60000 80000 100000 Nominal Sales Figure 2. Net PPE/Sale for Russian Oil Company “Lukoil” 60000 50000 Gross PPE 40000 y = 0,311x + 21432 R² = ,9 30000 20000 10000 0 0 20000 40000 60000 Nominal Sales 80000 100000 Figure 3. Gross PPE/Sale for Russian Oil Company “Lukoil” 2,0000 1,8000 1,6000 1,4000 1,2000 1,0000 Net PPE/Revenue Ratio 0,8000 Gross PPE/Revenue Ratio 0,6000 0,4000 0,2000 0,0000 2002 2003 2004 2005 2006 2007 Figure 4. Comparing Gross PPE/Sale and Net PPE/Sales Ratios approaches for Russian Oil Company “Lukoil” The nominal Gross PPE/Sales ratio approach produces little improvements. However one may easily notice that the oil market was driven mostly by the oil price in the fitting period. So the good forecast should consider the real values of sales and assets to understand actual generating capacity. 160 140 100 <OPEN> 80 <HIGH> 60 <LOW> 40 <CLOSE> 20 Figure 5. Brent Prices in 1991-2008 Oct-08 Nov-07 Dec-06 Jan-06 Feb-05 Apr-03 Mar-04 May-02 Jun-01 Jul-00 Aug-99 Sep-98 Oct-97 Nov-96 Dec-95 Jan-95 Feb-94 Mar-93 Apr-92 0 May-91 $ per barrel 120 0,4 y = 0,008x + 0,290 R² = , 0,35 0,3 0,25 0,2 Net PPE/Real Sales Ratio y = 0,017x + 0,111 R² = , 0,15 Gross PPE/Real Sales Ratio 0,1 0,05 0 2002 2003 2004 2005 2006 2007 Figure 6. Comparing Gross PPE/Sale in units and Net PPE/Sales in units Ratios approaches for Russian Oil Company “Lukoil” The transition to Sales in units provided some improvements and decreased the standard deviation of ratio considerably. The resulting Ratio may be estimated at the 2004 value, since the last two years the firm invested intensively (Figure 7). This is only an example, but there is no sense in empirical research, the situations are very specific. The internal reasons should be studied instead. 10 000 9 000 8 000 7 000 6 000 5 000 4 000 3 000 2 000 1 000 0 2000 2001 2002 2003 2004 2005 2006 2007 Figure 7. CapEx of Russian Oil Company “Lukoil” The same reasoning also extends to working capital/sales ratio. It may depend on the both the policies and situational considerations. It‟s worth mentioning that the inventories consist of different items and include not only the raw materials supplies, but also works in progress and finished goods. Every of these items should be scrutinized separately for making better forecasts. For example, the finished goods in stock would decrease, if the company arranges its sales channels, or on the contrary would tend to increase as the market approaches to maturity. Another problem concerns the capacity utilization. According to Brigham the firm not operating at full capacity could achieve a greater level of production from fixed assets and only then the additional assets will be required. To solve the problem Brigham suggests calculate firstly the ratio of current fixed assets to full capacity sales: Ratio of Current Fixed Assets to Full Capacity Sales = Current Fixed Assets/(Current Sales/Percentage of Capacity). Then this ratio is suggested to be applied to the forecasted sales in order to determine the required level of Fixed Assets. The step function of fixed assets to sales may be realized in Excel by means of the roundup function. The additional inputs are the fixed assets lumpy sum that is the undividable unit in money terms and the fixed assets capacity. The calculations are quite easy and may be a part of more complicated models of sales-driven forecasts. Figure 8. Illustration of the step function for fixed assets to sales in Excel Figure 9.Formulas for realization of the step function for fixed assets in Excel The sales-driven forecasts may be deliberated to reflect different product-lines and various productivities of generating units. Lundholm and McVay (2004) suggested a mixed sales-driven model for the retail firms based on the publicly available information. They identified stores as sales-generating unit and distinguished between three classes of stores: mature existing stores (they name them „mid‟ stores), closing in current year stores (dead stores) and new stores. They assigned different sales rates for each kind of stores. However the reinvestments were beyond the scope of the study. Capital-driven forecast The sales-driven forecasting is preoccupied solely with the capital requirements to support sales growth. There are no considerations on what to do with the free cash flows earned. However the management‟s routinely task is to find investment opportunities, which to engage invested capital in. Both the sales-driven and capital-driven approaches concern the problems of single product forecasting and multiple products forecasting. These problems should be examined before we continue our discussion of the capital-driven forecasting techniques. The basic forecast, given the fixed or even neutral (only replacement of amortizable assets) investment policy, usually consists in finding the additional funds needed or excess cash amount. The case of negative cash balance is rather uninteresting. It requires the managers to take financial policy decisions to raise money needed. The implications of those decisions, assuming lack of changes in investment policy and capacity, are regular interest payments. However, the case of positive cash balance requires managers to decide how to place the excess cash. There are several possible ways to effectively use cash: share buyback, dividends, new investments in long-lived assets, and as a result in expanding business, investments in marketable securities or financial assets, investment property. The most complicated and the most realistic case is new investments in long-lived assets and strategic decisions. There may be alternatives - the firm may invest to increase sales of existing product or products or it may invest in developing new product(s). The forecasting of the multiple products firm involves a lot of complexities. The approach depends on the availability of information on prices, volumes, costs of production and invested capital per product group. Every product group often sets special requirement for PPE, technological costs etc. Sometimes products information may be avoided and the whole product portfolio is treated as a common input into the model. It might be reasonable only if the product portfolio remains the structure with time. However, it‟s better to treat each of the product groups separately if possible. The easiest case is when the PPE and costs may be attributed to each product group to find financial performance measures per group. Sometimes the same technology is used to produce homogeneous products, which might have different prices and selling expenses, but remain the same cost of sales per unit. This scenario might also be applied to treat different geographical segments for the same product. The most troublesome case is when different products are produces by means of the same PPE. It may be impossible to allocate the PPE between product groups, while the return on investment measures will be different for products because of the various costs of sales and prices. Direct costing or other internal cost management and measurement techniques would be helpful. In the case of new products there are usually additional investments, new costs and new revenues estimated. But the existing capacities and resources are often involved in new projects and they should be designated in order to highlight accurate financial performance indicators. It seems that new projects often engage underutilized resources and idle capital, raising the efficiency of the firm. The special case is when the product cannot be precisely identified. The substitutes might be use to express the indefinite services or products as a typical measurable units. For example, the consulting firm may apply orders or man hours to measure its sales. The groups based on different hours per customer or different costs and revenues profiles might be identified as well. The simple capital-driven forecast (applied to the case of the single product group) assumes that the sales are a function of the gross fixed assets. However the additional fixed assets also depend on the sales and the amount of retained earning. The scenario of capital-driven earnings growth is especially interesting for the equity financing and venture financing schemes, when the fund is established in order to increase value of the businesses portfolio considerably or to accumulate earning to repurchase the share by management. There‟s an expectation that the principals involved would reinvest overtime, increasing the retained earnings and that this would be a basis for adjusting the percentage ownership in the initiative2. There should be little hindrance to sales and potentially unlimited demand. The major growth determinant would be the Return on Invested Capital. External investment may be also considered to stimulate growth under optimistic scenario. The spreadsheet model should facilitate this analysis. The simple capital-driven forecast is effective for emerging firm with potentially unlimited market, but only for finite prediction horizons so that there is no anxiety about the market saturation and competition for the market share with other entities. The multiple products capital-driven forecast involves additional considerations about the product lines and limitations to grow sales of specific product groups. The limitations may be imposed to either 2 I would like to thank Dave Balsillie (Manager, DSS Strategy & Research, The Nielsen Company) for interest and helpful comments. growth rate per product or to absolute amounts of sales per product by year. The remaining capital would be invested in other products or reinvested in financial assets. The management decisions are mandatory under such a scenario. So the multiple products capital-driven forecast reduces to the restricted simple capital-driven forecasts for every product or to mixed sales-driven and capital-driven forecasts for different products. Either case requires a lot of managerial considerations. When forecasting the fixed assets the main difficulty concerns the proper accounting for depreciation. In fact the acquired long-lived assets have different expected lives and every asset involves periodic depreciation expense, accumulated during its life. However at the end of the asset‟s life its book value vanishes or the asset is written of if realized at the salvage value and the accumulated depreciation is written off as well along with the used up asset. The above-mentioned pattern may be maintained when forecasting the finite investment project which is liquidated at the end of the life. But for the ongoing enterprise the long-lived assets are usually replaced permanently or periodically from the depreciation allowance. Firstly, it should be noted that the capacity of long-lived assets usually doesn‟t decrease because of depreciation. For example, the brick factory or diary farm keeps steady output in spite of asset ageing. So it may be reasonable to use the ratio of gross fixed assets to real revenue instead of the commonly used ratio of book fixed assets to nominal revenue. The gross fixed assets are calculated as book fixed assets plus accumulated depreciation. A historic inflation adjustment according to the assets average age may be valuable. The capacity disappears at once when the fixed asset is written off. This means that one should carefully consider the depreciation schedule in order to forecast gross and book fixed assets in place. A typical assumption is that the depreciation reserve serves to preserve the nondecreasing book level of long-lived assets and that the generating capacity of those assets is constant. Under this assumption there is no writing off the assets, the depreciation reserve is not available for any other purposes and the increase in fixed assets is always financed with reinvested earnings or external funds raised. The retirement of assets may also useful in the simpler net PPE/Sales approach. Copeland, Koller and Murrin (2000) when valuing Heineken corporation suggested the assumption about the steady-state amount of net PPE it takes to generate each dollar of sales. They also assumed that fixed assets are used until they are fully depreciated and that they have no material scrap value. So the amount of assets retired from gross plant, property, and equipment each year would equal the amount of the reduction in accumulated depreciation. They assumed it to be equal 1% (they report no the details of this assumption). This might be easy but hardly adequate for the gross PPE/Revenue approach, since it distorts generating capacity of the assets considerably. The possible Excel algorithm is shown below. The depreciation is accrued according to historic assets value and the assets replaced will be insufficient to restore the capacity on the account of inflation. And this will eventually lead to slightly declining capacity assuming the assets efficiency is unchanged. Actually the gross assets base should be used to predict the generating capacity and the depreciation reserve will be used to increase capacity, while at the moment of writing off the assets the capacity will dramatically decrease, if the firm will not replace it with new CapEx. Peter Jennergen in his “A Tutorial on the McKinsey Model for Valuation of Companies” (2002) assumes that real revenues are related to real gross PPE: “The assumption that revenues are related to gross rather than net PPE implies that each piece of PPE is 100% productive until the end of its economic life. At that point in time, it suddenly ceases to function and is retired. This seems like a somewhat more intuitive hypothesis than the alternative, relating revenues to net PPE, since that would mean that the productivity of each piece of PPE is proportional to its remaining economic life.” The gross PPE/Sale ratio approach requires some complexities to resolve. The adjustment for inflation should be separate for PPE and for revenue, since the nominal revenue is determined by changes in the prices for products sold and the nominal gross PPE is influenced with the prices for means of production in this or that particular industry. For example, we may consider the relationship between revenue and PPE for Russian oil company Lukoil in 2002-2007. There are no necessary improvements in the fitting line for real quantities of PPE and sales, because of different factors such as rapid technological changes, functional depreciation of PPE, the several years lag between investments in new long-lived assets and generated sales (it‟s evident that investments in new oil wells, mining and sales come one after another with quite a long lag), difficulties of adjusting historic PPE cost for inflation and so on. The real revenue is always preferable, since the fixed assets are employed in production and they cannot influence price policies. For simple cases the sales in units forecast and the ratio of gross fixed assets to sales in units is used to arrive at real sales. However the more widespread case of a multiproduct firm involves additional complications, especially if there are hundreds or thousands items in the product portfolio. For simplification those products should be grouped into categories according to Pareto 80% to 20% principle. The fixed assets should be grouped according to their relationship to the product categories if possible. The second-best alternative approach may be applied. It is the specification of the intermediary tables with cost of sales, commercial expenditures, operating margins, capital employed in production of the new products assuming they have similar features at the stage of the lifecycle. Another alternative is the individual projects approach, which may require the deployment of retained earnings along with the external financing. The inputs and parameters (time-varying operating margins, COGS, capital employed etc.) of the projects should be aggregated and introduces into the intermediary table and then summary will be used in the primary statements. Fixed Assets at the beg. of year t Sales at year t Retained Earnings at year t Excess Cash at year t New Fixed Assets at the end of year t Sales at year t + 1 Working Assets Current Liabilities Figure 10.Circulation under the Capital-Driven and Gross PPE assumptions model The capital expenditures leads to increase in sales, the increase in sales entails addition in working capital at the beginning of the period and changes other proportional items. The chain of changes extends to debt financing decisions (both long-term and short-term). It would be the acme of optimist to believe in exponential growth of revenue and income. Sometimes the reasonable assumption is conservation of fixed return on investment pattern over time. This assumption is very popular among analysts, applying simplified models. The fading return on investment scheme is often considered more realistic. But in effect the return on investment is an output, not an input, and assumptions about it are very questionable. The investment in new assets Multiple investments scenario induces some trouble to calculate periodic depreciation expense as the investments are made in various points of time, may have different lifetimes and they inevitably change the depreciation schedule of the whole firm. In order to facilitate calculations the modeler may realize the following simple algorithm. First, one makes the discretionary decision table to input the cost of the long-lived asset, date of recognition of that asset in the balance sheet and its useful life for every new asset. The date of writing off the asset and the depreciation expense may be easily calculated then. The estimated “gross assets/sales” or “net assets/sales” may be designated as well. The next step is to find depreciation for every period for every asset and to calculate the sum total. To do that one needs to apply logical operators. To facilitate the model development and further revisions the following algorithm for long-lived assets treatment may be appropriate. Firstly, the modeler should the inputs box like one shown in figure 10. This example id designed to illustrate only treatment of new investments; the total values of these dialogs may be added to the existing assets values. It may also be used in order to develop the customizable dialogs to evaluate financial policies. The number of assets should be reserved so not to retreat the model. Figure 10. Long-Lived Assets Description at input (all needed data to determine depreciation schedule, gross and net values etc.) The next step is check the current date and the dates of recognition and retirement of assets from the cells in figure 10. The depreciation expense would equal nil, if the current date is outside the range of time when the asset is on the books. The detailed algorithm is shown in Table 1. There is need to work out only the left top formula, other cells in each of the blocks are filled by copying these cells. The summary cell accumulates the numbers for all of the assets and may be referenced by other intermediary tables or directly by the balance sheet items. The depreciation expense algorithm for current year: If and(current year>year of date of recognition;current year<year of date of retirement) then Annual depreciation of the Asset else If current year=year of date of recognition then Annual depreciation of the Asset*(The first day of the next year after year of recognition – Day of Recognition)/365 Else If Current year=year of retirement then Annual depreciation of the Asset*(date of retirement – the first day of the day of retirement)/365 Else 0 End If End If End If Table 1. Calculations of depreciation, gross assets and book values on individual investments A B C D E 14 Depreciation 2008 2009 =IF(AND(YEAR($G3)<D$14;YE =IF(AND(YEAR($G3)<E$14;YEAR($H3 AR($H3)>D$14);$F3;IF(YEAR($ )>E$14);$F3;IF(YEAR($G3)=E$14;$F3*( G3)=D$14;$F3*(DATE(YEAR($ DATE(YEAR($G3)+1;MONTH(1);DAY( G3)+1;MONTH(1);DAY(1))1))$G3)/365;IF(YEAR($H3)=D$14;$ $G3)/365;IF(YEAR($H3)=E$14;$F3*($H F3*($H33DATE(YEAR($H3);MONTH(1); DATE(YEAR($H3);MONTH(1);DAY(1)) DAY(1)))/365;0))) )/365;0))) 15 Asset 1 =IF(AND(YEAR($G4)<D$14;YE =IF(AND(YEAR($G4)<E$14;YEAR($H4 AR($H4)>D$14);$F4;IF(YEAR($ )>E$14);$F4;IF(YEAR($G4)=E$14;$F4*( G4)=D$14;$F4*(DATE(YEAR($ DATE(YEAR($G4)+1;MONTH(1);DAY( G4)+1;MONTH(1);DAY(1))1))$G4)/365;IF(YEAR($H4)=D$14;$ $G4)/365;IF(YEAR($H4)=E$14;$F4*($H F4*($H44DATE(YEAR($H4);MONTH(1); DATE(YEAR($H4);MONTH(1);DAY(1)) DAY(1)))/365;0))) )/365;0))) 16 Asset 2 … … … … 29 Asset 15 … … 30 Total =SUM(D15:D29) =SUM(E15:E29) 31 Accumulated 32 Depreciation 2008 2009 =IF(OR(E15=0;$H3<=DATE(E$32;12;31 ));0;D33+E15) 33 Asset 1 =D15 =IF(OR(E16=0;$H4<=DATE(E$32;12;31 ));0;D34+E16) 34 Asset 2 =D16 … … … … =IF(OR(E29=0;$H16<=DATE(E$32;12;3 47 Asset 15 =D29 1));0;D47+E29) 48 Total =SUM(D33:D47) =SUM(E33:E47) 49 Gross assets at the beginning of the period 2008 50 2009 =IF(AND(D$50>YEAR($G3);D$ =IF(AND(E$50>YEAR($G3);E$50<=YE 51 Asset 1 50<=YEAR($H3));$D3;0) AR($H3));$D3;0) =IF(AND(D$50>YEAR($G4);D$ =IF(AND(E$50>YEAR($G4);E$50<=YE 52 Asset 2 50<=YEAR($H4));$D4;0) AR($H4));$D4;0) =IF(AND(D$50>YEAR($G5);D$ =IF(AND(E$50>YEAR($G5);E$50<=YE 50<=YEAR($H5));$D5;0) AR($H5));$D5;0) 53 Asset 3 … … … … 66 Total =SUM(D51:D65) =SUM(E51:E65) 67 68 69 Book Value Total 2008 =D66 2009 =E66-D48 The results of the calculations above are presented in Appendix 1. The Constraint of the Capital-driven Forecasting Model The market usually imposes restrictions to increase in sales. Additional sales will either lead to decrease in price (and as implications to lower operating margin), or require serious investments in promotion. There is need in detailed scenarios of price-volume pattern over time. Generally the firm is exposed to budget constraints. For example, the dairy farm may be designed for five hundreds head of cattle and if the firm plans to expand its dairy business it has to construct a new farm that require a great lump-sum of investment. So the incremental investments may follow a stepped graph, rather than a continuous smoothed line. The case of developing and launching new products requires additional calculations. The R&D budget should be developed to estimate possible costs of product innovations. The next thing to do is calculating cost of sales for each of new products. Coarse oversimplifications may be applied, but they decrease the accuracy of forecast. Actually, the new products development is linked to decision-making and requires a lot of managerial considerations. This way of using available cash reserves relates to discretional, normative forecasting. Investment in Financial Assets and the problem of Interest Expense and Interest Gains Forecasting Vélez-Pareja (2008) attacks the problem of circularity in the spreadsheet modeling, which may cause calculation error, consumes much time and hinders to Monte-Carlo simulation of the forecasted statements. He refuted the conventional plugs approach as error-prone and as giving rise to harmful circularity and proposed a Cash Budget approach, which allow realize double entry bookkeeping and check line for the balance sheet to find out involuntary mistakes in the design of the primary or intermediary tables. However Vélez-Pareja assumes some constraints that he considers unavoidable to solve for the circularity problem. For example, the textbooks recommend apply interest expenses payable on debt and interest receivable on the financial assets based on the average of the debt at the beginning and at the end of the year. This implies an assumption that debt or financial assets were added or subtracted evenly throughout the year. Such an approach causes circularity, which Brigham calls financing feedback. Vélez-Pareja suggests the simple approach to avoid it by using the end of period convention for new loans and principle and interest payments. This study suggests a little sophisticated procedure to solve the circularity when applying the interest expense based on the average of the debt at the beginning and at the end of the year. This procedure also rests on the cash balance intermediary table, which combines the additional funds needed and no-circularity approach and requires some assumptions about current and long-term debt policies along with the reinvestment policies. For simplicity we assume that 1) there is no division on short-term and long-term debt, 2) the debt is repaid as soon as possible and 3) there is no reinvestment of free cash (it‟s just accumulated on the cash account). The second assumption may be reasonable for investment projects planning and for specific debt policies. The model may be deliberated, but it should be done carefully to provide consistency in the order of financing different assets. The formulae and the design of the model may be seen in appendix 2. To equilibrate the model with interest expense based on the average of the debt at the beginning and at the end of the year one should firstly calculate the minimum cash reserve needed to finance operations and the cash available before debt changes. The debt amount at the end of the period is calculated as the minimum of the debt on the beginning of the period and the cash available before debt changes minus minimum cash reserve, since if the cash before debt was greater then the minimum cash reserve, it might be used to repay debt. However under the interest expense based on the average of the debt at the beginning and at the end of the year approach the circularity arises when trying to calculate cash available before debt, because the debt at the end of the period influences the interest expense and the interest expense influences the retained earnings that must be included in the cash available before debt changes. To resolve this secularity, the following equations should be considered. Notice that cash before debt changes may be expressed as: BDCash  BRECash  BDRE  REDE , (1) where BDCash – cash available before debt changes; BRECash – Cash without (before) Retained Earnings; BDRE – Retained Earnings before Debt Effects (interest expense); REDE – Retained Earnings Debt Effect (effect of interest expense). Then we may set that the  NOPAT  1  DPR  if Net income>0 BDRE   . NOPAT if Net income<0  (2) where DPR – dividend payout ration (as a proportion to net income). And  1 i  Dt 1  Dt  1  T 1  DPR  if Net income>0  REDE   2 . 1 i D  Dt  1  T  if Net income<0   2  t 1 Notice that the (3) Dt 1  Dt  Dt 1   Dt 1  D   2 Dt 1  D. At first it is convenient to establish a net income condition checking: BDRE  1 i  2 Dt 1  D  1  T   0. 2 (4) Then the change in debt (ΔD) may be expressed   BRECash  NOPAT 1  DPR    max( Dt 1;    if Net income>0   MinReserve  1 i  2 Dt 1  D  1  T 1  DPR    (5) 2   . D    BRECash  NOPAT  MinReserve    max( ; D if Net income<0     t 1 1 i  2 D  D  1  T     t  1   2  Hence D  max      D ;  t 1       BRECash  NOPAT 1  DPR          MinReserve i D 1 T 1 DPR      t 1 if Net income>0   1 1  i 1  T 1  DPR   2 .   BRECash  NOPAT      MinReserve  i  Dt 1 1  T    if Net income<0  1  1 i 1  T   2  (6) The further calculations to find interest expense, cash after debt are quite easy and are illustrated in appendix 2. The prospective balance sheet (statement of financial position) is balanced jointly through the cash item (no financial assets) or the financial investments item in the assets side and the debt items in the liabilities and equity side. The intermediary table to determine the cash balance and the use of cash is an indispensable tool for error-protected financial modeling process. The excess cash should be calculated in consecutive step-by-step procedure with the priorities set out ahead in order to develop a consistent model. The policies assumptions are unavoidable. Firstly, one should consider the links between assets and their sources of financing. Usually it‟s desirable to finance long-lived assets with the long-term debt and to finance the working assets with the equity (retained earnings) and the shot-term debt if needed. The nondebt (operating) liabilities are a free source of financing originating from the operating activities and it decreases the funds needed. So the financing requirements are determined after the non-debt liabilities in a prescribed manner. In reality the financial sources and the assets groups often diverge, but in forecasting it is reasonable to adhere to predetermined policies. The first step in the balancing the balance sheet is to designate the cash reserve as the minimum cash required to maintain the firm‟s liquidity and to finance operations, unforeseen costs and the like. The next step is to finds out the cash available after the minimum cash required reserve. This involves the calculation of the retained earnings and the changes in cash due to changes in non cash working assets and current liabilities. All the prospective income statement and the balance sheet (with the cash line referencing to the cash balance of the intermediary table at our current attention) should be calculated to proceed at this step. The necessary debt payments should be also considered at this step, since the short term debt raised in the previous year should be fully repaid in the current year. The order of the possible applications of cash should be assigned in advance as a policy. For example, it may be desirable to send cash available after financing working capital to repay long-term debt at first. Then the remaining cash should be used to finance long-lived assets acquisition or to form a financial assets portfolio that will generate income gains. On the other hand the raising debt should also conform to the predetermined policies. For example, if the cash after minimum cash reserve and financing non-cash working capital is negative the lacking cash should be raised as the short term debt. Given investment program, the long-term debt or additional equity may be needed. The long-term debt financing at a common rate may be established in the basic scenario with the possibility to input discretionary figures for the contributions from owners and for the long-term debt schedule. The basic scenario will indicate the additional fund needed amount to decide on the alternative sources of financing. The financing fine tune (manual adjustment) intermediary table should provide possibilities to see the excessive or deficient cash for filling the gap of the external financing needed. The user may check the basic scenario indications to ground discretionary figures. Figure 12. The Cash Account and Debt Intermediary Table to equilibrate the model (formulas are in Appendix 3) The interest gains and dividends on the financial investment are assigned as an expected return on the financial activities. The financial investments are undertaken in order to earn some interest on the temporary free cash and reserves. The free cash is put in bank deposits, is loaned or invested in shares of publicly traded companies, bonds and the like. The categories of financial assets are hardly predictable since it‟s a situational decision to choose the concrete objects of financial investing. The typical assumption is that the financial assets earn the normal market return (remember the risks involved in financial assets when valuing them). The investment property may be another application of utilization of the temporary free cash. The differentiation between the long-term and current financial investments (classifying the financial assets as hold to maturity or as available for sale, tradable securities) is also difficult, but it may be desirable in order to forecast future solvency and liquidity ratios. The algorithm may be developed to adjust the target value of the most significant of the ratios or may establish a constant proportion of the allocation of the free cash between the long-term and current financial assets. This is discretional or policy assumption. The Long-Term Debt, Short-Term Debt and Cash items are intertwined and jointly balance the statement of financial position. The Cash line is also linked to the Retained Earning and Non-Cash Working Assets and Current Liabilities (excluding portion of Short-Term Debt) items. The logical operators are indispensable to solve the equations. Figure 13. Gross Assets Approach Mixed financing forecast Usually the firm is favorable if it establishes the constant target debt to book equity ratio. The increase in equity due to retained earnings raises credibility and allows raise additional debt without harm to liquidity and solvency and at the same time increase value receiving tax savings. Maintaining constant debt to equity ratio the firm implements the expansion benefits from the tax shield and financial leverage. However this policy should be directly stated as an input to the financial model. Sometimes it goes without saying, but that might not be a good practice, since the debt policies are quite a delicate question, which should be carefully considered and grounded. Financial Statements Forecast-based Valuation considerations The simple payback period have limitations. It cannot be applied to the cash flow with changing signs. It assumes that the cash outflows are followed by consecutive cash inflows. But real projects often require supporting investments in the midst of the project lifetime and sometimes have assets retirement obligations or other events that cause cash outflows at the end of the project‟s life. So the modified payback period is needed and it may be determined on the basis of the difference between the sum total of all of the cash outflows and the accumulated positive cash flows. This algorithm embraces all possible situations and indicates the period in which the accumulated cash inflows recovers both previous and future expected cash outflows. This simple algorithm is illustrated in figure 14. Figure 14. Payback Period and Modified Payback Period simple algorithms in Excel for monthly cash flow It should be also noted that there may be two assumptions for both the payback period and modified payback period: 1) the cash flows take place at the end of the period (appropriate for monthly cash flows); 2) the cash flows are distributed evenly within the period (then above formulas must be adjusted to show the fractional part of the period; this is appropriate for long periods, such as quarter or year, when the cash flows occur on a daily basis). However sometimes the cash flows in fact take place at specific dates, and the fractional part will be redundant. The value of the project or firm should be established on rather the distributions of cash to shareholders or on free cash flows that may be distributed to shareholders. The first way is the most preferable and error-proof, as the cash distributions are well-defined and easily calculated. Managers are usually disposed to use the free cash flow model to evaluate projects. This model is much more complex and error-prone. Besides, the common way to calculate NPV is to discount net cash flows. Some cash available to distribution in a period actually may be designated by management to finance future operations or future capital expenditures, or to repay debt. In such a case the deduction of designated cash flow from the net cash flow may be appropriate. The typical mistake in business planning is discounting the operating or free cash flow for each period. This is not a correct approach, since the firm usually has to make accumulations of cash to repay large amounts of debt or to finance large portions of investments, that require the amount of cash equal to several times of operating cash flow. Imagine that the firm received $1000 operating cash flow in a period. There was no CapEx in the current period, so all the sum may be considered as Free Cash Flow. But in the next year the firm is expected to encounter a $3000 repayment of debt. The operating cash flow of the next year is estimated at $1700 and it will be insufficient to ensure solvency. If the firm distributes the current cash in the next period it will meet the necessity to attract external financing. The common mistake is to forget that in the Free Cash Flow Model the excess cash is assumed to be fully distributed to owners. However the Cash Balance and the Cash account in the Statement of Financial Position still retain this supposedly distributed cash. So, when calculating the funds needed to repay debt, they rely on the forecasted cash balance, and the discrepancy between cash distributions and cash outflows arises and the sources of financing repayment of debt drop out from consideration. Under the proper treatment the free cash flow should be diminished by the debt repayment allowances (provisions). Another way is to stick to the Free Cash Flow approach and to do provide for external financing at the date of repayment, which is usually unpractical. The most correct way is applying to the firm‟s accepted policies on the issue. In principle the DCF and DCF approaches to valuation should give the same results if we assume that the excess cash will earn required return. However the constructions diverge. At first, the FCF model assumes that the excess cash is immediately distributed to the owners and the value of cash generated in operations is preserved. On the contrary the DCF valuation model evaluates the actual distributions to the owner. But it may also contain assumptions. To forecast actual distributions one must make a lot of calculations and pass the thorough financial statements model. The major problem is seemed to be in the treatment of excess cash converted into financial investments. Those investments should earn required return to preserve the value of cash inflows. But what is the rate for discounting the interest income received from financial investments. Assuming that the cash is put into risk free deposits in banks or is converted into treasury bonds to produce risk free rate of return, the interest income flow should be properly discounted at a risk free rate of return (or at a discount rate including a risk premium to reflect the agency costs). But usually the interest income is included in the cash distributions to equity and is discounted to the moment of ultimate distribution at a cost of equity rate. In fact the cash flows should be discounted at a cost of equity until the moment of their generation by corporate assets and then, if retained in financial assets, that cash flows should be discounted at a rate, reflecting the risk of those specific cash inflows stream. In so doing the valuation will be more accurate and converge to the free cash flow valuation (since the interest income on financial assets will usually preserve the value of the retained cash). Another valuation problem arises in the choice of the cash flows period. The project‟s NPV and IRR will be very different if one assumes monthly cash flows vs. yearly cash flow. For positive-NPV projects the month-based evaluation gives much higher results than year-based forecast. Let‟s consider a simple example. Assume, that the initial cash outlay is $-4000, then during the next five years their will be constant cash inflow of $1500 in every year. The discount rate is 14%. In order to distinguish between classical Free Cash Flow to Firm Approach to NPV calculation and between Discounted Dividends NPV approach, which also encounters in publications3, we use the term DNPV (Distributed Net Present Value), which shows the expected value of the payments to firm‟s owners. Conclusions The paper has studied the issues of reinvesting retained earnings when developing financial statements forecasting models. It distinguishes the revenue-driven and capital-driven forecasts, which considerably diverge in treating the retained earnings and imply differences in balancing the statements. It stresses the Gross PPE versus Net PPE forecasting approach as more consistent and provided some tips on the implementation. The procedure was suggested for balancing the financial statements under the interest expense based on the average of the debt at the beginning and at the end of the year. This procedure is fulfilled without circulation by means of developing an intermediary table for calculation of the cash and debt accounts assuming fixed debt financing policies. The check lines are used to ensure consistency and promote earlier errors discovery. The sophisticated algorithm for balancing statements under the interest expense based on the debt at the beginning of the year was worked out, which determines the short-term and long-term debt financing policies and establishes the rules for investing excess cash in financial assets. The paper also touches some issues concerning the firm (projects) valuation. The modified payback period was substantiated and the procedure for its calculation was presented. The adjustments for the conventional Free Cash Flow were proposed. It also attracts attention to the need to apply specific discount rate for interest received on financial assets as their risk is different from that of the firm (project) operations. However the conventional procedure for DCF evaluation was to apply the sole discount rate for the net cash flow, which may combine cash flows with very different risk profiles. References For example, Stern Stewart referred EVA to correspond NPV, while it‟s proved that the RIM model is equivalent to DDM; it may be adjusted to FCF model, but such an adjustment wasn‟t intended in conventional EVA model. 3 Cigola Margherita, Mario Massari, Lorenzo Peccati, Antonio Vulcano, Laura Zanetti, 2003, “On the valuation of a growing levered firm”, Working Paper, Social Science Research Network (New York: Social Science Electronic Publishing, Inc.). Damodaran Aswath, 2008, “Growth and Value: Past growth, predicted growth and fundamental growth”, Social Science Research Network (New York: Social Science Electronic Publishing, Inc.). Lee, Chi-Wen Jevons , Laura Yue Li, Heng Yue, 2005, “Performance, Growth and Earnings Management”, Working Paper, Social Science Research Network (New York: Social Science Electronic Publishing, Inc.). Mercer, Z. Christopher, 2004, “Valuing Enterprise and Shareholder Cash Flows: The Integrated Theory of Business Valuation” (Peabody Publishing, LP). 389 p. Michael C. Ehrhardt and Eugene F. Brigham, Corporate Finance: A Focused Approach (Thomson South-Western, 2006). Sharpe, Steven A., 2002, “How Does the Market Interpret Analysts‟ Long-term Growth Forecasts?”, Working Paper, Social Science Research Network (New York: Social Science Electronic Publishing, Inc.). Stephen Ross, Randolph Westerfield, and Bradford Jordan, Fundamentals of Corporate Finance (McGraw-Hill Irwin, 2008). Velez-Pareja, Ignacio and Tham, 2008, “Joseph,Prospective Analysis: Guidelines for Forecasting Financial Statements”, Working Paper, Social Science Research Network (New York: Social Science Electronic Publishing, Inc.). Vélez–Pareja, Ignacio, 2004, “Proper Determination of the Growth Rate for Growing Perpetuities: The Growth Rate for the Terminal Value”, Social Science Research Network (New York: Social Science Electronic Publishing, Inc.). Velez-Pareja, Ignacio, 2007, “Guidelines for Forecasting Financial Statements from Historical Financial Statements for Valuation Purposes”, Working Paper, Social Science Research Network (New York: Social Science Electronic Publishing, Inc.). Velez-Pareja, Ignacio, 2008, “A Step by Step Guide to Construct a Financial Model Without Plugs and Without Circularity for Valuation Purposes”, Working Paper, Social Science Research Network (New York: Social Science Electronic Publishing, Inc.). Velez-Pareja, Ignacio, 2008, “To Plug or Not to Plug, that is the Question: No Plugs, No Circularity: A Better Way to Forecast Financial Statements”, Working Paper, Social Science Research Network (New York: Social Science Electronic Publishing, Inc.). Appendix 1. Long-Lived Assets Inputs and Processing Blocks Figure A.1. Processing multiple assets procedure Appendix 2. Balancing the Financial Statements Model under Averages of Interest Expense Approach Figure A2.1. Inputs to the Model and Prospective Statement of Financial Position Figure A2.2. Prospective Income Statement Figure A2.3. Cash Account Balancing Table (do not mingle with the Cash Flow Statement) Appendix 3. Cash Account and Debt Intermediary Table Formulas Figure A3.1