Excel Exercise #5: Stock Market Index,Valuation, Earnings+Conditional Formatting

Chris Haroun
A free video tutorial from Chris Haroun
Award Winning MBA Professor, Venture Capitalist and Author.
4.4 instructor rating • 51 courses • 832,464 students

Learn more from the full course

The Complete Financial Analyst Training & Investing Course

Succeed as a Financial Analyst &Investor by Award Winning MBA Prof who worked @Goldman, in Hedge Funds & Venture Capital

22:31:28 of on-demand video • Updated April 2020

  • 22+ hour complete financial analyst course!
  • #1 Best Selling Investing Course on Udemy!
  • How to pick stocks.
  • Become an expert in Excel for financial analysts (no prior Excel knowledge is required).
  • How to manage a portfolio.
  • How an IPO works.
  • How to build financial models.
  • How to get hired and promoted as a financial analyst.
  • How risk management works.
  • How to use technical analysis.
  • How to value companies.
  • Use and create Excel based templates developed by Chris to help you create financial statements from scratch (meaning income statements, balance sheets, cash flow statements and more).
  • Use and create Excel based templates developed by Chris to help you value companies using several different valuation methodologies, including P/E, P/R and Discounted Cash Flow (DCF).
  • Use and create Excel based templates developed by Chris to help you manage a portfolio.
  • How Monetary Policy works.
  • How Fiscal Policy works.
  • How interest rates are changed and why this is crucial to understand for successful financial analysts.
  • How to pitch long and short ideas to portfolio managers.
  • How to find great venture capital investment ideas.
  • How to come up with mutual fund investment ideas (longs - meaning buys) using an easy to understand top down and bottoms up research process.
  • How to come up with hedge fund investment ideas (longs and shorts) using an easy to understand top down and bottoms up research process.
  • Identify crucial catalysts (timed events) in order to know when the optimal time is to buy or short a stock.
  • Understand how investment banks (the 'Sell Side') can help you be more successful in a hedge fund or mutual fund career.
  • Analyze and understand an income statement (even if you have no experience with income statements).
  • Analyze and understand a balance sheet (even if you have no experience with balance sheets).
  • Analyze and understand a cash flow statement (even if you have no experience with cash flow statements).
  • Understand and use modeling best practices so you can create financial models.
  • Know where to get data in order to build a financial model (in depth understanding of identifying and using/navigating the best free websites and sources to build your financial model)!
  • Create a financial model (projecting the future) for an income statement.
  • Other valuation methodologies, including EV/Sales, EV/EBITDA, P/B, EV/FCF, etc.
  • Create a financial model (projecting the future) for a balance sheet.
  • Create a financial model (projecting the future) for a cash flow statement.
  • Understand valuation best practices so you can create target prices based on your financial models.
  • How to use Discounted Cash Flow (DCF) and how to create the Weighted Average Cost of Capital and Terminal values in order to pick target prices.
  • How to use P/E in order to pick target prices.
  • How to use P/R in order to pick target prices.
  • Come up with a target price based on an average of several different valuation methodologies.
  • Learn about 14 different Financial Analyst jobs and how they overlap and work together (including Investment Banking, Venture Capital, Private Equity, Private Wealth Management etc.).
  • Investment Banking: Understand from a high level perspective what an Investment Bank is as well as what the role/job is of an Investment Banking Financial Analyst, including the pros and cons.
  • Venture Capital: Understand from a high level perspective what a Venture Capital firm is as well as what the role/job is of a Venture Capital Financial Analyst, including the pros and cons.
  • Private Equity: Understand from a high level perspective what a Private Equity firm is as well as what the role/job is of a Private Equity Financial Analyst, including the pros and cons.
  • Private Wealth Management: Understand from a high level perspective what a Private Wealth Management firm is as well as what the role/job is of a Private Wealth Management Financial Analyst, including the pros and cons.
  • Sell Side Research Analyst: Understand from a high level perspective what a Sell Side Research Analyst’s firm is as well as what the role/job is of a Sell Side Research Financial Analyst, including the pros and cons.
  • Sales Trader: Understand from a high level perspective what a Sales Trader’s firm is as well as what the role/job is of a Sales Trader Financial Analyst, including the pros and cons.
  • Buy Side Trader: Understand from a high level perspective what a Buy Side Trader’s firm is as well as what the role/job is of a Buy Side Trader Financial Analyst, including the pros and cons.
  • Mutual Fund: Understand from a high level perspective what a Mutual Fund is as well as what the role/job is of a Mutual Fund Financial Analyst, including the pros and cons.
  • Sell Side Trader: Understand from a high level perspective what a Sell Side Trader’s firm is as well as what the role/job is of a Sell Side Trader Financial Analyst, including the pros and cons.
  • Large Non Finance Company: Understand from a high level perspective what a Large Non Finance Company firm is as well as what the role/job is of a Large Non Finance Company Financial Analyst, including the pros and cons.
  • Equity Capital Markets: Understand from a high level perspective what an Equity Capital Markets’ firm is as well as what the role/job is of a Equity Capital Markets Financial Analyst, including the pros and cons.
  • Hedge Fund: Understand from a high level perspective what a Hedge Fund is as well as what the role/job is of a Hedge Fund Financial Analyst, including the pros and cons.
  • Equity Sales: Understand from a high level perspective what an Equity Sales’ firm is as well as what the role/job is of an Equity Sales Financial Analyst, including the pros and cons.
  • Tech / Artificial Intelligence: Understand from a high level perspective what a Tech / Artificial Intelligence’s firm is as well as what the role/job is of a Tech / Artificial Intelligence Financial Analyst, including the pros and cons.
  • Learn what finance role you are most passionate about pursuing.
