How to Build a Basic Financial Model in Excel - YouTube

hi in this video we're gonna learn how to build  a basic financial model in Microsoft Excel 2016  

we're gonna focus on the P&L or income statement  in this video so the first thing you want to do  

when you're starting to build a financial model  is having a part of the model of where you store  

all of your assumptions and let's say that we  have 2016 2017 2018 and maybe we're selling  

let's say chairs so chairs so you're gonna have  revenue assumptions so let's first say revenue  

cost of goods sold and then operating expenses  so we're gonna have assumptions for each one of  

these things so to keep it very basic let's say  we have units and price for our revenue and so  

we're selling we're a chair business let's say we  sell a hundred units and then 180 and then 350 one  

thing that you always want to do no matter what  when you're modeling in Excel is to format your  

assumptions as a nice dark blue so if we go to  the color section I like to choose my own custom  

color and under more colors something like this  a nice bright blue and the reason we do blue is  

because when someone else looks at a model if  your assumptions are blue then it's very very  

easy to tweak the model and to test the model's  assumptions and say ok what if our units are  

500 in this year or 100 and it makes it easier  to understand and faster to navigate and when  

you don't have assumptions you want to keep them  black so here's our revenue model price let's say  

we have you know two hundred and fifty dollars  a chair here I will apply the accounting number  

format and then just decrease the decimal so you  can see I'm not using the mouse I think mouse  

really really slows you down in Microsoft Excel so  I try to avoid it next we have cost of goods sold  

we already know how many units so our let's say  unit cost or maybe manufacturing cost let's say  

it's a hundred and fifty dollars per chair and in  the interest of saving time I'll just copy this to  

copy the format and then type 150 so I move that  across and the way that I did that was I pressed  

the ctrl C then I held down shift and hit the  right arrow twice and then I trust press control  

V so I was copy/paste I'll delete this row and  this will allow us to build the income statement  

down to the gross margin so that's revenue minus  costs next we'll have our operating expenses  

let's say we have payroll and/or labor maybe we're  outsourcing this and the labor costs us 10,000 a  

year a 10,000 year and let's make sure to actually  apply the same type of format 10,000 a year maybe  

this is 15,000 a year and 20,000 a year okay and  then maybe you know marketing costs maybe this  

is 5,000 a year 6,000 a year 7,000 a year okay so  once you've built your assumptions you're ready to  

actually start modeling and to start modeling what  I'm gonna do here is I will go to view and freeze  

panes so I'll move I'll move the whatever header  I want on the very top row to the top row by  

scrolling down and I'll freeze panes and freezing  panes will lock the top left corner of your active  

cell when you click you'll see that everything  above or to the left of this active cell is now  

frozen so as I scroll around I can still see which  fiscal year I'm in so that's really helpful and  

maybe I'll move this assumptions piece down down  below so now that I'm here I'll start the income  

statement income statement so let's start with net  revenue the net revenue is pretty easy so an equal  

sign we'll multiply units times price and you can  see that it's just grabbed the format for us so  

we have net revenue and then cost of goods sold  COGS the acronym so we'll multiply units times  

the asterisk unit cost and then copy C sorry ctrl  C and then drag it over and now we have our gross  

margin or gross profit as you could say and so  that will be our revenue minus our direct costs  

which are our cost of goods sold and what I'll  usually do here in things like gross profit I'll  

put a GP percent something like that underneath  so you can see exactly what the margin is so I'll  

divide gross profit by revenue go to the Home  tab make sure that you're using a percent number  

format and so we can see that you know our gross  profit is is exactly flat which would is well we  

would expect in our assumptions because our price  and our unit costs are staying the same next we  

have our operating expenses expenses and we  have two pieces and I'll just link them in  

from the assumptions up below up above labor  and marketing and to make it a bit easier to  

see I'll go to the Home tab and I will increase  indent so that they're kind of out to the side  

so you can see they're sub groups of operating  expenses if we grab labor grab labor here and  

you can just copy it all down because it's in  the same number of cells you have six cells and  

six cells and ultimately we have is operating  income or earnings before interest in taxes or  

ebit many many many different words for the exact  same thing and you know whenever actually I have  

multiple costs whoops I'm going to insert a row  here and I'll create a total just so you can see  

and I will decrease the indent maybe put a little  bottom border here and I'll put a a subtotal just  

so you can see what our total operating expenses  are and then your operating income is your gross  

profit minus your operating expenses so you know  this business sucks until the third year and  

we'll do kind of the same thing so maybe we'll  say operating margin no percent so that's easy  

enough now we have this your operating income and  in order to calculate a margin what you have to do  

is you take your your profit and you divide it by  the revenue always so now if we apply a percent to  

it we have our operating income all the way down  if you wanted to go one step further we could have  

taxes so we would have to add a tax assumption  up above so if we want to go back into our  

assumptions piece maybe we insert a couple more  rows and we add a tax assumption because we need  

a tax assumption to get us the net income so let's  say the tax rate is 40% well that's not showing up  

as the correct format forty percent each year  now we can go down and it's quite easy we take  

our operating income except here our operating  income is negative so you don't pay any taxes on  

but if you're operating income is positive let's  not worry about a tax loss carry forward here  

let's say that yeah if you're forty percent  and then you have your your operating income  

and then ultimately you get your net income and  so if I'm coming in and I'm you know inheriting  

this financial model here it's very easy for me  to play with it you know I can see basically all  

of the important metrics which are gross profit  revenue operating income net income and then if I  

want to I can say okay here's my here's my PL you  know how can we change this business you know what  

if we bring our unit costs down to 140 and then  130 and if you don't label the model correctly  

it's it's very very difficult to tweak things and  in business very frequently you're you're changing  

assumptions you're tweaking you're constantly  rebuilding so you want flexible easy models that  

are formatted in this way and these are industry  standard formats so you want to keep your large  

numbers with with no decimals and your assumptions  in blue all right well if you'd like to learn more  

I have a udemy course covering advanced modeling  topics it's called how to become an Excel power  

user in two and a half hours you'll learn things  like learning how to use Excel completely without  

the mouse to boost your speed there's a link in  the course details and thanks for your attention