Business Analyst Project: Build an interactive Excel Dashboard For Coca-Cola.
Data : Row=3889, Column=12
Objectives
.How Coca-Cola performed in 2021
.What are some of the customers trends so firstly
.Build a timeline and slicer
. Which state has more customers.
- Transform data in table by selecting all and CTR+T
- Insert a pivot table, drug down unit sold, total sales, operating profit and operating margin into value.
3. Transform operating margin: go to field setting + Average + ok.
4. Take these total on pivot table and link to Dashboard.
5.Monthly Breakdown: We create another pivot table
6. Drug Invoice into Row and Total sale in Values
7. Customize the Pivot table: CTR+1
8. Go to pivot table analyze + pivot charts+ go to field Button + hide all. All legend will be hide on the chart.
9. To make bar chart bigger, right click on it and go to format data series and change Gap Width to 40%
10. copy (ctr+c) bar chart and past (ctr+v) on Dashboard. See image below.
11. To make the chart bar dynamic, Click on bar chart + pivot table analyze and select timeline invoice date.
12. The best sale region (state), insert pivotable and drug state into row and units sold into values.
Note: Since we can’t insert map into pivot table, so copy (ctr+v) the pivot table and past (Ctr + v) anywhere on the sheet. From here insert + map.
13. Now copy and past on dashboard.
14. Click on the bar chart+ pivot table analyze and insert slicer for Region, Beverage, and Retailler.
15. To make the final dashboard dynamic, select each slicer + click on slicer+ click report connection and select all items. See Final image below.
