Question

please help

Saginaw Valley State University Using Spreadsheets (Excento Solve a Business Problem The client hires your company to analyze repairs and maintenance costs. Your company assigns you the task of helping the client to determine the cost behavior for repairs and maintenance using a regression analysis. Your supervisor provides you some help by given you the following example, notes and instructions below: Repair and Maintenance Activity Machine Hours 5.000 6,000 7,500 8.000 Cost 32.000 35 300 38 650 39,450 45.000 40.000 30.000 25,000 20.000 15.000 Cost 5.000 0 2.000 8,000 10,000 4,000 6,000 Machine hours The chart bype shown above is the XY (scafford graph. Using excelgo to <DATA> then <Data Analysis then select <Regression> Use the Arrow keys to highlight the X Values machine hours) and "Y" values then hit "Output Range to indicate the placement of your regression statistics If your excel does not show "Data Analysis when you select <DATA> then you must select FILE then <OPTION> and then Add-ins, and highlight Tol Pax and then "Go check off the TOOLPAX, and the DATA ANALYSIS should show under DATA on the night. Answer: The following output would be shown. SUMMARY OUTPUT Regression Statistics Multiple R 0.9947427 R Square 0.9867299 Adjusted R Square 0.9800947 Standard Error Observations ANOVA Regression Residual Total 1 34461538.5 2 403461.538 3 34925000 34461638.46 231730.7692 F S ignificance F 148.71369290.008657257 Intercept X Variable 1 Concients Standard Error 20042 308 1358.75188 24615385 0 20185111 Stat 14.750528 1219482238 P-value 0.004584607 0.008657257 Lower 95% Upper 95% 14196.06613 25888 5493 1.593042618 333003431 INSTRUCTIONS: CLIENT DATA 1. Use the CLIENT DATA given to the right). Prepare a graph Machine and the Regression "Summary Output" using the client data. Hours Costs 2. Prepare a short report to your client explaning the cost behavior 6,500 $ 35,800 that is indicated by your regression output 3. Include the meaning of R Square in your comments. 9,200 $ 42,900 12,700 $ 4. Your client wants to know what to expect if the company 45,400 14,600 $ 51,500 reaches 18,750 machine hours. Provide your client with your calculation. This report should be prepared using excel and all comments/answers should be typed 17,600 $ 59,900 with your name typed in the upper-right hand corner of the page. Klacha

Answer 1

			X	Y
			Machine Hour	Costs							1
			6500	$35,800
			9200	$42,900
			12700	$45,400
			14600	$51,500
			17600	$59,900
		Click "Data"
		Click "Data Analysis"
		Select "Regression" tool
		Click"OK"
		Input y_Range (Costs)
		Input x_Range (Machine hour))
		Click "OK"
		Cost (Y)=Intercept(a)+X Variable(b)*Machine Hour(X)
		Intercept	22451.30
		X variable	2.03
		Y=22451.30+2.03X
		Fixed Cost per month	$22,451.30
		Variable Cost per machine hour	$2.03
		R square	0.956294539
		R square indicates coefficient of determination
		It indicates 95.6% of observations can be explained by the above regression equation
	4	For Machine hour =18750
		X=18750
		Y=22451.30+2.03*18750=	$60,583.57
		COSTS=$60,583.57
		SUMMARY OUTPUT
		Regression Statistics
		Multiple R	0.977903134
		R Square	0.956294539
		Adjusted R Square	0.941726051
		Standard Error	2197.336571
		Observations	5
		ANOVA
			df	SS	MS	F	Significance F
		Regression	1	316935136	316935136	65.64130714	0.003929933
		Residual	3	14484864.02	4828288.006
		Total	4	331420000
			Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
		Intercept	22451.29718	3197.091289	7.022413547	0.005931745	12276.72582	32625.87	12276.73	32625.87
		X Variable 1	2.033721355	0.251016829	8.101932309	0.003929933	1.234873776	2.832569	1.234874	2.832569