Updating Analytical Procedures
By Edward Blocher, George W. Krull, Jr., Leonard J. Tashman, and Stephen V.N. Yates
In Brief
Tools for Greater Audit Effectiveness
New tools for analytical procedures promise even greater audit effectiveness and more efficient analytical procedures. Tools such as Microsoft Excel are already on most auditors’ desktops. Business forecasting tools (BFT) are also readily available and promise both ease of use and greater precision for the expectations that auditors must develop when using analytical procedures. These tools can allow the auditor to turn more attention to gathering reliable data for analytical procedures, and reward the effort of doing so. Perhaps most important, the new tools facilitate the development of explicit types of expectations, such as regression and time-series analysis, which, because they are more informed than implicit expectations, are more precise and more reliable for both the planning and substantive test objectives of analytical procedures. Moreover, BFT systems, unlike Excel, have built-in statistical checks to guide the auditor to more effective use of the tool.
The most effective analytical procedures provide the most reliable and accurate expectations for the account balance or item being examined. The most precise procedures involve making an “explicit expectation” of the account balance or item. With an explicit expectation, the auditor identifies the financial and operating relationships affecting the account, then uses those relationships to develop the expectation.
A simple comparison of current to prior year balances or ratios is not an explicit expectation. The implicit expectation is that the balance or ratio will be the same as the prior year’s. In contrast, an explicit expectation asks “What should that balance or ratio have been at the end of the year?” For example, an explicit expectation for the sales account for a retail firm would use local business trends as well as information about the firm’s operations (e.g., advertising expense) for the year. Because explicit procedures are more precise, they are the strongest analytical procedures for audit planning and testing. The most commonly used explicit procedures are regression analysis and time-series analysis. Spreadsheets and forecasting tools are readily available to perform these analyses.
Case Study on Developing Expectations for Balances
Consider an auditor performing an analytical procedure for the purpose of assessing the reasonableness of reported sales of a retailer. The monthly sales balances for the current year (2001) and the two prior years are known, and the sales are likely to be affected by three variables:
The auditor expects sales to increase in proportion to an increase in each of the three variables.
Regression and time-series analysis are predictive methods that systematically and mathematically provide the best-fitting expectation for the available data. Both the regression and time-series methods use prior periods’ account balances to predict future balances. In the audit context, the “future balances” are the unaudited monthly figures for the current year.
Time-series analysis takes into account only information for the particular account balance: that is, its seasonality, trend, and any specific events that affected the sales balance for the current and prior years. These balance-specific factors are often called the time-series properties of the balance. Seasonality, special promotions, and other time-series properties are likely to be useful predictors of sales.
In contrast, regression analysis can include time-series properties as well as information about the relationship of sales to an index of local business activity, operating expenses, and selling expenses variables.
The Sidebar illustrates both regression and time-series analysis using Microsoft Excel and another widely available business forecasting tool that uses spreadsheet data input. For a discussion of the statistical details, see the AICPA Auditing Practice Release, “Analytical Procedures,” published in 1998.
Reasonableness Test Using Excel
The reasonableness test can be done easily with Microsoft Excel’s regression analysis tool, found in the Analysis ToolPak under the Tools menu. Note that this case includes all three variables, based on the plausible expectation that each variable has a strong relationship to sales. Exhibit 1 shows the results of this regression.
This regression’s R-square of 0.9999 indicates a very high correlation between actual monthly sales and the monthly sales estimates. In addition to the R-square, other important considerations are the plausibility of the relationship between sales and the predictor variables, the reliability of the data, and the adequacy of the other statistical diagnostic measures.
Reasonableness Test Using Forecast Pro
Excel is a valuable general-purpose tool for statistical analysis, but it lacks the power and ease of use of specialized business forecasting tools (BFT) software. Data can be entered into a more comprehensive BFT, such as Forecast Pro, from an Excel spreadsheet.
Variables can be easily added and deleted from the regression in most BFTs. In contrast, Excel requires the variables in the regression to be in adjacent columns. Exhibit 2 shows the regression results for Forecast Pro, with statistical measures identical to those of the Excel regression in Exhibit 1. The Forecast Pro output, however, shows additional statistical measures the auditor can use to assess the reliability of the regression.
Whereas Excel requires the user to enter formulas to calculate the forecasts for each month in the audit period, BFTs provide this information automatically. Exhibit 3 shows the Forecast Pro forecasts for the example. This report shows upper and lower confidence intervals for each month (5% and 95% limits; other levels are also available), to assess the possibility that a given month’s sales are misstated. For example, the results suggest that the three months in the second quarter are understated, while December’s sales are possibly overstated. These results provide the auditor a basis and direction for further investigation.
Perhaps most important, BFTs can provide diagnostic messages that alert the user to potential ways to improve the regression—a diagnostic tool that Excel does not provide. For example, Forecast Pro produces a report that shows whether the use of lagged variables (e.g., to represent lagged relationships, seasonality, etc.) will improve the model. Other diagnostic tools are also available.
Overall, although Excel provides a useful tool for precise explicit procedures such as regression, BFTs make the job easier and provide the diagnostic tools that improve precision.
Time-Series Analysis
Often reliable explanatory variables for expectations are elusive. For example, if only the prior 24 months of sales balances and 12 current months of sales data were available, regression analysis would not make sense, but another approach, time-series analysis, would be useful.
Time-series analysis is a collection of statistical methods that use only the historical patterns in the balances to predict future values. Time-series analysis looks for trends, seasonality, and other patterns in the balances to forecast future data. An appealing feature of time-series analysis is that BFTs can implement a wide range of potential time-series models to find the one that best fits the properties of a given set of balances. Forecast Pro calls this “expert selection” because the software does the selection for the user.
Exhibit 4 illustrates the application of time-series analysis to the balances. Forecast Pro tried a number of different time-series models to fit the balances and found an exponential smoothing model made the best fit. The statistical measures for this model, however, are not as good as those of the regression in Exhibit 2. Also, the forecasts (in Exhibit 5) are quite different from those for regression (in Exhibit 3). For other data, however, the time-series analysis might have given better results.
Time-series methods have other useful applications. For example, time-series methods can forecast the values of each of the regression’s predictor variables (operating expenses, selling expenses, or local business index) for the current audit period, whose comparisons to their current actual values could provide evidence concerning the reliability of current actual values. This additional step recognizes that the reliability of the predictor variables is critical to the effectiveness of the analytical procedure. Consideration of both regression and time-series forecasts is often the best policy.
Microsoft Excel cannot be used to develop time-series predictions because Excel provides few time-series procedures and has no internal program to differentiate the best time-series model. Users will find a BFT system to be more suitable.
The CPA Journal is broadly recognized as an outstanding, technical-refereed publication aimed at public practitioners, management, educators, and other accounting professionals. It is edited by CPAs for CPAs. Our goal is to provide CPAs and other accounting professionals with the information and news to enable them to be successful accountants, managers, and executives in today's practice environments.
Visit the new cpajournal.com.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}