The Complete Course On Creating A Waterfall Report In Excel | Chris Benjamin | Skillshare

The Complete Course On Creating A Waterfall Report In Excel

Chris Benjamin, Instructor, MBA and CFO

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
16 Lessons (1h 16m)
    • 1. Introduction to Waterfall Reports in Excel

      1:33
    • 2. Introduction

      6:37
    • 3. Review of a Sample Waterfall

      8:04
    • 4. Waterfall Framework Video 1

      3:42
    • 5. Waterfall Framework Video 2

      4:38
    • 6. Waterfall Framework Video 3

      4:21
    • 7. Waterfall Framework Video 4

      4:58
    • 8. Waterfall Framework Video 5

      6:56
    • 9. Waterfall Formulas Video 1

      5:36
    • 10. Waterfall Formulas Video 2

      4:25
    • 11. Waterfall Formulas Video 3

      4:38
    • 12. Waterfall Formulas Video 4

      2:30
    • 13. Waterfall Formulas Video 5

      4:14
    • 14. Waterfall Report Vetting Video 1

      6:31
    • 15. Waterfall Report Vetting Video 2

      5:11
    • 16. Course Conclusion

      2:27

About This Class

Are You a CEO or CFO of a Startup Company?

Are You An Entrepreneur Who Wants To Know How To Effectively Work With Investors?

Do You Want The Inside, Real World Knowledge Of The Startup-Venture Capitalist Relationship?

Did You Ask "What Is A Waterfall Report?"

Do You Want To Learn How To Build a Waterfall Report Investors Will Love? 

If You Answered "Yes" To Any Of The Above, Look No Further.  This Is The Course For You!

*** Updated June 2019 with new content! ***

Enroll today and join the 100,000+ successful students I have taught as a Top Rated instructor!

Three reasons to TAKE THIS COURSE right now:

  1. You get lifetime access to lectures, including all new lectures, assignments, quizzes and downloads

  2. You can ask me questions and see me respond to every single one of them thoroughly! 

  3. You will are being taught by a professional with a proven track record of success!

  4. Bonus Reason: Udemy has a 100% refund policy - no questions asked and no risk for you!

Recent Reviews:

Pasqual R says "Really glad I took this course.  The waterfall report has been a life saver for me in my company, such useful information and impressive yet simple.  I was able to build one with ease, provide it for my management who were blown away not just by the report, but also by my initiative.  Chris is a great instructor, very knowledgable and personable"

Entrepreneurs: Why You Need A Waterfall Report:

Have you ever been asked to not only track a Budget vs. Actual month by month, but also somehow track changes in the budget... in an easy to use, simple way?  Just thinking about it made my head spin the first time!  

There's still hope!! Let me teach you how to wow investors with a Waterfall Report that does EXACTLY WHAT INVESTORS WANT!

By the end of this course, you will have fully developed waterfall report which is detailed, complete, and most important: 

INVESTORS, CEO'S, CFO'S, MANAGERS (AND EVERYONE ELSE) WILL BE BLOWN AWAY BY!

This course teaches entrepreneurs, startups and small businesses, accountants, CFO's and ANYONE how to write build a waterfall report that is actually effective and usefull, follows the best business practices, and isn't another "fill in the blank" report investors hate to see. 

What You and I Will Do In This Course

In this course we start with a completely blank Excel workbook.  By the end, we have a fully functioning Waterfall report.  During the course we do everything that takes us from A to Z.  We'll go step by step on building, best business practices, data entry, how to maintain and update the report, and more.

The course is almost all video instruction, a mix of both me talking and instructing, along with screen captures as we work through a waterfall report.

If you are ready to roll up your sleeves, ditch all the fill in the blank software and build a report that is custom tailored to you, your team and your investors needs, then lets get started today! 

Additional Bonuses For You

I have also included a copy of my ebook "Fund Your Startup by Chris Benjamin" which you will find as a download in the last lecture.  Learn your options for funding, where to find investors, how to wow them, and lastly the must do steps to successful funding!

*** JOIN NOW AND LEARN HOW TO BUILD A WATERFALL REPORT WITH EASE TODAY! ***

About The Instructor

Chris Benjamin, MBA & CFO is a seasoned professional with over 20 years experience in accounting and finance.  Having spent the first 10 years of my career in corporate settings with both large and small companies, I learned a lot about the accounting process, managing accounting departments, financial reporting, external reporting to board of directors and the Securities and Exchange Commission, and working with external auditors.  

The following 10+ years I decided to go into CFO Consulting, working with growing companies and bringing CFO level experience to companies.  I help implement proper best business practices in accounting and finance, consult on implementation of accounting systems, implementing accounting procedures, while also still fulfilling the CFO roll for many of my clients which includes financial reporting, auditing, working with investors, financial analysis and much more.  

Thank you for signing up for this course on Udemy. I look forward to being your instructor for this course and many more!

Chris Benjamin, Instructor, CFO & MBA

Transcripts

1. Introduction to Waterfall Reports in Excel: Hello, everybody, and welcome to my course how to build a financial waterfall report using Excel. My name is Chris Benjamin and I go by the broke CFO, accounting of finance professional with over 20 years experience in the industry. In the last 10 years, I've worked as a consultant, working with smaller companies who are looking to grow. And a lot of times a big part of that is working with investors, people like venture capitalists, angel investors and crowdfunding. So what I've done is I put together a course that shows you how to build a financial waterfall report. Now what is it? It's a report that investors really like to see because it shows and compares what the original forecast is for your revenues, your expenses, your cash outlay. Then it compares that your actuals, and then it tracks changes over time and that creates that waterfall effect where they can then track and see what the changes are. While still referring back to what original amounts were forecast. It it's really simple in the end, but it is really, really useful. And like I said, investors love to see the support and not everybody knows about it. So I'm here to teach you how to create one. You can create one in less than a few hours. After you take the course, you will be able to build these no problem at all. So we're gonna go step by step. We're gonna go start with a blank workbook, and by the end, we're gonna completed waterfall report. And then as well, I provide the completed report for you to download in case you want to download it as a sample to use for yourself or so you can follow along whatever the case might be. So I encourage you all to go ahead and sign up today and learn how to be an expert at building financial waterfall reports in next to no time. 2. Introduction: Hello and welcome to my course, everybody. How to create a waterfall report. My name is Chris Benjamin and I go by the rock CFO a little bit about me and my background and why I'm sort of qualified to teach this course. I have an undergraduate degree in accounting and finance and then went on to get a master's in business from the University of Washington traffic school. Have, over 20 years experience in accounting and finance and working as a CFO. I worked in both private and public companies, all different industries, and probably most relevant is the last 10 years I've spent working as a consulting CFOs. Rather than work for one company, I basically go out as a consultant and help growing companies with all sorts of things, whether it's developed business plans, financial forecasts, working with investors, reporting Securities Exchange Commission lots of different stuff. But part of that working with start up companies as I get to interface with a lot of venture capital firms and angel investors and people that are interested in seeing how the performance of the company is. And that's kind of where the genesis of this waterfall report came from It was used in several firms, and I went on to refine and just kind of make it nice and easy. So I should mention as well before we go on and talk more specific about the course of several other courses on new Demi encourage you to check them out. Everything is sort of focused towards smaller businesses, so I have finance and accounting for start ups. You know how to build a financial forecast in Excel, how to write a business plan, how to find investors. And there's lots more coming down the road. So definitely check out the other courses that I have to offer. And I will mention just now at once the end of the course, you get to the end of the course. There's actually promotion code where you can take any mother courses for $10.99 and as many of them as you want for that price, definitely stick around and check out the promo code. At the end of the course. Let's talk a bit about the course outline, so first of all, we're doing this introduction now. Then what we're going to do is go step by step and basically start with a blank workbook and build a waterfall report. So what we'll do very first is look at a completed waterfall reports. Do you have an idea of what one looks like and where we're going, or at least the one that I'm gonna be teaching you eso from there will. Definitely The major sections will be frame working formatting, so we'll set it all up. The next section will put in the formulas and then the final section will do. Vetting and testing will put in test data to make sure everything works. And at the very end, of course, we'll do a course wrap up and make sure we met all of our goals. So each major section contains multiple video lectures. So we go step by step, literally like you see every single step it takes from blank workbook to finished waterfall report each videos approximately 4 to 6 minutes. What I tried to do is I would be doing something and maybe showing you some formatting stuff and maybe some tips. And if I noticed the video is getting a little bit towards that 56 minute mark, I cut it off, then we pick it up again in the next video. Just so it's a nice little sections. Feel free to pause videos along the way, so it's up to you if you want to build your waterfall report or anyone. Maybe it's a test along the way. As I'm going, feel free to do that, and maybe you just have to pause the video. Or maybe there's some things you have to watch twice just to really understand it, or just watch the entire course and sort of absorb it. And then you probably by the end would have a good idea how to go about building your own waterfall. Report. It's up to you. I wouldn't encourage you to skip around just because it does obviously start from scratch and goes from a disease. So it's kind of hard to, you know, jump toe em when you're still need to be at A and B. So let's talk about some of the goals of the course, So the goals are to provide you with the tools to build your very own waterfall report. And I mean, of course, the rial tool you need, like physical. I guess you'd say is the software is Excel. I should mention. It works in any spreadsheet. You could use Google sheets as well. It will be the very same concepts, but to give you the sir, that knowledge is what we really want to give you along the way I throw in some bonus added Excel tips, some tricks and shortcuts. Basically, I'll be doing something. I use 10 to use a lot of hot keys. So, like, you know, control B C V. You know, F twos, things like that. So I always talk about what I'm doing, since you might not see that on the screen. And then I show you other ways of doing things as well. So wherever I can to do that, especially when it comes to building the formulas, I could be a bunch of tips in there on ways to make it quicker. So let's see. So yep, show you several ways, do things and excel. And then, basically, at the end, we want to give you this new tool that you have in your backpack that you can use with investors, manager, CEO, CFO's and more so whether somebody has actually asked you to produce this report. Now you know how to build it or you are Say, I don't know your position, but you're the CFO and a new start up and you want to impress the investors. Go ahead and build Ah, waterfall report. And then you could present it to them either you know, at a meeting or a board meeting and say, Hey, look at this tool that we have now where we can track changes between our actuals and our forecast. So before we start, and then we'll dive in. So feel free to email me with any questions. There's, Ah, as you've taken the course of several opportunities for you to message me through you Demi relaxing, absorb the information. I know what times that might. If it's new, it might seem overwhelming or difficult. Or maybe you don't understand a concept or something. I'm doing an excel. Maybe just watch a little bit longer and see if it makes sense or go back and re watch it or email me with any questions. Review the lectures, especially ones most applicable to yourself. So maybe you, you know, you really want to focus on the formatting aspect. So you go back and look at formatting again. So the goal is not to become an expert at everything related to Excel, nor to become a budgeting probe were basically building the report that brings it all together. But we're not necessary. Building the budget and we're not building the actuals are doing any accounting. Were just providing a template, basically document to kind of present to investors and management, etcetera. We want to be able to build the tools that allow others the ability to easily make decisions and see what trends are over time. So that said, I encourage you to jump, Then start taking the course I will mention as you go along. I think after a few lectures, it'll pop up and prompt you to give a review of the course. I encourage you give a fair review, or maybe watch till the end before you go ahead and give your full review. Just I think it's a little premature it after one or two videos ask you to review the entire course up. I think you'll get a lot out of this. It's gonna be terrific, and by the end you will have your own waterfall report. So that said, let's jump in and get started 3. Review of a Sample Waterfall: Okay, everyone in this lecture, we're gonna take a look through a sample waterfall report that's actually done. I want us to show you what one looks like when it's complete. So then as we go on and we build our own, you kind of have an idea in your head of what we're after, where we're going with things. So a few things to point out, Justus notes. And they were gonna look through this in more detail. Ah, this one only goes for nine months. Of the one we build will be a full 12 months in all 4/4. We'll probably slightly different coloring. I think this was the company's coloring of the example amusing. And then as well. They did this for four different sort of line items from their financials that have for revenue for gross margin. With scroll down operating expenses and their cash balance, we will only be building one section. But the mechanics worked the exact same for each of these. The point is that once you build one, then you can really choose what line items that is that you want to track in your auto fall report. You could ultimately track every single line item on your financial statements. Probably a bit of overkill. This is kind of a standard outlay of what one would be looked like. The point of this So this is really is he might have ran the course description has really used between start up companies heavily and their investors, their investors want to see how are things progressing. And how are we changing our updating our forecast as time goes on based on our actual results. So speaking of that, let's get into some of the numbers. So first of all we have are year they only did nine months. This is an older one to that in six. So you lay out each month and again we're gonna get in. We're actually gonna build this from scratch so we won't spend too much time on the building part here. Essentially, what you do then is you lay out your original operating plan. This was your forecast at the beginning of the year for each month. What third revenues would be each month and says You stamp your budget budget will use the words interchangeably. Budget forecast, etcetera. They use operating plan here and then there's budget up here, but nonetheless sub budget and then actual results will be in yellow. So for each month. And you see the month up here in the month up here. So for January, the actual results were this for February, the actual results for this. So the way this waterfall works and it gets its name from this kind of cascading nature of the report. So the beginning, the year we set up our operating budget plan, January happens and we had this amount on sale slightly less than the forecast. What you do then is you forecast forward. So if you re forecasts or whether you don't, you put in the new numbers. So So this company kept the same forecast for February. For some reason, they bumped it up just a tiny bit in March. Maybe they knew about some special deal. It was coming in. And then the rest of the months. As you can see here, the original forecast was 100. Then revised forecast is 100 it gets more interesting when things change. So we'll look at that. So February happens now. And we had forecast that 2500 originally After January, we forecasted 2500. Still, an actual sales came in at 17,000. So now the company forecast for it again. And that's the way this goes. So each month you have the actual results and then the re forecast of numbers for each of these months. So, for example, they had 17. Even though they had a lower month. They forecast, now 60. So maybe there was some sales they were counting on here that just are in the pipeline but didn't close. So now they're counting on them in March, who knows? They're forecasting a lower April, a lower May higher June. So whatever the reasoning is, that's not so much what we're concerned about here. The point is that this is where you would then enter your re forecasting and it continues. We'll just look at one more month. So March happens. They originally forecasted 50,000. They bumped it up a bit. Then they bumped it up a lot to 60,000 and it didn't turn out so great. It was $22,000. So now again, the reef forecast forward 25 40 50 and you see a big drop here, originally 100 and 77 now 25 originally 1 51 140 And that the same story goes on. Um, let me, let's let's look at the last one they originally forecast of 404 100 now 200 things they're starting. There's obviously a bit of an issue they're not. There's something happening, and that's how it goes. So at the end of the year, I mean this case, it's only nine months. You have a nice picture of each month. How did you change your forecast based on what happened? And there's probably a bit of a story in here. So as an investor think of this from the investor's perspective, when they see something like this, they will start to ask questions like, Well, OK, so originally you thought you were gonna do 100,000 in April, and it only ended up doing 32. What happened there and as well. We see you dropped your forecast weight on a 25 but you actually did 32. So it's a good tool for X, bringing light questions about the budget, how the budgets changing what actually happens, Um, ultimately So then, yeah, here you have originally 400. They dropped down to 200. And then as I got closer later in the year, they bumped back up to 2 75 and hit 2 45 stuff. Hopefully, you see the story that it kind of tells now it's a simple report. Don't get me wrong. I'm not saying this is completely complex, but I hope you see the value both for a company and for an investor looking at this and being able to really see these changes in what's being forecast it I'm over here. They have a nice quarterly one. All it's ultimately doing is summing, you know, three months worth of information and you can go down there. So similar idea, you know, here are three months was really 80. It end up being 42 for 51 big difference There originally, 9 75 being five. So you get the point. They did the exact same thing for this. I think revenues is an important. I mean, all of these are important. But then cash is probably the first or second most important one, especially to, well, both the company and the investor. Um, as they say, cash is king. And from an investor's perspective, that's what you put in the company. You injected cash. So how did things turn out? So this is cash balance. So this will be their ending cash balance at the end of the period. They forecast it along there originally forecasting 97,000 at the end of the year. Well, what happened? They were in the whole 43,000. So investors are gonna make questions about that. Um, you know, tracking things so special. Let's just pick a middle month here. They originally forecast that to 75. Ended up with 2 77 Okay, so when looking at that would go well, good. I mean, they at least even though the revenues seem to be way off. Although it looks like the revenues for that month they weren't too far off, but nonetheless, they hit the cash pretty much on the head. But then you have a month like here where they forecast to 20 ended up with 1 62 So what? And pleasure to see a lot of variants in here in the forecast. And that's a very sort of odd numbers, if you will. But that's just the nature of things that go into the cash balance. Forecast would be lots of different expenses and things that change if you know your statement of cash flows. So ah, that's not completely I but what is It is just the variance 18 to 22 72 40 down, all the way down to 60 at one point. So that in April is that at that point, they thought in July that be done to 60. But they ended with 1 60 So a lot of you know, this brings light to a lot of questions that I hope you see the value there is an investor where you would someone be asking questions. And as it start up, you know, CEO or finance person, you know what is happening, what is going on here? So that said, that's a little summary of what one looks like. We're gonna start from scratch, will build out. You know, the framework of this, basically, is what we'll start with. That will start putting in numbers and logic and formulas, and we'll go from there and then we'll put in some sample data. We might not fully populated for all 12 months, although we might just to verify that the formulas are working and that's what we will be completing in the course. So with that, let's move on to the next lecture. 4. Waterfall Framework Video 1: Okay, let's begin. We have ah blank Excel workbook open. So I encourage you obviously to do the same. If you want to follow along and build, I build. Terrific. If you just want to watch and see what I do and then maybe build yours after, that's fine. I'm gonna provide the sample that I build as part of the downloadable content. So certainly you could just download it and and monkey with it. After after you watch the videos, you'll have a good understanding of what we've done. So But I also encourage people to use the skills and actually build as we go along, and you can obviously pause the video. So if I do something, you know, I'm just gonna keep going. So just hit, pause And, uh, you know, it gives you a chance to catch up and do what you do. So, first of all, you have already opened a blank workbook here. So go ahead and do that. I'm gonna save this from the start. Just good practice. So I'm gonna say this as water for report, you dummy sample. And will this hit safe? All right, so we have it saved. Just So you know I'm using Excel 2016. I mean, this would work in any version of Excel. It's well, Google docks. Honestly, if you're using Google Docks, I mean, they pretty much worked the same. I mean, when you get to some of the more advanced functions, there's differences. But for the purposes of this, other than some basic formulas and some basic formatting, it's fairly straightforward. So it's more understand that concept and what to do in the methodology. So, first of all, I know if you remember from the sample that we looked through and we might do a few things and then change it as we go alone, that's generally how I operate in Excel So But if you remembered, we had the months and then we have the months across, and then we have the cascading thing, and then a little break. And then the quarter summed up. So in general, let's just start with that. So first of all, let's give it a snappy title like Excise the Company Waterfall report. And just as I do this, so I use a lot of hot keys as well. So if you don't see me move up here to bold something. It's because I use things like control B for bold control, See for copy control V for paste control X for cutting something. So if you're not familiar with the hot keys, that's okay. I'll say something like, Let's bold this and you can certainly just go click bold if you're completely unfamiliar with Excel and even that seems a bit much, um, and encourage you to take a course on Excel. And there is one that if I have not created as of the time you're watching this, it will be shortly thereafter. It's one of the other courses I have on deck. So where I just teach people how to do the basics of Excel, So put the company name. I like bolding. So some of this is completely preferences. Well, I should mention is well, just so these videos don't get too long and I might haven't mentioned in the introduction, I'm gonna break them up. So we're not going to do one long video here and creating all the framework. I'm gonna break this into about 3 to 5 minutes sections, So this video is already coming up on three minutes So what I might do here. It's just go ahead, center my title and then break off the video and then we'll start in the next video with the next tests up. I like to merge in center things, so things are nice and centered. Obviously, at this point, we don't know how far across we're going to go. But I'm just that way. I might just stick it that fire and hit emergence center. There you go. If you're not familiar with that again, I'll once while throwing a few tips that just basically takes all those cells and makes it one big cell and then centers your text. I also like to make things bold. Big. So there we go. So we gotta Life's little header. Alright, so that's it. Let's cut off this video and then we'll start in the next one right here. 5. Waterfall Framework Video 2: okay, So picking up here. So let's go ahead and build some framework. And I remember it. If you remember correctly, it had a little color coded legend, if you will here. But let's put that in after. But I'm gonna leave a few spaces for that. Maybe these two spaces leave it blank and start here. So first of all, it had the months across. So we're just going to say, you can leave this blank or you could put in months again. I liked a bold I'm gonna hit the control. Be too bold It I like to leave Any time I do any type of financial report, I like to leave a little little space just in case I wanna mess with things. So I'm grabbing up here and just move if you click and then you can move the with of these so I'd like to leave a little empty column. Then let's just go across, um, January Fran and you can abbreviate. Certainly. Actually, let's abbreviate. If you're familiar with the fill function, you could use that here. I will. Let's let's use it really quick. I don't want to deviate too far from creating these. So actually, to use the field function, what we're gonna have to do is actually do these as a numerical date. So just starting here we do 701 2018. It doesn't really matter the day, cause the way we format this ultimately is just gonna be the name of the month. So there's a month and then what I want to do is put the next several months out here. So what I do is I highlight the first where it starts. Ah, highlight where I want the rest of the filling to go. So we want July, August, September, October, November, December we go to on your main home menu Go to fill. This is a Siri's. So we want a series of numbers. If you just choose right, it will just go one day at a time. So you want a Siri's and the Siri's we want brings up this handy menu. And again, I'm not spending a ton of time on each of these is not meant to be excel one on one. So but I want at least kind of popping a few tips, if you will, and you could go back and re watch these. So excuse me if I don't slow down too much for these, but we're gonna fill the rose. We're gonna go with the date. So it figured out already, just based on the format of the first cell, and it assumes a day we're gonna go with a month. So what it's gonna do is so when I was 7010809 and then these three, we could make each of these bigger. And there you go. 10 11 12. If you're not familiar with just another hint if you have a column on off, remember that had dollar signs You can highlight a multiple multiple rows at once. Using the very tops. You can adjust all them to be the same. If you grab one of them. It'll do all the same that are highlighted. Or conversely, you could just double click on the in between and it'll fix. It'll auto fit each of those. So I notice another tip, um, so that we have ah date dates in here. We wanted to look more like the words. I'm just gonna highlight all these if you go up here to date Let's see. We want a short date. I believe it. ISS Oops. That's not exactly what I wanted. More number formats. Here we go really wants I mean, these air dates. He could have, like, 3 14 it come March, March 12. I know that there's just march. Let's just go with this and see what looks like July 18. Not horrible. So nonetheless, at this point, let's do our fill. This isn't something I usually don't usually feel forward. You don't feel kind of backwards. So, um, you see, it's not giving me the option to fill a month backwards, so really quickly. Here, guys. I'm just gonna fill this in. I'm gonna do the exact same thing I did before, So I get to see it twice, and then maybe, let's see, we will cut this video off then and we'll we'll make a little bit faster that going forward . So there we go. So those air that and then you can also copy of formats. So if you like the way this format it rather and go find it on the menu for these, this is your four. Meant paid are So you click the cell. You want to copy the format of click it and then highlight the cells you want apply to Boom . And there we go. I like to center things. That's the last thing we'll do here. Let's just give a lot of center, All right? So let's I really would like to hit, say to So control s and you see, my little icon came up in spun the, um, just the same. I like to save along the way. You can also turn auto save on, depending on what version of excel you have. Unless let's cut this video off and then let's continue on after this. 6. Waterfall Framework Video 3: Okay, guys. So now we have our months across the top. Now we need the list. Are months down here? You can do the same thing. You know, in the example used. They spelled out the months here just to keep things consistent. I'm going to do the same for matting down as I do across. So I'm gonna I'm just gonna copy this one. And then you know what? I'm going to use our Phil again. I'm not sure how many cells I've I think I've done more than 12. But that's okay. I'll just fill it, and then I will deletes the ones that I don't need again. I'm gonna go a bit faster now. I don't want to bore you guys with the so Yeah, so we went a little bit too far, but let's just get rid of those. So there we go. So now we have the months across, and months now also actually made a mistake here. If you remember, we need to put in. This is gonna be Our budget line is right underneath. And if you remember, always like to leave a little space. That's more so on the roadside. First. Whatever reason, Just my own format and columns. I don't mind this much. So here. I'm gonna put the other example of called operating Plant. I was gonna call budget. Let's just call budget for now. I'm hesitant to even leave months, and here is just cluttering thing. Like we obviously know these air the months across and behind someone's gonna delete that when I will do is bold this. So we're gonna have our budget across for each month. It would probably have to make these cells a little bit wider. That's OK. We'll do that in a second. Now, here actually went down. So we will start off. Let's move this up. And you could even put, like, original budget just to help out people know. Okay, so we're gonna list are original budget across here, and then we're gonna have our actuals, and we're gonna can't quite do it that way. But I'm cascading down this way. So let's start with so this, you know, this section of lectures is just setting it up. So we're not going to start putting in numbers at this point. If we do with simply just to be a placeholder and just kind of remind us what it is, but we really just want to set things up this point. So we have our months down the side. We have our months across. Well, if you remember correctly, it had then the quarters. So let's go ahead. And maybe in this video, we'll also get these quarters set up, and then we will end this video and move onto the next one. So, um, obviously, you know, we're using 2018 as the example here. Let's just do q one 18 just to keep the formatting the same. And you know what? I'm just gonna copy across. So I hit control C. I highlight. If you don't know, highlight its shift in the Neuquen Arrow. I pasted control V. And then if you go to a cell again, you're not seeing my mouth smooth because I'm doing this all on the keyboard. If you go to a cell and you hit F to the F two key up on top just above your two and three keys, it will go to edit mode. And you see, here I have a cursor. So I just make this Q two in a row over F to it. Make this Q three and F two and Q four. Also like the center things I don't know. Short card for a short cut for center. I don't know if there is one. If there is, I just it's quick enough to click the button. There we go. So we have 1/4 set up across the top and we have each of the individual months over here. I think that's good. I don't know that we necessarily a total you wouldn't necessarily be digging in. Plus, is there really a need for a total? I mean, I could see one. Maybe we'll put one in. Let's put one in Just for fun. I will. I'm gonna make it its own again. I like to put in that space to kind of break up information that's not quite like from here going month to month given a break. Okay, these air the quarter is given a break was put total 2018. That will be our now the only thing that this does and you want to think about your users. Well, if this in for you by putting that in. So now we have a full sheet. We go right to the edge, share viewing it, and that's OK. But say we had another common. We want to include kind of bad practice, just enough. That's like hanging column or two. So we would. You either want to zoom out a little bit so we could see everything. I'm gonna tell you a second I did that or we would just, you know, think about the user. And do they really need that? So the zoo mountain in Hold your control key and just school. Use the scroll wheel on your mouse girl up to zoom in back. Teoh towards you, this girl out and let's just get to that original size. I think that was it. Okay, so that's it. Let's move on and we'll keep on going in the next video 7. Waterfall Framework Video 4: Okay, so let's keep going. This is going really quick. I hope you can see how you could probably build a waterfall report and easily in an hour. It's more than that. The information is what really takes time and thinking through what's the budget? And obviously you would have your budget done already and dropping that in and then each month, updating this, um, the four million, though not so bad. So we'll finish up the formatting. So now we have all recalls. We're gonna be doing the formulas in the next section, so we're not putting any formulas in right now. Let's put in some line breaks and things like that. So we you know this right now that's pretty pretty boring. And if you just put, start putting in numbers, you're not going to know what anything means. So if you remember, there was an original budget and it was all in green. Some first of all, let's make our little legend here will say, um, original budget. And then we'll put, um, month or actual results. Oops, about actual wrong. Okay, let's give each of these a color, something pleasing in the eyes. Let's do green for actual results because that sounds like a nice and I like to use just I don't like these. The heavy colors just go with the light. The light shading original budget. You know what? Be for blue. Be for budget. There we go. Simple. Is that so? We're gonna go across here. I'm gonna hit the blue, getting a skip, that space, the blue and really, we only enter original budget in one spot. If you remember from the example is, well, they didn't. So as you revised your budget each month, you didn't. Those weren't color coded. We don't want the circus is in town. We don't need everything color coded. I think it's good. Just pick out the original budget and the actual results, and then knowing how the waterfall report works, we know that the numbers that will be in the middle here are the kind of the revised budget month by months of, um, we're gonna add in some lines as well. But for now, let's just work on colors a little bit since there's not a ton of coloring and the other thing, So I'm just gonna go. I guess that I make that green. So we're just gonna cascade this so you'll notice like it'll be March March, etcetera. So another thing we can do a couple things you could do. Your format painter. The problem is, you can only do that one cell at a time. The other thing you could do is just go up here and use my handy control, See? And then I'm gonna arrow down and hit control V and you see that it's still up here has the dotted line that's moving, so it's still active. It's still hot so I can go down here. Hit control V Hit Control v etcetera. So let's just get all the months in here. There we go now for the quarters so your first quarter will be actually march. So your quarter one ends in March. Your quarter two ends in June. And if you ever have in trouble like, well, seeing across look over here. You notice this is one won't highlight while I'm right now, wrote 12. But when I go off, it's got a slightly darker grey, and that's corresponding with the cell that I'm in. So the 12 is darker and the Q is darker cause That's the cell that I have highlighted. So it helps you look across and see what's highlighted. And then here, this would be, um, September and then December and then totals for 2018 will just be a guy of something wrong here. Or do I know I wasn't asleep? OK, there we go. Perfect. It looked a little off to me, but not quite. Okay, Perfect. So now we have some four men again. So that's where we're gonna put our actual results each month again. You want a bold these for fun? Terrific. Um, typically bold headers and line items. I don't get too crazy on the bolding. And since there's not a lot going on here other like at this point, we have pretty much everything in in terms that that's not gonna be a number. So I'm not gonna worry too much about bolding. Um, let's put some sort of lines and framing on this, if you will. I don't want to frame it. I don't want to, like, outline each individual cell, but I kind of want to at least highlight bigger areas. And since this is kind of a main section, if you will this one. I am gonna put lines in between each self. So the way to do that. So I'm gonna do this for this one. I'm gonna end this video and then we'll pick up in the next one in the next video and kind of finish the framing of the, um, the cell. So just so you know, if you click up here and then we'll hit the ground running, you can go to your this box, which is all the different outlining of cells again. If you're not familiar with, you know, check it out. You can take the time and try different things. Um, if you ever highlights that you do something you don't like, you could hit the undo button up here for this case we wants. Where did it go? Here we go. All borders. So basically that's gonna list lineup, every single cell that I had every way. So now if we look at that, there we go. So I'm gonna hit my save button again. Control us in this video, and in the next video, we'll finish sort of framing up with the outlines 8. Waterfall Framework Video 5: Okay, so let's finish up with those outlines. So, you know, again, this is subjective. This is whatever you guys want to do. So where I'm just going to kind of go through, I'm gonna be doing stuff. I'll be talking all the while. So of course I want the same formatting over here. Um, just highlight groups. Highlight those cells. Hit the button. How? Like this cell hit the button. And again, there's different ways to do this. You could highlight. I'll show you what these, actually. Let's just do it over here. What's easy? They didn't want to do one by one. Like they don't want to click here. Go up here, Click here. What you can do is if you hold down the control key, so click the 1st 1 you want. Don't do anything else. Hold on your control key. And now, as you click other cells, it's elects multiple cells all at once. And if you go do something on those, now, change the color or box them in. There we go. You see a box of my land. So we're gonna do the same thing here. I'm holding down. Oops. So you gotta watch out where your original cursor words mine was up there. So we want to start here. I'm holding down my control button. I'm clicking. Probably hear it. So let's just get all of these, Okay? Perfect. Now it still looks a little empty, like there's just, you know, it just doesn't look quite right. So what I do want to do, it's kind of put a major sort of frame around this, and I like to use in this case, I'm just use the same sort of formatting of lines a lot times I'll use, like, the heavier line, maybe for the outline. In this case, let's just keep it simple. So you don't want to hit this because they will feel in each individual cell, and that's not really what I want right now. I just want a big outline around the whole box. So that is, um, outside bumps or outside borders. There we go. So if we click off now, you see we have this outside border. We're going to the same stuff over here, But let me finish with this one first, and then we can just kind of whipped through these two, so I also like to break, you know, headers and the side wrote columns off to their own thing. So let's do the same thing. Like, let's make this its own box. And let's make the top row its own, you know, sort of box here Now in the sample. I believe they had each sort of row. Let's actually take a look here. Let me see if I still have it open. Find it here. Yeah, if you noticed Here they they did each calm and I kind of like that. It breaks it up. It's a little bit easier on the eye. So that said, Let's just do that as well here. And once we do one of these weaken do all of them. So again, it's a simple ast the outside box, And we might even actually want to drag do it up to here. So let's just go here and then see, we can't use the format painter here because we have some different color shading. It would then apply the same stuff across. Unfortunately, on this one, we're just gonna have Teoh do this. I mean, we could have did this before we did the coloring. That's OK, It's one of those things. You do it, you realized you could have did it a different way. It's gonna take us all of about 20 seconds to do this. And then once we're done, we are. Let's see where About three minutes. Right now? Yeah, we'll do the same formatting on the other two sections over here. I guess the other thing you could do just for sake of teaching. So I know that I want to do this to all these on Rather's. I was in one by one because it really wouldn't take that long. But let's take just a minute to teach you a different thing without lining if you highlight a whole section so we know that we want the vertical lines in each of these. But that's about it. We just want the vertical in between lines. You can drop this down. You could do a couple things. Actually, that's pretty much what I think from here is only one main thing you could do if you go to a more border. So these are all kind of pre defined set ups, if you will, and they're gonna be 99% of the time the thing you want to go home or bored or so here it shows you Here's, you know, kind of are outlined. So this will be the outline of the sexually, and this would be indicate the middle breaks in between. So we want a vertical line in between as well. If you want the horizontal ones, we would click that. But we just want the vertical ones and you see, it's pre chosen. If you can see that's kind of outline, its pre chosen the formatting that's already used on the outside box. So it's assuming that's probably what we want to use, and it's correct. So if you click on this vertical line now, you see it solid, so it's gonna ply that vertical line to each of the the Rose are sort each of the columns in our highlighted area. If we wanted the rose, we would do it here. We don't so we click that Let's hit. Okay, and there we go. It's a simple is that's let's use some of those tricks here. So first of all, let's highlight this whole section. So let's think about this so we want the vertical lines we also want the outside box. So at this point, there was no outside box highlighted for this, so it's just kind of guessing at what we want. So let's just go ahead. But it did pick the line style. So what we really want is the outside box and the in betweens. And there you go. Simple is that same thing here. Although in this case there's really no in between boxes, it's really just the outside box. So, um, let's just use the more just to show you see, it doesn't even give you an option for a middle one because there is no middle one. And there we go. So hey, I like how this is looking. If you think about it, we're pretty much done with the formatting just like that. In about 1/2 an hour, we fully formed a waterfall report. The only other thing of formatting wise. You know, like I said, if you want a bold these, maybe let's just give that some bold Um, let's hit our save button really quick. So one thing, once we start putting numbers, we will probably need to change the formatting here. But let's wait. Let's just do this as if we weren't really, you know, maybe not thinking ahead, and we just did it as we were doing it. So, um, other things, I kind of want to put a box around here. Since this is kind of independent, um, she know what? I'm gonna pop out of there, and I'm just gonna give it outside borders. I'm not sure that I need this column now. You know, I have mentioned I always like it, but in this report, I don't know if it's necessary. I'm gonna get rid of this column. So this is kind of the again some of that subjective stuff we build it. Maybe we're gonna change something. I do believe we're gonna have to make these a bit bigger. The columns once we start putting numbers in again, let's figure that out once we do it, Maybe throw some boxes around this possibly, and I'm gonna use that. All borders. There we go. Guys. I like what I'm seeing. This is pretty much set up at this point, So let's call that it for formatting. And then maybe as we start monkeying with it and putting in formulas now, we will change a little bit of the formatting, but I think we're pretty much done. So in the next section we're gonna work on the formulas and the formulas are primarily. I mean, if you remember, this is all just data entry. The formulas will be over here, so it shouldn't be too long. Toe put in the formulas, but let's save that for the next section. 9. Waterfall Formulas Video 1: Okay, so now let's get to building some formula. So before I build the formulas just a few things to point out, it doesn't matter if this is for revenues. Cash operating senses. It works the same. Regardless, We're just building kind of the template, if you will. And then we can apply later on what the actual numbers are as well we saw in the example. You know, they had multiple. They did like revenues and operating senses and gross margin, etcetera. Once we get this one bill weakness, copy paste and use it over and over. In terms of formulas. There's no formulas in this section. These are all just sort of hard coded numbers. Where the formulas will be will be over here. So we're going to start here, will think through some and then once we get the I D ideology down would be fairly simple to copy. Paste a few things. So thinking of Q. Once cute, the quarters are obviously three months in our example. We're going with a calendar year where it's January December. Some companies start in other months. We won't get into that. There are reasons why they do that. Um nonetheless, in our example for some, for Q one, he would just want to start with Cuban Here, see your companies. Quarter one is July, which I've seen you would have July, August, September, October, November, December and in January. Continue wants up unless Q one will be these three months. So all we're gonna be using a some for some formulas. There's several ways to write these or summer ways put together. I'm gonna show you, I think, three ways. So let's go with a very simple way. So if you're in the cell, you could go the formula bar or going to start typing here so you can hit equals. And then basically, we want that formula be this cell plus the cell plus this self. So it's a simple as click that and then hit our plus sign and then click that south and hit our plus sign and then hit that cell and then hit enter. So if we go back up here so over my mouth there, right here, we can see our formula up here in the bar. If we click on it, it will bring it up here. Uh, if you hit up too. That's another one. That's the way to edit any cell. If you hit off to it goes to the editor F two. So be and it. Plus it shows us here and semen color coded B six c 66. Perfect. And guess what that works. If we enter the numbers 12 and three, we get the total over of six over here. So perfect. So we know it works. Let's just get rid of these. Another way of doing this formula just teach you a few different ways is some function. So I like that hard, right? Them. So you hit the equal sign and then you type the words some. And you see, it brings up a bunch of formulas that it knows. I'm just gonna keep typing those something hit some. I'm gonna bracket so and now it shows even example Some number one. Number two, number three. So the way I write these is some bracket I click the first cell. Now here you could do be six plus C six plus D six and then close the bracket and it would work. But what's the point? Like why? To bother using the some function if we're just going to do the exact same thing we did in a lot of format. So what I like to do here is if you use a colon and then you can specify a range, so there we go. So what I did Waas b six. Let's actually exit out here and do this again. So equal some bracket. Easiest way to do this is Then click and hold your click down and just highlight the cells you want to some and then let go and hit Enter and there we go. So now it's some B six colon de six. What that's telling it is some from B 62 d six and everything in between. Sure, for three cells, it's just as quick the type that Oops, don't move that. Undo that. Hang on. And it's just as usual type, you know, at this this and this. But see, you're adding a whole range of cells. That's some function is really handy when you could just hit some and then highlight a bunch of stuff and hit enter and it sums it all perfect now. So there you go. So that works. I'm gonna If you hit up to again? It highlights the block. Perfect. The third way of doing this and get this out here. We're not gonna do this for each cell is after this. Actually, after this, I assure you this we're going in this video, then we'll come back and fill in the rest. There's the function of there's the formulas menu or there was shortcuts. I don't know if you noticed on the home and you over here, auto, some auto sums Not really gonna work for you because it's gonna try to pick stuff. So let's go to insert function. And now again, we're not going to get into each of these. There's financial. Let's just go with insert function. It has some already kind of picked, I think cause I just used it. You could click on some and hit OK, and basically it will help you build your formula some. It's gonna ask you, what do you want to some? So from here, you can click in here or say this is covering it like, say, this box was here and we couldn't get to what we want. You click on this and it'll kind of condense that and you can click that and then down. And then number two. You can go here to this and it brings up number three. Let's do this and we're done. Those were the only three We want it. We hit OK, And what did it do? So it wrote it out slightly different. Some hoops. I moved off it. Some B six coming C six. Coming d six. It would be the same thing as you know, some as equals B six plus six plus D six. Etcetera. Um, I personally I'm a fan of just hard typing. Like Once you get good and excel, just hit equals some. I'll show you a quick it ISS highlight those three hit enter and off we go. It just becomes almost like a robot. So I said, Let's end this video And then let's fill in the rest In the next video 10. Waterfall Formulas Video 2: Okay, so let's do the rest of these quarters here and fill this in. So cute, too. I mean, that's teamwork the exact same way it's going to be April, May June. So I'm just gonna I'm gonna do the way I like to do, which is to type it out, go over. Highlight hit. Enter Well, some bracket. July, August, September and then, lastly, equal some groups. October, November, December There we go. So we have recorders done now. One interesting thing here, So let's just think about this. Let's do Quarter one. First of all, that's your budget, but it's fairly straightforward. Goes straight across the top. So now let's think about each of these. So for quarter one in the month of January, so your month of January includes thes three, so it's fairly straightforward for that's one. Let's just put this in. I think where you'll see where it gets a little have to wrap your head around. It will be the upcoming months. Okay, so there you go. So now firm for February. So we're on the February line over here. So in the month of February for Q one, So what's this number gonna be made up of. So if you think about it for our budget, it just was the three months budget for January. It was the actual January plus the two revised months now for crew one at the end of February. What do we want to reflect? Well, we want to reflect the original Earth story, the actual January plus the actual February plus the forecast in March. So you always want whatever actual months have actually happened, plus any forward looking months that haven't happened. So we're looking Q one, which means only January, February, March. But January February of happens. We want the actual we want the actual and we're gonna want the forecast it remember as of February, there is no there is no March actual yet, so hopefully that makes sense. So let's let's just give us a try. So here I am, going to start using just sell plus El Plus Cell because we're kind of moving around. So we want the actual January plus the actual February plus the forecast in March and then I'm going to do the same here. So for at the end of Q one, remember actual results. So if you think about it? This should make sense here. We want the actual January, the actual February in the actual march. So I'm just gonna put some numbers in here and see if this helps you out. So we're gonna use low numbers, say, 10 10 and 10 now say, the actual January was eight and we forecasted still 10 and 10. Actual results were, let's just say six and we forecasted eight here. And then the results came in and 12 that a great month. So let's look at this quarter by a month by month through the corner. So the regional forecast was 30. That makes sense. That's what we forecast it at the end of January. Our waterfall reports that 28. So what does that mean? Well, we had eight actual, and we're still forecasting 10 and 10. There's your 28 at the end of February 22. So what's that made up of? What's made up? We had eight actual here who had six actual here. So that is 14 plus. We're still forecasting eight. Now, remember, at the end of February, we don't know what this number is. This is a blank. We'll have numbers all across, but nothing in here. I might have done you a slight disservice. And I showed you kind of a finished waterfall report when we looked at the example. But when you're kind of mid year, you would only really have numbers. You might have your forecast detailed out, but you're not gonna have numbers for, say, June in April. So it's going to be a lot of empty spots so nonetheless. So think about this. This 26 is actual, actual actual. The 22 is going to be the actual for January for February, and then our forecast in March and then at the end of March, we get the actual So hopefully that makes sense. It makes for a little bit of an interesting now kind of building this formula. So now that is gonna be it for this so much like how the space under here is empty or space under here is empty. This whole area, it's the same over here. This whole section's gonna be empty, could great out. There's no real point. Um, anyone familiar with the report would know how it looks. So that said, the CEO is going to be four minutes. I'm gonna cut it off, and then we're gonna fill in the rest of these 11. Waterfall Formulas Video 3: Okay, so hopefully that all made sense for you. Let's do let's do these now. I'm just gonna leave those numbers here temporarily, in case we need to do another example. So So let's think about this. This is Quarter two in January. Well, as of the month of January, all we would have is, and I know we're starting to fill in some numbers here, but mainly, for example, let's just say our forecast was always 10 across. And then January happened. It was eight, but it looks like we're still forecasting 10. So at the end of January are our report would probably look something like this. You only have numbers across for the actual months that were in, so January is done. Here's our numbers. So for Q two in the month of January, well, remember, Q two is these three months, and we're looking at January, so it's just going to some of those three. So for here we can just do some April May June. Likewise, while Radic Q three is just gonna be July, August September, let's put that in. Feel free to ask many questions. If you are slightly confused by this, I know this is kind of getting a bit more in tow more than just, Ah, the the Met. The formulas air simple. What's confusing is the logic may be behind it, but hopefully you're you're with me. I think it's one of those things that want to understand. It makes complete sense. So okay, so Q two in the month of February. Well, let's think about this. So now February happens. So let's just say I feel I think we had six and we re forecast out eight. But then let's say we still think things will pick up and it'll be 10 across. So this is how it might look after the month of February. So let's think about this for Q two in the month of February. It is just gonna be some still of April May June, so the actuals aren't impacting us. In Q two, the actuals impact the quarter in which they're included. So once we get down here, they will impact it. So for now, we just want a from a June and likewise here. Remember how we said we want it? Um, we just calm copied over. So here we had. Let's use our copy paste to make this go maybe a bit quicker. So here it waas copping these three. I shouldn't say competition, say, adding, Here is these three and here it's a similar. We could actually just copy this formula down. Let's see what happens now if we hit off to Hey, look at that July August, September for February. Perfect. Same thing here. A copy of that Perfect. And in theory, we could do that all the way down to the point where the quarter starts. But let's just do it at the time we get there. For now, though, this is here now, say March happens, Let's just put in the number. And then we said 12 and then let's say we're gonna forecast 12 out, and it goes across. Things have gotten better. So for a Q two in March, it will still just be those three months. Remember the actuals of March? Don't impact Q to Q two is still just adding up a forecast so and likewise. Here we go. Oops, gonna undo that. There we go. We can copy down to formula now, so let's think about this April May June perfect. So when it gets to April. We want these three sells. Well, that still works for us. We can just do a formula now. We didn't put numbers in. I'm gonna skip putting numbers that indicated the point here. We just want to move down. So I'm gonna copy Paste those. Now, here's Ricketts. It's kind of those last two months in the corner. You need to get the tricky part. Cohen here. We want him my equal sign. So it's April. May, June. So I want to actually approach. We want the forecasted Let's see here on the right. Yep. Okay. So we want the actual April, which we didn't enter number. We want the actual May. And then we want the forecast in June. So much like it. So this cell is very much like this one. So we hit up to rotate the actual the actual and then the forecast for last month. Here we took the actual the actual in the forecast. It's gonna spell the same idea, and then here, this will be actual, actual actual. So it's gonna be April. It's gonna be may. And it's gonna be Jew. All right. So without putting, I'm not gonna put in numbers that I hope you get the point of this at this point. So again, another four minute video. So now that we kind of hopefully have the points, we're gonna fill the rest of these in. I'm gonna do that in the next video, and then we'll worry about that. Total is well. 12. Waterfall Formulas Video 4: Okay, so let's handle this Q three now. So again, I'm not gonna put in numbers. Hopefully that ideas work. So just quickly, thinking about what's Q three. It's July, August, September in the month of May. We're only looking at forecasts in numbers. It's not till later in the year that we care about the actual. So let's copy these down. Okay, so let's just at any point and go to a cell hit F two. Just make sure things look right. So let's see. In the month of June, the Fork Q three would still be the forecast for these. Now I'm gonna copy, please. One more down. So in the month of July, it will be the actual July plus the next two months. Forecasts of copying worked here. Remember, it's the cell above the green where it gets tricky because we want the actual July. We want the actual August. Since we're in the month of you know what, I moved down one life. I apologize. So we're in the month of August. So we have the actual July, the actual August on the forecast that September and then 4 September. We want the actual July the actual August and actual September. Let's just hit. So if you remember up here, I'm gonna goto February It was actual actual forecasts. And then for the green sail with actual actual actual If you look over to the left same thing here for Q three. It waas actual actual forecast and then actual actual actual for June. Same thing here, actual actual forecast and then actual actual actual. Now we're going to the same thing here. So let's just copy this all the way down here and let's just double check this bottom one that looks right. We get the actual October in the next two forecasts. Then for this one, we want October's plus November's plus December sore cast. And like the other 3/4 we looked at the final month of the quarter, we want the actual actual actual. So there you go. Without putting a bunch of numbers again, we're gonna get more numbers and presentation in the future that works for the quarters. Now, for the total, I'm gonna cut this off here. We're going to talk about the total in the next video just cause it's kind of its own creature, if you will. So it had stopped, and then we'll pick this up in the final video for the Formula Section 13. Waterfall Formulas Video 5: so the total. At any given point, it's much like the quarters. So if you're in January, it's gonna be the actual January, plus the forecast for the rest of the year when you're in February. It's the actual January, the actual February and the forecast for the rest of the year and likewise. So say October, it's gonna be the actuals for each of these down to October and then the forecast for the final two months. So you kind of get the overall picture and, of course, doesn't get closer to the end of the year. It's gonna match much by the end. It's just your actuals for all the periods. So, um, we can go. So here is actually just gonna be the entire across groups. And it looks like the formatting. It s so let's just copy, go to our home format, paint that and you know what? I'm actually just gonna do the format paint for the whole thing, just so it doesn't bug us anymore. And for some reason oh, I must have hit the round foreigner. That's why it's maybe goofed up. I missed that when I was a might have looked away There we go. That looks more reasonable. Okay, so that's adding the whole thing. So now, months by months, we have take the actual plus the forecast, and then it's good. So by the end, it's gonna be kind of a pain in the butt of think about. It's gonna be the actuals for each of these. Plus that so But unfortunately, that's just the nature of the beast. So let's just go through and do them. Um, so this one, actually the very 1st 1 is just the sum across because and you can move your cursor. It's just January, actually. Plus all that, the next one will be that plus, and I'm gonna show you another formula to make this go a little bit quicker. So it's the January actual plus the February actual, plus the forecast for the rest of the year. So we want these seven plus this whole section. So rather than type it all out, we're gonna hit B seven plus. Now we can do a some formula and the bracket and highlight what we want. And I'm gonna show you guys this in a second here After I hit, Enter hoops, Close the bracket for me. Okay, so we're adding B seven, which is this plus the sum which allows us to some a whole array of cells. This is where the some formula really comes in handy when you're summing up a bunch of stuff. So now here, we could do. Plus, unfortunately, we have to do this. Plus this, plus some bracket, all of these, and I'm gonna hit enter. It's gonna prompt me just cause I didn't close the bracket and it will fix it for me. So that's basically the methodologies. Just always remember, you want the actuals up to the point and the across. So, uh, for here, it's gonna be this and this a bit monotonous to watch. So I'm just gonna do maybe one more. Plus, these are our thing. So yeah, So you go down the line, and by the end here, obviously it's this. Plus this. Plus this, etcetera. So I'm not gonna continue those just cause I don't want to bore you with typing formulas, you know? Let's just, um, you know, all those highlights. So here you would do. Let's see. So it would be actually this this this this this this this and then across, you know? So that is that when we come back in the next section, you'll see those formulas field in hopeful. That makes sense again. If not, I'm providing you this template so you can certainly use it as an example, and I'll have the numbers cleared out as well. But hopefully that makes sense that you can see what's happening there. Always just adding the actuals down to the month that were in and then plus the month that we're in, then the forecast forward. So cause there's gonna be no numbers here. As of say, March, let's just delete this one for fun as of March. So end of March, you know, you're in accounting and you're doing your report. This is how this report's gonna look. You're not gonna have any numbers down here, so it doesn't make sense. Toe have any summing up anything down here you're just summing the actuals. And then across that said, Let's end this video and then we'll jump ahead to the next section 14. Waterfall Report Vetting Video 1: All right, guys. We've made it to the last section before we conclude. And here. So I I mentioned last video, the last section I'd fill in the formulas. And so we have numbers here. Now, Now, you notice this is all 26. It's because I didn't touching in the data here, So this section was really about entering data and vetting things. So I want to delete some of this. We're going to use some different data, so you'll notice. I mean, I have, like, a bigger area that I need to delete that everything zeros out. So first thing I know is that this is all gonna be numbers. And let's just use small numbers, like, $100 etcetera. And let's just pretend it's revenues. So I'm gonna throw revenues in here. All right, so let's just say our budget waas 101 100 now already. I can see it's not for my I like it depends on how you want it to look. I prefer myself the dollar sign, but we don't need the sense so you can hit dollar and then back out two cents that works. There's other formatting as well. I tend to use that one just cause it's quick and it's here. You can do everything with the buttons. So let's say Let's be realistic. So first of all, I want that formatting across for all of these. I'm gonna use my format painter. I'm only gonna do these because it's got the blue shading as well, and I don't want to shave everything blue, but we know ultimately we want everything to be the dollar signs. So you know what? I did goof something up. Now the line went missing. Rather than try to un back it out. It's quicker just to let's see, we can throw in the full. There we go. So we want all these to be dollar signs, and we want it over here to Let's just highlight all this and let's do this and then back out to There were perfect. So all these air format it, these air coming up with actual zeros or dashes because their formulas where's and here there's just nothing. If we enter 20 little shop is a dash, so let's just pretend, let's like, you know, 100. But let's say Q two, we think sales will be 100 20 a month and 1 50 I'm just quickly hand typing these. I mean, these come off your actual budget and let's say things really ramp up on 200 just for our sake. So we're gonna be entering data here and we're checking out a few things. One is this formatting. But do we want to make sure things are adding up over here So right away, I see a problem here because these numbers look reasonable for 1/4 and that looks like a year total. But this is not the right formula. So let's hit up to Ah, look, I messed that up, and I probably messed that up when I did the last video and you guys might have noticed inside. Chris, what are you doing? So that's why we do this. This is why we've ended. So and I actually didn't do that on purpose. I actually did mess that up. So let's fix this formula. It's a sequel. Some and then go across three and there we go. Now it's more reasonable number if you want to check the totals like that's 300. Yep, that 300 that says 3 60 Yep, that's 3 60 And another tip, if you will. If you highlight some numbers, you can look down here at the bottom. It tells you the average and you could actually change what that shows the count. How many are counting and the some 3 60 that I It's up to that. You can click on these, Let me see. Yeah, you can click on these and actually change what else is shown here. But I knew some basic stuff. That's fine. Same thing here. So you could highlight the 31 fifties for 50. Sure enough. And three times 200 is offices. Six. The 17 might be when you want to check. So let's just check that down here. 17 10 17 10 And as well, the 4/4 should add up. 17 10 17 10 17 10. You know you could easily. Since this was adding up over here, you could have mess one of these up like, say, here. I had stunned like these three instead of the proper three, I might not have noticed it, cause the numbers still would have been, you know, a bigger number and I might not have just I might have just over looked at where I would have caught it is when I tried to compare the 4/4 total to this quarter. So there we go. I'm happy with those formulas. We gotta formatting, Let's go on. So let's say January happens and we sell, uh, bad months. We only sell 90 were down 10% but we think things will be OK. So we're going to continue with 101 101. 20. Um, say it's gonna be the same across another tip for you. You can highlight cells now. We can't just copy paste them, cause gonna copy the coloring as well. But we can do is hit. Copy which control, See? And then when you pay something. So if I go here and it'll pace the whole range, you could right click and have paced options. So that's just paste as it is, that's pays values, which is what we want. There's also pays formulas, and then there's some other ones to actually very seldom use those. So we just want the values. We don't want the formatting. We just want the values were using the same numbers and there you go hoops Somehow I goofed that up. Let me try that again. I'm curious why that didn't work. I'm gonna delete this out. All right, so let's say, Well, to start here, we want to copy these and we want to pace just the values. There we go. I must have slipped over. All right? Perfect. So again, we can look here. So let's take a look. Now we now extend to get some stuff happening. So things make sense. Are, actually month was $10 less. So if you think about it, it's just starting on the right. That makes sense now that our total is $10 less. The forecast was exactly the same. But our actual with $10 less as well. Our quarters would be the same and the actual is $10 less. Um, I see. Okay, so now these numbers are not going to make sense. Anything below this line won't make sense. See, there's a bunch of nineties. There's 90 because it's adding this plus this. Plus this, which is correct. But there's nothing. There's no values that so you have a choice. You could just not include these formulas until the month comes although I would say depending on the youth, just ignore them. You could gray them out if you were sending this to someone or like I said, just delete these formulas and then just put them in month by month, I'm more a fan of just building the model, having all the formulas in there. And hopefully people understand that you're just looking at the current month and this is just a function of the way it's laid out. So everything looks good. We're not going to every single one just cause you give you this view, the idea. We're up to six minutes already. Let's just do one more and then I'm going to call it quits. Actually, you know what I'm gonna do? I'm gonna go ahead and fill this in, and then in the next video, then we can take a look, and I'm gonna be doing is putting a number. So you can You don't need to watch me put in numbers. I'll put them in, and then I'll come back in the next video and will quickly vet the entire thing 15. Waterfall Report Vetting Video 2: All right. So let's take a look at what we've done. All this explain kind of pretend theory, You know, our pretend company. What happened? Little kind of roughly looked through. So originally we were thinking a little bit of constant growth, right? 100 and 1 2150 and 200 per month in each of those quarters. Well, our first month was a bit of a bummer. Our second month bounced back a bit. Third months, we had a terrific months. So what we do? Let's look at the abnormal months. So originally we were forecasting we kept the forecast pretty similar the 1st 3 months. Then we had a blockbuster months. We jumped up the sales a little bit and even down here, but not too aggressive. Then the fourth month, 130. All right, so now we we just left it the same. Then we dive down to 100. So what do we do? So you look at the previous month. You know, we had originally forecasted 1 40 for that month. We only did 100 so we were not originally. But in April, we were forecasting 1 50 Now we're changing our forecast of 1 20 because they're month was lower. We're forecasting less. So this gets more into the theory of forecasting what? Not which. Here we're just sort of building a presenting models. Don't get too into it, But I kind of wanted to show why these numbers might change and kind of be all over. Um, same thing here is that we had a bumper month and we jumped up. We doubled from here to here, so that impacts their budget. So originally here we were bad months. We just kept a pretty conservative like Omei were only get, like, $100 a month company. Then we double our sales here. We're at 1 60 Okay, You know what? Now we're good. That was just an abnormal month. We jump up to 200 they actually hit 200 so they leave their forecast the same then oh, things dipped down again 1 50 But they've at this point learned their lesson that things tend to bounce around a bit. So they dropped their forecasts, but not horribly lower. Actual month comes in at 1 40 Now it's like, OK, well, that's a bit tough. So they lower their forecast again, you know they go from 200 to 1 82 50 to 2. November comes long holiday season. Sales are terrific. At this point. They were only forecasting 1 80 there, jumping up 2 to 70 and they're thinking 300 for December and December comes in 300. So that's kind of the story that I've told with my example here. So I don't want I see Let's see here. So let's quickly look at these numbers, and one thing stands out to me right away, and hopefully it does to you, too. It's the cell right here. So four fifties. These look like they make sense for 2300 Yuri forecasting. Okay, and then all of a sudden, 13. So something's wrong. Here is Let's take a look at what happened. Let's see. Yep, It looks like we grab the entire That's exactly what we did. So we grab six months worth of data, not just three. So we just need to fix that. And that's why we do this vetting process to make sure that things were right. We wouldn't want toe. They were building this for a client and what they would actually get to that month until whatever month that was. That was like July. So they get to July and and see some crazy number over here. So that's why we get from a test this stuff. Now, you could certainly do what we did in the previous video. Go through an ad each. You know, each one up, like this should be those three. And you go through and just hit up to and make sure things look good, etcetera. Your four actuals here should equal this number as well. So the equal 18. 80. That's 18 80. Perfect. If you want to, you can go click each of these. That should equal 18. 80. What else can I tell you? Just reading this report now as well. So the things kind of makes sense. Quarters, all right? I mean, it could go through and out of the quarters. Just we're not going to each one, but 3 50 like that. Just makes sure 7 10 you know, you see a big number. Alright. 7 10 7 10 Perfect. We know those add up to that, so I'm feeling pretty good about the mats here. Nothing stands out is crazy and wild. I'm here. You know, we have 7 57 50 and in some house down to 5 20 But if remember a story, it went from 200 to 1 50 to 1 40 So we did. We started dropping here. We were forecasting, you know, to 50 to 50 and it dropped down to 1 82 100 So it makes sense. Her numbers are dropping off over here, especially with that 1 40 So there you go. And really quickly. Another sort of analytical thing. If you want just looking at this, you can read this from a user's perspective. So, you know, they at the end of their they look at this thing, Okay? In January or like last December, we thought we'd do 200. November went up doing to 70. That's terrific. But along the way, we bounced between 200 to 2 22 1 80 we ultimately end up with 2 70 This month's probably most wild. They forecasted 200. They only got 1 40 At one point, they had forecasted to 50 not too long ago. So they look at things like this and this is where someone the user would use this and be ableto hopefully extrapolated. Learn from this and say OK, you know what? Sales are a little bit more rough ride than we originally forecast. Maybe we need to put more thought process into that. So I might be how someone would use this report. But for now, the formulas checked out. Like what we see. Let's move on to the next video. 16. Course Conclusion: all right. Now that you've made it through the course, let's just go through some final points and a conclusion. So hope. First of all, I hope we met all your goals. So our goals, if you remember when we set out, were to give you the tools to be able to build on investor quality waterfall report. And I hope you feel you can now that we've watched the videos where we essentially went through it and built one from scratch. I also want to give you some additional Excel tips. Tricks. Shortcuts gets useful. Some of those things are real time savers, you know, sometimes people know them. Sometimes they don't, and it's good to learn from each other. So especially a lot less hot key shortcuts and whatnot. So as you can see in Excel, there's lots of different ways to do like one thing. So I also want to give you a tool that you can use, which is useful in wealth owned by investors, managers, CEO, CFO's and others. Of course as well. So as I mentioned, you might be able to go into a situation. Maybe you're consulting CFO, go into a new start up and say, Hey, we don't have a great way to track changes to our budget in our forecast over time, relative to our performance, that's exactly what the waterfall report does. You can implement something. You can see how quickly it really is to build something like that. It's a useful tool that does not take a lot of time. So I hope that along the way you got to ends. Ask any questions. I provide the answers. Um, at this point, you've always to taking the course. But if you do go back and you need to review something, feel free to shoot me a message through the site and I will be happy. An answer And then, lastly, a couple points. Be sure to check out my other courses if they'll be of help. It's worth looking. After this video, I provide the bonus code where you can get any of my other courses for 10 99. So that's terrific. And I mean, I have things like building a business plan, building a financial model, accounting and finance, advanced accounting and finance for start ups and a bunch of others so even have a free course which obviously don't need the coupon code for him. But it's, ah, how to start an online business. So I'm trying to put up as much content out there. Is that can I love teaching you guys online? That said, I welcome your review as well. Of the course, I encourage you to leave your review. It helps me. It helps the website as well. Uh, look forward to that bonus code. And then it's Well, I'm gonna provide the template to download just so you have mine if you need to go back and maybe even look at one of those formulas. You know, while we're doing the course, I'm gonna provide it. Here is the completed template that said, thank you, everybody for watching and have really enjoyed teaching you all about waterfall reports.