Gulf University for Science & Technology Department of Economics & Finance
ECO?380: Business Statistics
Dr. Khalid Kisswani
Room: N1?116 Tel: 2530?7339
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
