Blog Layout

Tutorial - How to make a simple cash book in Excel

Christina Gorham • Jan 15, 2021

Record keeping for start-ups


Knowing you are making a profit is important, especially when starting out and every penny counts. 

When most businesses are new, it’s normal for the business owner to leave their “salary” in the business to help it grow. It’s also normal for the business owner to want to know how soon they can start drawing a small wage. This is where accurate accounting comes in. Accurate accounting (recording of all financial transactions) will enable you to know how soon your business will be making a profit and how much longer until it can afford to pay you or will enable you to invest in other ways that will facilitate growth.

How can you do this? If you can’t afford a bookkeeper yet, but still want to manage your business well, a good start is to maintain a cash book. 

Your first cash book is usually produced in Excel as you can set it up to do the maths for you, but you can use word or even a pen and paper satisfactorily. The simplest ones look like your bank statements, with columns for the date of the transaction, what the transaction was for, how much money came in to or out of the business, and how much money is left. The key difference between the bank statement and your business cash book is that the bank statement only looks at what is in your bank account while the cash book looks at everything (bank, cash in hand and cash in the till). If you are issuing invoices you will need to record this separately.

To set up the most basic cash book, I recommend you copy the table below.

Date Transaction details Money in Money out Running total
01/01/2021
02/01/2021
03/01/2021
04/01/2021


If you’re using Excel and have put the titles in the top row of the spreadsheet, you can make the running total column automatically calculate how much you have left by putting in the first row how much you are starting with and in the next row down entering this formula 

=E2+C3-D3

You can copy it into the cells below by hovering over the bottom right corner of the cell where there is a small square. Your curser will change from a white cross, to a smaller black cross. When you click, hold and drag down as far as you want to go the formula will be copied, automatically adapting to provide you with a running total. In the image below, I’ve circled the small square you’re looking for.

 

Basic Cash book


Now you’re set up, all you need to do is update the spreadsheet with every transaction no matter how small, and you’ll know how much cash is available to your business.


Once you’ve got the hang of this sort of record keeping, you may decide to move on to using the more detailed analysed cash book. Here’s a fictional example. You can make it as simple or complex as you want, even splitting items between category as I have done with the hand sanitizer in this example.


Analysed Cash Book


It can be hard remembering to record every transaction. This is why it’s important to reconcile your books regularly. I’ll produce a separate tutorial on how to do this.

