BUS700 Learning Activity 1 BUS700 ECONOMICS T319, DUE: 11:59 P.M. FRIDAY WEEK 10 Learning Activity Towards Preparation of Assignment This activity will help you complete your assignment. KOI Economics and Statistics department uses EXCEL often to undertake data analysis, as such, this activity introduces you to two useful functions of EXCEL: regression analysis and graphs. Part I introduces how to use Excel for regression analysis; Part II is about how to use Excel for graphs, and Part III contains recommended Youtube videos about using Excel for regression analysis and interpretation of results. PLEASE NOTE THAT THE INFORMATION CONTAINED IN THIS ACTIVITY IS A GUIDE FOR YOU TO COMPLETE THE ASSIGNMENT. IT SHOULD BE USED AS A GUIDE TO THOSE WHO ARE UNFAMILIAR WITH REGRESSION ANALYSIS AND GRAPHS. Part I: Regression Analysis The first thing to do is to collect data on some variables and, then, specify a regression model. The table below (2.3. Example 1) shows the values of the factors that influence the average demand for energy bars: average income per person, tariff on energy bars imported, number of stores selling energy bars. The dependent variable (Y) is demand for energy; and the independent/explanatory variables (X) are income (X1); Tariff rate (X2); and number of stores (X3). 1.2 Multiple Regression Model Y = α + β1X1 +β2X2 +β3X3 + β4X4 + ϵt (1) Demand = ( β1*Income)+ ( β2*Tariff)+ ( β3*No of Stores) + ϵt The slope/coefficient/parameter (β1, β2 etc.) should be interpreted as the impact (effect) of the explanatory variables (variable 1, variable 2 etc.) on the dependent variable. 1.3 Multiple Log-Linear Regression Model LnY = α + β1lnX1 +β2lnX2 +β3lnX3 + β4lnX4 + ϵt (2) ln (Demand) = β1* ln (Income) + β2*ln (Tariff) + β3* ln (No of Stores) + ϵt The slope/coefficient/parameter (β1, β2 etc.) should be interpreted as the percentage of the dependent variable in response to percentage change in of the explanatory variable (variable 1, variable 2 etc.) or the elasticity of the dependent variable with respect to the explanatory variable. NOTE: WE CANNOT TAKE LN OF NEGATIVE NUMBERS. * ONLY REGRESSION RESULTS, Tables and graphs are to be inserted in the body or essay. Place raw data in the appendix. BUS700 Learning Activity 2 Part II: Regression Process - Excel 2.1: Installing/Activating Data Analysis tool in Excel 1. Open Excel, then 2. Go to File, then click on 3. Options. In left-hand side of Options dialog Box, 4. Add-ins, then on the bottom of right-hand side, Excel Add-ins appears in front of Manage 5. Click on Go, then in Add-ins dialog box, select 6. Analysis ToolPak, the click on 7. Ok, to return to Excel Worksheet. 8. On the Menu Bar, Click on Data, then see 9. Data Analysis appear or the Right-hand corner BUS700 Learning Activity 3 2.2: Running Regression 1. Open data file in Excel 2. On Menu bar, Click Data, to reveal Data Analysis on right-hand corner 3. Click on Data Analysis, then 4. Select Regression, then Ok 5. Regression dialog box appears (see screen shot below) 6. Input (Y) range: select data range for dependent variable, including label 7. Input (X) Range: select data range for independent variables, including labels 8. Tick Label 9. Under Output Options, Click 10. Output range, to place regression output in current worksheet or select New Worksheet 11. Click Ok, and regression output will appear as per your choice in (10) BUS700 Learning Activity 4 2. 3: Example 1 A regression analysis of the raw data comparing the effect the Average Annual Income, Import Tariff and Number or Stores produces the following results. Summary Output Regression Statistics Multiple R 0.955933 R Square 0.913807 Adjusted R Square 0.898597 Standard Error 7.819135 Observations 21 ANOVA df SS MS F Significance F Regression 3 11019.21 3673.07 60.07749 2.95E-09 Residual 17 1039.361 61.13888 Total 20 12058.57 Annual average dem and of energy bars per person A verage incom e per person T arif rate on im ports of energy bars N um ber of stores w here energy bars are of ered 106 15,500 5 15 90 15,810 5 15 93 16,395 5 15 92 16,887 5 15 91 17,495 5 15 110 18,282 5 16 109 19,013 5 16 122 19,508 5 16 82 19,898 10 16 84 20,276 10 16 102 20,702 10 17 92 21,550 10 17 115 22,197 10 20 112 22,330 10 20 109 22,754 10 20 148 23,619 7.5 20 143 23,855 7.5 20 139 24,452 7.5 20 158 24,941 7.5 23 142 25,514 7.5 23 158 25,948 7.5 23 162.7 25948 7.5 24 BUS700 Learning Activity 5 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept -12.1602 11.30761 -1.0754 0.297222 -36.0172 11.69675 -36.0172 11.69675407 Average Income 0.004838 0.001815 2.665207 0.016316 0.001008 0.008668 0.001008 0.008667683 Tariff Rate -6.45698 1.041615 -6.199 9.71E-06 -8.65459 -4.25936 -8.65459 - 4.259360996 No of Stores 4.072444 1.897801 2.145875 0.046614 0.068434 8.076454 0.068434 8.076454295 2.3.1 Presenting the results in the body of report/essay: The whole summary output is not required in the report. Insert only the part needed for your analysis, for example, table of coefficients, standard error, t- statistics, and P-value. Coefficients Standard Error t Stat P-value Intercept -12.1602 11.30761 -1.0754 0.297222 Average Income 0.004838* 0.001815 2.665207 0.016316 Tariff Rate -6.45698* 1.041615 -6.199 9.71E-06 No of Stores 4.072444* 1.897801 2.145875 0.046614 R2 = 0.913807 • Significant at the 5% level The regression output suggests that we can predict what the Demand would be, with 91% confidence (R square), equal to the following formula Demand = (−12.160220) + (0.004838 x Income)+ (−6.456977 x Tariff)+ (4.072444 x No of Stores) The regression