1. Introduction to financial modeling
Hello and welcome to Financial Modeling in Excel. My name is Nick Edwards, and I will be your instructor for this course.
2. Expectations
In this course, you will walk through a financial model that you will build hands-on.
If you don't have a financial background, no problem! This course is designed to teach you everything that you need to know.
We recommend you take these prerequisite courses before starting.
3. Welcome to the Streit
You will play the part of a newly hired junior analyst for Streit, a fictional real estate investment trust, also known as a REIT. REITs are businesses that own and operate properties on behalf of investors.
Streit wants to buy an apartment building in Downtown Detroit. You will use financial modeling skills to determine if this property is a good investment.
4. Financial modeling
So, what is financial modeling? A financial model is a mathematical representation of a real-world financial situation used to make predictions or analyze financial performance, and it usually uses a mix of historical data and assumptions to derive a numerical result.
Financial models try to answer a question posed by the analyst, and they can take many different forms.
5. Independent versus dependent
There are many different variables in financial models. Independent variables derive their value from outside of the model. They are the inputs of the analysis. Dependent variables derive their value from the model and rely on independent variables. They are often the observed output of the analysis. Look at this equation. Which variables are dependent, and which are independent? Net income is a dependent variable in this example because it relies on the other independent variables: revenue and expenses.
6. Financial model color coding
Financial models are often complex, with many different inputs and outputs, so keeping them organized and easy to follow while in Excel is important. One of the best practices is to color-code the cells.
7. Financial model color coding
Blue fonts are for hardcoded inputs and assumptions. These will be the independent variables.
8. Financial model color coding
Black fonts are for calculations, outputs, and cell references within the sheet. These are the dependent variables.
9. Financial model color coding
Green is for any cell references to another worksheet within the same Excel workbook.
10. Financial model color coding
And finally, red is for any external links or references. This could be to external workbooks, for example.
11. Income statements (profit and loss)
Finally, you must be familiar with some fundamental financial concepts.
An income statement summarizes a company's profitability over a period of time. It does this by calculating the net income of the company. This is an example of an income statement for a fictional cinema company.
12. Income statements (profit and loss)
Income statements are organized with revenue first, then expenses. Each subtotal has individual line items.
13. Income statements (profit and loss)
Ticket sales and popcorn make up the total revenue.
14. Income statements (profit and loss)
And the building and royalties line items make up total expenses.
Using parentheses for negative numbers is common practice, as is rounding to the nearest whole number. Also, currency symbols are not included.
15. Income statements (profit and loss)
Net income is calculated by subtracting the $150,000 in total revenue from the $50,000 in total expenses to get $100,000 in profit.
Net income is revenue minus expenses. If net income is positive, it is called a profit; if it is negative, it is called a loss. This is why the income statement is also called the profit and loss statement, or P&L for short.
16. Let's practice!
Let's take some time to practice.