The producers of general spreadsheet programs have convinced many companies that the companies can program their own financial planning templates. But can each businessperson and management consultant learn to become a programmer to create specialized templates?

This article discusses what to consider when purchasing a financial planning software product.

Financial Planning is Done for

Many Different Reasons

The most important issue any company must determine before shopping for financial planning software is its reason for planning. Most reasons fit into the two categories of short-range or long-range planning.

Short-range planning should be used to project cash flow and profitability, aid in business decision making over the next one to three years, to review continued compliance with loan requirements, and to project financial condition as a prerequisite for additional loans.

Long-range planning should be used to project the cash flow and profitability of long-range projects, present the results of mergers and acquisitions, project the long-range results of such activity, and forecast long-range cash flow and profitability to determine the value of investments slated for acquisition or divestiture.

Short- or Long-Range Needs?

Most software currently available is meant to provide long-range planning. However, on the whole, short-range planning activity is far more prevalent in companies than long-range planning.

Software for long-range plans is much easier to design and code. Long-range plans have less emphasis on seasonality, specialized reporting, product analysis, and cost mix. However, computer memory requirements are very important. A three-year forecast by month requires 37 columns of information, including an opening balance sheet. A twenty-year forecast by annual period requires just 21 columns. Considering that many software products only provide a ten-year forecast by annual period or less, the tremendous memory requirements are obvious.

Many programs limit the planner to a smaller number of financial accounts. This allows the program to offer more months or years, but at a price. It would be better for a user to obtain a more sophisticated program and create two files if necessary.

Some programs store their information in a database as opposed to a spreadsheet. This allows storage of a large number of financial accounts. The trade-off is that spreadsheet programs are easier to work with. Because all spreadsheet information is maintained in memory and not on the hard drive, spreadsheet programs can operate faster assuming the spreadsheet program is equally well written.

Cash Flow and Long-Range

Planning

Many businesses can accurately forecast their profitability. However, when they look at their bank balances they wonder where the money went. Profitability and cash flow do not necessarily go hand in hand. Purchasing new equipment, equipping new offices, installing new telephone systems, hiring new employees and many other events can drain the cash of a profitable business. Interim cash shortages can also occur if these purchases or changes are made when cash inflow is low.

Long-range planning and many analysis programs will not catch dramatic changes in cash flow; in fact they may paint a very rosy picture. Short-range planning can catch these dramatic changes. With advance information, short-term bank loans can be obtained, suppliers can be alerted to expected needs, and arrangements can be made for extended credit lines.

LONG-RANGE PLANNING

CONCERNS

Users of long-range planning software tend to pay close attention to the first two years. After that, they tend to continue fixed growth rates for the next ten or twenty years. This defeats the purpose of a long-range plan. The reality is that planning beyond two years is difficult and after five years may be next to impossible. In addition, financial planners in charge of preparing a forecast often receive skimpy information beyond the first year or two from other departments. So the financial planners do the best they can; they continue the same growth relationships, ad infinitum.

However, this can lead to an inaccurate long-range plan. For example, a trade publication may have 20,000 subscribers out of an audience of 35,000. There may also be a limit to how much can reasonably be charged for an annual subscription. Using a constant rate of sales growth (dollar amount), the planner may project dollar sales that exceed the price and/or number of subscribers of the publication's audience.

The motto from this is: a long-range plan must be based upon a realistic perspective from the first year through the last. Departments and others furnishing information must carefully review the data and assumptions for accuracy and reasonableness. Each department should provide an estimate of confidence in its forecast assumptions for each year. Overall confidence for each significant department should be noted in the report on the forecast.

Looking for a Long-Range

Planning Package

Companies looking for a long-range planner usually fit into one of the following categories:

* The company will be making acquisitions of other businesses.

* The company is already using a short-range planner or budget program, or has developed internal software programs to handle the short-range forecast.

* The company is a very steady business with little change each month. No significant changes, such as a surge in growth or decline in the business or new product lines, are expected. Short-range changes are not very significant. However, management would like to see long-range changes in the business.

* The company will be sold. Management would like to determine a fair price for the business.

