INSTRUCTIONS FOR USING FINFORECAST

The financial forecasting model, FINFORECAST, provided in an accompanying Excel document, and described in this word document, is based on the presentation of financial forecasting provided in Chapter 12 of Small Business Management:  An Entrepreneurial Emphasis, 12th edition, by Longenecker, Moore, and Petty (South-Western).

Follow the instructions below to download the FINFORECAST spreadsheet to your computer:

If Netscape is your browser, click the FINFORECAST link below, select 'Save it to disk', the [Save As] dialog box appears, select the directory where you want to place the program, then click 'Save'.

If Microsoft Internet Explorer is your browser, right-click the FINFORECAST link below, select [Save Target As...] from the popup menu, the [Save As] dialog box appears, select the directory where you want to place the program, then click 'Save'.

FINFORECAST

FINFORECAST EXPLAINED

As a means to prepare a simplified business plan, we provide an Excel spreadsheet, FINFORECAST, that allows the user to make five-year projections of income statements, balance sheets, and statements of cash flows.  The model is totally integrative in that any change in assumptions will be recognized throughout all the financials.   For instance, a change in the amount of debt shown in the balance sheet affects the firm's interest expense in the income statement, which in turn affects net income, which alters the retained earnings in the balance sheet.   All these changes are recognized simultaneously.

To use the financial forecasting model, open the Excel file, FINFORECAST.  When you open the file, you will immediately notice an area of the spreadsheet that is shaded yellow with the words and numbers in bold italics.  This area is for entering the assumptions to be used as the basis of your forecast.  The assumptions already appearing in the cells are based on an illustration described below.  However, as soon as the assumptions are changed by entering new numbers in the place of the existing numbers and the existing macros are used, the revised answer will appear.

To use FINFORECAST, we first make assumptions about the firm's future financial performance in terms of:
  1. Sales projections-both beginning sales and sales growth rates.
  2. The minimum acceptable amount of cash to be maintained in the business.
  3. Cost and expense relationships to sales.
  4. Relationships of assets and spontaneous liabilities1 to sales.
  5. Estimates regarding the purchase of equipment and building.
  6. The amount of equity (common stock) that the owner will invest in the business.
  7. The terms of debt financing-type of loan, duration of the loan, the interest rates, and any loan covenants imposed by the lender.
Once the basic assumptions have been designated, the forecast model uses macros to determine:
  1. How much line of credit2 the firm can borrow from the bank without violating the bank's requirement that the firm maintain a predetermined minimum current ratio-if such a 'loan covenant' is imposed on the company.
  2. The amount of additional long-term debt (in addition to a mortgage to finance the purchase of a building) that will be required to complete the firm's financing needs. The model assumes that the firm will use as much line of credit as possible before resorting to any long-term debt.
If the firm reaches the point where it has more financing than required to meet its financial requirements (debt plus equity is greater than total assets), then we assume that the excess is invested in cash.  In other words, if the firm's debt and equity before borrowing any line of credit or additional long-term debt exceeds total assets, then the difference is used to increase cash above the minimum acceptable cash balance.   Alternatively, an entrepreneur may not want to retain all of the increasing cash in the firm, and could choose to expand the firm's plant and equipment or to pay dividends or to pay down the mortgage more quickly than planned.  Any such choices can be recognized in the financial model by simply changing the appropriate balances in the balance sheet.

FINFORECAST ILLUSTRATED
To illustrate the use of FINFORECAST, consider the following example.

Camaron Chai is planning to start a new business to provide sales training material for automobile dealers.   After studying a similar company in a different state, she believes the business could generate sales of approximately $200,000 in the first year, with expected sales growth for the next five years at 35 percent per year.  Based on her investigation of the opportunity, she has also made the following projections:

1.  Expected expense relationships

  Variable cost of goods to sales 25%
  Variable operating expenses to sales 20%
  Mortgage interest rate 10%
  Line of credit and other debt interest rate 12%
  Tax rate 30%
  The company's fixed costs would be as follows:  

YEARS
 1
 2
 3
 4
 5
Fixed cost of sales 60,000 60,000 75,000 80,000 100,000
Fixed operating expenses 45,000 45,000 55,000 70,000 70,000


2.  The requirements for accounts receivable, and inventories have been estimated as a percentage of sales:

Assets
 Percentage of Annual Sales
Accounts Receivable 15%
Inventories 20%

3.  Camaron has searched for an office/warehouse facility and has found a building suitable for the needs of the business that would cost about $90,000. The facility will be depreciated over 15 years to a zero salvage value.

4.  Equipment purchases will be around $40,000 for the first year and $10,000 per year in the ensuing years. She will depreciate the equipment on a straight-line basis over five years.

5.  Camaron has negotiated with a supplier to extend credit on inventory purchases; as a result, it is expected that accounts payable will average about ten percent of sales.

6.  Accruals should run approximately five percent of annual sales.

7.  Camaron plans to invest $100,000 of her personal savings in the venture in return for 20,000 shares of common stock.

