Build a Dynamic Financial Model in Just 15 Minutes

in this video we'll build a simple Dynamic model like this in just 15 minutes first we'll create assumptions to forecast our Revenue then we'll work on our fixed and variable expenses third we'll work on our income statement and all the profit margins and finally we're gonna make a dynamic using the scenario analysis on Excel with a cool formula you might not know so let's get into it so here's the Excel model that we'll be working with which you can download for free in the video description to follow along as you can see we have two main areas one being the income statement which is all of this here and then down below we have all of the different assumptions for a total of five years as you can see up over here let's assume that this is for an e-commerce business so it could be like an online clothing store first up here to forecast our Revenue we're gonna need to look at our assumptions so if we scroll down towards the bottom here you can see that for Revenue we have a few different assumptions first we've got the number of orders which we're seeing we're starting at 3 000 orders and then we've got the average order value so the price times the quantity is going to give us the total revenue and to forecast this forward we simply have a border growth rate here let's suppose that we're hoping to grow by a hundred percent on the first year and then 75 50 and finally 35 percent so with this information we can go ahead and scale out the number of orders so we would just go equals this previous year's figure multiplied by in Brackets we're gonna do one plus the growth rate which is 100 in this case close the parenthesis and just hit enter there and then once we have that first number we'll go shift right and hit Ctrl R there and you might have noticed here that some numbers are in blue While others are in black and generally the convention here is that the blue numbers are those that are hard coded so you just type them in yourself while the black ones here are Dynamic so they're linking to something like a formula or a cell now that we have the number of orders we can also work on the average order value let's assume that this stays fixed throughout the five years so we'll do Ctrl V there and now we can work on the first line item for us which is the revenue this is going to be equals to the number of orders so the quantity multiplied by the average order value so the price and hit enter there and again we'll do shift right and hit Ctrl R there now that this first step is done we can now move to our expenses we're going to have two main types fixed and variable expenses so you can see if we scroll down to our assumptions we have them per order so these are the variable ones meaning that they change per every order that is made and then down below we have those that are fixed in nature so things like salaries are gonna stay fixed same thing goes with our marketing expenses so let's go ahead and scale these out so for manufacturing we're saying that it costs us 6. 5 dollars to say create that T-shirt and we can simply drag that across and just hit Ctrl r same thing goes with order fulfillment so this is things like putting it in the package and getting it delivered so we'll hit Ctrl R there again one thing you could do depending on your assumptions is maybe as you have more orders this is going to start to get a bit less as we're going to have economies of scale there so if we order in bulk we're probably going to get some lower prices then further down below we have the warehouse rent let's suppose that this stays fixed salaries and payroll marketing so this is how we're actually going about selling this stuff and other so this could be things like accounting or legal fees let's just scale this out by hitting shift right and Ctrl R again let's suppose that for warehouse rent as we have more orders we could switch this out to something like 30 000. hit enter there and just drag that across and Ctrl R again same thing with salaries maybe as we get more orders over here it's it's a bit too tiring to only do it with one person so we might double that to a hundred thousand and again we'll drag that across and for marketing as well maybe to reach this type of order numbers we need to scale this out so we need to make this bigger maybe we do something like 50 000 here and eventually we're gonna reach a hundred thousand same thing over here like so and finally for other we'll leave this fixed right now we've made these assumptions in a fairly simple Manner and later on we're going to create the different scenarios so this could be for example an optimistic case and we're gonna have a different set of assumptions for a pessimistic case so we can kind of adjust and see how that would look finally we've got the corporate tax rate in this case we just said it's 20 we'll hit Ctrl R there this is obviously going to vary depending on where your company is incorporated with all this information we'll now be able to bring it up to the income statement and speaking of financial statements if you're looking to learn more about financial modeling you can consider checking out our complete finance and valuation course where you can learn all about financial accounting Corporate Finance valuation and financial modeling on Excel first we cover financial statement analysis using Apple's real annual report as an example then we'll get into Financial modeling through a three statement model after that we'll begin the valuation phase where you learn to do a discounted cash flow a comparable company's valuation and a present transactions valuation on Adobe looking at the real financial statements to eventually derive evaluation range lastly we'll show you how to present an investment thesis using a stock page format so if you're interested in checking it out head out to the link in the description below alright back to the video now that we've calculated all of the assumptions we can work on the income statement and the profit margins so let's go back up over here and the first line we have is the manufacturing which is going to be equals to the number of orders and let's lock this one so we can drag it across so we're going to hit the F4 key there and you'll see there's a dollar sign and we just want to lock it by the row so we're going to hit the F4 key one more time so that dollar sign is only by the 30.