Forecast methods used for long-range plans do not need to be as accurate as those needed for short-range plans. This does not mean that there will be mathematical mistakes, but that the formula method does not have to produce results as precise as for short-range plans. Accordingly, the first years' results created as part of a long-range plan may not have the degree of precision as those resulting from a short-range plan. The bottom line is, use a long-range planner for long-range decision making. Use a short-range planner to make general business and short-term financing decisions.

Reports created by long-range planners are generally used by upper management. Therefore the reports should be presented in a summarized formula.

A word of caution if the program being considered is designed to determine the valuation of a business. Some of these programs, when calculating the present value of cash flows, will assume a mid-point in the year, while others will use an end-of-year point for cash flow discounting. The end-of-year approach can result in the undervaluation of a business. The higher the rate of discounting, the more pronounced the error. Seasonal businesses may also have to adjust their discounting to take into consideration whether their cash flow (not necessarily their sales) occurs early or late in the year.

A program that allows the entry of an annual forecast, and then spreads that forecast by month, based on seasonality or other factors, is very helpful. The software can calculate cash flow, and the interest and tax effects for the first month of the first year. That month's complete set of financials can then be updated before calculating the second month, and so on.

Looking For a Short-Range

Planner

Companies looking for a short-range planner usually fit into one of the following categories:

* The company would like to project cash flow and profitability over the next one to three years, to aid in business decision making.

* The company needs to review continued compliance with loan requirements and project financial conditions as a prerequisite for additional loans.

* The company is already using a long-range planner or budget program, or has developed internal software programs to handle the long-range forecast. They may also be looking for a "what-if" tool to analyze the information already gathered by their in-house budgeting programs.

* The company expects significant changes such as a surge in growth or a fall-off in the business, or introduces a new product line. Management would like to see the impact of these expected short-range changes in the business.

* The company will be making acquisitions of other businesses. Cash flow from such transactions is critical in the first two years. Bridge loans payable within two years can greatly affect cash flow of the combined operations, and if not carefully evaluated can adversely affect future plans and operations.

* The company or a division will be sold. Monies obtained from sale will be used immediately to pay down debt, expand other operations, or provide a cash cushion for the company.

Forecast methods used in short-range planning need to be flexible. A short-range plan should provide monthly or quarterly financial statements and/or monthly analysis.

Factors to Consider in a Short-Range

Planner

Does the software planner being considered require a dollar amount or growth percentage for each month (or quarter) of each financial account?

If the answer is yes, then the planner requires significantly more data. However, the trade-off is complete control over every amount in the forecast. In this case five to ten formula methods may be sufficient.

If the answer is no, then the planner is providing some intelligent decision-making built into the program. Planners of this nature might request quarterly or annual information, spreading the results in some arbitrary fashion over the intervening months to create complete sets of monthly financial statements and/or analyses. The user may have little control over the monthly amounts. To obtain precise control, the user should make use of the powerful software capabilities, such as the ability to spread based on seasonality and using complex mathematics. The program may even assume creation of seasonally adjusted geometric progressions, requiring some heavy duty programming. The benefit is the creation of twelve sets of financial statements for each one set previously created. Hidden pitfalls, such as a sudden cash drain in a particular month, may now be brought to the user's attention months before they happen. "What-if" analysis becomes much more exacting. Best of all, the work needed to create monthly sets of financial statements is about the same as for annual statements, only the software does all the hard work.

Types of Software Planners

Some planners are developed using a general spreadsheet program as the building tool. For example, many have been created using Lotus 1-2-3 or some other spreadsheet. Several have worked a year or more building complex and highly accurate templates, which are currently on the market. The result is a top notch planner. If a user would like to operate in a familiar environment, then the author highly recommends the use of a good commercial-quality template developed in your favorite spreadsheet program.

There are also stand-alone programs, constructed using a program language. Others are constructed based upon an existing database program and still others are their own, full-fledged spreadsheet programs. The developers of these programs realize that a template using an existing spreadsheet program, although finely built, has severe limitations. The initial size of the spreadsheet reduces available memory for macro-language programming and financial data.

Features to Look For in Planning

Software

