Week 5 Excel Exercise Use the Excel dataset 'Product Sales' to do an exploratory data analysis by converting the data into Pivot tables (on a new worksheet called Analysis) and creating

an interactive dashboard with tables and charts (on a new worksheet called Dashboard). DO NOT select recommended pivot tables, you need to learn how to design your own pivot tables! 1) Perform data prep, to prepare your data for analysis - do you need to delete rows, due to missing data? - do you need to add additional columns? For example: • Cost (eg: manufacturing cost) = quantity ordered * unit cost • Sales (eg: retail sales) = quantity ordered * unit price • Profit = Sales - Cost • Month, Year - refer to week 3 data prep PDF for various functions to pull out the Month, Year given a date 2) Perform analysis to answer the following questions and add to an interactive Dashboard. Add appropriate filters/slicers. Use different chart types appropriate to the data. - Sales of various products ('Product') by the year ( e.g., 2013, 2014. ...) - Sales of 'Product Category' (accessories, clothing) by countries by year - Sales of 'Product Category' by customer gender by year - Profits vs Sales by Year and Month (hint: add multiple levels with Year at the top and Month below it) 3) Finally provide some brief, yet concise analysis of your observations. This should be a paragraph or two, put into a new worksheet called Conclusion. Submit your Excel workbook.