Question

I want to know how to solve the following in excel. What is the x value and what is the Y value using the data table below?

Wal-Mart is the second largest retailer in the world. The data file (Wal-Mart Revenue 2004-2009.xlsx) is posted below the case study one file, and it holds monthly data on Wal-Mart’s revenue, along with several possibly related economic variables.

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 scatter plot, residual plot and normal probability plot for the best model in part (d) and comment on what you see.

Date	Wal Mart Revenue	CPI	Personal Consumption	Retail Sales Index	December
1/30/2004	12.131	554.9	7977730	281463	0
2/27/2004	13.628	557.9	8005878	282445	0
3/31/2004	16.722	561.5	8070480	319107	0
4/29/2004	13.98	563.2	8086579	315278	0
5/28/2004	14.388	566.4	8196516	328499	0
6/30/2004	18.111	568.2	8161271	321151	0
7/27/2004	13.764	567.5	8235349	328025	0
8/27/2004	14.296	567.6	8246121	326280	0
9/30/2004	17.169	568.7	8313670	313444	0
10/29/2004	13.915	571.9	8371605	319639	0
11/29/2004	15.739	572.2	8410820	324067	0
12/31/2004	26.177	570.1	8462026	386918	1
1/21/2005	13.17	571.2	8469443	293027	0
2/24/2005	15.139	574.5	8520687	294892	0
3/30/2005	18.683	579	8568959	338969	0
4/29/2005	14.829	582.9	8654352	335626	0
5/25/2005	15.697	582.4	8644646	345400	0
6/28/2005	20.23	582.6	8724753	351068	0
7/28/2005	15.26	585.2	8833907	351887	0
8/26/2005	15.709	588.2	8825450	355897	0
9/30/2005	18.618	595.4	8882536	333652	0
10/31/2005	15.397	596.7	8911627	336662	0
11/28/2005	17.384	592	8916377	344441	0
12/30/2005	27.92	609.4	8955472	406510	1
1/27/2006	14.555	573.9	9034368	322222	0
2/23/2006	16.87	595.2	9079246	318184	0
3/31/2006	16.639	598.6	9123848	366989	0
4/28/2006	17.2	603.5	9175181	357334	0
5/25/2006	16.901	606.5	9238576	380085	0
6/30/2006	21.47	607.8	9270505	373279	0
7/28/2006	16.542	609.6	9338876	368611	0
8/29/2006	16.98	610.9	9352650	382600	0
9/28/2006	20.091	607.9	9348494	352686	0
10/20/2006	16.583	604.6	9376027	354740	0
11/24/2006	18.761	603.6	9410758	363468	0
12/29/2006	28.795	604.5	9478531	424946	1
1/26/2007	16.1	606.3	9540335	332797	0
2/23/2007	17.984	594.6	9500318	327686	0
3/30/2007	18.939	599.3	9547774	376491	0
4/27/2007	22.47	613.3	9602393	366936	0
5/25/2007	19.201	642.8	9669845	389687	0
6/29/2007	23.77	623.9	9703817	382781	0
7/27/2007	18.942	625.6	9776564	378113	0
8/31/2007	19.38	626.9	9791220	392125	0
9/28/2007	22.491	623.9	9786798	362211	0
10/26/2007	18.983	619.9	9816093	364265	0
11/30/2007	21.161	620.6	9931068	372970	0
12/28/2007	31.245	642.5	9953178	434488	1
1/25/2008	19.923	623.4	10018937	342422	0
2/29/2008	21.512	622.3	10146599	344464	0
3/28/2008	19.023	626.9	10197093	339463	0
4/25/2008	20.178	651.2	10255207	388158	0
5/30/2008	21.9	636.1	10326976	378653	0
6/27/2008	21.24	638.7	10363123	397579	0
7/25/2008	22.1	640.2	10440525	394488	0
8/29/2008	20.981	641.9	10456119	389780	0
9/26/2008	20.419	643.2	10451414	403812	0
10/31/2008	20	641.2	10482584	373978	0
11/28/2008	21.022	637.9	10521902	381932	0
12/26/2008	32.85	656.9	10508628	443677	1
1/30/2009	19.784	637.8	10578596	350195	0
2/27/2009	20.962	639.7	10714428	353997	0
3/27/2009	22.951	638.9	10768153	356183	0
4/24/2009	22.062	643.7	10829987	351032	0
5/29/2009	20.856	648.1	10906349	354928	0
6/26/2009	23.700	649.4	10944809	395869	0
7/31/2009	24.413	651.4	11027165	389656	0

Answer 1

We are asked to perform regression analysis ,so we can use excel.

We have to predict Wal mart revenue using the variable CPI , Personal Consumption , and  Retail Sales Index

So here Y is Wal-Mart revenue and x values are CPI , Personal Consumption , and  Retail Sales Index

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

First enter the data sets Wal-Mart revenue and  CPI  into excel.

Then go to Data menu ---> Data analysis ---> Regression .

Plug the inputs as shown below ,

( If you have selected column headings ,then you must check the box for Labels )

Select the boxes for Residual plots and Normal probability plots and then click on OK.

The output will be appeared as follow..

The linear regression model : Y = -39.0413 + 0.0959*X OR Wal mart revenue = -39.0413 +0.0959*CPI

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

Follow the same steps as we did in part a) just change the x variable CPI to Personal Consumption , the output will be appear as follows

The linear regression model : Y = -8.4427 + 0.000003*X OR Wal mart revenue = -8.4427 +0.000003*Personal Consumption

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

Follow the same steps as we did in part a) just change the x variable CPI to Retail Sales Index , the output will be appear as follows

The linear regression model : Y = -16.4208 + 0.0001*X OR Wal mart revenue = -16.4208 +0.0001*Retail Sales Index

Part 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.

The p-values ( significance F value ) of all models are approximately 0 , therefore all models are significant.

But R square for model Retail sales index is large , therefore model Wal mart revenue = -16.4208 +0.0001*Retail Sales Index is the best for prediction of Wal Mart revenue.

Part E). Generate a scatter plot, residual plot and normal probability plot for the best model in part (d) and comment on what you see.

Select Wal mart revenue and Retail Sales Index data set ( only number ) thenGo to Insert option --->Charts ---> select Insert Scatter option .. Scatter plot will be appeared as follows , we already have residual and normal probability plot with the regression analysis output.

From the scatter plot we can conclude that there is strong positive correlation between the variables Wal mart revenue and Retail Sales Index

From normal probability plot we can conclude that both data set follows the normal distribution.

From residual plot we can say that linear regression model : Wal mart revenue = -16.4208 +0.0001*Retail Sales Index is appropriate.( since points are randomly and equally dispersed around the both sides of horizontal axis)