English Narrator: Welcome to exercise number five. Now, it's important for you to note that we're going to learn by doing, rather than me giving you lectures on 'what is a stock market?', and that sort of thing. We're going to actually analyze data and learn about the stock market by doing these exercises. So please complete all the exercises, if you can, for the next couple sections at least, where we talk about stock market and portfolio management. It's important that you do these exercises so that you understand, in more detail, how to navigate and customize the Portfolio Dashboard. Okay? Which I provided you with, and we'll be talking a lot about. Okay, and what I mean by that is, on the Portfolio Dashboard there's a lot of conditional formatting, which appears down here in question number 12 here, okay. So, on the Portfolio Dashboard, there's a ton of stuff that we can analyze. But I really want you to understand, not only how to make it by following these exercises with me in Excel, but also listening to me explaining the business concepts and logic behind a lot of the information we're going to be analyzing. In this exercise, we're going to talk a lot about the SNP 500, and the SNP 500 is an index of 500 stocks in the United States that represent the broader US markets or US economy, okay. Other countries have their own indexes, in Canada it's the TSX, in Germany it's the DAX, in France it's the CAC 40 or the CAC Quarante. There there's other indexes in other countries as well. So, we'll also talk about earnings here, from a high level perspective, and the average company in the SNP 500, how much they make, what does price earnings mean, we'll analyze the data and look out for spikes and understand why we saw spikes in certain years. It's important to follow through on all these exercises, please. So that, again, when we make or customize our Portfolio Dashboard today, in future lessons or in the future whenever, you'll have a better understanding of stock market concepts, again we're going to learn by doing. So let's talk about what we're going to learn, in this exercise. There are 12 questions in this exercise, it's a pretty long one, and I'm going to introduce you to a lot of stock market concepts in this lesson, and we're going to be analyzing data from the SNP 500, and then what we'll be doing is, we will analyze the valuation of companies in the SNP 500. We'll also make a couple charts, I'll teach you how to format cells, and do what's called Conditional Formatting, which is a lot of fun. It looks actually, like this. I think you'll enjoy it. So please read the question first, and try to come up with the answer yourself. If you need a hint, read this cell here, and if you need a lot more help, you're not understanding the answer, you can actually click over here to watch a video explanation of the answer or what you can do is, after you've finished this entire exercise, you can just skip on to the next lecture in the course, where I'll discuss the answers to all of the questions. There's a lot of stuff that we're going to cover in these Excel exercises over the next couple sections, that will make a lot more sense when you look at the Portfolio Dashboard which you can customize yourself. As a reminder, the Portfolio Dashboard is called Portfolio Dashboard dot X L S, it's in Excel, and it's saved as a resource to download in the first section of this course. Again, a lot of this stuff will make more sense once we finish the next couple of sections in this course. There's just a ton of data you can access in this Portfolio Dashboard, which I'd love for you to customize, thank you.