# Super accounting: wow clients with analytical skills that are faster than a speeding bullet, more powerful than a locomotive.

The challenge of today's competitive economic environment requires that accountants be flexible and help their clients respond to problems and opportunities. As an accountant, you must assist your clients in understanding their businesses as well as meeting the challenges of their marketplace.To effectively meet client needs you must possess a strong accounting background coupled with the ability to readily call upon an arsenal of technical and economic tools. However, mastery of the basic accounting principles does not guarantee success. Today's dynamic business environment requires more than the ability to prepare basic financial statements and tax returns in order to gain and retain clients. To be successful you must be a proactive agent of change for your clients. Demanding clients expect that you interpret financial information and provide advice on how they can improve their cash flow and operating performance.

Statistical techniques such as regression analysis are tools of action that enable accountants to make financial data meaningful to their clients. Through regression analysis the client gains an understanding of the underlying forces driving revenues and expenses. Regression analysis allows the accountant to express the client's cost and revenue structure in the form of a mathematical equation that can be used to forecast the potential impact of future events. Such information is useful to clients in planning, controlling and making decisions concerning their operations.

Many accountants have avoided using statistical techniques such as regression analysis, erroneously believing that the computational complexity outweighs the benefits. However, this is simply not true. Spreadsheet packages such as Lotus 1-2-3 make regression analysis a simple, quick and useful tool for accountants and their clients. The purpose of this article is to generate an understanding of the nature of regression analysis and its usefulness in modelling a variety of financial and operational dimensions of the firm. An example involving the determination of an appropriate overhead rate is given to enhance our understanding of regression analysis.

Regression Analysis as an Accounting Tool

A number of methods are available to identify cost behavior patterns. These methods include simple plotting of data, account analysis, two-point estimation and regression analysis. Each of these techniques attempts to either manually or mathematically fit a straight line through a series of data points and thereby provide an estimate of the causal relationship between the dependent and independent variables.

Regression analysis possesses certain advantages over other estimation methods. With regression analysis the cost line is positioned in order to minimize the sum of the vertical distance between the cost line and the data points. Statistically, regression analysis finds the best estimates for the slope (variable cost) and the y-intercept (fixed cost) of the line. Additionally, regression analysis can be used to estimate either linear or curvilinear cost and revenue behavior patterns.

In designing a regression model, care should be taken in selecting the dependent and independent variables. Regression analysis is a "blind" statistical technique and you should not delegate your judgment to it. Mechanically, regression analysis can be used to express a mathematical relationship between almost any set of variables. It can be used to mathematically represent nonsensical relationships. For example, a regression equation can be developed that relates sunspot activity with stock market prices!

In any organization managers need to understand the relationship between cost and activity. With knowledge of the firm's cost behavior patterns, managers are able to take advantage of a number of planning and controlling techniques including:

* Cost-Volume Profit Analysis is a planning technique that enables the accountant to answer management's "what if" questions. CVP Analysis recasts the traditional functional income statement on the basis cost behavior patterns and provides management with a means for understanding the impact of various events on profitability.

* Flexible Budgets are detailed plans that can be adjusted to reflect what costs should be at various activity levels. Flexible budgets enable management to focus their attention on the controllable elements of performance.

* Work Measurement is the systematic analysis of a task to determine the inputs required to perform the task. For example, cost behavior estimation techniques are used by bank management to understand the time and cost elements of processing loan applications.

* Identification of cost drivers in activity based costing requires the use of cost estimation techniques. Regression analysis allows the accountant to identify as well as evaluate the relationship between a manufacturing cost and various cost drivers.

Regression Analysis: As Easy As 1-2-3

Computerized spreadsheets such as Lotus 1-2-3 make regression analysis a quick and easy tool for the accountant. To employ regression analysis, you must first activate the Lotus 1-2-3 spreadsheet (version 2.2 or higher) on your personal computer. Once the spreadsheet appears on your monitor, perform the following steps:

1. In column A enter the values of the dependent variable and in column B enter the values of the independent variable(s). If you are using multiple regression you will enter the second independent variable in column C, the third independent variable in column D, etc.

2. Type /DR to activate the regression analysis program.

3. Place the cursor on the "Y-Range" option, press the return key and type the range of observations for the dependent variable (e.g., 12 observations are entered in cells A1...A12). Press the return key.

4. Place the cursor on the "X-Range" option, press the return key and then type the range of observations for the independent variable(s) (e.g., for simple regression 12 observations are entered in cells B1...B12 and for multiple regression B1...C12). Press the return key.

5. Move the cursor to the "Output-Range" option, press the return key and then type in the desired starting cell for the results (e.g., A15).

6. Move the cursor to the "Go" option and press the return key to run the regression analysis. At this point the regression results will appear on the screen.

Making Regression Analysis Work for You

The following example involving the determination of an appropriate overhead rate is presented to enhance our understanding of regression analysis.

To plan and control their manufacturing operations a client needs to understand the cost behavior pattern of their overhead costs. The accountant believes that overhead costs are driven by either units of output or machine hours. The accountant must determine the best measure of activity. Table 1 presents the monthly observations of the two possible cost drivers (independent variables) and overhead costs (dependent variable) that the accountant has collected for the past year.

Employing Lotus 1-2-3, the accountant estimates three overhead rates: (1) one rate based on units of output, (2) a second rate based on both units of output and machine hours, and (3) a third rate based on machine hours. Referring to the Lotus 1-2-3-generated regression output presented in Exhibits 2, 3 and 4, the "Constant" represents the estimated fixed component of overhead costs, while the "X Coefficient(s)" is the estimated variable overhead rate. The estimated linear regression equation for overhead costs (OH) with units of output designated as the cost driver is: OH = $89108.69 + $5.105757 (units of output). The estimated multiple regression equation for overhead costs using both units of output and machine hours as the independent variables is: OH = $53685.21 + $0.706112 (units of output) + $8.617027 (machine hours). Finally, using only machine hours as the cost driver, the linear regression equation is: OH = $51940.82 + $9.343195 (machine hours).

Exhibit 2: Linear Regression Results for Overhad Costs and Units of Output Constant 89108.69 Std Err of Y Est 8958.902 R Squared 0.525077 No of Observations 12 Degrees of Freedom 10 X Coefficient(s) 5.105757 Std Err of Coef 1.535536 Table 1: Data for Overhead Rate Example Overhead Units of Machine Month Costs Output Hours January 94000 2000 4800 February 96000 4000 4500 March 112000 2100 5600 April 108000 2400 5600 May 114000 4700 7000 June 130000 7600 8000 July 128000 6200 8000 August 112000 3500 6800 September 108000 1900 6200 October 106000 4000 5600 November 95000 3000 5600 December 93000 3000 4300

The obvious question is: Which equation is the "best?" The Lotus 1-2-3-generated regression results yield additional measures to help us evaluate the relative usefulness of the alternative equations. First, "R squared" or the coefficient of determination provides an indication of how well the equation represents the data. "R squared" indicates the percentage of variation in the dependent variable (overhead) that can be explained by the independent variable(s). This measure ranges from 0.0 (little or no relationship) to 1.0 (a strong relationship between the independent and dependent variables).

The results indicate an "R squared" of 0.525 for the linear equation using units of output as the cost driver. The "R squared" measure increases to 0.877 when machine hours are added as a second independent variable to the equation. When units of output is dropped from the equation, leaving machine hours as the cost driver, the "R squared" changes only slightly to 0.872. It appears that units of output add little to the usefulness of the equation.

Exhibit 3: Multiple Regression Results for Overhead Costs and Units of Output, Machine Hours Constant 53685.21 Std Err of Y Est 4791.763 R Squared 0.877722 No of Observations 12 Degrees of Freedom 9 X Coefficient(s) 0.706112 8.617027 Std Err of Coeff 1.191761 1.691377

Additionally, the significance of the relationship between overhead cost and the individual independent variables can be determined through "t-values." The value is determined by dividing the "X Coefficient(s)" by the "Std Err of Coef." In general, the larger the absolute "t-value," the greater the significance between the dependent and independent variable.

The results indicate a "t-value" of 3.3251 for the linear relationship between overhead costs and units of output. In the multiple regression equation, the "t-value" for units of output is not significant at 0.3678, while for machine hours at the measure is a significant 5.09467. When units of output is dropped from the equation, there is a significant "t-value" of 7.3481 between overhead costs and machine hours.

Based on the regression results and the foregoing analysis, the accountant should recommend that machine hours be selected as the cost driver to apply overhead costs to production. Relative to units of output machine hours explains more of the variation in overhead and exhibits a higher level of significance.

Exhibit 4: Linear Regression Results for Overhead Costs with Machine Hours Constant 51940.82 Std Err of Y Est 4633.675 R Squared 0.872952 No of Observations 12 Degrees of Freedom 10 X Coefficient(s) 9.343195 Std Err of Coef 1.127151

A Tool for Action

To achieve success in today's competitive economic environment, you need to be more for your clients than an "independent accountant." You must be a business confidant and an agent for change. As spreadsheets replaced paper, pencils and ten-key adding machines, your capabilities to provide your clients with relevant, reliable and timely information have expanded exponentially. To realize your potential as an accountant you must take advantage of all tools available to you. Regression analysis is such a tool and can be used to assist you in providing a valuable service to your clients.

Dorothy M. Fisher, PhD, is an assistant professor of computer information systems at California State University, Dominguez Hills. Her teaching and research interests are in the areas of software development, local area networks and decision support systems.

Steven A. Fisher, DBA, CMA, CPA, is a professor of accountancy at California State University, Long Beach. His teaching and research interests are in the areas of cost accounting and financial accounting.

Printer friendly Cite/link Email Feedback | |

Author: | Fisher, Dorothy M.; Fisher, Steven A. |
---|---|

Publication: | The National Public Accountant |

Date: | Aug 1, 1993 |

Words: | 1938 |

Previous Article: | Creating a new class of licensed accountant. |

Next Article: | Go with the flow: using ratios to analyze cash flow data. |

Topics: |