Solved by verified expert:Attached are 3 documents. Worksheet #2 is the main assignment that needs to be completed. please make sure to complete all tabsWOrksheet 1 is a completed worksheet, the data from the worksheet will be needed to complete worksheet 2Worksheet instructions is just a break down of what is required
worksheet_1.xlsx
worksheet__2.xlsx
worksheet_instructions.xlsx
Unformatted Attachment Preview
More
Histogram
25
20
15
10
5
0
Mo…
77
75.4
73.8
58.7
70.6
69
67.4
65.8
64.2
Frequency
62.6
Frequency
2
4
9
15
15
22
12
20
2
0
0
61
61
62.6
64.2
65.8
67.4
69
70.6
73.8
75.4
77
Frequency
58.7
Frequency
←
←
←
←
←
←
←
Copy the data from the Data
tab. Make sure and paste as
“Values (V)” (icon with “123”)
so that this data does not
regenerate every time you
change the sheet.
Descriptive Statistics
Mean
Median
Mode
Range
Minimum
Maximum
Standard Deviation
Your height
z score for your height
Low
58.7
61.00
62.60
64.20
65.80
67.40
69.00
70.60
72.20
73.80
75.40
67.8
67.7
69
75.1
58.7
75.1
3.29403018
68.3
0.2
Frequency Distr
High
61.00
62.60
64.20
65.80
67.40
69.00
70.60
72.20
73.80
75.40
77.00
(remember to create the
2.3
Frequency
Generated Data
71.9
69.7
65.0
68.7
64.1
69.0
62.6
67.1
66.1
69.3
63.8
64.5
71.7
66.2
64.8
67.2
72.8
72.1
72.6
63.9
67.7
65.1
64.4
71.1
68.6
69.9
67.1
63.2
67.3
68.3
72.2
67.7
73.7
65.6
60.4
69.2
70.1
61.7
63.3
74.2
68.5
67.2
64.7
71.4
64.8
20
18
16
14
12
10
8
6
4
2
0
67.6
68.3
71.4
71.5
69.9
68.0
70.9
63.5
67.1
65.2
67.8
66.5
73.4
66.0
64.5
70.0
70.8
70.9
71.0
70.3
70.1
66.0
69.0
70.7
58.7
72.1
66.1
66.7
66.4
70.6
67.5
68.1
67.5
64.3
65.5
62.8
65.7
64.4
67.8
72.6
66.8
68.4
72.8
64.1
69.7
72.0
62.5
64.8
67.9
69.0
62.5
67.7
69.9
75.1
67.5
63.5
68.9
Frequency Distribution
Bins
Cumulative Frequency
[58.7,61.00
1
[61.00,62.60)
2
[62.60,64.20)
6
[64.20,65.80)
15
[65.80,67.40)
30
[67.40,69.00)
45
[69.00,70.60)
64
[70.60,72.20)
78
[72.20,73.80)
93
[73.80,75.40)
100
[75.40,77.00)
102
(remember to create the Histogram, too).
Frequency
Using the data set of 102 random heights provided us
formulas to find the following:
1
1
4
9
15
15
19
14
15
7
2
Mean
Median
Mode
Range
Minimum
Maximum
Standard deviation
Frequency distribution
Histogram
The z score for your height (use the computed
Frequency
Bins
[58.7,61.00
[61.00,62.60)
[62.60,64.20)
[64.20,65.80)
[65.80,67.40)
[67.40,69.00)
[69.00,70.60)
[70.60,72.20)
[72.20,73.80)
[73.80,75.40)
[75.40,77.00)
dom heights provided use Excel
height (use the computed mean and
Frequency
1
1
4
9
15
15
19
14
15
7
2
Budget Expenses
Requirements: Answer each question fully. Answers must be recorded on the worksheet. EXCEL FORMULAS MUST BE USED.
Points Possible
Location identified
Travel Expenses identified and calculated for
the entire group with an Excel formula
Lodging Expenses identified and calculated for
the entire group with an Excel formula
Food Expenses identified and calculated for the
entire group with an Excel formula
Entertainment Expenses identified and
calculated for the entire group with an Excel
formula
Total Budget
Question 1 total
Points Earned
Comments
2
14
14
14
14
2
60
0
Loans
Requirements: Answer each question fully. Answers must be recorded on the worksheet. EXCEL FORMULAS MUST BE USED.
Possible Points
Calculations for both loans are correct
Explain in 25 to 100 words which loan is the
best option and why.
Question 2 total
Points Earned
Comments
21
14
35
0
Stats
Requirements: Answer each question fully. Answers must be recorded on the worksheet. EXCEL FORMULAS MUST BE USED.
Mean, Median, Mode
Range, Minimum, Maximum
Standard Deviation
Frequency Distribution
Histogram
z-Score
Points Possible
6
3
4
6
6
5
Question 3 total
30
Points Earned
0
Worksheet 1
Total for Worksheet 1
125
0
Comments
Location
Transportation
Item
JFK to MIA airfare
Taxi to hotel
Car rental 2 cars
Qty
Price each
Total price
Source
6
$348.60
$2,091.60 https://www.expedia.com/FlightChecko
2
$18
$36 http://www.uber-fare-estimator.com/ro
6
$96
$573.96 https://www.expedia.com/carsearch/de
Item
1 week aprtment (3 people)
Qty
Price each
Total price
2
$1,778.00
$3,556.00 https://www.booking.com/hotel/us/arts
Lodging
Food
Item
Description
Water 2 cases (16.9 fl oz)
Food for 1 week per adult
happy hour drinks
Qty
Price each
Total price
numbers Number or NA Formulas
Link or citation
48
$0.15
$7.20 https://www.walmart.com/ip/Nestle-Pu
6
$72.00
$432.00 https://www.expatistan.com/cost-of-livi
6
$40
$240
Entertainment and Misc.
Item
Numbers
Ultra music festival tickets
Qty
Price each
Total price
numbers Number or NA Formulas
Link or citation
6
$449.00
$2,694.00 https://www.stubhub.com/ultra-music-f
Budget Total:
Formula
ww.expedia.com/FlightCheckout?tripid=1f1adcd6-c491-42cb-b2d3-1d3dbea5d256&c=5d46232e-517b-4e3b-8994-988674caad57
w.uber-fare-estimator.com/route/54tyk
ww.expedia.com/carsearch/details?date1=04%2F06%2F2018&time1=1130AM&date2=04%2F13%2F2018&time2=1030AM&styp=4&locn=
ww.booking.com/hotel/us/arts-fashion-district-by-mare-azur-miami-bay.html?aid=336408;label=miami-ZrcbLv1b42qcYr6OmKrVhQS15456
You are going on a dream vacation with five of your c
are taking a math course, your friends asked you to c
much it will cost each of you. Please identify your loc
the total costs – remember to be thorough so the gro
expense list must include the following (this means t
paid trip)
• Travel expenses, such as plane or bus fare
ww.walmart.com/ip/Nestle-Pure-Life-Purified-Water-16-9-Fl-Oz/14871803
• Lodging expenses. If you plan to camp, yo
camping gear.
ww.expatistan.com/cost-of-living/miami
• Food expenses while you are on
of living in your destination county to find
to budget to pay for food. Another is to re
groceries.
• Entertainment or activity expenses and/o
occur.
ww.stubhub.com/ultra-music-festival-tickets/grouping/51405/?gclid=CMjM0oft4tkCFQbQswodu6QPFg&gclsrc=ds
time2=1030AM&styp=4&locn=Miami%2C%20FL%2C%20United%20States%20(MIA-Miami%20Intl.)&dtyp=4&loc2=&piid=AgAQAIiOgFENL
bLv1b42qcYr6OmKrVhQS154563832741%3Apl%3Ata%3Ap1300%3Ap2%3Aac%3Aap1t1%3Aneg%3Afi%3Atiaud-146342138710%3Akwd-1
am vacation with five of your closest friends for a week. Since you
rse, your friends asked you to come up with a budget to see how
of you. Please identify your location in the US and then determine
mber to be thorough so the group does not run out of money. Your
ude the following (this means that you cannot book an all-expenses
enses, such as plane or bus fare, local travel within the area.
penses. If you plan to camp, you will include expenses for your
nses while you are on your trip. One way to do this is to research cost
your destination county to find out what a local family would expect
o pay for food. Another is to research local restaurants and
ent or activity expenses and/or miscellaneous expenses that may
p=4&loc2=&piid=AgAQAIiOgFENLwqAEY9r6pASIMCFcQIxjMwQIgzMECKgsIARABGNZTIJ3IAw%2CAQAQAKBggBEAEYARoECAEQAA&totalP
3Atiaud-146342138710%3Akwd-12080561%3Alp9004466%3Ali%3Adec%3Adm;sid=dbcdfb8e112055252b1dba5777066d55;checkin=2018
AKBggBEAEYARoECAEQAA&totalPriceShown=287.39&searchKey=1120810339&offerQualifiers=GreatDeal&pickUpCountry=US&dropOffCo
2b1dba5777066d55;checkin=2018-04-13;checkout=2018-04-14;ucfs=1;srpvid=ac9a9c76ca5a0179;srepoch=1520720111;group_adults=6;
al&pickUpCountry=US&dropOffCountry=US&abax=12881.0%7C12882.0%7C12880.0&isQuickOffer=false&pickUpDistance=&dropOffDista
ch=1520720111;group_adults=6;group_children=0;req_adults=6;req_children=0;room1=A%2CA;room2=A%2CA;room3=A%2CA;highlight
=A%2CA;room3=A%2CA;highlighted_blocks=217254301_114149805_6_0_0;all_sr_blocks=217254301_114149805_6_0_0;hpos=7;hapos
114149805_6_0_0;hpos=7;hapos=7;dest_type=city;dest_id=20023181;srfid=c3f8f346fbc3c50df78fa1c150365176accbd131X7;from=sear
50365176accbd131X7;from=searchresults;highlight_room=#hotelTmpl
Loans
Loan A
Loan B
Principal (P) (same for both loans)
$9,630.76
$9,630.76
rate ( r)
6.90%
9.25%
# of Compoundings per year (n)
12
12
time in years (t)
2
3
Payment (PMT)
$431
$307.38
Suppose you all decide you want to financ
offers (the internet is a good source
different in each option. Calculate the mo
over the life of the loan and the total inte
information on the internet. You can find
without providing personal information.
Loan payment formula:
PMT is the payment required to pay off
$10,338.24
$11,065.68
$707.48
$1,434.92
https://www.wellsfargo.com/personal-credit/rates/
You must use Excel formulas including ce
paid and interest paid.
Wells Fargo personal Loan vs Personal Credit
Total Paid
Interest Paid
Source
Discuss which loan would be a b
you all decide you want to finance the trip with a loan. Find two different available
e internet is a good source). Make sure the interest rate and loan length are both
in each option. Calculate the monthly payment amount, the total amount you will pay
life of the loan and the total interest you will pay. NOTE: Do not provide personal
ion on the internet. You can find loan rates on major bank and credit union websites
providing personal information.
Loan payment formula: PMT = P*(r/n)/(1 – (1 + r/n)^(-n*t))
the payment required to pay off a loan of $P at interest rate, r%, compounded n times
per year for t years.
t use Excel formulas including cell references to calculate the loan payment, total
interest paid.
s which loan would be a better option and why: (14 points!)
Generated Data
68.5
71.7
66.0
72.3
71.9
68.0
69.7
65.6
63.7
70.1
70.1
72.0
64.4
69.1
72.1
65.5
68.0
74.0
70.0
65.2
68.4
66.0
68.5
64.8
65.9
71.2
64.5
66.8
72.7
68.7
66.8
66.5
65.8
69.9
65.3
71.3
74.0
75.8
67.9
73.9
70.7
70.8
73.1
63.1
69.0
71.3
65.7
62.7
68.9
69.8
68.8
68.5
64.5
68.7
64.4
72.7
67.5
67.3
66.6
67.1
67.1
66.5
68.9
67.0
68.7
62.3
69.2
64.0
67.0
75.4
62.2
67.4
61.5
67.0
69.5
69.5
72.0
62.2
69.0
68.3
69.9
70.4
61.6
66.7
63.8
67.5
63.6
70.2
64.3
70.0
74.7
63.9
70.2
69.6
65.8
69.8
62.2
57.0
71.8
71.6
66.9
66.8
Part 1
Requirements: Answer each question fully. Use Excel formulas with cell references. Answers must be recorded o
Possible points
Points earned
Five year inflation rate
10
Projection of expenses in Worksheet 1
10
Part 1 total
20
0
Part 2
Requirements: Answer each question fully. Use Excel formulas with cell references. Answers must be recorded o
Possible points
Points earned
Descriptive Statistics
16
Interpret Descriptive Statistics
14
Proportion calculations
10
Interpretation of proportions
10
Conversion of before well
5
Conversion of after well
5
Improvement level data set
5
Descriptive Statistics for improvement levels
10
Histogram
5
Standard error of the mean
5
Confidence interval
10
Discussion of the placement of 0
10
Part 2 total
105
0
Total of Worksheet 2
125
0
t1
references. Answers must be recorded on the worksheet.
Comments
t2
references. Answers must be recorded on the worksheet.
Comments
0%
To prepare for the final project:
This part of the assignment is not graded, an
final Mission Trip project.
Your Mission Trip will occur in 5 years, so yo
able to do the 5 year projections of your Mis
You should also be filling in the details of yo
the Mission Trip assignment:
• An overview of your project: Where are g
the sole purpose of your trip.
• Cultural and social aspects of the country
• How your choice of project works toward
How does your personal worldview and t
trip?
• Information about your target population
population of the country as a whole?
• Aspects of the country that make it a suit
• The economic situation in your co
• Possible concerns that you need
safety from violence or from hea
inal project:
gnment is not graded, and you will not hand it in at this time. It is here to help you stay on track for your
ill occur in 5 years, so you will need to make budget projections into the future. By now, you should be
r projections of your Mission Trip budget.
illing in the details of your narrative. If asked, you should be able to answer the following questions from
our project: Where are going, and what service will you be providing? Remember, evangelism cannot be
of your trip.
al aspects of the country and its population that led you to choose this area of the world.
of project works toward fulfillment of the Judeo-Christian-Islamic ideal to love your neighbor as yourself:
ersonal worldview and the possible worldviews of your target population influence your plans for the
ut your target population: Who are you planning to serve? How does this population compare to the
e country as a whole?
ountry that make it a suitable location for your trip. In particular, include information about
nomic situation in your country and how your project will improve this.
concerns that you need to keep in mind to keep your team and your target population safe. This could be
om violence or from health problems (for example, will your team need inoculations to travel to the
Input your name here
name
Unadjusted CPI, all items for 5 years ago
Unadjusted CPI, all items for last month
Inflation rate: something that cost $1.00 five
years ago would cost what now?
What percent increase is this?
Total budget from Worksheet 1
5 year projected budget total
Month
number
number
Year
number
number
CPI
number
number
formula
formula
number
formula
Part 1 – Budget Projection:
Your friends have decided to delay your dre
and you need to estimate what the cost of y
Step 1: Go to the Bureau of Labor Statistics w
bin/surveymost?cu.
Step 2: Check U.S. All items, 1982-84=100
Step 3: Click “Retrieve Data”
Use the most recent CPI value and the CPI fo
estimate the price of your trip in five years a
dget Projection:
s have decided to delay your dream vacation from Worksheet 1 for five years,
ed to estimate what the cost of your trip will be by then.
to the Bureau of Labor Statistics website at https://data.bls.gov/cgimost?cu.
eck U.S. All items, 1982-84=100
k “Retrieve Data”
ost recent CPI value and the CPI for the same month but five years earlier to
he price of your trip in five years and the five year inflation rate.
Before wells
were dug Millions of
E.Coli per ml
23
21
64
54
72
50
52
49
55
73
55
51
38
28
60
57
59
60
61
57
71
57
63
64
63
23
21
64
54
72
50
52
49
55
73
55
51
38
28
After wells
were dug Millions of
E.Coli per ml
52
3
35
44
49
35
38
10
32
52
17
37
26
0
44
40
30
42
34
33
50
40
38
43
52
0
3
35
44
49
35
38
10
32
52
17
37
26
0
YOUR NAME:
Joe Lope
Before
min = formula
max = formula
mean= formula
SD = formula
sample size = formula/number
0 count = formula/number
Ratio
Percent Clean
min =
max =
mean=
SD =
sample size =
0 count =
Before
formula
Conversions
ml
29.5735
oz
1
In 24 ounces
E.coli before
formula
E. coli after
formula
After
formula
formula
formula
formula
formula/number
formula/number
After
formula
60
57
59
60
61
57
71
57
63
64
63
23
21
64
54
72
50
52
49
55
73
55
51
38
28
60
57
59
60
61
57
71
57
63
64
63
23
21
64
54
72
50
52
49
55
73
55
44
40
30
42
34
33
50
40
38
43
52
0
3
35
44
49
35
38
10
32
52
17
37
26
0
44
40
30
42
34
33
50
40
38
43
52
0
3
35
44
49
35
38
10
32
52
17
51
38
28
60
57
59
60
61
57
71
57
63
64
63
37
26
0
44
40
30
42
34
33
50
40
38
43
52
Part 2 – Data Analysis:
Enter your name in cell F1 to generate data.
You have just completed a mission to Sierra Leone. The goal of the mission was to improve the quality of
water in 100 wells in a certain region. You collected data on the E. coli count from each well before and
after your mission. You need to write a report on the success of the mission and for that you need to
perform some statistical analysis on the data. You will be looking at the data from different perspectives
to determine if the water quality has improve.
1. Calculate descriptive statistics for your data in the table provided in the Excel spreadsheet. Use the
means and standard deviations of the data to decide if it appears that there has been improvement in
water quality? (Fill in the before (F3:F8) and after (H3:H8) tables to the left for the descriptive statistics.
The data has been named before and after for your convenience in creating formulas.)
Answer here:
2. The water quality is “good” if the count of E coli is 0; otherwise, the water quality is still bad. Calculate
the proportion of wells with “good” water to wells whose water is not good. From this measure does it
appear that the quality of water improved? Explain and use the proportions that you calculated. (In G11
and H11 calculate the percent Clean for before and after.)
Answer here:
.
3. Look at well #1 (B2 and C2) in your data. If you drank 24oz of water how many E.coli would you ingest if
you drank from the well before the mission? After the mission? (In E19 and G19 calculate how many E.coli
would you ingest if you drank 24 oz. of water from Well 1 before the mission and after the mission.)
ou ingest if
many E.coli
Original
Before
Data
78
19
32
125
53
68
4
106
38
36
4
17
43
23
32
49
36
2
33
58
75
82
80
70
79
73
76
72
72
70
84
81
69
85
100
70
74
63
Original
After
Data
67
4
23
110
41
42
10
79
6
16
14
5
9
3
8
28
18
21
22
25
59
63
60
52
50
66
54
42
55
53
54
62
59
52
69
57
45
39
76
78
87
71
83
71
75
76
63
70
65
83
76
78
76
68
77
75
67
74
86
85
72
73
59
72
64
67
79
64
86
74
83
81
70
71
68
76
72
71
86
86
88
78
73
84
78
60
75
64
41
67
56
57
58
39
38
50
59
48
59
49
47
51
58
53
45
58
67
48
65
43
55
25
48
55
33
65
53
61
55
47
54
54
64
55
55
77
62
67
59
45
57
53
77
88
83
70
65
74
73
79
87
79
77
66
73
85
77
57
68
61
32
48
52
53
58
59
57
55
52
58
63
55
Random
seed
numbers
9
17
25
33
41
49
57
65
73
81
89
97
5
13
21
29
37
45
53
61
69
77
85
93
1
9
17
25
33
41
49
57
65
73
81
89
97
5
8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
63
4
17
110
38
53
0
91
23
21
0
2
28
8
17
34
21
0
18
43
60
67
65
55
64
58
61
57
57
55
69
66
54
70
85
55
59
48
52
0
8
95
26
27
0
64
0
1
0
0
0
0
0
13
3
6
7
10
44
48
45
37
35
51
39
27
40
38
39
47
44
37
54
42
30
24
13
21
29
37
45
53
61
69
77
85
93
1
9
17
25
33
41
49
57
65
73
81
89
97
5
13
21
29
37
45
53
61
69
77
85
93
1
9
17
25
33
41
49
57
65
73
81
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
61
63
72
56
68
56
60
61
48
55
50
68
61
63
61
53
62
60
52
59
71
70
57
58
44
57
49
52
64
49
71
59
68
66
55
56
53
61
57
56
71
71
73
63
58
69
63
45
60
49
26
52
41
42
43
24
23
35
44
33
44
34
32
36
43
38
30
43
52
33
50
28
40
10
33
40
18
50
38
46
40
32
39
39
49
40
40
62
47
52
44
30
42
38
89
97
5
13
21
29
37
45
53
61
69
77
85
93
1
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
62
73
68
55
50
59
58
64
72
64
62
51
58
70
62
42
53
46
17
33
37
38
43
44
42
40
37
43
48
40
Before
wells
were dug Millions of
E.Coli per
ml
After
wells
were dug Millions of Improvement
E.Coli per Level:
ml
Before – After
23
21
64
54
72
50
52
49
55
52
3
35
44
49
35
38
10
32
IMPROVEMENTS
min =
formula
max =
formula
mean=
formula
SD =
formula
SE =
formula
73
55
51
38
28
60
57
59
60
61
57
71
57
63
64
63
23
21
64
54
72
50
52
49
55
73
55
51
38
52
17
37
26
0
44
40
30
42
34
33
50
40
38
43
52
0
3
35
44
49
35
38
10
32
52
17
37
26
Low
High
Bins
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
Formula/NumberFormula/Number
words or formula
(remember to create the Histogram, too).
Frequency Distribution
95% Confidence Interval
Lower number
to
Higher number
formula
to
formula
28
60
57
59
60
61
57
71
57
63
64
63
23
21
64
54
72
50
52
49
55
73
55
51
38
28
60
57
59
60
61
57
71
57
63
64
63
23
21
64
54
72
50
52
49
55
73
0
44
40
30
42
34
33
50
40
38
43
52
0
3
35
44
49
35
38
10
32
52
17
37
26
0
44
40
30
42
34
33
50
40
38
43
52
0
3
35
44
49
35
38
10
32
52
55
51
38
28
60
57
59
60
61
57
71
57
63
64
63
17
37
26
0
44
40
30
42
34
33
50
40
38
43
52
ncy Distribution
Cumulative
Frequency
Formula
Formula
Formula
Formula
Formula
Formula
Formula
Formula
Formula
Formula
Formula
eate the Histogram, too).
Part 2 – Data Analysis:
You have just completed a mission to Sierra Leone. The goal …
Purchase answer to see full
attachment
