Accounting Templates In Excel

What Is Accounting Template In Excel?

Accounting templates in Excel are preformatted account statements, which one can download for free and customize to fit their business needs. And these templates have accounting formulas embedded to manage the figures and calculations across financial years.

Users can use the inbuilt accounting templates or create templates in Excel from the start to generate cashbooks and accounts payable and receivable ledgers.

For example, if we require Excel accounting templates general ledger with budget comparison. Then, we can download the required template from the available options from the New tab under the File tab in the Excel ribbon.

Choosing the highlighted option in the New tab will result in the workbook General ledger with budget comparison1, shown above. It includes the accounting template and the additional tabs containing source data required to update the template.

The above mentioned method to create Excel accounting templatesgives users free access to predesigned and customizable templates available in MS Excel.

On the other hand, we can create the required Excel accounting templates general ledger with comparison in worksheets from scratch. For example, the below image shows an accounting template worksheet for the same requirement prepared from the start.

The second method allows us to manage the entire workbook, including the overall design. On the other hand, we can automate and customize it according to our requirements.

Top 5 Accounting Templates In Excel Worksheets

We shall see the top-5 accounting templates in Excel worksheets useful for individuals or firms looking to streamline their accounting department activities.

#1 – Cashbook Template

We can use accounting templates inworksheets to prepare cashbooks.

A cashbook is a financial journal that one can use to record all the cash inflows and outflows occurring during an accounting period chronologically.

Such managerial accounting Excel templates help accountants manage cash flows efficiently and track cash balances at any time.

Here is how to prepare a cashbook template in Excel.

Step 1: Select cell A1 and enter the template title, Cash Book Ledger.

And then divide the ledger into two sections. While one will record the credit transactions, the other will show the debit transactions.

Each section will contain three columns, Date, Transaction Description, and Amount.

In this scenario, we shall leave rows 4 to 10 empty to update the cash inflows and outflows’ entries as and when required.

Furthermore, such managerial accounting Excel templates contain a summary section. It will show the available balance based on the total cash inflow, outflow, and fixed balance.

Step 2: Let us enter the credit and debit transactions’ details in the respective sections, as shown below.

Step 3: Select cell C13, enter the SUM excel function to find the sum of all cash inflows and press Enter.

=SUM(C4:C10)

Step 4: Select cell C14, enter the SUM() to find the sum of all cash outflows and press Enter.

=SUM(G4:G10)

Step 5: Select cell C15, enter the formula to determine the closing balance, the difference between the total cash inflows and outflows, and press Enter.

=C13-C14

Step 6: Select cell G13, and enter the formula to display the closing balance determined in the previous step.

=C15

Next, we shall update the fixed balance in cell G14, a negative value of -$400.

Step 7: Select cell G15, enter the formula to determine the total available balance, and press Enter.

=G13+G14.

Thus, now that we have the formulas updated in cell range C13:C15, G13, and G15, we can update rows 4 to 10 with the cash credited and debited information. And once we update cell G14 with the fixed balance amount, the template calculates the total available balance in the specified financial period.

#2 – Petty Cashbook Template

We can use free accounting templates in Excel worksheets to prepare petty cashbooks.

A petty cashbook records the petty transactions of small denominations, such as stationery and conveyance. Small ventures will find a petty cashbook very resourceful, as they can maintain their daily expenses chronologically, according to their business model.

Let us see how to create a petty cashbook from scratch.

Step 1: Select cell A1 and enter the petty cashbook title.

And then, create six columns for updating the entry number, date, transaction description, cash debited and credited and calculating the cash balance, as shown below.

Step 2: Let us now enter the petty transaction details in columns A to E.

And then, select cell F3 and enter the formula to determine the cash balance on 1^st Mar after debiting and crediting the amounts specified in the row.

=E3-D3

Step 3: Select cell F4, and enter the formula to calculate the cash balance on 10^th Mar after the cash got debited and credited on 1^st and 10^th Mar.

=F3+E4-D4

Step 3: Using the fill handle, update the cell F4 formula in the remaining target cells in the range F5:F7.

Thus, the cell F7 formula will show the cash balance available on 16^th Mar after all the incoming and outgoing transactions from 1^st to 16^th Mar.

Likewise, we can develop similar free accounting templates in Excel for achieving different petty cashbook designs, tailor-made to suit our business needs.

#3 – Accounts Payable Template

We can use financial accounting templates in Excel to prepare accounts payable ledgers.

An accounts payable ledger helps us record our payable invoices in one sheet. Thus, we can organize and prioritize the invoices we must pay our vendors and suppliers.

Let us see the steps to create an accounts payable ledger template.

Step 1: Select cell A1 and enter the accounts payable ledger title.

And then, select cell F2, enter the SUM() to determine the total accounts payable, and press Enter. Also, let us set the cell F2 data format in Home – Number Format as Accounting and assume the template provides rows 4 to 20 to create the payable invoices entries.

