How to Create a Budget Template to Save for a Goal | Travers Morgan | Skillshare

How to Create a Budget Template to Save for a Goal

Travers Morgan, Photographer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
5 Lessons (16m)
    • 1. Introduction

      0:28
    • 2. The Master Budget

      4:32
    • 3. Setting Up The Monthly Template For Tracking Expenses

      3:25
    • 4. Setting Up Totals for Tracking Savings

      3:44
    • 5. Conditional Highlighting For Tracking Patterns

      3:39

About This Class

c313097b

In this class we will learn to create a template using the Numbers application on a Mac.  This course is relevant to any spreadsheet application such as Microsoft Excel, Apple Numbers, and Google Sheets.  You will be able to take the lessons learned in this course to create a template in any of the spreadsheet softwares.  You can also use both Apple or Windows operating systems to create this template.

The course will teach you how to set up a monthly and yearly budget in the spreadsheet and then link individual monthly sheets to the budget to track your spending and work toward a savings goal.  In the budget template creation process we will be learning some basics about spreadsheets if you have never used a program before and how to do more specific things for example, how functions work and how conditional highlighting works.

By the end of this course you will have a template ready to use in every day life to track your expenses and understand whether you are going over budget in specific categories to determine whether you need to adjust specific budgetary line items.

Transcripts

1. Introduction: welcome to my course on creating a personal budget to say for a goal. In this course, you're gonna learn how to create a template that tracks your expenses for each category of expenses in your life and then help you incrementally save towards a goal by demonstrating where you're saving properly and where you're not. And a Miss Travers. Morgan and I look forward to teaching you how to create this template and save for your goals. With that said, Let's dive right into the course. 2. The Master Budget: welcome to your first lesson in creating a personal budget. To save for a goal, we're gonna start by mapping out your year. What you're gonna do is figure out your income, then next you're gonna categorize your expenses, breaking down everything, and then you're gonna come up with a savings goal. So go ahead and pause for a minute and write these things down so you can dive in headfirst ones. We start mapping out the template, so hopefully you've written everything down. If not, you're gonna learn how to create the template anyway. And you can implement all of this later. As you can see here, I've written down all of the categories of my expenses on the left hand side and at the top have selected year and month asthma categories begin filling these things out. You need to select a cell and type whatever your expenses are in the month I put in 1000 and in the year I'm gonna multiply that 1000 by 12 by selecting it, hitting the equal sign and then typing 12 times and I select the category or sell that I want to multiply it by. You'll see that it turned into the $12,000 which is 1000 times 12. After that, I'll keep going down the line and filling out each of the categories accordingly. So next I'll go buy food. And truthfully, it doesn't have to start with a month. You can know how much you want to spend per year on that and calculate vice versa. So, for example, in food you may think, OK, I know how much I want to spend per week so you can select the year in order to calculate how much you want to spend per week and multiply that by the number of weeks in a year. So I'm putting $100 per week, and I'm gonna multiply that by 52 because there's 52 weeks in the year and hit Enter from there, you can just divide the yearly total divided by 12 because there's 12 months in a year. You may notice that it's got a repeating decimal there, and you want it to be in a currency so you can select the cell and then select cell category on the right, and there's a drop down many of data format and just select currency, and that changes it. You can also do this toe all the cells by just clicking and dragging over all the cells you want to change and then changing the date of ormat again. So from there you're gonna do the same thing. You're gonna fill out each one of these categories entertainment, travel, car insurance, etcetera. There's a lot of categories that you could have expenses in that I don't so think of all of the categories that you have and fill them out accordingly. As you saw before. It doesn't matter if it's starting with the month. You may have an idea of how much you want to spend per week per month or per year, and from there you can calculate everything based on that as we went over briefly. If you want to start with a year, you can select the total number that you want to spend in the year and then divided by 12 in order to get your month. If you want to do weekly, you can select a year at the equal sign right the amount that you're gonna spend per week and then multiply that by 52 because there's 52 weeks in a year. Then you select the month and divide that your sell by 12 in order to get your monthly total. Finally, if you want to calculate by month, you select them on cell. How much you want to spend? Then select the year cell and multiply the month cell by 12 in order to get the yearly total. Once you've got that all filled in, you can calculate the living total for the year. Calculate the living total. You're gonna need to use what is called a function. To do that, you're gonna have to select the cell hit the equal sign, which will bring up this box that you see on the screen right now. And then you're gonna type in the function name. This one is gonna be called some because you want to sum up the total of everything in the column that's above it. Do that type in some and click the some there at the bottom and you'll notice that it's got something called value. You can go to the top and click and drag over all of the numbers that you want summed up when you hit return. You'll get a total number. You can repeat this process to calculate the total for your month as well, so just select cell type in some, select some and drag over all of the numbers in order to get the total for your months. 3. Setting Up The Monthly Template For Tracking Expenses: The next thing we need to do is set up a monthly template where you can track your expenses each month and figure out if you're hitting your budget marks or not. To get started, you're gonna create a new tab that is for monthly spending. You're gonna do so by creating the same categories of expenses and then have your columns be labeled spent and remaining. Do you have that set up? You can click on the rent category in the spent column, hit the equal sign and then click over to the budget sheet. From there, you can click on the cell pertaining to the month rent spending. Then you hit return. It will pull up $1000 because that's your rent, because you have to expend that exact amount every month and rent. That's all we're going to do for that section. But for the rest of the categories, we're gonna put the amount you've spent and then the amount remaining in your budget. That way you can figure out whether you're on track to hit your goals, so let's start with food. Click the remaining cell, hit the equal sign and then select the budget cap then select the month category for food, which is $433. In this circumstance, go back over to your June template, and then what you're gonna do is hit minus spent category because you want to subtract your budget minus how much you've spent in the spent cell. We're going to set it up to track your expenses, which you will record in the parallel cells. Aligning with the food category. Okay, so let's get it set up. Select the cell, hit the equal sign, and then that will bring up the function box. As usual, we're gonna do this some of all of these cells. So if you click and drag all the way across, it'll highlight all of them, as you can see in the picture, currently at zero, because you have nothing entered there. Obviously, my format changed. So let's change that backed currency. Now that it's set up, let's see how it works, so you'll notice that highlights that when you select the remaining. That's because you're subtracting there spent from the remaining, and if you input a number, let's say you spend $40 at the store. You can hit it right there. It will show that you've spent $40 subtracted it from the remaining in your budget. Now that we see that it's working, let's reset it and fill in. The rest of the cool things about programs like Excel and Numbers is that you can duplicate the work you've already done. Let's show you how to do that. To make things more efficient, click the remaining category that you already done. Grabbed a little yellow dot click hold and drag all the way through transportation. You'll notice that these are the same numbers that are in your budget template. Now you're gonna go back and do the same thing in the spent category. So grab the yellow dot in the food one. Drag it all the way down and you're all set up. You'll notice that if you and put numbers, it's subtracting from the remaining and adding to the spent. In that way, you can track all of your expenses in every category, but you did it a lot faster than in putting each one of these individually like you do in the budget page. So the next thing we'll do is fill out the totals to show you how to work towards your savings goals. 4. Setting Up Totals for Tracking Savings: Okay, So in this lesson, let's set up your totals for tracking your total savings. So the first thing we're gonna do is select the spent total month cell. Then we're going to sum that up, using the some function drag over the cells that you want to some and hit return again. You'll see that the current total is 1000 just because all you've spent is your 1000 on rent. The rest of the categories need to be filled in, and they'll increase and increase the total. As time goes on and you have more entries, then we'll do the same thing here on the remaining category equals type in some, select some drag over all of the categories and then hit return. Once you do that, it will come up with the total month remaining. So it'll do the opposite as the spend category. Once you have the total month for spent in remaining, we can start to figure out the total month savings that you're gonna have to do. So this is where you're gonna need to know your income. So we're going to select the budget tab and under the Year column at the very bottom. We're gonna type in your salary. So let's say that was $50,000. You're gonna type in $50,000 subtract it by the living total. That would come out to $20,000.680 in this circumstance, and that's how much you have left to save. So let's go ahead and label that as your savings so we can remember what it is. So then, in that same row under the Month column, to do so, go ahead and select the cell under the Month column, and then it equals select the year savings and just divided by 12 because there's 12 months in a year. Once you have those filled in, we can go back to the June expenses and figure out your total month savings based on much expense. So go ahead and select the total month savings. Hit the equal sign, and then from there, we're gonna go to the budget tab, select the month savings. Add that with over in the June expenses, the total remaining. What this is going to do is take your total month savings that you have and then add whatever you have left in remaining in your budget. This will work even if you have a negative in that total month remaining because it'll just reduce the amount that you have saved. At that point, you can go to the total savings and then make that equal to the total month savings. That formula will actually change when you go to the next month. So when you get that in place, what we can do is copy and paste all of the June expenses. You do that by dragging over all of the cells, hitting command, see or control. See if you're on a PC selecting the upper left hand corner and pasting it into the July tab . You noticed that the total savings remains the same. So this is why we have to change it in each successive month. To do this, select the total savings tab it equals, Then go back to the June expenses, select the total savings of that month. And then what we're gonna do is add that to the total savings of your current month. And if you hit return, you'll get your total savings. That includes June and July. If you do this for each month, then you'll be able to calculate the total that you've saved ever since you started tracking this. Total savings is where you're gonna be able to see whether you're getting close to the goal that you want to save for your total expenditure, like a trip or maybe a down payment on her home. 5. Conditional Highlighting For Tracking Patterns: In this final lesson, we're gonna set up conditional highlighting for tracking patterns in your spending habits to start select the cell that you want to highlight and go over to conditional highlighting on the right hand side than at a rule. Then what we're gonna do is select less than or equal to, because we want to know whether you're spent category is less than or equal to the total amount that you've allotted in your budget. And once we select less than or equal to, we're gonna give it a fill color. We don't want it to be read, because if you're less than your budget, you want it to be green, something that reminds you that you're on track. We'll switch it to read when it gets over budget. So from there you can fill in the function. You do that by selecting the blue button on the right side of that cell, select the budget tab and then select the cell that corresponds with the category. The trim in this circumstance is the month of food. You'll notice that it turns green because you've spent less than or equal to the amount in the budget, so now we wanted to change colors When we go over budget to do that, we'll add another rule by selecting the at a rule button and then going to greater than because we wanted to turn red when you go over budget in the category that we're working on . So go ahead and press the function button that a little blue button on the right hand side of rule to go over to the budget tab and then select the monthly expense for food again. This time, we're gonna change it to read Phil, so notice that if you type in a number that's greater than your budget, it'll turn red. So you spent 500. That's more than what you want. And if you should have been 400 you'll notice that that turns back to green because that's under your budget. To do this for all of the categories, do the same thing as we've done previously by dragging the little yellow dot over each of the categories. Finally, well, to repeat this process for the total month, you have to do it separately because it's got underlying functions that are different. So go back to adding a rule do less than or equal to. We're gonna fill a conditional highlighting with green this time because we're under budget that must like the function blue button on the right. Go to the budget tab, select a living total and hit return. That'll fill your conditional highlighting for this. So at another rule again to the greater then And then we're gonna fill this with red, um, by selecting the drop down and hitting red and then fill in the function, go back to the budget tab, hit the living total category again and hit return. So at this point, your sheet is all set up and ready to use. You can start in putting and values, and it will change your budget accordingly. You'll be able to tell how much you spent whether you've gone over and use the conditional highlighting to do so, Um, and you'll be able to track how much you've wanted to save in the total savings with month to month. Finally, if you have any questions, feel free to leave a comment below and ultra and answer your question as soon as I can. Thanks for taking my course, and I look forward to working with you on my next one to shamelessly plug for my next course. I'm gonna teach a course on keynote and how to create the slides and animation involved with it. You can do a lot more than I did here, which I'll show you in my next class, so make sure to follow me on skill share if you want to be updated when my next class publishes.