How to create a Household budget in Excel

in Excel For All4 years ago (edited)

There are many reasons you would want to keep a household budget.  Setting a budget allows you to control spending and save more money.  A budget should give you an accurate picture of what you are earning and what you are spending.  Knowing what you can afford is critical if you want to stay out of debt. Keeping a household budget will really help you with that.

Microsoft Excel is an amazing tool often used to keep track of budgets both in the workplace and at home.  With the built-in templates provided by Microsoft, creating a household budget in Excel can be done with ease.  Before you take this on, some basic knowledge of Excel would be preferable, but it is not a necessity.  

The household budget template in Excel is easy to set up and it helps you to quickly create and track your budget plan.  By using a template, it saves you time trying to work out what is included in a household budget.

In this article you are going to learn, step by step how to set up, manage and edit the monthly household budget template in Excel.

What we will cover in this article:

How to Open Household monthly budget Excel template

Budget Overview

              Projected Income

               Actual Income

Monthly Expenses

              Projected Costs

              Actual Costs

Reports

             Budget Overview

             Budget Summary

             Monthly Expenses

Additional Data.

             Adding Expenses and Categories

Saving your file.

Tips for keeping this household budget

How to Open Household monthly budget Excel template


Open a new instance of Excel and select New


In the search bar search for Household budget.  You will find a template named Household Monthly Budget


Select the Household monthly budget template and select create.

Household Budget in Excel

The household monthly budget in Excel template will download and open.  The template contains 4 worksheets and we will look at each one of these in detail as we move though the article.

Budget Overview


Household Budget in Excel

Budget Overview sheet allows you compare your actual spend and savings against your budget.  Don’t panic just yet! I know it looks a lot. We only have a few small details to complete on this page as most of the work is automatic.  All we must do is complete the Income section, both Projected and Actual.

Projected Income


Projected income is all the cash you expect to take home during the month.  For example, a household has two earners.  The higher earner expects to take home $6000 during the month and the lower earner expects to take home $1000.  You can see in the projected field for income 1 we have $6000 and for income 2 we have $1000 representing this.

Household Budget in Excel

We complete these values at the start of the month.

If your household only has one income, you can leave the content of income 2 blank.   If you are expecting other income, such as child support, rental income or any other cash income, this goes into the Extra income projection cell.

Once you have entered these 3 values, the Total income will automatically calculate.

Actual Income


Only when the actual amounts have been paid to you, can you then enter them into the actual fields.  Very often the actual will not be the same as the projected.  Working hours might change, or sick days can happen.  There are many reasons why the projected will not be the same as the actual.

Monthly Expenses


We are going to skip to the third worksheet in the Household budget template now.  Monthly Expenses.  On this sheet we need to enter all our outgoings.  Our household expenses.  Everything from loans to Food. You need to enter every cent you plan on spending during the month to this sheet.  Leaving out any expected expenditure will give you an inaccurate budget.

Household Budget in Excel

The monthly expenses sheet has been prefilled with many different expense types within different categories.  For example, we have a Description of Extracurricular Activities, and this is in the category Children.  We will look at adding and amending some of these Descriptions and Categories in a few minutes but for the moment lets work on completing this sheet.

Projected Costs


For each item on the expenses sheet you need to complete the Projected cost.  This is the amount you expect to spend in the month. This will be your spending budget that you want to try and stick to.  For some expenses you will know the exact amount as there is a fixed charge.  When you don’t know the amount, you can use the average that you have spent over the last few months.

Scroll down the page and make sure you fill in all the Projected costs that you have.  You might find there are some expenses on the template that do not apply to you.  You can ignore these.  If there are expenses you want to include that are not on the template, we will cover that later.

 When you have completed the projected costs, you need to ensure that you are not overspending.  It would be nice to put in an entertainment budget of $1000 but just because you enter it, does not mean you can actually afford it. 

Return to the first worksheet, Budget Overview.  By keeping an eye on the Balances, we can ensure we are not overspending on our household budget.

Household Budget in Excel

We need to ensure the projected balance value is a positive value.  If there is a minus value here, you are overspending.  You must revisit the Monthly Expenses sheet.  Review all your expenses and where you can, make some reductions.  First start with expenses that are not a necessity.  Continue to reduce expenses until the project balance is a positive figure.

