Question

Bargain Rental Car offers rental cars in an off-airport location near a major tourist destination in California. Management would like to better understand the behavior of the company’s costs. One of those costs is the cost of washing cars. The company operates its own car wash facility in which each rental car that is returned is thoroughly cleaned before being released for rental to another customer. Management believes that the costs of operating the car wash should be related to the number of rental returns. Accordingly, the following data have been compiled:

  

Month	Rental Returns	Car Wash Costs
January	2,400	$	11,300
February	2,500	$	13,300
March	2,800	$	12,100
April	3,100	$	14,700
May	3,700	$	16,500
June	5,100	$	23,900
July	5,600	$	22,500
August	5,600	$	22,900
September	4,700	$	23,100
October	4,100	$	22,000
November	2,200	$	11,000
December	2,900	$	15,300

Using least-squares regression, estimate the fixed cost and variable cost elements of monthly car wash costs. The fixed cost element should be estimated to the nearest dollar and the variable cost element to the nearest cent.

Answer 1

Month	Rental Returns	Car Wash Costs
January	2,400	$                 11,300
February	2,500	$                 13,300
March	2,800	$                 12,100
April	3,100	$                 14,700
May	3,700	$                 16,500
June	5,100	$                 23,900
July	5,600	$                 22,500
August	5,600	$                 22,900
September	4,700	$                 23,100
October	4,100	$                 22,000
November	2,200	$                 11,000
December	2,900	$                 15,300
SUMMARY OUTPUT
Regression Statistics
Multiple R	0.947876356
R Square	0.898469586
Adjusted R Square	0.888316544
Standard Error	1709.211015
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	258522643.7	258522643.7	88.49265451	2.77535E-06
Residual	10	29214022.92	2921402.292
Total	11	287736666.7
	Coefficients
Intercept	3075.379274
X Variable 1	3.841061492
Steps involved: 1	First go to file, option and add in, in excel
	go to manage tab and select excel add in
	tick the analysis and select ok, it will enable data analysis in excel
2	Using the excel - Data - Data analysis - Regression function, we get Intercept as 3,075 and X variable as 3.84, i.e. Fixed cost is
	$3,075 and Variable cost per direct labor hour is $3.84
	Y = 3075 + 3.84X
	Fixed Cost per month			$                 3,075
	Variable cost per rental return			$                   3.84
	R-Square			90%

Plz hit like, if it helped.