please see attachments and what it ask follow each step by step

please see attachments and what it ask follow each step by step


Washington DC Memorial Visitors
Source: https://irma.nps.gov/Stats/Reports/Park/FRDE
Year Completed
2000
Lincoln Memorial
1922
4,009,145
World War II Memorial
2004
Vietnam Veterans Memorial 1982
3,782,445
Korean War Veterans Memorial
1994
2,923,716
Franklin Delano Roosevelt Memorial
1997
2,918,846
Jefferson Memorial
1943
3,458,956
2005
3,638,806
4,410,379
3,799,968
3,214,467
2,852,565
2,944,976
2010
6,042,315
3,964,351
4,555,371
3,072,716
2,238,052
2,436,110
2015 Trend
7,941,771
5,068,224
5,597,077
4,077,835
3,290,080
1,698,656
Sightseeing Locations
Edgar Allan Poe National Historic Site
White House
King’s Chapel Burial Ground
Park Street Church
City Hall
Fireman’s Hall
Vietnam Veterans Memorial
Korean War Veterans Memorial
Faneuil Hall Marketplace
Powel House
Old City Hall
Washington Monument
Cathedral Basilica of Saint Peter and Paul
Martin Luther King, Jr., Memorial
Elfreth’s Alley
Fenway Park
Christ Church
Old State House
Arlington National Cemetery
Franklin Delano Roosevelt Memorial
Old South Meeting House
USS Constitution Museum
Boston Massacre Site
Washington Square Park
National World War II Memorial
Jefferson Monument
Bunker Hill Monument
Congress Hall
Independence Hall
United States Capitol
Betsy Ross House
Library of Congress
Ford’s Theatre
Paul Revere House
Todd House
Massachusetts State House
Lincoln Memorial
Supreme Court
Old North Church
City
Philadelphia
Washington DC
Boston
Boston
Philadelphia
Philadelphia
Washington DC
Washington DC
Boston
Philadelphia
Philadelphia
Washington DC
Philadelphia
Washington DC
Philadelphia
Boston
Philadelphia
Boston
Washington DC
Washington DC
Boston
Boston
Boston
Philadelphia
Washington DC
Washington DC
Boston
Philadelphia
Philadelphia
Washington DC
Philadelphia
Washington DC
Washington DC
Boston
Philadelphia
Boston
Washington DC
Washington DC
Boston
Fee
Time Needed
$0.00
120
$0.00
120
$0.00
30
$0.00
30
$12.00
120
$0.00
60
$0.00
60
$0.00
60
$0.00
120
$5.00
60
$0.00
60
$0.00
60
$0.00
60
$0.00
60
$5.00
60
$25.00
60
$5.00
60
$10.00
75
$0.00
180
$0.00
60
$6.00
60
$10.00
75
$0.00
30
$0.00
30
$0.00
45
$0.00
30
$0.00
75
$0.00
30
$0.00
90
$0.00
120
$5.00
60
$0.00
60
$3.00
120
$3.50
60
$0.00
60
$0.00
120
$0.00
45
$0.00
90
$3.00
30
Departure Date
Return Date
Lodging Base/Night
$
Total Base Lodging
$
City
1
2
3
4
Destination City
Boston
Miami
Orlando
Philadelphia
San Antonio
Washington DC
8/18/2018
8/23/2018
Ground Transportation
Round-trip Shuttle
$
Rental Car per Day
$
Rental Car Total
115
575
COL
95%
110%
125%
140%
City Type
3
2
1
3
1
4
Rental
Car?
No
Yes
Yes
No
Yes
No
Airfare
$
367
392
299
479
218
469
Shuttle or
Rental
50
30
Lodging
Summary Trip Costs
Average
Lowest
Highest
Meals Total Cost
$
375
330
285
375
285
420
Travel Vacations
Project Description:
You are considering several cities for a vacation. In particular, you are interested in Washington DC, Philadelphia,
and Boston. You will format a list of memorials in DC, add Sparklines to compare the number of visitors over a
15-year period, and create a bar chart to illustrate annual visitors at each memorial. In addition, you will create a
table of sightseeing locations, sort and filter the data, apply conditional formatting, and add a total row to display
average time needed to spend at each memorial. Finally, you will complete a worksheet by adding formulas to
compare estimated major expenses for each city.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
Points
Possible
1
Start Excel. Download and open the file named exploring_ecap_grader_a1.xlsx.
0
2
On the DC worksheet, select the range A4:G4, wrap the text, apply Center alignment, and
apply Blue, Accent 5, Lighter 60% fill color.
6
3
4
5
On the DC worksheet, merge and center the title in the range A1:G1. Apply Accent5 cell style
and bold to the title.
On the DC worksheet, change the width of column A to 34.
On the DC worksheet, select the range C5:F10 and insert Line Sparklines in the range G5:G10.
6
3
4
6
On the DC worksheet, select the range G5:G10, display the high point sparkline marker, and
change the color of the high point markers to Dark Blue.
4
7
On the DC worksheet, select the range G5:G10, apply Same for All Sparklines for both the
vertical axis minimum and maximum values.
2
8
On the DC worksheet, select the ranges A4:A10 and C4:F10 and create a clustered bar chart.
Apply the Color 16 chart color. Apply the gradient fill to the plot area. Do not change the
default gradient options.
5
Note, depending on the version of Office used, the chart color may be named Monochromatic
Palette 12.
9
Ty
Position the top-left corner of the chart in cell A13. Change the chart height to 6 inches and
the chart width to 7 inches.
6
Instructions
10
Change the chart title to Annual Visitors. Apply Blue, Accent 5, Darker 25% font color to the
chart title and category axis labels. Change the value axis display units to Millions.
5
11
Apply data labels to the outside end of the 2015 data series. Apply Number format with 1
decimal place to the data labels.
4
12
Click the Places sheet tab, convert the data to a table, and apply Table Style Medium 6.
5
13
On the Places worksheet, sort the data by City in alphabetical order and then within City, sort
by Sightseeing Locations in alphabetical order.
4
14
On the Places worksheet, add a total row to display the average of the Time Needed column.
Apply Number format with zero decimal places to the total.
4
15
On the Places worksheet, select the values in the Time Needed column and apply conditional
formatting to highlight cells containing values greater than 60 with Green Fill with Dark Green
Text.
4
16
On the Places worksheet, apply a filter to display only fees that are less than or equal to $10.
4
17
On the Cities worksheet, click cell F4 and enter a formula that will subtract the Departure Date
(B1) from the Return Date (B2) and then multiply the result by the Rental Car per Day value
(F3).
5
18
19
20
Ty
Points
Possible
Step
On the Cities worksheet, click cell E13. Depending on the city, you will either take a shuttle
to/from the airport or rent a car. Insert an IF function that compares to see if Yes or No is
located in the Rental Car? Column for a city. If the city contains No, display the value in cell
F2. If the city contains Yes, display the value in the Rental Car Total (F4). Copy the function
from cell E13 and use the Paste Formulas option to copy the function to the range E14:E18
without removing the border in cell E18.
On the Cities worksheet, click cell F13. The lodging is based on a multiplier by City Type.
Some cities are more expensive than others. Insert a VLOOKUP function that looks up the City
Type (B13), compares it to the City/COL range (A7:B10), and returns the COL percentage.
Then multiply the result of the lookup function by the Total Base Lodging (B5) to get the
estimated lodging for the first city. Copy the function from cell F13 and use the Paste
Formulas option to copy the function to the range F14:F18 without removing the border in cell
F18.
On the Cities worksheet, click cell H13 and enter the function that calculates the total costs for
the first city. Copy the function in cell H13 and use the Paste Formulas option to copy the
function to the range H14:H18 without removing the border in cell H18.
7
7
3
Step
Points
Possible
Instructions
21
On the Cities worksheet, select the range E14:H18 and apply Comma Style with zero decimal
places. Select the range E13:H13 and apply Accounting Number format with zero decimal
places.
3
22
On the Cities worksheet, in cell I2, enter a function that will calculate the average total cost
per city. In cell I3, enter a function that will identify the lowest total cost. In cell I4 enter a
function that will return the highest total cost.
6
23
On the Cities worksheet, select Landscape orientation, set a 1-inch top margin, and center the
worksheet data horizontally on the page.
3
24
Ensure that the worksheets are correctly named and placed in the following order in the
workbook: DC, Places, Cities. Save the workbook. Close the workbook and then exit Excel.
Submit the workbook as directed.
0
Total Points
Ty
100

Purchase answer to see full
attachment