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.
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,
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.
- 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.
- 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.
- 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)
- After that, press Enter and you will get the Total Income.
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.
- 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.
- 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.
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.
- 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.
- After that, look where you can cut the Expenses. Here, I cut $50 from Personal. And then the Final Balance becomes -$50.
- 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.
- 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)
- Then, press Enter.
- After that, enter the Actual Expenses in the table.
- 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.
- 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
- Finally, press Enter.
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Â
- Next, press Enter to get the Difference.
- After that, drag the Fill Handle down to copy the formula.
- 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.