This project is designed from Matt Brattin's project tutorial https://youtu.be/45_yTM1HfTc
The board is asking to see how volume looked in Q2. I got some data (attached), but didn’t have a chance to pull anything together and was hoping you could take a stab at it.
I think they just want to see Q2 2021 volume by region and wanted to know if everything was looking good. I think this file has what you need. I don’t remember all the region codes – I know NAM ends in 1, EMEA ends in 3 and APAC and LATAM are 2 and 4, but I don’t remember which is which. I do know LATAM has the lowest volume so just go ahead and assign that to which ever comes out lowest.
I used these Alt shortcuts for productive work during this project.
- Move tab right ctrl+Page down
- Move tab left ctrl+page up
- Move to end of dataset ctrl+end
- Move to top of sheet (A1) ctrl+ Home
- Move right across full cell ctrl+ right arrow
- Move down across full cell ctrl+ down arrow
- Select right across full cells ctrl+shift+right arrow
- Select down to end of cells ctrl+shift+end
First of all save file with different name for source security. copy each sheet tab and rename it ending with ( OG ) stands for original. For rename tab click Alt+H+O+R Then hide it.
cleaning starts now
Ctrl+A two times for full cells for wrap/unwrap text alt+H+W auto fit cell with Alt+H+O+I then create table by ctrl+T Rename table with Alt+J+T+A
ctrl+spacebar for whole column selection for selection of blank within range click Alt+H+F+D+S+K then = and cell no which we want reference example =A2 for fill all left blanks click ctrl+enter allcolumn value are now filled accordingly upper cell.
For grouping column value select column by ctrl+space Then text to column Alt+A+E+F
Reformat this ctrl + spacebar for selecting Alt+H+O+I, Alt+H+K, Alt+H+9
Alt+A+M
repeat all above process for both sheets.
now used Xlookup, index match, and vlookup function for Region Id
made table Start, end date and name of quarter example Q1 2021 Used Vlookup using approximate match for range created by us.
for creating pivot table Alt+N+V then created pivot chart started variance analysis and basic forcasting.
Key findings write-up ( Conclusion after Data cleaning, Data wrangling, Data Exploration and Analysis and creating Dashboard. )
Q2 YoY growth slowed from Q1 growth of 4% down to 2.7% or ~13k in volume primarily driven by: (7k) volume or 55% of the decline from loss of two customers in LATAM driving overall growth for region from 9% down im Q1 to flat in Q2 YoY NAM client onboarding in Q2 2020 anniversaried in Q2 2021, slowing perceived growth and amplifying Q1 growth by ~5k units or 1%