ANALYSIS OF COST BEHAVIOR USING

REGRESSION ANALYSIS ON ECXEL

Mohammad S. Bazaz

 

Assume we want to analyze the behavior of total expenses based upon sales volume. The following 12 months of data is available to us. The following is an Excel file.

 

                                                                                    A                      B

 

Units

Total

 

Sold

Expenses

1

76,506

$348,670

2

75,877

$352,090

3

78,679

$363,520

4

70,470

$347,290

5

67,699

$319,030

6

72,413

$331,630

7

80,629

$384,280

8

78,737

$411,730

9

74,299

$365,800

10

73,814

$416,980

11

67,870

$380,740

12

82,398

$424,210

 

Steps for Conducting Regression

  1. Load Excel program
  2. In Column A, type in your data on activity volume (in this case, for example, units sold)
  3. In Column B, type in your data on cost (in this case, for example, total expenses)
  4. In Tools pooled-down menu, select “Data Analysis” option, then select Regression option and Click O.K. key. (Note: If Data Analysis option does not exist, select Adds-In option instead.  Then click "Analysis Toolpak" box in order to add “Data Analysis” to the Tools' pooled-down menu).
  5. In "Input Y Range" box, type in the range for the cost data (example  B3:B14)
  6. In "Input X Range" box, type in the range for the data on the activity volume (example A3:A14)
  7. Click the icon for the "Output Range," then type in the desired beginning cell for the results (example A15).
  8. Click the O.K. button for conducting the regression analysis.

 

Analysis of results

Assume the following results are obtained from a regression analysis:

 

SUMMARYOUTPUT

Regression Statistics

Multiple R

0.568832

 

 

 

Coefficients

t Stat

Intercept

67637.23289

0.487542

X Variable 1

4.040871218

2.187122

 

Given the above data, the behavior of total expenses can be expressed as follow:

 

            Total Expenses = 67,637 +  4.04 x Unit Sold

 

That is, $67,637 is the amount of monthly expenses regardless of sales volume (also called fixed expenses). The variable expense per unit is $4.04 per unit sold.

 

Additional Analysis

According to the value of "R Square" (57%) total expenses has a relatively a moderate relationship with sales units. Note that, the value of R Square is between 0 and 100%.  The closer the value of R Square to 100%, the higher is the reliability of the relationship. 

 

According to the “t Stat.” Values, it is very likely that total expenses will change $4.04 when sales volume changes (increases or decreases) by one unit. As a generally accepted practice, if the absolute value of "t Stat" is greater than 2, then the estimated value is highly reliable. However, the likelihood of having $67,637 of fixed expenses per month is low as the “t Stat.” for it is less than 2.