BUS700 Learning Activity 1BUS700 ECONOMICS T319, DUE: 11:59 P.M. FRIDAY WEEK 10Learning Activity Towards Preparation of AssignmentThis activity will help you complete your assignment. KOI Economics and Statistics department usesEXCEL often to undertake data analysis, as such, this activity introduces you to two useful functions ofEXCEL: regression analysis and graphs. Part I introduces how to use Excel for regression analysis; PartII is about how to use Excel for graphs, and Part III contains recommended Youtube videos about usingExcel for regression analysis and interpretation of results. PLEASE NOTE THAT THE INFORMATIONCONTAINED IN THIS ACTIVITY IS A GUIDE FOR YOU TO COMPLETE THE ASSIGNMENT. IT SHOULD BEUSED AS A GUIDE TO THOSE WHO ARE UNFAMILIAR WITH REGRESSION ANALYSIS AND GRAPHS.Part I: Regression AnalysisThe first thing to do is to collect data on some variables and, then, specify a regression model. Thetable below (2.3. Example 1) shows the values of the factors that influence the average demand forenergy bars: average income per person, tariff on energy bars imported, number of stores sellingenergy bars. The dependent variable (Y) is demand for energy; and the independent/explanatoryvariables (X) are income (X1); Tariff rate (X2); and number of stores (X3).1.2 Multiple Regression ModelY = α + β1X1 +β2X2 +β3X3 + β4X4 + ϵt (1)Demand = ( β1*Income)+ (β2*Tariff)+ ( β3*No of Stores) + ϵtThe slope/coefficient/parameter (β1, β2 etc.) should be interpreted as the impact (effect) of theexplanatory variables (variable 1, variable 2 etc.) on the dependent variable.1.3 Multiple Log-Linear Regression ModelLnY = α + β1lnX1 +β2lnX2 +β3lnX3 + β4lnX4 + ϵt (2)ln (Demand) = β1* ln (Income) + β2*ln (Tariff) + β3* ln (No of Stores) + ϵtThe slope/coefficient/parameter (β1, β2 etc.) should be interpreted as the percentage of thedependent 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. Placeraw data in the appendix.BUS700 Learning Activity 2Part II: Regression Process – Excel2.1: Installing/Activating Data Analysis tool in Excel1. Open Excel, then2. Go to File, then click on3. 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 Manage5. Click on Go, then in Add-ins dialog box, select6. Analysis ToolPak, the click on7. Ok, to return to Excel Worksheet.8. On the Menu Bar, Click on Data, then see9. Data Analysis appear or the Right-hand cornerBUS700 Learning Activity 32.2: Running Regression1. Open data file in Excel2. On Menu bar, Click Data, to reveal Data Analysis on right-hand corner3. Click on Data Analysis, then4. Select Regression, then Ok5. Regression dialog box appears (see screen shot below)6. Input (Y) range: select data range for dependent variable, including label7. Input (X) Range: select data range for independent variables, including labels8. Tick Label9. Under Output Options, Click10. Output range, to place regression output in current worksheet or select New Worksheet11. Click Ok, and regression output will appear as per your choice in (10)BUS700 Learning Activity 42. 3: Example 1A regression analysis of the raw data comparing the effect the Average AnnualIncome, Import Tariff and Number or Stores produces the following results.Summary Output

Regression Statistics

Multiple R

0.955933

R Square

0.913807

Adjusted RSquare

0.898597

Standard Error

7.819135

Observations

21

ANOVA

df

SS

MS

F

SignificanceF

Regression

3

11019.21

3673.07

60.07749

2.95E-09

Residual

17

1039.361

61.13888

Total

20

12058.57

Annualaveragedem and ofenergy barsperperson

A verageincom eperperson

T arif rateon im portsof energybars

N um ber ofstoresw hereenergy barsareof 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

StandardError

t Stat

P-value

Lower 95%

Upper95%

Lower95.0%

