Excel Spreadsheet

Part 1: Data Analysis: Download the data file valleyhospitalitysales2019.xlsx and save the file. On the Sales Data 2019 worksheet, calculate the (1) total sales, (2) total cost, (3) profit, and (4) profit margin for each shipment. Be sure to create a table for your data. Check your calculations carefully to ensure the rest of your analysis is accurate. Part 2: PivotTables & Charts On a separate worksheet labeled “Analysis,” create four PivotTables and Charts to show the following information: a. Total Sales and Gross Profit by Month b. Total Sales by Product c. Total Sales by Customer d. Total Sales by Representative Part 3: Dashboard On a separate worksheet labeled “Dashboard,” create a dashboard showing total sales and gross profit by month, total sales by product (show top five only), and total sales by customer (show top five only). Add a title and formatting to your dashboard and insert slicers so the data can be filtered by product and customer (see example). Finally, insert a timeline so the data can be filtered by month and quarter. Please be creative with your dashboard and insert your own design elements (logo, colors, fonts, layout, etc.). File Submission: Upload a single Excel 2019 (.xlsx) data file with worksheet tabs clearly labeled (Sales Data, PivotTable Analysis, and Dashboard) to the Assignment Dropbox. All formulas and functions should use cell references, currency should be in accounting format – 0 decimals. Be sure to check all slicers to ensure your dashboards can be filtered by product, customer, and month. Please review the grading rubric carefully to see how the assignment will be graded. Note: This is an individual assignment and your submission must be your original work to receive credit. Assignment Checklist ☐ Complete data analysis ☐ (4) PivotTables and Charts correctly formatted (number formats, chart titles, data sorted correctly, labels, etc.) ☐ Interactive Dashboard with (3) charts, (2) slicers, and (1) timeline ☐ Include (2) Slicers (filters) ☐ Connect the Pivot tables to the Slicers ☐ Company logo (optional). To add a logo (go to https://www.logaster.com/ and create a design.