Do not change the default gradient options.

Published by

Nov 13, 2022

s

Struggling with a similar assignment? Don’t know where to start? Don’t have time to work on this? Get a high-quality paper written for you from scratch – PLAGIARISM FREE, guaranteed to get you a good grade. To get started, please click on the Submit Your Instructions at the bottom of the page.

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.

n

Need Writing Help? Our writing specialists are here 24/7, every day of the year, ready to support you! Instantly chat with an online tutor below or click here to submit your paper instructions to the writing team.

More than just an assignment.

GET THE GRADES YOU DESERVE | A OR A- GUARANTEED
NO MORE SLEEPLESS NIGHTS DOING RESEARCH
NO MORE LATE POINTS DEDUCTIONS 
GET A QUALITY PAPER SENT TO YOUR EMAIL
GET GOOD GRADES ON YOUR ASSIGNMENTS
Explore Now →

Who is this homework service for?

* If you are having a really hard class and want to get through it, then this is for you.

* If you have a medical emergency or someone close to you has a medical emergency and you don’t think you’ll be able to turn your assignment on time, this is definitely a service you could use.

* You can use us if you are having a tough Professor who won’t give you the grades you deserve.

* If you have a tight work schedule and you are getting points deducted for not submitting assignments on time.

* English might not be your first language and you feel like you are being left behind in class because of it.

* If you have a large project coming up and don’t think you have enough time to get it done well, definitely reach out to us.

TALK TO SUPPORT
{

Super stoked you are checking us out! We would like to help you with your assignment. We just need a few things from you:

* The full assignment instructions as they appear on your school account.

* If a Rubric is present, make sure to attach it.

* Any relevant weekly readings or learning resources.

* Include any special announcements or emails you might have gotten from your Professor regarding your assignment.

* Any templates or additional files required to complete the assignment.

If your assignment is somewhat complex and you need to explain it, please don’t hesitate to reach out to me via live chat. 

 

FAQ

Frequently asked questions

How soon can I get my paper done?

It depends with your deadline. If you need your paper completed in 3 hours, we will deliver it in that time. All you need to do is indicate your deadline in our custom order page here. Alternatively, if you are sending us your instructions via email, please be sure to indicate your deadline.

Will it be completely original? I don't want to be caught in a case of Academic Integrity Violation.

We are as paranoid as you are. Maybe even more! And we understand that the greatest sin you can commit in your academic journey is plagiarizing your academic work. To that end, we have made sure that we check and double-check our papers using high quality plagiarism detection tools such as SafeAssign and Turnitin before submitting the paper to you.

Who is my writer? Is he/she a native English Speaker?

All our writers are native English Speakers. That is not to say that ESL writers are not good, we just prefer hiring native writers because we want the very best people working on your paper. This might mean paying a little bit more for your paper as opposed to when you pay a foreign company whose writers are non-native English Speakers.

What if I need revisions? Will your charge additional for this?

Of course not! If you do happen to require a revision on your paper, our team will handle it for you free of charge. Matter of fact, we won’t rest till you are happy with your paper. So, ask for as many revisions as you need, it’s completely FREE!

Will you give me my money back if I don't like my paper?

We have very few instances where we delivered a paper that a client didn’t fall in love with. But if it so happens that you don’t like your paper for any reason whatsoever, we’ll refund your money back no questions asked.

I have more assignments after this, can you help me with those too?

Of course! And what’s even better is that we can reserve a writer permanently to work on your entire class. This comes in handy for projects which build up on each other and where you need just one writer, one writing style.

I got my order information wrong, can I change that?

Yes you can. Just reach out to our support team via email (support@essaynook.com) or live chat here and they’ll help you change the instructions.

Can I place an order via email instead of going through the order page?

Yes you can. Email Anna at anna@essaynook.com, she’s in charge of our sales team. Alternatively, you can talk to our Live Chat team here and request to speak to Anna.

Trusted by Thousands of Students

Delivering quality assignments since 2007

%d bloggers like this: