The JPMorgan Chase & Co. Excel Skills Virtual Internship, hosted by Forage, is an immersive program designed to equip aspiring finance professionals with essential Excel skills. The Excel Skills Virtual Internship empowers participants to stand out in the competitive finance landscape, setting them on a path to success.
J.P. Morgan is a global leader in financial services, that offers services to the world’s most important corporations. The Data Analytics team is looking to analyze the account performance metrics and sales trend for the 5years period between 2017-2021.
Practiced common keyboard shortcuts to make working in Excel more efficient.
Demonstrated key conditional formatting techniques in Microsoft Excel. Explored the data, for
- Missing values
- Repeated / duplicate values
- Errors in the data or in calculations that drive values in the data
- Trends and patterns
- Values that are negative or positive
- Values that are larger or smaller than a cutoff value
- Values that occur in a certain date range
Applied Conditional formatting techniques in Excel to make data exploration visual. Formatted data by using colors, fonts, and highlighting methods to make decision-making
Familiarized with VBA, Excel’s built-in programming language. Created the following two macros using the data modified with conditional formatting
- A macro to sort the entire spreadsheet by 5 YR CAGR in descending order to see which accounts have 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 have the highest overall unit sales in 2021
Built a data visualization dashboard using charts in Excel. Created a dashboard using the account sales dataset worked with in prior tasks. Some of the focused business contexts when presenting findings were:
- Unit sales by year?
- Top 5 accounts by unit sales or CAGR?
- Effectiveness of different marketing programs by the number of sales driven?
- Sales by account type?
Communicated analysis and insights using PowerPoint to tell a data-driven story. PowerPoint presentation to communicate key insights and data from the analysis and visualization work in the prior task. Creating a data-driven narrative allows you to directly link the issue you want to address, the insights useful to address the issue, and the action or decision you want the reader or listener to take. Communicated insights into which accounts are and are not performing well, how sales are growing year over year, which account types are selling more units than others, and other kinds of findings that you could communicate. Communicated key points and ideas about your account sales analysis, including sales performance (or lack thereof), opportunities for improvement, or operational focus. Made recommendations for an action that the audience should take as a result of the analysis of account sales.