CS101 Infection Rate Charts i upload the excel files that you will work on it , and the instructions. please follow the instructions and be careful HOMEWOR

CS101 Infection Rate Charts i upload the excel files that you will work on it , and the instructions. please follow the instructions and be careful HOMEWORK INSTRUCTIONS
Homework #2
HIV Statistics Problem
Background Information
The scourge of HIV/AIDS has had an extraordinary impact on the entire
world. The spread of the disease has been closely tracked since the
discovery of the HIV virus in the 1980s. Since that time, many millions of
people have died from AIDS.
While there are now pharmaceuticals available to help manage HIV/AIDS,
the disease is still extremely deadly and enormously expensive to treat. As the data
presented in this assignment shows, the future of an HIV-infected person can be
directly impacted by the part of the world in which they live.
Problem Statement
In this assignment, students will analyze the growth of HIV/AIDS throughout the
world using UNAIDS and World Health Organization statistics. They will also look at
the relationship between economic conditions and the spread of the epidemic.
Instructions
IMPORTANT: Complete the steps below in the order they are given. Completing the
steps out of order may complicate the assignment or result in an incorrect result.
1.
Download and extract the provided Data Files ZIP file. It contains the following
files for use in this assignment:
a.
hivstatistics.csv – Statistics on living with HIV for regions of the world
between 2004 and 2014 [1]–[5].
Column Name
Region
2004
Type
Text
Number
2006
Number
2008
Number
2010
Number
2012
Number
2014
Number
Introduction to Computer Applications
West Virginia University
Page 1 of 7
Description
Name of region.
Estimated number
in 2004.
Estimated number
in 2006.
Estimated number
in 2008.
Estimated number
in 2010.
Estimated number
in 2012.
Estimated number
in 2014.
of people living with HIV
of people living with HIV
of people living with HIV
of people living with HIV
of people living with HIV
of people living with HIV
Version 21.4
Modified 5/23/2018
HOMEWORK INSTRUCTIONS
Homework #2
HIV Statistics Problem
b.
infections.csv – Statistics on new HIV infections for regions of the world
between 2004 and 2014 [1]–[4], [6].
Column Name
Region
2004
Type
Text
Number
2006
Number
2008
Number
2010
Number
2012
Number
2014
Number
Description
Name of region.
Estimated number
with HIV in 2004.
Estimated number
with HIV in 2006.
Estimated number
with HIV in 2008.
Estimated number
with HIV in 2010.
Estimated number
with HIV in 2012.
Estimated number
with HIV in 2014.
2.
Create a new Microsoft Excel workbook named
lastname_firstname_hw2_hivsp.xlsx.
3.
We must adjust the sheets in our workbook.
4.
5.
a.
Rename Sheet1 to HIV Statistics.
b.
Add a new sheet named New Infections.
c.
Add a new sheet named Infection Rates.
d.
Add a new sheet named Analysis Questions.
of people newly infected
of people newly infected
of people newly infected
of people newly infected
of people newly infected
of people newly infected
Import the following items into the workbook:
a.
hivstatistics.csv file – Import starting in cell A3 of the HIV Statistics
sheet. The file is comma-delimited. Its first row contains headers.
b.
infections.csv file – Import starting in cell A3 of the New Infections
sheet. The file is comma-delimited. Its first row contains headers.
We wish to apply formatting to the HIV Statistics sheet.
a.
We must setup a table to store data on HIV.
i.
If a table does not already exist in cells A3 through G9, create one
using a style of your choice. The table has headers and will overlap
external data ranges. If prompted, convert the selection to a table
and remove all external connections.
ii.
If a table already exists in cells A3 through G9, format the table
using a style of your choice other than the default table style.
Introduction to Computer Applications
West Virginia University
Page 2 of 7
Version 21.4
Modified 5/23/2018
HOMEWORK INSTRUCTIONS
Homework #2
HIV Statistics Problem
b.
We need to add additional table columns to store statistics and a
sparkline.
i.
c.
Enter text in the cells as indicated below:
i.
A1: People Living with HIV
ii.
H3: Minimum Value
iii.
I3: Maximum Value
iv.
J3: Infected Increasing
v.
K3: Sparkline
d.
Merge-and-center cells A1 through K1.
e.
Apply the Title cell style to cell A1.
f.
Format the cells as indicated below:
i.
6.
Insert four new table columns to the right of existing column G.
B4 through I9: number with no decimal places, use 1000 separator
g.
AutoFit the width of columns A through K.
h.
Apply Red-Yellow-Green color scale conditional formatting to cells B4
through G9.
We also wish to apply formatting to the New Infections sheet.
a.
b.
We must setup a table to store data on new infections.
i.
If a table does not already exist in cells A3 through G9, create one
using a style of your choice. The table has headers and will overlap
external data ranges. If prompted, convert the selection to a table
and remove all external connections.
ii.
If a table already exists in cells A3 through G9, format the table
using a style of your choice other than the default table style.
Enter text in the cells as indicated below:
i.
A1: New HIV Infections
c.
Merge-and-center cells A1 through G1.
d.
Apply the Title cell style to cell A1.
e.
Format the cells as indicated below:
i.
f.
B4 through G9: number with no decimal places, use 1000 separator
AutoFit the width of columns A through G.
Introduction to Computer Applications
West Virginia University
Page 3 of 7
Version 21.4
Modified 5/23/2018
HOMEWORK INSTRUCTIONS
Homework #2
HIV Statistics Problem
7.
We want must also apply formatting to the Infection Rates sheet.
a.
Enter text in the cells as indicated below:
i.
A1: HIV Infection Growth Rates
ii.
A3: Region
iii.
B3: 2004
iv.
C3: 2006
v.
D3: 2008
vi.
E3: 2010
vii.
F3: 2012
viii. G3: 2014
ix.
A4: Asia and the Pacific
x.
A5: Eastern Europe and Central Asia
xi.
A6: Latin America and the Caribbean
xii.
A7: Middle East and North Africa
xiii. A8: Sub-Saharan Africa
xiv. A9: Western and Central Europe and North America
8.
b.
Merge (but not center) cells A1 through G1.
c.
Apply the Title cell style to cell A1.
d.
Format cells A3 through G9 as a table using a style of your choice. The
table has headers.
On the Infection Rates sheet, we wish to calculate infection statistics.
a.
In column B, write a formula to calculate, for each region, the annual
infection growth rate. Reference the values on the HIV Statistics and New
Infections sheet in your formula.
You can calculate the infection growth rate using the formula:
[ ]
[ ℎ ]
b.
9.
In columns C through G, duplicate your infection growth rate formula
from column B.
We must apply additional formatting to the Infection Rates sheet.
a.
Format the cells as indicated below:
i.
B4 through G9: percentage with 1 decimal place
Introduction to Computer Applications
Page 4 of 7
West Virginia University
Version 21.4
Modified 5/23/2018
HOMEWORK INSTRUCTIONS
Homework #2
HIV Statistics Problem
b.
AutoFit the column width for columns A through G.
10. On the HIV Statistics sheet, we wish to calculate people living with HIV
statistics.
a.
In column H, write a formula to calculate the minimum number of people
living with HIV in each region.
b.
In column I, write a formula to calculate the maximum number of people
living with HIV in each region.
c.
In column J, write a formula to determine if the region’s 2014 number of
people living with HIV was greater than its 2004 number. If it was, display
Increasing; otherwise, display Not Increasing.
11. We wish to add a sparkline to the HIV Statistics sheet to compare data.
In column K, insert a line-type sparkline based on the values in columns B
through G.
12. We wish to create a chart to plot the infection growth rate in all regions for
each year.
a.
Create a 2-D line chart based on cells A3 through G9 of the Infection
Rates sheet. Move the chart to a new sheet named Infection Rates Chart.
Ensure the years are shown as labels for the horizontal (category) axis,
not plotted as chart data. Specify appropriate chart and axis titles.
b.
Add a trendline based on the infection rate for the Western and Central
Europe and North America. Use the trendline type that best fits the data
and project the values forward 5 periods (through the year 2024). Display
the R-squared value on the chart.
NOTE: You cannot use the Moving Average type for your trendline.
13. We need to set up the Analysis Questions sheet so that it can store responses
to the analysis questions.
a.
Enter text in the cells as indicated below:
i.
A1: Question Number
ii.
B1: Response
b.
Bold the contents of row 1.
c.
AutoFit the width of column A. Set the width of column B to 100 (8.39”).
d.
Set the height for rows 2 through 5 to 110 (1.53”).
e.
Change the vertical alignment setting for columns A and B so that the
text is displayed at the top of each row.
Introduction to Computer Applications
West Virginia University
Page 5 of 7
Version 21.4
Modified 5/23/2018
HOMEWORK INSTRUCTIONS
Homework #2
HIV Statistics Problem
f.
Turn on text wrapping for column B.
14. Starting in row 2 of the Analysis Questions sheet, answer four of the five
analysis questions below. Respond to one question per row.
a.
Which trendline type did you use on Infection Rates Chart? Why did you
choose this type of trendline?
b.
What sort of factors might cause the HIV infection growth rate to increase
or decrease in the future?
c.
Why might the number of reported HIV cases not be an accurate
reflection of how many people actually have HIV?
d.
Looking at the regional HIV statistics, what sort of relationships can you
deduce between an area’s general economic condition and its new
infection rates? Why might this relationship exist?
e.
What sort of effect might the high prevalence of HIV have on the
demographics and economy of Sub-Saharan Africa in the future?
Grading Rubric
This assignment is worth 50 points. It will be graded by your instructor using this
rubric, with partial credit awarded as appropriate:
Steps 3a-d
Step 4
Steps 5a-g
Step 5h
Steps 6a-f
Steps 7a-d
Steps 8a-b
1.5 points total
3 points
3.5 points total
2.5 points
3 points total
3 points total
4.5 points total
Steps 9a-b
Steps 10a-c
Step 11
Step 12a
Step 12b
Steps 13a-f
Steps 14a-e (pick 4 of 5)
1.5 points total
4.5 points total
3 points
4 points
3 points
3 points total
2.5 points each
The analysis questions in Steps 14a-e will be evaluated using this rubric:
Standard
Answer is reasonable.
Answer is supported.
Meets Requirements
(1.25 points)
Answer addresses the
question prompt and is
factually correct or a
reasonable interpretation of
available data.
Logical rationale is provided
to support the given
answer.
Does Not Meet
Requirements (0 points)
Answer does not address
the question prompt, is
factually incorrect, or is an
unreasonable interpretation
of available data.
Logical rationale is not
provided to support the
given answer.
Acknowledgments
The image in the introduction appears courtesy of Amada44 [7].
Introduction to Computer Applications
Page 6 of 7
West Virginia University
Version 21.4
Modified 5/23/2018
HOMEWORK INSTRUCTIONS
Homework #2
HIV Statistics Problem
References
[1] “AIDS epidemic update,” UNAIDS, Dec. 2006. Available:
http://data.unaids.org/pub/EpiReport/2006/2006_EpiUpdate_en.pdf.
[2] “AIDS epidemic update,” UNAIDS, 2009. Available:
http://data.unaids.org/pub/report/2009/jc1700_epi_update_2009_en.pdf.
[3] “UNAIDS World AIDS Day Report,” UNAIDS, 2011.
http://www.unaids.org/en/media/unaids/contentassets/documents/unaidspublic
ation/2011/JC2216_WorldAIDSday_report_2011_en.pdf.
[4] “UNAIDS report on the global AIDS epidemic 2013,” UNAIDS, 2013. Available:
http://www.unaids.org/sites/default/files/media_asset/UNAIDS_Global_Report_
2013_en_1.pdf.
[5] “AIDS by the numbers 2016.” UNAIDS. Available:
http://www.unaids.org/en/resources/documents/2016/AIDS-by-the-numbers.
[6] “AIDS by the numbers 2015.” UNAIDS. Available:
http://www.unaids.org/en/resources/documents/2015/AIDS_by_the_numbers_
2015.
[7] Amada44, World AIDS Day Ribbon. 2007. Available:
https://commons.wikimedia.org/wiki/File:World_Aids_Day_Ribbon.svg.
Introduction to Computer Applications
West Virginia University
Page 7 of 7
Version 21.4
Modified 5/23/2018

Purchase answer to see full
attachment