How To Create a Budget Spreadsheet to Make Your Financial Goals a Reality
In a recent article publised on E!, Financial Guru Suze Orman listed her top five tips and tricks that will put you on the path to financial success for 2014. Her tips, designed to keep you on track and on budget for the year, include paying as you go using your cash or a debit card to ensure you only spend money you actually have, rather than spending on credit. She also suggests you pay yourself first using a debit order to make sure you are saving what you set out to save each month.
If you would like to learn how to set up a budget from scratch, then sign up for the Jean Chatzky’s Money School: Budgeting Bootcamp course now for guidance and tools that will help you to set up and stick to a monthly spending plan.
This tutorial will show you how to set up your own personal budget spreadsheet. This is a great tool that will allow you to spot budgeting problems easily on a monthly basis. The steps to set up your own budget include:
Create a new Excel worksheet
Determine your income
Determine your fixed expenses
Determine your variable expenses
Make provision for funds you may need
Create your financial goals
Ideas on how to kick start your emergency fund
Analyze your budget
Track your expenses
With all the talk of the credit crunch and dwindling savings, it’s about time that everyone invested their time in creating a personal budget. Let’s examine some of the top reasons you need a budget in the first place.
Top 5 Reasons Why You Need a Budget
A budget helps you to specify and achieve financial goals. Without a clear roadmap for success, it is really hard to achieve anything in life, and financial success is no different. A budget allows you to view your financial position from a perspective that allows you to spot opportunities and also make plans on how to best invest for your financial future. People often shy away from preparing a budget because a lot of figures and math is involved, but with Excel, you no longer have that excuse. Excel makes setting up a personal financial budget really simple. For a course on how to use Excel 2013, sign up for the Excel 2013 course today from Udemy.
A budget helps you to prioritize your spending. If you move haphazardly through your financial journey you will often be faced with times where you would love or need something you cannot afford. By planning for such events in the future, a budget allows you to create a nest egg ready for when these events pop up on your journey.
A budget gives you a plan to help you build wealth for your future. You need a good overall view of your finances before you can take charge of your financial future. Only once you know how much money you are really making and how much you are spending, can you come up with plans to reduce unnecessary spending and make the most of your income to build wealth for your future.
A budget helps you create a slush fund for emergencies. When you have a clear idea of your financial position you can make arrangements for extra funds to be saved for emergency purposes. None of us think that it will happen to us and yet it inevitably, eventually does happen. Having a budget means you have peace of mind should you lose your job or face other unforeseen emergencies.
Finally, a budget should provide for your retirement and smart budgeting means you can afford to retire and still live the life you’ve become accustomed to.
So now that you know why a good clear budget is essential, let’s examine how you can set up your own personalized budget spreadsheet.
Create a new worksheet for your budget spreadsheet
My personal preference for setting up a budget is using an excel spreadsheet. With over a hundred million users worldwide, Excel takes the math out of creating and following your own personal budget. If you are new to Excel then sign up for the Excel Course (Basic and Advanced) to learn how to setup a spreadsheet to create your own personal budget spreadsheet.
We are going to start creating our budget using a blank worksheet. To create a budget we need to create a column for each month of the year and then we need to create rows to reflect our income, expenditure and savings so that we can get a clear indication of our financial status and so that we can keep track of our performance throughout the coming months.
To create a worksheet, open Excel and click new. Choose Blank workbook. This will give you a blank worksheet to begin setting up your own your personal budget spreadsheet. Set up a wider cell for income and expense descriptions and then create one column for each month of the year. Add totals for the columns and format the worksheet the way you want to.
This is what my initial spreadsheet looks like:
Now that you have the columns ready you can begin to work on the figures that you need to create the budget. We will also add some rows below our spreadsheet once we have the information we need.
The first figures you need for your budget is your income.
Determine your income
This is one of the easiest figures to determine on your budget. Any income that you receive must be included in these rows. Income generated from salaries, interest received, rentals received and any other income you generate should be included. Income should also include child benefits and tax credits you receive.
If you are taking out a loan the money received for the loan should be reflected in the income column. For the sake of this example, I have recorded income of $2500.00 dollars received as wages and interest of $100.00 for a interest I receive on my savings account.
Determine your fixed expenses
Next you need to work out your expenses. I have created to subcategories of expenses for my budget. One consists of the fixed monthly expenses and the other consists of variable expenses. Fixed monthly expenses are expenses that do not change from one month to the next. You always pay the same amount on fixed expenses. Fixed expenses include your monthly mortgage or rental payment, your phone rental amount excluding calls, payments you need to make on furniture and other items that are fixed.
One of the simplest ways to create a list of your fixed monthly expenses is to take your bank statement and make a list of the monthly payments that go through your account every month. This includes any debit orders or stop orders on your account.
For the purposes of the example, I have added rental due, phone rental, home insurance, auto insurance, a car payment and a cable/satellite television bill.
Determine your variable expenses
Variable expenses are a little more difficult to determine than fixed expenses. Variable expenses are expenses that change on a month to month basis. These expenses include telephone calls, groceries, clothing, gas, electricity and water, childcare costs and entertainment expenses.
To help you determine your variable expenses, you can start with your credit or debit card statement. Make a list of the various categories that you spend money on using your credit or debit card and then add up the totals for each category. To get a better idea of what you spend on these categories you should take your statements for the previous few months and average those totals.
Take all your bills and create an average for them. Items like mobile charges, telephone calls, clothing accounts are all a good source for working out how much you actually spend on these items per month.
You also need to keep your sales slips from the various stores for cash purchases to create an average for the amounts you spend on cash purchases.
Now you have the basics ready to create your budget. For a five step process on how to create your own budget, sign up for the Create a Budget that Works course from Udemy. It offers simple PDF’s and Excel spreadsheets that will take you through a step by step process of setting up your own personal budget.
Make Provision for the Various Funds You Want and Need
Now that you have a basic budget, it’s time to take a look at the funds you need that you don’t normally think about during the year. You need an emergency fund. This is a fund you create to make provision for emergencies like losing your job or unexpected health expenses.
According to Bankrate.com, you should have at least nine months worth of expenses saved as an emergency fund. Now that you have your budget you can work out what that amount should be by multiplying your total monthly expenses by nine. In our example we should have an emergency fund of $22 131.
Our budget does not include any funds for a vacation or special occasions. A budget is a great tool that allows you to take control of your finances and since you have taken the time to set up a budget, it’s a great idea to use that tool now to plan for things you want and or would like, like a vacation fund. I would like add a vacation fund to my budget in the example for a total of $2000.00.
Now that you have some financial goals in place and the information you need to take steps towards your financial goals, you can make some solid financial decisions based on this information.
Creating Some Financial Goals
First take a look at the shortfall/surplus you have at the end of the month. This is the starting point to creating a financial plan that works for you.
In our example I have added a column below the spreadsheet and subtracted the total expenses from the total income to work out our budget surplus or shortfall:
Now you can see, if you have a shortfall or surplus. The next step is to create some financial goals.
First we need to know how much we need to save for our emergency fund and our vacation fund. The vacation fund is for a yearly expense, the emergency fund is a savings account we will need for those unforeseen emergencies.
To create a vacation fund of $2000.00 you would need to save $166 dollars a month. I used the spreadsheet to divide two thousand by twelve to work out the monthly saving required.
The emergency fund is a lot larger and may seem intimidating but you can plan to save the funds, you just need to stay focused on your goals and be persistent. Start with a smaller goal for your emergency fund and then add to it as time goes by. Saving $22 000 would be a mammoth task. So instead of aiming for nine months worth of expenses this year, start by aiming for half that amount in the first year. So this year we are aiming for an $11 000.00 emergency fund.
Kick Start Your Emergency Fund
If you have not created a budget and looked at your financial situation before, then chances are you have some things that can help you now to kick start your emergency fund. Take a look at the things stacked in your attic or garage. Sell what you don’t use on eBay. It is amazing how fast the dollars add up. You may be surprised by what you find that you can sell to kick start your fund.
If you don’t have things to sell, then think about the ways you can create money for your fund. Perhaps you could arrange a few bake sales? Use your imagination to think of ways that you can use to kick start your emergency fund. Once you set your mind to it, you will find that it is easier to find a few thousand dollars to use as your base for your emergency fund
Now that you have a plan, start by opening a savings account for your emergency fund. It’s best to keep your emergency funds separate from your daily bank account to help you to avoid spending the money. A savings account also offers interest that will help you grow your emergency fund.
Let us assume you have managed to find three thousand dollars to kick start your emergency fund and you’ve placed it in a savings account. It’s time to analyze your budget and make it work for you.
Analyze your budget
One of the purposes of creating a budget is to see how to adjust your spending behavior to help you to meet your financial goals. Now that you have a basic budget spreadsheet, you can use that spreadsheet to analyze what you are spending money on and how you can save money to meet your goals.
Let take a look at our example spreadsheet:
Start with your fixed expenses. Take a look at each expense and think about whether there is a way to save on these expenses. Don’t assume that you can’t save. Call about your home and auto insurance agent. There is fierce competition in the insurance markets today and you may find if you call around, that there are companies offering better deals on these items.
Next, look at your variable expenses. See what expenses are really necessary and which expenses you can cut down on. Think about how you can save on each of these expenses. Perhaps a carpool for work could save some money on your gas bill. Pay attention to items on sale on your grocery bill. How often do you eat out and how much could you save by cutting out one outing a month?
Learning to live within your budget takes some planning and a shift in your mindset. For a course on how to live more frugally, sign up for Frugal Living from Udemy and learn how to save money effectively by saving money on your grocery bill and your other monthly living expenses.
By using water wisely, switching off appliances that are not being used, and by watching for specials and sales, our new budget looks like this:
We saved five dollars on the water bill, fifteen on the electric bill by unplugging unnecessary appliances, we saved one hundred dollars on our grocery bill by taking advantage of savings and specials, we decided to make our own homemade gifts for people, we cancelled our gym subscription and saved forty dollars a month by creating a carpool.
These few changes have translated to an extra six hundred dollars a month in savings that we can use to increase our vacation fund and our emergency expense fund.
This gives us the ability to add two more expenses to our monthly budget to ensure we meet our financial goals. We will add $166 to our vacation fund and five hundred dollars to our vacation fund. Our new budget will look like this:
There is a twelve dollar surplus each month but our budget now includes savings for our vacation and emergency fund.
You now have a budget that takes your current expenses into account and also makes provision for your financial goals. Once you have a complete budget, it’s time to put your budget into practice.
Track your expenses
Putting your budget into practice means tracking your expenses on a monthly basis to make sure you are keeping the amounts you are spending within your planned budget. To create a budget spreadsheet to help you keep track of your progress is simple now that you have your budget.
First we will copy the figures into the next eleven columns to create a budget amount for each expense per month. These are our aims for each month.
Next we need to create an empty worksheet to keep track of what we actually spend each month. You can create this new table on the same tab or on a new tab. Copy the entire table. Click on a new tab and then paste the table into the new tab. Then delete the figures in your new table so that they are all zero.
Your new table should look like this:
This is the table you use each month to see if you are on track or not.
Now we need one final table that we can use to see at a glance where we are spending more than we want to or whether we are achieving our target.
In a new tab, copy the table again. Each cell of this table will take our budget amount and subtracts our actual spending from our monthly income and expenses table to see if we are on track. We will format the cells to show negative numbers as red cells so you can immediately see where you are overspending. For a course on mastering Excel to learn how to format cells and work in Excel, sign up for the Mastering MS Excel course now from Udemy.
Our final worksheet looks like this:
Evaluate your progress
Using the budget spreadsheet above makes it easy to keep track of your financial progress. Once you have taken the time to set up a budget then most of your work is done. Entering expenses once a month is really simple and quick and your final tab is a quick reflection of how you are doing in terms of your budget.
Budgeting is an ongoing process and by creating your own budget spreadsheet, you can keep an eye on whether you are achieving your financial goals. Go back every once and a while to see how you can adjust your budget to improve your financial position. When you decide to take control of your financial future, you will realise the benefits of using a budget to keep you on track.
For a great course on how to create your own budget, sign up for the Budget – Save – Win. Simplify your financial life course from Udemy today.
Business Budgeting students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.