Course description

If you keep the books for a small business in Excel, this course shows you how to make your spreadsheets prove themselves every month: a bank balance that agrees, VAT figures you can explain, and check cells that turn green when everything adds up. It's practical bookkeeping in Excel, from the bank file to the VAT return and the month-end close.

There are 10 sections and 50 written lessons, and each one ends with a short exercise in a practice workbook. You follow Lorraine, a freelance bookkeeper in Coventry, as she takes on the books of Amrit's print shop for a quarter: cleaning his bank files, building his cash book, reconciling three months and finding four errors in June, chasing his debtors, checking a supplier statement, preparing his VAT return and closing the quarter. In the final project you take on a second client yourself. Every section has a practice pack with workbooks to start from, finished versions to compare with, model answers and a cheat sheet.

What you'll learn

• Your spreadsheet bookkeeping system: the parts a system needs and how they link, your Excel version and the functions the course uses, setting up a workbook with tables and a settings sheet, and HMRC's record keeping periods.
• Getting bank data into Excel: reading CSV files, importing with UK dates, cleaning descriptions and amounts with formulas, proving the running balance, and splitting card payouts into takings and fees.
• The cash book in Excel: a cash book table with drop-down categories and VAT codes, the VAT fraction, posting a month from the evidence, and monthly totals with SUMIFS.
• Bank reconciliation in Excel: matching the bank and cash book with COUNTIFS, timing differences, finding and correcting errors, and a reconciliation statement that proves itself.
• Sales invoices and aged debtors: an invoice register with due dates and numbering checks, part payments, an aged debtors report, statements and reminders, and how cash accounting affects VAT.
• Purchases, suppliers and expenses: a purchase register, payment runs, staff expense claims, lines with no VAT, and supplier statement reconciliations.
• VAT workings in spreadsheets: VAT basics and the registration threshold, a workings grid built with SUMIFS, the nine return boxes, cash accounting and the Flat Rate Scheme, and checking the return before it goes.
• Making Tax Digital with a spreadsheet: digital links and bridging software, keeping links unbroken, quarterly Income Tax figures, and an audit trail of changes and backups.
• Month-end and quarter-end routines: a checklist with WORKDAY due dates, control checks that close the month, a profit and loss summary, closing the quarter and handing the year to the accountant.
• Final project and next steps: taking on a new client's spreadsheet and working through a full quarter: importing her bank file, reconciling June, Flat Rate VAT and month-end.

Who it's for

Bookkeepers who look after clients' books in spreadsheets, small business owners who keep their own books in Excel, and people moving into bookkeeping from an office or accounts role. You should be comfortable with everyday Excel and know the basic ideas of bookkeeping; the course explains every formula and method as you need it.

What this course is, and isn't

This is a course about doing bookkeeping tasks well in Excel. It isn't a general Excel course, and it doesn't teach double-entry from scratch or Self Assessment returns. The VAT, Making Tax Digital and record keeping rules are general information based on HMRC guidance on GOV.UK, not tax or accounting advice: check the current rules and talk to an accountant before you rely on them. Works with Excel 2016 or later, on Windows or Mac, and Excel for the web.

What will I learn?

• Import and clean bank statement CSV files in Excel, with UK dates and amounts that add up
• Build a cash book with Excel tables, drop-down lists, VAT codes and SUMIFS totals
• Reconcile a bank account each month with matching formulas, and find and correct errors
• Keep a sales invoice register with due dates, part payments and an aged debtors report
• Keep a purchase register, plan payment runs and reconcile supplier statements
• Build VAT workings that produce the nine VAT return boxes, with checks, including cash accounting and the Flat Rate Scheme
• Keep digital links unbroken for Making Tax Digital for VAT and prepare quarterly Income Tax figures
• Run a month-end and quarter-end routine with a checklist, control checks and a profit and loss summary
• Take on a new client's spreadsheet and work through a full quarter from bank file to VAT return

Frequently asked questions