Business 101: Tracking Your Company Sales and Goals In Excel | Chris Benjamin | Skillshare

Business 101: Tracking Your Company Sales and Goals In Excel

Chris Benjamin, Instructor, MBA and CFO

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
15 Lessons (1h 24m)
    • 1. 089SALES Section 01a Course Introduction

      1:38
    • 2. 089SALES Section 01b Instructor Introduction

      0:54
    • 3. 999EXCEL Section 01d Excel Basics

      11:30
    • 4. 089SALES Section 02a Excel Tabs and Labels Start

      6:22
    • 5. 089SALES Section 02b Forecast and YTD Formulas

      7:22
    • 6. 089SALES Section 02c Avg Formula and Totals

      4:33
    • 7. 089SALES Section 02d Formatting and Graph

      6:10
    • 8. 089SALES Section 02e Year 2 Setup

      5:31
    • 9. 089SALES Section 02f Year 3 Setup

      7:09
    • 10. 089SALES Section 03a Year 1 Specific Sales Data

      6:20
    • 11. 089SALES Section 03b Year 1 Finalize

      8:06
    • 12. 089SALES Section 03c Year 2 and 3 Creation

      5:02
    • 13. 089SALES Section 04a Quarterly Tracking

      8:29
    • 14. 089SALES Section 04b Wrap Up of Model

      3:19
    • 15. 089SALES Section 05a Course Conclusion

      1:13

About This Class

Are You Running a Small Business?

Have You Neglected The Financial Reporting Side Of Your Business?

Does The Idea Of Accounting and Financial Reports Not Interest You At All?

Do You Recognize The Need To Have Insight Into How Your Company Is Performing?

Do You Want To Create a Focused Growth and Goals Model?

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 30 day 100% money back guarantee if for some reason you don't enjoy the course!

  5. Bonus Reason: I provide the template we build in this course for you to download at the end!

Recent Review:

John P. says "I run a business, but am horrible at the finance and accounting side.  This course really helped me develop a way to track sales and profits without getting a degree in accounting.  Simple to use and implement, yet so powerful! Really a must take for anyone in business."

Why You Should Take This Course:

As an entrepreneur, you have to wear many hats.  One which often gets ignored is the finance and sales analysis side of your business.  By spending a little bit of time each week by setting goals and tracking your results, you'll have far greater insight into what works and what doesn't, and how to proceed.  You will also be documenting a history of your sales for you to refer to in the future.   By setting your monthly sales goals and tracking your actual results, you will have that insight. 

In this course I teach you how to build a relatively simple but VERY effective sales goal setting and actual results tracking tool in Excel (or Google Sheets).   Take your business to the next level.

What We Learn In The Course:

  • Learn basic Excel concepts for anyone not familiar with the program

  • Learn how to properly set sales goals and what factors should be considered

  • Learn how to effectively track your actual sales in Excel, and compare them to the forecast

  • Track your results in detail, as well as use this info to create monthly, quarterly and annual comparative results

  • Graph your results for visual representation of how you are performing

  • Make intelligent business decisions

  • And more! By the end of the course you'll be ready to take your business to the next level.

At any point if you have a question, please feel free to ask through the course forum, I'd be happy to answer any and all questions.  

***JOIN NOW AND LEARN HOW TO TRACK YOUR COMPANY SALES AND GOALS IN EXCEL! ***

About The Instructor

Chris Benjamin, MBA & CFO is a seasoned professional with over 20 years experience in accounting, finance, financial reporting, small business and accounts receivable.  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. I look forward to being your instructor for this course and many more!

Chris Benjamin, Instructor, CFO & MBA

Transcripts

