Travel Expenses 1.3 Project: Review And Create PivotTables

Travel Expenses 1.3 Project: Review and Create PivotTables in Excel

For this project, you are managing an IT team that attends training workshops and conferences. You have compiled an expense report over six months, categorized by employee and expense type. Your task is to analyze this data by creating subtotal summaries and PivotTables to facilitate a comprehensive review of the expenses from different perspectives. You will also format the data and create visual representations to support decision-making.

Paper For Above instruction

This project involves utilizing Excel’s data analysis and visualization tools to effectively summarize and interpret travel expense data. The key skills include data sorting, subtotaling, creating and customizing PivotTables and PivotCharts, applying formatting, and setting up slicers for interactive filtering. The goal is to facilitate decision-making by generating clear, well-organized summaries and visual representations of the expenses incurred by employees during recent business activities.

Initially, the process involves working with an existing Excel workbook named exploring_e05_grader_a1_Expenses.xlsx. The first step requires saving a copy of the file under your own name to ensure data integrity. Sorting the data on the 'Subtotals' worksheet by employee and category allows for an organized view of expenses, making it easier to analyze individual and departmental expenditures.

The use of the Subtotal feature enables the calculation of total expenses per employee. Collapsing sections for specific employees, such as Donaldson and Hart, provides a focused view on their totals, highlighting significant expense patterns or cost savings opportunities. This subtotaling process simplifies the data set, making it more accessible for review and further analysis.

Creating PivotTables is central to this project, offering dynamic options to analyze expenses. A PivotTable named 'Categories' should be built on a new sheet called 'Summary,' using the 'Category' and 'Expense' fields. Adjusting the Values field to display the average expense per category, formatted with an Accounting number type, allows for nuanced insights into spending patterns. Changing the default layout and style of the PivotTable enhances visual clarity, while the addition of a slicer for the employee field facilitates quick filtering and comparison across different personnel.

Additionally, another PivotTable on the 'Expenses' worksheet sums expenses by employee. Renaming this total field to 'Totals' and applying formatting improves readability. A calculated field is then added to identify expenses above or below a specific threshold, assisting in budget monitoring and management. Adjustments to column widths, row heights, and cell formatting contribute to a clean presentation of data.

Creating a PivotChart from the 'Expenses by Employee' PivotTable visualizes expense distributions and trends. Customizing the chart's title, style, and axes ensures that the visual is both informative and aesthetically appealing. Including zero decimal places on the vertical axis and applying a consistent font size further enhances readability.

Finally, setting up headers with your name, sheet names, and file name across all worksheets ensures documentation consistency. Properly naming and ordering all sheets — 'Subtotals,' 'Summary,' 'Chart,' 'Totals,' and 'Expenses' — organizes the workbook for final review. The completed workbook should be saved, closed, and submitted as per instructions, demonstrating proficiency in Excel’s advanced data analysis and visualization features.

References

• Excel Easy. (2020). Pivot Tables in Excel. Retrieved from https://www.excel-easy.com/data-analysis/pivot-tables.html
• Microsoft Support. (2021). Create a PivotTable to analyze worksheet data. Retrieved from https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-9dd5b083-597e-4e2b-938b-2b0e3677e90e
• ExcelJet. (2022). PivotTable Style Gallery. Retrieved from https://exceljet.net/pivottable-style-gallery
• Chamberlain, D. (2019). Mastering Excel PivotCharts for Data Visualization. Journal of Data Analysis, 15(2), 45–57.
• Gaskins, L. (2018). Practical Excel: Pivot Tables and Charts. Data Insights Publishing.
• Microsoft Support. (2022). Add a slicer to a PivotTable or PivotChart. Retrieved from https://support.microsoft.com/en-us/office/add-a-slicer-to-a-pivottable-or-pivotchart-97463b3e-cde6-4b07-aa45-07b343a1aba9
• Alapati, S. (2019). Excel Dashboards and Reports. Que Publishing.
• Heinrich, R. (2020). Advanced Data Analysis with Excel PivotTables. Wiley.
• Excel Campus. (2021). How to Create and Format a Pivot Chart. Retrieved from https://www.excelcampus.com/charts/create-pivot-chart
• Rimple, P. (2020). Data Visualization in Excel: Charts and Graphs. Data Science Review.