Question

antain viruses. Unless you need to edit, its safer to stay in Protected View in Duntested View Enable Editing Question 6. Your organization is consider mg the purchase of a computer-controlled lathe for its ufacturing facility. The issue of maintenance cost has come up and you have been tasked with forecasting the maintenance cost of the machine based on hours used. The following data were collected from the vendor that shows weekly usage verses annual maintenance costs. Develop a regression model, using Excel (either data analysis or the formulas 3-8, 3-9 & 3-11) displaying the generated regression formula, r-value and the forecast cost if weekly usage is 10 hours/week Weekly Usage (hours) 13 10 20 Annual Maintenance Costs ($00) 17.0 2220 30.0 17 47.0 30.5 32.5 39.0 51.5 400 31 40 38 often a $OBN

Answer 1

Step: 1

weekly usage (hours) annual maintena nce costs ? X Гок Cancel 30 Data Analysis Analysis Tools Exponential Smoothing F-Test Two-Sample for Variances Fourier Analysis Histogram Moving Average Random Number Generation Rank and Percentile Regression Sampling t-Test: Paired Two Sample for Means Help 30.5 32.5 39 51.5 40 38

Step: 2

Regression Input Input Y Range: ок | $B$2:$B$11 SA$2:$A$11 Cancel Input X Range: weekly usage (hours) annual maintena nce costs Help Labels Confidence Level: Constant is Zero 95 % 17 30 50511 28 37 Output options O Qutput Range: O New Worksheet Ply: New Workbook Residuals Residuals Standardized Residuals 30.5 32.5 39 Residual Plots Line Fit Plots 51.5 40 Normal Probability Normal Probability Plots

Step 3

28 32 17 37 47 30.5 32.5 Regression Statistics Multiple F 0.925298 R Square 0.856177 Adjustedi 0.838199 Standard I 4.249618 Observati 10 39 ANOVA 51.5 40 df Regressio Residual Total SS MS F gnificance F 1 860.0509 860.0509 47.62383 0.000124 8 144.4741 18.05926 91004.525 Coefficientsandard Err t Stat P-value Lower 95%Upper 95%ower 95.0%pper 95.0% Intercept 10.52796 3.744867 2.811303 0.022797 1.892277 19.16364 1.892277 19.16364 X Variable 0.95344 0.13816 6.901002 0.000124 0.634844 1.272037 0.634844 1.272037

Regression equation:

Y = b + ax

Y = 11 + 0.95X

For 10 hours

Y = 11+(0.95*10) = 11+9.50 = 20.50

(Intercept gives fixed costs and X variable gives variable cost per unit)t

R-square = 86%