 Hello, everybody and this welcome to our webinar for today on financial modeling All right, so it's quite a small Session as in not too many of us here. So I want to make it as interactive as possible So we'll build this model together this we're going to build a budget model today part of the financial modeling webinar and Also, I'll share some information on On the financial modeling Institute some new information about the financial modeling Institute So we have our budget figures, but the thing about building a budget model is not necessarily just you build a model And that's it you have to also do budget and fork I have to kind of reports on the budget every single month So every single month they are reporting actuals against forecast and that's where things get a little bit tedious So what I want to do is just do a very simple Model, let's see if I can get some sample data We're going to do a very simple model With simple data, but then we're going to pretend that time is passing And we will need to put some actual data in there and some budget data all together in one place So I think I'll use this data. Let's go to a budget template. Let me copy Some raw data. There's some raw data here. It's just fictitious information And let's paste that fictitious information here Okay, so I have revenue cost of goods gross profits as journey other expenses profit before tax tax and profit after tax Right, so let's assume that this is budget now When once you build your model if you follow along some of our courses those have attended our courses and those that haven't You there's actually I'll give you a link I'll give you so probably a coupon at the end where you can actually go and access A course that will teach you how to build a template a template like this a template model A template for your model. So one key aspect of models when you build models is you need timing Timing and Let me just say timing masks So timing masks, what do I mean by timing masks? Let's even put our financials. We don't even know when our model is starting Let's say first of january 2018 So if I come to my menu sheet, let's just agree when our date our start date is 2018 We're going to build monthly model So I'll just do monthly total monthly forecast we're going to do for maybe 24 months and This is today's date Version 1.0 and that's fine. So this is going to be our first forecast year this 2018 Or should I in fact, let me just put january 2018 So january 2018 and we're going to build for 24 months So in that situation it's good practice. We're going to put that Somewhere up here So let me come to menu. Let's see this menu And we have a date there already. So let me just delete all this. I don't need that Just copy simple budget okay, so We're going to put our our timing here as we're starting at a date. This is the first date. We're going to start is let's put it here equal to We're starting in january 2018. So I put an f4 there So january 2018 now this comes in as a number now in excel it dates our numbers We need to format this as a number because the formats Can not change what's in the cell but changes what we see and there's a simple format code I'm going to put mmm dash y y y Not the mmm. We kind of know, you know, so this is The start this is starting period now going afterwards after this is you're probably just going to go in here And I want to move one month forward. So to move one month forward in my timing you use something called your month So e o month gives you the end of month for the next period. So I say your month one Let's I'm going to copy and paste special format I'm just pasting the format on this just to see it So what I did in here is your month and then I'm going to drag this all the way to maybe Till I get to Another year. Let's see. This is january february march april may december Then january february march april may december up to december. So I think we're good here I can delete the rest Okay All right, so There we go. Cool So this is period one. This is your first period second period third period and stuff. So this is january To the end now our timing flag. Let's assume that we have a way of identifying when our actual data is I want to be able to identify actual actual Data, I want to be able to identify forecast data So when I'm building my model, I want to be able to identify actual data and forecast data Such that if these are my budget figures, let's just assume that these are my budget figures I'm going to call this one's my budget figures yeah Now we'll change them to assumptions, but I want to be able to identify when is actual figures and when are when are forecast figures But I'm going to use a mask to identify them So when you're building your budget model, you should know that when if you are in march 2018 That means january february is actual and maybe that march is forecast So I want to be able to identify when is my When is this actual start? What is my first year of actual my actual actual Figures, this is like my small input figures Starts on So I'm just saying where does my actual figures start on my actual figures start on what? What's the question my actual figure starts? Maybe let's see What april 2018 I'm just putting figures here. I'm just use the paint brush to brush this So let's just say actual start in april 2018 So if my actual starts in april 2018 I'll come here So my actual data. I'm just going to link it here So that we can see it All right Hello Okay, so I'm going to link it here. Sorry Let's everyone can see this april again. Why is it showing a number? If I the shortcut to make this Just you do control something called control hash Or if your key if hash is on on on three on your keyboard is control shift hash That's what I just did here Conditioned hash. So if this is april 2018 one one Aspect of this is okay. How do I know? When actual is and when forecast is so I want to be able to see if actual is april 2018 that Or yeah actual starts in april or the actual was up to april 2018 That means january february much april is is actual And then may june july blah blah blah is forecast So I'll to be able to identify actual data. How do I do that? Who can suggest a small formula for me, please anybody? I want to be able to say true. What I need here is this I need true True true true These are actual and then Falls falls falls all the way here false false false false false What I want is this is My actuals are up to april 2018 That's my latest actual Actual figures. Okay. Latest actually say latest actual figures Latest actual figures So the latest actual figures I have is april What that means is maybe I'm in the month of may right now and all my actual figures are up to april So I will I will not want in my final report. I will not want my budget figures to be I don't want to see budget figures for january february march april I want to only see budget figures starting from words May right me And the best approach to do that is to create a mask. You need to have true and false is here Do you get so here we are assuming these are our budget figures, right? Yes, and then let me just put some fictitious information for our Act for our actuals. So let's assume that our actuals. I'm just going to copy some info Here, let's just say rand I'm just going to put some random numbers for actual run between The minimum of my figures here the minimum of this f4 This is just me generating data. Don't don't mind my excel so the minimum of those figures from g to Yeah And maximum of the same thing. So I'm creating random numbers using this fictitious figures I have here So there we go maximum of the same thing close Close so this should give me a random number and it should give me random numbers all through And what I can do is this Everybody knows that this is supposed to be equal to this minus this, right? It's a formula. These are formulas These are formulas. These are formulas And I can just drag that to the right This is also a formula. It's equal to equal to gross profit minus It's g and a minus other expenses And then this one is equal to profit after tax minus tax, right? Yeah, right. So this are random number generators This formula is here. It's the same formula as this. So let me just copy this one's down here Or just copy the formula. I'm just being lazy. I should have just copy the formula here And then this one Let's see this gross formula. This is equal to this minus this minus this The figures themselves are not difficult to get. Everybody has budget figures. The problem is automation Automation is usually where the trouble is for budgeting Because every month people spend so much time budgeting When they should be doing more productive work Do you agree? Yes So that's that's what we're trying to automate. So if these let's assume All right that these are Figures Yeah And if these are actual figures, let's say our actual figures stopped in In where? April. April. So these are actual figures or even let's leave Fixed shows figures there, but what we now want to have here Is okay. So what is our Is it what what do we call it in your business when you have actual and budget and forecast in the same report What was it called? Is it? Latest estimate or something What what every business what does in your own business what what they call it? It depends on how many months, but uh, uh, they call it F8 F8 plus okay f8 plus four and all those things right My former business is called a pf1 pf2 Okay Yeah, pf1 pf2. Okay. So the trick for us here is we need a way Of automating this Such that when we have our budget figures and of course We can see how to project these budget figures going forward, right? It's we'll learn how to do that But the the core right now is this my my financial report or whatever that I want to show my management for The month of for this revenue here, which what should I pick as my latest estimate? Should I pick actual or should I pick? budget actual So I pick out Definitely, so I pick actual for january But I don't want to do this manually because I would have done it manually like this, right? Let me by the way, let me fix this figure so they don't change Although I'll just save the formula for next time maybe I don't need to write it again. Let me just drop it here And then let me just fix these figures. So what I'm doing guys. I'm just copy paste special values He's like the best friend of a lot of people copy paste special values So you highlight you do control c, you know the shortcuts alt e or sv. Yeah, nice So let me show you another way to do that shortcut So if you highlight what you want to copy paste special values like this, I highlight it What you do is you take your mouse to any edge you right click drag and drag back And then you release your mouse So I don't know if you saw that it's very funny Copy and paste special values shortcut Once you highlight anything you go to the edge you drag With your right mouse and drag back and then it gives you options copy here as values only Very nice trick for copy paste special values All right, so we now have all our figures So the key thing for us now is to automate this revenue such that anytime our budgets anytime our actual changes The forecast figures we change and I think I should bring those my forecast figures up here So we can see it happening. I'm going to promote this whole line So if when you're building models, right, it's always useful To have flags and masks now what I mean by that is this I want to say is this month Maybe greater than or equal to this month, right? Now for this these few months, you should make sure that it's first of the month So we're asking you this for you to say your actual Then this month here, right must either be greater than Yeah, and I have to lock I'm locking the column here. It has to be let me make it bigger So you can see equals to this Our actual this the latest actual figures must be greater than or equal to this my Figure this my dates up there And what am I locking on this who knows please who can tell me What am I supposed to lock anybody You should lock the row should lock the row perfect. You're right. I should lock the two f4 f4 So now we have a true when we highlight and drag that to the right Okay, I think I figure stopped here. So let's just stop here when we drag it to the right You see true true true And then you see false here. Why do we see false here? So obviously this is not first of the month So let's check because here we're saying are you greater than or equal to if you're greater than or equal to which you see true So if you if I do a copy paste special values on this and then we check what it is You see is the end of the month 30th of the month So this is usually a problem Yeah, so for us to make sure that that's my problem because this is a monthly model Because this is a monthly model. What you do should do is make this End of month try and no matter what someone types in here in your inputs So here in your input, let me change this format so we can see what's exactly in there The person put that hey the latest actual figures in our database is April so you wanted to type april boy put first of april. Well, then in our model These things here are end of the month. So to avoid that in your formula make sure you convert Um, you convert to end of the month This figures here. So this should always be automatically converted to end of the month or you can do it in here so when you're bringing it from the from the input sheet or I'm using menu sheets You can come into the formula like this and say e o month, right? e o month tab Then go to the end comma and zero zero just keeps in the same month So you're saying give me the end of the month of this thing for this month So if I put instead of zero if I put one is saying give me the end of the month for that april for next month I have to give you next month's end of the month So this is this month's end of the month. So I put zero Now you see true true true all the way here Now forecast is just the opposite, right? So forecast is basically the opposite of this so There are different ways you could you could do forecast you could say um If this is true, then this is false. Maybe that's an easier way or or you could say equals to not Uh, well if you say not true, it's false. But then if you say not false, it's true Don't know if you know that trick So because since it's the opposite we could just say equals to not So not is a function is a logical function that just reverses the logic So it reverses the logic of something. So I just say not and I drag this to the right Now why are we spending so much time doing this? This is the trick about modeling that the thing that makes your models dynamic Is actually this this mask and flags So you can imagine I have a loan and there are 10 covenants in that loan. I'm going to have 10 Masks and one thing about mask is something called bullion logic So bullion logic means this if you say true times false Look at it. It's it's actually like mathematics true Times false will give you zero And the only time you have Something that's not zero is when you say true, maybe times true And then maybe you say times 200 What do you think that is this looks strange? But true means one in excel. So this will give you 200 So here we're now going to say, okay, do you know what? How do I know when to use? When am I using forecast? When am I using actual? So You're using actual this revenue here is you're going to take this true And the only time you're going to use true you just take this true and I'm going to lock F4 f1 when I lock the row I will multiply this true which is checking for actual I will multiply it by the actual figure down here Yeah and I can leave that as a relative reference is the same size I'm multiplied by that figure down here. Then I'm going to plus it. I'm going to add it So this forecast Which I will lock This and also this forecast I'm multiplying it because this is checking the forecast is its forecast data We should pick I will multiply it by the forecast figures. Where's for Budget figure. So the budget figure here So look at this formula. We're saying true times the actual figure of 37 million Plus false times The forecast figure of 43 So true times actual figure down here, which is this 37 plus false times 43 will give us 37 Now now because I have done the what's it called the We call it referencing correctly. I've locked the right things. I can now copy this thing I just copy and paste all the way here, right paste the whole thing like this And then I can highlight this whole thing. I like copy and paste and copy and paste copy and paste So what I've just done is we have combined forecast An actual in our model and all the user needs to do is okay We have our actual figures and everybody keeps they keep typing actual figures And then once they type the actual figure they go to their input sheet or wherever it is and say Oh, actuals are now from Maybe May or June June 2018 so by typing June 2018 and you go back to your budget data, you will see that This trues have extended to Where have they extended to They've extended to June and then it's from july that is false actual is now false And budget is now a forecast is now true And because of the way you wrote your formula, this is already correct But then let's make it easy for our users or easier for our users to see when actual is So I want to be able to put actual in here Just the name actual and then forecast. Maybe we'll put it up here So we just write a simple if right So if this is true, right this one down here is true Then what? Double quotes actual Right Else Forecast so is the latest estimate Let me say latest estimates because people like using latest estimates All right So once I do that I have something up here. I can now Centralize this or something and I can drag this control r another way to copy something to the right is control r So control r is a way to copy something to the right I'm going to delete all of this since we don't have data. We're just using fictitious data So I copy to the right another thing we could do just to improve this report is We could actually color this whole column separately so that when actual actual can be a different color To latest estimates, so it's easier on the eyes for people to see And to do that we use our trusty friend conditional formatting So to do conditional formatting it's a little bit tricky Because right now I want to conditional format the whole Table like this this whole table So if I'm doing conditional formatting, I highlight what it is. I want to do conditional formatting on yeah And then I now have to think I want to conditional format all these columns I want to maybe color this column lights light green or lights I don't know lights red or something for actual and then once it sees le it should color it What? Maybe it shouldn't color it. So just leave it So anytime actual just appears here to now color it So whenever you're doing conditional format, you need to think about the active cell when you're doing a conditional format like this You have to ask yourself. Where is my active cell if I look into my formula But I will see g3 g3 is my active cell. Let me enter If I enter like this g3 is no more my active cell My active cell is now g8 So everything you're writing your formula is based on your active cell So if this is my active cell g8 That means everything I'm writing must be in relation to either g or 8 or g8 itself So and the cell that I want to kind of work with is g3 So look at this. I've highlighted this. I go to home I go to conditional format. The first step is to highlight for this kind of conditional format Now none of these rules will help you. You need to do the format yourself to go to a new rule And then under new rule You have many options, but really you should go to formula use a formula to determine which cell to format Now the way conditional formatting works with this style is As I said think about your active cell and pretend you don't know anybody else But your active cell and then once you lock things right it will work for everybody else So here we want to what you're looking for is you write a formula that evaluates to true or false That formula must evaluate to true or false So whatever formula you're writing here the answer of that formula must either be true or False and when it's true it should implement your format and when it's false it shouldn't implement your format So question is what is the formula? So we have to think I want to color the columns that have actual in the cell g3 Okay, see that I want to color the columns that have actual in the cell g3 So obviously i'm going to type g3 now. Why am I typing g3 if my active cell this my active cell was in i I would type i3 Okay, that's what why active cell is so important So you have to type based on the active cell of g3 then i'm g Eight i'm typing g3 Now the next important thing after typing g3 Is this rule every single column here is going to obey row three Like you know this column i i must look at i3 m Must look at m3. So if you notice g is changing i is changing m is changing but three is not changing So that's what we're going to lock So you're going to lock Three so here i'm saying equals to g dollar three not dollar g3 not just g3 And definitely not dollar g dollar three. It is g dollar three. This is called referencing And if you want to be a good modular you need to understand referencing in total absolute reference relative reference Row constant column constant. So g dollar three. You're asking. Are you equal to when you put double quotes Double quotes now again, this is hard coding right, but it shouldn't really hard code So typically what I do is I type this in the cell somewhere and I say is equal to that So once you say are you equal to actual they go to format and then that's the light fill maybe Let's see. I don't know what color is nice I mean this color. Okay, so this is a light fill Now once I do that once I I say I put the fill I say whenever This g dollar three is equal to actual. Please fill it with this color click okay And you see it fills out Now let's test it. Let's go back to our menu And change the when we got actual so maybe february 2018 So now we expect coloring to be general and february right so go there you see January and february are nicely colored and the rest are not So gradually we're improving this tool and making it a little bit better and easier to manage So then what you could do is do you know what I would prefer to have So I'm going to put a drop down. I'm going to create a combo box to take in my actual figure starts at a certain point Now when you create combo boxes, let me show you what it is first and then I'll explain if you go to developer There is an excellent tool under developer developer is a is for vba. It's a different software But under vba, we go to insert and then there are some nice tools in here These are form controls. They control how people use your model As much as possible, right? Don't allow people to type Into as inputs in your model if you can avoid people typing is better. Give them a list So if you look at this list, I'm going to click it It's a combo box. I draw the box Now this is not excel This is a tool that works with excel But if excel is only in cells anything that's not in a cell in excel is not excel Just take it that way Now this tool, how does it work? You have to fill it up There are two things you need to fill up in this tool One is you fill it up with list and then you designate a cell to be your switch So I right click I say format control now what I'm showing you is not best practice This is not the best way to do it. But I'll just for Making it fast So your input range if I come here If I was in excel, right? I would typically do control shift right But that's not working. You have to manually highlight. Unfortunately I have to drag it manually to the end. So this is my list What about my cell link? Which is my switch? I'm just going to select a cell as my switch. I click okay Now once I click out and click on this drop down Oh, you see that it's only january showing and that's because the list doesn't go this way It's only only takes a list downwards, which is so unfortunate So it needs a list that goes down not right Okay, excel would have accepted that data validation would have accepted this but combo box doesn't So for us we could just come in here And generate a list So we could come and just say, okay, we're going to generate a list here I just want a list of months starting from obviously when we started which is this month And So this is my list shortcut if you remember control hash So this is my list and how do I grow month on month? I basically say, okay, this is e o month e o month This comma one And they gain control hash And then I just drag this down and I create my list Okay, so that was just a quick way of creating a list for this one. I can make it end of month by just saying e o month e o month is a very Powerful tool for playing with dates. So this is my list. Let's just name this list I'm going to highlight this list and give it a name I'll call it l months Or dates maybe dates right now It's not going to be that easy to do this because if I right click this and do formats Now my list is now l months, isn't it? If I don't know if I put an underscore you can't press f3 in excel. See this is excel Anytime you name something right if you just press f3 It brings out the names very nice l underscore dates Because that is excel this thing I just brought here combo box is not excel So if I right click it and go to format control and I click in here and I say f3 Sorry, yeah on your own nothing will happen because this is not excel just a tool that works with excel So you have to remember the spelling l underscore Dates I hope I got it's dates or dates. See I remember so let's check. Does it work? Nope doesn't work. So I think it's dates Yeah dates So if I click out and click on this you see that you have the drop down now now this Drop down is quite silly right What is happening here is as I drag as I'm just clicking Is instead of giving me the dates in here is giving me one two three And that's what this combo box does you fill it up with a list when you pick Let's say this is the first item on the list the second item on the list the third The fourth when I pick I pick in the fourth item on the list all it does is type four in here So now you need to now come and say, okay If this is four Then return the fourth date on my list in here And the best way to do that is using the index function Now by the way, if you are a modeler and you want to really be a good modeler You need to master lookup functions And the most important lookup function is index forget we look up index is the most important lookup function So if I do index Index is a very funny function. It's so powerful. It has two versions It has two versions very powerful But we're using the first version. We just say okay And in this index index is asking for three things It's basically asking for a list And then in that list, which row should I go to? And which column should I go to if you remember our list is called our press f3 since we're in excel f3 Our list is called l dates Now the row it should go to is actually specified This constant this combo box we went to the trouble of creating this because this is a very cool tool So we said that this tool needs two things a list and a switch So since i'm here, let me just give you best practice Best practice is when you create your list for this combo box, right Make sure you name it give it a name and we did that we call this one l Dates and then the next thing you should do is designate a cell And make it your switch So if I call this one l dates, then the best practice is to call this switch Which is a cell just by the side call it s dates a switch s s underscore Dates so that way I have named two things. I'm going to make this an input style So this is my switch So this is my list and this is my switch Then this is my combo box. I'm going to create another combo box. I'm just going to go to developer Um insert combo box under form control I draw it. I right click it. I go to format control And because I'm doing best practice Instead of high if you highlight it will work, but you only work in that sheet It will never work in another sheet if you want your combo box to work everywhere Give it a name give your list and your switch a name And then you come here and type l underscore dates and they're on up. That's not underscore l underscore dates And they come here and type s underscore dates Now once you type those two Look at this if I click okay And I click out this is now activated if I select the third item or fourth item on the list It types four the beauty of doing it this way best practice is I can right click this copy Take this thing to This my budget sheet and paste. I just come here and paste Now this will work if I come here it is working and affecting that other cell So I can delete this guy's not best practice delete this But this one if I select number one on the two on the list If you come back to menu it has type two here And because it has type two we can now use index to affect this cell So say I want to see okay. Let me just select june. I want to be able to see june. See this june I want to see it in here. So when I select june or july I want to be able to see july right in there And to do that you do equals to index and I'll give a very simple shortcut When you say equals to index there are three things it needs array row and column If you use a combo box your array is always your list L underscore dates your array is always your list And if you're using a combo box your row is always your switch s underscore dates And if you use a combo box your column is always one So this is a shortcut When you want to kind of bring in what is in the combo box into a cell You use index and your index is basically equals to index your list comma your switch comma one always always that's how it is always once you enter You get your value and can you see our actual has updated automatically? So this now becomes your control this this button Yeah, so if I come here I change my hour. I have actual only up to uh me You can see everything Works perfectly And this technique is what you should use in combination with other techniques when you didn't really have too much time To automate how you do your budgeting and how you do your reporting. All right