{"id":39458,"date":"2021-12-11T13:27:47","date_gmt":"2021-12-11T13:27:47","guid":{"rendered":"https:\/\/academicwritersbay.com\/answers\/exp19_excel_appcapstone_introassessment_travel-computer-science-homework-help\/"},"modified":"2021-12-11T13:27:47","modified_gmt":"2021-12-11T13:27:47","slug":"exp19_excel_appcapstone_introassessment_travel-computer-science-homework-help","status":"publish","type":"post","link":"https:\/\/academicwritersbay.com\/answers\/exp19_excel_appcapstone_introassessment_travel-computer-science-homework-help\/","title":{"rendered":"Exp19_excel_appcapstone_introassessment_travel | Computer Science homework help"},"content":{"rendered":"
\n

#Exp19_Excel_AppCapstone_IntroAssessment_Travel<\/p>\n

Solution File + Step by Step Video Link is Available in Answer<\/p>\n

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.<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0<\/p>\n

Start \u00a0\u00a0Excel. Download and open the file named Exp19_Excel_AppCapstone_IntroAssessment_ \u00a0\u00a0Travel.xlsx<\/em>. \u00a0\u00a0Grader has automatically added your last name to the beginning of the \u00a0\u00a0filename.<\/p>\n

\u00a0<\/p>\n

On the DC sheet, check the \u00a0\u00a0spelling and correct all misspelled words.<\/p>\n

\u00a0<\/p>\n

#On the DC worksheet, select the \u00a0\u00a0range A4:G4, wrap the text, apply Center alignment, and apply Blue, Accent 5, \u00a0\u00a0Lighter 60% fill color.<\/p>\n

\u00a0<\/p>\n

On the DC worksheet, merge and center the title in the range A1:G1. Apply Blue, Accent5 cell style and bold to the title.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

On the DC worksheet, change the \u00a0\u00a0width of column A to 34<\/strong>.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

On the DC worksheet, select the range C5:F10 and insert Line Spark lines in the range G5:G10.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

On the DC worksheet, select the \u00a0\u00a0range G5:G10, display the high point sparkline marker, and change the color \u00a0\u00a0of the high point markers to Dark Red.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

On the DC worksheet, select the \u00a0\u00a0range G5:G10, apply Same for All Sparklines for both the vertical axis \u00a0\u00a0minimum and maximum values.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

On the DC worksheet, select the \u00a0\u00a0ranges A4:A10 and C4:F10 and create a clustered bar chart. Apply the \u00a0\u00a0Monochromatic Palette 12 chart color. Apply the gradient fill to the chart \u00a0\u00a0area. Do not change the default gradient options.<\/p>\n

\u00a0<\/p>\n

#Cut the chart and paste it in \u00a0\u00a0cell A13. Change the chart height to 6\u2033 and the chart width to 7\u2033. \u00a0\u00a0Add Alt Text The bar chart shows the number of visitors to each \u00a0\u00a0memorial for the years 2002, 2007, 2012, and 2017.<\/strong><\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

Change the chart title to Annual \u00a0\u00a0Visitors<\/strong>. Apply \u00a0\u00a0Blue, Accent 5, Darker 25% font color to the chart title and category axis \u00a0\u00a0labels. Change the value axis display units to Millions. Add Primary Minor \u00a0\u00a0Vertical gridlines to the chart.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

Apply data labels to the outside \u00a0\u00a0end of the 2017 data series. Apply Number format with 1<\/strong> decimal place to the data \u00a0\u00a0labels.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

On the Places sheet, find all \u00a0\u00a0occurrences of BOS <\/strong>and replace them with Boston<\/strong>.<\/p>\n

\u00a0<\/p>\n

On the Places sheet tab, convert \u00a0\u00a0the data to a table, assign the table name Tourist_Attractions<\/strong>, and apply Blue, Table Style \u00a0\u00a0Medium 2.<\/p>\n

\u00a0<\/p>\n

On the Places sheet, freeze the \u00a0\u00a0top row.<\/p>\n

\u00a0<\/p>\n

#On the Places worksheet, sort \u00a0\u00a0the data by City in alphabetical order and then within City, sort by \u00a0\u00a0Sightseeing Locations in alphabetical order.<\/p>\n

\u00a0<\/p>\n

On the Places worksheet, add a \u00a0\u00a0total row to display the average of the Time Needed column. Apply Number format \u00a0\u00a0with zero decimal places to the total.<\/p>\n

\u00a0<\/p>\n

#On the Places worksheet, select \u00a0\u00a0the values in the Time Needed column and apply conditional formatting to \u00a0\u00a0highlight cells containing values greater than 60<\/strong> with Light Red Fill.<\/p>\n

\u00a0<\/p>\n

On the Places worksheet, apply a \u00a0\u00a0filter to display only fees that are less than or equal to $10<\/strong>.<\/p>\n

\u00a0<\/p>\n

On the Cities worksheet, click \u00a0\u00a0cell F4 and enter a formula that will subtract the Departure Date (B1) from \u00a0\u00a0the Return Date (B2) and then multiply the result by the Rental Car per Day \u00a0\u00a0value (F3).<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

#On the Cities worksheet, click \u00a0\u00a0cell E13. Depending on the city, you will either take a shuttle to\/from the \u00a0\u00a0airport or rent a car. Insert an IF function that compares to see if Yes or \u00a0\u00a0No is located in the Rental Car? Column for a city. If the city contains No, \u00a0\u00a0display the value in cell F2. If the city contains Yes, display the value in \u00a0\u00a0the Rental Car Total (F4). Copy the function from cell E13 and use the Paste \u00a0\u00a0Formulas option to copy the function to the range E14:E18 without removing \u00a0\u00a0the border in cell E18.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

#On the Cities worksheet, click \u00a0\u00a0cell F13. The lodging is based on a multiplier by City Type. Some cities are \u00a0\u00a0more expensive than others. Insert a VLOOKUP function that looks up the City \u00a0\u00a0Type (B13), compares it to the City\/COL range (A7:B10), and returns the COL \u00a0\u00a0percentage. Then multiply the result of the lookup function by the Total Base \u00a0\u00a0Lodging (B5) to get the estimated lodging for the first city. Copy the \u00a0\u00a0function from cell F13 and use the Paste Formulas option to copy the function \u00a0\u00a0to the range F14:F18 without removing the border in cell F18.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

#On the Cities worksheet, click \u00a0\u00a0cell H13 and enter the function that calculates the total costs for the first \u00a0\u00a0city, including airfare, shuttle or rental, lodging, and meals. Copy the \u00a0\u00a0function in cell H13 and use the Paste Formulas option to copy the function \u00a0\u00a0to the range H14:H18 without removing the border in cell H18.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

#On the Cities worksheet, select \u00a0\u00a0the range E14:H18 and apply Comma Style with zero decimal places. Select the \u00a0\u00a0range E13:H13 and apply Accounting Number format with zero decimal places.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

On the Cities worksheet, in cell \u00a0\u00a0I2, enter a function that will calculate the average total cost per city. In \u00a0\u00a0cell I3, enter a function that will identify the lowest total cost. In cell \u00a0\u00a0I4 enter a function that will return the highest total cost.<\/p>\n

\u00a0<\/p>\n

\u00a0<\/p>\n

Group the three worksheets and \u00a0\u00a0create a footer with Exploring Series<\/strong> on the left side, the sheet tab code in the \u00a0\u00a0center, and the file name code on the right side.<\/p>\n

\u00a0<\/p>\n

On the Cities worksheet, select \u00a0\u00a0Landscape orientation, set a 1\u2033 top margin, and center the worksheet \u00a0\u00a0data horizontally on the page.<\/p>\n

\u00a0<\/p>\n

Save and close Exp19_Excel_AppCapstone_IntroAssessment_Travel.xlsx.<\/em> \u00a0\u00a0Exit Excel. Submit the file as directed.<\/p>\n<\/p>\n<\/div>\n

\u00a0 <\/p>\n

\u201cCUSTOM PAPER\u201d<\/h5>\n","protected":false},"excerpt":{"rendered":"

#Exp19_Excel_AppCapstone_IntroAssessment_Travel Solution File + Step by Step Video Link is Available in Answer 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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-39458","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/posts\/39458","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/comments?post=39458"}],"version-history":[{"count":0,"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/posts\/39458\/revisions"}],"wp:attachment":[{"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/media?parent=39458"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/categories?post=39458"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academicwritersbay.com\/answers\/wp-json\/wp\/v2\/tags?post=39458"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}