SOFTWARE UPDATE REVIEW -- QPLAN

By Pat Hamel, CPA, and Pamela Rafferty, CPA, Hamel Associates, Inc.

In the January 1997 issue of The CPA Journal, we reviewed QPLAN. Since that time, many interesting changes have taken place in this fascinating product. Before discussing the changes, here is a very brief summary of our previous review.

QPLAN was only available in Windows 3.1 but is now a fully functioning Windows 95 system. The QPLAN system is an integration of a number of programs which allows the user to build comprehensive personal financial statements and projections in a spreadsheet format through the use of simple input forms. The spreadsheets, which are built by the system, are also able to be exported to Excel. The QPLAN system utilizes a database which enhances the use of the program for centralized office control for LAN users. The system is comprised of five separate programs: The Administrator, Securities Maintenance, The Planner, Database Maintenance, and Ad Hoc Reporting.

QPLAN is designed for the user who would rather (1) review spreadsheet financial output than become a spreadsheet expert, (2) not worry about formulas in any given spreadsheet cell being changed by an inexperienced staff, and (3) use the power of the database and spreadsheet in combination as opposed to the limited data base functions found in spreadsheet programs. Beginner and pro alike can find functions and features that will make their financial planning life easier.

It should be noted that while QPLAN can produce innumerable spreadsheets, it only produces spreadsheets for which there is data. Consequently, the user may limit the output to income tax projections, and there are no blank lines or captions with a row of -0-'s across the page.

Let's take a brief look at the different sections which comprise the QPLAN
system.

Programs

The Administrator. The Administrator is where the user customizes the system. This is also where inflation and deflation defaults are set for items of income, expense, and asset appreciation (depreciation). These defaults may be overridden by the user for any given client. The user may also customize the lists which will appear on the input forms in The Planner such as adding categories of living expenses. Spreadsheet formats may be standardized for the office and tax tables reviewed or maintained. There is also a time recording device, so that the system will keep track of which staff worked on which client for how long. In addition, the adminstrator maintains the foregoing: names and addresses of advisors (attorneys, brokers, insurance agents, etc.) to the clients and who may use the system and their passwords.

Securities Maintenance. Securities Maintenance controls the list of securities in which clients have invested. It is maintained and updated for periodic pricing, dividends, and stock splits. Here also is an input dialog in which entries can be made for client security trades without loading all of the client information. This allows for entry of the trades by clerical staff. In that way the professional staff may concentrate on the review of the financial information as portrayed in the spreadsheet analyses.

A securities pricing download is in the works and should be available by the end of the year. In the meantime, the user has the option of obtaining the securities pricing data from Muller Data Corporation in their D-40 configuration, or entering the information manually.