this way it's always going to stay fixed to this number of orders and even if we drag it down it's gonna stay there then we're gonna multiply that by the manufacturing because these are going to be on a per order basis and we'll hit enter there now we can simply drag it down and hit Ctrl D and we have that down over here and if we click on it you'll see how this has stayed fixed at the number of orders while this cell has gone down because we then lock this one which is what we want then the total cogs is simply going to be alt equals that's going to sum these two over here and we can hit enter there then for our first profit margin which is the gross profit that's going to be equals to our Revenue minus our total cost of goods sold and the percentage year is going to be equals to the gross profit divided by the revenue and we'll hit enter there then to drag all of these across we're just gonna hit the shift right and hit Ctrl R they should all move dynamically there awesome now moving on to our operating expenses down below over here so these are going to be fixed in nature so all we need to do is simply link them so we link this first one and then we can just drag that down and hit Ctrl d then for the total it's going to be the alt equals is a shortcut again we'll hit enter there for the operating profit margin this is going to be equals to the revenue minus d total cogs minus the operating expenses and we'll hit enter there as you might have realized this is actually the same thing as going to our gross profit minus our operating expenses then for the margin here it's going to be equals to the operating profit margin divided by our Revenue and we'll hit enter there then we can drag that across as well just by hitting the shift right and going to control r awesome and finally we've got the corporate tax this one's not quite that simple because if you don't have a profit there's nothing to be taxed on and so that's where we're gonna have to create some kind of a conditional statement so we'll do equals if hit the top key there and The Logical test is that this figure over here is less than zero now if that is true then we're gonna do an N A in quotations there so open up the brackets the quotation sorry hit the n a close the quotations comma and the value it falls is going to be the actual multiplication so it's going to be the operating profit multiplied by the 20 corporate tax rate and we'll hit enter so that first one is an n a and that seems about right but if we drag this across and hit Ctrl R you'll notice how how all of these are getting taxed and that makes sense because they do have a profit in those years then for the profit or loss it's going to be equals to our operating profit minus our taxes and we'll hit enter there but you'll notice that this first one gives us an error that's because we're trying to do a minus on a text so Excel is not able to understand that so what we're gonna have to do is put an if error in front hit the top key this is going to be in the case where there is an error like it's currently the case first we want this value but if the error does happen so value if error then we're simply going to want the operating profit amount which is going to be the negative loss for us and we'll hit enter there so that first one we have a loss and then as we drag that across and then hit Ctrl R you'll see how we start to have a profit and so it's going to be this figure minus the tax which equals to our profit which makes sense there finally in this last part of the model we're gonna make it Dynamic using the scenario analysis so right now you might have noticed that we only have this set of assumptions let's suppose that we think this is a touch too optimistic so what we need to do is actually copy this whole table so make sure you drag it all hit Ctrl C we'll paste it once and then a second time down over here as well what we're gonna do is change these assumptions we'll call this one this is the middle one right now and we'll call this the uppercase and then in parenthesis I can say something like scenario one so this is going to be our first scenario and then down over here let's call this one the lowercase and this is scenario two close those parentheses and then the very first one we had up here is gonna be called our live case as it's going to be the one that's reflected on the financial models over here so we've got the uppercase which as of now is the same as the live case and we've got a lowercase so for this lowercase let's change up the assumptions a bit because we felt like the first one was maybe attached to optimistic as you can see we already have a profit after that first year which maybe is a bit too aggressive so first we can switch up the number of voiders to say 2000 and you can see how all of that is going to change as well then for the order value maybe it's more realistic at around 34. 95 and I can drag that across as well and hit Ctrl R there and finally one more thing that I could change is maybe these manufacturing orders are going to be higher at around say eight dollars so we'll go for eight there again I'll drag that across Ctrl r and finally maybe the government decides to increase our tax rate to 25 so let's see what that would look like as well great now that we have these two scenarios we just need to create some kind of a toggle up over here so I'm going to call this say scenario and over here we want to create a box that's going to say scenario one or scenario two there so let me quickly format that let's say we go for putting borders all around it so all borders let's change the color there to something like a yellow and I'm also going to center it embolden it by hitting Ctrl B same thing over here I'm just gonna align it over to the right awesome now comes the part where we actually make a dynamic that's by going to equals up over here and we're gonna use the choose formula hit the top key there and that index number for us is a scenario so this one right here will hit the F4 key there comma the value number one is going to be the first case so that's our scenario one is going to be this figure over here a comma and our scenario two is the lowercase so this one right here what close up parenthesis and hit enter now all we need to do is just hit Ctrl C shift down arrow shift right arrow we'll go to control alt V for that and here we basically don't want it to change the format so we want the second one in percentage then this into decimal places and so on so we just want to paste the formulas there so that second one and hit on OK and you'll see there that it's done the job for us let's clean this up a bit by just removing these here and same thing here and this last one down over here finally we should change the color of all of these because now they're Dynamic so they should all be in Black like so now you can see that it's actually all gonna be dynamic so right now we're in scenario two I can change that to scenario one and all the numbers are gonna change in scenario one you can see that we have profit in 2024 if I change that to two you'll notice that we only have profit in 2026.