DSCI 3710 Excel Assignment #4 Food Lion Inc. would like to investigate the feasibility and future prospects of setting up stores in Denton County. Food Lion has provided you with a sample from a database of household financial variables. They would like you to use regression techniques to predict the monthly expenditure on groceries, of families who eitherrent or own their homes in Denton. The sample contains 100 records. The various fields in the sample are: Income1  Annual income of head of household or primary wage earnerIncome2  Annual income of secondary wage earnerFamlsize  Size of family (number of people permanently residing in the household)Ownorent  1 if household is owned; 0 if it is rentedAutodebt  Automobile related debt pending for wage earners in the householdHpayrent  Household mortgage payment or rent per monthGroc  Monthly expenditure on groceriesLoc  1 = East Denton (E); 0 = West Denton(W); -1 = North Denton (N); 2 = South Denton (S)For this assignment you will be given minimal instructions since, having completed the first three assignments, you should now be quite familiar withExcel functions and pull down menus.1. The sample data are contained in a file named Assgt#4.xls to be found in the folder Excel Assignments for All Sections. Download the files from the folder onto a disk.2. Import the data into the first nine columns (A-I) of your spreadsheet. Thefirst row contains labels for the variables in the order outlined in the data description. The table below illustrates how the first 10 data records shouldlook, after this step is completed. Obs Income1 Income2 Famlsize Ownorent Autodebt Hpayrent Groc Loc1 36557 20610 4 0 15290 1339 278 02 27045 25490 5 0 14676 1175 220 03 38878 0 8 0 10317 1108 456 04 41448 0 2 1 9504 729 253 05 33136 25300 6 1 17802 875 344 016 44308 24559 2 1 20537 1282 326 -17 31997 25419 4 1 11725 919 308 18 43437 0 6 1 12084 970 311 29 41625 22802 4 1 14863 945 305 010 40140 32158 5 0 15708 1470 432 23. Insert three columns prior to the column labeled “Groc”. In these columns, create three logical (dummy) variables to represent the four locations. You may use the IF function from the pull down menu, to do this. Please follow the illustration below, where: ED=1, if record is for East Denton; WD=1, if record is for West Denton; and SD=1, if the record is for South Denton (thus North Denton representing the base). After the creation of the dummy variables, the first 11 records should look as shown below.Obs Income1 Income2 Famlsize Ownrent Autodebt Hpayrent ED WDSD Groc1 36557 20610 4 0 15290 1339 0 1 0 2782 27045 25490 5 0 14676 1175 0 1 0 2203 38878 0 8 0 10317 1108 0 1 0 4564 41448 0 2 1 9504 729 0 1 0 2535 33136 25300 6 1 17802 875 0 1 0 3446 44308 24559 2 1 20537 1282 0 0 0 3267 31997 25419 4 1 11725 919 1 0 0 3088 43437 0 6 1 12084 970 0 0 1 3119 41625 22802 4 1 14863 945 0 1 0 30510 40140 32158 5 0 15708 1470 0 0 1 43211 31448 0 4 0 6160 985 1 0 0 2594. Conduct the regression analysis with Groc as the dependent variable (Y) and Income1 (X1), Income2 (X2), Ownorent (X3), Autodebt (X4), Famlsize (X5), and Location variables (X6 through X8) as the independent variables at1% level of significance. Use the Regression tool accessed from the Data Tab/ Data Analysis (in 2003 use Tools/Data Analysis) pull-down menu. Checkonly the labels box and specify that you want the output in cell M1. (Make sure that you enter “Confidence Level = 99%”). Also, make sure to check the Standardized residuals box to obtain outlier information. A partial output is shown below for your guidance.SUMMARY OUTPUTRegression StatisticsMultiple R 0.779966R Square xxxx2Adjusted R Square 0.5739159Standard Error xxxxObservations 100ANOVAdf SS MS F SignificanceFRegression 8 760733.044 95091.6 xxxx xxxxResidual 91 xxxx xxxxTotal 99 xxxxCoefficientsStandardErrort Stat P-valueLower 95% Upper95%Lower99.0%Upper 99.0%Intercept 59.847981 43.87639859 1.364 0.176 -27.307103 147.0031 -55.58863 175.284587Income1 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxxIncome2 xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxxOwnrent xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxxAutodebt xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxxFamlsize xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxxED xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxxWD xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxxSD xxxx xxxx xxxx xxxx xxxx xxxx xxxx xxxx4. By substituting appropriate values directly into the sample regression equation given in your output generate a point estimate for (Alternatively, you may use the TREND function from the pull down menu, and follow directions given in Excel or use the KPK macro to run regression and for prediction as shown in class):a) the monthly Grocery Bill for a family of 4 living in a rented home in South Denton, whose primary income is $42,457 and with the secondary wage earner having an income of $10000, and Autodebt is $6,000b) the monthly Grocery Bill for a family of size 7 living in a home in the North Denton that is owned, whose primary income is $35,000 with the secondary wage earner having an income of $25,000, and Autodebt is $10,000.Details on the trend function can be found by clicking the fx icon on the toolbar then selecting statistical and trend. The new values for Income1, Income2, Ownorent, Autodebt, Famlsize, and Location variables can be placed in convenient cells in the two rows below your regression output and the point estimates for Groc evaluated in the adjacent cells.3Use the output from the regression analysis and trend function to answer the following questions.A. Write one to three sentences to interpret the meaning of your model’s R-square value. B. Conduct an F test for the regression model containing the eight independent variables, at the 1% significance level. State the null and alternative hypotheses, the decision, reason for the decision, and a conclusion.C. Conduct a t-test, at the 1% significance level, for the usefulness of the dummy (indicator) variable Ownorent. State the null and alternative hypotheses, the decision, reason for the decision, and a conclusion. D. Conduct a t-test, at the 1% significance level, for the usefulness of the variable Income2. State the null and alternative hypotheses, the decision, reason for the decision, and a conclusion.E. State which of the eight X (independent) variables are statistically significant and which ones are not, at the 1% significance level.F. Write one or two sentences on