5 tips for optimising accounting with Excel and pivot tables (2024)

You don’t manage your accounting internally? Then you are not always up to date on your liquidity situation. Don’t be in the dark thanks to a simple list in Excel, in one column, with essential information such as: Transaction type, due date, third party (customer, supplier…), amount, payment method, etc. A daily entry of your receipts and payments in this list allows you, thanks to the pivot table and its integrated statistics functions, to totalise your current or future expenses and income in a few clicks, to calculate your actual or estimated cash balance thanks to the calculated fields, and to sort and total your transactions by period (month, quarter, year…).

While you are making your entries, you can update your pivot table with just one click.

At the end of the financial year, you must calculate the value of your securities and determine the unrealised gains or losses. By keeping a list of purchases and sales of your securities with information such as: Title, type of transaction, date of transaction, quantity, unit price purchase, unit price sale, purchase value, sale value, etc., you can easily use the pivot table to determine the total value of your portfolio, calculate the average purchase price per security, determine the net asset value and calculate the “Values” of the financial year.

Tracking securities is just one example, you can do the same for other inventory transactions, such as foreign exchange transactions, to identify likely exchange gains or losses, tracking doubtful receivables, to manage your reserves, etc.

Numerous specialised software programs (invoicing, commercial management, accounting, payroll, etc.) allow you to record your activity data on a daily basis. They offer numerous standard statistical reports that do not always meet the specific needs of each user. This is why most of them offer the creation of export files that you can easily use with the Excel software: Text files, CSV files or even Excel files directly.

Import functions such as PowerQuery, which are integrated into the Excel software, allow you to import and convert your data with the option of saving changes so that you do not have to make them again. Combined with the use of pivot tables, your imports and statistical calculations will be simple and fast.

As an employee in the accounting department, it is often part of your job to prepare the VAT return for small structures, even though the accounting entry has not yet been finalised. Provide your customer with a ready-to-use Excel file that includes:

• An initial worksheet for tracking its customer and supplier invoices for the year with easy-to-transfer information: Invoice date, type of third party involved, amount excluding VAT, VAT rate, VAT amount, amount including VAT, payment date if the customer/supplier is subject to VAT on incoming payments, etc.
• A second worksheet with a pivot table on one side that calculates sales and VAT collected by tax rate and a table on the other side to determine the deductible VAT on purchases.
• A filter on each table to select the relevant month.

This file will not only make your work easier but will also enable your customers to go one step further when analysing their invoices.

Daily monitoring of your accounting data enables you to make the right decisions at the right time. Excel offers you various tools to create a meaningful dashboard:

• The pivot table: it allows you to quickly display useful statistics in the form of values or percentages: Total turnover and/or turnover per activity, tracking your costs, status of your liquidity…
• A pivot chart is often more meaningful for analysing the share to visualise…
  • of your turnover per activity.
  • of your expenses per item.
  • of the development of your turnover per month.
• You can use filters to emphasize certain information and not display the rest: Limit the display to one activity, one expense item, one department or one location or branch of the company…
• Segments that make it clear which filters are currently being applied to your data. The chronological segment to get a quick recalculation of statistics by time period (month, year, …), etc.

Whether as an aid for completing daily tasks, to facilitate checks and decision-making or to prepare for stocktaking work – Excel is the ideal program for analysing and evaluating your accounting data. Excel is particularly popular because it is reliable, easy to use and inexpensive. With MEDIAplus you’ll become an expert, mastering Excel and all the MS-Office tools 😉

To find out more about MEDIAplus elearning and how it can transform your learning experience, feel free to contact us!