Malloy & Sons Windows, Inc. Creating a Dashboard Using Pivot Table in Excel The Case Malloy & Sons Windows, Inc. is a company which specializes in the sale of windows. The company employs five salespeople who sell four different styles of windows to three different categories of customers. Company executives are in the process of reviewing 2017/18 sales results and wish to analyze the data utilizing numerous criteria. The detailed sales data that will be analyzed (i.e. by salesperson, category, and style of product) is provided in the file named “Malloy 2018 Sales Data”. Requirements Using this data, company executives would like to see a set of Key Performance Indicators (KPIs) displayed as a Dashboard which they can use to quickly evaluate how well the organization is doing in achieving its overall strategic goals. The executives would like an interactive tool that will provide them with a quick overview of the sales of the organization. The report should be in chart or graphic form and must be condensed to a single worksheet. The report should be interactive such that users would be able to change variables directly on the face of the chart (e.g. change variables so that they see the trend for only the last 6 months as opposed to the entire year). The following KPIs must be included on the Dashboard: 1) Pie chart depicting the percentage of sales dollars by salesperson 2) Line chart depicting the monthly sales dollars by product style 3) Stacked bar chart depicting sales dollars for each category of customer by salesperson 4) Table containing comparative average sale amount per month by category (include a sparkline chart for each category’s average monthly sale amount per category, and directional icons) 5) Using the slicer to conduct analyses When creating the Dashboard for the executives, it is important to remember that the original data sheet should remain intact; however, additional sheets may be added to the workbook as necessary to support the finished Dashboard product (e.g. it will be necessary to create separate pivot tables for each KPI). Note that the end product must communicate, with clarity, an analysis of the sales of the organization for the reported period, it should not be overly complex, information should be organized in a meaningful manner, and it must be visually appealing. Total scores of this project is 50 points. I will ask five individuals to explain one of this chart. If you participate this presentation and explain well you can earn extra up to 5 points.
Attachments:
malloy-Sales-….xlsx
Dashboard-The….docx
