PROJECT 1: CENSUS DATA

OBJECTIVE

The purpose of this project is to demonstrate your understanding of the following course concepts:

1. How to create and navigate within workbooks.

2. How to enter data and format cells.

3. How to format worksheets and workbooks.

4. How to create, edit, and format tables.

5. How to modify tables.

6. How to use functions and formulas.

7. How to calculate and transform data.

8. How to create and modify charts.

PROJECT DESCRIPTION

You are working as part of a group creating a report that includes data from the most recent census. Your task is to create and format a worksheet using information from the census.gov website and chart the data.

1. Create a new workbook and save it as Project1_Census.xlsx.

2. Identify three U.S. cities of your choice and enter the city and state names in cells A4, A5, and A6.

3. Open a web browser and navigate to the data.census.gov website. Use the most recent census data to locate the population, median age, number of housing units, and median income for each city (e.g. type “median income in Maryland” in the search box at the top of the page).

4. Enter the information into the respective cells in the worksheet, as shown in the image below but using cities of your own choosing. Note: If you are unable to locate the necessary data, either choose a different city or perform a search for another website containing the necessary data.

PROJECT 1 FIGURE 1

5. Format the data table using techniques you have learned to present the data in a visually appealing form such as:

a. Set the column widths appropriately.

b. Set labels alignment appropriately.

c. Apply borders, gridlines and shading to the table as desired.

d. Set the columns labels alignments appropriately.

e. Align the worksheet vertically and horizontally on the page.

6. Enter a formula to find Average Household Income for all THREE cities.

7. Enter a formula to find the Total Population for all THREE cities.

8. Create charts that present the data for each of the four categories of data. Decide which chart types will best present the data. (Hint: If you are not sure which types to use, consider selecting he data and using the Recommended Chart button to narrow down and preview suitable choices.)

9. Place each chart on a separate sheet and format the charts to best present the data in a clear, attractive format.

10. Give each worksheet a descriptive name and color the tabs using theme colors. Reorder the sheets so that the data table appears first, followed by the charts.

11. Save and submit the workbook to the Assignment Folder.

SUBMISSION REQUIREMENTS

Please submit the following to your Assignments Folder:

1. The completed Project1_Census.xlsx file.

2. Submit a Project Reflection (see below).

PROJECT REFLECTION

Answer the following questions when you submit your assignments files.

a. Describe a situation in daily life when someone would use Microsoft Excel.

b. You made several decisions while creating the workbook for this assignment: how to lay out the data in the worksheet and which chart types to use. What was the rationale behind each of these decisions?

c. What issues or challenges did you face completing this project?

SHOW MORE…

Business Statistics

1.Grade

ID

Grade

1-3

Grade

Frequency

Relative Frequency

Percent Frequency

1

A

A

2

B

B

3

A

C

4

B

Total

5

C

6

C

7

C

8

B

1-4

Bar Chart

9

B

10

B

11

B

12

A

13

B

14

B

15

B

16

C

17

B

1-4

Pie Chart

18

C

19

B

20

A

1-1

1-2

2.Cell Service

City

AT&T

Sprint (y)

T-Mobile (x)

Verizon

x-xmean

y-ymean

(x-xmean)(y-ymean)

Atlanta

70

66

71

79

Boston

69

64

74

76

Chicago

71

65

70

77

Dallas

75

65

74

78

Denver

71

67

73

77

Detroit

73

65

77

79

Jacksonville

73

64

75

81

Las Vegas

72

68

74

81

Los Angeles

66

65

68

78

Miami

68

69

73

80

Minneapolis

68

66

75

77

Philadelphia

72

66

71

78

Phoenix

68

66

76

81

San Antonio

75

65

75

80

San Diego

69

68

72

79

San Francisco

66

69

73

75

Seattle

68

67

74

77

St. Louis

74

66

74

79

Tampa

73

63

73

79

Washington

72

68

71

76

2.3. COVARIANCE (Sxy) =

2.4. Correlation Coefficient (rxy) =

2-1.

Q1

Q2

Q3

Q3-Q1

mean

median

mode

25percentile

50percentile

75percentile

IQR

T-Mobile

Sample

Sample

Coefficient

Lower

Upper

Variance

SD

of Variation

Minimum

Maximum

Range

Limit

Limit

T-Mobile

2-2.

x Boxplot

Bonus

City

AT&T

Verizon

Atlanta

70

79

Boston

69

76

Chicago

71

77

Dallas

75

78

Denver

71

77

Detroit

73

79

Jacksonville

73

81

Las Vegas

72

81

Los Angeles

66

78

Miami

68

80

Minneapolis

68

77

Philadelphia

72

78

Phoenix

68

81

San Antonio

75

80

San Diego

69

79

San Francisco

66

75

Seattle

68

77

St. Louis

74

79

Tampa

73

79

Washington

72

76

Q1

Q2

Q3

Q3-Q1

Provider

mean

median

mode

25percentile

50percentile

75percentile

IQR

Sample

Sample

Coefficient

Lower

Upper

Provider

Variance

SD

of Variation

Minimum

Maximum

Range

Limit

Limit

Boxplot

5.Ratings

Meal Price (y)

Quality (x)

1

2

3

Total

1

42

39

3

84

2

33

63

54

150

3

3

15

48

66

Total

78

117

105

300

Joint Probability Table

Meal Price (y)

Quality (x)

1

2

3

Total

1

2

3

Total

1

Quality (x)

f(x)

x*f(x)

x-E(x)

(x-E(x))^2

f(x)*(x-E(x))^2

1

2

3

2

Ratings (y)

y*f(y)

y-E(y)

(y-E(y))^2

f(y)*(y-E(y))^2

1

2

3

3

4 CRN 19915 Z01

Name: Midterm

MGMT 5311

1 Direction

Question and Excel files for Midterm will be available from October 14,

2022 on CANVAS

Download two files: MGMT 5311 Z01 Midterm Data.xls and

MGMT 5311 Z01 Midterm.pdf

Must use excel functions only used in the class examples.

The following excel function are NOT allowed:

STDEV.S, CORREL AND COVARIANCE.S

The following two files (Excel and Document) must be submit-

ted on canvas.

Must submit the Midterm Excel file which must include all your

answers calculated by excel function(s) by 12 pm, October 21,

2022.

Submit the Midterm Answer file for not using excel.

Late submission will not be available to get full credit.

1

CRN 19915 Z01

Name: Midterm

MGMT 5311

Problem 1: 20 pts

A student has completed 20 courses in the School of Arts and Sciences. Her grades in the 20

courses are shown below:

A B A B C

C C B B B

B A B B B

C B C B A

1. Define a best variable name for the given data. (2 pts)

2. What is the type of variable? (3 pts)

3. Fill the following blank. (10 pts)

Grade Frequency Relative Frequency Percent Frequency

A

B

C

Total 20 1 100

4. Develop Bar chart and Pie chart for Frequency. (5 pts)

2

CRN 19915 Z01

Name: Midterm

MGMT 5311

Problem 2: 25 pts

Consumer Reports provides overall customer satisfaction scores for ATT, Sprint, T-Mobile,

and Verizon cell-phone services in major metropolitan areas throughout the United States.

The rating for each service reflects the overall customer satisfaction considering a variety of

factors such as cost, connectivity problems, dropped calls, static interference, and customer

support. A satisfaction scale from 0 to 100 is used with 0 indicating completely dissatisfied

and 100 indicating completely satisfied. Suppose that the ratings for the four cell-phone

services in 20 metropolitan areas are as shown below.

Metropolitan Area AT&T Sprint T-Mobile Verizon

Atlanta 70 66 71 79

Boston 69 64 74 76

Chicago 71 65 70 77

Dallas 75 65 74 78

Denver 71 67 73 77

Detroit 73 65 77 79

Jacksonville 73 64 75 81

Las Vegas 72 68 74 81

Los Angeles 66 65 68 78

Miami 68 69 73 80

Minneapolis 68 66 75 77

Philadelphia 72 66 71 78

Phoenix 68 66 76 81

San Antonio 75 65 75 80

San Diego 69 68 72 79

San Francisco 66 69 73 75

Seattle 68 67 74 77

St. Louis 74 66 74 79

Tampa 73 63 73 79

