How to Make a Zero-Based Budget in Microsoft Excel

by
Spread the love

Have you ever reached the end of the month, looked at your bank account balance, and thought, “Where did all my money go?” If you’re ready to start planning where your hard-earned cash is spent, it’s time to try a zero-based budget. This guide explains this method of budgeting and shows how to create a zero-based budget in Microsoft Excel.

Content

What Is a Zero-Based Budget?

With a zero-based budget, you plan your monthly spending until the end result is zero. Begin by listing your income and expenses like most 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 expenditures, then place those remaining funds in a sensible spot. For instance, if you have $100 left over, why not put that money into your savings account or retirement fund rather than spend it on items that you don’t really need.

For personal budgets, the idea isn’t to spend every penny you earn but to allocate the money you make 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 seek out lower costs for expenses by assigning only a specific amount per item.

Now that you know what it is, let’s look at a few ways to create a zero-based budget in Microsoft Excel.

Use a Zero-Based Budget Template

One of the best ways to get started with your zero-based budget and see whether it works for you is to use a template. These two budget templates give you the categories you need and include all formulas for the calculations. Just enter your amounts, and you’re good to go.

Zero-Based Budget Template in a List Format

This first zero-based budget option comes from Smartsheet. You can download the Excel template for free and get straight to planning.

Using a list format, there are separate areas for income and expenses with individual spots 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.

Head to the bottom for your totals and remaining balance.

Zero-Based Budget Template in a Sectioned Format

This next zero-based budget template comes from Vertex42 and is also available in Excel at no charge.

You’ll see color-coded sections for income and expenses, with plenty of detailed items. For instance, you can enter your gross income and add the taxes and other paycheck deductions. For expenses, there are spots for small bills, big bills, and daily living.

This template also includes both “Budget” and “Actual” columns for your planning and confirmed amounts. At the bottom, you’ll see the final balance surrounded by a dark red border, making it easy to spot.

A couple of 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 an “Example” to help you get started, use the “Printable” tab if you want a physical copy, then, of course, you have the blank “Budget” tab when you’re ready to get to work.

Create a Zero-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 any way you would like. Below, you’ll learn how to whip up a zero-based budget sheet in Excel in just minutes.

Set Up Your Income and Expenses

  1. Start by entering the budget name and a spot for the month at the top. Optionally, set up two columns, as in the templates above, for Budget and Actual amounts. For this tutorial, we are using only one column for the planned amounts.

  1. Add the area for your income. You can use your gross income and include another section for taxes and deductions or simply add your net income. If you have more than one source of income in your home, list those on separate lines, then include a subtotal at the bottom of the section.

  1. Add a section for expenses with a subtotal at the bottom. You can enter all of your bills, debts, and obligations in one long list or break them up into sections. For our purposes, we are listing several common expenses.

  1. Below 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 fixed bills or debt. Again, include a subtotal.

  1. Finally, add a section for savings accounts, like a retirement or college fund, an emergency account, or a vacation savings with the subtotal.

Enter the Subtotal Calculations

Once you add all of the above items, it’s time to enter the calculations. You can do this using basic rather than advanced functions and formulas.

  1. Go to the cell to the right of the first “Subtotal” cell and head to the “Home” tab. In the “Editing” section of the ribbon, select “AutoSum.”

  1. When you see the SUM formula in the cell, select or enter the cell range for the values above it. For example, we drag through cells B4 and B5 to select our income sources. Press Enter or Return to save the formula in the cell.

  1. Do the same for the other Subtotal cells in the sections.

Determine the Final Totals and Remaining Money

  1. At the bottom of all sections, you can enter the grand totals, then subtract the expenses from the income.

  1. To the right of “Total Income,” enter an equal sign and the cell reference for the income subtotal. In our case, this is cell B6. Press Enter or Return.

  1. To the right of “Total Expenses,” enter the formula to add those subtotals. To add the amounts in cells B18, B25, and B31, you’d use this formula:
=SUM(B18,B25,B31)

  1. Subtract Total Expenses from Total Income. Enter an equal sign and subtract the amounts using the following formula, replacing the cell references with your own:
=B33-B34

Complete Your Budget

Enter your income and expense amounts on the sheet to see the totals and remaining money.

Remember, you want the final amount to be zero. Add money to other expenses, such as your savings account or retirement fund, until your remaining amount equals zero.

Optionally Format the Sheet

If you want to spruce up your custom zero-based budget, you can use Excel’s font formatting section in the “Home” tab.

For instance, you can increase the font sizes for the name and month, give the income and expense sections different fill colors, or bold the section headings. It’s all up to you!

Frequently Asked Questions

Who introduced the zero-based budgeting concept?

The zero-based budget was developed in the 1970s by Peter Pyhrr, an accounting manager for Texas Instruments. The system was built to help management allocate funds based on current needs rather than historical spending. Because you start each month at zero, the intent is to more effectively plan expenditures, seek lower costs, and get approvals for all expenses.

How do you use the 50/30/20 rule in zero-based budgeting?

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 sections for your zero-based budget if you want to try the method.

What if my final amount is below zero?

The intent of zero-based budgeting is to plan where your money will go. If you wind up with a result below zero, you’ll obviously need to decrease the amount you’re spending.

Because expenditures like rent, mortgage, and utilities are normally mandatory and fixed, consider lowering the amounts you add to your variable expenses, like entertainment, dining out, or clothing. However you decide to spend your money, the point is that you’ll plan it ahead of time.

All screenshots by Sandy Writtenhouse.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe


Sandy Writtenhouse

With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.

Comments are closed