Academic journal article Management Accounting Quarterly

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

Academic journal article Management Accounting Quarterly

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

Article excerpt

(ProQuest: ... denotes formulae omitted.)

Cost estimation is one of the most fundamental tools in the management accountant's toolkit. It is used for establishing predetermined overhead application rates for product costing purposes, preparing budgets (both financial and operating), decision making (such as projecting costs associated with various decision alternatives), and contract bidding. Mechanisms for generating cost estimates range from the relatively simple-like professional judgment and intuition or the use of a scattergraph-to the sophisticated, such as the use of regression analysis.

Microsoft Excel is a flexible and powerful tool that can be used to help generate cost estimates for managerial planning and decision making. For example, Excel can be used to fit alternative regression models to a set of past observations of a cost and one or more cost drivers (i.e., independent or explanatory variables). Yet the use and interpretation of output from regression models can be complex and intimidating to some.

This article is the first in a two-part series that aims to help demystify the cost-estimation process using Excel in a managerial accounting setting. Part 1 illustrates the estimation of a simple (i.e., one-variable) linear cost function using two different methods in Excel: (1) a chart and its related functionality, and (2) the Regression analysis tool in the Analysis ToolPak.1 Part 2 covers the estimation of learning curve models (one form of a nonlinear model) and the use of linear regression models that include more than a single independent variable.

This series should be of interest for managers who require decision-useful information, management accountants charged with the responsibility of supplying that information, and accounting students who did not have prior exposure to the use of Excel for estimating cost functions or who found that exposure (e.g., in statistics class) less than satisfying.

To illustrate the process, we will use a hypothetical example of a package-delivery service located in the Charlotte, N.C., metropolitan area.2 With a fleet of four small vans and six pickup trucks, the company spends a considerable amount of money on vehicle upkeep. Figure 1 shows a screenshot of the monthly data from the past year in Excel, including the total vehicle expenses vs. number of deliveries.

It is possible to fit a one-variable linear regression model to the data in Figure 1 using Excel. This model could then be used to estimate monthly vehicle upkeep costs for the coming year. The functional form of the model to be estimated is: Y = a + bX, where Y is the monthly vehicle upkeep cost (the dependent variable), X is the number of deliveries per month (the independent variable), a is the fixed cost component of total monthly cost, and b is the variable cost rate (the slope of the cost function or, equivalently, the rate of change in Y per unit change in X).

GRAPHING THE DATA

The first step in fitting a regression equation to the data set in Figure 1 is to plot the data in a scattergraph in Excel (see Figure 2).3 This initial step serves three important purposes:

1.The chart provides a visual means for assessing whether a linear relationship between the two variables (X and Y) is plausible.

2. It provides a visual means for identifying seasonal patterns, which is an issue when time-series data is used to estimate the cost function.

3. The chart allows the user to isolate any possible atypical or abnormal observations. This ability is critical because regression estimates of the cost coefficients (a and b) are sensitive to the existence of outlier observations, as we will see.

Looking at Figure 2, it appears the data doesn't have drastic swings (which suggests that a linear cost function is plausible) and that there are no apparent outliers in the data set. With that established, the next step is to begin the cost estimation. Excel provides several options for fitting a linear regression model to a set of observations. …

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.