- ABC analysis conducted on sales data
- PowerPivot was used to handle the large set of data by using data model
- PivotTable and PivotChart were used for reporting
- Load the relevant dataset into the excel workbook
- Create relevant PivotTables and PivotCharts/ Charts for ABC analysis
- Summarize findings
- the datase is obtained from kaggle website, source: https://www.kaggle.com/flenderson/sales-analysis
- download the data and load it into the PowerPivot data model
- PowerPivot ribbon > Data Model > Manage > Get external data > From other sources > Excel files
- Create new column "Cost" by mulitplying "PriceReg" and "ItemCount" columns
- This column is used for ABC analysis
- create PivotTable from Data Model
- Insert ribbon> PivotChart > From Data Model
- move 'SKU_Number' to rows, 'File_type' to filter, 'PriceReg', 'Itemcount' and 'Cost'(thrice) to Value
- filter the 'File_type' to Historical
- select the 'Cost2' to show value as running total in... and rename it ('Cumulative sum') to show the cumulative sum
- sort the 'Cost2' from Largest to smallest
- select 'Cost3' to show value as % running total in... and rename it('Percent of Total') to show the percentage of cumulative sum over the grand sum
- apply conditional formatting that will relay which of rows in what category (i.e A(red),B(yellow) or C(green))
- for advance reporting and chart copy the 'Percent of Total' into new worksheet
- Create "Catergory column" adjacent to the 'Percent of Total" column
- Using IF Functions to categorize accordingly: =IF(AND([@[Percent of Total]]>0,[@[Percent of Total]]<0.6),"A",IF(AND([@[Percent of Total]]>=0.6,[@[Percent of Total]]<0.85),"B", IF([@[Percent of Total]]>=0.85,"C",)))
- Create the appropriate charts
Category A items is represented 21% from the overall items and 60% in term of value. Category B items is represented by 30% from the overall items and 25% in value. Category C items is represented 49% from the overall items and only 15% in value.