Have you ever reached the end of the month, looked at your bank account balance and thought, “Where has all my money gone?” If you’re ready to start planning where you spend your hard-earned money, it’s time to try a zero-based budget. This tutorial explains this budgeting method and shows how to create a zero-based budget in Microsoft Excel.
What is zero-based budgeting?
With a zero-based budget, you plan your monthly spending until the end result is zero. Start by recording your income and expenses, like any other type of budget. The difference is that after you subtract your expenses from your income, your goal is for the remainder to be zero.
If you have money left over, you should review your expenses, and then put the remaining funds somewhere that makes sense. For example, if you have $100 left over, why not put that money into your savings account or retirement fund instead of spending it on items you don’t really need.
For personal budgets, the idea is not to spend every penny you earn but to allocate the money you earn by planning ahead of time where it will go. This can decrease the likelihood of frivolous spending and increase the probability of saving more.
For business budgets, this method can help you find lower costs for expenses by allocating only a specific amount to each item.
Now that you know what it is, let’s look at some ways to create a zero-based budget in Microsoft Excel.
Use a zero-based budget template
One of the best ways to start with zero-based budgeting and see if it works for you is to use a template. These two budget templates give you the categories you need and include all the formulas for the calculations. Just enter your amounts, and you’re good to go.
A zero-based budget template in list format
This first zero-based budget option is coming Smartsheet. You can download the Excel template for free and start planning right away.
Using a list format, there are separate areas for income and expenses with individual points for debts and adjustable items.
Enter your planned amounts in the “Budget” column and your actual amounts in the “Actual” column when the time comes.
Go to the bottom for your totals and remaining balance.
A zero-based budget template in segment format
The next zero-based budget template is coming Vertex 42 And also available in Excel free of charge.
You’ll see color-coded sections for income and expenses, with lots of itemized items. For example, you can enter your gross income and add taxes and other payroll deductions. For expenses, there are places for small accounts, large accounts and everyday life.
This template also includes “budget” and “current” columns for the plan and approved amounts. At the bottom, you’ll see the final balance surrounded by a dark red border, making it easy to identify.
Some nice features of this option is that it includes a difference column and additional tabs. You can see the difference between the “budget” and “actual” amounts you enter.
You can also view a “Sample” to help you get started, use the “Printable” tab if you want a physical copy, and then, of course, have an empty “Budget” tab when you’re ready to get to work.
Create a scratch-based budget from scratch
While the templates here are great for a quick start on your budget, you can also create one yourself if you prefer. This way you can enter the exact items you want and design the sheet in any way you want. Below, you’ll learn how to create a zero-based budget spreadsheet in Excel in just a few minutes.
Define your income and expenses
- Start by entering the name of the budget and a place for the month at the top. Alternatively, set up two columns, as in the templates above, for budget and actual amounts. For this tutorial, we only use one column for the budgeted amounts.
- Add the area to your income. You can use your gross income and include an additional item for taxes and deductions or simply add your net income. If you have more than one source of income in your household, list them on separate lines, then include a subtotal at the bottom of the section.
- Add a section for expenses with an interim summary at the bottom. You can enter all your bills, debts and obligations in one long list or divide them into parts. For our purposes, we list a number of common expenses.
- Under expenses, you can add a section for adjustable, variable, or miscellaneous items. You might include groceries, entertainment, dining out, clothing, childcare, and similar items that are outside of regular bills or debt. Again, include a subtotal.
- Finally, add a section for savings accounts, such as a retirement or college fund, an emergency account, or vacation savings with the subtotal.
Enter the subtotal calculations
Once you’ve added all of the items above, it’s time to enter the calculations. You can do this using basic rather than advanced functions and formulas.
- Go to the cell to the right of the first “Subtotals” cell and go to the “Home” tab. In the “Edit” section of the movie, select “Auto Sum”.
- When you see the SUM formula in a cell, select or enter the range of cells for the values above it. For example, we drag through cells B4 and B5 to select our sources of income. to press to enter or Return to save the formula in the cell.
- Do the same for the rest of the subtotal cells in the sections.
Determine the final amounts and the remaining money
- At the bottom of all the sections, you can enter the large amounts, and then subtract the expenses from the income.
- To the right of “Total Income,” enter an equal sign and the cell reference for the subincome. In our case, this is cell B6. to press to enter or Return.
- To the right of “Total Expenses,” enter the formula to add these subtotals. To add the amounts in cells B18, B25, and B31, use this formula:
- Subtract the total expenses from the total income. Enter an equal sign and subtract the amounts using the following formula, replacing the cell references yourself:
Complete your budget
Enter your income and expense amounts in the sheet to see the totals and money left over.
Remember, you want the final total to be zero. Add money to other expenses, such as your savings account or retirement fund, until your balance equals zero.
Optionally format the sheet
If you want to enhance your custom budget from scratch, you can use Excel’s Font Formatting section on the “Home” tab.
For example, you can increase the font sizes for the name and month, give the income and expense sections different fill colors, or highlight the section headings. its up to you!
Common questions
Who introduced the zero-based budget concept?
God Zero-based budgeting Developed in the 1970s by Peter Fehr, an account manager for Texas Instruments. The system was built to help management allocate funds based on current needs rather than historical expenditures. Since you start each month with zero, the intention is to plan your expenses more efficiently, look for lower costs and get approvals for all expenses.
How do you use the 50/30/20 rule in a zero-based budget?
With the 50/30/20 rule of budgeting, the idea is that you spend 50 percent of your income on needs, 30 percent on wants, and 20 percent on savings.
When you plan your monthly budget, you can allocate the remaining amount above zero using this concept. You can also use the three categories as the headings for your zero-based budget if you want to try the method.
What if my final amount is less than zero?
The point of a zero-based budget is to plan where your money will go. If you reach a result below zero, you will obviously need to reduce the amount you spend.
Since expenses like rent, mortgage, and utilities are usually mandatory and fixed, consider deducting the amounts you add to your variable expenses, like entertainment, eating out, or clothing. However you decide to spend your money, the point is to plan ahead.
All screenshots by Sandy Writtenhouse.
Sign up for our newsletter!
Our latest tutorials are delivered straight to your inbox