Question

the table shows total sales of a US retailer

2. The table below shows total sales of a US Retailer (t is the number of years since 2012). 2022 Year (t) 0 1 2 3 4 15. I 671 Total Sales (y) ($ millions) 112 110 109 106 102 103 100 100 99 REQUIRED: 3) Use technology to find the linear regression line and R-squared (4 points) Regression Line y=-1.9405 x + 111.9167 R-Squared 82 -0.9592 Since the in iso = tell the negative relatiship bw 2 varios b) Interpret the slope of the line (2 points) - - 19405 Hell how much dia for cuery dollar inflege / decrease. slope. m-44 but from the regression the slope . Ax = 1440 5x + 14.976 The slone is at c) Use the regression line to estimate the retailer's total sales in 2022 (3 by=-1.9405x+111.9167 1+ 1/ 7

Answer 1

a)

The general form of the linear regression equation is given as

Y = b₀ +b₁ X

where b₀ = intercept

b₁ = slope

Y = dependent variable = Total sales

X = independent variable = Time period (t)

The regression equation is obtained as

Regression line : Y = 111.9167 - 1.94048 X or Y = 111.9167 - 1.9405 X

R squared : 0.959204 or 0.9592

To estimate the value of the intercept and slope, we can use excel and perform regression analysis to find out the values.

In excel, go to data, choose data analysis and select regression.

E Data Analysis ? Solver VaReapply Refresh Excell03-Advanced Exercises - Excel File Home Insert Page Layout Formulas Data Review View Power Pivot f Tell me what you want to do. 2 Show Queries Connections ALZA 7 Clear E Flash Fill - Consolidate e é Group From Table E Properties I'Remove Duplicates & Relationships Ungroup » Get External New Text to What If Forecast Data" Query v Co Recent Sources All Edit Links Advanced Columns Data Validation Manage Data Model Analysis Sheet Subtotal Get & Transform Connections Sort & Filter Data Tools Outline 83 X v 112 2 A B C D E F G H I J K L M N O P Q Al Sort Filter Analyze R S 2 Year 9 X Data Analysis Total sales 0 112! 1 110 2 109 3 4 5 Cancel 8 5 6 7 Analysis Tools Histogram Moving Average Random Number Generation Rank and Percentile Regression Sampling t-Test: Paired Two Sample for Means t-Test: Two-Sample Assuming Equal Variances t-Test: Two-Sample Assuming Unequal Variances z-Test: Two Sample for Means 99

Review View Fille Home Insert Page Layout Formulas Data a Show Queries Connections All From Table - Properties Get External New Refresh Data" Query- Do Recent Sources All Do Edit Links Get & Transform Connections Power Pivot & Tell me what you want to do... Clear E Flash Fill -- Consolidate Reapply 3-4 Remove Duplicates Relationships Text to Advanced Columns Data Validation Manage Data Model Data Tools Data Analysis ? Solver 12 Sort Filter What If Forecast Analysis Sheet Forecast é Group éË Ungroup Subtotal Outline Sort & Filter Analyze H10 X 7 Regression K L M N O P Q R S Input Input Y Range - OK S853:5B510 4 A B 1 2 Year() Total sales (V) 0 112 1 110 2 Cancel Input X Range SA$3:$A$10 $ Help 109 WN Labels Confidence Level: Constant is Zero 95 % 106 102 5 103 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 Regression Statistics Multiple R 0.97939 R Square 0.959204 Adjusted F 0.952405 Standard E 1.058788 Observatio 8 ANOVA df SS MS 1 158.1488 158.1488 6 6.72619 1.121032 7 164.875 f ignificance F 141.0743 2.16E-05 Regression Residual Total Coefficientstandard Errt Stat P-value Lower 95% Upper 95% ower 95.0%loper 95.09 Intercept 111.9167 0.683445 163.7538 3.5E-12 110.2443 113.589 110.2443 113.589 X Variable -1.94048 0.163375 -11.8775 2.16E-05 -2.34024 -1.54071 -2.34024 -1.54071

-------------------------------------------------------------------------------------------

b)

The slope of the regression line is negative which indicates a negative relationship between the year and total sales. It suggests that each successive year starting from 2012, the total sales of a US retailer is decreasing. The negative sign of the slope in the regression line signifies the negative relationship. The slope of the line is -1.9405 which means that for each successive year, the sales will decline by - 1.9405 $\times$ 1,000,000 = - $ 1,940,500

----------------------------------------------------------------------------------------------------------------

c)

To estimate the retailer total sales in 2022.

Year 2022 corresponds to period 10

Y = 111.9167 - 1.9405 X

Y = 111.9167 - 1.9405 $\times$ 10

Y = 92.51 million