Trend analysis is based on regression analysis. Regression analysis produces a line of best fit and predictions can be made based on the line. It is also known as the least square line, because the line passes through the distribution where the distance squared from the line is minimized.

Similar to that of correlation, the x variable can estimate or predict the y variable. That is, if x changes, then how much y changes can be estimated. For two numeric variables, you can predict y from the x variable if the correlation coefficient is strong and there is a linear pattern for the variables. Normally you would want the r correlation coefficient to be better than plus or minus 0.60.

Unless there is perfect correlation, the prediction for the value of y, given x, is merely a prediction. It is a guess but an educated guess with some sound scientific basis. The prediction will be subject to some amount of error. The standard error of the estimate measures how much the predicted values deviate from the actual y values. IDEA uses the mean absolute percent error (MAPE) to calculate the accuracy of predictions. MAPE is the average of the percentage errors, is expressed in percentage terms, and works best with positive amounts. The MAPE number is the predicted line on the average that is away from the actual line in percentages. Low MAPE values mean that the past data has a good it to the regression line and you can have more confidence in the data and prediction.

To use trend analysis in IDEA, the database needs at least one numeric field, and the field where trend analysis is to be applied cannot contain any bad data. The database cannot contain more than 65,536 records. The audit unit field cannot be the same field as the trend analysis field. In addition, the database should not contain seasonal data. If it contains seasonal data, then time series analysis should be selected over trend analysis. time series works similarly to trend analysis.

In our data file, we will perform trend analysis on the 12-month data from the debit card payments field of DEBIT_2010_2011 and generate 3 months of forecasts. It is not necessary to provide a reference field or audit units. Refer to Figure 6.3.

For debit card payments, it is trending slightly downward, and it is predicted that at the end of the three months, debit payments would drop to approximately $18,000 for that month as seen in Figure 6.4.

The MAPE is 5.36 percent, which provides high confidence as the reliability of the predictions.

In contrast to the debit card payments, cash payments are trending upward when we select CASH_2010_2011 as the field to trend.

There is less reliability in the prediction as the MAPE percentage is 23.17. It is predicted that at the end of the three months, cash payments would increase to approximately $14,500, as displayed in Figure 6.5. We discuss this increase more in Chapter 16 regarding zapper fraud.

We will perform a trend analysis showing a reference field and audit units. There are five branches and we will include all of them as audit units. The reference field of GLOBAL_AVERAGE_SALES is the average sales for the five stores broken down by months. We have eight years of data so each store would have 96 records (8 years x 12 months). We will generate forecasts for three months as shown in Figure 6.6.

FIGURE 6.3 Applying the Advanced Statistical Trend Analysis in IDEA

FIGURE 6.4 Trend Analysis Results of Debit Card Payments with Forecast of Three Months

FIGURE 6.5 Trend Analysis Results of Cash Payments with Forecast of Three Months

Branch A outperformed the average of the five branches. The actual data is above the reference data line and shows good promise of trending upward in Figure 6.7. The prediction is sound as the MAPE is 5.26 percent.

By selecting all five branches as the audit unit, we can display other branches by choosing from the Audit unit pull-down menu. We will look at one more by selecting

FIGURE 6.6 Applying Trend Analysis of Sales Referencing Global Average Sales for Each Branch

FIGURE 6.7 Results of BranchA's Trend Analysis with Three-Months Forecast

branch B (see Figure 6.8). Branch B fits right in around the global average sales and is also trending up.

For data with seasonal values where there are higher values in certain months (or any other time units) and lower values in other months, the time series analysis option is more appropriate. In our example in Figure 6.9, we will use gas-heating costs as the

FIGURE 6.8 Results of Branch B's Trend Analysis with Three-Month Forecast

FIGURE 6.9 Applying Time Series Trend Analysis of Heating Costs for Each Branch with 12-Month Forecast

field for the time series and all five branches as the audit units. We ensure that we input the correct season length of 12 as our records are broken down by months. We will generate 12 months of forecasts.

FIGURE 6.10 Results of BranchA's Time Series Trend Analysis with 12-Month Forecast

From the chart in Figure 6.10, you can see that heating costs are higher in the winter months and lower in the summer months for branch A. The mean absolute percentage error of 11.40% is fairly reliable in terms of the forecast. You can also view the other branches by using the pull-down menus from the Audit unit area.

Trend analysis using IDEA is simple and the auditor need not be concerned with the complex formulas to calculate the regression line and the mean average percentage error.

Found a mistake? Please highlight the word and press Shift + Enter