accounting cycle project excel

Top Excel Templates for Accounting

By Andy Marker | December 29, 2015

• Share on Facebook
• Share on LinkedIn

Link copied

In this article, you’ll find the most comprehensive list of free, downloadable accounting templates for a variety of use cases. 

Included on this page, you’ll find an accounting journal template , accounts payable template , accounts receivable template , and more.

Accounting Journal Template

‌   Download Excel Template

Try Smartsheet Template   ‌

An accounting journal is an accounting worksheet that allows you to track each of the steps of the accounting process, side by side. This accounting journal template includes each step with sections for their debits and credits, and pre-built formulas to calculate the total balances for each column. 

We’ve also included links to similar accounting templates in Smartsheet, a spreadsheet-inspired work management tool that makes accounting processes even easier and more collaborative than Excel.

Accounts Payable Template

When making large purchases for items like inventory, supplies, or equipment it may be necessary to do so on credit, which could result in multiple monthly payments made to different vendors or suppliers, due on different dates. Using this accounts payable template will help to keep track of what you owe to each party, and will provide a quick look at the total outstanding balances and due dates.

Accounts Receivable Template

Every company should have a process in place to manage the outstanding balances owed to them. Using this accounts receivable template will help streamline the process by providing a place for you to track the amounts due to your company and help prioritize collection efforts. 

Bill To Invoice Template

‌ Download Excel Template

‌ Try Smartsheet Template

For any company providing goods or services, using an invoice that looks professional and can be customized to fit your needs, is important. This simple bill template will help you get started quickly. Add your company details and payee information, provide an itemized list of the description, quantity, and price of each item you are charging for, and include directions on how your customer may remit payment. 

Bill of Lading Template

‌  Try Smartsheet Template

A bill of lading is a document detailing how goods are being shipped from a seller to a recipient. It includes details about the items being shipped, the quantity of items included in the shipment, and the destination address. Use a bill of lading template to ensure you complete this document for each shipping transaction. This template includes a signature section that should be signed by you, then the shipping company, and finally the recipient, so that if the shipment is lost, the signature detail will help identify at what point it was lost and who was liable.

Billing Statement Template

‌ Download Excel Template

A billing statement is helpful if you receive regular bi-monthly or monthly payments from your customers. Use this billing statement template to track customer invoices, account details, and billing status, all in one location. Additionally, this template looks professional  and is customizable to match your needs.

Cash Flow Statement Template

‌ Try  Smartsheet Template

A cash flow statement is important to provide a good picture of the inflow and outflow of cash within your company. It shows where the money came from (cash receipts) and where the money went to (cash paid). Use a cash flow statement template, in conjunction with your balance sheet and income statement, to provide a comprehensive look into the financial status of your company. This cash flow template includes two additional worksheets to track month-to-month and year-to-year cash flow.

Cash Flow Forecast Template

Creating a cash flow forecast can be helpful for managing your business’ finances. It enables you to estimate how much money your business will make and spend at any given point, and will allow you to take the appropriate steps to ensure that your cash outflow is not more than your inflow. Use a simple cash flow forecast template to get started quickly. Be sure you include all income including revenue and investments, and account for all expenses including fixed costs.

Expense Report Template

A simple expense report is helpful to keep track of business expenses for an individual, department, project, or company, and provides a quick way to document and track expense details. You can require that your team submit monthly expense reports or as the expenses are accrued. Use this expense report template to quickly input specific expense details and obtain approvals as needed.

Income Statement Template

An income statement, or profit and loss statement, provides a look into the financial performance of a company over a period of time. The statement provides a summary of the company’s revenue and expenses, along with the net income. Use this income statement template to create a single-step statement that groups all revenue and expenses, and is helpful for businesses of all sizes.

Payment Schedule Template

You will likely have multiple bills to pay in a month, to different companies and on different dates. It is important to have a way to track when specific bills are due, the amount that is due, and to whom. Use a simple payment schedule template to track these details. This payment schedule template will help you remember when each bill is due and be able to budget accordingly.

Simple Balance Sheet Template

A simple balance sheet template provides a quick snapshot of a company’s financial position, at a given moment. Use this balance sheet template to summarize the company’s assets, liabilities, and equity, and give investors an idea of the health of the company. 

Travel Itinerary Template

Whenever you or your team are scheduled for business trips, it’s helpful to have a travel itinerary that lists the details for transportation, lodging, car rentals, meetings and more. Use a simple business travel itinerary template to keep all of these details in one location, and be able to share the details with important stakeholders. 

