Individual assignment 3 Follow the instructions to analyze the sales data from a (hypothetical) furniture franchise (i.e., Home Depot) and prepare a dashboard: 1. (15%) Analyze the performance of the sales team Add a new worksheet 'Summary data 1' & create an interactive table showing the total sales amount and total # of orders for each sales representative (with their names), with a slicer to select product category. You can either use a simple pivot table, one made from a data model, or a table that is linked to a pivot table (i.e., with GETPIVOTDATA function). 2. (40%) Prepare for Dashboard - part 1 Make a chart that compares the total sales and profits by quartersover the past three years, with an interactive slicer to select which product category. Next to this chart, display the total # of orders that occurred in the latest month (make sure this number is also linked to the product category slicer). - (10%) Prepare summarized data for the chart using 'vanilla' pivot tables or pivot from a data model. Name the sheet with pivot table(s) 'Summary data 2'; - (10%) Generate the chart from summarized data on the same sheet - 'Summary data 2': choose a chart type that fits, and format the axes and data labels where you feel necessary. - (5%) Add a slicer for product category; - (5%) Prepare another pivot table for 'total # of orders by month' on the same sheet; - (5%) Make sure the category slicer is linked to this new pivot table; - (5%) Copy the chart and slicer to Dashboard; add a textbox that displays the # of orders for the latest month. 3. (30%) Prepare for Dashboard - part 2 A chart that compares the delivery timeframe (i.e., how many days it takes to deliver the products once the order is placed?) by store's region, with an interactive slicer to select which Year's data to display. We can break it down into: - (15%) Calculate the delivery timeframe, then prepare summarized data using a simple pivot table or pivot from the data model. Name the sheet with pivot table(s) 'Summary data 3'; Note: if you're using the data model method, after you insert a new column in 'Orders' to calculate the delivery timeframe, you need to refresh the data model. It's in the 'Data' tab, then the green icon Manage Data Model. Then 'refresh' the data model. You may also need to refresh the pivot tables. - (10%) Generate the chart from summarized data on the same sheet - 'Summary data 3': choose a chart type that fits the question best, and format the axes and data labels where you feel necessary; - (5%) Add a slicer to select by Year and copy the chart and slicer to Dashboard. 4. (15%) Make your dashboard professional! Format the dashboard to make it professional and user-friendly: for example, a dashboard title, give charts proper titles, arrange the charts & slicers (the audience would know which slicer controls which chart), and apply a color theme to all charts and textbox, etc. Bonus questions: (20%) Instead of adding a column to calculate the delivery timeframe (changing the format of the original dataset is something we'd like to avoid), we can add a 'measure' in the data model (just like what we did in the workshop on 'profit margin'). Here is the official explanation of the function to calculate the difference between two dates: https://docs.microsoft.com/en-us/dax/datediff-function-dax. Read the official instructions (and maybe play with the examples to see how it works), add a measure call 'DeliveryTime' to the data model and make a pivot table for Q3 using this new measure. (10%) We introduce the formula XLOOKUP() during the workshop to manually combine two tables. Other formulas can do the same thing: such as using INDEX + MATCH. Do some research, then use INDEX + MATCH to add a column 'Region' in the 'Stores' table. Hints: 1. About raw data: - The main table is 'Orders'; - You can get store's information (such as state) from 'Stores', product information (such as product category) from 'Products', and the sales representative who took the order from 'Sales team'. To find out the region for each store, you can link the table 'Stores' to 'Regions' by StateCodeor State. 2. Get quarters/month from OrderDate: there are multiple ways - One way is to manually add a quarter - year (or month) column based on orderdate in 'Orders': =YEAR(CELL_REF_DATE)&" "&"Q"&ROUNDUP(MONTH(CELL_REF_DATE)/3,0) - Another way is when you drag orderdate into pivot row, it would automatically generate year, quarter & month from orderdate, then you can aggregate by year & quarter/month. Remember to change pivot table's Design - 'Report layout' to 'Tabular form' - so you can sort by year & month both in descending order to get the lastest month. 3. Hint on 'delivery timeframe': date in Excel is actually numbers; therefore, we can use subtraction to calculate the gap between two dates.