1. 089SALES Section 01a Course Introduction: Hi, everybody. Welcome to the course. Thanks so much for signing up in this introductory video. I wanted to mainly just welcome you to the course and give you a little bit idea of how the course is going to go. So essentially, once we get past the introductions and there's a few introductory videos, I give you a little bit more background on myself. I give you a little basic introduction to Microsoft Excel. Then from there we're gonna start with a full on screen cost, and you're gonna follow a dizzy while we build this template to track your actual sales are projected sales on Bring it all together in a terrific dashboard with graphs and analytics . So you will be following along with my screen. You're welcome to build your own as you're watching the course, and I encourage you to just take positive videos, get caught up, apply what you've learned and tailor it to make it your own auras. Well, you're welcome to follow along and then use the template that I build. I provided for download at the end of the course so you could do that route as well through the course. If you have any questions? I highly encourage you to send me a message. I love taking your questions, and I'm here to help you. I want this to be interactive. The course is broken up. Each of the videos is about 5 to 7 minutes. So if you watch one through, you could then be a natural stopping point kind of catch up if you are building your own other and that encourage you to just, uh, you know, be engaged, really learn what it is that we're doing. It's not just about the mechanics of building this tool. It's what I really want you to do with it. I really want you to use the knowledge I provided well, to forecast your sales and think about your business and really growing your business. That's ultimately the goal here. I'm glad again you signed up for the course. Let's go ahead and move on to a little bit of an introduction to me and then into the course material 2. 089SALES Section 01b Instructor Introduction: Welcome back, everybody. So quick. Introduction to myself before we get started again. My name is Chris Benjamin. I've been in accounting and finance for over 20 years now. I've been in consulting for over 10. I came up through the accounting Brinks. I got a master's in business is well worked my way up to the CFO level. The last 10 years, I've worked primarily as a consulting CFO. I build a lot of things in Excel, so I have both the sort of CFO and analytical accounting side to me as well as the Excel expertise. So that's why courses like these I really enjoyed teaching cause I get to bring it all together and help entrepreneurs with their business and teach some really valuable, useful tools that they can use. So that said, that's a little bit about my background. As I mentioned before, you have any questions there in the course? Definitely. Send me a message. Happy to answer, Let's get on with course 3. 999EXCEL Section 01d Excel Basics: All right, everybody. So before we get started on the course, I want to give you an introduction to excel. If you're familiar with Microsoft Excel, you understand the different cells have input? Um, maybe had do formulas and, most important, here, familiar with this home ribbon, You know, the different formatting, etcetera. You can probably go ahead and skip this video, and then anything beyond what we cover in this video. If we do something during the course, I'll actually call it out and explain it at that point. So this is really geared towards people who, you know, maybe, you know, open an Excel workbook before but really don't understand, You know, all sort of the functionality in here. So if you are still with me, let's go ahead and it started. Welcome to Microsoft Excel. So first of all excels. A spreadsheet program is by Microsoft, operates very much similar to Google sheets. Eso If you use Google sheets, most of this will apply as well. You might just have to go find, uh, things in different locations. Uh, so each of these is considered a cell in the space of where we're gonna be entering data whether it's numbers, letters or formulas. Each cell has a cross reference, so you'll notice all the columns have a letter, and all the rows have a number. So any given cell has a cross reference. For example, this one is I 10 and you can tell that because it's slightly shaded and slightly shaded. Or you can also just look right here and see what is eso. As we click anywhere, you'll see that changes, uh, Microsoft Excel. So So these air considered workbooks, and they very much are like a book and that there's different sheets. So I have my default set up to when I create a new workbook. It just opens up with one sheet. Yours might be different. It might have had multiple sheets already, regardless of whether it did or not. You can add sheets by clicking the plus sign. So you know, if we're using multiple sheets in our course, you know we will need a few different sheets. Eso, anything we do on one sheet won't impact the other. So these really are like pages in a book. So this is like paid one page two, page three. It's a trap so most of functionally we're gonna be using again. It's on this home tab excels very powerful tool. You can do quite a bit with it, but for the purpose of this course, we really don't need to get to two involved. I just want to show you how to do basic sort of data entry, um, and some formatting and moving things around, etcetera. So the way we're going to start this is just going to create a short little sort of table, if you will. Let's assume it's a table of test scores. So I'm gonna write out some names. So, Chris Bob Oops. Keep typing around. Um, Julie and Mark. And then maybe we have test one Test two and test three. Now, the way moving around is on my keyboard. I'm just using the arrow keys. And simple is that whenever you're in a cell to start typing and it will enter, you'll see that whatever we type here is also up here. This is held the formula bar. So later on, when we actually create formulas, what you'll see here is you'll to see the result of the formula. You'll be able to actually see the formula itself up here. So, for example, if we typed, you know, equals four plus seven, it would show 11 in here, but it would actually show the plus four plus seven up here. All right, so for our test scores, let's go and put in some just dummy data. I'm just trying toe populate some information. Ah, just so we have something to work with a little bit unrealistic. So, um Well, God. So we have a nice little table here, so this would be one good use of excel. You know, maybe your teacher, and you want to keep track of test scores? Well, you know, trying to then apply what we're doing right now to what we're gonna be doing in the course . No, we typically want to form at this. So just presents a bit better. It's a little bit easier to look at it a little bit easier to understand. Um, so first things I want to do is kind of take care of this hetero, um, so we're gonna go across and use all the things here, but not necessarily in order, because something that makes sense to do later on. So, first things first to highlight, so anything we do. So if we're highlighting a cell and then we go and do something up here like we apply a bold or color, it will only do it to the cell That's highlighted. Now we can highlight multiple cells, and the way we do that is if you click on the first cell and then hold down your left click and now move your mouth, you can highlight and I'm just scrolling around. I saw my left click press down so it could scroll around Someone's gonna scroll to hear Let go And then those are all highlights now, anything we do up here will apply to all of these cells. Conversely, another way to do it is on your keyboard. If you hold down the shift key and then move your arrows around, that will also highlight. So this whole shift and when you're done, you just let go. So let's go up here, gonna hold down shift and there we go. Okay, guys. So I like the typically bold my header. So let's go whole head and play some bold. So this very first part here I should say, second part because we'll get to this. Um, is you can do bolding. Basically, it's the font itself so we could change the font style. Any time you see a drop down box like a little arrow, that means you can drop it down. You have multiple choices. Typically, I don't change the funds. The one thing we might do is change the font size. So maybe we make headers a little bit bigger, so they will make that a 12 just to make it stand out. You could use these as well, which is increased font size and decrease font size. And the other thing you'll notice, too, is as I hover over any of these items that tells you what it is. Just leave your mouth there for a second. It'll pop up and tell you what that actually does. So, um, so here we have. If you want to read, have bold italics and underlined, Um, one other thing I want to point out is you'll see it says bold. And then in brackets that says control be so if there's ever a keyboard shortcut, it'll tell you what that is as well. So, um, so we could click on the bold and you'll notice that bold everything We can click it again , un bold it or we can hold down our control. So I'm holding out control on my keyboard and pressing B. Oops. Gotta get out of that. There we go. So, control, be bold everything. Um, typically, I don't use to much italics or underlining will come back to this in a second. One thing I do like to do, though. So just kind of popping over here to alignment that we're gonna move back here. I like the center. So for headers, I want a center. My header. So Ah, here you have your alignments, the left center and right, So I'm gonna hit center. So you noticed those air all center now within their cells. You could, in dense of these air in dense and removing dense and these air vertical alignments, we will get too much an emergency center rap tax, most likely. And if we do, I'll just show you during the course again. This is meant to be very introductory for you guys. Just we conform at what it is that we work on during the course. So the other thing I like to do to make this stand out a few other things. When I like to put a line under these cell, it's not quite the same as underlined. So underlying underlines just the tax that's in any given cell. Uh, right here we have Borders and Borders will put a underline under the entire box. So that's what I want to do, something to go here. And if I click often now, you'll see. There's that. My solid line. Just a note again. There's an arrow, Siegen. Drop it down and there's lots of different borders. You could do the right, the left, the top boxes, etcetera. Everything I like to do is maybe shade it, Um, you know, so we could change our font color. I'm typically not a fan of that. I usually leave everything. This is black. If I want something to stand out, I will, though, shade the cell so again dropped that box down and you can highlight and you see it changing already. Over here is I scroll around so it's not permanently changed, like click on it. But it's kind of showing what it would look like. So maybe we want like a light green. So there we go. We got something nicely formatted. Okay, guys. So, um next let's say that this. Ah, So let's go over here to copy Paste. This is copy paste are cut and paste, etcetera in the format. Peter. So say we. This was semester one. And now we want to duplicate this first semester to you know, we have the same students. We have the same tests. We don't want to have to recreate this whole thing over here, since we have it right here. Well, just use our highlights. So hold down your left mouse and and ah, scroll till you cover it. You go here to copy and you'll notice. Copy is control. See? So we compress controls. Hold down. Control, press, see, Or just click on this. You'll see the jagged line outlines it. That means it's been copied. Something's happening with it. So it's just paste it right here. So go to where you want to put that copy and you can just hit the big paste button. And there we go. Now we have a duplicate of it. Easy. Is that much easier than having to recreate it? Okay, guys, So let's say I wanted to, um I wanted to copy this kind of the format of it, but I didn't really want to, um, copy of the data itself just because, you know, we're gonna have different students next semester. Obviously, the scores will be different. Um, you know, and maybe instead of test ABC, these were dates or something. So I like the format. I like all the time I spent, you know, making a green making and centered, etcetera. Um, one way we can just copy the format is if you get new highlight and take. Technically, all we really did was this. These four cells was all we did. Um, this little paintbrush here, it's called the format painter. So you highlight something you have formatted. So this click the format painter and then go where you want to pace that format. So there you go. So we have a green. We have our line. If I type something test six, it's gonna be centered. It's gonna be bold, its executive formatting. So it's a way of copying formatting without copying data. Um, cut works the exact same. So if we highlight something and go, the scissors. So instead of creating copy now, it will. If we had pays, just it'll cut it and move it where we went. All right, guys. Last. Ah, a few little things. So instead of test scores say these were actual dollar amounts. Say these were sales amounts. These were salespeople. We were keeping track of how much they sold of each product. Um, so we want to format these toe look more like dollars. You notice these? They're just straight numbers. So the way we can do that is highlight all these again. Then we have her numbers box. So we conform at in different ways. So the defaults air source that we should say the shortcut ones air the dollar sign of percentage, the comma. There's several other options. You can drop this box down, um, dates, fractions, etcetera. And then you can go to Mawr and have even more options. But for our purposes, you know what? We just want a dollar signs. So there we go. Perfect. Now let's say that we didn't really want the sense we don't need the two decimal points. Um, there's a quick way to get rid of that. If you leave it highlighted. Don't bother. On Highlight. Right here is the decimal increase and decrease. So if we click decrease once and twice, it gets rid of that decimal point and it will round off. It'll so those decimals air technically still there? Um, just they were all zeros because we put in whole numbers. But if there was no 0.56 and there, it would have rounded this number up. All right, guys, I believe that. Is it anything else that we cover in the course? Like I said, um, at that point in the course, I'm just going to show you how to do it there. So we might. So we'll do things like write formulas. We might do some sorting. We might do a little bit more formatting, but I encourage you at this point, if you if you're feeling with thick salary, feel comfortable this point, let's move on. If not, maybe create your own blank worksheet and type some data. Just test out. Just try some of these different formats, see what they look like. Increased the fonts, cut and paste it around. Change things the dollar signs make a list of dates. Um lots of cool stuff you can do in Excel. So that's it for this introduction. Let's get started on the course. 4. 089SALES Section 02a Excel Tabs and Labels Start: All right, everybody, welcome back. It is time to get started. So the process we're gonna take essentially what we're gonna be doing is building, ah, couple of different tabs. Now there will be one that's based their summary tab where we will compile information from other tab. So we're going to be listening out different years, the different months. We will then put together a forecast for what we forecast for each month, and then we'll be pulling in our actual sales from some of the more detailed tab. So the best process to go through this is we're gonna start constructing kind of the main dashboard, if you will, where you have the monthly summaries. That's where you probably get the most sort of, you know, overall information you can use for your business. So what we'll do is we'll build out the template for that, and then we'll go and we'll build a template for one year, and then I'll show you how you linked those two. And then from there we can springboard and build like a nice say quarterly in nearly summary. So stick with me and it makes a lot more sense. I think once you start seeing it. So as mentioned probably before feel free to follow along. I am the what we build on Excel through this course I am going to make available for you to download. So it'll either be attached with the conclusion video or just in general on the course. It depends on what platform you're taking the course. So I'm not sure exactly where you find it, but it is available. And if you have any issues, send me a message and certainly can help you out one final point before we get started. I do try to keep the videos between five and seven minutes. The basin. Just we're gonna move ahead when I see the videos getting to about that length, we'll try to cut it off a nice logical point. And then in the next video, we'll pick it up right where it left off. So I believe everything you'll see in the course in the videos. You know, we're not gonna jump anything you like. Every step that I take, you will see on video. So that said, let's go and get started. So as mentioned our first things, let's name a few tabs just so we can always know where we're doing. This is gonna be our summary. Let's just call this our, uh Let's just call it our summary. Now, eventually, we're gonna be building once for each year. So this would be I'm gonna label this 1 2018 and I mean, you could call 2018 details if you want. I'm just gonna leave in a 2018. I think it's fairly clear that that's going to be, you know, the sales information for that specific year. I'm gonna um um Action was gonna create another one. But let's delete this for a second, because once we build the 2018 1 we can actually just then copy the sheet and replicated for 2019 2020 and set those up. So save us a lot of time rather than sort of recreate them. We'll just duplicate what we've already created. So someone put those in, this will be your summary, and we might want to call this like our It will be a summary of month and also be a summer by year. Often times like to throw in kind of a quarterly one we might make a separate tab for that . But we'll get to that in a little bit. So let's go ahead. And it started. So this is gonna be I'm just gonna go with the name X Y Z. So whatever the name of your company is, let's give it a name. Excise the sales report now. So some of this is I'm gonna be showing you how to set it up and make logical sense. And some of it is just for men and guys. So a lot of times, I'll go through and to start putting stuff down, and then we'll go back and format at once. We get a few things down, So, uh, it'll clean up a little bit. You know, we'll make this bold and a little bit bigger, fun, But I'm essentially really at first Someone. I just want you to know what this is gonna look like. So we're gonna write year one 2018. Then we will do month. And this again, I'll make sense a little bit more. Some things I'm not gonna explain. Every single you know. Now I'm typing the word sales where it's gonna get it down on paper or in excel And so this is short for cumulative sales, A little forecasts, and the reason we abbreviate this is because we don't need big, huge columns. Imagine if I wrote out cumulative sales. The whole you know thing. It's already, uh, you know this wide, and we're gonna have numbers down below. We don't need a big, long, wide cell well center. These will throw it out a little bit of formatting here, and then we'll make these bigger. And now some of these things I'm doing excel to. I'm not explaining every single click. I make everything I do. Hopefully, you pick some of that up in the Excel Basics video that was in the kind of opening introduction part of this course. If you're already kind of lost, like, what's he clicking? What's he doing? Maybe go on, watch that video. Sometimes I'll try to explain what I'm doing. Justus. Well, so, for example, when the cells were too short, you know that the words are cut off. If you double click between any two cells, it'll automatically resize itself to the size of the largest content sell. So if I had another cell that having longer sends that would have made it big enough to see that you can also just click. So I'm right clicking or sorry, left clicking. And you can drag and drop How big you want to sell to be one other point Worth mentioning. I usually I should mention this upfront. I've already saved this file. Business sales forecast, goals and results tracking dot XLs sex. Um, go ahead and save your file. And then also, I would automatically turn on auto save if you can. I I personally can't because I saved to an external driving it. Well, let me enable the auto safe. So I just go ahead and click the save once in a while, or I press control s so I'll try to tell you from doing keyboard shortcuts as well. So since we have our titles for this is and believe me guys going to get some date and here we have put in some graphs, this thing is gonna be a lot cooler than your thinking right now. Let's go ahead, though, because we're already up to almost six minutes. Cut this off in the next video, we're gonna start laying out the individual months. Um, we'll start thinking that about the forecasted amount. We're gonna put that in here. Well, right. Formulas for these as well as for these. So this will all be formula driven. So in the end, the only thing you're actually in putting on this sheet is the forecast. Once it's built, and then the sales data will be pulled from the individual years. Once we built those just to give you an idea and that will link up some cool graphs over here to it as well. 5. 089SALES Section 02b Forecast and YTD Formulas: All right, so next up guys again, we'll do a bit of formatting on this in a bit, but I kind of get somewhere the labels. And so for a month, you would just have the months of the year so very easy. So what we'll do, I'm gonna show you a trick you could could do this a few ways. I mean, you could type if you wanted to. You could actually type January 18. So this will be for 2018. Ah, you notice the little possibly I put in here. That is so that if you put an apostrophe before anything and excel, it will keep exactly what you wrote. If I don't put the apostrophe, let's just see what happens. It formats it like a date. I could then go and change it. Sometimes it's easier just to put the apostrophe it right. Exactly how you want and then go from there. Uh, the other way could do it is I could just do the like a 1 31 18 Just picked the last day of the month were discovered a month and year. We don't really care about the date. Um, go to short date. You could do that or I prefer I kind of wanted to say January 2018. So I'm going to go to more number formats date, and then I'm gonna look for format. That is like I want There we go, the three letter month and then the year we're gonna hit that now I could go through and I could type 0 to 28 18 and we could then copy this. You know, do the same formatting and set your butt and give you a little easier way to do this. We know there's 12 months in the years we're going to go down 12 spots. Eso if this is the sixth, is that the 18th? In his 17th? There's something called Phil. You'll see it here on the home ribbon over here. If you drop that down, one of them is called Siri's. So Excel is smart enough to figure out we're going to say, Hey, I've already written one month. I want this to be a serious now. February, March, April May um, it goes in the column, so Excel already figured it out a little bit because I highlighted cells. It's a date so recognizes the date formula. And this the same to you wanted to go up by day. We don't we want to jump by one month at a time. The step value is one. So one month at a time on the stop value. So if I hadn't already pre highlighted cells, I could say, Hey, goes go up one month at a time and do 12 months. But since already highlighted, it just did the highlight section and there we go. So that saves us that typing not a huge time savings, but a time savings nonetheless keeps the format consistent. And imagine if we didn't say we were filling in days of the year and we needed 365 you could see how that Phil formula would really save someone a lot of time. I mean, believe me, guys, I've seen people like hand type, every single thing and excel they don't even look for. Try to think of a shorter way to do it. So we're gonna fill in this area with the different formulas and our forecasts. I'm gonna put a very generic forecast to start with. We can rethink it a little bit later. um, but I want to get this in, and then we'll work on formatting this area to pretty it up a little bit. So first things first, so I'm just gonna put in a very generic formal our forecast. Let's their first month. We want 1000. Now. Already, the format isn't the greatest here, and we know that all of these are gonna be dollar signs, right? So let's just highlight this entire area, and I'm actually going to go down just a few more spots because down here will put totals in, and then some averages actually gonna go One more. The dollar signs right here. If you want periods, that's great. I personally I mean, we're gonna be dealing with big enough numbers that, you know, I don't care if it's 1000 and 50 cents for 1000 and 70 cents. That doesn't worry me. So I'm gonna take away two decimal points and will you do that? Is decreased decimal. Click at once. Click it twice. Now, this entire areas format the same. All right, so let's say our second miles we're doing 1,502,000 2500. Then think, let's you know 3000. Let's we think this 1st 6 months, we can really ramp things up. Then we're really gonna ramp things up. You know, we start to maybe get some repeat business, but then things maybe start toe level off. Let's go to 7000. 8000. So we just were considering 1000 month is how we're going to grow completely. Um, you know, we're just putting in a dummy budget, if you will, Just for some numbers sake, let's see, we're up to 4 23 We can do at least one formula here, So cumulus sales. So sales will come from the sales tab. We don't have anything built. Yes, we have nothingto link, but we can still build this formula. So, cumin, their sales is basically saying, What's your year to date sales? I mean, that's another way to do it. You could rename this if you want. I might actually do that. Only because I think that's maybe more of a standard, um, forecast standard terminology. Um, so it's very simple. So the year to date in January is just the January sales, right? So if you press your equal, sign arrow over to sales that now your second month will be your year to date from the priest. So there's a couple ways to do this. You could take your year today from the previous month, plus the new month. Or you could say, Hey, add up this month plus this month I'm gonna do it this way. Take the previous month's year to date, plus the new month hit Enter. And the reason I'm doing that is because now I have a nice formula that I can copy and just copy all the way down. So I press control, see to copy I'm holding down shift I'm pressing my arrow down and now I'm gonna press control Hold down control Impressive V for Paste Gonna hit escape just to get rid of that and I'm gonna show you. So if you go to any of these and if you press f two, it brings up the edit box. Let's just go. The very last month, it's saying, Hey, take the year to date sales from the previous month plus December. Take the previous say at your day Sales from June plus July. Something makes sense, right? Easy way to put that formula and easier than trying to add up. You know, one month, two months, three months, etcetera. The awesome thing now. And actually, we have time for this. We do really quick the year today. Forecast is gonna work exactly the same, right? It's gonna be this month. And it's gonna be this month. Plus this month, this month with this month, etcetera. Since the this column is adjacent to this one and this one to this one, we can actually just copy this and you'll see. So we're gonna press control, see pressing control the V to paste it. And since we have data and here it's adding it up, let's see if it checks out. Okay. 1000. So this should be 2500? Yep. And that adds up that way. In that way, let's pick a random month down here. 17 500. So is taking 13 5 plus 4000. Well, let's just double check that. So it saying as of July, we had 17,500 in forecast cumin sales. If you highlight all of these and guys, if little trick in excel, if you look down here, it will do the very some very simple things for you. It will always show on average account in a some. So the sum of 17 517. 500. Perfect. Let's just do that. One on the very last one. The some down here 50 to 550 to 500. I mean, we can check our formula. Perfect. So this video is up to seven minutes. I'll cut this off, will do the averages and the next one just cause that's a little bit different of a formula and start putting in some totals and a little bit of formatting as well. 6. 089SALES Section 02c Avg Formula and Totals: All right. So let's pick this up, guys. And just, uh, just for informational purposes. So once we kind of Phyllis in, like, right now, we're just on the building phase. We'll get to more of the analysis sign how you can actually really use this tool. Once we have something built that's usable essentially on, I'll show you. And I mean, this is honestly the very exact tool I use. I'm consulting for my versus first several years setting out a forecast, tracking my actual sales. Seeing what My year to dates were comparing, Did I hit my targets? And now what decisions can I make to do better? So on average to date. Now, this is just for the actual sales. I didn't really need a I mean, I couldn't potentially put an average for the forecast, but I really just want to know what my average monthly sale was. And what I was really looking for was for that to grow right each month. I mean, it should be growing. If I'm hitting the forecast, it should. So ah, very simple. So your first month is just going to be in the formulas average, um, average and It's just this. So now since we have nothing in here, if I put like a forward, that would actually be a dollar amount. We don't have anything. Um, I'm gonna put in just some very I mean, these obviously aren't the real numbers. Just some data. Just so we have something for the average to calculate off, we could make sure the formula is working. Now, this one, I'm gonna show you a different way to write the furnace. So for the 2nd 1 is gonna be on average. Now I'm typing this formula so equals average open bracket. Then I are over. It's going to the average of these two, right? So I hold down shift, I close it, and then I closed my bracket, and there's your formula. Now, I don't really want to type this formula over and over again. So luckily, we're in excel, and there's a way to do this. So we know that the average will always start in this cell, right? And then it's just gonna move down and include. You know, this one, This one, This one etcetera. So the weight. But if I was to just copy this formula and a hit off to you see, It's just gonna move down. It doesn't know. It's just It knew that before it was averaging two cells and it's just gonna keep moving down by two. So that doesn't work to this copy. What we need to do is we need to lock in this first cell and let it kind of free roam down with the 2nd 1 So be six is always gonna be part of our average. The way you can lock in the cells, put a dollar sign before the column and before the number. Now you can lock in one factor, not the other. We probably won't get to that in this course, but for all intents and purposes right now, all we want to do is lock in B six, so the formula still works the same. But watch what happens now when I copy it down. If I hit up to it kept B six, it locked in B six and it moved down to be eight and we'll do the same. I'm just gonna copied all the way down and show you not to believe me down here. So all these in between it's only you can see it on here on the left. It's only coming down as faras here as the cell and on the very last one, it's averaging all these B six is locked in. Another trick for you definitely comes in handy, guys. Okay, so I'm gonna shorten up the celibate now, these are the real numbers. Um, I think let's, um let's just leave those in for now. We know that those are the real numbers and we will replace them. Uh, so let's actually get start on, do a little bit of it's gonna say form a but let's put in some totals first. So total's so equal Some open bracket. I'm highlighting all these by holding down shift in the arrow key, and I think we know we can copy this over here. Now you're to date. It doesn't make sense to do a total on your today because your date is already a total of this same thing here. So we didn't don't need totals here, and we don't need totals on an average. So these are really the only two totals that we need. Alright, guys. So that is it. So let's do a actually were up to four minutes. I want to say, Let's cut this one off. Now that we kind of have this built the next video, we'll put in some formatting, make this look a little bit nicer. Probably after that will put in a graph on then. After that, what we're gonna do is replicate this for two more years so I can show you how I can take all this hard work we've done and then boom, just quickly make the next two years. You know, once we do this little bit of work, we're good to go, and it's really no issue moving forward. 7. 089SALES Section 02d Formatting and Graph: All right, so, as promised, let's do a bit of formatting. So right off the bat, I want to make this a little bit bigger. Let's make this maybe, like, 16. Give it a bold and obviously you put your company's name here. This is well, like to give it a bit of a bold maybe making 12 or 14 just to make it stand out a bit month . So let's pulled these. We've already centered them. Totals tend to like to bold things that air totals Save that. We can actually shorten these columns up. Just a touch if we want. Don't have to. And if you don't like the dollar sign on the far left, there's other number formats as well you can use. I'm just gonna leave them as they are. But if you explore the different number formats like accounting at Centra, you can get it where the dollar sign is right next to the number. Okay, so gonna give this just a touch of shading. I like to like, make it really stand out. If you want to use bright colors, be my guests. Uh, well, I know. Let's go with some green. We're talking about sales here. So you kind of have different sections here, right? Like you have in a month. And you have the sales in the forecast, which kind of go together. You have the year, two dates, which kind of go together, and then this is kind of some extra supplementary info, so I like to break it up a bit. First thing I'm gonna do is throw a box around this with just borders. Let's see, outside Borders. And then essentially, let's do it below. Here is Well, now there's a couple different ways to do that. But kind of the quickest is just to re highlight that on. Then we're gonna do another border here. So I'm just highlighting what I want to throw a border around. So just imagine lines. And even though this already has three lines around, it doesn't hurt anything to just redo it. And, like magic, I mean, 30 seconds of work. We've got a nice little formatted table now, guys just kind of, like, separates things a little bit easier to compare it now. You could certainly run with this. I would encourage you to obviously make this your own. Like you might want to put in a variance column and say, Well, hey, I forecast it. I had 1200 actual sales, and I forecasted 1000. So I was 200 to the good. This month, I forecasted 15. I only sold another 1200 SOEs, 300 to the bad. Make it your own. I didn't do that for this. Purpose is you know, it's fairly easy. And I think when you look at totals, I mean, that's what is really after. Not a month, a month variants. So much so we do use them for making good decisions. But you don't want to sort of micro manage too much, either. It be to take that to an extreme. Imagine if you did this every day, and he tried to forecast your sales every single day, drive yourself nuts and not have enough time to run your business. So, um, I'm going to Let's make a graph now, in order to make a graph that's actually like kind of looks relevant, I am gonna throw in just some rial numbers here. Um, some months on, make bigger some months on, make smaller. Some months, maybe I'll make the same. Let's say that let's say continue. Let's say we're a bit behind the gun now. But then let's say things pick up really well, but then drop off and then, uh, 500 then let's say 8000 we don't quite don't quite meet our goals, So this will look a lot cooler and a graph. Some grafts are under the insert ribbon up here as soon as it goes under charts that called charts Now. So essentially a quick way to make a graph. And we want a month by month, right? We don't need to grab these totals, etcetera. Let's just grab. So highlight what? You wanna want a chart now? Because we haven't on this nice, tabular format. Excel should do a pretty good job figuring things out. And one thing you could do, just rather than sort of try to figure out come to recommend a charts, it will give you a bunch of suggestions on what you could graph so you could do a cool line graph plot chart doesn't really seem to help us this when I like, and this is actually what I do personally used, um, this one wouldn't make much sense. Nor would this. I mean, it's stacking one number on top of the other doesn't really help. We want to compare our actual to our forecast. So this seems like the perfect graph. Let's choose that. Let's just move it over here to the right. Um, I don't like this exactly what I want to see. I want to see every single month and let's see the sales. The forecast. I mean, certainly we have the data right here, but, you know, we have a nice visual comparison and both are moving up so really pretty good. Right? Let's I'm gonna rename. This could call it actual versus forecast. Not much else. You really have to put on here, guys that it already figured out the labels. The dollar amounts the months, etcetera. So I was simple. Is that Let's see if we can bold this? Yep. Just, um, somewhere I saw the bold pop up. It's not the end of the world. No, let's see if we can pull. There we go. All right, Now we can get rid of this. We don't really need too much editing. You could go to town to create whatever kind of graphs you think are necessary. But literally. This is exactly what I use my 1st 5 years of consulting just tracking individual sales. And that's when I was doing a lot more individual sales. So it's a lot more smaller amounts but higher volume than I kind of moved into more working with just one or two clients at a time. So, um, this method, what still would have been completely valid, But it kind of lost some value because I had a much more stable business, if you will. I knew what? What? I was up against him each month so nonetheless. OK, guys, we're up to almost six minutes. So let's. And this video in the next one we're going to do is create the next two years. And then after that, we're gonna move into creating this. I'm gonna show you kind of the methodology I used to track individual sales by month, come up with the totals, and then link up those totals over to this graph and will replace these with formulas 8. 089SALES Section 02e Year 2 Setup: okay. So as promise we have our 2018. So we're actually just gonna directly copy this, So just highlight all of this. Not sure if it's gonna grab that graph or not. If not, it's not a problem to recreate one. We're gonna throw it down here. I'm going to Right click. I'm gonna hit paste, Just pay strip paste everything and it does. It copied the graph. So we're good now is a real test. We can just quickly change one of these numbers. And what you do see is that the graph isn't changing. So this graph is still linked to this. And you see that now that I've highlighted that graph, so we're actually gonna have to recreate. We could try and go in and monkey with the settings of this graph, but you saw how quick and easy it was to create it. It'll probably a lot easier to guys. Just do this. Let's just create the graph while we're here. This let's show you real time. How quickly and create this graph. Okay, moving over here. Give this a double thick actual versus forecasts and actually let's put in the years well in front of. So this is gonna be 2019. Um, and then I'm gonna make this slightly bigger. Just actually, there's a space in their perfect Okay, this coming year two. 2019. Uh, this one. I'm just gonna put in that 2018 total teen or actual versus forecast 2019 and then we need to change these dates. We could go down and change each one. We could do the fill thing fairly quick. I'm just gonna do these just for fun and to show you different ways. All I'm doing is hitting F two to editor cell backspace one tight nine. Enter. So F two backs phase nine, enter four keystrokes and see, we're almost there. Probably been this slightly quicker to do it, Phil, but no big deal. Okay, so and a simple is that guys, we have another year mapped out now. I mean, obviously, we're gonna have to link up these. I'm just gonna delete these out for now and then our forecast. So the one thing you enter you physically enter on this sheet is your forecast that was there. Year one forecast. So let's think in your too. So thinking a little bit about strategy and how you're going to use this. This workbook. Well, we've already gone through one year. 2018. Hey, we came pretty close to our, you know, to our forecast, But we didn't quite those last few months. I mean, we did really well here. But then, you know, we fell short here. We fell short here, which fell short here, so I wouldn't want to continue with the trend of, you know, I'm just going to leave this house. I know these are way too low now for our next year, I wouldn't continue to say, Well, we're gonna make 10,000 this month now. I mean, a lot of it depends on your business. Maybe there's a reason, you know, and this completely depends on the nature of your business, etcetera. But for me, personally, I'm just thinking of this from a consultant perspective, you know? Hey, you know, we've been able to get 75 8000. Seems like we had a big jump here, but we also had a downfall here. Maybe I start looking at things like, Well, what was that? Averaging those last four months. And we can use the little tool down here. 6800 was our average. We did see some gross. So maybe we realistically want to scale this back. Let's try toe, you know, not shoot ourselves in the foot. Let's say we're going to start off with 7000 and then we're gonna stair climb by 500 up to 9000 even though nine thousands are original December forecast. Let's just give ourselves a bit of time, and then we say, Okay, Well, while we're working on this and figure this out, we're really going to start planning ahead some now. We are gonna start stair climbing at 1000. Maybe we maybe we have an end goal. We say You know what? By the end of December 2019 we want to be bringing in at least 15,000 month, and we want to be pretty close to that by the end of the year. So we're gonna make this 14 500 this 14. Well, now we just gotta fill in some blanks. So 11 12 13. Hey, it seems like a logical progression. Right? And there's their forecast now. Obviously, we came up with a forecast in our heads off of you know, one or two minutes, you would certainly put a little bit more thought into this. You've already thought Well, what's gonna get you to 15? Is it reasonable? Can you do it? You have to hire people. All of those types of things. Um, let's see here. I'm curious. 01 other thing to remember and see. This is where sometimes it takes. It's good to put in a reasonable evaluation. It's still locked to be six, remember? So what it's doing is averaging all of this. So we need to reset this to be 24 and then we can copy this down now because we've done a little bit formatting. And that's why I leave formatting to the end, cause when you start copying moving cells now, use up the formatting. If we right click on this, I mean, we could certainly paste it, but it's gonna wipe out the bottom border. You could also do pace formulas. This way, it on Lee pace the formula. Guys, it doesn't copy the formatting. All right, so I think we're set up, right? So we just had to make a few edits. We had to change the names are the dates here, this header, this header. That's formula. But that's it. That's all it really took. And then we put it but Intel into creating your next month's forecasts up. We're up to 5.5 minutes. Let's cut this off in the next video. We'll do year three, and that's all will do for our summary tab. Then we'll get into doing some actual data. 9. 089SALES Section 02f Year 3 Setup: Okay, so we have our year. One we have our year to I'm gonna put in year three. Where do one last little bit of formatting, and then we can move on and start looking at actual individual sales. So try to stay consistent, like here. I left to blank sales between you know that you're in this year's let's do the same here will paste it here. Where is going apace it as it is. And we already kind of a heads up, so it might move a little bit faster here. So we know this is, um, your three. We know this needs to be 2020. Let's in this one. Let's do our fill formula. So I'm just gonna highlight all of this, and I'm kind of moving at, like the speed, you know, if I wasn't explaining it, that I wouldn't move that just to show you how quickly this really can't come together, you see that? We're gonna have to create new forecast. Let's do that in a second. We already know we have to change this formula. It would now be be 42. We need to copy this down. We need to right click. We need to pace just the formulas, which is the FX. We need to delete this graph. We need to create a new graph and somewhere out, because I paste it down. I lost a bit of the formatting here. Um, let's just throw a box around that. No worries and trade this. We're gonna insert a graph, recommend it, and we don't have any data in here. That's why this is a single line. But you see, it still doesn't show that it's capturing the sales column. There's just no information in there yet. Put that over here. 2020. Oops. I get type of right actual versus forecast. It don't need to hit. Entering this move. Click off perfect graphics set up. We have our formulas. Correct. This is correct. All we need to do now is re forecast. So let's think so. We didn't come up with any bogus sort of our pretend. If you will sales data for this year. Let's just assume that I mean, there comes the limit, right? You can only do so much. And again, I'm going from the mindset of a consultant. You can grow your business, but, um, month over month after month. But let's say this was kind of your goal. Or let's say that was your goal for that year. Uh, and you think now you're gonna look at it more from like it's gonna stabilize somewhat. So your goal is really to stabilize, incur some gross, but not you know, that you can't ramp it up like you did in the past, so we're not going up 1000 or 2000 every month. So maybe, though your goal is to maybe bring on a new client and and every quarter. So we bring this up to 20,000 and it could be copying those Damn, it's fairly quick, that type and let's just say must keep with the same theme. So now you have amore sort of core legals, if you will. Any kind of the same goalies quarter. Ah, looking at this from an annual perspective, your first year goal is 52. Got to 49 Your to your goal is to more than double, which is fairly aggressive. But if you've been on this trajectory, that might be a fairly reasonable and then 1 29 to 2 25 definitely still a huge jump. Ah, at this point, you know your forecasting ahead, you would continually the another point to this. You continue absorbing revising this to say, like, stay here. You know, actual sales fall off like things just aren't continuing as well. Maybe you got lucky. Maybe it's just there's more people doing what you do, whatever it is. And you're not hitting the 7000 say this is 55545 At that point, you might want to reconsider re forecasting this. I mean, remember, this is only for your own personal use, its for setting goals, tracking your performance. There's no shame and changing this when if you're only occurring, you know, fours and fives and sixes. Six. That you know, thousands. Are you really going to get to 15,006 more months? Maybe if you have a plan. Terrific. But if not, um, no harm and re forecasting. So, um, let's just throw in a dummy number here just to make sure a graph works. Um, oops. Way too big of a number. That was part of the problem. And we need another one to link it to. There we go. And actually I just realized I completely chose the wrong graph and part of that. So let's just check this one. Okay? So this one works. I chose the wrong graph type down here. Guys gonna delete this real quick. Sorry about that. I didn't even notice that. That was kind of the flaw of just having one. Ah, you know, one set of data and I don't have any dummy data in there, so it didn't catch my eye like, immediately that it was different. Eso busy talking. So But on the flip side, if we get to see how quickly Oh, versus forecast, it could be fixed like that under a minute. Hey, we got a nice new graphics showing our dummy data. We're gonna delete this. So for up to five minutes, guys. Three other thing. What I mentioned, um, you might want to do from the formatting front. You could create a separation between between the years. So basically put a huge box around this, and I'm gonna do that. He's and I I'm gonna go with the thicker border and then we'll see how this looks. Create the same one, put it down to here, and then continue on with this one. Oops. There we go. And there you go, guys. Just creates a bit more of that visual separation. I'm gonna zoom out a little bit just to show you, even though obviously would want to be zoomed in to analyze the data. But, hey, look at that in, like, 1/2 hour. We have a nice table and think about the usefulness of this data. Um, you know, at the end of the course, I'll put I'll do kind of a final summary will go through and really think about how to use this. But just as kind of, you know, we've touched on it a few times. You know, that analysis aspect of you know, how are you doing on any given month at the end of July? Well, July was short 3000 but year to date were only short about our story. We're short 300 but you're the date were also only short about 24 Earth. Yeah, about 24 day. Not quite to 39. So hey, we're not horribly off. Were averaging 2400 now this year were really ramping up, so the average is always going to kind of be lagging behind on the specific month. So but then you can read. Use this information to forecast ahead. Say, this year have been blockbuster. Well, maybe we jumped. This number's up. Okay, guys, gonna cut this video off on the next one that we're going to start building. I'm gonna show you how I would plan out a specific year and tracking month by month what it is that we're doing. We're gonna track actual individual sales. So again, just coming from a consultant perspective. You know, you do several jobs a month, but it's not like you're doing 200 jobs. Two months you're doing, you know, 10 maybe eso. It's easy to list out the specifics and keep track of those. And that helps in your forecasting as well. Because some of them are repeat clients. You already know that. You can count on that for the next month. So we will start that in the next video 10. 089SALES Section 03a Year 1 Specific Sales Data: all right, so kind of next part of this course. Let's create a template to tracker individual sales for a given year. And then once we build this again, we can like in the previous kind of section. We can duplicate this for for coming years, and we can almost use it, too, as, Ah, we'll show you when we get there, we could use it. It's sort of a forecasting to a little bit as well and put in some, you know, dummy data. Like I said, if we have a customer that is repeating, maybe they have a year long contract. Well, that's revenue you can count on so you can kind of put that in in advance. There's going to be fairly simple. We're really just capturing data here. We don't do too much analysis on this tap. What we're really doing here is capturing the day of that thing gets pulled into the summary tab that we can then use for a sort of more making decisions, some some real simple Let's see. First of all, we would put her month, so this one doesn't have to be too fancy. Many can put ahead around and if you want. Let's ah, sure, let's look X Y z company 2018. Um, sales do that. Let's give it a bull. Let's just do a bit of formatting right off the bat. Make 16 and hit my state. But don't forget to hit that save button. And if you hadn't already remember to create a new tab just given name, like 2018 or 2018 specifics or details or sales, whatever you want to call and then what we're actually gonna do. So we're gonna let's put on some headers first months or this month, clients. So you're gonna have to tailor this a little bit to your business so personally again, I came from consulting, you know, business CFO consulting. So I had clients. I mean, maybe you if you sell stuff on eBay and Amazon and offer up, but such or maybe that's instead of clients, you have platforms or places you sell your goods, and that's what you're gonna track your sales from. You know, maybe you sell stuff online and in a retail shop, well, maybe that's how you distinguish so whatever makes this sense of a way to distinguish but get a fairly detailed information now for myself. I wanted to keep track of. And, you know, this certainly only applies almost go in the consulting business by I kept track of hourly clients and fixed fee because some people pay just a set amount. Hey, here's X amount of dollars. Do this for us. Other people say it will pay by the hour. So I would just separate those two more just for information. If I look back at a client and they could remember that on then total revenue and then this will be the number that ultimately is getting pulled over. So let's go ahead. Let's, uh oops. Let's center those. And, you know, I'm gonna put in some common cold notes occasionally. You know, you just want to jot down a note on the side. Whatever it might be you could even right. This is a one time project. They were recommended by Bill, Whatever the note might be. Just so years later, when you look back, maybe something you want to know. So, um so for January again, let's ah, put it in our 31 18. I think I Period, Let's now this one you have a choice as well. You could actually put in the specific date. Let's say you did actually do, um, again, let's go back to the idea instead of consulting that you're selling on Amazon and eBay, etcetera, maybe you do want to keep track of this every single day. You know what? We're my sales on each of those platforms. That would definitely be valid way to use this tool. Because ultimately, we're just gonna pull the total for the month over to here. So whether you do it by data by week by, you know, client we are, you know, Amazon is your client, whatever it might be. See, you really need to think through this section. And what will make the most sense for you now for me? Because I was in consulting. It was individual clients. So and, you know, they were just pay me once a month for whatever it might be. Um, so I might actually put in the date that they paid me. Let's say one was the 15th the 20th and I'll show you another formatting trick here in a second, the 25th and let's do 1/4 1 that paid on the 31st. If you have like this, um, there's this thing here called the Format Painter. If you click that, then you can just highlight other cells and it will form at them the same as the one you copied quickly to do that. So clients again, client name. We're gonna put in a few dummy ones. We're not going to do this for every single piece of data. Guys, let's say this gonna be ABC. That's going to be D f uh, G h I and J K l, of course, and then could give these guys different names. Let's see, these guys were hourly. Now, this isn't the hourly rate. This is the total amount that I would have made. Ofttimes, let's say they were 500. Uh, these guys were hourly. They were to 50. This guy's detain me 1000 to do a project, and these guys pay me 7 52 year project now again, as it went the other sheet, we need to do better formatting. Now we know that these will always be dollar amounts, right. These three columns. Ah, one way to do that. Since we don't know how far we're going down, Go down. You could just highlight all these. Do this. Since these are words that I can impact them again your choice You can leave in the sense I just drop him out on the total revenue is simply a formula. And it is this cell plus this cell, Brooks. I can copy this down. Now we're going to a bit more for Manning, but let's get another month or about So one thing I did want to do, though, is one time done the month. I want to put in a total so, uh, total here, so this will be total. And I was gonna write out January 2018. There's no data here. It's just a total. And I mean, we could put in the totals by the different types of revenue. But we're certainly not pulling these over to our summary tab. We're pulling over the grand total. So that is one month, and I think you know how this is going to go. We're going to do the exact same thing for February from March for April. So I cut this video off. When we come back, we'll do a bit of formatting on this. And then we're just gonna put in place holders for those really quick. We're not going under dummy data for every single month. Guys, it just doesn't You know, you get you get the point, I guess, is what I'm saying. So but for now, let's end this video. 11. 089SALES Section 03b Year 1 Finalize: Okay, so let's just do a bit of form. And that's because then we can just copy some of the stuff down. I'm gonna bull. The title's gonna bold my totals, going to put in Let's see, let's put in a so we can really decide here. How do we want to do this? Let's put just a bottom border on this totals I like to use in this case, it's very much the counting formatting. It's a single admitted double on the bottom. It will create a nice separation, too. Is that what we're going to start with February right here and just continue on down. So, uh, this works well, I mean, a month you could take a look if you want it to just, um, and actually did do this. But it was more for data mining on the future, but I would actually right outside created another column and I would just write January, January, January, January, Um, just with consistence. All these were tagged basically as January without the specific with the specific date next to them. But I certainly don't have to do that. So let's just do one more month quickly, uh, to 22 28. Let's just stay with three. We're gonna and we'll go through this kind of quick. You know how I I like to do it when we've already kind of gone through it once. So let's say this person, I mean again, we don't really just gonna put in again just gibberish because there's a matter. Let's say these were all fixed fee. Uh, let's say we got $3000 jobs. Let's just make your life easy. Now we can copy this, but we have to be careful in those totals formulas because, you see, this isn't correct. This isn't correct on this and cracked because it in the past it was adding the four cells above it. Well, now we only need the three cells above it, but it automatically goes for force. You need to rewrite your formula could make this and nine, and we can copy this over now. We also need to put in our now this one we can just copy down. And there were just a simple fix. But those are things you really need to be careful of, because if say, you didn't catch that and say the numbers were small enough that they didn't stand out. So, you know or say, you know, Yeah, this was only the total from this month had only been, you know, $500. Well, at eyeball, you might not have noticed that this was 3500 and really just needs to be three. Or let's say you had, like, 20 different sales, and it was grabbing a total from the previous month as well. You might not pick up on that at first. He really be careful. Any time you have a formula, I really encourage you during the process and at the end, go through. Just hit that F too. But make sure you know what highlights is the right thing. Okay, Last thing here. We need to change this to February. Now, let's guys where we're at three minutes, so let's put in. So let's put in some placeholders. So, essentially, here's what I'm gonna dio. I'm gonna copy this down, but we take out the dates. We're gonna change this to march. We're gonna take out these guys take out this because there's no data. And here there were so data anyways, so we have our formula we ever formula. And then let's just do that for all the months that way. Now, this might seem anything out. Jeez. So this is the April May June. There's a reason. July, August, September, October, November, December. Now we have to manually change these just cause of the way we kind of did it. Now, why am I bothering to do this? And Chris, just do it each month. Um, the reason is I want to. Well, couple reasons. One, I want to be able to copy this sheet and create a 2019. So if we set it up here now, think of how easy it's gonna be to set up 2019 2020. Another reason is to I mentioned the past May we have recurring clients so we can put in some information. Alright, We know that X Y Z client is going to be each of these months, so we could enter that information in advance if we want to. We certainly don't have to. August. Hopefully we did the months right here. I think we did, um, July and I mean, while it might seem a little bit Liberace, I mean, we're talking a matter of minutes here. It's not a big deal. This is May. And this should be April And in March and in February. Perfect. Um, April. Okay, guys. And just like that, look at this in under five minutes under probably three minutes. So we're all set up now, so same March rolls around. I went up with five sales. Real simple. Highlight this insert. And then you could as well another shortcut. Hold down control and hit your plus sign. It adds rose hit, minus control minus that Deletes rose. The only thing you will have to do is copy leads to now. You could. Another way to do that would be to actually instead of inserting, Rose would be to copy this, um, toppy. And then you could insert copied cells and it will keep the formulas again. Double check these. You want to make sure that everything is good, but you should be good. And that's a simple is. It is. And now we just set up and ready for you to make those sales. You make notes and the notes you want to take. And that is it for that. And so what? We have laughter we won't do it in this video. So we want to create 2019 and 2020. Um, one thing you could do as well just to kind of preface this, uh, let's go ahead and do it. Actually want to do it before we copy it over. I want to insert one that says, um, kind of like forecast. So we know anything we put in this column we're guessing we're going to get. We're not just guessing a general number. These air, like, specific, Like so say, like, this client does happen each month. We would list them down here, put them in the forecast column story right here. So it actually hasn't happened yet. We know that anything in this column we haven't actually had yet, but we are counting on it. It kind of gives us a little bit of insight. One other thing I want to do is I just want to highlight this conscious with a color, just we could go with the green again just since it's the totals. And then you could certainly a few other options you have. I'm not gonna go through with it would be toe, you know, create those vertical lines to separate separate out things. This one really is more about data gathering than it is. You know, we want a nice presentation, but we don't certainly have to, um, have every specific thing. So, uh, just speaking of this again, uh, quickly think about a year later. So you go through a year, you make all these sales, we have this all linked up. You're gonna have your sales. And remember, we're gonna link these sums going to leave these out for now. You would have all your individual sales be able to do your analysis. Maybe one month was really high. You could go back and look and go, OK, Well, what happened in that month, it was that one time really big project. This is very useful information. And the longer you do this over the course of years, you will just gain more and more appreciation for and more and more knowledge. I still look back at mine from 10 years ago, and I'll look and I'll say it's almost a reminder to yourself What was I doing 10 years ago to generate clients, create revenue? Where was I getting them from? You know. Maybe in my case, I write a little note as to where I found the client. You know, not just the client's name. Eso There's a wealth of knowledge that can really gain from this, Not just in the immediate term, but in the long term. So really encourage you Teoh to do this and make it your own sub. Ah, that said, Let's go ahead will end this video in the next video, I think Let's create 2019 2020 Really quick. Then after that, we're gonna link this back and then I'm going to show you a few other sort of add ons that you could potentially do with it. Teoh, make a drone. 12. 089SALES Section 03c Year 2 and 3 Creation: Alright, guys. So let's go ahead. Let's duplicate this. So if you right click and just more excel general knowledge. If you right click anywhere in excel, it will give you a menu of things you can do, and it will be relative to where you're clicking. So, you know, if I click here, I'm gonna get office like Todd Copy, and it's related to that row insert. Delete. If I right, click on this tab. You know, insert, delete, move or copy, etcetera. So we actually want to create a copy of this tab. Need to click. Create a copy, and it's gonna ask me where. So summary 2018. I want to move it to the end. Ah, it's in this, um, spreadsheet. If you have multiple spreadsheets open, you can actually create copy in a different spreadsheet. You can't name it here, so just hit, okay? And then we'll double click it, and they may 2019. Uh, so let's clean this up, and then we'll copy 2019 and 2020 because it'll basically other than a bit of renaming, uh, will be ready to go. This one. We need toe, you know, kind of delete out. Actually, let's just go ahead. Delete everything here. And I think that might be it. Guys. Yeah. I mean, up the, uh, the years on here, we could do so. Certainly if you wanted to. You could just name this total January total, February 20 Total March, etcetera. Then you don't need the, like rename each individual month. I like having it on there. Just just the nature. I mean, it's the counting background. I don't know. Um, and for the minimal amount of effort here in under a minute, we're gonna have this raid troops ready to go again. I'm just pressing f two to edit, omitting backspace, some typing. Nine hitting enters for keystrokes and you are set. That was definitely under a minute. Okay, so you might know it's one thing to we didn't put a total for the year on here again. This is more about tracking the specific months. You certainly could if you wanted to remember. You can't just total up this column. Ah, you could total it up and divide by two. That would work. I would be a little bit gun shy about that. I would probably just say this one plus this one. Plus this one that cetra, um, after we had these set up will then start linking over to here. But for now, let's just get the 3rd 1 set up. So now that this is kind of created, I know we have a bigger gap here from March. Let's take that out. Uh, since you know, to be consistent more or less, all right, I think we're good. So let's duplicate this now for 2020. Now, you certainly don't have to do this all up front. You could wait till, you know, just do one year at a time once you go through a year, then set up the next year. But, um, I kind of want to set up three. Just ah, a little bit of forecasting. So maybe over here where we have our forecast for three years, maybe. You know, I decide, Um, that I really kind of want to map it out in here before I just jump in the number so later down the road. So stay here. We are beginning in 2018 and I'm thinking by 2020 I'm gonna be making 22,000. What's my plan? How am I gonna do that? Well, maybe I would come over here and go. Well, OK, so but, Well, this one is now the 2021. We need the name it, but, um, you know, I would have jotted down some notes. How am I gonna get there? How many clients know how much my charging them could do that? Now you could certainly. There's a couple ways gonna cut. You can actually create individual forecast sheets. Link those up. No harm in doing that. I'm basically giving you guys the way I kind of went about business while I create it. Kind of this, you know, template this format for a few years out. I was really You know, I had a big picture a few years ahead, but I really focused more year to year. Like, what am I gonna be doing in the next year? Kind of reacting toe. How the current, you know, was going and kind of forecasting had a little bit. So I didn't need, you know, full on forecasting for three years when I mean, who knew? I mean and say I can tell you from experience, I wouldn't be able to forecast three years ahead from now. Just because your business model change your clients change your focus changes, at least in the nature of what I do. So Okay, guys. So one thing so well, not one thing. The thing that will take just a bit of time. So we'll be now linking each of these. So we specifically have to say this equals the total from here for January. There are ways to automate this, the way we haven't set up. I can't think of how that would work on guy. Think the amount of time it would take to change things so that it could be automated would be the same out of time is doing this. So I think Let's do this. So you see what I'm doing? I'm just hitting equals we know this is April. Now then that's where this comes. It does come in handy. It's a quick visual check. So I'm gonna do these. Let's do two more than we're going to stop the video and I will link up the rest myself. Serve off camera. You don't need Teoh to sit and watch if you are following along while we build this you know, use that opportunity, you know, before starting the next lecture to go ahead and link up your own. Okay, guys. So you get the picture. What I'm gonna do now is I'm gonna link up all of these, and then when we come back, we'll do some final kind of clean up and actually want at in. Um, well, you'll see when we come back. 13. 089SALES Section 04a Quarterly Tracking: All right, guys, we have basically the nuts and bolts of this. So just a quick summary. And then I want to show you one more tab I want to create with you guys. Um, so we already know we have our individual data, and we haven't put in dummy data, guys, you know, I So I went through after the last lecture. NAMI said linked up each of the months. So there's nothing because there's no data. Testa's Well, so I was encouraged you. So state, come here. You know, put in. No, it doesn't matter how much. Hop on over here and make sure it shows up, and it does. So I've already gone through that process, so definitely test your data. Gonna make sure all these formulas, air working it is worth the effort to make sure things work. Right, cause the last thing you want to do is enter information, assume everything's working great. And it turns out you're making decisions bad based on bad data. Okay, so that aside, we have our everything set up. Um, So let's go ahead. When I want to create is kind of a quarterly announces because we kind of jump from very specific month a month. Information to, you know, monthly again. It's month, a month, and then we also have some annual on here. But we're really tracking and making decisions. You know, the way I really use this was to forecast ahead. Go through a month. So say March 2018. My actual sales are 3000 yen. Okay, well, how does that impact this? Do I change my forecast at all? It's such a one of my averages look like it doesn't have any impact on the future. Maybe I get a new client who's fairly large, and I get a one year contract while then I might revisit these totals. So that is essentially how you use this. But when I want to show you, is that what I really want is a quarterly one. So it's kind of ah, across, if you will, between the two of them, because we have our annual and we can compare a year So say, three years down the road. We could look at our annual amounts to go great, but I really want to see its quarterly as well. So I'm going to create a new tab for this, and I'm just gonna call quarterly summary. And this is I mean, if you want to name this monthly annual whenever you want to call up now for quarterly, I'm not doing anything to elaborate. Honestly, the way I'm gonna do this is let's see here. I'm gonna put in the years across the top, the quarters down the side, there's only 4/4 and then the total. So it's just gonna be a very quick, nice little schedule. So, like, this is gonna be 2018 2019 2020 Now, if you want. I mean, you know, eventually in time, 11 2021 2022 groups be over here. Up to you. I mean everything. So far we've done, it's just through the three years, uh, and then we would put Q one If for some reason you decide as well that you have different calendar year. So say you start your business and July of 2018 so you decide that you're fiscal year is gonna be July 2, June. Hey, so be it. I mean, you probably won't change the naming conventions a little bit just to make sure that you're clear on what months you're actually tracking. But aside from that, you know, this would obviously work exactly the same gonna center that gonna bold it, gonna bold these. We're gonna put in a total groups. Apparently, I had italics on old Let's go there Totals And like, before, we're gonna do our double. It's already highlighted from using from before and then these will all be numbers. Let's just go ahead and format that we already know we need to on then. This is a very simple exercise in linking, and we know these will be totals of the numbers above To see how quick some of the stuff is create guys. And I think it just it's a valuable. It's just terrific information. Now we have to be a little bit careful here, so we have to do a bit of work to create this. So for 2018 it's gonna be a some right of three months. We don't have anything currently that has the numbers. Now you can get that data few places you could grab it from here or grab it from here. I'm gonna grab it from the summary tab only because you know it's already. You know, sequential, if you will. So I'm gonna grab these three hit enter, and that is those three. So you have a few choices you could repeat that process, you know, highlight. Coming. Click enter. You could copy this formula down and just change the references. So we know that the 1st 3 months or be six to be eight, So these will be 9 10 11 So just change this. Now, keep in mind because we copy the formula down. It kind of adjusted. Which months were in there, which sells? It was grabbing, so just know. Exactly. So we know we need to capture 12 13 14. So let's copy this 12 13 14 and will be 15 16 17. Right. Perfect. And you can come back and check 15 16 17 and look at that. Cute for is that. Now we have to repeat that for the next two. Um, we obviously have no data for these. We just have these totally. And this placeholders figured. Why not? There's no real way to copy of this formula now and make it work for here. Um, so we're just gonna have to kind of manually grab these. Unfortunately, I'm gonna have you hang in there with me while we do this. Now that value is zero election surprise. Let me see sales. And for one, I grabbed the wrong month, actually. So let's hear or the wrong here. So some for 2019 is these three. There we go. I must have grabbed something and correct. All right, so let's do the same exercise. So we know it's 24 to 26 will be 27 28 29. It's 29 30 31 32 and 33 34 and 35 groups. All right, one more. Your guys stick with me here. Okay? So 2 44 So 45 six and seven Be 89 and 50 and 51. Oops. Through 53. Okay. And we'll just double check that. So 51 to 53. That is correct. So I feel pretty confident and that so now we have a nice Corley summary. Now, I personally didn't create graphs or anything. One thing I did do was I put in a growth factor, and basically I was just looking for my growth from one year to the next, and we haven't put in enough dummy data obviously do that, but basically, it would be this number divided by this number expresses a percentage. So if this was the exact same, it would came out of 100%. I mean, we can put the formula in, it's gonna come up with an air or zero, and it would be the same. And this is actually a percentage so a 0% growth. Okay, So, simply guys, once you enter data again, you get down the road a year, two years, three years. This starts to be informed of. You're looking for growth quarter over quarter, your book for growth year over year. You know, maybe there's 1/4 that's traditionally better than this is where you look at those things. And then you think, OK, why is Q three always you know, a lot higher than the other quarters, like consistently now. Then you could go to your specific data and go Oh, you know, maybe you have a client who appears once a year, and they appear in quarter three. Or maybe that's just a month when you have more time to do work and you know you're out there really hustling and finding clients and ah, and that's when it's working. I mean, you could see I mean, we've kept this in reference primarily to like a consulting business. If this was a retail business, your state buying things, selling them on E Bay, Amazon, etcetera. Well, the holiday season, like any retailer, is probably going to be the big ones. So you would extent we expect to see that bumping Q four for a retail based business. Q one is traditionally a slower quarter for lots of different businesses because everybody's coming out of the holiday. So but on the flip side, if you're in consulting, you know that's when companies budgets air renewed so they have money to spend again. So maybe Q. Four is your slow quarter. Plus, people are taking time off in the business world. Que forest slow. Q one is your busy season. So it's all that type of information that you can kind of read into this So none last guys , I'm gonna cut this video off, and we're actually the next video. We're gonna get into even a little bit more of that analysis and how I'd really like you to use the spreadsheet 14. 089SALES Section 04b Wrap Up of Model: All right, guys were towards the end. We built everything I wanted to build. It's want to go over it real quick one final time and just give you some final thoughts from myself. I mean, again, this is very much a replica of exactly what I used my 1st 5 years and insulting. Um, it was great value. I spent a lot of time on this doing analysis, thinking ahead, looking back. So a few things to keep in mind. One. I mean, the sheet is available for download again, so it will be attached either to the course. Conclusion to the course in general just depends on how you take the course. So look for this file to download it. If you have any issues, send me a message. I'll send you a link or just email you the file. Um, so you're gonna get the file exactly as you see it Now, I'm not gonna do any adjustments to interchanges. It isn't excel. So first of all, my few final points B be realistic in your forecasting. Don't set crazy goals because you're ultimately, you know, you don't want to set yourself up for failure. Really trying to set achievable goals. You know, say, you know, this month you want to get to 3000 What are you gonna have to do to get to 3000? So don't just put in numbers blatantly. Don't just say, OK, we're gonna grow, and then we're gonna double. We're gonna triple. No, really. Think about how those numbers are gonna happen. You know, this is a tool toe. Help you run and grow your business. It's not just, you know, put in numbers for fun. You know this. You're only fooling yourself. Some be very realistic in your forecasting. Be diligent about putting in your data every month. So you're not playing catch up again If you're playing catch up. If you're not updating it for three months, you're not really using the tool to begin with. So I encourage you to always be in the top of this on. Think about. So always be reflecting back, you know? What did I do in these months? For example, is there any like, can I? Maybe I should reach out to some of these clients again, see if they need more projects done. Uh, you know, maybe you do a bit of analysis again. I used example. You sell online Amazon eBay local, You know, like Craigslist offer up. Well, you know which ones they're doing best. Is there a way to make the other one's better? Should you focus more on the ones that that do better? So really, use this in town again to make your business just run better. And the other thing, I guess maybe the final point is that over time you're gonna build up this data set that's really win. This just snowballs, and it gets more and more valuable, so again encourage you to stick with it, update it. Um, and then, yeah, you can look back again when you're here in 2020. Maybe things are going great. And you just want to reflect back on where things all started. Or maybe things aren't going great. You know, maybe things take a turn and you're like, Okay, well, what was I doing back then? To make things grow and you can actually have that data us rather than just kind of thinking about it. Hypothetically, you could look back and say, Oh, here's exactly what it was that it was doing. And now, Maybe I can repeat that. And, uh, gross sales again. So that is it, guys again? If you have any questions during the course, send me a message. Definitely. Download template. Or if you build your own Congratulations. I applaud you. Uh, be sure to watch. Of course. Conclusion the bones video after, and that is it. Thanks, everyone. I've really joy t I love teaching these courses in excel. Like teaching people how to build tools to run their businesses better. So thanks for sticking in there with me and good luck with whatever business it is that you run. 15. 089SALES Section 05a Course Conclusion: Congratulations. Everybody have made it through the course. I kind of wrapped up in the previous video as far as the model on how I want you to use it , etcetera. I just want to say a final. You know, again, Thank you so much for taking the course. If you have any questions that came up and you haven't asked me yet, definitely. Send me a message. I'm happy to answer them. I want this to be interactive experience for you. Definitely download the template if you want to. If you didn't build your own, it should be attached either this lecture or to the course somewhere in the course download section. So if you have any issues and the message of happy, just email it to you. Definitely final point. Definitely. If you've enjoyed the course, if you could one leave a review. I really appreciate when you guys leave reviews for the courses. Um, it helps me out greatly. And I love hearing your feedback as well to I'd encourage you to check out my other courses . I have quite a few courses, and in the next video there's actually a bonus. A lecture attached to this course, there's a discount coupon for any of my other courses, and I currently have well over 80 other courses. So lots of things related to business, accounting, finance, economics, excel a little bit of something for everybody in the business world. So again, thank you so much, guys. I really love teaching these courses. I'm looking forward to seeing you in some of the other courses as well.