Family Budget Template Tutorial

I prepared this tutorial because there was a lot of requests for a tutorial about how to build Family Budget Template. You can understand the components and how to build your personal template by following this family budget template tutorial.

First, let’s understand the template file. This template is consisting of 3 sheets.

  1. Actual: This is where you input your actual figures and see results in a visual way.
  2. Budget: This is where you input your budget figures.
  3. Helper(hidden): This sheet holds data tables for the charts in Actual sheet.

Now the actual Family Budget Template Tutorial:

1. First create a Budget sheet with tables for income, expenses and cash available (like the ones before). Cash available will show the amount left after you decrease your expenses from your income.

2. Now input your budget figures into these tables. For realistic results, try to forecast salary increases, inflation rates, etc.

3. Create another sheet and name it as Actual. Copy your tables from Budget sheet into this new sheet and delete figures you inputted. These tables will be filled with actual figures as time passes.
4. Leave some space at the top of the Actual sheet for charts. We will place our charts here.5. Now create a third sheet and name it as Helper. This sheet will hold data and calculations for our charts. Since we are planning to have 3 charts, we need 3 sections on Helper sheet.
6. First chart will show monthly comparison of a selected expense. For expense selection, insert a data validation list for expenses. Select the list source as the first column of your expenses table. Now we can select the expense we’d like to compare against budget.
7. Your chart data should be like below:

Cell C4 gets data from cell B4 in Actual sheet. That is the cell that we select expenses. Budget and Actual fields contain a SUMIF formula getting data from related sheets.

Formula for Budget (cell D5): =SUMIF(Budget!$B$17:$B$34,Helper!C4,Budget!$C$17:$C$34)

Formula for Actual (cell D6): =SUMIF(Actual!$B$35:$B$52,Helper!$C$4,Actual!C$35:C$52)

 8.  Now insert a column chart with 2 data series. One series will be budget and another will be actual. Adjust series types as shown below.

9. Now we are going to skip to chart 3, then return to chart 2. Create another data set for chart 3 in Helper sheet. This data set shares the same formulas with the one for chart 1. Only difference is, it doesn’t use a dynamic criteria (cell c4). It just shows totals, so change C4 in the middle of the formulas to C12 (which has “Total” as value) and it will work.

10. Insert a new chart into Actual sheet and apply the same procedure as you did for chart 1. Your chart should show the totals.

11. Now we are going to build the 2nd First thing we are going to do is to write group names beside our expenses in actual sheet (like shown below).

Now create another field in Helper sheet for second chart. It will have same groups in first column, so we can summarize our expenses by these groups. Here is how it should look like:

Populate this table with sumif formulas just like the tables we created for other charts.

12. Notice there is a small table at the right, this will be the actual source for our chart. To build this table, copy first column of the chart on the left as first column. Then move to Sheet 1 and create another data validation list just like the one you did for the first chart. Select any range that contains months as data source. This way our drop-down list will show months. Location of the drop-down should be on top of 3rd chart in Sheet1 (G4 in this example).

13. Now type this formula (=Sheet1!G4) to the header cell of second columns of the small table. Then populate cells in second column with sumif formulas like we did for other charts. Now values in our small table will change every time we select a new month on our drop-down list in Sheet1.

14. Select this table as a whole (excluding the sum at the bottom) and insert a pie chart. Format it to your liking and place it on the Sheet1 .

This is the end of our family budget tutorial. With following the steps above, you can easily create one for yourself. And then an even better one if you like.

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.