The At Work Holiday Survival Guide Part 3


Happy New Year!

If your Christmas was anything like mine, you over-spent, over-ate, and over-slept - and it was wonderful. However, all good things must come to an end, and in the New Year (particularly this one), many of us are left trying to piece together our finances as a result of our misbehaviours.

Well, the topic of the third and final part of the Holiday Survival Guide is how you can use Microsoft Office (specifically Excel) to help you get a handle on things, and turn managing your finances from a time consuming pain in the neck into a quick and easy thing you do every month to get more bang out of your paycheck.

Before we kick things off, I’ve searched the Internet to find you some decent resources on how to get into the budgeting mentality. The best page I’ve found so far is probably this one. Not only is it a pretty good introductory guide to budgeting for the financially undisciplined amongst us, it also contains many links to other stuff that is more advanced.

So, let’s assume you’ve read the above and want to now fix your finances. You could use a paper notepad, but we’re going to use Excel because:

- It doesn’t get messy when you make changes

- It keeps all your information together

- Will do the donkey work for you

- Is much more secure than paper

Here are three tips on how you can use Excel for this job – and how it will make your life much easier.

Templates

You can use a template that has been designed exactly for this task, available at this address.

clip_image002

It’s pretty good as it covers a lot of the basics that you’ll need to budget, leaving you free to personalise it with extra rows specific to your lifestyle. It’s also set up to do all the calculations for you – you just need to plug in the numbers. Loads easier than racking your brains for all the costs from scratch, drawing it up on paper, and then realising you forgot one and it all getting messed up.

Comments

These are a really useful way of making sure that all the meaning behind the numbers stays with you, preventing those puzzled looks at an abstract number three months after you first entered it.

Let’s say in June I know that I’ll be paying out £20 for stuff relating to sorting out my mud-pit of a garden so that the other half and I can use it over the summer (well, the five days of sun we get in this country, call it what you will). In a notepad I might scrawl a note with an arrow on what this will cost will be, and such scrawlings are a fast track to turning your lovely neat budget into a visually confusing eyesore.

In Excel 2007, you simply highlight the cell you want to add a note to, click ‘Review’ followed by ‘New Comment’ (press Shift and F2 in older versions):

clip_image004

...and hey presto – you’ve got comments that will appear and disappear only when you want them, and that can be edited without crossing out and erasing.

You can also use this retrospectively so that when you are logging necessary deviations from budget, you keep a track of what went wrong.

Here are some additional links about comments:

http://office.microsoft.com/en-gb/excel/HP051999281033.aspx?pid=CH010003681033

http://office.microsoft.com/en-gb/excel/HP052022641033.aspx?pid=CH010003681033

Conditional formatting

This is a really useful feature of Excel that will help you when analysing the numbers.

With your master plan budget complete, you should always enter the ‘actual’ costs at the end of the month so that you can see any variances from budget and understand/correct any problems the variances create.

Let’s see how I’d do this in Excel 2007 to help me spot pertinent issues in my budget. Go to Home tabà Conditional Formattingà ‘Greater than...’:

clip_image006

Then, enter in the threshold above which you want Excel to highlight variances to, as I have with in a hypothetical phone bill example:

clip_image008

clip_image010

Because of the Excel conditional formatting rules I’ve set up, we can see an emergent trend – looks like I’ve been consistently overspending on my mobile. We can see that my skimping on a cheap mobile tariff is in actual fact costing me dearly each month. I may be paying less in monthly contract fees, but the extra minutes I’m using are costing me megabucks, and soaking up my disposable income. Time to switch to another plan, or be more disciplined.

Here are some more links to show you how to use conditional formatting:

Microsoft Office 2007

Microsoft Office 2003 or before

Password Protection

A notepad really isn’t built with security in mind. In fact, probably the only life form that wouldn’t be able to get into it would be a fish, due to its lack of hands, and inability to use tools to compensate. And fish-based privacy intrusions aren’t really what I’m worried about – I’m more concerned about the intrusion of my other half, for if she did stumble across my budget, she’d surely discover just how much I spent on that item from the sales. And let’s just say some things are best ‘kept in the past.’

Fortunately, unlike a notepad, Excel is very much built with security in mind. To password protect your budget/spending log, do the following steps, available at this guide:

Microsoft Office 2007

Microsoft Office 2003 or before

And hey presto – my sordid history of sales temptation is safe from prying eyes.

Well, there you go – four ways Excel will help you get your finances back on track. I hope these tips help you get your finances in order this year.

Let me know what you think via the comments!

Adios,

Phil


Skip to main content