Gulf University for Science & Technology

Department of Economics & Finance

ECO‐380: Business Statistics

Dr. Khalid Kisswani

Room: N1‐116

Tel: 2530‐7339

kisswani.k@gust.edu.kw

Excel Assignment 1

Direction: Use Microsoft Excel to answer the following three questions (Excel

Instructions are provided below)

1. Time spent studying and grades: Are the grades one receives in a course related to the

amount of time spent studying the subject? Consider the following data on hours spent studying

and grades by ten students.

Time Grade

40 77

41 63

37 79

47 86

25 51

44 78

42 83

48 90

35 65

28 47

Compute:

a. The mean, standard deviation, minimum and maximum of the two variables and

interpret the results.

b. Find the correlation coefficient and interpret your finding.

c. Use a scatter plot to graph the relationship between Time and Grade. Is your finding

consistent with finding in (b)? Explain.

d. What can you conclude about the relationship between hours of study and grades?

2. Distance and Fire damage from fire Station (see file: Fire) A fire insurance company wants

to relate the amount of fire damage in major residential fires to the distance between the burning

house and the nearest fire station. A sample of recent fires is selected from a large suburb of a

major city. Miles is the distance between the fire and the nearest fire station; Damage is the

amount of damage in thousands of dollars.

Miles Damage

3.4 26.2

1.8 17.8

4.6 31.3

2.3 23.1

3.1 27.5

5.5 36.0

0.7 14.1

3.0 22.3

2.6 19.6

4.3 31.3

2.1 24.0

1.1 17.3

6.1 43.2

4.7 36.4

3.8 26.1

a. Find the mean, standard deviation, minimum and maximum of the

variables distance and fire damage. Interpret your findings.

b. What is the correlation coefficient between distance and fire damage? Is

the sign and magnitude of the coefficient consistent with intuition?

Explain.

c. Depict a scatter plot of the relationship between distance and fire damage.

Is your finding consistent with finding in (b)? Explain.

d. What conclusion can you reach about the relationship between distance

and fire damage? Explain briefly.

3. Determinants of Sales: A firm wants to investigate the effect of advertising on

sales. Data on sales and advertising expenditures are gathered for 20 stores. Given

the variable sale (SALES, in $100) and the variable advertising expenditures

(ADV, in $100), use the data file SALES to answer the following question:

a. Construct a descriptive statistics table of the mean, standard deviation,

minimum and maximum of the two variables. Interpret your findings.

b. What is the correlation coefficient of SALES and ADV? Is the sign and

magnitude of the coefficient consistent with expectation? Explain.

c. Depict a scatter plot of the relationship between the two variables. Is your

finding consistent with finding in (b)? Explain.

d. What implication does this result have for the firm? Explain briefly.

Introduction to Microsoft Excel (for Excel 97 or 2003)

Microsoft Excel is a widely available and powerful statistical analysis tool. You should take the

opportunity to become familiar with its data processing and analysis capabilities if you have not

already done so. This set of instructions will enable you to access Excel and some of its

statistical analysis tools.

Introduction to Microsoft Excel (for Excel 2003)

Turn on the computer and start Excel

Click Tools (on the standard toolbar)

Click Data Analysis

N.B.: If Data Analysis is not available, you can load it by following the steps: click

Add-Ins, then check Analysis ToolPak and Analysis ToolPak VBA, then click OK.

1. Descriptive Statistics

•Type or import your data

•Click Tools

•Click Data Analysis

•Click Descriptive Statistics

Input range(supply the range of the data or highlight the column of the data range)

•Click Label in first row

•Click New Worksheet Ply

•Click Summary Statistics

•Click OK and print your results

2. Using Scatter plot

• Type or import your data

•Click Chart Wizard (on the standard toolbar)

• Click XY scatter

•Click next

•Provide Data range (or highlight the column of the data range)

•Click next

Provide title, Label “X” axis and “Y” axis

•Click next

•Click As new sheet

•Click Finish

•Right click on any dot on the graph

•Click Add trend line

•Click Options

•Click Display equation on Chart

•Click Finish

(You may want to print your graph)

3. Covariance and correlation

•Type or import your data

•Click Tools

• Click Data Analysis

•Click covariance (or correlation)

•Input range (Supply the range of the data, or highlight the column of the data range)

•Click Label in first row

•Click New Worksheet Ply

•Click OK and print your results

Introduction to Microsoft Excel (for Excel 2007)

Microsoft Excel is a widely available and powerful statistical analysis tool. You should take the

opportunity to become familiar with its data processing and analysis capabilities if you have not

already done so. This set of instructions will enable you to access Excel and some of its

statistical analysis tools.

Turn on the computer and start Excel

Click Data (on the standard toolbar)

Click Data Analysis

N.B.: If Data Analysis is not available, you can load it by following the steps:

Click Add-Ins

Click Office Button

Click Excel Options

Click Add-Ins

Click Go

Check ToolPak

Check Analysis ToolPak VBA

1. Descriptive Statistics

•Type or import your data

•Click Data

•Click Data Analysis

•Click Descriptive Statistics

Input range(supply the range of the data or highlight the column of the data range)

•Click Label in first row

•Click New Worksheet Ply

•Click Summary Statistics

•Click OK and print your results

2. Using Scatter plot

• Type or import your data

•Click Insert

• Click Scatter

•Select the first graph

•Click Move Chart

•Click New Sheet

•Click OK

•Right click on any dot on the graph

•Click Add trend line

•Check linear

(You may want to print your graph)

3. Covariance and correlation

•Type or import your data

•Click Data

• Click Data Analysis

•Click covariance (or correlation)

•Click Label in first row

•Click New Worksheet Ply

•Click OK and print your results