cost accounting

InsightRegressionAnalysisFall20191.xlsx

Information

Information:
Your company is preparing an estimate of its production costs for the coming period. The controller estimates that direct materials costs are $50 per unit and that direct labor costs are $24 per hour. Estimating overhead, which is applied on the basis of direct labor costs, is difficult.
The controller’s office estimated overhead costs at $4,100 for fixed costs and $18 per unit for variable costs. Your colleague, Lance, who graduated from a rival school, has already done the analysis and reports the "correct" cost equation as follows:
Overhead = $10,855 + $15.82 per unit
Lance also reports that the correlation coefficient for the regression is 0.81 and says, “With 81% of the variation in overhead explained by the equation, it certainly should be adopted as the best basis for estimating costs.”
When asked for the data used to generate the regression, Lance produces the following:
Month	Overhead	Unit Production
1	$57,556	3,080
2	60,781	3,290
3	77,273	4,220
4	56,594	3,050
5	81,800	3,450
6	72,347	3,960
7	63,969	3,380
8	73,547	4,060
9	77,612	4,170
10	60,247	3,240
11	61,528	3,410
12	73,884	4,130
13	73,316	3,930
The company controller is somewhat surprised that the cost estimates are so different. You have therefore been assigned to check Lance’s equation. You accept the assignment with glee.

Question 1:
Prepare a scattergraph relating overhead cost to the number of units produced.
Month	Overhead	Unit Production	Produce your scattergraph below this line:
1	$57,556	3,080	Hint: Select your data. Then, go to "Insert" to produce the scattergraph table.
2	60,781	3,290
3	77,273	4,220
4	56,594	3,050
5	81,800	3,450
6	72,347	3,960
7	63,969	3,380
8	73,547	4,060
9	77,612	4,170
10	60,247	3,240
11	61,528	3,410
12	73,884	4,130
13	73,316	3,930

Question 2:
Run regression analysis based on the original data with an outlier
			Now Run the regression with the data:
			Hint: Click on "File". Go to Excel "Options" . Click on "Add-In", select "AnalysisToolPak", and press OK. You will find "Data Analysis" appearing on the right side of the "DATA". Run DataAnalysis and select regression. Inside the regression panel, select data for Y (B6-B19) and X (C6-C19). Check "labels" and "Line Fit Plot". Specify "Output Range" as $E$7. Then, run the regression.
Month	Overhead	Unit Production
1	$57,556	3,080
2	60,781	3,290
3	77,273	4,220
4	56,594	3,050
5	81,800	3,450
6	72,347	3,960
7	63,969	3,380
8	73,547	4,060
9	77,612	4,170
10	60,247	3,240
11	61,528	3,410
12	73,884	4,130
13	73,316	3,930
			Which observation is the outlier? (Hint: the outlier has the highest residual based on the RESIDUAL OUTPUT)
			Month	Overhead	Unit Production
			Now complete the following cost equation and adjusted R square based on the regression result
			Y =	a	+	b	X
			Y =		+		X
			Adjusted R square =
			Is this result the same as what Lance found?
			Please mark only one using X
			Yes
			No

Question 3:
Run regression analysis based on the NEW data without an outlier
Now, delete the ENIRE ROW of the outlier observation (click on the row and use the right key of your mouse to delete it)
Month	Overhead	Unit Production
1	$57,556	3,080
2	60,781	3,290
3	77,273	4,220
4	56,594	3,050
5	81,800	3,450
6	72,347	3,960
7	63,969	3,380
8	73,547	4,060
9	77,612	4,170
10	60,247	3,240
11	61,528	3,410
12	73,884	4,130
13	73,316	3,930
			Run the regression with the NEW data:
			Run DataAnalysis and select regression. Inside the regression panel, select data for Y and X including the labels. Check "labels" and "Line Fit Plot". Specify "Output Range" as $E$24. Then, run the regression.
			Now complete the following cost equation and adjusted R square based on the NEW regression result
			Y =	a	+	b	X
			Y =		+		X
			Adjusted R square =
			Is this result better than what Lance found?
			Your comments: