This virtual internship was under JP Morgan Chase and Co. on Excel Skills by Forage.

Problem Statement:

The Sales Team wants to know how their unit sales are doing and how can they improve.

Task 1: Excel Keyboard Shortcuts

Practice common keyboard shortcuts to make working in Excel more efficient

Task 2: Conditional Formatting

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.

Task 3: Visual Basic for Applications (VBA) Macros

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

Task 4: Data Visualization in Excel

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.

Task 5: Data-Driven Storytelling

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.