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 spreadsheet model help Mr. Madison monitor key information? And, how would his business benefit from being able to retrieve key information on a regular basis? a. b. 2) List three decisions that management can make using the data in the workbook and specify which level of management each decision represents (e.g., Structured, Unstructured and Semi-Structured). a. b. c. 3) Describe how you incorporated the following features in your spreadsheet. a. Formatting: b. Text Labels: c. Named Ranged: d. General Organization: 4) Your spreadsheet contains several VLOOKUP functions. Joe Madison appreciates that information but doesn’t understand how they work and wants to you explain it in plain language. You explain to him that………. a. 5) What is the purpose of offering discounts? Using the data provided in the spreadsheet as the sole source of information, do you think that clients are saving too much money (and possibly causing negative consequences for the travel agency)? a. Discounts: b. Consequences? 6) Spreadsheets always grow over time. Do you have any recommendations for changes and/or updates that would benefit Tropical Travel Agency? a. 2
Purchase answer to see full attachment