BUS700 Learning Activity XXXXXXXXXX1
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 XXXXXXXXXX1)
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 XXXXXXXXXX2)
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 XXXXXXXXXX2
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 XXXXXXXXXX3
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 XXXXXXXXXX4
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 XXXXXXXXXX
R Square XXXXXXXXXX
Adjusted R
Square XXXXXXXXXX
Standard Error XXXXXXXXXX
Observations 21
ANOVA
df SS MS F
Significance
F
Regression XXXXXXXXXX XXXXXXXXXX95E-09
Residual XXXXXXXXXX13888
Total XXXXXXXXXX
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, XXXXXXXXXX
90 15, XXXXXXXXXX
93 16, XXXXXXXXXX
92 16, XXXXXXXXXX
91 17, XXXXXXXXXX
110 18, XXXXXXXXXX
109 19, XXXXXXXXXX
122 19, XXXXXXXXXX
82 19, XXXXXXXXXX
84 20, XXXXXXXXXX
102 20, XXXXXXXXXX
92 21, XXXXXXXXXX
115 22, XXXXXXXXXX
112 22, XXXXXXXXXX
109 22, XXXXXXXXXX
148 23, XXXXXXXXXX
143 23, XXXXXXXXXX
139 24, XXXXXXXXXX
158 24, XXXXXXXXXX
142 25, XXXXXXXXXX
158 25, XXXXXXXXXX
XXXXXXXXXX
BUS700 Learning Activity XXXXXXXXXX5
Coefficients
Standard
Error t Stat P-value Lower 95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
Average Income XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
Tariff Rate XXXXXXXXXX XXXXXXXXXX71E XXXXXXXXXX XXXXXXXXXX
-
XXXXXXXXXX
No of Stores XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
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 XXXXXXXXXXt Stat XXXXXXXXXXP-value
Intercept XXXXXXXXXX XXXXXXXXXX
Average Income XXXXXXXXXX* XXXXXXXXXX.016316
Tariff Rate XXXXXXXXXX* XXXXXXXXXX71E-06
No of Stores XXXXXXXXXX* XXXXXXXXXX.046614
R2 = XXXXXXXXXX
• 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 = (− XXXXXXXXXX004838 x Income)+ (− XXXXXXXXXXx Tariff XXXXXXXXXXx No of Stores)
The regression