Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Project Description: Tropical Travel Agency has hired a consulting firm to audit some of its existing business processes to ensure that the business is
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. Steps to Perform: Step 1 2 3 4 5 6 7 8 9 10 11 Instructions Start Excel. Open the file named ex02TravelAgency_START. Save the file with the name ex02TravelAgency_LastFirst, replacing LastFirst with your name. On the Agency Clients worksheet, apply the Table Style Light 14 to the Clients named range. 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. Create a named range for the range C14:E18 on the Agency Data and Analysis worksheet with the name Rewardlookup. 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. Apply conditional formatting to the Reward Points YTD column using the Ratings 3 Stars icon set and the default settings. 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. 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. 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. In cell E4, use the SUBTOTAL function to count the records in the Customer named range for records visible in the filtered data. 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. Points Possible 0 2 2 2 8 4 2 2 5 3 3 12 13 14 15 16 17 18 19 20 21 22 ex02TransactionData.txt, into the Client Transactions Import the tab delimited text file, worksheet, starting in cell A2. Apply the Table Style Light 14 to the range A2:K58, removing the connection to the text file. On the Client Transactions worksheet, in cell 13, 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. 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. In cell K3, calculate the cost of the trip after the discount has been applied. Format as Accounting with 2 decimals. 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. 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. 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. 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. 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 right corner of the chart ends in cell P30. 5 6 6 4 5 3.5 5 3.5 4 6 9 Step 23 24 25 Instructions 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. On the Agency Data and Analysis worksheet, starting in cell G2, paste the list of all named ranges created in the workbook. Save and close the workbook and then exit Excel. Submit the workbook as directed. Total Points Points Possible 5 5 0 100
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Certainly The task involves working with an Excel spreadsheet provided by the consulting firm hired by Tropical Travel Agency Heres a breakdown of the steps 1 Open the File Begin by opening the Excel ...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started