Using excel to track your finances – 1

The beauty of Microsoft eExcel is that you can tailor it precisely to what you want it to do and your level of skill. I have a file for each financial year for Busy Lives! and a separate one for the cost of living, which believe it or not I set up in 1994!

This is the first in a series of Excel finance blog posts. It explains how to use some of the simple controls to be able to create, use and track your own finances on excel.

The second post will explain how to insert simple formula, filter information and dropdown lists. Last but not least, the third post will be how to use and show data in charts and graphs.

 

Overview

 

  1. What sheets to create & why

  2. Having a professional look

  3. Shortcut keys & Freezing panes

  4. Formatting a column to present data the same way

  5. How Busy Lives! can help & support you with this

 

 

What sheets to create for your business finances

A good rule with excel is to only have a sheet if you really need it to inform your business. Here are the main sheets I have both used myself and created my clients:

  • Annual Expenditure
  • Car Related Expenditure
  • 12-month Cash Forecast
  • Expenditure / Expenditure (Client) / Expenditure (VAT)
  • Bank Statements
  • Income Invoices / Income Invoices (Client)
  • Client Analysis
  • Thank you

If you sell items I would also suggest / incorporate:

  • Profit Margins
  • Stock Levels
  • Client Ordering

 

 

Why

Annual Expenditure – I find this sheet useful to both track and predict how prices increase year on year, along with where my money is going.

12-month Cash Forecast – This has a formula built in which will automatically add your totals up each month showing at a glance what your currently income, expenditure and bank balance is. I find this sheet is really good where have one-off large expenditure occurring in different months such as business insurance and tax. (Blog Post 2 will show you how)

Expenditure (General)/ Expenditure (Client) – This sheet needs to be set up to reflect how your business runs. For example:

  1. Busy Lives! – the vast majority of what I buy is for just my business and I don’t earn enough to be VAT registered.
  2. The BlG Blue Hat Gardening Services – expenditure reflects a combination of tools bought for the business and materials such as plants, gravel and compost for customers. This sheet is set up so that you can see the overall expenditure, the actual business expenditure and what needs to be claimed from the customer.

Car Related – I like to keep this separate. Personal choice.

Bank Statements – It’s good to have a tracking overview to see the movement of monies in and out of your account as opposed to just the month you’re in when you view your statement.

Income Invoices / Income Invoices (Client) – Like the expenditure option above, set it up to reflect your type of business.

Client Analysis – My favourite sheet which really gives me an insight into the business. It has a variety formula in it which will populate graphs as you add to it each month.

Thank you – A useful quick reminder to ensure I follow through and say thank you to my existing clients.

 

 

Having a professional look

Adding the little things can make a world of difference to your spread sheet, here are my favourite top three:

  1. Colour each sheet tab and give each sheet a name – right click with the cursor over the word ‘Sheet1’ and you will see the option to Rename and Tab Colour. I often use the business brand colours!
  2. Have the same font and size on all your sheets – select the box between ‘1’ and ‘A’ then select the font and size you wish to use. I usually use Aerial, size 11 on excel.
  3. Make you headings and titles stand out by filling the cell with colour and making the font size slightly bigger – select the cells with your heading in and use the fill option which is directly under the size. Again, why not use your brand colours.

 

Shortcut keys & Freezing panes

Shortcut keys

The following keyboard shortcut keys can be used not just with excel but across all Microsoft Office Applications. These are the ones which I find really useful:

  • ‘Ctrl A’ – to select everything in the document press Ctrl A.
  • ‘Ctrl C’ – to copy – highlight what you want to copy then press these two keys.
  • ‘Ctrl V’ – to paste – select the cell or area you want to paste the copied information in, then press these two keys.
  • ‘Ctrl B’ – make wording / digits bold – highlight any text / digits you wish to make bold and then press these two keys.
  • ‘Ctrl U’ – to underline – highlight any text you wish to underline and press these two keys.
  • ‘Ctrl I’ – for Italics – highlight any text you wish change to italics and press these two keys.

Freezing Panes

Freezing panes is another expression for locking multiple rows and columns. It’s really useful to have when you have lots of entries, as it enables you to still be able to see the headings.

From the heading ribbon at the top of the excel file select ‘View’ and you will see the options for you to select to freeze multiple panes, just the top row or just the first column.

To lock multiple columns, select the column to the right of the last column you want frozen, choose the View tab, and then click Freeze Panes.

Formatting a column to present data the same way

You can set up the columns in excel for different purposes. For instance, on my Expenditure sheet I need a column to show the date, a column to show the price including the price sign, and a column to give me the option of the different allowable expense areas. This is done through formatting the cells.

Select the column you wish to format. Right click either your mouse / scroll pad and you will see the option to ‘Format Cells’ click on this. You now have a set options to select from.

My top three common ones I always use are:

Date – select the option that suits you I always like the date, month and year as digits.

Accounting – so the pound sign always automatically shows and has the same number of digits after the decimal point.

Text – where you want to insert phone numbers and have the ‘0’ showing at the beginning.

 

How Busy Lives! can help & support you with this

Busy Lives! was created when I left a busy career that left me time poor and unable to give sufficient priority to what really mattered to me. I now provide a wide range of business support for people in that position or are wanting to start their own business.

I am happy to help by:

  • Teaching you how to use excel for yourself at a pace and time to suit you.
  • Setting up a bespoke financial year excel template for reflecting your business needs.
  • Entering all your finance invoices, bills and receipts for you as and when you need support. (This can be on Excel, QuickBooks or Xero)
  • Analyse your data on a monthly basis and produce a summary of the key headlines in a report. This will help you recognise trends and areas within your business to help it grow in the future.
  • Setting up an easy system for your filing all your financial paperwork and receipts.

 

Find out more about my Small Business Support Services – Click Here

Let you help you gain precious time back. Ring Busy Lives! 07565 722 031

Drop me a message on this website, LinkedIn or Messenger on Facebook

Or Email: karen@busylivesnottingham.co.uk