Exp22_Excel_AppCapstone_IntroAssessment_Travel
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.