Save Hours of Manual Work with Smartsheet

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change. 

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed. 

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time.  Try Smartsheet for free, today.

Looking for more

Free 30-day trial

Enable everyone to work better, at scale, with Smartsheet.

Get started for free

Download free templates

Test drive Smartsheet, the Enterprise Work Management Platform.

Get free templates

Recommended Articles

Future of Work Management Report 2023

Project Management Guide

Free Project Management Plan Templates

Discover why over 90% of fortune 100 companies trust smartsheet to get work done..

Accounting | How To

How To Use Excel for Accounting [+Free Templates]

Updated June 24, 2023

Updated Jun 24, 2023

Published May 30, 2023

REVIEWED BY: Tim Yoder, Ph.D., CPA

WRITTEN BY: Eric Gerard Ruiz, CPA

This article is part of a larger series on Accounting Software .

• 1 Reconcile Bank Accounts
• 2 Track Unpaid Invoices (A/R)
• 3 Track Unpaid Bills (A/P)
• 4 Track Cost of Goods Sold
• 5 Create Amortization Schedules
• 6 As a General Ledger
• 7 Importing Data Into Excel from QuickBooks Online
• 8 When To Use Bookkeeping Software
• 10 Bottom Line

Microsoft Excel is an invaluable tool for accountants to perform complex mathematical calculations that traditional bookkeeping software doesn’t provide. We don’t recommend that you use Excel as your primary bookkeeping system. However, it’s a fantastic tool to supplement your bookkeeping platform by performing needed reconciliations and calculations.

We discuss how you can use Excel to enhance your accounting. We also provide several templates, including bank reconciliation, invoice tracker, bills tracker, loan amortization, and general ledger, to assist you.

Are you looking for alternatives? Check out our list of the best small business accounting software .

Using Excel to Reconcile Bank Accounts

FILE TO DOWNLOAD OR INTEGRATE

Free Bank Reconciliation Template

Thank you for downloading!

A bank reconciliation is essential to keep your bank and book balances on the same page. Due to timing differences, bank and book balances don’t always agree. Most accounting software has bank reconciliation features, but they always come with the paid or premium version.

We prepared our Excel bank reconciliation template in case you want to use free accounting software that lacks bank reconciliation features. With our file, you can reconcile the check register balance and bank statement balances by adjusting them for deposits in transit and outstanding checks.

How To Use Our Bank Reconciliation Template

To prevent any problems with Excel formulas in the sheet, you only need to fill out boxes in blue. You’ll need to gather the following information:

• Balance per check register (Tip: Update first your check register before reconciliation).
• Bank statement ending balance.
• Deposits in transit, which are deposits you’ve recorded in your check register that aren’t shown on the bank statement.
• Outstanding checks which are checks recorded in your check register that aren’t shown on your bank statement.

The template matches the adjusted bank balance with the balance per check register. If it’s not equal, the template will show you the difference. Once reconciled, you’ll see the word “RECONCILED.”

Using Excel to Track Unpaid Invoices (A/R)

Free Invoice Tracker Template

An A/R aging report is used by many businesses that invoice their clients to keep track of what payments are due to them. It 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’re owed at any given time.

You can use Excel to generate your invoices, especially if you want unlimited customization options, such as adding your logo and 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 .

How To Use Our Invoice & A/R Tracker Template

After downloading the template, go directly to the CustomerList sheet. Replace all the information in the sheet with your actual customers. Once done, go to the InvoiceData sheet.

Items you can delete and replace with actual data in the InvoiceData sheet:

• Invoice Number
• Invoice Date
• Company Name
• Invoice Total

The Customer Name and Days Overdue columns cannot be replaced and deleted—these cells contain the formulas. After updating the sheet, go to the Customer Tracker or A/R Aging sheets. Right-click on the table and click Refresh to reflect your changes.

Using Excel to Track Unpaid Bills (A/P)

Free Bills Tracker Template

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 monitor your payable accounts so that you know which bills you must pay first and how behind you’re on any past-due accounts.

How To Use Our Bill & A/P Tracker Template

After downloading the template, go directly to the VendorList sheet. Replace all the information in the sheet with actual vendors, then go to the BillData sheet.

Items you can delete and replace with actual data in the BillData sheet:

• Bill Number
• Vendor Company Name

Using Excel to Track Cost of Goods Sold

Free Inventory & COGS Tracker Template

The main challenge of inventory accounting is determining the cost of inventory on-hand at the end of the year versus the cost of inventory sold during the year, also known as cost of goods sold (COGS) . Tracking COGS is crucial in determining net income. With our COGS tracker, you can compute the most recent COGS balance of a particular inventory item and determine individual inventory gross profits or cost per unit.

The computation of inventory ending balance and COGS depends if you’re using the average cost (AVCO) method or first-in, first-out (FIFO) method . For our free template, we use the AVCO method because implementing the FIFO method in Excel requires complex functions.

How To Use Our Inventory & COGS Tracker Template

The template above uses the AVCO method of inventory costing. You should only fill out the yellow cells, and Excel will compute the rest. Don’t touch the white cells because these contain formulas that compute certain values. Remember that this template assumes that you’re only tracking one inventory product. If you have several inventory products, copy the sheet and rename it, such as sheet 1 = “Light Bulbs.”

Tracking inventory in a spreadsheet is extremely time-consuming, especially if you have a lot of different products to track. QuickBooks Online Plus will track inventory automatically and split costs between COGS and ending inventory. Learn more about it through our review of QuickBooks Online .

Using Excel to Create Amortization Schedules

Free Loan Amortization Template

A loan amortization schedule shows you the schedule of payments, the amount of interest you pay, and the amount that gets deducted from the principal balance. You can use the amortization table to record monthly interest expense and update loans payable in the books. Most accounting software doesn’t have an amortization schedule feature. Our Excel template can help you prepare journal entries to update account balances.

How To Use Our Loan Amortization Template

The template is a dynamic amortization table, and it adjusts based on the number of payment periods and terms. All you need to do is fill out the yellow cells and Excel will generate the table automatically. Take note that the maximum loan term in this template is 40 years. The amortization table contains the following information:

• Period : It is the ordinal number corresponding to the number of payment periods. For example, period 1 pertains to the first payment while period 6 pertains to the sixth payment.
• Date : It refers to the date when payment is due.
• Beginning Balance : It’s the balance of the loan at the beginning of the payment period.
• Payments : It’s the installment payment you need to pay based on the payment frequency. This cell changes dynamically if you choose a payment frequency.
• Interest : It’s the amount of interest expense based on the beginning balance of the loan.
• Principal Amortization : It’s the portion of installment payments that is deducted from the beginning balance of the loan.
• Ending Balance : It’s the balance of the loan after deducting the principal amortization.

Using Excel as a General Ledger

Free General Ledger Template

The general ledger is the collection of all accounts used in the accounting system. All transactions during the period must be posted to an account in the general ledger. However, you can skip journal entries if you only have a few transactions per month and update the accounts in the general ledger.

How to Use Our General Ledger Template

We recommend using this template if you don’t need to keep detailed records of transactions. It will enable you to keep tabs of all accounts related to your business. To keep things organized, we recommend using one sheet per month so that you don’t mix previous and current month transactions in one sheet.

Instructions on how to manipulate the template are included in the Excel file. Download it and read the instructions carefully.

Importing Data From QuickBooks Online Into Excel

Exporting Reports to Excel in QuickBooks Online

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:

Step 1: Select Reports in the left sidebar.

Step 2: Search for and select the report that you want to export.

Step 3: Scroll to the top of the report to adjust the time period, accounting method, and other preferences, if desired.

Step 4: Click the Export icon in the top right corner of the report, then select Export to Excel. You also have the option to save the report as a PDF.

Step 5: Save the report in a location that you’ll be able to find easily, such as your desktop.

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

Sample Profit and Loss (P&L) Statement exported to Excel from QuickBooks Online

If you’re missing data when you open the report in Excel, it’s likely that the file is in a protected view. Select Enable Editing in Excel to view the full report.

When To Use Bookkeeping Software

While there are many accounting uses for Excel, we 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 (A/R) and paying bills (A/P), many 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
• Connect to your business bank accounts or credit cards and import and sort transactions automatically
• Create workflows to simplify accounting tasks because you still have to input formula and functions

Frequently Asked Questions (FAQs)

Can i use excel for bookkeeping.

Yes, but it’s not recommended. If you’re a very small business, using Excel is cost-effective. But for small businesses with complex processes, Excel can be counterproductive and difficult to use. We recommend getting dedicated accounting software instead.

How do accountants use Excel?

Accountants and bookkeeping professionals use Excel to perform complex calculations, like amortization tables and depreciation schedules. They also use it to compute product cost, allocate overhead, and perform budgeting and forecasting.

Is QuickBooks better than Excel?

Overall, QuickBooks is better than Excel for accounting. It’s also easier to use because you don’t need to create formulas to perform calculations and process data. Excel requires users to have in-depth knowledge of formulas and even a little bit of programming to create automations.

Bottom Line

While using Excel for 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 guidance.

About the Author

Find Eric Gerard On LinkedIn

Eric Gerard Ruiz, CPA

Eric is an accounting and bookkeeping expert for Fit Small Business. He has a CPA license in the Philippines and a BS in Accountancy graduate at Silliman University. Since joining FSB, Eric has used his expertise and authority in curating and writing content about small business accounting and bookkeeping, accounting software, financial accounting and reporting, managerial accounting, and financial management.

Join Fit Small Business

Sign up to receive more well-researched small business articles and topics in your inbox, personalized for you. Select the newsletters you’re interested in below.

Excel Accounting and Bookkeeping (Template Included)

Bryce Warnes

Reviewed by

July 12, 2023

This article is Tax Professional approved

Sometimes life forces you to use spreadsheets. If you’re planning to do your small business accounting with Excel, this is one of those times. But there are ways to make the process easier. We’ll show you how, using Bench’s Income Statement Excel Template to get started.

I am the text that will be copied.

First: What Excel can and can’t do

Microsoft Excel (and its simpler, online cousin Google Sheets) is adaptable. The better you know how to use it, the more you can get done. That being said, even if you’re good enough at Excel to compete internationally , there’s only so much you can do with it in a DIY bookkeeping context.

Single-entry bookkeeping in Excel

Before we get started on DIY bookkeeping, download a copy of Bench’s Income Statement Template . Later, you may prefer to use a different template, or even create your own. But when you’re getting started, the Income Statement Template is tidy and approachable. And it has everything you need to get a grasp of single-entry bookkeeping in Excel.

Single-entry bookkeeping is a good choice if you run a small, simple business with a low volume of transactions. Every time money enters or leaves your business, it’s recorded once, as a positive (income) or negative (expense) value.

A quick heads up about double-entry bookkeeping. The double-entry method of bookkeeping is standard for larger, more complex businesses. It’s more effective than single-entry for tracking cash flow and protecting against errors and fraud. However, it’s also more work to maintain—and difficult to set up in Excel. If you’re at the point where you believe your business needs double-entry bookkeeping, it’s time to use accounting software. Or, think about hiring a bookkeeper (like Bench ).

What you need to use the Income Statement Template for DIY bookkeeping

Before you get started with Bench’s Income Statement template, make sure you understand the differences between single-entry and double-entry bookkeeping. You’ll also need access to spreadsheet software such as Excel or Google Sheets.

Understanding the Income Statement Template

There are three sheets in Bench’s Income Statement Template:

1. The Chart of Accounts

This lists all the accounts that make up your books. Think of each account a category. Every transaction you record gets sorted into one of these categories.

2. Transactions

This contains details of every business transaction you perform—the date, a description, the amount, and the account.

3. The Income Statement

This pulls info from your Transactions and performs some basic calculations. Then it summarizes how much income your business has earned and spent within a defined date range.

Are you currently doing your own books for your business?

Customizing the Income Statement Template for your business

The info in the copy of the Income Statement Template you downloaded is only there as a placeholder. Here’s how you adapt it to your business.

1. Add or remove accounts from the Chart of Accounts

The Chart of Accounts includes an account for “Gas & Auto.” But if you don’t drive for work, you don’t need this row—so you can delete it. The same follows for any other account that doesn’t apply to your business.

Likewise, if there are any transaction categories you need for your business that are missing from the Chart of Accounts, you should add them.

A note about account types:

• Income designates any account used to track money coming into your business.
• Expense designates any account used to track money leaving your business.
• Cost of Sale (COS) and Cost of Goods Sold (COGS) are unique. These accounts track the money you have to spend to create the product you sell your customers or clients. They’re often used interchangeably—COS by retailers, and COGS by manufacturers. Learn more about calculating these expenses.

2. Clear the Transactions sheet

Before you can start tracking your own transactions, you need to clear the placeholder data.

Select everything in the Date, Description/Transaction, and Category columns, and delete it.

3. Add or remove accounts from the Income Statement

By default, the Income Statement includes data for every account listed in the Chart of Accounts. If you’ve added or removed accounts from the Chart of Accounts, make the same changes to the Income Statement. For instance, if you don’t track “Gas & Auto” as an expense, delete the row.

4. Make copies of the Income Statement for each month

In order to generate monthly Income Statements, you’ll need separate sheets for each month. Make twelve copies of the original income statement, one for each month in the year.

Then, on each Income Statement sheet, change the Date Range (Beginning Rate and End Date) to cover the relevant month. Name the sheet after the month it covers.

5. Optional—Add a sheet for tracking invoices

When you enter income from paid invoices into your Transactions sheet, include the invoice number in the description. That way, you can cross-reference it, and avoid errors—like forgetting to enter invoice payments, or entering the same invoice twice. This is a lot easier if you’ve got a separate sheet to track your invoices.

First, download a free invoice template for Excel , or get one for Google Sheets . Then, add a new sheet to your Income Statement Template. Copy and paste the data from the invoice template you downloaded into the new sheet.

Keep the tab for your invoice tracker to the right of the Transactions sheet, but to the left of your monthly Income Statements. It’ll be easier to check for reference.

6. Optional—Add a sheet for projecting cash flow

Excel spreadsheets aren’t up to the task of creating a proper Cash Flow Statement that you can present to investors. However, for your own personal planning, you can still plan your cash flow month by month in a simple spreadsheet.

To get started, check out our Google Sheets Cash Flow Projection Template . Copy and paste it into a blank sheet next to your Transactions.

Keeping your books up to date in Excel

Once you’ve customized the Income Statement Template for your business, you’ll mostly be using the Transactions sheet.

Input and categorize transactions

Every time a transaction takes place—whether you’re moving money, cash, or credit—you need to enter it into the Transaction sheet, in its own row. Then you need to categorize it.

When you use Bench , your transactions are automatically imported and categorized for you. But for DIY bookkeeping in Excel, you’ll be doing it yourself.

• Get a record of the transaction. That could be a credit or bank card statement, info in your Paypal account, or receipt from a cash payment.
• Enter the date the transaction was recorded under the Date column, using DD/YY/MM format.
• Categorize the transaction. Enter its relevant account under the Category column. Be sure to type it exactly as it appears on the Chart of Accounts. The Income Statement will look at the data you enter in order to figure out how the transaction will be categorized.
• Add any notes for yourself in the Description/Transaction column.

Save income sheets

By the end of every month, the Income Sheet should have all the data it needs to summarize your bookkeeping for that period. Make sure to keep the Income Sheets separated, organized by month.

You may want to make a copy of each one at the end of the month, and either save it or upload it to the cloud. Come tax season, your accountant will need your income sheets for the year.

Advanced accounting in Excel

If you want to get beyond bookkeeping and start understanding your finances in deeper ways within Excel, we recommend starting with pivot tables.

Pivot tables allow you to visualize and summarize your accounting info.

In our Income Statement template, we select Data > Summarize with Pivot Table

After we select all the categories we want to see summarized, here’s what we get.

This pivot table gives us a simple summary of how much we spent from each vendor and how often we bought from them.

There’s much more you can do with pivot tables. If you want to learn more, check out the Microsoft Office training page for pivot tables .

And if you just want some good old fashioned Excel tips to level up, check out this in-depth blog article from Hubspot.

Outgrowing DIY bookkeeping in Excel

If your business keeps growing, you’ll eventually need to move on from Excel.

Signs you’re ready for a bookkeeper:

• Data entry - Manually entering and categorizing transactions for your books is starting to take up too much of your time. (This can happen when you start processing more transactions per month)
• You decide to upgrade to double-entry bookkeeping
• You decide to switch to accrual accounting
• You need complex financial statements such as a Balance Sheet or Cash Flow Statement
• You start working with an accountant, and they need access to professionally kept books
• You’ve just experienced a hectic tax season, and realize you need to be better organized for next year
• You’ve experienced a phishing or malware attack on your business computer, and need a more secure way to maintain your business records
• You have bookkeeping questions that can’t be answered by searching online

Sound like you? Learn how to dump spreadsheets and outsource your bookkeeping .

Join over 140,000 fellow entrepreneurs who receive expert advice for their small business finances

Get a regular dose of educational guides and resources curated from the experts at Bench to help you confidently make the right decisions to grow your business. No spam. Unsubscribe at any time.