Financial Model in Excel
Financial Model in Excel! 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 becaus 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 the mouse really really slows you down in Microsoft Excel so I try to avoid it next.
For more information on sell your business in Queensland please visit Nash Advisory
We have the 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 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 assumption piece down below so now that I’m here I’ll start the 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 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 subgroups of operating expenses if we 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 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 does not worry about a tax loss carryforward here let’s say that yeah if you’re forty percent and then you have 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 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 very very difficult to tweak things.
And in business very frequently 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 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.
For more articles visit this website