=SUM(F4:F20)

And if we update our invoice entries with the payment due dates and balance payable amounts to the vendors entered in columns E and F. Then, the cell F2 formula calculates the total payment due based on the column F values.

And we can update the payment installments and due dates (Columns G to L) for each installment to plan and stay on top of the payments to vendors.

Thus, with such financial accounting templates in Excel worksheets, we can now identify the invoices to pay on high priority.

Furthermore, we can customize these Excel accounting templates by adding columns such as vendor-wise applicable taxes on the due amounts.

#4 – Accounts Receivable Template

We can use accounting templates in Excel worksheets to prepare accounts receivable ledgers.

An accounts receivable ledger enables one to manage client invoices every month, all in one sheet. Thus, we can track what our customers owe us and when we must receive the specified amounts.

Here are the steps to create an accounts receivable ledger template from scratch.

Step 1: Select cell A1 and enter the accounts receivable ledger template title.

And then, select cell F2, enter the SUM() to determine the total due pending from all the clients and press Enter. Also, let us set the cell F2 data format in Home – Number Format as Accounting and assume the template provides rows 4 to 20 to create the receivable invoices entries.

=SUM(F4:F20)

Also, we shall calculate the dues balance per client in column F cells using the formula given in the Formula Bar in the below image.

The formula deducts the installment amounts the client paid out of the total due invoice amount.

Step 2: Enter the invoice date, number, client name, the invoice amount payable by the client, and payment due date in columns A:E.

And then, update the payments received in installments from each client in columns G:K.

Then, the formulas in column F execute, as shown below.

For example, Client1 must pay a total invoice amount of $12,000. But cells in the range G4:K4 values show that the client paid $2,000 in two installments. And thus, the balance amount they must pay us becomes $10,000 (Difference of $12,000 and $2,000) in cell F4.

Likewise, the balance amounts for each client get calculated, and cell F2 shows the total dues the clients owe.

#5 – Aging Schedule Of Accounts Receivable

Typically, the longer the accounts receivables are overdue, the more likely they can become doubtful debts. So, businesses prepare an aging schedule to separate the accounts receivable into time brackets and monitor whether their clients are paying on time.

We can create accounting templates in Excelworksheets to determine the aging schedules of a firm’s accounts receivables in the order of their due dates.

The following example shows one template design, showing the aging schedule.

Step 1: Select cell A1 and enter the ledger title.

Step 2: Select cell D2, enter the SUM() to determine the total due pending from all the clients, and press Enter. Also, let us set the cell D2 data format in Home – Number Format as Accounting and assume the template provides rows 4 to 20 to create the receivable invoices entries.

=SUM(D4:D20)

Step 3: Select cell G2, enter the TODAY excel function to display the current date, and press Enter.

Step 4: Update the accounts receivables data in columns A:E, as shown below.

So, cell D2 calculates the overall invoice amount pending due payments from the clients’ side.

Step 5: Select cell F4, and enter the IF excel function to determine the time bracket the due payment date falls for the first client.

=IF(E4-TODAY()>30,”Payment Due In Over 30 Days”,IF(E4-TODAY()>25,”Payment Due In 25 – 30 Days”,IF(E4-TODAY()>20,”Payment Due In 20 – 25 Days”,IF(E4-TODAY()>15,”Payment Due In 15 – 20 Days”,IF(E4-TODAY()>10,”Payment Due In 10 – 15 Days”,IF(E4-TODAY()>5,”Payment Due In 5 – 10 Days”,IF(E4-TODAY()>0,”Payment Due In 1 – 5 Days”,IF(E4-TODAY()=0,”Payment Due Today”,”Exceeded Due Date”))))))))

And using the fill handle, enter the formula in the remaining column F cells.

The IF() checks the difference between the payment due date and the current date and returns the aging schedule according to the TRUE condition in the specific cell.

For example, the difference between 4/30/2023 and 4/3/2023 (current date) is greater than 25 days but less than 30 days. And thus, the formula returns the aging schedule as “Payment Due In 25 – 30 Days”.

Thus, we can now check whether the clients regularly pay us and identify those we must remind about the due payments.

Important Things To Note

• Before downloading accounting templates in Excel, check the Default personal templates location field in File à Options à Save. Ensure it is C:\Users\<Username>\AppData\Roaming\Microsoft\Templates.
• Update the applicable formulas in the cells displaying the dates and total payable and receivable amounts, leaving the remaining sections empty. And then save the automated accounting template for reuse.
• While the original downloaded accounting template gets stored in the abovementioned location on the computer, we can store the customized template in the same location. But the file type should be Excel Template when saving. Otherwise, we can save it as a standard Excel file in the desired location on the computer.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Accounting Templates In Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Accounting Templates In Excel. Here we learn how to create accounting templates in excel worksheets with examples & downloadable excel template. You can learn more from the following articles –

• Accounting Number Format in Excel
• IRR vs XIRR
• Auto Format In Excel