JPMorgan Chase Excel Internship

JPMorgan Chase & Co.'s Excel Skills on Forage Virtual Internship

Task 1: Excel Keyboard Shortcuts

I learned key Excel shortcuts, practiced them in Excel, and attended a quiz to test my knowledge.

Task 2: Conditional Formatting

Using the conditional formatting tools, I performed the following tasks:

• Highlighted cells with formula errors in purple with white text.
• Highlighted cells with missing values in yellow.
• Identified accounts that had not been cross-sold with Product 2 by highlighting the appropriate Product 2 cells in orange.
• Highlighted accounts with a 5-year sales CAGR of at least 100% in green and those with a negative CAGR in red with white text.
• Highlighted accounts in the top 10% of unit sales for 2021 in blue.

Task 3: Creating Macros

I created two macros and associated buttons:

• A macro to sort the entire spreadsheet by 5-year CAGR in descending order to identify accounts with the highest overall 5-year sales growth.
• A macro to sort the entire spreadsheet by 2021 unit sales in descending order to see which accounts had the highest overall unit sales in 2021.

Task 4: Building a Dashboard

I created a simple dashboard using the account sales dataset from prior tasks. The steps included:

1. Doing background learning on building a dashboard in Excel.
2. Analyzing the dataset to determine useful charts and graphs, such as unit sales by year, top 10 accounts by unit sales or CAGR, and the effectiveness of different marketing programs.
3. Transforming the data to simplify analysis, such as disaggregating raw data into a new sheet with a row per sales year per account.
4. Adding pivot tables to support various types of charts and analyses.

Task 5: Creating a PowerPoint Presentation

I wrote a short PowerPoint presentation to communicate key insights and data from my analysis and visualization work. The process involved:

1. Learning about data-driven storytelling.
2. Reviewing my analysis and dashboard from Task 4 to identify compelling data, ideas, insights, and examples for decision-makers.
3. Creating a presentation using a provided template, ensuring it was clear, concise, and compelling. The presentation included:
   • A compelling title to grab the audience's attention.
   • A key overarching point with a compelling visual.
   • Key points about account sales analysis, including sales performance, opportunities for improvement, and other relevant findings.
   • A recommendation for action based on the analysis of account sales.