In: Statistics and Probability
Production volume
400
450
550
600
700
750
Total cost
4000
5000
5400
5900
6400
7000
An important application of regression analysis in accounting
is in the estimation of cost. By collecting data on volume and cost
and using the least squares method to develop an estimated
regression equation relating volume and cost, an accountant can
estimate the cost associated with a particular manufacturing
volume. Consider the following sample of production volumes and
total cost data for a manufacturing operation. Excel File: data12-21.xls
|
Answer:
Here let y is total cost and x is production volume.
First enter the given data in excel:
Production Volume(x) | Total cost(y) |
400 | 4000 |
450 | 5000 |
550 | 5400 |
600 | 5900 |
700 | 6400 |
750 | 7000 |
To use the regression analysis in excel use the following path:
Data> Data Analysis ( upper right corner) > select Regression > select range both x and y variables> Give output range> OK
The output as below:
SUMMARY OUTPUT | ||||||
Regression Statistics | ||||||
Multiple R | 0.979127101 | |||||
R Square | 0.958689879 | |||||
Adjusted R Square | 0.948362349 | |||||
Standard Error | 241.5229458 | |||||
Observations | 6 | |||||
ANOVA | ||||||
df | SS | MS | F | Significance F | ||
Regression | 1 | 5415000 | 5415000 | 92.82857 | 0.00064897 | |
Residual | 4 | 233333.3333 | 58333.33 | |||
Total | 5 | 5648333.333 | ||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
Intercept | 1246.666667 | 464.1599341 | 2.685856 | 0.054894 | -42.04791038 | 2535.381244 |
Production Volume(x) | 7.6 | 0.788810638 | 9.634759 | 0.000649 | 5.409910566 | 9.790089434 |
From output:
a)
The coefficients are
b0 = 1246.66667 = 1246.67 ... rounded to 2 decimals
b1 = 7.6
Hence regression is y^ = 1246.67 + 7.6*x
b)
Here slope coefficient is 7.6, hence variable cost is 7.6 per one unit is produced.
c)
From summary output in bold case
coefficient of determination = r2 = 0.95868 = 0.9587 ...( rounded to 4 decimals)
This interpreted as 95.87 % of variation of estimation of cost(y) is explained by production volume(x).
d)
The company's production schedule shows 500 units must be produced next month (i.e x = 500 )
Hence total cost = y = 1246.67 + 7.6 * 500
= 5046.67 $
R code and Output for reference:
> x=c(400,450,550,600,700,750)
> y=c(4000,5000,5400,5900,6400,7000)
> regression=lm(y~x)
> regression
Call:
lm(formula = y ~ x)
Coefficients:
(Intercept) x
1246.7 7.6
> summary(regression)
Call:
lm(formula = y ~ x)
Residuals:
1 2 3 4 5
-286.67 333.33 -26.67 93.33 -166.67
6
53.33
Coefficients:
Estimate Std. Error t value
(Intercept) 1246.6667 464.1599 2.686
x 7.6000 0.7888 9.635
Pr(>|t|)
(Intercept) 0.054894 .
x 0.000649 ***
---
Signif. codes:
0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1
‘ ’ 1
Residual standard error: 241.5 on 4
degrees of freedom
Multiple R-squared: 0.9587, Adjusted
R-squared: 0.9484
F-statistic: 92.83 on 1 and 4 DF, p-value: 0.000649
Coefficients and R squared are in bold case , which gives same output.