Exp22_Excel_AppCapstone_IntroAssessment_Travel  

Exp22 Excel AppCapstone IntroAssessment Travel

Excel Application Capstone Assessment 

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.

Start Excel. Download and open   the file named Exp22_Excel_AppCapstone_IntroAssessment_Travel.xlsx.   Grader has automatically added your last name to the beginning of the   filename.

One worksheet has the original   default name, Sheet1. You want to give the worksheet a meaningful name.
 

  Rename Sheet1 as DC2017.

You want to move the Year   Completed column between the Memorial and 2002 columns.
 

  Select and cut column F. Insert cut cells in cell B1.

Now you want to separate the   worksheet title and source from the dataset with a blank row.
 

  Insert a blank row between rows 2 and 3.

Now you are ready to work with   the DC2020 worksheet. Your first task is to correct spelling errors.
 

  Display the DC2020 worksheet. Check the spelling and correct all misspelled   words.

Continue using the DC2020   worksheet. You will format the headings on row 4.
 

  Select the range A4:G4, wrap the text, apply Center horizontal alignment, and   apply Blue, Accent 5, Lighter 60% fill color.

You now want to format the title   so that it is more prominent.
 

  Merge and center the title in the range A1:G1. Apply Blue, Accent5 cell style   and bold to the title.

The first column is too narrow   for the names of the memorials. You will increase the width to display the   full memorial names.
 

  Change the width of column A to 34.

Sparklines provide visual   representations of data. You will insert sparklines for the dataset.
 

  Select the range C5:F10 and insert Line Sparklines in the range G5:G10.

Now you will format the   sparklines.
 

  Select the range G5:G10, display the high point sparkline marker, and change   the color of the high point markers to Dark Red.

You want to create a chart that   compares the number of visitors for each memorial.
 

  Select the ranges A4:A10 and C4:F10 and create a clustered column chart.   Apply the Monochromatic Palette 12 chart color. Apply the gradient fill to   the chart area. Do not change the default gradient options.

The chart displays over the   data. You will move it below the dataset and adjust its size. In addition,   you will add Alt Text for accessibility compliance.
 

  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 chart shows the number of visitors to   each memorial for four years. (including the period).

The chart needs a meaningful   title. In addition, you want to format the axes and add gridlines.
 

  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.

You want to add data labels for   one data series.
 

  Apply data labels to the outside end position of the 2020 data series. Apply   Number format with 1 decimal place to the data labels.

Now you are ready to focus on   the Places worksheet. First, you will find an abbreviation and replace it   with a city name.
 

  Display the Places worksheet. Find all occurrences of BOS and replace them with Boston.

You decide to convert the   dataset to a table, assign a name to the table, and apply a table style.
 

  Convert the data to a table, assign the table name Tourist_Attractions, and apply Blue, Table Style   Light 13.

The table on the Places   worksheet is large. As you scroll through the table, you want the headings to   remain onscreen.
 

  Freeze the top row.

You decide to rearrange the data   to cluster the memorials by city and then by location.
 

  Sort the table by City in alphabetical order and then within City, sort by   Sightseeing Locations in alphabetical order.

You want to insert a total row   to display the average time recommended to view the memorials.
 

  Add a total row. Change the total value to display the average of the Time   Needed column. Apply Number format with zero decimal places to the total.   Type Average   Time in cell   A41.

You want to apply conditional   formatting to highlight the times over 60 minutes to view the memorial.
 

  Select the values in the Time Needed column (range D2:D40) and apply   conditional formatting to highlight cells containing values greater than 60 with Light Red Fill.

Most memorials have free   admission, but some charge a small fee. You want to set a filter to display   only memorials that charge less than or equal to $10.
 

  Apply a filter to display only fees that are less than or equal to $10.

For the rest of the tasks, you   will work with the Cities worksheet. Your first task is to enter today’s   date.
 

  Display the Cities worksheet. Insert the TODAY function in cell B1.

You want to calculate the   estimated car rental cost.
 

  Click cell F4 and enter a formula that will subtract the Departure Date (B2)   from the Return Date (B3) and then multiply the result by the Rental Car per   Day value (F3).

Depending on the city, you will   either take a shuttle to/from the airport or rent a car. Your next task is to   enter the cost of the shuttle or rental car in the dataset using a logical   function.
 

  Click cell E14. Insert an IF function that compares to see if Yes or No is   located in the Rental Car? column for a city. If the cell contains No, display the shuttle value in   cell F2. If the cell contains Yes, display the value in the Rental Car Total   (cell F4). Copy the function from cell E14. Use the Paste Formulas option to   copy the function to the range E15:E19 without removing the border in cell   E19.

Next, you will enter a formula   to calculate the lodging. The lodging is based on a multiplier by City Type.   Some cities are more expensive than others. You coded cities 1, 2, 3, or 4   and a percentage of cost.
 

  Click cell F14. Insert a VLOOKUP function that looks up the City Type (cell   B14), compares it to the City/COL range (A8:B11), and returns the COL   percentage. Then multiply the result of the lookup function by the Total Base   Lodging (cell B6) to get the estimated lodging for the first city. Copy the   function from cell F14 and use the Paste Formulas option to copy the function   to the range F15:F19 without removing the border in cell F19.

You are now ready to calculate   the total expenses for each city.
 

  Click cell H14 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 H14 and use the Paste Formulas option to copy the   function to the range H15:H19 without removing the border in cell H19.

You are considering taking out a   one-year (12 month) loan to pay for your vacation.
 

  In cell I14, insert the PMT function. Divide the APR (cell I7) by the number   of months in a year (cell I8). The   term is one year (12 months). Use a cell reference. The Total Cost for the   city is the present value. Use relative and mixed (or absolute) references   correctly. Make sure the result is a positive value. Copy the function from   cell I14 to the range I15:I19 and maintain the original border formatting.

You decide to format the values   in the dataset.
 

  Select the range E15:I19 and apply Comma Style with zero decimal places.   Select the range E14:I14 and apply Accounting Number format with zero decimal   places.

The Summary Trip Costs section   is designed to display average, lowest, and highest costs.
 

  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.

The Lookup section (range D7:F8)   is designed to enable you to enter a city in cell D8 and return the   corresponding total cost for that vacation.
 

  In cell F8, insert the XLOOKUP function that looks up the city in cell D8,   compares it to the Destination City range and returns the applicable Total Cost. Use appropriate ranges. Enter Boston in cell D8 to make sure the function returns   the correct value.

On the Cities worksheet, select   Landscape orientation, set a 1″ top margin, and center the worksheet   data horizontally on the page.

Save and close Exp22_Excel_AppCapstone_IntroAssessment_Travel.xlsx.   Exit Excel. Submit the file as directed.