How to Create a Zero Based Budget in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for a way to create a budget to spend your money in the right way then you can try a zero-based budget. You can create a zero-based budget in Excel very easily. The objective of this article is to explain how to create a zero-based budget in Excel.


Watch Video – Create a Zero Based Budget in Excel


What Is Zero Based Budget?

A zero-based budget refers to a budget in which you use every penny you earn for a specific purpose. It is an income and expense plan where you make a list of your expected income and expenses before a month starts. And then assign a specific amount to every expense category so that the end balance remains zero. This means for zero-based budget,

Total Income – Total Expenses = 0


Create a Zero Based Budget in Excel: Step-by-Step Procedures

Here, I will show you how you can create a zero based budget for yourself in Excel. By creating a zero based budget you will be able to track your income and expenses in a better way so that you don’t have to be in debt at the end of the month. And you will be able to cut unnecessary expenses. Let’s see the steps of creating a zero based budget in Excel.


Step-01: Make Table for Income

In this first step, I will make a table for income in the zero based budget in Excel.

  • In the beginning, create a table with four columns for Income. You can add rows as per your need.

Make Table for Income to Create Zero based Budget in Excel

  • After that, name the columns.
  • Here, I named my 1st column Category. In this column, I will enter the income sources.
  • Then, I named the 2nd column Expected. Here, I will enter the Expected Income.
  • The next column is Actual. Here, I will enter the Actual Income.
  • And the last column is named Difference. I will contain the difference between the Actual Income and the Expected Income.

Read More: How to Prepare Budget for a Company in Excel


Step-02: Enter Categories and Expected Income

Here, I will enter the Categories and Expected Income in them.

  • Firstly, enter your income sources in the Category column. Here, I entered mine. Salary is fixed income here. Business Profit and Interests are variable income.

Enter Categories to Create Zero Based Budget in Excel

  • Secondly, at the bottom of the Category column create a row for Total Income.

  • Afterward, insert your Expected Income for every Category into the table. Here, I inserted mine.

Entering Expected Income to Create a Zero Based Budget in Excel

  • Then, select the cells where you want to calculate the Total Income. Here, I selected cell C9.
  • Next, in cell C9 write the following formula.
=SUM(C6:C8)

Using SUM Function to Create Zero Based Budget in Excel

  • After that, press Enter and you will get the Total Income.

Calculating Total Income to Create Zero based Bedget in Excel

Here, in the SUM function, I selected cell range C6:C8 as numbers. The formula returns the summation of the cell range C6:C8.

Read More: How to Prepare Annual Budget for a Company in Excel


Step-03: Create Table for Expenses

Now, I will create a table for Expenses in the zero based budget in Excel.

  • First, create a four-column table for Expenses. You can add rows to this table as per your need.

Create Table for Expenses for Zero Based Budget in Excel

  • Next, name the columns.
  • Here, I named the first column Category. I will add the Categories for Expenses in this column.
  • Then, I named the second column Budgeted. Here, I will insert the Budgeted Expenses.
  • And the last column is named Difference. In this column, I will calculate the differences between Budgeted and Actual Expenses.

Read More: How to Create a Business Budget in Excel


Step-04: Enter Categories and Budget

In this step, I will enter the Categories for Expenses and the Budget for a specific category.

  • Firstly, insert the Category for Expense into the table. Here, I inserted mine.

Entering Categories and Budget to Create Zero Based Budget in Excel

  • Next, at the bottom of the Category column create a row for Total Expenses.

  • Afterward, enter Budgeted Expenses for every Category.

  • Then, select the cell where you want to calculate the Total Expenses. Here, I selected cell C19.
  • Next, in cell C19 write the following formula.
=SUM(C12:C18)

  • After that, press Enter to get Total Expenses.

Calculating Total Expenses to Create Zero based Budget in Excel

Here, the SUM function returns the summation of the cell range C12:C18.

Read More: How to Create a Project Budget in Excel


Step-05: Make Some Adjustments to Get Zero

