10 Cut the chart and paste it in cell A13. Change the chart height to 6" and the chart width to 7". Add Alt Text The bar chart shows the number of visitors to each memorial for the years 2002, 2007, 2012 and 2017 Hint. Right click the chart to enter Alt Text. 11 5 ch 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. Add Primary Minor Vertical gridlines to the chart 12 Hint. The Format Axis task pane contains the option to change the value axis display units Apply data labels to the outside end of the 2017 data series. Apply Number format with 1 decimal place to the data labels. 13 2 Hint: Use Chart Elements to add data labels. On the Places sheet, find all occurrences of BOS and replace them with Boston Hint: The Home tab contains the Find and Replace feature. On the Places sheet tab, convert the data to a table, assign the table name Tourist Attractions, and apply Blue, Table Style Medium 2 Hint: Use the Insert tab to create a table 14 a 15 On the Places sheet, freeze the top row. Hint. The View tab contains an option to freeze the top row On the Places worksheet, sort the data by City in alphabetical order and then within City, sort by Sightseeing Locations in alphabetical order. HintUse the Data tab to sort 16 3 17 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 Hint: The Design tab contains the option to display a total row 18 3 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 Light Red Fill . Hint: The Home tab contains the option to apply conditional formatting 19 On the Places worksheet, apply a filter to display only fees that are less than or equal to $10 3 Created On 01/04/2021 2 Exp19. Excel AppCapstone IntroAssessment Travel 1.2 Grades Instructions Esto 2019 Project Step Instructions Points Possible 20 5 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) 21 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 it Yes or No is located in the Rental Car? Column for a city of 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 funcbon 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 22 6 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 (85) to 15 To trom ine airport or rent a car insert an ir Tuncuon that compares to see if Yes or NOIS 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. 22 6 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 F14F18 without removing the border in cell F18 23 3 On the Cities worksheet, click cell H13 and enter the function that calculates the total costs for the first city, including airfare, shuttle or rental, lodging, and meals. 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. 24 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 I Hint The Home tab contains options to apply number formatting 25 6 On the Cities worksheet, in cell 12, enter a function that will calculate the average total cost per city. In cell 13, enter a function that will identify the lowest total cost. In cell 14 enter a function that will return the highest total cost. Hint Use the AutoSum arrow to select the functions 26 3 27 Group the three worksheets and create a footer with Exploring Series on the left side the sheet tab code in the center, and the file name code on the right side On the Cities worksheet, select Landscape onentation, set a 1" top margin and center the worksheet data horizontally on the page. Hint. The Page Layout tab contains options to format the page layout 5 G D E Ground Transportation Round-trip Shuttle $ Rental Car per Day $ Rental Car Total 50 35 B Departure Date 6/17/2021 2 Return Date 6/22/2021 3 A Lodging Base/Night $ 125 5 Total Base Lodging $ 625 6 City COL 7 1 95% 8 2 110% 9 3 125% 10 4 140% 11 Summary Trip Costs Average Lowest Highest Shuttle or Rental Lodging Total Cost $ Yes 12 Destination City 13 Boston 14 Miami 15 Orlando 16 Philadelphia 17 San Antonio 18 Washington DC 19 20 Rental City Type Car? 3 No 2 1 Yes 3 No 1 Yes 4 No Airfare 367 392 299 479 118 469 Meals 375 330 285 375 285 420 30 A B D E Time Heeded 2 120 13 4 120 30 30 120 60 60 City Hall 120 60 13 14 + Sightseeing Locations City Edgar Allan Poe National Historic Site, Philadelphia White House Wachington DC King's Chapel Burisl Ground BOS 5 Park Street Church BOS Philadelphia 2 Fireman's Hall Philadelphia 3 Victim Veterans Memorial Washington DC 9 Korean War Veterans Memorial Washington DC 10 Fonevil Hall Marketplace BOS 11 Powel House Philadelphia 12 Old City Hall Philadelphia Washington Monument Washington DC Cathedral Bacilica of Saint Peter and P: Philadelphia 15 Martin Luther King, Jr., Memorial Washington DC 16 Elfrethe Alley Philadelphia 17 Fenway Park BOS 18 Christ Church Philadelphia 19 Old State House BOS 2011 Arlington National Cemetery Washington DC 21 Franklin Delano Roosevelt Memorial Washington DC 22 Old South Meeting House BOS 233 USConstitution Museum BOS 24 BOS Macaocre Site BOS 25 Washington Square Pork Philadelphia 26 National World War Memorial Washington DC 27 Jefferson Monument Washington DC 28 Bunker Hill Monument BOS 29 Congress Hall Philadelphia 30 Independence Hall Philadelphia 31 United States Capitol Washington DC 22 Betsy Ross Hourd Philadelphia Library of Congress Washington DC 34 Ford Theatre Washington DC 35 Poul Revate House BOS Todd House Philadelphis 27 Mosenchusetts State House BOS 30 Lincoln Memorial Washington DC Supreme Court Washington DC 40 Old North Church BOS c Fec $0.00 $0.00 $0.00 $0.00 $12.00 10.00 $0.00 $0.00 $0.00 $5.00 $0.00 $0.00 $0.00 $0.00 $5.00 $25.00 $5.00 $10.00 $0.00 $0.00 $6.00 $10.00 $0.00 $0.00 $0.00 $0.00 $0.00 30.00 $0.00 $0.00 $5.00 30.00 $3.00 13.50 $0.00 $0.00 10.00 $0.00 $3.00 180 8888888888888888888888888888888 30 120 41 Chart 7 A B @ D E F G Trend Year Completed 1922 2004 1982 1994 2002 2007 2012 2017 3,551,973 4,214,167 6,191,361 7,956, 117 4,079,606 4,161,685 4,876,842 3,296,596 3,633,793 4,424,407 5,072,589 2,675,205 3,433,663 3,267,124 4,155,947 2,493,089 2,751,725 2,764,459 3,507,402 2,066,932 2,327,254 2,613,131 3,366,571 1997 1943 5 Lincoln Memorial 6 World War II Memorial 7 Vietnam Veterans Memorial 8 Korean War Veterans Memorial 9. Franklin Delano Roosevelt Memorial 10 Thomas Jefferson Memorial 11 12 13 14 15 16 17 Thomas Jefferson Memorial 18 19 Frenkin Delano Roosevelt Memorial 20 21 22 23 24 OK Korean War Veterans Memorial DC Places Cities