Start Excel. Open exploring_ecap_grader_h1_Trips.xlsx
and save the workbook as e00a1Trips_LastFirst. Make sure the Lookup
Tables sheet is active.
Assign the range name ratesto the base price and commission
range. Assign the range name toursto the data for the package, tour
description, departure, and base cost.
Click cell D13 on the Data sheet and insert a VLOOKUP
function that looks up the tour code, compares it to the tours table, and
returns the description. Copy the function down through cell D22.
Click cell E13 and insert a lookup function that looks up
the tour code, compares it to the tours table, and returns the departure
date. Copy the function down through cell E22.
Click cell F13 and insert a lookup function that looks up
the tour code, compares it to the tours table, and returns the base cost of
the trip. Copy the function down through cell F22.
In cell G13, insert a formula that adds taxes and fees to
the base cost of the trip (in cell F13) by using the percentage value in the
input area. Use a mixed reference to the cell containing 20% in the input
area above the data. Copy the formula down through cell G22.
In cell H13, insert a PMT function to calculate the
payments for students who want to pay for their trips in three installments.
Use the interest rate and months in the input area above the data. Use
appropriate relative, mixed, and/or absolute cell references in the formula.
Make sure the result is a positive value. Copy the function down through cell
H22.
Click cell I13 and calculate the agent commission using
the base cost of the trip and a VLOOKUP function that returns the commission
rate based on the base cost of the trip using the rates lookup table. The
function should then calculate the monetary value of the commission. Copy the
function down through cell I22.
Merge and center the main title on the first row over all
data columns on the Data sheet. Apply bold and 18 pt font size. Merge and
center the subtitle on the second row over all data columns.
Apply Currency number format to the monetary values in
columns F, G, H, and I. Hide the Tour Code column.
Wrap text in the range F12:I12. Set the column widths for
these columns to 11, if necessary. Adjust the row height, if necessary.
Freeze the panes so that the row of column labels does not scroll offscreen.
Apply the Light Red Fill with Dark Red Text conditional
formatting to values in the Total Cost with Taxes column when the values are
above average.
Insert a function to calculate the total for all trips
(including taxes) in cell G5, the average trip cost in cell G6, and the
median trip cost in cell G7.
Insert a function to calculate the lowest trip cost in
cell G8 and the highest trip cost in cell G9. Click cell G10 and enter a
function to display today’s date.
Copy the Data sheet and place the copied sheet before the
Summary sheet. Remove the conditional formatting rule on the Data (2) sheet.
Convert the data range in the Data (2) sheet to a table.
Apply the Table Style Medium 21 style to the table.
Sort the table by departure date from oldest to newest and
then alphabetically by trip description. Apply a filter to display trips
arranged by agents Avery and Ross only.
Display a total row. Select a function that calculates
totals for all monetary columns.
Create Line sparklines in column H in the Summary sheet to
display six-month trends for each agent. Show the high point in each
sparkline. Apply the SparkLine Style Accent 1, Darker 50% style. If needed,
apply the Blue, Accent 1 high point marker color.
Insert an IF function in column I that displays a $500
bonus if an agentâs average sales are greater than the
average of all sales for the six months. Use two nested AVERAGE functions in
the logical_test argument of the IF function to make the comparison.
Create a clustered column chart of the agents and their
six-month sales, using the chart type that displays the months on the
category axis. Move the chart to a new chart sheet named Sales Chart.
Apply the Layout 1 chart layout. Type January-June 2016 Sales by Agentfor
the chart title. Apply the Style 14 chart style.
Create a footer with the sheet tab code in the center, and
the file name code on the right side of each sheet.
Apply 0.2″left and right margins and scale
to one page (height and width) for the Data and Data (2) sheets. Select
Landscape orientation for the Data (2) sheet.
Save the workbook. Ensure that the worksheets are named
correctly and in the following order: Lookup Tables, Data, Data (2), Sales
Chart, and then Summary. Close the workbook and exit Excel. Submit the
workbook as directed.
Our Service Charter
-
Excellent Quality / 100% Plagiarism-Free
We employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers. -
Free Revisions
We keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed. -
Confidentiality / 100% No Disclosure
We make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service. -
Money Back Guarantee
If the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit. -
24/7 Customer Support
We have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.