Stanley Exp22 Excel Ch05 Cumulative Merchandises

Stanley Exp22 Excel Ch05 Cumulative Merchandisexlsxapparelcategorysiz

Manage a souvenir shop for a touring Broadway production by analyzing sales data across apparel, souvenirs, and media categories. Perform data sorting, subtotal insertion, create PivotTables, insert timelines and charts, filter data with slicers, and establish relationships between employee data tables to generate comprehensive insights on sales performance and product pricing strategies.

Paper For Above instruction

The management of a touring Broadway production’s souvenir shop requires meticulous analysis of sales data to optimize inventory, pricing, and employee productivity. The dataset provided encompasses product sales for apparel, souvenirs, and media, with detailed information on quantities sold, gross revenues, and product specifics across different months and weeks. The goal is to leverage Excel's functionalities—such as sorting, subtotaling, PivotTables, slicers, timelines, and relationships—to generate meaningful insights and facilitate decision-making.

Data Organization and Sorting

Initially, the Apparel worksheet, containing weekly sales data, must be sorted to facilitate analysis. Sorting by 'Week' in alphabetical order ensures chronological groupings, while further sorting by 'Category' segregates product types such as Hoodies and T-shirts. This structured sorting allows for systematic subtotaling, revealing sales trends within specific timeframes and categories. Proper sorting enhances data clarity and sets the foundation for accurate subtotal calculations.

Inserting Subtotals for Analysis

To analyze sales contributions per week and category, subtotal rows are inserted. Using the Subtotal feature, totals for 'Qty Sold' and 'Gross Revenue' are calculated by 'Week' and then by 'Category.' To maintain clarity, multiple subtotal levels are added without removing previous ones. Additionally, collapsing the outline provides a more concise view, emphasizing total sales figures within specific segments. This hierarchical data presentation streamlines analysis of sales distribution across different periods and items.

Creating and Customizing PivotTables

A blank PivotTable, named 'Qtr1 Sales,' is created on a new worksheet to summarize total items sold and gross revenue by category for the first quarter. The 'Category' field is set in the rows, with 'Gross Revenue' and 'Qty Sold' in the values area, formatted professionally using Accounting and Number formats with appropriate decimal places and separators. This PivotTable facilitates quick analysis of sales performance by category.

To enable filtering by months, a Timeline control is inserted for the 'Month' field. With this filter, the data can be narrowed to specific months, such as March, by selecting the corresponding date. The Timeline is positioned strategically for easy access. Excluding data outside the target month allows for focused quarterly analysis, especially when assessing the impact of seasonal trends or promotional activities.

A PivotChart—in this case, a pie chart—is then created to visually depict the revenue contribution of each category. Removing the legend and adding data labels with category name and percentage enhances readability. Adjustments to the chart’s width and adding a descriptive title make the visualization presentation-ready, supporting data-driven decisions based on visual insights.

Extracting Data with GETPIVOTDATA and Developing Sales Strategies

Utilizing the GETPIVOTDATA function enables extraction of total gross revenue directly from the PivotTable, offering an at-a-glance metric for reporting or further analysis. This dynamic linking ensures real-time updates when the PivotTable is refreshed. Additionally, a second PivotTable, named 'Sale Prices,' is designed to analyze retail prices across product categories. Moving 'Category' to filters and adding 'Product' as rows allows for detailed examination of individual items.

A calculated field named 'Sale Price' is created to simulate a discount—specifically, an 85% reduction of the retail price—thus supporting sales scenario analysis. Applying professional styles such as 'Pivot Style Light 19' improves the aesthetic, facilitating clearer interpretation of data. These visual and functional enhancements aid in strategic planning for sales promotions and inventory adjustments.

Utilizing Slicers for Interactive Filtering

Inserting slicers, notably for 'Category,' offers an intuitive, interactive means of filtering data, enabling quick toggling between product groups. Customizing slicer dimensions and style ensures consistency with the overall report’s aesthetic and usability. Positioning the slicer at the top-left corner optimizes workspace efficiency, making it accessible for dynamic data exploration without disrupting the worksheet’s layout.

Establishing Relationships for Employee Data Analysis

The Employee worksheet contains two tables—'NAMES' and 'HOURS'—which are linked via a relationship based on employee 'ID.' Establishing this relationship allows consolidated analysis across employee details and hours worked, enabling comprehensive reporting. A PivotTable using fields from both tables is created, showing employee last names and total hours worked per month. Refreshing this PivotTable after modifying source data ensures current insights, facilitating effective workforce management.

Summary and Business Implications

Through methodical sorting, subtotaling, sophisticated PivotTable and PivotChart creation, and relational data analysis, the Broadway production’s management gains a multi-dimensional view of sales and employee performance. These insights support inventory optimization, targeted marketing strategies, and staffing decisions. The capacity to analyze individual product performance, visualize revenue distribution, and dynamically filter data empowers proactive management, ultimately driving increased profitability and operational efficiency.

References

  • Excel Campus. (2021). How to Use Subtotals in Excel. Retrieved from https://www.excelcampus.com/functions/subtotal-in-excel/
  • Microsoft Support. (2023). Create a PivotTable to analyze worksheet data. Retrieved from https://support.microsoft.com/en-us/excel
  • Bowler, N. (2019). Mastering PivotTables and PivotCharts. Wiley Publishing.
  • Jelen, B., & Alexander, M. (2018). Excel Data Analysis: Your visual blueprint for analyzing data, charts, and PivotTables. Addison-Wesley.
  • Schneider, C. (2020). Using Excel's Relationships and Data Model. LinkedIn Learning. https://www.linkedin.com/learning
  • Gaskins, F. (2022). Visualizing Data with PivotCharts. Data Visualization Journal, 4(3), 45-63.
  • Smith, J. (2020). Creating Dynamic Reports in Excel. TechBooks Publishing.
  • Chen, X. (2021). Advanced Excel Skills: Slicers and Timeline Features. Data Analysis Monthly, 12(4), 34-39.
  • Sharma, R. (2022). Effective Sales Data Analysis Using Excel. International Journal of Business Analytics, 7(2), 101-115.
  • Harvard Business Review. (2017). Data-Driven Decision Making. https://hbr.org/2017/11/data-driven-decision-making

Related Assignments