Question

101-131, Management Accounting Ex. 6-27A Insert Insert blue yellow cells with formulas or cell references cells with text or numbers Fill in the table below with the data from the exercise in the textbook Month January February March April May June July Miles Driven Van Operating Costs 15,500 $5,390 17,400 $5,280 15,400 $4,960 16,300 $5,340 16,500 $5,450 15,200 $5,230 14,400 $4,680

Answer 1

To perform the regression analysis in excel, go to data , data analysis and choose regression.

Svar 5 InsertingAndFormattingText - Excel File Home Insert Page Layout Formulas Data Review Connections . LS Properties View Developer Help Power Pivot Tell me what y X Clear E Flash Fill -o com - Remove Duplicates of Rela Filter Text to V: Advanced Columns E Data Validation - Ma Sort & Filter Data Tools We Reapply Get External Data E Show Queries From Table Query Lo Recent Sources Get & Transform 2 Sort Refresh All Edit Links Connections B2 x ✓ fx Regression Input Input Y Range: OK SCS2:SCS8 Cancel Input X Range: $B$2:$B58 Help 1 Month 2 January 3 February 4 March 5 April 6 May 7 June 8 July Miles driven van operating costs 15,500 $5,390 17,4001 $5,280 15,400 $4,960 16,300 $5,340 16,500 $5,450 15,200 $5,230 14,4001 $4,680 Labels Confidence Level: Constant is Zero 95 % Output options Output Range: New Worksheet Ply: New Workbook Residuals Residuals Standardized Residuals Residual Plots Line Fit Plots Normal Probability Normal Probability Plots

SUMMARY OUTPUT $5,390 Month Miles drive Van operating costs January 15,500 February $5,280 March 15,400 $4,960 April 16,300 $5,340 16,500 $5,450 $5,230 July 14,400 $4,680 Regression Statistics MultipleR 0.688906607 R Square 0.474592313 Adjusted R S 0.369510775 Standard Err 218.1305117 Observation 7 May June 15,2001 ANOVA of significance F 0.086932425 0.19135833 X + 2163.804771 Regression Residual Total 1 5 6 SS MS F 214895.3992 214895.399 4.516419576 237904.6008 47580.9202 452800 Coefficients Intercept 2163.804771 X Variable 1 0.191358325 Standard Error Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 1426.351339 1.51702089 0.18970942-1502.748072 5830.35761 1502.748075830.357614 0.090043055 2.12518695 0.086932425 -0.040104716 0.42282137 -0.04010472 0.422821366 The R square describes the proportion of variation in the dependent variable that is explained by variation in the independent variable. R square is considered as measurement of goodness of fit in the data. Thus 47.46% of the variation in van operating costs can be explained by variation in miles driven. This is considered as a weak model because more than 50% variation in the van operating costs cannot be explained by variation in the miles drive. Hence the company should not rely on this estimate. + X 16500 0.1914 0.19135833 3158.1 5321.91 2163.81 2163.804771 2163.81

- Trace Precedents Codine leme Formulas for { 1.2ACRO . Calculate Na på Trace Dependents "Error Checking ' Watch Calculation Insert Function Calculate Sh AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference Trig Functions Function Library Use in Formula y Name Manager Create from Selection Defined Names Remove Arrows (8) Evaluate Formula Window Ontions Formula Auditing Calculation 133 x v fx Van operating costs SUMMARY OUTPUT 5390 1 Month 2 January 3 February 4 March 5 April 6 May 7 June 8 July Miles driven 15500 17400 15400 16300 16500 15200 14400 4960 5340 Regression Statistics Multiple R 0.688906606708991 R Square 0.474592312767297 Adjusted Square 0.369510775320756 Standard Error 218.1305117488 Observations 5450 5230 ANOVA Regression Residual 214895.399221032 237904.600778968 452800 NYS 214895.399221032 47580.9201557936 Total Intercept X Variable 1 Coeficients 2163.80477117819 .191358325219085 Sandard Ency 426.35133881671 0.0900430548712076 1.51702088559279 2.12518695076253 0 The square describe the dependent variable in the independent vari as measurement of go 47.46% of the variation explained by variation it a weak model because van operating costs ca the miles drive. Hence! this estimate This is considered as 0.1914 16500 = 118 3158.1 2163.81 1:117 2163.81 :D27.G27