Command Overview

The trend command is used to compute a "best fit" line for balances or amounts over a period. The command supports two sources of data for testing:

1. WorkSheet
2. Selected range within a worksheet

Syntax

The command is entered on the command line as ("trend", or just "tr") followed by one or more command parameters. Each command parameter consists of an argument name, immediately followed by an equal sign ("=") and then immediately followed by a value. The value may be enclosed with quotation marks, and must be enclosed by quotation marks if the value consists of any embedded spaces.

An example of a command parameter to specify that the sheet name to be processed is "Cash Flows" is entered as sheet = "Cash Flows" (the quotations are required because there is an embedded space in the work sheet name). Each argument on the command line must consist of only valid parameter names. If a required parameter name is missing, then the command line will be highlighted in yellow, an error message will be displayed on the Excel status bar and an audible tone will be sounded.

The following is a listing of all parameter values for the trend command:

The data to be analyzed

All of the commands below will be used on a statement of cash flows. Some commands will be used for the entire statement, others for just portions of the statement. The "Cash Flows" worksheet is included within the QuickStart workbook which can be downloaded.

Simple Trend analysis

The simplest example of the command is just to select a series of balances for an account, such as shown above, and then enter the command "trend" in the toolbar. (Command can be abbreviated as just "tr").

The following information will be shown on the toolbar:

• R2 - this is the measure of how closely the data points match the best fit line, and is the R-squared statistical measure. This value ranges from +1 (perfect fit) to -1 (perfect mismatch). As a rule of thumb, values between .80 and .99 indicate that there is a fairly string linear trend. Lesser values, e.g. .2 - .5 indicate that there has been a good deal of fluctuation.
• Slope - this is the average amount by which the balance increases (or decreases) each period
• Intercept - this is the estimate for the starting balance
• Right - Actual - this is the account balance to the right side, which may be either the latest or the earliest depending upon how the amounts are shown
• Right - expected - this is the amount that would be expected, based upon a linear estimate. If R2 is close to 1.0, then this amount should approximate the actual account balance.
• Left - actual - this is the account balance on the left side, which may be either the latest or the earliest, depending upon how the amounts are shown
• Left - expected - this is the amount that would be expected, based upon a linear estimate. If R2 is close to 1.0, then this amount should approximate the actual account balance.

All of this information should provide the auditor/analyst a basis for evaluating the reasonableness of the account balances and provide an indicator as to the extent of further work required

More complex analysis

Sometimes it may be desirable to analyze more than one line item (row) at a time, i.e. a selected range on the worksheet. This can be accomplished by highlighting the area and then entering the command specifying that a selected area has been specified, with the name of the work sheet to contain the results.

An example is shown below, both the data being tested and the results obtained and written to another worksheet.

The results of the analysis are written to another sheet, as shown below:

Main Form