by Christina Gorham 25 Jun, 2021
Depending on where you get your charitable income from, this can be the most challenging part of charity bookkeeping and reporting with levels of complexity not found in other sectors. It sounds so simple. The charity commission reporting requirements only want you to categorise your charity transactions according to restricted, unrestricted and endowment. But what do you do when a donor asks you how you have spent their donation, as is their right to know? How do you separate out what their money was spent on without opening lots of bank accounts? How do you ensure their money was spent on only the items they have asked you to spend it on? What if the donor set a time limit on the grant, requiring unspent funds are returned to them? This is when you need to be able to categorise your transactions twice, according to the charity commission headings and according to who gave you the money in the first place. Some charities go a step further by requiring a third level of filtering and categorisation for management reports that may group a number of donations together as they were given for the same purpose, or even split donations up if they were given for multiple purposes. If your charity is small and has a large number of grants/ restricted donations. You may find it easier to do this sort of filtering using Excel spreadsheets by adding categorisation columns similar to the ones shown below.
by Christina Gorham 18 Jun, 2021
So, you’ve finally managed to complete setting up your Government Gateway access and have found where to click to submit your first Self-Assessment. You’re all ready to go……Or are you? Before you click that button to begin your Self-Assessment there are a few things you need to consider and prepare for. • Do you know your business income and expenditure figures or are you just guessing? • Are you working from home? Could part of your household bills be regarded as allowable business expenses? What about milage? • Do you have any employed work? Have they given you a P60, P45 or P11D? • Do you have any other sources of income? For example, share dividend, bank interest or pension income. • Have you received any start-up grants or Covid related support? • Have you donated anything to charity? Did you Gift Aid it? • Have you paid anything extra towards your pension or student loan repayments? There are more questions your accountant should be asking you when they are helping you prepare your accounts, but this is a good place to start as the answers to all of these questions will affect your final tax bill. “But Coronavirus has hit me hard. I can’t afford an Accountant!” Are you sure you can afford not to use one? Qualified Accountants can save you hundreds, if not thousands of pounds on your tax bill by asking the right questions. And qualified Bookkeepers can save you a lot on your Accountants bill by helping you provide the numbers in the best way to help them sort your Year End reports and Self-Assessment as efficiently as possible. If you don’t know the answers to the questions above. 64 Bookkeeping can help you find out by going through your bank statements, invoices and receipts (all your business paperwork) to produce accurate bookkeeping records uniquely tailored to your business. By gathering together all the information you will need into one place and by sorting out your documentation, we make it easy for your accountant to find all the numbers they need to save you money. But there is more. Did you realise that the accounting records we produce for you can be used for more than your Self-Assessment? These records can be used to help your business grow throughout the year. The more often you update your records the better your idea of how your business is doing, enabling you to make the wisest decisions for your business.
by Christina Gorham 11 Jun, 2021
One of the most important, yet often forgotten steps, when setting up as a sole trader is to register with HMRC for tax. Yes, even hobbyists need to register if your income is over £1000 per year (this includes all your non-employment income such as interest, casual sales, and independent trading). But first, let’s look at what a sole trader is. Usually they are a “one man band” though many have employees and a rare few grow surprisingly large while retaining the original person as the owner and figurehead. HMRC states, “You’re probably self-employed if you: • run your business for yourself and take responsibility for its success or failure • have several customers at the same time • can decide how, where and when you do your work • can hire other people at your own expense to help you or to do the work for you • provide the main items of equipment to do your work • are responsible for finishing any unsatisfactory work in your own time • charge an agreed fixed price for your work • sell goods or services to make a profit” “You could be classed as a trader if you sell goods or services. If you’re trading, you’re self-employed. You’re likely to be trading if you: • sell regularly to make a profit • make items to sell for profit • sell items on a regular basis, either online, at car boot sales or through classified adverts • earn commission from selling goods for other people • are paid for a service you provide” As you can tell, the definition of what makes a sole trader is very wide to include all commercial activities that have not been registered with Companies House or the Charity Commission. Now we need to consider when you will need to register as Self-employed. HMRC state you must register with them if; • you earned more than £1,000 from self-employment during one financial year (Eg. 6th April 2020 to 5th April 2021) • you need to prove you’re self-employed, for example to claim Tax-Free Childcare • you want to make voluntary Class 2 National Insurance payments to help you qualify for benefits I would add to this list that registration becomes compulsory if you are required to by your licencing body, if you are a CIS employee who has agreed to pay the CIS related taxes yourself, or if you have non-trading income that must be declared via Self-Assessment. As soon as you realise you are a sole trader, you need to register with HMRC for Self-Assessment. The absolute latest you can leave it is 5th October following the end of the tax year in which you became self-employed. That means that if you started selling crafts during the second lockdown (before 5th April 2021), you only have until 5th October this year to register for self-assessment; but if you decided to open a café with no outdoor space or online ordering, you would have been obliged to open after 6th April 2021, meaning you don’t need to register until 5th October 2022. Having established if and when you need to register with HMRC, the online registration process is fairly straight forward, if a little long winded due to security checks. 1. First you need to create your Government Gateway Account. 2. Next HMRC will post to you your Unique Taxpayer Reference (UTR) number. This should take no more than 10 working days, but delays are normal during busy periods. 3. Then you will need to log into your Gateway account using this UTR number so you can request an Activation Code is sent to you by post in a separate letter. This should take an additional 10 working days but can be delayed if HMRC are busy. 4. When the Activation code has arrived, you need to log back into your Gateway account and enter the activation code. Now you should be able to access all your tax information and you will be able to submit your Self -Assessment. Top tip* Keep your UTR letter and your password somewhere safe yet memorable as you will need to use this every time you login. You really don’t want to register now and forget where you put your login details next year when you’re wanting to sort your Self-Assessment submission, as a lost UTR can cause even more delays that could result in late filing penalties. Top tip 2* The sooner you start sorting your gateway login the better as the longer you leave it the longer the delays get. I’ve heard of businesses taking three months to register. Don’t risk late filing penalties just because you didn’t register soon enough!
by Christina Gorham 14 May, 2021
Did you know that every year many businesses owners are due a refund with their Self-Assessment? This is especially common among business owners who have an employed role alongside their own business. Times when you may have overpaid tax include: • Paying too much in your payments on account • Being put on an emergency tax code when you start a new job • Being put on the BR tax code by a second job when you still have some Personal Allowance available from your first one • Being put on the wrong tax code by an employer (deliberately or accidentally) There are other reasons for being owed tax by HMRC and if you think you may be due a refund it’s well worth having a chat with your accountant. Remember to ask about allowable expenses. While most employees are automatically assessed and issued with a P800, some of us slip through the net and need to submit a claim. If you think you’ve paid too much tax but have not received a P800, you’ll want to take action. The easiest way to get your refund is to submit a self-assessment before the deadline of 31st January. If this is your first Assessment, you can’t leave it to the last minute because you will need to set up your individual Gateway account and this can take a month or more. I always recommend sorting your Self-Assessment as soon as possible following April 5th. After all, your money is of more benefit to you in your bank account than the tax man’s and it’s not worth delaying the job just because you can. There are also the late filing fines to consider that could completely wipe out any refund you are owed. If you have never submitted a self-assessment it’s worth reviewing the last four tax years as you may be able to claim more than you were expecting. UK law allows you to claim overpaid tax for up to the last four tax years, providing you get the claim in before 5th April when a new tax year will begin. So, what do you need to prepare in order to submit your self-assessment? You will need a full and accurate breakdown of all your income sources including documents such as your P60 to prove how much of your income has already been taxed, along with calculations of pension contributions, gift aided donations and all business-related expenses (including personal utility bills if you work from home). Any good bookkeeper will be able to help you setup and complete a set of personal books that will give you certainty you have the correct figures, even if you have multiple businesses and employers. If you want help sorting out your personal books, get in touch. How much you will get back?
by Christina Gorham 30 Apr, 2021
When preparing to close last year’s books it’s important to check the data is correct because it’s not always easy (and certainly not advisable) to edit the books in a closed period and you should definitely avoid editing data that has been submitted to the tax man. You, or your bookkeeper, should check and confirm the accuracy of all the data you will be submitting to your accountant for your year end reports. Try to correct every error you find if you know how to do this and don’t worry if you have a few things you don’t know how to correct, most accountants will appreciate you stating upfront, “I’ve found this error but don’t know how to sort it. Can you please help me?” It’s better to point out unresolved errors than to leave them to hurt you later. How can these errors be found? Well, it depends a lot on how you are recording your data. If you are using a spreadsheet most data entry errors can be found using filters and pivot tables. If you are using cloud software there will be a variety of methods, specific to your program, for finding these mistakes. The first place I always look is the year-end reports as most accounting software allows you to dig into the data from the reports. There is also usually an easy-to-use transaction search function that will allow you to confirm, for example, all of the Electricity invoices have been coded to Electricity and not Gas and you have all 12 invoices recorded. Once you have checked all the recorded transactions are correct, it’s important to look for any transactions that may have been missed, duplicated or added in error. This is where good paper records/ online filing comes into its own and where some accounting packages trump others. Your bookkeeper will need to check every document you have against the bookkeeping program, simultaneously confirming your filing and bookkeeping records are complete and accurate while removing any documents and transactions that have been recorded twice, correcting bank reconciliations at the same time. Yes, you probably perform many of these checks monthly when you reconcile the bank, but it’s advisable to come back later and confirm nothing is missing from the records. After all, have you never started to reconcile the bank and found a direct debit payment you didn’t have the invoice for yet or received and invoice which you have put into the online system only to enter the payment separately weeks later when reconciling the account? This is the sort of thing that’s easy to miss on a monthly basis and why it’s important to check again. Larger businesses will prefer to perform this sort of deep-data check on a quarterly basis as well as annually, while non-micro charities may schedule the data deep-clean to coincide with trustee meeting report generation. This may sound like a lot of work, but it’s worth it. Some businesses have discovered errors that could have cost them thousands of pounds if they had not checked their records carefully enough. If you want help checking your records, please use the “Contact Us” button below.
by Christina Gorham 26 Apr, 2021
Why is it so important to check my annual reports? Isn’t that what I pay the accountant for? Yes and no. You pay your accountant to produce your annual statements and to submit your self-assessment, but you are still legally required to check the reports before they are finalised. It’s important that you understand the reports you are given. If you are a charity trustee, it’s even more important that you understand the reports as you are required by the charity commission to confirm that you are happy with them. How can you do this if you don’t know what you’re looking at? Let’s begin with the Income and Expenditure report (or I&E), also called the “Statement of Financial Position” (or SoFA) and the “Statement of Profit and Loss” (or SPL), depending on who has produced it and whether it is for a charity or not. Every good Income and Expenditure report will begin with a summary of all the income received in the year, then summarise all the expenses incurred in the year and end with a profit or loss calculation (this can also be called the “Net movement in funds figure”). The basic income figures can be broken down into various categories such as donations, trading income, grants, and legacies while the expenditure figures will probably be subdivided into cost of sales, payroll, utilities etc. Charities have an extra line between their total expenditure figure and the net movement in funds to show any transfers between funds. While business accounts usually only display this year’s figures in one column beside last years figures for easy comparison, charity accounts are subdivided into several columns indicating the proportion of funds received and spent from unrestricted and restricted funds, and endowments or legacies. This way of presenting charity I&E reports can make their year-end reports much harder to read if you’re not a numbers person. Moving on to the balance sheet (also called the “Statement of Financial Position”, or the SFP). For charities and normal businesses this report has more similarities making it easier to understand if you are accustomed to looking at reports produced for a different business or charity setup.. Your balance sheet should always include sections for your assets and liabilities. Your assets will include things owned by the business (your physical assets like the building you operate from and stock waiting to be sold), the amount of money available to the business (in the bank, as cash in hand and as unpaid invoices) and any investments made. While your liabilities will include summaries of any unpaid supplier invoices, any payroll liabilities (such as any tax due to HMRC by the 19th of the following month) and any loans owed by the business or charity. By subtracting the liabilities from the assets, you get the total net asset or liability figure. I hope it’s always a positive, net asset figure for you as this indicates the business is in a much safer long-term position even if your profit figure is negative for the short-term forecasts. Below these figures on the balance sheet, you will find another set of figures. This is where things get most different between charity and business reports. At the end of a charity’s balance sheet it must have an analysis of its retained funds, where a business balance sheet will detail the owner’s capital and drawings. However, it is essential that the total figure at the end of this summary matches the total net asset or liability figure further up the balance sheet, as this is a key check that the reports have been prepared correctly. In this final section you should also find a figure you recognise from the Income and Expenditure report. Charities call it the “Accumulated Funds”, and small businesses give it various names including “Current year Earnings”. This is the profit or loss figure or the Net movement in funds figure. This is also another check you need to make to confirm the reports are consistent. If you can’t find the bottom-line figure from your Income and Expenditure report in the analysis at the end of the balance sheet, a mistake has been made somewhere and you are well advised to refuse the report until the discrepancy has been resolved. You should now have a basic understanding of your two primary End of Year reports. If you need more help understanding them, please get in touch.
by Christina Gorham 16 Apr, 2021
Does your filing system look like this? Are you in the habit of stuffing your financial records in a shopping bag or shoebox in the order you remember to add the bits of paper to the collection, trusting your bookkeeper or accountant to know what it all means? Did you realise we can spend more time sorting out records stored like this than we spend on the actual bookkeeping and this will be one reason your fees are so high? If you want to save money on your year end, it pays to invest a little time and effort throughout the year in sorting your records out a bit and in checking nothing is missing. Even sorting the records into Bank account statements, income, and expenditure will save a considerable amount of time and money. If filing is not your thing, a good bookkeeper or administrator with bookkeeping experience will be able to set up and maintain your records. They will also chase you for missing receipts and invoices while you still remember what they are for and will be able to easily locate any bit of paperwork you or your accountant may require. A good bookkeeper will go further than an administrator. They will keep accurate records of who owes you money, how much is owed and how old the debts are. This makes it a lot easier to keep on top of cashflow as you will be able to chase your outstanding debtors, confident in the knowledge that the debt is truly outstanding. Good bookkeepers will keep an eye on your outstanding invoices too, pointing out whenever an invoice received has not been paid, helping you to avoid late penalties and to claim early payment discounts. But there are other advantages to a good filing system. Good filing leads to good computerised records, and the ability to report on the financial state of the business as often as you want. Accurate reporting on the current state of the business can help you respond to surprise events such as supplier price rises and pandemics. I’ve covered the potential impact of good reporting in a different article. To summarise, good filing results in: • Lower year end fees • Searchable records • Accurate debt and debtor management • Current reporting on the businesses financial position and sustainability leading to security, confidence and new business opportunities for growth and investment. How much are you missing out on because, “Filing is not my thing”?
by Christina Gorham 12 Mar, 2021
Many in finance are still processing the potential impact of the chancellor’s budget announced last week and how it will directly impact their clients. In this blog, I hope to help you understand some of the potential financial impacts as we look forward to leaving lockdown and embracing a vaccinated new world. So far as grants are concerned, the new date to watch out for is September 30th. On 30th September the CJRS grant and the SEIS grant will end (following a three-month tapering off period). This is also when the reduced VAT rate of 5% for hospitality, accommodation and attractions will end, rising to 12.5%, and when the extended Apprenticeship incentives will finish. Why is September such an important month? Because Mr Johnson wants to remove all restrictions on 21st June. If all goes according to the latest road map, the last restrictions to trade will be lifted on 21st June, though many businesses are hoping to open again from 12th April. This gives us three to five months to get back on our feet before the support ends and businesses will be back to fending for themselves. Is this long enough for businesses to get back on their feet? Probably. A quick google search reveals it can take a start-up business up to five years before it starts making a profit (if at all) but that the business wants to have a maximum of six months’ worth of debt on its books. Given your business is not a start-up and, presumably, has an established customer base and reputation, you won’t need to work as hard to re-establish your position within the market (especially if you have been working on this throughout the pandemic). You will already have most of the resources you will need, so you won’t have this extra outlay before you can “open the doors” and you will already know your preferred suppliers. Your already past the starting point meaning your primary focus will be on persuading customers it’s safe for them to come to you and spend their money. This is where the uncertainty lies. None can predict how long it will take for “Jo Public” to emerge from the protection of their homes and start spending again. Especially given what happened last time, when we flocked out of our homes only to be sent back having caused a second wave. I suspect people will be much more cautious this time, afraid of causing another national lockdown and of loosing another summer. If you have the financial capacity, it may help to go above and beyond when it comes to cleaning, safety and informing potential customers this is what you’re doing. The added reassurance may help encourage them to visit you first. Equally, no one can predict how many will prefer to save their money, investing “for a rainy day” instead of splashing out on non-essential goods and services. After all, it’s the people and businesses that had reserves allowing them to pivot in those first few months that have survived and thrived during lockdown. Successfully getting the economy back on its feet will involve a lot of reassurance and confidence building combined with innovative marketing strategies many developed during lock down, and you will need to start laying the groundwork now (if you haven’t already) to get that important commercial advantage. It’s time to invest in the future.
by Christina Gorham 05 Mar, 2021
In his second budget as chancellor Rishi Shunak made a number of promises. Here are the ones I think most likely to affect small businesses and charities. • CJRS scheme extended to September 2021, starting to taper off from July. • SEIS extended to Sep 2021 and includes people who filed their 2019-20 returns on time. • New Restart Grants now available as a one-off grant for hospitality, accommodation, leisure, personal care and gym businesses in England. • New Recovery Loan Scheme to help businesses of all sizes through the next stage of recovery. • 5% VAT reduced rate for hospitality, accommodation and attractions extended until September, rising to 12.5% until 31st March 2022. • Extension of Apprenticeship hiring incentives to September and introduction of new “flexi-job” apprenticeship, enabling apprentices to work for more than one employer. • New Taxpayer Protection Taskforce to crack-down on COVID fraudsters who have exploited UK Government support schemes. • Duty on fuel & alcohol has been frozen, not raised as some feared. • “Future Fund: Breakthrough” to invest in highly innovative companies such as those working in life science, quantum computing & clean tech. • Minimum wage increasing to £8.91 per hour from April but the PAYE & NI rates have remained unchanged. • Tax-free Personal Allowance frozen at £12,570 until 2026. Charities working in these specific fields have been promised extra support. • Extra financial support for veterans with mental health needs. • Extra funds to provide “respite Rooms” for homeless women and those suffering domestic abuse. • Extra funding for museums. • Extra funding for spectator sports, sports clubs & their governing bodies. • Funding for various green schemes, infrastructure & job creation. • Community Ownership Fund will allow communities across the UK to invest to protect the assets that matter most to them such as pubs, theatres, shops, or local sports clubs.
by Christina Gorham 26 Feb, 2021
In the tutorial “ How to reconcile your cash book and bank account, ” I said that it’s important to know your records are correct and explained some simple ways to check the accuracy. But what happens if you have a lot of cash and card transactions? What happens if someone counts the wrong change or enters the wrong numbers into the card machine? In brief, if the salesperson makes a mistake, it will appear in the records and it’s up to you to find it while remembering that it’s a lot easier to hide mistakes in a business that has a lot of small cash transactions than a few large online ones. To paraphrase my first boss “a 1p mistake found at the end of the day could be hiding £100.99 worth of mistakes accumulated throughout the day.” It could also be used to reveal fraud from dishonest employees. If you are not careful to count takings accurately, these mistakes could be missed to the detriment of your business. So, what can be done to prevent these mistakes happening? I’d recommend starting with some basic training in the importance of accurately recording every transaction and how to correct any mistakes, if possible, while setting up a system where employees can safely admit to any mistakes including those they have corrected. Some employees may need extra tools to help them, for example calculators or a special colour filter. Once you are happy your employees are able to record transactions accurately and correct or report any errors they make, the emphasis moves from recording each transaction to the cashing up process. Some businesses get the employees to cash up their own tills at the end of each shift, others have a shared till that is counted by management at regular intervals throughout the day. Regardless of who does the counting or how frequently it’s done, you need to ensure they do the job accurately while taking into account any reported errors and corrections. Remember, the figures from the till report and the cashing up make your foundational documents. These can be compared to your stock take figures to discover if someone is entering incorrect or fraudulent transactions. I know of a business that used accurate bookkeeping to catch a thief. If you are in the habit of paying for expenses (business or personal) from the till. Stop! Paying for expenses using cash from the till makes cashing up at the end of the day more complicated and opens you up to another level of potential errors. Expenses should be paid from the business account or the petty cash account. If a supplier insists on cash, ask yourself why. Could they be trying to hide income from HMRC so they don’t need to charge VAT? If so, can this supplier be trusted and what is the probability of you getting dragged into things if HMRC came knocking on their door? If you are VAT registered, the implications of inaccurate records are far more significant. If HMRC suspect you have been submitting incorrect VAT returns, they can visit you and raise an estimate for the VAT they think you should have paid, and they don’t take into account poor record keeping as a reasonable excuse. How can poor counting impact on your business? You could lose a lot of money and your good reputation. In extreme circumstances, you could even loose the business, personal assets and find yourself in court. This is entirely avoidable by implementing robust checks at every stage. Contact me if you want help ensuring the accuracy of your record keeping.
More posts
Share by: