Academic journal article Management Accounting Quarterly

Using Excel 2013 for Regression-Based Cost Estimation: Part 2

Academic journal article Management Accounting Quarterly

Using Excel 2013 for Regression-Based Cost Estimation: Part 2

Article excerpt

Part 1 of this series demonstrated two methods in Excel 2013 for estimating a simple (i.e., one-variable) linear regression model using the example of a hypothetical package-delivery company. The dependent variable (Y) was the company's monthly vehicle expense, and the independent (or, explanatory) variable (X) was the number of deliveries per month.

Part 2 extends the application of Excel to estimate and interpret the output from a multiple-regression model--in other words, a cost function that includes more than a single independent variable--and a learning curve model, which is one form of a nonlinear cost function. (1) This involves fitting a multiple (i.e., more than one variable) linear regression model to a new data set. This approach is helpful when more than one X variable helps to explain variations in the dependent variable.

The overall purpose of these articles is to demystify the use of Excel 2013 for estimating a variety of cost functions. (2) They should be of interest to managers who require decisionuseful information, such as estimated cost data generated by regression models; managerial accountants charged with the responsibility of supplying that information; accounting students who are looking for user-friendly resources that can be used to learn advanced topics in the area of cost estimation using Excel; and individuals preparing for the CMA[R] (Certified Management Accountant) exam. (3)

A MULTIPLE LINEAR REGRESSION MODEL USING EXCEL 2013

To illustrate the multiple linear regression model, we will use the hypothetical example of a nursery and retail store specializing in house and garden plants and supplies. (4) The data is presented in Figure 1. (5) Using Excel, the data in Figure 1 will be used to estimate a linear cost function where the dependent variable (Y) represents the monthly supplies expense and where there are three independent variables: monthly sales in units ([X.sub.1]), monthly sales dollars ([x.sub.2]), and December ([X.sub.3]). (6) Data for the dependent variable (Y) is in Column E, while data for [X.sub.1], [x.sub.2], and [X.sub.3] is in Columns B, C, and D, respectively. The goal is to develop a multiple regression model based on the 31 months of data (June 2012 through December 2014) presented in Figure 1. This model will be used to predict monthly supplies expense for each month of the upcoming year (2015) based on projected values for each of the three independent variables, as shown in Columns B, C, and D of Figure 2.

We consider the monthly sales in units ([X.sub.1]) and monthly sales dollars ([x.sub.2]) to be continuous variables, meaning that we will look at one-unit changes in them for interpretation. December ([X.sub.3]) is a dummy variable, which means that its effect is only manifested in the month of December. The dummy variable takes a value of 1 for each observation in the data set from December and 0 for any other month. Dummy variables (season of the year, presence or absence of a medical procedure, order complexity, and so forth) that can potentially be used to improve the descriptive and predictive power of an estimated cost function. (7)

Running a multiple regression in Excel using the Regression analysis tool follows the same procedure as required for a simple, one-variable regression. The difference is the inclusion of two or more independent variables, which we denote as [X.sub.i] (i = 1 to n, where n = the number of independent variables). Excel has a built-in control feature that prevents users from including two columns for the Y (dependent) variable. One limitation in Excel's multiple regression procedure, however, is that it requires that all included independent (X) variables be in adjacent columns. When exploring sets of independent variables to include in the estimated model, you must be able to manipulate the spreadsheet in a way that preserves source data but allows for alternative setups. (8) In the present case, this means that independent variables need to be in adjacent columns. …

Search by... Author
Show... All Results Primary Sources Peer-reviewed

Oops!

An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.