Hey everyone, thanks for joining me for my third post here on the new CNA Finance. On the old CNA, I wrote a whale post about how to make a budgeting spreadsheet, and readers seemed to love it. It was the most shared post in my website’s history through social channels. It was awesome!
The only problem was that I used hypothetical scenarios to make the post. When I actually made my own spreadsheet, I realized that I needed to include much more data, simplify the process of accessing that data, and make it fun! So, I’ve decided to start from scratch!
The truth is, I haven’t made the new budget spreadsheet yet. This time, I figured I’d use my personal data and, document the process to let you, my readers, in on the entire process step by step. So, without further ado, let’s get started…
Time To Make The Spreadsheet
If you would like to see any of the pictures up close, click on it and it will open a blown up version in a new tab for you!
Step #1 – Decide Which Program To Use – When it comes to spreadsheets, there are 3 sources that seem to work out best for me. They’re Google Drive (which I’ll be using for this tutorial), Microsoft Excel, and OpenOffice.org Calc. All you need to do is choose the one that you feel most comfortable using. Because I like to be able to access my files everywhere, I often use Google Drive and, it’s going to be my choice for this sheet.
Step #2 – Start Your Spreadsheet – For this tutorial, I’m going to assume that you’re using Google Drive like me. That being said, to start a new spreadsheet, simply log in and click “Create”. Now from the drop down menu, choose the “Spreadsheet” option. Once you click this option, a new tab will open in your web browser with an empty spreadsheet. Now we’ve got our foundation, let’s fill it with data!
Step #3 – It’s Time To Name Your Spreadsheet – You’re going to want to be able to easily access your spreadsheet when you want to find something out, add data, or just see how you’re doing in the different areas of your budget. Therefore, you want to add a name to your budget to make it easy to find when you log into your drive. To add a name to your spreadsheet, all you need to do is click in the upper left hand corner where the words “Untitled Spreadsheet” appear. When you do, a little pop up will come with a field to enter the new name of your spreadsheet. Name your budget whatever you’d like and click “OK”. The popup box will disappear and you will now see your new title in the upper left hand corner. If your title has updated, you got it right!
Step #4 – Title Your Cells – As I said above, I haven’t made the spreadsheet so I’m not 100% sure what I’m going to include in it. That being said, lets start by making sure we include all monthly bills. We are going to want to add titles to an area of the spreadsheet for this. So, starting in Row 1, Cell A, let’s create title cells to track our bills. In Cell A:1, type in “Payable To”, in cell B:1, type in “Amount Due”, in cell C:1, type in “Pay To Address”, and in cell D:1, type in “Due Date”. If you’ve got it correctly, your spreadsheet should look like the image to the left. Don’t worry, I know it looks a little bland now. We’ll be adding tons of data and color to the spreadsheet in the upcoming steps!
Step #5 – Updating Your Monthly Bill Information – Now, add in any monthly bill making sure to include all information about that bill that is requested. With regard to the pay to address, you can amend that by placing in Pay by Pone, Pay by Email, ect… You can also add in the website you pay that bill at. No matter how you decide to pay your bill, just make sure that there is a reminder there that shows you what you need to do when it comes time to pay. Also, don’t trust yourself when filling out this page. You want to make sure to go grab your statements. This way, you’re sure of how much money you will need to send to the monthly bill and more importantly that you’ve added all of your monthly bills to the sheet. There are few things more disheartening in budgeting than when you realize that you’ve forgotten a bill and have to move things around to account for it.
Step #6 – Make Your Monthly Bills Easier To Read – I don’t know about you but, nothing is less appealing to me than a spreadsheet with nothing but a munch of white space and black lettering. When you have a spreadsheet that lacks color, it’s so easy to get lost in the data and difficult to find exactly what you’re looking for when you’re looking for it. So, lets add some color to make it easier to find things on a linear model like a spreadsheet. Let’s start by outlining the title cells. To do so highlight the title cells(Cells A:1 – D:) by clicking on A:1 and dragging your mouse to D:1. Now, click the paint bucket icon in the icon tray at the top of your screen. When you do, a color pallet drop down will appear. Choose a color that is appealing to you and click on it. When you click the color you’d like, the color of the cells you’re editing will change. I like to chose a dark color for title cells which can make it hard to read the text within them. To change the text color, simply highlight the cells again and click the capital “A” to the left of the paint bucket. When you click on it, a color pallet will come down. Choose white to add contrast to the cell.
Now, don’t stop there. Title cell colors are good but, you want to be able to quickly see the information you need. Color can help with this. Highlight the cells below the titles and choose a couple different shades of your main color to make clear lines of data. When you’re done, your sheet should look like the one in the picture with the colors that you’ve chosen.
Step #7 – Adding A Total To Your Monthly Bills – It’s definitely cool to know what monthly bills you have to pay, how much money the bills are individually, who they need to be paid to and when by, but we’re missing one thing. A very useful piece of information that’s missing here is the total amount owed monthly! Underneath your final monthly bill, in the “Payable To” cell, type “Total”. In the cell directly to the right of it, type “=sum(“, click on the first amount due, drag your mouse to the last amount due, and type this symbol, “)”. If you’ve done this right, your cell should look like the one in the image to the left. If so, hit “Enter” on your keyboard and walla, you’ve got a monthly bills total! When you’ve got your total, use what you learned in step $6 to add color to this data and make it easier for your eyes to target
Step #8 – Add An Area For Tracking Income Into Your Spreadsheet – Tracking your income is a very important part of budgeting. Many people have more than one source of income and, without proper tracking may not know how much money they have available to allocate to their monthly necessities. That being said, leaving one blank cell to the right of your monthly bills section of your spreadsheet, starting in cell F:1, type Income source. Now in G:1 type Net Income Amount. Now, start to list your income sources. For instance, my income sources are my salary and my blog. Well, my blog isn’t making any money right now but, I anticipate that it will some time in the near future. So, for the total income for my blog, I put 0 and I will update that when I start to get some money in. Now. Below your income sources, type “Total” and using the “=Sum(” trick from step #7, add up your total monthly net income. When you’re done add a bit of color and let’s move on to the next step.
Step #9 – Adding Miscellaneous Monthly Expenses – The fact that you don’t get a bill in the mail for monthly food costs doesn’t mean that you don’t pay for food throughout the month. There are a few expenses that the average household has that they don’t necessarily get a bill for. Let’s take a minute to add those in. To do so, skip one cell below the “Total Income” cell and type in “Random Expense”. In the cell directly to the right, type in “Total Cost”. Now, fill out the cells below. For my random expenses, I have food which is a total of $400 per month and gas which is a total of $255 per month. Below the last expense, type “Total”. In the cell to the right of total, use the “Sum” trick I showed you earlier to total it up. Now of course we’ve gotta add a bit of color and, we’re off to the next step. Of course if you want a good size example of what it should look like when you’re done, click the image to the left!
Step #10 – Time To Create The Debt Profile – Any good budget spreadsheet is going to include a debt profile. This is a simple area that lists all of your debts and information you need to know about them. It helps you to prioritize your debts and get them paid off faster. To add a debt profile to your budget spreadsheet, skip a row below your “Total” monthly bills cell and type in “Debt”. In the cell to the right, type in “Account Number”, type “Amount Owed” to the right of “Account Number”. Finally, to the right of “Amount Owed”, type “Interest Rate”. Fill in the cells below to complete your debt profile. You can look to the image on the left for an idea of how this should look. Of course, I blocked out my account number using a nifty little Microsoft program called Paint for security! When you’re done adding in the information on your debts, make sure to add a bit of color to make it more fun to look at!
Step #11 – Create A Fun Budget – As human beings, we are social creatures. We like to have fun in social atmospheres, and without proper guidance, this can become very costly. Therefore, it’s best to create a fun budget to keep your fun time spending in check. Your fun budget should be a very small portion of the amount of money that you have left over after paying for all of your monthly bills and your miscellaneous expenses. For Ana and I, I’m going to set our fun budget to $100 per week which should be plenty! At $100 per week, given that most months have 4 weeks in them, we’ll just average it out at $400 per month. To add that in, below your Random expenses area, skip a row. In the cell below the skipped row, type “Fun Budget”. To the right of that, type “Total Spent So Far”. Now, add your fun budget total into the cell below the title “Fun Budget”. As you spend, add the total up and put it in the cell for your total spent so far. Below your budget total cell, type in “Available Spending”. To the right, type “=”, click your fun budget total, type “-“, and click your total spent so far cell. Now, hit enter on your keyboard and you will have the total you’ve got left available for fun spending.
Step #12 – Add An After Expenses Total – When coming up with savings and investing goals (which we’ll get into a bit later.), it’s going to be helpful to know how much money you’ve got after all of your monthly expenses are totaled. Doing so is pretty simple. Skip a row below your “Available Spending” cell. In the cell you land on, type “After All Expenses” and color the cell like you would any title cell. Now, in the cell to the right, type “=”, click your total income and type “-“. Now click your total monthly expenses, type “-“, click your total random expenses, type “-“, click your fun budget and hit “Enter” on your keyboard. When you do, the total the spreadsheet gives you should be the total amount of money that you have left over after all of your expenses are covered.
Step #13 – Add Your Savings Goals To Your Spreadsheet – Finally, it’s time to track your savings. Lets face it, if you don’t track the progress of your savings and investment accounts, chances are, you won’t make it a point to save every month. So, after skipping 1 row below your final debt, type in “Monthly Savings Goal”. In the cell to the right, type “Saved So Far This Month”, to the right type “Need To Save This Month”, and to the right of that type “Total Savings/Invested”. Now, in the cell below “Monthly Savings Goal” type in the total you have after all expenses. As you put money into savings, update the “Saved So Far This Month” cell. In the cell below the “Need To Save This Month” title, type “=”, click on your monthly savings goal, type “-“, click on the total you’ve saved so far this month, and hit “Enter” on your keyboard. Now your spreadsheet will automatically calculate how much money you still need to save for the month. Finally, add the total amount of money you have saved ever in the empty cell below “Total Savings/Invested”. Now, you’ve got a complete budget spreadsheet!
Common Budget Spreadsheet Questions
Question #1- Is it best to use a budget spreadsheet or an online service like Mint.com?
Answer – That really depends on what you are comfortable with. Although, I think Mint is a great service as you can see in my review of them over at Modest Money, some people aren’t comfortable with keeping financial information online. As far as capabilities go, services like Mint.com are usually much better than spreadsheets. However, actually using online services depends on how comfortable you are with that information being online.
Question #2 – What information should a budget spreadsheet contain?
Answer – A good budget spreadsheet should contain any information you would need with regard to your financial life. For instance, it should be able to track monthly spending, monthly bills, income, savings, investments and more. If it’s got to do with your overall financial picture, it should be in your spreadsheet.
Question #3 - What Is The Best Service Available For Making Spreadsheets?
Answer – There really is no clear cut and dry answer to this question. The truth is, the program that you use is all left up to use preference. I personally love Google Drive but, you may have the same feelings about OpenOffice.org Calc or Microsoft Excel.
Great Budgeting Resources
Mint - Found at Mint.com, Mint is an online software that does an incredible job of tracking just about every detail of your personal finance portfolio. They help make budgeting recommendations, track investments and more!
Penny Pinchin’ Mom’s Downloadable Budget Spreadsheet - Tracie Fobes at Penny Pinchin’ Mom created a great budget spreadsheet. You can download it here!
A Great Post By The Fiscal Flamingo – Taynia wrote a great post about budgeting when you’ve got changing income. Check it out here!
Do you use a budget spreadsheet to track your finances?