Time to get a little tricky, only a little…

Now it’s starting to get fun, it’s time to try a tiny bit of manual formulation.

Ask yourself, what do you need to know now? Well, naturally after you have decided to allocate funds here and there you will need to know how much you have left to play with.

• You know your Total Combined Income
• You know how much you need to leave in General Expenses
• You know how much you are planning to transfer/allocate to other accounts

So let’s see what’s left?

1. Click the empty cell directly next to ‘PLAY MONEY’ or whatever name you may have given that left over $$$ cell.
2. Now type the following =(B9-B26)

(Remember your cell references may differ but the process is the same) 

*THE FORMULA MUST BE IN BRACKETS AND MUST BEGIN WITH = 

What you are asking the spreadsheet to do is take away your total expenses from your total income……and asking, what is left?

Here is where you will find out if you need to adjust anything in order to make sure you really do have enough money without going into the red.

Don’t forget to copy that formula all the way along by clicking the cell, and dragging the solid black square all the way to the end.

Now we can sort out your other accounts.

Transfer data from one cell to another

Once again, what do you know so far?

• you know your Total Combined Income
• you know how much you need to leave in General Expenses
• you know how much you are planning to transfer/allocate to other accounts

AND

• Now you also know what is left over to play with in the end
• So now we will work out what is happening with the money you are transferring into your ‘expense account’ and any other accounts you may have included in your spreadsheet.

Remember we made the first cell under the heading of ‘EXPENSES’ into a deposit field. So we need to make the blank cell next to it automatically contain the same amount that we already allocated whilst in our general expenses section. So, Tsf to Expense = Deposit. The image on the right illustrates this function.

 

So it’s time to do another formula.

1. Click on the blank cell next to the deposit field
2. Enter the formula which tells the spreadsheet that you want the cell reference next to ‘deposit’ to = the same as the cell reference in which you entered the amount you intended to transfer into the expense account.

So for example: If the cell reference for ‘tsf to Expenses’ was B21 (that is, the amount you want to transfer from GENERAL BUDGET to EXPENSES), and the blank cell reference next to deposit in EXPENSES is B32 then the formula will read:

=(B32=B21) 

Or in other words….whatever you enter in B21 will automatically be transferred into B32 

Don’t forget to copy that formula all the way along by clicking the cell, and dragging the solid black square all the way to the end.

Do the same for any other accounts you have decided to transfer funds into.

If you have a field in GENERAL BUDGET that says you are going to transfer funds from general expenses to say, ‘savings’, then in the blank cell next to DEPOSIT, under the heading of ‘SAVINGS’ you need to enter the formula that says the two cells = the same.

NOTE

The same will not work in reverse. I.E. if you manually enter a figure into the actual expense account deposit section, it will not automatically change the transfer amount in GENERAL BUDGET…..think about it. That is not what the formula asks for.

So keep it simple and work from the GENERAL BUDGET section.

The next post will show you how to auto-calculate a running balance, click here to go there now.