Meeting Of Dolby, Amanda Hayden, Sinclair Community College

Meeting Ol Dlby Amanda Hayden Sinclair Community College 3210also K

Identify and perform a series of tasks in an Excel workbook related to a travel company's data management. These tasks include naming ranges, inserting lookup functions, calculating costs and commissions, formatting, sorting, filtering, creating tables and charts, and applying various formatting styles and layout settings. The overall goal is to prepare a comprehensive, well-formatted workbook that accurately tracks agent sales, trip details, costs, and other relevant information, culminating in a professional presentation with charts and reports.

Paper For Above instruction

The assignment involves managing and analyzing travel data using Microsoft Excel, with a focus on creating a detailed and organized workbook that captures student tour information, costs, commissions, and sales data. This task emphasizes proficiency in Excel functions, table and cell formatting, sorting and filtering data, and visual data representation through charts and sparklines, competent layout and printing techniques, and strategic data management practices.

Initially, the task begins with opening an existing workbook named exploring_ecap_grader_h1_Trips.xlsx and saving it under a new name to ensure data integrity. The first step involves defining name ranges such as "rates" for the base price and commission ranges, and "tours" for the tour data, which allows for easier and more dynamic referencing throughout the worksheet.

Next, detailed lookup functions including VLOOKUP and other lookup functions are inserted in specific cells (D13 through F22) to fetch descriptive data such as tour descriptions, departure dates, and base costs based on tour codes. These functions facilitate dynamic data retrieval tied to tour identifiers, ensuring the data remains consistent and updating automatically when source data changes.

Subsequently, calculations related to trip costs are performed. For example, in cell G13, a formula computes the total trip cost including taxes and fees by applying a percentage value from a designated input cell, using mixed cell references for flexibility. In cells H13, the PMT function calculates installment payments, considering interest rate and payment duration inputs, with correct relative and absolute references to maintain accuracy upon copying.

The assignment further requires calculation of agent commissions (cell I13) based on trip costs and commission rates retrieved with VLOOKUP. These formulas are extended through the applicable data range, ensuring each trip's commission is accurately computed. Formatting tasks include merging and centering titles, applying currency formats, wrapping text, adjusting column widths and row heights, and freezing panes to improve readability and navigation.

Conditional formatting is applied to highlight above-average total costs, improving data visualization. Auxiliary statistical functions calculate total, average, median, minimum, and maximum trip costs in designated cells. Also, the current date is displayed dynamically, providing temporal context for the report.

The process involves copying the Data worksheet before the Summary sheet, removing conditional formatting from the copy, and converting data ranges into Excel tables with a specific style, facilitating advanced sorting, filtering, and total row functionalities.

Sorting and filtering are then utilized to organize the data by departure date and description, or to focus on specific agents, such as Avery and Ross. Additional totals are calculated using table features, and visual trends are highlighted with sparklines that display six-month sales trends for each agent. Annotation and conditional formatting enhance interpretation.

A clustered column chart visualizes the sales data across months, with specific formatting and title customization. The chart is positioned on a dedicated sheet for clarity and presentation purposes. The final steps encompass setting print layouts with specific margins, orientation, and scaling to one page, ensuring professional report readiness.

Finally, the workbook is saved with correctly named sheets in a specific order, and the entire file is submitted, demonstrating mastery of Excel skills in data analysis, visualization, and presentation within a tourism management context.

References

• Walkenbach, J. (2013). Excel 2013 Bible. John Wiley & Sons.
• Girvin, T. (2012). Excel Data Analysis: Your visual blueprint for analyzing data, charts, and PivotTables. Que Publishing.
• Alexander, M., & Heydt, C. (2015). Microsoft Excel Data Analysis and Business Modeling. Microsoft Press.
• Gaskin, J. (2014). Excel for Dummies. John Wiley & Sons.
• Hutchison, D. (2011). Excel 2010: The Complete Guide. Cengage Learning.
• Few, S. (2012). Show Me the Numbers: Designing Tables and Graphs to Enlighten. Analytics Press.
• Chamberlain, R. (2017). Advanced Excel Reporting and Analysis. Packt Publishing.
• Kasper, S. (2014). Excel Dashboards and Reports for Dummies. John Wiley & Sons.
• Microsoft Support. (2023). Create and manage tables in Excel. Microsoft Corporation.
• McFedries, P. (2016). Excel Formulas and Functions for Dummies. John Wiley & Sons.