Quantitative business analysis – case study

  

-one Excel file to complete this case study, and use one spreadsheet for one problem. 

-using Excel, for which your Excel worksheet is not fully functional, or for which you have not shown all of the calculations or other work performed to derive your answer(s)

-use Microsoft Excel to complete each problem and submit a single Excel spreadsheet that contains a separate worksheet (i.e., separate tab) for each problem. 

-have each worksheet be clearly labeled to identify the associated problem. 

-to show all calculations or other work performed to derive your answer(s) for each problem. have your spreadsheet be fully functional (i.e., configured to allow the reader to not only see all numerical values, but also be able to see the underlying formula associated with each calculated value). 

-to label your work in each worksheet to clearly identify the nature of each piece of data or calculated value.

-Wal-Mart is the second largest retailer in the world, and it holds monthly data on Wal-Mart’s revenue, along with several possibly related economic variables. The data file is attached

(a) Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only independent variable.

(b) Develop a linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable.

(c) Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable.

(d) Which of these three models is the best? Use R-square values, Significance F values, p-values and other appropriate criteria to explain your answer.

(e) Generate a residual plot and a line fit plot for the best model in part (d) and comment on what you see.

Identify and remove the four cases corresponding to December revenue.

(f) Develop a linear regression model to predict Wal-Mart revenue, using CPI as the only independent variable.

(g) Develop a linear regression model to predict Wal-Mart revenue, using Personal Consumption as the only independent variable.

(h) Develop a linear regression model to predict Wal-Mart revenue, using Retail Sales Index as the only independent variable.

(i) Which of these three models is the best? Use R-square values, Significance F values, p-values and other appropriate criteria to explain your answer.

(j) Generate a residual plot and a line fit plot for the best model in part (i) and comment on what you see.

(k) Comparing the results of parts (d) and (i), which of these two models is better? Use R-square values, Significance F values, p-values and other appropriate criteria to explain your answer