Number of financial accounts. Financial statements must mesh as a whole. This means that there should be a complete balance sheet, income statement, and cash flows statement. Each should have enough pre- programmed accounts to handle any need. Often, users say they only need a select list of accounts and that half the accounts in the more sophisticated programs are not needed. But which accounts are those? The manufacturer is not about to make a specialized program for each customer's needs. According to Murphy's law: Even the simplest business will ask for financial accounts or formula methods not available on even the most sophisticated programs. Their business may be simple, but their needs are often quite unique.

Addition of new sub-accounts. Good financial planning software can be designed to understand the substance of each account. For example the program should be able to distinguish a fixed asset from an account receivable. In this way it can create a cash flows statement and calculate various ratio analyses. Some programs allow the user to enter new sub-accounts to the main account. For example, five new accounts might be created under the area of accounts receivable. Each of these accounts can be programmed individually. However the software remembers that these new accounts are part of accounts receivable.

This feature is a distinguishing characteristic in evaluating the power of programs available from software companies. With the ability to create new sub-accounts, the planner can use different formulas on subparts of a main category. Seasonality can also be introduced.

It is in the area of sub-accounts that the software planners built on existing spreadsheet programs have the biggest limitation. What some template developers have done is build several windows--separate parts of the spreadsheet--into their main spreadsheet. A user might be able to enter 20 or more general expense categories into a window. The totals are automatically entered into the main spreadsheet area on re- calculation. Although this works, there might be only five or six such windows. Additional accounts receivable, sales, COGS, and fixed asset accounts may not be possible.

Linking Results. Linking is the ability to have changes made to one account or one cell of one account, based on the results of another account.

For example, accounts receivable ("A/R") might be forecast as a percentage of sales. If sales are $1000, $2000, and $3000 for years one, two and three, and A/R is set at 30% of sales, A/R would be $300, $600 and $900 respectively for the three years. A/R can also be forecast as a percentage of the change in sales, or even a percent of the previous sales amount.

Selection of Formula Methods. Typically only one formula method may be selected for each account. If sales are forecast by a growth factor over the previous calculated amount, all cells used for data entry must use this formula method. Some programs allow the user to override the formula method for a particular cell, and enter a dollar amount instead.

Other programs allow the user to enter a different formula method for every cell used for data entry. Using sales as an example, a different formula method may be used for each month/quarter/year or other period of data entry. Some have seasonality charts and each cell may be linked to a different seasonality chart.

Mark Accounts for Deletion. Some programs may let the user mark an account or spreadsheet row for deletion. This can be very useful when playing "what-if."

For example, there may be five different sales scenarios. The user would set up five sales sub-accounts, with perhaps five cost of sales and five A/R accounts that link to each of the sales scenarios. By marking any four of the scenarios for deletion you can see the effect of the remaining scenario on the projection.

This feature keeps all scenarios on screen at one time. It can be a time saver when working with many different scenarios, or making several minor changes to a particular scenario.

Spreadhseet Commands. Spreadsheet commands, such as cursor key movement and the ability to copy amounts of formula methods into future periods will be found in most available programs.

Historical Financals. All programs will permit entering an historical balance sheet. The long-range planners will generally let the user enter one or several years of historical financials.

Account Descriptions. Some programs allow changing the description of an account. Although the user may change an account's description, the character of the account will remain the same. For example, "accounts receivable" will always remain A/R in character when the program calculates cash flows and ratio analyses.

It can be annoying not to be able to change an accounts name; this is especially important for specialized industries. Comparing forecasts to other internal reports with different account descriptions can be confusing.

Some programs also let the user change all descriptions for each financial statement to upper or lower case, capitalizing the first character on conversion to lower case.

Number of Months or Years. Shortrange planners typically create one to five-year forecasts by month. Long-range planners typically create five to 25 year forecasts by annual periods.

The number of months or years needed will differ, depending on the use. However, this should not be an overriding criterion. If the user needs a six-year forecast by month, either three two-year files or two three-year files could be used. It is not as convenient, but it's not so terrible either. Just change the opening balances in the second file, and make some income statement changes.

Financial Statements

As previously noted, unless an analysis program is being used, the three basic financial statements, balance sheet, income statement, and cash flows statement should be included.

The quality of the cash flows statement will differ the most between software products. Some will produce their own easy-to-read cash flow statements. Others will produce a cash statement in accordance with SFAS 95, using either or both the direct and indirect methods.

For general planning the user should seek a detailed and easy-to-read cash statement. The author finds the cash statements in accordance with SFAS 95 confusing. If you need SFAS 95 statements, the user should look for a product that will produce both an easy-to-read cash statement and one produced in accordance with SFAS 95.

Programs that can create SFAS 95 cash flow statements can serve double duty for public and corporate accountants. By entering historic balance sheet and income statement information, along with any supplementary cash flow data into the planner, the planner can produce the quarterly or other periodic SFAS 95 statement.

Reporting

The user should look for programs that can create and save multiple report formats. Export of files is very important. Because most financial planning software is written as templates to existing spreadsheet programs, this is not a problem. Major spreadsheet programs can all convert their files into dbase and ASCII text.

Graphics provide a visual aid to reports. Some products provide built in graphics. Financial information can also be ported to a general spreadsheet program for top-flight graphics.

Valuation

Many programs can calculate present values for cash flows. This is useful in determining the value of a business or operation. As noted earlier, care should be taken as to how the program determines the date when the cash flow is created. There are also valuation programs on the market, produced by companies that do not have financial planners. But let the buyer beware! The author believes there is a stronger correlation between valuations based on projected cash flow than there is typically displayed by many other valuation methods.

Monthly Adjustments

For programs that can perform spreading, there should be some way that the user can manipulate the computed montly amounts.

For example, the author's program Financial Wonder allows the user to load a saved file under its report mode. The user can work with all of the monthly dollar calculations. New sub-accounts can be added, amounts changed, and the file recalculated. Re-calculation will re-foot all totals. More importantly, changes in cash flows and taxation will be run through each monthly set of financial statements and ratio analysis based on the changed monthly amounts.

Consolidations

Some of the programs allow consolidation of companies. If using a template program, the user can probably have Lotus 1-2-3 do the consolidation.

Most consolidation programs combine the amounts from like accounts into one set of totals. If there is need to work with or obtain reports that show the detail from each of the companies in addition to the combined totals, the user should look for programs that can produce consolidated and consolidating reports.

Create Templates from Saved

Files

An important feature to look for is template creation. Chances are that future budgets will use the same set of financial accounts and formula methods. By "blanking" the amounts and saving the overall structure of financial accounts and formulas, a user can create a template for future use. This is a lot easier than loading the previously saved file, and zeroing out the amounts one at a time.

Formula Methods

Most planners will only permit using one formula method for each account. As noted, some allow using a different formula method for each forecast (data entry) cell of each account. Many will permit entering a dollar amount, as an override, at any point. This is an important feature.

Ease of Use

No matter how sophisticated the program, it should be easy to use. It is acceptable for a financial program to provide options or even formula methods that stretch the limits of your financial understanding. But even sophisticated programs should hide as much of their complexity as possible.

Easy-to-use software takes much longer and is more costly for software companies to develop.

It Will Soon be Easier

Until now, the highly sophisticated, stand-alone programs have been used more for mergers and acquisitions than for budgeting. Spreadsheet template programs made for budgeting have lacked the power of stand- alone programs. As discussed, monthly budgeting can require much from a software planner.

The future for software holds more advances. Until now the 640K memory limitation of MS-DOS has posed severe limitations on high-end planning software. Databases could be used to store information out of RAM memory, but then highly advanced calculations would be very slow. Holding all information in RAM memory reduces the amount of information that can be worked with such a limiting a budget to 12 or even 36 months. The use of DOS-Extenders which allow programmers to address approximately 16 million characters, will become very important. Template programs can now be made for Lotus 1-2-3, Release 3, which uses such a DOS-Extender. The author's Financial Wonder has recently been modified to take advantage of virtual memory.

Eventually the split between long-range and short-range planners will disappear. The added memory will allow the highly sophisticated techniques of short-range planners to be used across many years of historical and forecast information.