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