Upper95.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 partneeded for your analysis, for example, table of coefficients, standard error, tstatistics, 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% levelThe regression output suggests that we can predict what the Demand wouldbe, with 91% confidence (R square), equal to the following formulaDemand = (-12.160220) + (0.004838 x Income)+ (-6.456977 x Tariff)+ (4.072444 x No of Stores)The regression results show that there is a positive relationship betweenNumber of Stores and the Demand. To be precise, for each additionalstore where energy bars aresoldwecan expect theAverage Annual Demandper Person to increase by 4.1.In addition, wecan expect that forevery additional $1000 increaseof annualincomeperperson theAverage Annual Demand perPerson wouldincrease by4.8.BUS700 Learning Activity 6Example 2.3. 2: Example 2, Log-Linear Model

Demand

A verageincome

Tarifrate

N ofstores

Ln (Demand)

Ln (Income)

Ln ( Rate Tariff )

Ln ( Stores No of )

106

15,500

5

15

4.663439094

9.6485953

1.609438

2.708050201

90

15,810

5

15

4.49980967

9.66839793

1.609438

2.708050201

93

16,395

5

15

4.532599493

9.70473169

1.609438

2.708050201

92

16,887

5

15

4.521788577

9.73429937

1.609438

2.708050201

91

17,495

5

15

4.510859507

9.7696704

1.609438

2.708050201

110

18,282

5

16

4.700480366

9.81367225

1.609438

2.772588722

109

19,013

5

16

4.691347882

9.85287823

1.609438

2.772588722

122

19,508

5

16

4.804021045

9.87857992

1.609438

2.772588722

82

19,898

10

16

4.406719247

9.8983745

2.302585

2.772588722

84

20,276

10

16

4.430816799

9.9171932

2.302585

2.772588722

102

20,702

10

17

4.624972813

9.93798559

2.302585

2.833213344

92

21,550

10

17

4.521788577

9.9781311

2.302585

2.833213344

115

22,197

10

20

4.744932128

10.0077124

2.302585

2.995732274

112

22,330

10

20

4.718498871

10.0136863

2.302585

2.995732274

109

22,754

10

20

4.691347882

10.0324962

2.302585

2.995732274

148

23,619

7.5

20

4.997212274

10.0698068

2.014903

2.995732274

143

23,855

7.5

20

4.96284463

10.0797491

2.014903

2.995732274

139

24,452

7.5

20

4.934473933

10.1044673

2.014903

2.995732274

158

24,941

7.5

23

5.062595033

10.1242683

2.014903

3.135494216

142

25,514

7.5

23

4.955827058

10.1469826

2.014903

3.135494216

158

25,948

7.5

23

5.062595033

10.1638498

2.014903

3.135494216

SUMMARY OUTPUT

Regression Statistics

Multiple R

0.950776

R Square

0.903976

Adjusted RSquare

0.88703

StandardError

0.070563

Observations

21

ANOVA

df

SS

MS

F

SignificanceF

Regression

3

0.796845

0.265615

53.34624

7.35E-09

Residual

17

0.084644

0.004979

Total

20

0.881489

BUS700 Learning Activity 7

Coefficients

StandardError

t Stat

P-value

Lower 95%

Upper95%

Lower95.0%

Upper95.0%

Intercept

-3.59456

2.364134

-1.52045

0.146778

-8.58244

1.39333

-8.58244

1.39333

Ln (Income)

0.655447

0.323221

2.027859

0.058545

-0.02649

1.337383

-0.02649

1.337383

Ln (Tariff)

-0.43437

0.072707

-5.9743

1.51E-05

-0.58777

-0.28097

-0.58777

-0.28097

Ln (Stores)

0.921077

0.298117

3.089646

0.00665

0.292104

1.55005

0.292104

1.55005

2.3.2.1 Presenting the results in the body of report/essay:The whole summary output is not required in the report. Insert only the partneeded for your analysis, for example, table of coefficients, standard error, tstatistics, and P-value.

Coefficients

Standard Error

t Stat

P-value

Intercept

-3.59456

2.364134

-1.52045

0.146778

Ln (Income)

0.655447*

0.323221

2.027859*

0.058545

Ln (Tariff)

-0.43437*

0.072707

-5.9743*

1.51E-05

Ln (Stores)

0.921077*

0.298117

3.089646*

0.00665

R2 = 0.95

