Question

The following information on maintenance and repair costs and revenues for the last two years is available from the accounting records at Arnie’s Arcade & Video Palace. Arnie has asked you to help him understand the relation between business volume and maintenance and repair cost.

Month	Maintenance and Repair Cost ($000)	Revenues ($000)
July	$2.11	$55.00
August	3.08	49.00
September	2.60	45.00
October	2.01	61.00
November	2.10	73.00
December	0.94	101.00
January	2.92	41.00
February	2.96	47.00
March	2.82	57.00
April	2.78	59.00
May	1.84	63.00
June	1.58	75.00
July	2.40	69.00
August	1.82	63.00
September	2.37	71.00
October	2.18	73.00
November	1.23	83.00
December	0.56	113.00
January	2.38	57.00
February	2.08	59.00
March	1.49	79.00
April	1.75	83.00
May	1.75	69.00
June	1.45	65.00

Required:

Using Excel, estimate a linear regression with maintenance and repair cost as the dependent variable and revenue as the independent variable. (Negative amounts should be indicated by a minus sign. Round "Multiple R, R Square and Standard Error" to 7 decimal places, "Intercept and Revenues" to 4 decimal places.)

	Regression Statistics Multiple R R Square Standard Error Observations Coefficients Intercept Revenues

Answer 1

Step-1:

Regression Statistics Multiple R R Square Standard Error Observations Coefficients Intercept Revenues 0.8669249 0.7515588 331.4728810 24 4297.2874 -0.0335 [X Variable 1] Maintenance Month and Repair Revenues Cost July $2,110 $55,000 August $3,080 $49,000 September $2,600 $45,000 October $2,010 $61,000 November $2,100 $73,000 December $940 $101,000 January $2,920 $41,000 February $2,960 $47,000 March $2,820 $57,000 April $2,780 $59,000 May $1,840 $63,000 June $1,580 $75,000 July $2,400 $69,000 August $1,820 $63,000 September $2,370 $71,000 October $2,180 $73,000 November $1,230 $83,000 December $560 $113,000 January $2,380 $57,000 February $2,080 $59,000 March $1,490 $79,000 April $1,750 $83,000 May $1,750 $69,000 June $1,450 $65,000 Step-1: Insert data in an Excel sheet in cell numbers: A1 thru C25. Press 'Data' tab and then 'Data Analysis' option at the right-hand side on top of the page. Step-2: Press 'Regression' option and then 'OK' tab on the pop-up window. Step-3: Insert cell reference $B$2:$B$25 against 'Input Y Range' cell as Maintenance cost is the dependent variable. Insert cell reference $C$2:$C$25 against 'Input X Range' cell as Revenues is the independent variable. After that, press 'OK' tab on the pop-up window itself. Step-4 (Final): SUMMARY OUTPUT and ANOVA table will be generated by Excel in a separate sheet. From there, required results are traced.

File Home Insert Page Layout Formulas Data Review De 1 Data Analysis ELIFT View Tell me what you want to do.... Clear B Flash Fill -- Consolidate Is Reapply 5 Remove Duplicates Relationships Text to Advanced Columns Data Validation - Manage Data Model Sort & Filter Data Tools Show Queries From Table New Query - La Recent Sources Get & Transform Get External Data Connections 9 Properties Refresh All Edit linke Edit Links Connections Group - Ungroup -- Subtotal Āj Sort Filter What If Forecast Analysis · Sheet Forecast Outline Analysis E1 - X fx D E F G H I J K L M N O P Q R S T U А в C Maintenance Month and Repair Revenues 1 Cost 2 July $2,110.00 $55,000.00 3 August $3,080.00 $49,000.00 4 Septembe $2,600.00 $45,000.00 5 October $2,010.00 $61,000.00 6 November $2,100.00 $73,000.00 7 December $940.00 $101,000.00 8 January $2,920.00 $41,000.00 9 February $2,960.00 $47,000.00 10 March $2,820.00 $57,000.00 11 April $2,780.00 $59,000.00 12 May $1,840.00 $63,000.00 13 June $1,580.00 $75,000.00 14 July $2,400.00 $69,000.00 15 August $1,820.00 $63,000.00 16 Septembe $2,370.00 $71,000.00 17 October $2,180.00 $ 73,000.00 18 November $1,230.00 $83,000.00 19 December $560.00 $113,000.00 20 January $2,380.00 $57,000.00 21 February $2,080.00 $59,000.00 22 March $1,490.00 $79,000.00 23 April $1,750.00 $83,000.00 24 May $1,750.00 $69,000.00 25 June $1,450.00 $65,000.00

Step-2:

Step-3:

FEE * Clear E Data Analysis ED Group Ungroup -- Subtotal Outline TV Analysis R S T OK File Home Insert Page Layout Formulas Data Review View Tell me what you want to do... A Show Queries B Flash Fill |-- Consolidate Connections 21 ĀJA From Table E Properties Reapply 5 Remove Duplicates e Relationships Get External New Refresh Zj Sort Filter Text to What-If Forecast Query - La Recent Sources Data All Edit Links y Advanced Columns Data Validation - Manage Data Model Analysis Sheet Get & Transform Connections Sort & Filter Data Tools Forecast E1 - X foc A B C D E F G H I J K L M N O P Q Maintenance Month and Repair Revenues Cost 2 July $2,110.00 $55,000.00 Data Analysis 3 August $3,080.00 $49,000.00 4 Septembe $2,600.00 $45,000.00 Analysis Tools 5 October $2,010.00 $61,000.00 Histogram 6 November $2,100.00 $73,000.00 Moving Average 7 December $940.00 $101,000.00 Random Number Generation 8 January $2,920.00 $41,000.00 Rank andrcentile 9 February $2,960.00 $47,000.00 Regression Sampling 10 March $2,820.00 $57,000.00 t-Test: Paired Two Sample for Means 11 April $2,780.00 $59,000.00 t-Test: Two-Sample Assuming Equal Variances 12 May $1,840.00 $63,000.00 t-Test: Two-Sample Assuming Unequal Variances 13 June $1,580.00 $75,000.00 z-Test: Two Sample for Means 14 July $2,400.00 $69,000.00 15 August $1,820.00 $63,000.00 16 Septembe $2,370.00 $71,000.00 17 October $2,180.00 $73,000.00 18 November $1,230.00 $83,000.00 19 December $560.00 $113,000.00 20 January $2,380.00 $57,000.00 21 February $2,080.00 $59,000.00 22 March $1,490.00 $79,000.00 23 April $1,750.00 $83,000.00 24 May $1,750.00 $69,000.00 25 June $1,450.00 $65,000.00 Cancel Help

Home Insert Page Layout Formulas Data Review Clear E Data Analysis ZU Á A Show Queries From Table Get External New Data Query - La Recent Sources Get & Transform A Connections 9 Properties Refresh All Edit Links Connections View Tell me what you want to do... 2 Flash Fill --Consolidate VaReapply 5 Remove Duplicates e Relationships Filter Text to y Advanced Columns Data Validation Manage Data Model Sort & Filter Data Tools Group Ungroup - Subtotal Āj Sort What If Forecast Analysis Sheet Forecast Outline Analysis Regression ? x D E F G H I J K Input ок Input Y Range: $B$2:$B$25 Cancel Input X Range: $C$2:$C$25 Help Constant is Zero Labels Confidence Level: C2 : X V fic 2 A B C Maintenance Month and Repair Revenues Cost 2 July $2,110.00 $55,000.00 3 August $3,080.00 $49,000.00 4 Septembe $2,600.00 $45,000.00 5 October $2,010.00 $61,000.00 6 November $2,100.00 $73,000.00 7 December $940.00 $ 101,000.00 8 January $2,920.00 $41,000.00 9 February $2,960.00 $47,000.00 10 March $2,820.00 $57,000.00 11 April $2,780.00 $59,000.00 12 May $1,840.00 $63,000.00 13 June $1,580.00 i $75,000.00 14 July $2,400.00 $69,000.00 15 August $1,820.00 $63,000.00 16 Septembe $2,370.00 $71,000.00 17 October $2,180.00 $73,000.00 18 November $1,230.00 $83,000.00 19 December $560.00 i$ 113,000.00 20 January $2,380.00 $57,000.00 21 February $2,080.00 $59,000.00 22 March $1,490.00 $79,000.00 23 April $1,750.00 $83,000.00 24 May $1,750.00 $69,000.00 25 June $1,450.00 | $65,000.00 Output options O Qutput Range: O New Worksheet Ply: New Workbook Residuals Residuals Standardized Residuals Residual Plots Line Fit Plots Normal Probability Normal Probability Plots
Step-4:

Review E Data Analysis File Home Insert Page Layout Formulas Data Show Queries Connections From Table 9 Properties Get External New Refresh Data - Query - La Recent Sources All - Edit Links Get & Transform Connections View Tell me what you want to do... Clear E Flash Fill 1- Consolidate Reapply -1 Remove Duplicates Relationships Filter Text to Advanced Columns Data Validation Manage Data Model Sort & Filter Data Tools Zj Sort What-If Forecast Analysis Sheet Forecast Group Ungroup -- Subtotal Outline Analysis K11 в с D E F G H I J K L M SUMMARY OUTPUT Regression Statistics Multiple R 0.866924883 R Square 0.751558753 6 Adiusted R Sauare 0.740265969 Standard Error 331.472881 Observations 24 10 ANOVA 11 12 Regression 13 Residual 14 Total Significance F 4.26059E-08 1 22 23 SS MS 7312366.042 7312366.042 66.5521235 2417233.958 109874.2708 9729600 15 16 17 Intercept 18 X Variable 1 Coefficients 4297.287377 -0.033499936 Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 283.6599802 15.14943128 4.02332E-13 3709.012584 4885.56217 3709.012584 4885.56217 0.004106417 -8.157948486 4.26059E-08 -0.042016123 -0.024983749 -0.042016123 -0.024983749 22