Here, I will calculate the Final Balance. And then I will show you how you can make some adjustments to get a zero based budget in Excel.

  • In the beginning, create a row for Final Balance at the bottom of your table.

Making Some Adjustments to Get Zero Based Budget in Excel

  • Next, select the cell where you want to calculate the Final Balance.
  • Then, write the following formula in that selected cell.
=C9-C19

  • Further, press Enter to get the Final Balance.
  • Here, you can see that the Final Balance is -$100. This means the Total Expenses are $100 more than the Total Income. But for a zero based budget, the Final Balance needs to be zero. So, I will make some adjustments to make it zero.

Calculating Final Balance to Create Zero based Budget in Excel

Now, the formula subtracts the Total Expenses from the Total Income and returns the Final Balance.
  • After that, look where you can cut the Expenses. Here, I cut $50 from Personal. And then the Final Balance becomes -$50.

Looking For Unnecessary Expenses to Create a Zero Based Budget in Excel

  • Then, again look for a category where you can cut Expenses. Here, I cut $50 from Restaurants. And finally, the Final Balance becomes 0.


Step-06: Enter Actual Amount

Here, I will enter the actual amount for both Income and Expenses. This will help to compare the Budget and the Actual values.

  • Firstly, enter the Actual Income. Here, in the following picture, you can see that I have entered my Actual Income and it slightly varies from the Expected Income.

Enter Actual Amount to Create a Zero Based Budget in Excel

  • Secondly, select the cell where you want to calculate the Total Income. Here, I selected cell D9.
  • Thirdly, in cell D9 write the following formula.
=SUM(D6:D8)

Entering Actual Income to Create a Zero Based Budget in Excel

  • Then, press Enter.

Here, in the SUM function, I selected cell range D6:D8 as numbers. The formula will return the summation of the cell range D6:D8.
  • After that, enter the Actual Expenses in the table.

Inserting Actual Expenses to Create Zero Based Budget in Excel

  • After that, select the cell where you want to calculate the Total Expenses for actual expenses.
  • Next, write the following formula in that selected cell.
=SUM(D12:D18)

  • Then, press Enter to get the result.

Now, the SUM function returns the summation of the cell range D12:D18.
  • After that, select the cell where you want to calculate the Final Balance. Here, I selected cell D20.
  • Then, in cell D20 write the following formula.
=D9-D19

Calculating Final Balance for Actual Amount to Create a Zero Based Budget in Excel

  • Finally, press Enter.

Here, the formula subtracts Total Expenses (Actual) from the Total Income (Actual) and returns the Final Balance.

Step-07: Calculate Differences

In this step, I will calculate the difference between the Actual value and the value in the Budget. This will show if you were able to maintain the budget.

  • In the beginning, select the cell where you want to calculate the Difference.
  • Then, write the following formula in the selected cell.
=D6-C6 

Calculating Differences to Create a Zero Based Budget in Excel

  • Next, press Enter to get the Difference.

  • After that, drag the Fill Handle down to copy the formula.

Here, the formula subtracts the Expected Income from the Actual Income and returns the Difference.
  • Finally, in the following picture, you can see that I have copied the formula to the other cells.

  • In the end, calculate the Difference for Expenses in the same way.

Read More: How to Calculate Remaining Budget Using Formula in Excel


Final Output

In the following picture, you can see the final output. Here, I deleted all the input values so that you can use this as a template for your zero based budget. You can also change the categories as per your need. Or, you can follow the steps to create a zero based budget in Excel on your own.


Advantages of Zero Based Budget

You can create a zero based budget in Excel before the month starts to track your Income and Expenses in a more effective way. The advantages of a zero based budget are.

  • It helps to track Income and Expenses.
  • A zero based budget helps to cut unnecessary Expenses.
  • It helps to set a goal and then spend money according to that plan.

Download Practice Workbook

You can download the practice workbook from here.


Conclusion

To conclude, I tried to explain how to create a zero based budget in Excel in simple steps. I hope this article was helpful to you. Lastly, if you have any questions then feel free to let me know in the comment section below.


Related Articles


<< Go Back To How to Create a Budget in Excel | Excel For Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo