2 Excel Problems Help

i need the last 2 problems done #’s 5 and 6. the tabs for the questions are on the bottom of the file.  i finished problems 1-4 myself, but if you can doublecheck my work and if there are any mistakes please fix them. 

please email me an example of the excel work you have done in the past

Instructions

Take-home portion of Test 1 (worth 24 points; comprises six problems) Independent work on this take-home portion of Test 1 is required, with you neither giving to nor receiving assistance from another person. Due by beginning of class on Friday, February 12: The Excel file containing your work, to be submitted through our D2L account in the Dropbox titled Take-home portion of Test 1. Due at beginning of class on Friday, February 12: A two-page printout of your work. Specifically, after using Excel to work the problems on sheets 1 through 6, copy and paste (as pictures) the results/answers to a Word document, in order by problem number. As needed, adjust the size of the pictures so that your Word document is only 2 pages long. Turn in a printout of the Word document. Friendly reminder: All bar charts, histograms, and line graphs call for axis labels/titles. In addition, any break in a histogram’s x-axis or y-axis scale should be indicated (e.g., by parallel hashmarks).

1

Below, from the Bureau of Economic Analysis, are the profits (in $ billions)
of the U.S. financial industry for the years 2000 through 2014.
Display the profits in the form of a line graph, using as the
chart title Profits of U.S. Financial Industry, 2000-2014 (source: Bureau of Economic Analysis)
YearProfits (in $ billions)
2000154.9
2001201.7
2002280.0
2003306.3
2004336.1
2005367.7
2006375.3
2007261.0
200864.1
2009321.7
2010368.2
2011355.7
2012438.2
2013385.8
2014383.6

Profits of U.S. FInancial Industry, 2000-2014 (source: Bureau of Economic Analysis)

Profits (in $ billions) 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 154.9 201.7 280 306.3 336.1 367.7 375.3 261 64.099999999999994 321.7 368.2 355.7 438.2 385.8 383.6

Years

Profits in $ Billions

2

The manager of a fast-food establishment periodically monitors various aspects of its operations, including service time, i.e.,
the time (in seconds) between placing and receiving an order. Below are the service times for a sample of 48 recent drive-through customers.
84787695841019178
121991049574878496
871095891927296109
76791087287677569
79111727675866874
10187111103120579786
Use Excel statistical functions to determine each of the following measures for the sample of drive-through customers.
(a) mean service time87.4375
(b) median service time86.5
(c) variance in the service times236.8896276596
(d) standard deviation in the service times15.3912191739
(e) range in the service times64

3

Below is the percentage distribution for the preferred shift of the 820 package handlers
employed by a large package delivery company called WeDeliver.
Preferred Shift% of package handlers
Midnight15%
Six am29%
Noon43%
Six pm13%
Portray the percentage distribution in the form of a bar chart,
using as the chart title Preferred shift of WeDeliver’s 820 package handlers

% of package handlers Midnight Six am Noon Six pm 0.15 0.28999999999999998 0.43 0.13

Preferred shift of WeDeliver’s 820 package handlers

% of package handlers Midnight Six am Noon Six pm 0.15 0.28999999999999998 0.43 0.13

Preferred Shift

% of package handlers

4

Below (in column A) are the water usage levels (in gallons) on 2/1/2016 of the 120 condos in the Lark Complex.
Summarize the water usage levels in the form of a frequency distribution (in table form), using as the numeric classes the intervals
15 to under 20, 20 to under 25, 25 to under 30, 30 to under 35, 35 to under 40, 40 to under 45, 45 to under 50, and 50 to under 55.
Water usage
22.8
28.0
29.1Water UsageFrequency
46.515 to under 206.0
30.520 to under 2523.0
30.025 to under 3033.0
31.130 to under 3523.0
26.235 to under 409.0
32.640 to under 4512.0
27.045 to under 5011.0
47.350 to under 553.0
32.4
45.2
46.8
27.8
28.9
46.4
21.8
28.3
28.0
21.4
28.8
30.2
37.9
22.7
40.6
40.3
23.6
24.1
23.6
41.2
28.6
48.0
25.1
35.5
37.6
28.0
46.3
25.8
30.1
22.5
41.9
23.8
26.0
43.9
33.4
27.3
44.4
33.4
48.9
28.6
42.5
46.6
53.6
28.8
30.1
32.3
36.8
22.4
31.5
31.2
27.8
44.0
22.4
15.1
28.2
23.3
29.0
24.7
21.3
36.4
33.5
22.1
41.0
29.6
32.4
24.4
25.0
24.0
43.1
18.0
45.1
30.3
17.9
33.4
28.4
22.9
28.7
38.4
24.9
30.7
42.9
35.3
32.1
29.4
21.7
26.8
34.6
25.1
48.1
31.8
50.0
29.3
27.8
51.3
23.2
41.6
22.3
32.3
30.6
29.1
25.9
19.7
26.8
37.0
25.8
19.4
21.6
39.2
16.8

