Solved by verified expert:I need somebody who know’s an expert in excel to do this grader project and 6 question business analysis .See attachments for further direction
campbell_ex02travelagency_start__4_.xlsx
ex02transactiondata__1_.txt
yo16xlgradercase2___travel_agency_12_instructions__1_.docx
business_case_2___tropical_travel_agency_1_.docx
Unformatted Attachment Preview
Customer#
First Name
Last Name
Max Spent
Average Spent
Customer#
CUST01
CUST02
CUST03
CUST04
CUST05
CUST06
CUST07
CUST08
CUST09
CUST010
CUST011
CUST012
CUST013
CUST014
CUST015
CUST016
CUST017
CUST018
CUST019
CUST020
CUST021
CUST022
CUST023
CUST024
CUST025
CUST026
CUST027
CUST028
CUST029
CUST030
CUST031
CUST032
CUST033
CUST034
CUST035
CUST036
CUST037
CUST038
CUST039
Address
City
State
Total Customers
Total Spent
First Name
Jack
Cynthia
John
Jessica
Greg
Jenny
Sara
James
Elaine
Harry
Erin
Jill
Cheryl
Mark
Chris
Coby
Anthony
Erich
Jennifer
Sylvia
Kiara
Kenneth
Margaret
Calvin
Hyacinth
Mariko
Daniel
Sigourney
Joelle
Paul
Ulric
Christopher
Carla
Alika
Sigourney
Rafael
Kameko
Robin
Armand
Last Name
Dixon
Bryant
Peters
Nguyen
Richardson
Branson
Trumbull
Ford
Griffin
Preston
Thomas
Belton
Montaigne
Mason
Serway
Alvarado
Brooks
Green
Baker
Vasquez
Schroeder
Cole
Marks
White
Zamora
Ratliff
Herring
Austin
Hyde
Salinas
Terry
O’Neil
O’Connor
Cameron
Gilbert
Bonner
Dickson
Ward
Knapp
Address
708 Suspe Street
2485 Fusce Street
7188 Sed Street
7268 Amet Street
7168 A Road
PO Box 735
9224 Donec Street
8497 Et Street
1587 Donec Road
549 In Street
6878 Enim Street
8407 In Street
480 Eros Avenue
8569 Sed Road
PO Box 819
4558 Sapien Avenue
8510 Ipsum Street
3751 Donec Avenue
7647 Posuere Avenue
5880 Quam Street
5388 Dictum Avenue
8089 Nec Avenue
4139 Ornare Avenue
2410 Elementum Street
6183 Cursus Road
9426 Risus Street
1751 Tellus Road
4890 Fusce Road
3699 Mauris Street
811 Eget Road
5575 Litora Road
5730 Donec Avenue
5095 Dui Street
2974 Arcu Avenue
7520 Posuere Avenue
7517 Aliquet Street
9578 Proin Street
7920 Enim Street
9861 Pede Road
City
Potomac
Arroyo Grande
Blue Lake
Dallas
Albuquerque
La Mesa
Ann Arbor
Cranbury
San Luis
Herkimer
Fairfax
La Mesa
Sandiston
Brookline
Fanwood
Menomonee Falls
Hammond
La Mesa
La Mesa
Palo Alto
Arlington
Hartford
Warwick
Claremore
Statesboro
Pine Bluff
Corvallis
Auburn Hills
Memphis
Corpus Christi
Plano
Kent
Nanticoke
Myrtle Beach
Compton
Malden
Malden
Santa Clarita
El Monte
State
MD
CA
CA
TX
NM
CA
MI
NJ
AZ
NY
VA
CA
OH
MA
NJ
IA
NY
CA
CA
WA
MI
AK
IN
OH
AK
AK
IN
VA
AL
KS
KY
NH
MS
KY
IL
FL
AK
ME
ME
CUST040
CUST041
CUST042
CUST043
CUST044
CUST045
CUST046
CUST047
CUST048
CUST049
CUST050
CUST051
CUST052
CUST053
CUST054
CUST055
CUST056
CUST057
CUST058
CUST059
CUST060
CUST061
CUST062
CUST063
CUST064
CUST065
CUST066
CUST067
CUST068
CUST069
CUST070
CUST071
CUST072
CUST073
CUST074
CUST075
CUST076
CUST077
CUST078
CUST079
CUST080
CUST081
CUST082
CUST083
CUST084
CUST085
CUST086
Hu
Flavia
Porter
Katell
Jamal
Macaulay
Lee
Shea
Logan
Lionel
Rinah
Sharon
Victor
Stacy
Yolanda
Brett
Steel
Keely
Ifeoma
Octavius
Calvin
Kelsie
Desirae
Dexter
Forrest
Kane
Yardley
Hyatt
Byron
Ray
Amy
Chadwick
Remedios
Mona
Samson
Melodie
Mallory
Glenna
Joseph
Lyle
Ivor
Indigo
Macey
Ashton
India
Honorato
Ila
Tyson
Anthony
Burton
Potter
Powell
Cote
Frederick
Alston
Coffey
Sampson
Dixon
Dennis
Shaw
Klein
Blanchard
Murphy
Gonzalez
Herman
Saunders
Fletcher
Whitley
Olson
Hart
Cash
Gillespie
Dawson
Cross
Emerson
Carson
Morgan
Farley
Hammond
Ellis
Weber
Estes
Hobbs
Goodwin
Valencia
Workman
Harrison
Barr
Mathews
French
Lawson
Bolton
Mitchell
Poole
6874 Penatibus Road
473 Cras Avenue
9260 Ornare Street
6308 Scelerisque Street
8071 Sit Avenue
9835 Consectetuer Street
1585 Quisque Street
1166 Lobortis Street
3651 Sit Avenue
4449 Mus Street
1108 Vulputate Avenue
5999 Turpis Street
9686 Lectus Avenue
2139 Ut Avenue
7489 Nascetur Street
5220 Diam Avenue
7953 A Avenue
9800 Nec Road
2291 Phasellus Road
9206 Nunc Avenue
6564 Sem Avenue
1948 Cras Street
2944 Cras Street
4340 Libero Street
9751 Nunc Avenue
750 Lacinia Avenue
7852 Non Road
7516 Tempus Avenue
3639 Nisi Street
936 Aenean Road
5095 Egestas Street
6778 Luctus Road
7320 Aliquet Avenue
7991 Risus, Avenue
2618 Et Avenue
4905 Aliquam Road
2755 At Avenue
5999 Dictum Street
5378 Ante Street
6971 Neque Street
2799 Sit Road
6376 Dictum Avenue
9373 Ut Street
5773 Enim Avenue
5413 Arcu Avenue
567 N 53rd Street
321 Bolivar Lane
Eufaula
Charlotte
Port Orford
Lubbock
West Palm Beach
Plattsburgh
Lubbock
Ann Arbor
Port Orford
Laughlin
Bay City
Portland
Christiansted
Salem
East Lansing
Lima
Somersworth
Rosemead
Muncie
Port Orford
Frankfort
Eufaula
Fallon
Yigo
Natchez
Escondido
Carrollton
Egg Harbor
Santa Monica
La Habra
Roanoke
Twin Falls
Flint
Worcester
Walla Walla
Midland
Sedalia
Laurel
Auburn
Fernley
Miami Beach
Oneida
Philadelphia
Santa Ana
New Haven
Santa Ana
Albuquerque
DC
AK
ND
IN
AZ
MO
VT
MN
ND
DC
NY
DE
NE
WA
MT
DE
ID
NM
CO
ND
NY
ME
NV
AK
MT
AL
LA
DC
LA
DE
PA
KS
OK
HI
NC
IN
NH
DE
WY
CT
FL
AK
CA
FL
NY
HI
PA
CUST087
.
Imogene
Talley
783 W Point Drive
Pueblo
VT
Zip Code
Zip Code
20854
93420
95525
75217
87114
92010
48104
07815
85350
15253
23020
92010
44301
02445
07023
72490
49211
92010
92010
82071
78934
94076
13968
23889
15474
16295
47222
22070
72595
31448
63310
71250
83385
46640
67920
23698
40510
33192
88699
Phone
Total Spent Agent
Phone
Total Spent Agent
(498) 555-9210 $
6,377 Paige
(349) 555-7159 $
2,207 Paige
(554) 555-0014 $
9,616 Charlie
(597) 555-4822 $
7,321 Jonas
(443) 555-2117 $
873 Charlie
(239) 555-5935 $
6,986 Charlie
(390) 555-8522 $
4,888 Jonas
(662) 555-0681 $
2,852 Charlie
(197) 555-8466 $
1,525 Jonas
(280) 555-6160 $
346 Jonas
(474) 555-4029 $
3,641 Charlie
(985) 555-3171 $
4,155 Paige
(161) 555-0479 $
3,760 Jonas
(229) 555-0415 $
1,963 Paige
(766) 555-0009 $
707 Charlie
(336) 555-0821 $
9,544 Jonas
(679) 555-1247 $
1,946 Charlie
(204) 555-0004 $
5,210 Jonas
(775) 555-8369 $
4,677 Jonas
(489) 555-6997 $
2,513 Paige
(385) 555-9931 $
6,436 Paige
(858) 555-8171 $
2,724 Paige
(516) 555-2672 $
6,613 Charlie
(268) 555-4714 $
2,034 Jonas
(178) 555-7663 $
943 Charlie
(140) 555-8299 $
1,136 Charlie
(485) 555-9710 $
5,936 Jonas
(871) 555-2268 $
4,502 Charlie
(277) 555-6021 $
7,630 Jonas
(797) 555-5806 $
2,607 Jonas
(946) 555-2382 $
5,538 Charlie
(938) 555-0103 $
4,675 Paige
(274) 555-0973 $
9,312 Jonas
(175) 555-3566 $
7,608 Paige
(948) 555-1817 $
7,606 Charlie
(311) 555-8124 $
4,643 Jonas
(106) 555-5457 $
3,084 Charlie
(300) 555-7927 $
6,662 Jonas
(589) 555-5036 $
3,584 Jonas
Reward Points YTD
Reward Points YTD
55257
18878
44758
18395
40865
35890
98654
62020
44758
88614
87083
95633
33026
85028
85212
48220
76808
40330
86184
44758
55830
83274
47440
79806
64209
72688
99909
34923
34334
23616
24758
57128
90876
77005
79736
63640
54911
79116
49653
88904
65819
92537
66550
43129
24835
57352
27771
(734) 555-5684
(628) 555-6314
(438) 555-5537
(226) 555-5581
(128) 555-3895
(864) 555-5493
(852) 555-9194
(483) 555-5508
(618) 555-3241
(884) 555-2389
(969) 555-4853
(761) 555-6438
(739) 555-0847
(810) 555-2599
(243) 555-3356
(941) 555-6917
(441) 555-8108
(819) 555-1818
(322) 555-8032
(295) 555-8865
(495) 555-5210
(804) 555-8230
(694) 555-2081
(604) 555-1745
(422) 555-2446
(118) 555-6416
(736) 555-5484
(303) 555-8937
(649) 555-0606
(607) 555-4907
(719) 555-7341
(963) 555-9254
(387) 555-7702
(880) 555-7226
(528) 555-7716
(487) 555-3706
(340) 555-4381
(104) 555-1837
(269) 555-5869
(681) 555-9759
(158) 555-4559
(726) 555-9894
(468) 555-7239
(425) 555-9714
(106) 555-6536
(322) 555-0942
(949) 555-7788
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
3,953
5,027
2,753
7,206
6,623
4,382
439
8,722
5,638
3,612
1,296
3,135
2,261
2,153
4,127
4,359
9,303
4,374
6,158
1,374
4,549
2,713
9,820
6,888
1,567
3,885
1,738
5,332
6,081
6,709
2,048
3,310
9,292
1,677
349
5,918
4,323
6,006
9,272
2,098
4,469
3,179
2,659
2,412
2,048
1,790
1,596
Paige
Paige
Paige
Charlie
Jonas
Charlie
Charlie
Jonas
Charlie
Jonas
Jonas
Charlie
Paige
Jonas
Paige
Charlie
Jonas
Charlie
Jonas
Jonas
Paige
Paige
Paige
Charlie
Jonas
Charlie
Charlie
Jonas
Charlie
Jonas
Jonas
Charlie
Paige
Jonas
Paige
Charlie
Jonas
Charlie
Jonas
Jonas
Paige
Paige
Paige
Charlie
Jonas
Charlie
Charlie
69794 (871) 555-3787
$
2,325 Jonas
Destination
Aruba
Cancun
Jamaica
Bahamas
St. Lucia
Ft. Lauderdale
Las Vegas
Orlando
Montego Bay
New Orleans
Punta Cana
San Juan
Miami
Nights
6
5
5
5
5
6
5
6
6
5
6
6
3
Cost/person
$
822
$
716
$
748
$
698
$
689
$
454
$
1,335
$
322
$
686
$
374
$
693
$
592
$
443
Flight?
✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
Hotel?
Car?
✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
Rating
Employee
Jonas
Charlie
Jonas
Jonas
Charlie
Paige
Jonas
Paige
Charlie
Jonas
Charlie
Jonas
Jonas
Data Import
Data Analysis
Cost of Trip Discount
Data Analysis
Total After Discount
Travel Agents’ Commission
First Name Date of Hire
Years Employed as of 1/1/2018 Commission Rate Commission Pay
Jonas
6/8/1998
19
10%
Paige
7/5/2013
4
5%
Charlie
2/19/2008
9
7%
Transaction Ranking
Below Average (Less than $4000)
Average (Between $4000 and $5000)
Above Average (Above $5000)
Total Spent
Less than or equal to $2000
Between $2000 and $4000
Between $4000 and $6000
Between $6000 and $8000
Equal to or above $8000
Customer# First Name
# of Transactions
Reward Points
Lookup Values
0
2000
4000
6000
8000
Last Name
Points Earned
200
400
600
800
1000
Address
Total
Discount %
2.5%
5.0%
7.5%
10.0%
15.0%
City
Named Ranges
State
Zip Code
Phone
Total Spent Agent Reward Points YTD
>5000 Charlie
Transaction# First Name Last Name Number of Guests Address Zip Code Trip Date Booked
TRANS-01 Jack Dixon 5 “PO Box 875, 708 Suspendisse Street” 20854 Aruba 2/2/2018
TRANS-02 Cynthia Bryant 3 2485 Fusce Street 93420 Cancun 2/2/2018
TRANS-03 Elaine Griffin 3 1587 Donec Road 85350 Orlando 2/2/2018
TRANS-04 Jill Belton 5 8407 In Street 92010 Montego Bay 2/2/2018
TRANS-05 Cheryl Montaigne 6 480 Eros Avenue 44301 New Orleans 2/3/2018
TRANS-06 Erich Green 5 “PO Box 832, 3751 Donec Avenue” 92010 Punta Cana 2/3/2018
TRANS-07 Jennifer Baker 5 7647 Posuere Avenue 92010 San Juan 2/4/2018
TRANS-08 Christopher O’Neil 6 5730 Donec Avenue 71250 Miami 2/4/2018
TRANS-09 Kameko Dickson 4 9578 Proin Street 40510 Jamaica 2/4/2018
TRANS-10 Armand Knapp 3 9861 Pede Road 88699 Bahamas 2/4/2018
TRANS-11 Hu Tyson 3 6874 Penatibus Road 55257 St. Lucia 2/6/2018
TRANS-12 Porter Burton 4 9260 Ornare Street 44758 Ft. Lauderdale 2/6/2018
TRANS-13 Katell Potter 5 6308 Scelerisque Street 18395 Las Vegas 2/8/2018
TRANS-14 Jamal Powell 4 8071 Sit Avenue 40865 Las Vegas 2/11/2018
TRANS-15 Macaulay Cote 5 9835 Consectetuer Street 35890 Miami 2/11/2018
TRANS-16 Logan Coffey 5 3651 Sit Avenue 44758 Bahamas 2/11/2018
TRANS-17 Lionel Sampson 3 4449 Mus Street 88614 Bahamas 2/11/2018
TRANS-18 Sara Trumbull 5 9224 Donec Street 48104 Bahamas 2/11/2018
TRANS-19 James Ford 4 8497 Et Street 7815 San Juan 2/12/2018
TRANS-20 Cheryl Montaigne 4 480 Eros Avenue 44301 Orlando 2/13/2018
TRANS-21 Sylvia Vasquez 3 5880 Quam Street 82071 Aruba 2/14/2018
TRANS-22 Kenneth Cole 5 8089 Nec Avenue 94076 Orlando 2/14/2018
TRANS-23 Victor Shaw 3 9686 Lectus Avenue 33026 Ft. Lauderdale 2/15/2018
TRANS-24 Stacy Klein 4 2139 Ut Avenue 85028 Orlando 2/15/2018
TRANS-25 Yolanda Blanchard 6 7489 Nascetur Street 85212 Ft. Lauderdale 2/15/2018
TRANS-26 Brett Murphy 6 5220 Diam Avenue 48220 Montego Bay 2/15/2018
TRANS-27 Octavius Fletcher 2 9206 Nunc Avenue 44758 New Orleans 2/16/2018
TRANS-28 Calvin Whitley 4 6564 Sem Avenue 55830 Punta Cana 2/16/2018
TRANS-29 Kelsie Olson 4 1948 Cras Street 83274 San Juan 2/17/2018
TRANS-30 Yardley Cross 3 7852 Non Road 99909 Miami 2/18/2018
TRANS-31 Hyatt Emerson 4 7516 Tempus Avenue 34923 Jamaica 2/18/2018
TRANS-32 Amy Farley 2 “PO Box 915, 5095 Egestas Street” 24758 Bahamas 2/19/2018
TRANS-33 Ray Morgan 6 936 Aenean Road 23616 St. Lucia 2/20/2018
TRANS-34 Amy Farley 6 “PO Box 915, 5095 Egestas Street” 24758 Ft. Lauderdale 2/21/2018
TRANS-35 Melodie Hobbs 4 4905 Aliquam Road 63640 Las Vegas 2/21/2018
TRANS-36 Mallory Goodwin 3 2755 At Avenue 54911 Las Vegas 2/21/2018
TRANS-37 Ivor Barr 6 2799 Sit Road 65819 Miami 2/21/2018
TRANS-38 Indigo Mathews 6 6376 Dictum Avenue 92537 Orlando 2/22/2018
TRANS-39 Macey French 2 9373 Ut Street 66550 Bahamas 2/22/2018
TRANS-40 Victor Shaw 6 9686 Lectus Avenue 33026 Aruba 2/23/2018
TRANS-41 Stacy Klein 5 2139 Ut Avenue 85028 San Juan 2/24/2018
TRANS-42 Yolanda Blanchard 5 7489 Nascetur Street 85212 Punta Cana 2/25/2018
TRANS-43 Dexter Cash 6 4340 Libero Street 79806 St. Lucia 2/26/2018
TRANS-44 Forrest Gillespie 2 “PO Box 851, 9751 Nunc Avenue” 64209 Miami 2/26/2018
TRANS-45 Calvin Whitley 3 6564 Sem Avenue 55830 Orlando 2/27/2018
TRANS-46 Kelsie Olson 2 1948 Cras Street 83274 Miami 2/27/2018
TRANS-47 Yardley Cross 6 7852 Non Road 99909 St. Lucia 2/28/2018
TRANS-48 Hyatt Emerson 2 7516 Tempus Avenue 34923 Ft. Lauderdale 3/1/2018
TRANS-49 Amy Farley 5 “PO Box 915, 5095 Egestas Street” 24758 Bahamas 3/1/2018
TRANS-50 Ashton Lawson 5 5773 Enim Avenue 43129 Miami 3/2/2018
TRANS-51 India Bolton 2 5413 Arcu Avenue 24835 Bahamas 3/2/2018
TRANS-52 Honorato Mitchell 2 567 N 53rd Street 57352 Ft. Lauderdale 3/2/2018
TRANS-53 Ila Poole 2 321 Bolivar Lane 27771 Punta Cana 3/3/2018
TRANS-54 Imogene Talley 2 783 W Point Drive 69794 St. Lucia 3/4/2018
TRANS-55 Melodie Hobbs 2 4905 Aliquam Road 63640 Orlando 3/5/2018
TRANS-56 Mallory Goodwin 4 2755 At Avenue 54911 Punta Cana 3/5/2018
Office 2016 – myitlab:grader – Instructions
Excel Project
YO16_XL_GRADER_CASE2 – Travel Agency 1.2
Project Description:
Tropical Travel Agency has hired a consulting firm to audit some of its existing business processes to ensure that
the business is running efficiently and effectively. You have been asked to analyze existing data and create a
spreadsheet that will be used to ensure group fees are being applied to clients correctly. Additionally, you will
create a form that will make it easier for all travel agents to take reservations and determine which fees should
be assessed based on whether the client is booking a reservation for a group, family, or individual.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
1
Points
Possible
Instructions
Start Excel. Open the file named ex02TravelAgency_START. Save the file with the name
ex02TravelAgency_LastFirst, replacing LastFirst with your name.
0.000
On the Agency Clients worksheet, apply the Table Style Light 14 to the Clients named range.
2
3
4
Note, the style may be named Green, Table Style Light 14, depending on the version of Office
used.
Create named ranges for each of the column headings in the Clients named range.
2.000
2.000
Create a named range for the range C14:E18 on the Agency Data and Analysis worksheet with
the name RewardLookup.
2.000
5
On the Agency Clients worksheet in cell K9, use a VLOOKUP function to determine the reward
points YTD earned based on total spent. Use the RewardLookup named range. The
breakdown of the reward points can be found on the Agency Data and Analysis worksheet.
8.000
6
Apply conditional formatting to the Reward Points YTD column using the Ratings 3 Stars icon
set and the default settings.
4.000
7
In cell B4, use the MAX function to calculate the maximum amount spent by the clients, using
the Total_Spent named range. Format as Accounting with 2 decimal places.
2.000
8
In cell B5, use the AVERAGE function to calculate the average amount spent by the clients,
using the Total_Spent named range. Format as Accounting with 2 decimal places.
2.000
9
Create an Advanced Filter using cells A8:K95 as the List range and A1:K2 as the Criteria range.
Type the appropriate criterion to only display client records with a total spent amount of more
than $6,000.
5.000
10
In cell E4, use the SUBTOTAL function to count the records in the Customer named range for
records visible in the filtered data.
3.000
Updated: 02/28/2017
1
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Excel Project
Points
Possible
Step
Instructions
11
In cell E5, use the SUBTOTAL function to calculate the total spent for the records visible in the
filtered data. Format as Accounting with 0 decimal places.
3.000
12
Import the tab delimited text file, ex02TransactionData.txt, into the Client Transactions
worksheet, starting in cell A2. Apply the Table Style Light 14 to the range A2:K58, removing
the connection to the text file.
5.000
13
On the Client Transactions worksheet, in cell I3, use the VLOOKUP function to retrieve the per
ticket cost for each transaction based on the trip city from the data on the Travel Packages
worksheet multiplied by the number of guests. Format as Accounting with 2 decimals.
6.000
14
In cell J3, use a VLOOKUP function to determine the discount earned based on the cost of trip
and the data on the Agency Data and Analysis worksheet. Format as Percentage with 1
decimal place.
6.000
15
In cell K3, calculate the cost of the trip after the discount has been applied. Format as
Accounting with 2 decimals.
4.000
16
On the Agency Data and Analysis worksheet, in cell E3, use a SUMIF function to calculate the
commission earned for Jonas Lamar, using the data on the Travel Packages worksheet and
the commission rate in cell D3. Autofill the formula down through E5.
5.000
17
In cell D8, use the COUNTIF function to calculate the number of transactions that fell below
average (below $4,000), in the Total_Spent named range.
3.500
18
In cell D9, use the COUNTIFS function to calculate the number of transactions that fell within
the average (between $4,000 and $5,000), in the Total_Spent named range.
5.000
19
In cell D10, use the COUNTIF function to calculate the number of transactions that fell above
average (above $5,000), in the Total_Spent named range.
3.500
20
21
22
Modify the Advanced filter on the Agency Clients worksheet to only show records with a total
spent of greater than $5,000 from agent Charlie. Copy and paste the results of the filter onto
the Agency and Data Analysis worksheet starting in cell A23. Return the advanced filter to its
previous settings so that only records with a total spent of $6,000 or more are displayed.
Create a Clustered Column PivotChart using the data on the Client Transactions worksheet.
Choose the Pivot Analysis worksheet and cell A3 as the location for the PivotTable and
PivotChart that will be created. Use Trip as the LEGEND (SERIES), Date Booked and Last
Name (in that order) as the AXIS (CATEGORIES). Expand the month grouping if necessary.
Use the Total After Discount as the VALUES.
Filter the PivotChart to only show trips to Ft. Lauderdale, Miami, and Orlando. On the
PivotChart, Hide the Field Buttons. Change the Colors to Monochromatic Color Palette 6. Apply
the Style 6 Chart Style. Add a vertical axis title of Revenue After Discount. Increase the
font size to 14 and make it bold. Add a Chart Title, above the chart, with the text Revenue
After Discount Generated by Florida Travel Packages. Increase the font to 16 and make
it bold. Resize the chart so that the upper left corner of the chart begins in F3 and the lower
Updated: 02/28/2017
2
4.000
6.000
9.000
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
right corner of the chart ends in cell P30.
23
On the PivotTable, format the Sum of Total after Discount field to Currency. Apply the
PivotTable Style Pivot Style Medium 14. Have Grand Totals On for Columns only. Have
Subtotals shown at Top of Group. Rename Column Labels in cell B3 to read Travel Packages
and rename Row Labels in A4 to read Date Booked.
5.000
24
On the Agency Data and Analysis worksheet, starting in cell G2, paste the list of all named
ranges created in the workbook.
5.000
25
Save and close the workbook and then exit Excel. Submit the workbook as directed.
0.000
Total Points
Updated: 02/28/2017
3
100.000
Current_Instruction.docx
EBTM251 – (Team #)
Memo
To:
From:
Date:
Re:
1) How will the …
Purchase answer to see full
attachment
