Question

4. Draw the trend analysis based on following sales data for four years and analyse the fifth-year projected sales. Mention the possible relationship between the sales and the year on the basis of linear regression. Also mention the R-square value and its interpretation.

Year	Sales
1	900
2	1100
3	1200
4	1450
5

Answer 1

As per the Trend analysis the projected Sales for 5th year is 1600 (as highlighted below)

Year	Sales
1	900
2	1100
3	1200
4	1450
5	1600

Assuming the linear regression formula for the sales data is Y (Sales) = a*X(Year) + b

where a,b = coefficient, constant

Substituting the resp. year and sales value we get below linear equation

Y (Sales) = 175 * X(Year) + 725

R-squared is the percentage of the dependent variable variation that a linear model explains. The most common interpretation of r-squared is how well the regression model fits the observed data. For example, an r-squared of 60% reveals that 60% of the data fit the regression model. Generally, a higher r-squared indicates a better fit for the model.

For our case for the given linear equation Y = 175X + 725 we get R-Square of 98.79% therby denoting how well the regression model fits the observed data.

To evaluate R-square of the values use RSQ function (=RSQ(B2:B6,A2:A6)) in excel where B2:B6 is the Sales range and A2:A6 is the Year range.