Water usage 22.8 28 29.099999999999998 46.5 30.5 30 31.099999999999998 26.2 32.6 27 47.300000000000004 32.4 45.2 46.800000000000004 27.8 28.9 46.400000000000006 21.8 28.3 28 21.4 28.8 30.2 37.900000000000006 22.7 40.6 40.300000000000004 23.599999999999998 24.099999999999998 23.599999999999998 41.2 28.599999999999998 48 25.099999999999998 35.5 37.6 28 46.300000000000004 25.8 30.099999999999998 22.5 41.900000000000006 23.8 26 43.900000000000006 33.4 27.3 44.400000000000006 33.4 48.900000000000006 28.599999999999998 42.5 46.6 53.6 28.8 30.099999999999998 32.300000000000004 36.800000000000004 22.4 31.5 31.2 27.8 44 22.4 15.100000000000001 28.2 23.3 29 24.7 21.3 36.400000000000006 33.5 22.099999999999998 41 29.599999999999998 32.4 24.4 25 24 43.1 18 45.1 30.3 17.899999999999999 33.4 28.4 22.9 28.7 38.400000000000006 24.9 30.7 42.900000000000006 35.300000000000004 32.1 29.4 21.7 26.8 34.6 25.099999999999998 48.1 31.8 50 29.3 27.8 51.300000000000004 23.2 41.6 22.3 32.300000000000004 30.599999999999998 29.099999999999998 25.9 19.7 26.8 37 25.8 19.399999999999999 21.599999999999998 39.200000000000003 16.8

5

Below are data on 97 homes sold in a resort community in 2015. [Adapted from Business Statistics by Robert Donnelly, 2012]
Use Excel’s PivotTable module to cross-classify the homes by Location and Price (in $1000s),
using Price intervals of 150-299, 300-449, 450-599, 600-749, 750-899, 900-1049, and 1050-1199.
Price (in $1000’s)BedroomsBathroomsDays on MarketAgeSquare FeetLocation
741447143,424Golf Course
79143102102,980Wooded
66542.539192,790Golf Course
76542.544212,840Golf Course
47233118321,968Wooded
5313335251,933Wooded
65032145241,699Golf Course
58242.5207222,018Wooded
8154442153,217Golf Course
40232.548221,610Wooded
35532150301,652Wooded
41633107261,750Wooded
58033183272,000Wooded
33022261341,700Wooded
47533195272,484Wooded
114954118143,550Golf Course
189113220520Wooded
5653232292,450Golf Course
50232.553241,660Wooded
9154447173,267Golf Course
52543.5181212,210Wooded
82043200142,110Wooded
104553.59843,277Golf Course
64433.589272,324Wooded
70042.5158182,779Wooded
57533200292,534Wooded
57233123342,018Wooded
54732.511222,100Wooded
2822298331,210Wooded
42533.5228331,690Golf Course
72043195122,060Wooded
5103326282,910Golf Course
57932.5373192,300Golf Course
98743.5105122,905Golf Course
67043111222,337Golf Course
115053.5484,929Golf Course
79643.54283,460Wooded
51022120302,248Wooded
51633112281,800Wooded
57043106202,287Golf Course
89143107123,030Wooded
33532160271,677Wooded
57433205241,650Golf Course
8154212992,262Wooded
61642144282,240Wooded
51843.525252,320Golf Course
75043.57312,476Golf Course
8584369143,050Golf Course
41022115282,198Wooded
38532217291,416Wooded
7584364123,000Golf Course
88743.5100102,855Golf Course
77443.525182,360Golf Course
45532155321,702Wooded
80042.5163202,829Wooded
1013541752,820Golf Course
61843.530272,370Golf Course
6313340271,983Wooded
89643.547103,510Wooded
55032140221,649Golf Course
71642149302,290Wooded
43022266361,750Wooded
4103321262,860Golf Course
4172227231,365Golf Course
47122156351,838Golf Course
48242.5202201,968Wooded
44732.56202,050Wooded
54433.584252,274Wooded
913541232,770Golf Course
83343107242,610Golf Course
43532165291,727Wooded
94553.59323,227Golf Course
289113722570Wooded
67443.520162,310Golf Course
104954113123,500Golf Course
3102299331,210Wooded
43032139291,992Wooded
73343102222,560Golf Course
67433210261,700Golf Course
41022104351,260Wooded
5172232251,415Golf Course
48532222311,466Wooded
52533.5233351,740Golf Course
62543.5186232,260Wooded
67932.5378212,350Golf Course
4653227272,400Golf Course
4502253331,896Wooded
38222103351,260Wooded
56632.559261,830Wooded
8414412163,474Golf Course
37122151331,788Golf Course
68033188292,050Wooded
3502248311,846Wooded
46632.554241,780Wooded
53032144312,042Wooded
65043.52292,426Golf Course
91542134112,312Wooded

6

Below is the (hypothetical) frequency distribution of the monthly rents of the homes being rented
in the Pope High School district.
Display the frequency distribution in the form of a histogram
using as the chart title Monthly Rents of Homes for Rent in Pope High School District
Monthly Rent ($)# of homes
1000-10993
1100-11994
1200-12997
1300-13999
1400-149911
1500-159928
1600-169935

# of homes 1000-1099 1100-1199 1200-1299 1300-1399 1400-1499 1500-1599 1600-1699 3 4 7 9 11 28 35