Actual Costs


To complete the household budget, we need to also complete the actual expenses. This can only be done when the expense happens.  When you make the payment.  Bills may vary month to month.  You might decide not to get your nails done, or you could get a voucher for your birthday and use that to pay for those jeans you want!  There are many reasons the actual expense won’t be the same as the budget or projected expense.

Reports


As we enter our actual income and expenses, we can keep an eye on the reports to make sure we are staying within budget.  This household budget template provides a lot of useful information that you can take action on.

Budget Overview


The key values to keep an eye on here are the Balances. 

The Projected Balance we looked at earlier.  This takes the projected income, and the budget expenses and lets you know how much is left at the end.

The Actual Balance shows us the Actual income less the actual expenses.

The difference shows us how much more, or less we ended up with against our projections.  A great household budget will keep this difference to a minimum.

Household Budget in Excel

At the bottom of this page you will see a chart.  This shows the actual money you spent by category.  The chart makes it really easy for you to see what categories you are spending the most on.

Budget Summary


Household Budget in Excel

The Budget Summary worksheet is an interactive report that allows you view details of your expenses.  It compares budget against actual and is easy to use.  It allows you quickly see where you have overspent as the difference column will appear red to let you know you have gone over budget.

If you keep the expenses sheet up to date during the month by added expenses as they happen, on the budget summary sheet, you can use the difference column to foresee any possible problems.  If something has gone red before the month is over, you might have time to save costs elsewhere.  That way you can keep your overall budget on track.

Monthly Expenses


The monthly expense sheet allows you see if you are within budget as you enter the actual costs.  The difference column will show up red if you are going over budget.  If this happens you can try and stay under budget on other items such as luxury goods.

The Actual cost overview bar gives you an idea how much of the overall spend each item contributes.



Additional Data




The additional data sheet contains two tables.  The Pivottable for Budget Chart shows the total of the actual costs by category.

The second table is a category list.  If you need to add new categories for your expenses, the category would be added to this table, but we will cover that next.

Adding Expenses and Categories


Although this template is rather detailed, and it contains a lot of expenses and categories.  There may be a case where you need to add expense that have not be included in the template.

Both the Category’s and the Expenses are included in Excel Tables.  Excel tables are a special format that allows for other parts of the spreadsheet to automatically update. To add a new row to either the Category table or the Expenses table, you must to the last row in the table and the right most column.  From there you press tab.  This will create a new row in the table, and you can then enter your details.

Saving your file


It is a good idea to save your file with the month name.  To save the file, in Excel go to File, and then Save as.  Navigate to the folder you wish to save your file in and press ok. 

Tips for keeping this household budget.


Before you start a budget, it is a good idea to get an idea of your income and expenses.  Go back over the last few months and work out how much you spend and how much you earn.  You can use the same template for this by entering the actual income and expenses into the template.

By looking back over a few previous months, you can then see where you are spending your money, what are necessary spends and what are not.  This will make an awesome foundation for getting your home budget right.

The final tip is to keep your budget up to date.  Add in the actual expenses as they happen.  That way you can see if you are reaching your budget spend and need to slow down, or if you have surplus cash for that fun family activity 😊



Cross posted from my blog with SteemPress : https://theexcelclub.com/how-to-create-a-household-budget-in-excel/
Sort:  

I have to say - I need Excel but I hate it. This is great, have to dive into this. No marketing budget plans without Excel - thanks for that!!!!

Embrace Excel, don't hate it. Stick with me and after a while, I bet I can get you to love it :-)

Sounds promising :-) - sticking with you then

I'm not sure excel spreadsheet would work for me and my budgeting strategy....

Outgoings = X
Income = 2.5x - 3.5x of X

That's it. lol
If it the ratios ever get lower, I disregard everything and concentrate on the 'income' part. (until the ratios of earnings to are 'back up to normal').

A very clever mathematician (from CERN), once said to to me, 'if you ever have to worry about your expenditure, your focusing your energies on the wrong part of the equation'.

(I wish I knew how to use excel though...lol)

Not everyone has the ability to focus on the other side of the equation. If they did, then the global wealth distribution would not be as it is.

Excel rocks, stick around here and you will learn it fast enough :-)