How to Use Excel for Accounting in 2022

Microsoft Excel is an invaluable tool for accountants to make numerous schedules and calculations that traditional bookkeeping software doesn’t provide. It comes with functions for performing complex mathematical calculations and also supports many add-ons for activities like financial forecasting and modeling. Because its features are so robust, it can be used with as little or as much complexity as you prefer. This article discusses how you can use Excel to enhance your accounting and fill in the gaps left by many free software and also provides templates to assist you.

When to Use Bookkeeping Software

While there are many fantastic accounting uses for Excel, I don’t recommend using it as your primary bookkeeping software to track your cash flow and classify your income and expenses. While it can be done, it’s much simpler to use one of the many free accounting software that are available in both cloud and desktop formats.

Basic bookkeeping software makes it easy to maintain detailed and accurate books. In addition to automating bookkeeping tasks, such as sending invoices (accounts receivable or A/R) and paying bills (accounts payable or A/P), many of these programs use double-entry accounting, enable you to generate financial reports, provide some form of technical support, integrate with business apps, and can scale with your business.

Unlike bookkeeping software, Excel won’t be able to:

• Generate reports based on the data from your books automatically
• Allow multiple users to work within your system easily
• Allow for access via a mobile app
• Alert you to potential mistakes with duplicate entries or data entry
• Connect to your business bank accounts or credit cards and import and sort transactions automatically

Are you interested in bookkeeping software but your budget is holding you back? Consider our recommendations for the best free accounting software.

Downloadable Templates

All the templates presented in this article are available to download free of charge and with no email required!

 

Using Excel to Reconcile Bank Accounts

Bank reconciliation is one of the most important accounting tasks for your business because it protects you from potentially overdrawing your account or becoming a victim of fraud. Reconciliation is the process of comparing your bank or credit card statement with your bookkeeping records for the same time period so that you can pinpoint any discrepancies. If there are any, you can make note of them so that you or your accountant can investigate.

To reconcile your statement, follow these steps:

1. Enter the ending balance as it’s shown on your bank statement.
2. Go through your check register and find any deposits that aren’t included on the bank statement. List the deposit date and amount in the Add Deposits in Transit section.
3. Go through your check register and find the checks that aren’t included on the bank statement. List the check number and amount in the Minus Outstanding Checks section.
4. Examine the bank statement for any transactions that aren’t included in your check register. For example, a bank service charge or unrecorded check. Record any missing transactions in your check register.
5. The amount for Balance per Check Register on the bank reconciliation should now match the ending balance in your check register. If not, repeat the steps above to locate your error.

Using Excel to Track Unpaid Invoices (A/R)

An A/R aging report is used by many businesses that invoice their clients to keep track of what payments are due to them. An A/R report shows when payments are owed, the amount due, and from which customer. It serves as an effective way to forecast cash flow and know exactly how much you are owed at any given time.

To create your A/R report, follow the steps below:

1. Enter your payment terms in the highlighted box in the upper right corner of the spreadsheet. Most companies use payment terms from Net 10 to Net 60 or Net 90 days. In this example, the payment terms are Net 30, which means customers have 30 days to pay their invoice before it’s considered overdue.
2. List your outstanding invoices in the highlighted box below that, filling in the customer name, invoice number, amount, and invoice date.
3. There’s a formula already entered that’ll calculate the due date and days late. Use this information to determine when it’s necessary to follow up with your customers that are past due with payments.
4. To add additional rows to the spreadsheet, right-click on the far left column, then select Insert 1 Above or Insert 1 Below to insert a new row.

You can use Excel to generate your invoices, especially if you want unlimited customization options such as adding your logo or changing fonts. However, you may want to consider using a free invoice generator, which automates the invoicing process. Check out our recommendations for the top free invoice generators, which include Invoice Ninja.

Using Excel to Track Unpaid Bills (A/P)

The process of tracking how much money is owed to vendors, when the bills are due, and when they have been paid is known as A/P. If you aren’t diligent with paying your vendors on time, you can end up with bad credit and a lack of supplies among other things. Creating an A/P aging file in Excel will help you to monitor your payable accounts so you know which bills you must pay first as well as how behind you are on any past due accounts. Follow these steps to create your A/P report:

1. In the highlighted section, enter the name of each vendor, the invoice number, amount due, invoice date, and the invoice terms. The due date and days late will be calculated automatically.
2. To add additional rows to the spreadsheet, right-click on the far left column, then select Insert 1 Above or Insert 1 Below to insert a new row.

Using Excel to Track Cost of Goods Sold

The main challenge of inventory accounting is determining the cost of your inventory on-hand at the end of the year versus the cost of your inventory sold during the year, also known as cost of goods sold (COGS). This important allocation can be made with either the Average Cost Method or first in, first out (FIFO) method.

Average Cost Method

The Average Cost Method assigns COGS using the average per unit cost of inventory on hand at the time of the sale. This spreadsheet will help you track your average cost:

With the Average Cost Method, the average cost per unit is calculated by dividing the total cost of inventory by the total number of units available for sale. COGS is then calculated by the average cost per unit times the number of units sold.

Here are the steps to creating your Average Cost Method report in Excel:

1. Under Inventory Purchases, enter the invoice date, quantity of items, and cost per unit.
2. Under Inventory Sales, Enter the quantity of each item sold.
3. The Cost per Unit, COGS, and Inventory Balance will be calculated automatically, giving you an accurate picture of your inventory costs. For a more in-depth look at how inventory is calculated using the Average Cost Method, read our article about the inventory aging report.

FIFO Method

With the FIFO method, items that are purchased first are assumed to be sold first and the remaining items will be the latest purchases. You can use FIFO accounting even if the physical flow of your goods doesn’t match this assumption. The accounting for FIFO inventory costs is much more complicated than average cost and I highly recommend you use specialized software to do it. QuickBooks Online Plus has outstanding inventory features and uses the FIFO cost method.

Using Excel to Create Amortization Schedules

A loan amortization schedule gives you the most basic information about your loan and how you’ll repay it. Typically, It includes a full list of all the payments you’ll be required to make over the lifetime of the loan. Each payment on the schedule is broken down according to the portion of the payment that goes toward interest and principal. You’ll also see a running tally of the remaining loan balance that’s owed after making each monthly payment, so you’ll be able to view how your debt decreases over the course of repaying the loan.

There are many ways that you can use the information in the amortization schedule. The basic purpose is to separate payments into interest, which is an expense, and principal, which reduces your liability. When you are aware of the total amount of interest that you’ll pay over the lifetime of the loan, it’s a good incentive to make your principal payments early if possible. This spreadsheet is also useful if you are considering different loans because you can enter the amount, interest rate, and loan term to determine your monthly payment and how long it’ll take you to pay off the loan. Here are the steps for creating a loan amortization schedule in Excel:

1. Enter the date of the first payment.
2. Enter the amount of the loan.
3. Enter the loan interest rate.
4. Enter the number of months in the loan term. The spreadsheet will accommodate up to 360 months, or 30 years.
5. Below the input section you’ll be able to view your payment schedule, the beginning balance of your loan, the interest paid, the payment amount, and the ending balance.

Importing Data from Bookkeeping Software Into Excel

QuickBooks Online can export any of its reports to Excel, which is useful as you can pull sales data, banking data, and invoices from many sources into one central workbook to support your accounting activities. Exporting the report is simple and can be done with the following steps:

1. Select Reports in the left sidebar.
2. Search for and select the report that you want to export.
3. If you scroll to the top of the report, you can adjust the time period, accounting method, and other preferences if desired.
4. At the top right corner of the report, select the Export icon. Then select Export to Excel. You also have the option to save the report as a PDF.
5. Save the report in a location that you’ll easily be able to find, like your desktop.

Here’s a sample Profit and Loss report exported to Excel:

Bottom Line

While using Excel for certain accounting tasks can be beneficial and inexpensive, it can also be a time-consuming process with the potential for errors. If you’re looking for a simpler and much more efficient way to manage your accounting, we recommend exploring different accounting software solutions to identify the one that’ll work best for you. Check out our list of the best small business accounting software for inspiration.