please follow the instructions on the file i have attached.
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