This virtual internship was under JP Morgan Chase and Co. on Excel Skills by Forage.
The Sales Team wants to know how their unit sales are doing and how can they improve.
Practice common keyboard shortcuts to make working in Excel more efficient
Use Excel’s conditional formatting tools to explore and visualize the characteristics of the data in the dataset
- Highlighted any cell with formula errors in purple with white text.
- Highlighted any cell with missing values in yellow.
- Identified accounts that have a 5-year sales CAGR of at least 100% in Green and any account with a negative CAGR in red with white text.
- Identified accounts that have not been cross sold with Product 2 by highlighting the appropriate Product 2 cells in orange.
- Identified accounts in the top 10% of unit sales for 2021 by highlighting the appropriate 2021-unit cells in blue.
Utilized macros in Excel and create two macros using same spreadsheet that was modified prior.
- Created a macro to sort the entire spreadsheet by 5 year CAGR in descending order to see which account have the higher overall 5 year growth
- Create a macro to sort the entire spreadsheet by 2021 unit sales in descending order to see which accounts have the highest overall unit sales in 2021
Created a simple dashboard using the account sales dataset
- Created macros to assist with disaggregating data by multiple years
- Utlized pivot tables and charts to create dashboard.
Created a PowerPoint Presentation to communicated key insights. The recommendations provided are:
- Invest the freed- up sales and marketing resources into our online retailer accounts would drive most sales growth.
- Close the underperforming accounts and launch efforts to identify the highest potential online retailers accounts for increased sales and marketing investment.