EXCEL Project

Overview

In this project, I analyzed real-life data from the New York Stock Exchange provided by Kaggle. The subset of the data that I used for this project contains historical financial data from S&P 500 companies. I went through the process of calculating summary statistics, drawing an inference from the statistics, calculating business metrics, and using models to forecast future growth prospects for the companies. I performed analysis and also created visual tools to communicate the results in informative ways. By the end of this project, I gained the abilities to:

• interpret the measures of central tendency and spread (mean, median, standard deviation, range).

• use a combination of Excel functions (e.g., IF statements, INDEX and MATCH, calculating descriptive statistics with the IF statement, dropdowns, data validation, VLOOKUP).

• analyze and forecast financial business metrics using Excel.

• create visualizations of a business metric and use Excel to create a financial forecast model.

Insight 1

Question: Does the Oil&Gas sector has a higher revenue level than the Health Care sector in year 1?

Insight 2

Question: What is the P&L statement look like for the company ABC?

I Create a dashboard for a Profit and Loss Statement that calculates the Gross Profit, Operating Profit, or EBIT for the company ABC. The statement includes the Gross Profit, Operating Profit, or EBIT values for all the years there is historical data available for that company in the dataset.

Insight 3

Question: What is the financial model look like for the company COO?

I created a financial model for the company COO. The model forecasts out the Gross Profit, Operating Profit, or EBIT for two years using three scenarios (Best case, Weak case, and Base case). The forecasting model is dynamic for the selection of the case (Weak, Base, Strong), and my assumptions for revenue growth, gross margin, and the operating margin will change for each scenario.