8.  The bank has agreed to provide a short-term line of credit to Camaron up to $30,000 at an interest rate of 12 percent. For planning purposes, interest for this source of credit will be computed based on the amount of debt outstanding at year end.

9.  The bank has also agreed to help finance the purchase of the building to be used in manufacturing and warehousing the firm's product. The bank will loan the company $45,000, with the building serving as collateral for the loan. The loan will be a mortgage to be repaid in equal annual payments over 20 years. The interest rate on the mortgage is to be 10 percent.

10.  As conditions for the bank agreeing to loan the money to Camaron, the banker would impose two loan restrictions (loan covenants): (1) the firm's current ratio (current assets ÷ current liabilities), should not fall below 1.75, and (2) no more than 60 percent of the firm's financing should come from debt. Failure to comply with either of these terms would result in the bank loans coming due immediately.

11.  If additional financing is needed, Camaron will seek out lenders who would provide long-term debt at an expected interest rate of 12 percent.  The principal could be increased or decreased as needed. For planning purposes, the interest rate will be based on the balance outstanding at the year end.

12.  Camaron wants to maintain a minimum cash balance of $10,000, but would like to have more cash when the firm can afford to do so without borrowing.


Given the information above, we can now use FINFORECAST to prepare pro forma financial statements for Chai, Inc. The steps are as follows:

  1. Open the Excel spreadsheet, FINFORECAST.  When the file opens, you will be given the option to disable or enable the macros.  Choose 'enable macros'.
  2. In the top part of the spreadsheet, you will enter the values for the different assumptions.  (The numbers for Chai, Inc. are already entered in the spreadsheet, but when ready, you can replace these numbers with your own assumptions.)
  3. Click on 'Set Min Cash' box next to Cell E7.  For Chai, Inc., this number should be $10,000.
  4. Click on the box 'Clear LOC' (line of credit) box in column I, between rows 58 and 59 and 'Clear Other LTD' (other long-term debt) shown in column I between rows 61 and 62, which will set these values to zero for all years.  At this point, the balance sheet is out of balance; that is, total assets do not equal total debt and equity.
  5. Finally, click on the box 'Balance All Prds', which computes the line of credit and other long-term debt needed to finance the firm's assets.  At this point, the income statement, the balance sheet, and the statements of cash flows are all complete.  (Note row 70 shows that 'additional financing needs' are equal to zero in all periods, which means that total assets exactly total debt and equity.  Also, the current ratio is equal to or greater than 1.75 and the debt ratio (total debt ÷ total assets) is not above 60 percent in any period.

Below the statement of cash flows, you will notice a section that shows the repayment schedule for the mortgage.   The equal payment is calculated, along with the annual interest and the note balance that is outstanding at the conclusion of each year.  If we continued the schedule for 20 years, we would see that the note would be completely paid off in the 20th year.
To understand the solution for the Chai, Inc. illustration, some comments are helpful:

  1. For year 1, the firm will pay no taxes due to its loss.  The line of credit is limited to $15,718, as opposed to the full $30,000 line of credit.  Any amount above the $15,718 would cause the current ratio to fall below 1.75 and the firm would be in violation of its loan agreement with the bank.  The remaining financing would have to come from long-term debt, which is $24,380.  When all this is done, we see that the 1.75 minimum current ratio is satisfied and the debt ratio is below the 60 percent constraint imposed by the bank.
  2. In the second year, the firm is profitable; however, in computing taxes, the loss in the prior year can be applied against the income for the second year.  Thus, the firm will pay taxes on the income for the year less the loss carry forward from year 1; that is, the taxable income is $1,188 ($19,500 - $18,312)  Accordingly, the taxes for the year are $357 ($1,188 x 0.30 tax rate).  Regarding the line of credit, the amount available is limited to $19,218, due to the loan covenant that the current ratio cannot be less than 1.75.  The remaining financing needs to fund the asset requirements would have to be provided from long-term debt, which amounts to $10,600.  The debt ratio has declined from 58 percent in year 1 to 53 percent in year 2, below the 60 percent maximum debt ratio covenant.
  3. In year 3, the firm pays taxes on the earnings before taxes, since the loss carry forward has been fully used in year 2.  If some of the loss in year 1 had not been fully used in year 2, the remaining loss would have been applied to year 3 and in future years if any unused loss still remains.  Further, the firm no longer has any need for long-term debt.  The firm's debt requirements can be satisfied completely with the line of credit in the amount of $8,701.  At this time, the amount of financing is not dictated by the 1.75 current ratio constraint, or by the maximum 60 percent debt ratio that the bank imposes.
  4. In years 4 and 5, the firm no longer has a need for the line of credit or the long-term debt.  The firm's profitability is now more than sufficient to provide the needed capital.  Now, the minimum cash balance of $10,000 no longer is a necessity, but depends on what the entrepreneur wants to do with the extra cash.   In this solution, we are allowing the owner the flexibility to have some additional liquidity in the form of extra cash if desired.  (The amount of cash is simply determined by finding the amount of cash that results in total assets equaling total debt and equity.)

back to top

Copyright © 2003 South-Western. All Rights Reserved.