Data Analytics and Business Intelligence Report: Car Sales Analysis

This report presents a data analytics and business intelligence analysis of car sales data. It begins with a multiple regression model exploring the impact of trend and seasonality, evaluating the R-squared value and statistical significance of the coefficients for year and month. The analysis then simplifies the model by focusing on the trend represented by months, re-estimating the regression and assessing the model's statistical significance. A graphical representation illustrates the upward trend in new car sales, with minimal seasonality. The report then provides a forecast of car sales for year 3 using a regression model and a forecast function. Finally, it evaluates the accuracy of the forecasts using the Mean Absolute Error (MAE), comparing the performance of the two methods and concluding that the regression model provides a more accurate estimate. The report includes relevant references to support the analysis.

Running head: DATA ANALYTICS AND BUSINESS INTELLIGENCE
Data Analytics and Business Intelligence
Name of the Student
Name of the University
Student ID

1DATA ANALYTICS AND BUSINESS INTELLIGENCE
Table of Contents
Question 1........................................................................................................................................2
Question 2........................................................................................................................................4
Question 3........................................................................................................................................4
Question 4........................................................................................................................................5
Question 5........................................................................................................................................6
References........................................................................................................................................7

2DATA ANALYTICS AND BUSINESS INTELLIGENCE
Question 1
Table 1: Multiple regression model using trend and seasonality
New car sales=41079.1314+ ( 2928.1906 ×Year ) +(218.4633 × Month)
The multiple regression model shows the impact of trend and seasonality of new car
sales. R square value of the regression model is 0.6249. The trend and seasonality account for 62
percent variation in sales of new cars. The respective coefficients for year and month are
2928.1906 and 218.4633 respectively. Both the coefficients are positive implying new car dales
tend to increase with increase in months and years. P values for year and month are respectively
0.3622 and 0.3422. Both the p value exceeds the significance level having an alpha value of
0.05. Given the p values larger than significance level, the null hypothesis stating no statistical
significance of the regression coefficient has been accepted (Schroeder, Sjoquist & Stephan,
2016). This implies in the multiple regression model both year and months are statistically
insignificant. Comparison of two p values shows that p value of year is larger than the p values

3DATA ANALYTICS AND BUSINESS INTELLIGENCE
of month. The variable yea therefore can be dropped and model can be re-estimated using trend
only represented by months. Result of the new regression model is given in the following table
Table 2: Result of the regression using only trend
New car sales=43179.7899+(410.7934 × Month)
R square value of the new model is 0.6044. That means time trend represented by
months’ alone account for 60 percent variation in sales. From the ANOVA table, the significant
F value is obtained as 0.002 which is less than significance value at 5% level. The model
therefore is statistically significant (Darlington & Hayes, 2016). Coefficient of month is
401.7934. That means the data constitutes an upward rising trend. The value of new car sales
increases with increase in months. The obtained p value for month is 0.0018. The p value is less
than significance level suggesting rejection of null hypothesis of no statistical significance of
regression coefficient has been rejected. The time trend variable therefore is statistically
significant.

4DATA ANALYTICS AND BUSINESS INTELLIGENCE
Question 2
Figure 1: Trend in New Car Sales
The figure above shows trend in new car sales. The figure shows an overall upward rising
trend in the new car sales. Seasonality is not much prominent in the data set. The seasonal
variation is very small and almost smoothen over time indicating presence of trend (Tanaka,
2017). Therefore, from the regression and the graph above it can be said that the data on new car
sales constitutes a trend.
Question 3
The final regression model that can be used for predicting new car sales for year 3 is
New car sales=43179.7899+(410.7934 × Month)

5DATA ANALYTICS AND BUSINESS INTELLIGENCE
Table 3: Forecast of car sales in year 3
Question 4
Table 4: Forecast of car sales in year 3 using forecast function

6DATA ANALYTICS AND BUSINESS INTELLIGENCE
Question 5
Mean Absolute Error (MAE) is the simplest measure for estimating accuracy of a
forecast. It is the average of absolute forecast error obtained as an absolute difference between
actual and forecasted value (Franses, 2016).
Table 3: MAE of the two methods
Between the two method, MAE is relatively smaller for forecasted value obtained from
regression model. This indicates the regression estimate gives more accurate value of actual sales
for year 3 compared to that obtained from forecast function.

7DATA ANALYTICS AND BUSINESS INTELLIGENCE
References
Darlington, R. B., & Hayes, A. F. (2016). Regression analysis and linear models: Concepts,
applications, and implementation. Guilford Publications.
Franses, P. H. (2016). A note on the mean absolute scaled error. International Journal of
Forecasting, 32(1), 20-22.
Schroeder, L. D., Sjoquist, D. L., & Stephan, P. E. (2016). Understanding regression analysis:
An introductory guide (Vol. 57). Sage Publications.
Tanaka, K. (2017). Time series analysis: nonstationary and noninvertible distribution
theory (Vol. 4). John Wiley & Sons