Towson University
EBTM 251
Project Description: Towson Travel Agency has hired a consulting firm to finalize an Excel workbook that will be used to quickly summarize quarterly sales and commissions. Steps to Perform: Step Instructions Points Possible 1 Start Excel. Open the file named TU03 - EBTM251 Chapter 2 - Towson Travel Agency.xlsx. MyITLab has automatically added your l
...[Show More]
Project Description: Towson Travel Agency has hired a consulting firm to finalize an Excel workbook that will be used to quickly summarize quarterly sales and commissions. Steps to Perform: Step Instructions Points Possible 1 Start Excel. Open the file named TU03 - EBTM251 Chapter 2 - Towson Travel Agency.xlsx. MyITLab has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files for future grading. 0 2 On the "Client Sales Report" worksheet, create range names for the columns in rows 14:63 using the column headers (row 13) of the data set. Hint: Remember, there is a way to perform this task in one step. 2 3 Convert the non-functioning data set (B13:K63) into a functioning table. Apply Table Style "Blue, Medium Table Style 2". Accept all defaults and change the font color in row 13 to "White, Background 1". 2 4 Use the CONCAT function in cell D14 to display the customer’s last and first name. When completed D14 should read: “Fontana, Anthony”. The remaining names in the column will fill automatically. 3 5 On the "Agency Data" tab, create the name range “TravelCost” for cells B4:H13 and “RewardPointSchedule” in cells L21:M25. 2 6 Return to the "Client Sales Report" tab. The key components on the Client Sales Report table are "Trip Revenue" (column I) and "Reward Points" (Column J). In cell I14, use a VLOOKUP formula to calculate the total trip revenue for Anthony Fontana’s trip to St. Thomas. Format the results as accounting with zero decimals. Hint: The cost of the Travel packages shown on the Agency Data tab are per couple. 5 7 The travel agency provides Reward Points based on revenue associated with each trip. In cell J14 use a VLOOKUP formula to calculate the total Reward Points earned by each "Client". Reward Points for the other "Clients" will be calculated automatically.
[Show Less]