Washington 72 68 71 76

3

CRN 19915 Z01

Name: Midterm

MGMT 5311

1. Consider T-Mobile first. Find the following the numerical measures in Excel? (10 pts)

mean median mode 25percentile 50percentile 75percentile

(Q1) (Q2) (Q3)

Range IQR Lower Limit Upper Limit

Sample Variance Sample SD Coefficient Minimum Maximum

of Variation

2. Develop Box-plot for T-Mobile. (5 pts)

3. Compute the sample covariance between T-Mobile and Sprint. (5 pts)

4. Compute the sample correlation coefficient between T-Mobile and Sprint. (5 pts)

4

CRN 19915 Z01

Name: Midterm

MGMT 5311

Bonus: 10 pts

For AT&T provider, Finish the following works. (10 pts)

1. Find the following the numerical measures in Excel?

mean median mode 25percentile 50percentile 75percentile

(Q1) (Q2) (Q3)

Range IQR Lower Limit Upper Limit

Sample Variance Sample SD Coefficient Minimum Maximum

of Variation

2. Develop Box-plot for AT&T.

5

CRN 19915 Z01

Name: Midterm

MGMT 5311

Problem 3: 10 pts

Clarkson University Alumni Survey. Clarkson University surveyed alumni

to learn more about what they think of Clarkson. One part of the survey

asked respondents to indicate whether their overall experience at Clarkson

fell short of expectations, met expectations, or surpassed expectations. The

results showed that 4% of the respondents did not provide a response, 26%

said that their experience fell short of expectations, and 65% of the respon-

dents said that their experience met expectations.

a. If we chose an alumnus at random, what is the probability that the alum-

nus would say their experience surpassed expectations? (5 pts)

b. If we chose an alumnus at random, what is the probability that the

alumnus would say their experience met or surpassed expectations? (5

pts)

6

CRN 19915 Z01

Name: Midterm

MGMT 5311

Problem 4: 20 pts

Living with Family. Consider the following example survey results of 18-

to 34-year-olds in the United States, in response to the question Are you

currently living with your family?

Yes No Totals

Men 106 141 247

Women 92 161 253

Totals 198 302 500

a. Develop the joint probability table for these data and use it to answer

the following questions.(5 pts)

b. What are the marginal probabilities for each gender and each response?(5

pts)

c. What is the probability of living with family given you are an 18- to

34-year-old man in the United States? (Hint. P (Y es|Men)?)(5 pts)

d. What is the probability of living with family given you are an 18- to

34-year-old woman in the United States? (Hint. P (Y es|Women)?)(5

pts)

7

CRN 19915 Z01

Name: Midterm

MGMT 5311

Problem 5: 20 pts

Canadian Restaurant Ratings. The Chamber of Commerce in a Canadian

city has conducted an evaluation of 300 restaurants in its metropolitan area.

Each restaurant received a rating on a 3-point scale on typical meal price (1

least expensive to 3 most expensive) and quality (1 lowest quality to 3 greatest

quality). A crosstabulation of the rating data is shown below. Forty-two of

the restaurants received a rating of 1 on quality and 1 on meal price, 39 of

the restaurants received a rating of 1 on quality and 2 on meal price, and so

on. Forty-eight of the restaurants received the highest rating of 3 on both

quality and meal price.

Meal Price (y)

Quality (x) 1 2 3 Total

1 42 39 3 84

2 33 63 54 150

3 3 15 48 66

Total 78 117 105 300

Let x = quality rating and y = meal price.

1. Compute the expected value and variance for quality rating, x. (5 pts)

2. Compute the expected value and variance for meal price, y. (5 pts)

3. The V ar(x+ y) = 1.6691. Compute the covariance of x and y. (5 pts)

4. Compute the correlation coefficient between quality and meal price. (5

pts)

8

CRN 19915 Z01

Name: Midterm

MGMT 5311

Problem 6: 10 pts

Consider a binomial experiment with n = 20 and p = .70.

(You can use Binomial Table.)

1. Compute P (x 16).(3 pts)

2. Compute P (x 15).(3 pts)

3. Compute E(x).(2 pts)

4. Compute V ar(x) and . (2 pts)

9

Direction