An Application of Functions: Budget Spreadsheet (PC)

In a budget spread sheet, you put information about your budget, keep records of your monthly income and expense and come up with summary statistics for your monthly finance. By keeping clear transaction entries and making informative charts, you could have a better knowledge of your financial situation.

There are three separate sheets in this spread sheet: Income, Expenses and Statistics and Charts. 

 

Income

 

The first four columns of the income are self-explanatory and you just have to enter the transactions manually. We want the amount to show as currency instead of a plain number. To do this, we select the entire column and use the drop down menu in the Number section in the ribbon and then we select currency.

 

Throughout the whole budget spreadsheet, we will use currency for numbers. So if the default setting is “General”, we could simply select the whole range of numbers and change the form to currency.

Staring from column E, the column titles are different categories of income. In this example, we only have two categories of income: salary and other. You could specify other categories in column C and include the categories as different columns. We want the amount of income to be entered below certain categories starting from Column E without having to reenter the data in Column C. In order to do this, we use the IF function.

 

IF function

The IF function needs three arguments.

 

The logical_test is the condition you want to specify. For example, the condition for Column E would be if the entries have category as “salary”. So the condition for cell E2 would be: $C2=$E$1. Similarly, the condition for cell E3 would be $C3=$E$1. Note that the right hand side of the equation, we use absolute reference $E$1 for the entire Column E because we want to put any income that is categorized as “salary” in column C under Column E. (recall how to do absolute reference)

The next two arguments are [value_if_true] and [value_if_falso]. You specify what value to put into this cell if the condition is true or if it is false. In this case, you put nothing ("") in the cell is the category is not salary and put the amount of income ($B2) into the cell if the category is salary. So the full function statement for cell E2 would be =IF($C2=$E$1,$B2,""). The rest of the Column E can be auto-filled by dragging down the formula in cell E2. (Recall auto fill here)

The same idea applies for the column “Other”. We have the statement: If the category of the transaction specified in Column C is “other”, then put the amount under this column. Otherwise put nothing under this column. Then we translate the statement into the IF function. For example, the function in cell F2 would be: =IF($C2=$F$1,$B2, "").

 

Expense

In this sheet, we keep records of expenses. 

 

In addition to the date, amount, category, we also keep a record of whether this transaction is paid by cash. In Column D, we put “y” to indicate a cash transaction. After the normal bookkeeping columns, we have a column to count cash transaction and other columns to separate different categories of transactions. Again, we use IF function for this columns. In the “Cash Count” column, the if statement would be: if the corresponding record in column D for this transaction is “y”, then put the amount into column F, else put nothing. For example, the function in cell F2 would be: =IF(D2="y", B2, ""). In the following columns, we put the amount of transactions into the corresponding categories. The idea is very similar with that in the “Income” sheet. Note that we use red color to indicate negative number instead of putting a negative sign before the number. In order to do this, first select columns we keep information of the amount of money.

 

In this chart they are column B and columns after F. Then click the icon in the “Number” section of the ribbon. In the pop-up window, select the second option, which is using red color to represent negative numbers.

 

Statistics and Charts

Finally, we have the Statistics and Charts sheet to summarize our monthly budget and transactions. 

 

We first look at the middle part of this sheet, i.e. column C to G. These columns summarize expenses. In column C, we put different categories of expenses. Column D to G each gives the actual expense, projected expense, deficits of this category of expense and finally the percentage of total expenses which is used for this category. The projected expense is our budget and should be entered manually. The actual expenses come from the sheet “Expense”. For example, the total actual expenses for “Rent” would be the sum of all rental expenses, which is equal to the sum of entries in Column G of the Expenses sheet. In order to get the sum, we use the function SUM and we need to refer to cells in the expense worksheet. In order to do this, we write the command as: = SUM(Expenses!$G$2:$G$100). We actually only have four rows of transaction but we calculate the sum from row 2 to row 100 which also applies to larger data sets. For Groceries, similarly, we put =SUM(Expenses!$H$2:$H$100). Notice that we have to use absolute reference to cells and we cannot use auto fill for the following rows.

Why is this the case? Because if we use relative reference: = SUM(Expenses!G2:G100) and use auto fill for the following columns, the formula for the next row would be  = SUM(Expenses!G3:G101), which is not what we want.

After we calculated the actual expenses, we could calculate the deficit using actual – projected.  For example, F2=D2-E2, and we could auto fill the rest of the “Deficit” column. Having columns D, E and F filled out, we could calculate some numbers in Column A. Projected Budget is the sum of all numbers in Column E and Total Expenses are the sum of Column D. Then we could calculate Column G using the total expenses. Column G shows the percentage of each category of expenses to the total expenses. For example, cell G2 is calculated as: = D2/$A$6. Then we could auto fill the rest of the column.

The Cash Count would be the sum of amount of cash transactions, which is equal to: =SUM(Expenses!$F$2:$F$100). Total Deficit/Surplus shows the sum of Column F.

Now we could create some charts to summarize the calculated information.  We first make the projected budget versus actual expenditures chart. We first create a blank 2-D column clustered chart. Then we right click on the chart and choose “select data”.

 

In the pop-up window and under the “Legend Entries”, we click on “Add”.  Then in the Edit Series window, choose cell A3 as the series name and cell A4 as the series value. Then click OK.

 

Similarly, add another series with A5 as series name and A6 as series value. After selecting the two legend entries, click OK.

This is the chart you will get as a default. We could edit the chart to make it look better. We format the vertical axis to range from -150 to 0, move the legend to the top, and add a title for the chart. (Do not remember how to format a chart? Find various chart formatting tutorials here)

When you edit the chart, there is one tricky part that is worth mentioning. 

 

There is a small “1” between the two columns. To remove it, simply select it and press “Delete” on the keyboard. Note that it is very easy to select the actual “columns” or the whole chart area instead of selecting the number. Make sure that after you select the “1”, the chart area appears as in the picture above, otherwise you might want to try clicking on different positions on the chart.

Here is an example of a completed chart.

 

It is definitely fine to have other designs of the chart, as long as it is informative to you.

Then we make a pie chart to show expenses by category. Select Column C and D to include all categories of expenses and create a pie chart.

 

This will give you a default pie chart. Click on the title to edit it. You could also edit the legend and choose different style of chart area. Here is an example of the pie chart after some editing.

 

After summarizing the expenses, we could do similar analysis for income. 

 

We copy the data from the Income spreadsheet using referencing another sheet to make the “Income by Category” and “Actual” columns. Then in the summary statistics in Column A, we calculate the monthly income by adding up all the income entries. We could also calculate Savings/Deficit, which is equal to the sum of total expenses (as a negative number) and monthly income.

 

Then we could calculate the percentage of each category of income by dividing the actual income by monthly income. We also make a pie chart to show income by category.

That is it! We have created a fairly informative budget spreadsheet.

 

<< Back to Macros
Continue to Analysis ToolPak>>

 

Return to Table of Contents

 

Questions, comments, concerns?
Send an email to the Empirical Reasoning Lab
Or drop in during the Lab's open hours