The Planner. The Planner is the meat and potatoes of the system. This is where the simple input screens collect the data to be stored in the database and are used to create the spreadsheet output. In the demo version of the program which is available on the QPLAN web site (http://www.qplan.com), the demo builds 54 integrated spreadsheets, which are exportable to Excel. It should be noted that the user also has the ability of selecting how many years of projections he or she wishes to build.

Spreadsheets. The following are most of the spreadsheets and a brief description of what you will find in the spreadsheets:

* Basic Level Spreadsheets. These are stand-alone spreadsheets with no integration from other spreadsheets:

* Businesses--Income accounting, cash flow, asset valuation, and loan information for as many businesses as the company may have.

* Rental Property--Income, cash flow, asset valuation, and mortgage information for as many properties as the company may have. The output from these spreadsheets feeds a passive activity loss spreadsheet for tracking passive activity loss carryover and utilization in the computation of the income tax.

* Stock Options--Full accounting for nonqualified options, incentive stock options (ISO's), and restricted stock grants, vesting, exercises, and expirations. These spreadsheets feed the statement of net worth, income statement, and cash flow as appropriate.

* Securities Portfolios--Securities portfolios accounting, buys and sells, gains and losses from securities trades, current pricing of the portfolio (pricing set in the Securities Maintenance program of the system), and estimated income from the portfolio. As many securities, portfolios may be entered as desired with all sorts of ownership and deferred accounts.

* Employee Benefits--Contributions, investments, and eventual withdrawals of pensions, profit sharing plans, and 401(k) plans.

* Insurance--All life, casualty, disability, real estate, and umbrella coverage.

* Passive Activities--Direct investments (formerly referred to as tax shelters); also fed from rental property spreadsheet. Information on the investment aspects of the transaction are centrally maintained in The Administrator.

* Advanced Level Spreadsheets. These spreadsheets are built from Basic Level spreadsheets with a minimum of additional input.

* Income Statement and State Taxes--This is where all of the information and calculation of state and Federal income taxes (including alternative minimum tax) takes place. All of the income tax information from the Basic Level spreadsheets and direct input (such as salary, bonus, and itemized deductions) culminate to produce the current year income tax calculations and all projected income tax calculations. The 1997 tax law changes are included except for the IRA changes. (High income taxpayers cannot use IRAs.)

* Cash Flow Statement--All items of cash flow from the Basic Level spreadsheets including security purchases and sales are found on the Cash Flow Statement. Direct input screens, such as living expenses, also provide needed information for the true calculation and projection of cash flow.

* Statement of Net Worth--The valuation of all assets and liabilities found on the Basic Level spreadsheets are summarized on the Statement of Net Worth. In accordance with the AICPA's Statement of Position (SOP) 82-1, Accounting and Financial Reporting for Personal Financial Statements, the user may include deferred income taxes from either the default in The Administrator or by overriding the default for a given client.

* Estate Analysis--Since, as previously noted, the user may determine the number of years of projections that are required, the Estate Analysis may be built for any projected year and will be based upon the Statement of Net Worth for that year. Input which may effect the Estate Analysis includes cumulative gifts and gift taxes paid, the establishment of life insurance trusts, and a qualified personal residence trust. (If a QPRT has been established, the value of the residence will not appear in the Statement of Net Worth after the term of the QPRT has expired.) Through using the "what if" function, the user may see the effects of various estate planning strategies. This is particularly dramatic in the QPRT area since the estate tax calculation may be run after the term of the trust has expired and "true" future estate tax savings may be quantified.

* Portfolio Structure--Because QPLAN has what is called a "universal" list of securities in the Securities Maintenance, it affords the user the ability to determine the allocation of securities over a broad spectrum of information useful to the financial planner. As the "universal" list of securities identifies the industry that a company "belongs to," the portfolio structure shows the planner the industry mix of the investments, the bond weighing by maturity date of the bond, and the categories of mutual fund investments.

* Asset Allocation--The Asset Allocation takes into account not only the items in the Portfolio Structure but also the items from the (1) Businesses, (2) Rental Properties, (3) Direct and Passive Investments, and (4) Employer Securities [Stock Options, 401(k) holdings, outright ownership, etc.] spreadsheets.

* Total Return--The Total Return analysis is currently structured for investments which have been made (including purchases and sales during the period of the analysis). This does not include money market funds which are considered like cash and available for investment.

* Summary Schedules. If the detailed information is not necessary, QPLAN also provides summary spreadsheets. These spreadsheets are very useful in providing thumbnail sketches for use in communicating essentials to taxpayers. These summary schedules include the following:

* Current Net Worth--A statement of net worth at the date of the securities pricing.

* Income Tax--A summary of state and Federal income tax liabilities and how they are being met through withholding or quarterly estimated income tax payments.

* Net Worth Projections--Projections of the composition of net worth for the number of years of the financial projections. This is useful in providing retirement planning suggestions until the QPLAN Retirement Planning Module is completed.

* Liabilities--The current balance of liabilities at a given date (securities pricing date) which will tie to the current net worth statement. This schedule also informs the user of who the lender is, the lender's address, the account number for the loan or mortgage, the original balance, the terms of the loan, and the monthly payment.

* Stock Options--gives the amount of employee stock options currently vested (through the securities pricing date) and options which will vest through year-end.

* Asset Allocation and Portfolio Structure--A summary of the asset allocation and portfolio structure.

* Ad Hoc Reporting. This is a most useful part of QPLAN--a place where the planner can manage his/her business. This is where the user gets to look at the total database and survey the entire spectrum of those in the system. In addition to this database overview, there are some "functional" parts of the program which should not be overlooked such as Educational Funding Analyses and Mortgage Amortization Schedules. The database overview also contains the following:

* Total Return Analyses--Here you may select as many as you would like. Select the portfolios that you would like to see included in the analysis, and build a total return analysis for the entire office or a particular subset of the entire office. With this analysis, you will be in a position to judge which clients are above or below the office average and take whatever action that the user feels appropriate. There is also an option to identify the "Key Brokers" of the office and compare their performance to that of the office in general or for specific clients.

* Office Portfolio Structure--This will give you the portfolio structure for all in the system and the specific portfolios which you select. Thus, the user may view l) the market value of portfolios under management, 2) the market value of portfolios under supervision, or 3) the market value of portfolios under influence.

* Database Queries--Since QPLAN is a database system, the query function is available through Ad Hoc Reporting. Here the user may select things in common across the entire database, such as persons born in a given month, those who own a given security, or those who own a given direct investment, etc.

* Time Reporting--As mentioned earlier in our discussion under The Administrator, there is a timekeeping option for all users of the system. The reports are produced under this program of the system. The user may build these reports keyed on either the taxpayer or the planner.

Evaluation

QPLAN has been improved substantially since our last review. It is an excellent analytical tool that allows the planner to evaluate a tremendous amount of data in the format of the financial statements in spreadsheet form.

Features will be added such as a Retirement Planning module and Survivor Capital Sufficiency Analysis. However, the system in its current comprehensive form should benefit the novice and professional alike. There is the usual charge for the license of the software and an annual maintenance fee which covers all upgrades and new releases of the product during the maintenance period. QPLAN has been developed by Roy Quarve, CPA, based upon the comprehensive spreadsheets that he created for counseling taxpayers over the past 15 years. For information, visit his web site or contact QPLAN, One Atlantic Street Stamford CT 06901, (203) 356-1910. *

Editors:
Milton Miller, CPA
Consultant

William Bregman, CPA\PFS

Contributing Editors:
Alan Fogelman, CPA
Clarfield & Company P.C.