JP Morgan and Chase Excel Virtual Internship

I participated in the open-access JP Morgan & Chase Excel virtual internship with theforage.com, completing a total of 5 tasks.

1. Excel Keyboard Shortcuts
2. Conditional Formatting
3. VBA & Macros
4. Dashboard Creation
5. Data Narratives

Task 1

It included various essential shortcuts commonly used by the company’s employees, and concluded with a quiz to test our knowledge.

Task 2

For the second task, we were given sales account data to perform conditional formatting and complete six exploratory analyses.

• 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.

After applying conditional formatting, the dataset appeared as follows:

Task 2 : Conditional Formatting

Task 3

We were instructed to record two macros and create associated buttons using the same sales data.

• 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.

Creating Macro

Macro for 5-year CAGR

Macro for sort the entire spreadsheet by 2021

Task 4

We were asked to create a dashboard using Power Query, Pivot Tables, and Excel charts with the same sales data. The dataset provided by the company was on a row-per-account basis combined with sales data over five years. I used the unpivot function of Excel’s Power Query to disaggregate the data and transform it into a row-per-sales-year-per-account format for better analysis.

To build the dashboard, I first conducted background research on creating dashboards in Excel. I then analysed 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. I transformed the data to simplify analysis by disaggregating raw data into a new sheet with a row-per-sales-year-per-account format. Additionally, I added pivot tables to support various types of charts and analyses.

Provided Data Set

Insights from JP Morgan & Chase Sales Dashboard

DashBoard View

1. Sales Values Over Time (2017–2021):

• All business types (Medium Business, Online Retailer, Small Business, Wholesale Distributor) show a steady increase in sales values over the years.
• Medium Businesses and Online Retailers have the highest sales growth trajectory.

2. Yearly Sales Value:

• There has been a consistent increase in yearly sales value, peaking in 2021 at 4,091,194 units.
• Significant jumps in sales values occurred between 2017–2018 and 2020–2021.

3. Year-over-Year (YoY) Growth Over Time:

• The YoY growth rate shows variability, with a notable peak in 2019 and a dip in 2020.
• Despite the fluctuations, the overall trend indicates positive growth in sales values.

4. Sales % by Account Type:

• The sales distribution among different account types is relatively balanced.
• Online Retailers (28%) and Medium Businesses (26%) constitute the largest segments, followed by Small Businesses and Wholesale Distributors, each at 23%.

5. Top 5 Decision Makers:

• The top decision-makers contributing to sales are Julie Ross (39,413 units), Janie Robeson (39,331 units), and Henry Inge (36,951 units).
• Their influence suggests they play a crucial role in driving sales for the company.

6. Top 5 Accounts:

• The highest sales account is MB 4 with 39,413 units, followed by OR 3 and WB 3, each contributing significantly to the overall sales.
• These top accounts should be prioritized for maintaining strong business relationships.

7. Total Sales Unit and CAGR:

• The total sales units over the analyzed period are 1,480,848.
• The average 5-year compound annual growth rate (CAGR) is an impressive 51.77%, indicating robust growth and effective sales strategies.

8. Number of Accounts:

• The total number of accounts managed is 60, showcasing a diverse client base that contributes to the overall sales performance.

Task 5

We were asked to create a short PowerPoint presentation to communicate key insights and data from our analysis and visualization work.

The process involved:

• Learning about data-driven storytelling: Understanding how to effectively present data to tell a compelling story.
• Reviewing our analysis and dashboard from Task 4: Identifying compelling data, ideas, insights, and examples for decision-makers.
• Creating the presentation using the 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.

Data-Driven Storytelling Presentation