How to use excel and get this assignment done?

I do not get excel at all. Been getting help and it just does not make sense to me. I have this assignment due and I don’t get it at all. Please Help me.
hauser_exploring_ecap_grader_h1_trips.xlsx

exp_e_capstone_1___european_excursions_25_instructions.docx

Don't use plagiarized sources. Get Your Custom Essay on
How to use excel and get this assignment done?
Just from $13/Page
Order Essay

Unformatted Attachment Preview

Base Price
$1,699
$1,749
$1,799
$1,849
$1,899
Commission
2.5%
3.0%
3.5%
4.0%
4.5%
Package
10
20
30
40
50
60
70
Tour Description
Adventures in Canada
Check Out the Czech Republic!
Croatian Wonders
Live it Up London
Parisian Pleasures
Roaming Rome
Viva España
Departure
6/11/2016
6/4/2016
6/11/2016
6/18/2016
6/25/2016
6/11/2016
6/25/2016
Base Cost
$1,699.00
$1,799.00
$1,899.00
$1,699.00
$1,749.00
$1,849.00
$1,799.00
European Excursions
Agent Sales and Commissions
Input Area
Taxes and Fees
Loan APR
Months Per Year
Total Payments
Agent Name
Avery
Collins
Avery
McDonnough
Ross
Shannon
Collins
Ross
McDonnough
Ross
20%
7%
12
3
Student ID
10001
10002
10003
10004
10005
10006
10007
10008
10009
10010
Total for all trips:
Average trip cost:
Median trip cost:
Tour Code
20
30
10
50
60
70
10
40
50
60
Trip Description
Check Out the Czech Republic!
Croatian Wonders
Adventures in Canada
Parisian Pleasures
Roaming Rome
Viva España
Adventures in Canada
Live it Up London
Parisian Pleasures
Roaming Rome
Departure Date
Summary Statistics
Total for all trips:
Average trip cost:
Median trip cost:
Lowest cost:
Highest cost:
Today’s date:
Base Cost of Total
Trip Cost withStudent
Taxes PaymentAgent
Plan Commission
2016 Total Sales by Agent
January – June
Avery
Collins
Kinsley
McDonnough
Ross
Shannon
January
$ 3,671
$ 1,549
$ 2,996
$ 2,003
$ 4,038
$ 1,172
February
$ 2,973
$
992
$ 6,948
$ 4,190
$ 2,064
$
866
March
$ 4,017
$ 2,267
$ 4,205
$ 3,496
$ 7,015
$ 2,109
$
$
$
$
$
$
April
5,040
3,201
5,523
1,975
6,832
3,385
$
$
$
$
$
$
May
4,963
4,008
6,597
2,936
5,971
4,078
$
$
$
$
$
$
June
5,497
3,798
5,147
3,498
5,497
3,598
Trends
Bonus
Bonus
$
500
Office 2013 – myitlab:grader – Instructions
Excel Project
EXP E Capstone 1 – European Excursions 2.5
Project Description:
You work for a travel company that specializes in arranging travel accommodations for student tours and
vacations in exciting destinations such as Canada, Rome, and the Czech Republic. You created a workbook to
store agent names, student IDs, and tour codes. The workbook also contains a work- sheet to store lookup
tables. You will complete the workbook for your manager’s approval. You will insert formulas and a variety of
functions, convert data to a table, sort and filter the table, and prepare a chart.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1
Start Excel. Open exploring_ecap_grader_h1_Trips.xlsx and save the workbook as
e00a1Trips_LastFirst. Make sure the Lookup Tables sheet is active.
0.000
2
Assign the range name rates to the base price and commission range. Assign the range name
tours to the data for the package, tour description, departure, and base cost.
2.000
3
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.
4.000
4
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.
4.000
5
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.
4.000
6
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.
4.000
7
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.
4.000
8
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.
4.000
9
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.
6.000
Apply Currency number format to the monetary values in columns F, G, H, and I. Hide the
Tour Code column.
4.000
10
Updated: 02/26/2015
1
Current_Instruction.docx
Office 2013 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
11
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.
4.000
12
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.
4.000
13
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.
4.000
14
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.
6.000
15
Copy the Data sheet and place the copied sheet before the Summary sheet. Remove the
conditional formatting rule on the Data (2) sheet.
4.000
16
Convert the data range in the Data (2) sheet to a table. Apply the Table Style Medium 21 style
to the table.
4.000
17
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.
4.000
18
Display a total row. Select a function that calculates totals for all monetary columns.
3.000
19
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.
6.000
20
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.
5.000
21
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.
6.000
22
Apply the Layout 1 chart layout. Type January-June 2016 Sales by Agent for the chart
title. Apply the Style 14 chart style.
6.000
23
Create a footer with the sheet tab code in the center, and the file name code on the right side
of each sheet.
4.000
Updated: 02/26/2015
2
Current_Instruction.docx
Office 2013 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
24
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.
4.000
25
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.
0.000
Total Points
Updated: 02/26/2015
3
100.000
Current_Instruction.docx

Purchase answer to see full
attachment

Order a unique copy of this paper
(550 words)

Approximate price: $22

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency

Order your essay today and save 15% with the discount code ESSAYHELP