Custom Search

AN IMPROVED MODEL

Now that you have grasped the idea behind financial planning models, we can move on to a more sophisticated example.

Table 1.14 shows current (year-end 1999) financial statements for Executive Fruit Company. Judging by these figures, the company is ordinary in almost all respects. Its earnings before interest and taxes were 10 percent of sales revenue. Net income was $96,000 after payment of taxes and 10 percent interest on $400,000 of long-term debt. The company paid out two-thirds of its net income as dividends.

Next to each item on the financial statements in Table 1.14 we have entered a comment about the relationship between that variable and sales. In most cases, the comment gives the value of each item as a percentage of sales. This may be useful for forecasting purposes. For example, it would be reasonable to assume that cost of goods sold will remain at 90 percent of sales even if sales grow by 10 percent next year. Similarly, it is reasonable to assume that net working capital will remain at 10 percent of sales. On the other hand, the fact that long-term debt currently is 20 percent of sales does not mean that we should assume that this ratio will continue to hold next period. Many alternative financing plans with varying combinations of debt issues, equity issues, and dividend payouts may be considered without affecting the firm`s operations.

Now suppose that you are asked to prepare pro forma financial statements for Executive Fruit for 2000. You are told to assume that (1) sales and operating costs are expected to be up 10 percent over 1999, (2) interest rates will remain at their current level, (3) the firm will stick to its traditional dividend policy of paying out two-thirds of earnings, and (4) fixed assets and net working capital will need to increase by 10 percent to support the larger sales volume.

In Table 1.15 we present the resulting first-stage pro forma calculations for Executive Fruit. These calculations show what would happen if the size of the firm increases along with sales, but at this preliminary stage, the plan does not specify a particular mix of new security issues.

Without any security issues, the balance sheet will not balance: assets will increase to $1,100,000 while debt plus shareholders` equity will amount to only $1,036,000. Somehow the firm will need to raise an extra $64,000 to help pay for the increase in assets. In this first pass, external financing is the balancing item. Given the firm`s growth forecasts and its dividend policy, the financial plan calculates how much money the firm needs to raise.

In the second-stage pro forma, the firm must decide on the financing mix that best meets its needs for additional funds. It must choose some combination of new debt or new equity that supports the contemplated acquisition of additional assets. For example, it could issue $64,000 of equity or debt, or it could choose to maintain its long-term debt-equity ratio at two-thirds by issuing both debt and equity.

Table 1.16 shows the second-stage pro forma balance sheet if the required funds are raised by issuing $64,000 of debt. Therefore, in Table 1.16, debt is treated as the balancing item. Notice that while the plan requires the firm to specify a financing plan consistent with its growth projections, it does not provide guidance as to the best financing mix.

Table 1.17 sets out the firm`s sources and uses of funds. It shows that the firm requires an extra investment of $20,000 in working capital and $80,000 in fixed assets. Therefore, it needs $100,000 from retained earnings and new security issues. Retained earnings are $36,000, so $64,000 must be raised from the capital markets. Under the financing plan presented in Table 1.16, the firm borrows the entire $64,000.

We have spared you the trouble of actually calculating the figures necessary for Tables 1.15 and 1.17. The calculations do not take more than a few minutes for this simple example, provided you set up the calculations correctly and make no arithmetic mistakes. If that time requirement seems trivial, remember that in reality you probably would be asked for four similar sets of statements covering each year from 2000 to 2003. Probably you would be asked for alternative projections under different assumptions (for example, 5 percent instead of 10 percent growth rate of revenue) or different financial strategies (for example, freezing dividends at their 1999 level of $64,000). This would be far more time- consuming. Moreover, actual plans will have many more line items than this simple one. Building a model and letting the computer toil in your place have obvious attractions.

Figure 1.17 is the spreadsheet we used for the Executive Fruit model. Column B contains the values that appear in Table 1.15, and column C presents the formulas that we used to obtain those values. Notice that we assumed the firm would maintain its dividend payout ratio at 2/3 (cell B13) and that we hold debt fixed at $400 (cell B23) and set shareholders` equity (cell B24) equal to its original value plus retained earnings from cell B14. These assumptions mean that the firm issues neither new debt nor new equity. As a result, the total of debt plus equity (cell B25) does not match the total assets (cell B20) necessary to support the assumed growth in sales. The difference between assets and total financing shows up as required external financing (cell B27). Now that the spreadsheet is set up, it is easy to explore the consequences of various assumptions. For example, you can change the assumed growth rate (cell B3) or experiment with different policies, such as changing the dividend payout ratio or forcing debt or equity finance (or both) to absorb the required external financing.