• Significant at the 55% levelLn (Demand) = -3.59456 + 0.655447 x ln (Income) + -0.43437 x ln (Tariff) + 0.921077 x ln (No ofStores)The regression results show that if the number of stores increases by 1%,ceteris paribus, average demand for energy bars will increase by 0.092%or the elasticity of demand for energy bars with respect to the number ofstores is 0.92. In addition, we can expect that for every 1% increase in income perperson,the average annual demand per will increase by 0.66% (i.e. energy baris a normal good).BUS700 Learning Activity 8Part II: Graphical AnalysisMarket analysis, data from international data bases from the World Bank (The World DevelopmentIndicators, Doing Business Indicators), the International Monetary Fund (International FinancialStatistics), the Asian Development Bank (Key Indicators), and the OECD (Services Trade RestrictivenessIndicators).Using some basic excel tools, our research team has created the following graphs for us. These graphsmake it significantly easier to compare the countries.

Goldland

Silverland

GDPgrowth(%)

Inflation(%)

GDP per capita,PPP (current $)

GDP growth(%)

Inflation(%)

GDP per capita,PPP (current $)

1994

7.3

2.6

9,200

5.8

1.7

16,579

1995

10.9

2.8

10,144

9.6

3.0

18,460

1996

10.4

2.9

11,120

9.3

0.5

19,995

1997

2.3

2.3

11,297

11.2

3.7

22,109

1998

2.0

1.6

11,396

8.9

6.3

24,516

1999

-5.4

2.3

10,741

10.8

4.1

26,531

2000

-5.5

39.9

10,220

10.2

6.1

29,620

2001

5.3

1.8

10,873

5.8

6.3

31,766

2002

7.1

-4.7

11,723

5.9

5.3

34,441

2003

-4.7

2.6

11,325

3.8

3.3

36,020

2004

7.0

2.3

12,390

4.4

2.6

38,155

2005

2.5

7.4

13,039

6.3

2.4

40,447

2006

-1.8

2.4

13,122

6.3

2.3

43,991

2007

0.1

0.5

13,408

5.5

1.0

46,740

2008

-4.7

6.7

12,961

-2.2

-2.7

43,902

2009

-10.7

5.2

11,590

-5.6

-4.3

41,880

2010

1.8

-3.1

11,868

0.4

-2.3

43,220

2011

8.3

0.8

13,034

2.6

2.0

45,674

2012

4.6

2.6

13,774

0.2

0.4

45,790

2013

-2.0

8.1

13,588

1.4

1.2

46,894

2014

8.0

1.6

14,757

5.2

0.1

48,755

Source: World Development Indicators (2016)BUS700 Learning Activity 9Figure 1 – Comparison of GDP growth ratesSource: World Development Indicators (2016)From Figure 1 we can see that Silverland had more stable growth rates than the Goldland. Silverlandhas also recovered faster from the Global Financial Crisis (GFC) in 2008/2009 faster than the Goldland.Figure 2 – Annual Inflation rate (%)Source: World Development Indicators (2016)-15-10-55 01015199419951996199719981999200020012002200320042005200620072008200920102011201220132014GDP growth rate %Comparison of GDP growth ratesGoldland SilverlandBUS700 Learning Activity 10Figure 2 shows that the annual average inflation rate is on average higher in the Goldland than inSilverland. In 2000, the inflation rate hit more than 40 per cent in the developing country; in 2013, theinflation rate was almost 10 per cent.Figure 3 – GDP per Capita in PPP($)Source: World Development Indicators (2016)Figure 3 shows the per capita Purchasing Power Parity, the exchange rate adjusted purchasing power,for both countries. The figure clearly highlights the much stronger purchasing power of Silverlandcompared to the Goldland and its development over the past 20 years.Part III: Recommended VideosThe following videos will help you understand how to use Excel to perform regression analysis.https://www.youtube.com/watch?v=wBocR96UdyYhttps://www.youtube.com/watch?v=O7TMCYuDbDcInterpreting:https://www.youtube.com/watch?v=tlbdkgYz7FM010,00020,00030,00040,00050,00060,000Millions ($)YearGDP per Capita in PPP($)Goldland Silverland

The post BUS700 Learning Activity 1 appeared first on My Assignment Online.