How to prepare outstanding Statement in Excel

A statement of account is a summary of sales made to a customer during the month, and will include any credits issued.

The statement can show in date order a summary of all sales invoices (paid or unpaid) and all payments received, or it can show a summary of just the unpaid sales invoices over several months.

A statement is usually only issued to customer whom the seller has previously approved to have an account with them, and who have signed a sales/purchases terms of agreement.

• Statements only need to be sent to customers who have to pay their account in the months following the date of invoice.
  
• Statements are usually sent once a month to remind the customer of what is owing.
  
• Customers who pay right away when buying your goods or services, or who pay within a short time frame, like 7 days, don't need to be issued a statement.
  
• A customer with a zero balance on their account does not need to be sent a statement unless they specifically request one, which doesn't usually happen.
  
• Statements can be printed and posted, or emailed to the customer. When emailing the document, you can use the Excel template of this form (download below). Simply fill in all your details, make any format changes you want, and 'save as' or 'pring' to a PDF document. If you want to fill the form in by hand, you can use the PDF printable also available below.

Statement of Account Layout

Here are completed examples. The first layout is based on the Excel Template, and the second one is based on the PDF template.

Details on Statement of Account

Some of these details may be really obvious requirements but there may be one or two things you hadn't thought of.

Name and Address

• Top Half – On the top half of the statement the customer's full business name and address needs to be included, as well as yours, the seller, with contact numbers.
  
• Bottom Half – Complete your business name and address on the remittance, and your customers name on the right.
  

Reference

This could be your customer's account number if you allocated them one when approving them as account holders.

Or you could use the month as a reference i.e. April.

Or just leave it blank.

Date

Some businesses date their statements at the last day of the month i.e. April 30, this statement will show all invoices and credit notes for the month of April. 

Some businesses date their statements at the first day of the month i.e. May 1, and will show all invoices and credit notes for the month of May.

There is no strict rule about how to date it. Choose your preference and stick with it.

Opening Balance

This is the 'total due' balance taken off the statement sent out the previous month.

Headings

• Date – this is the date on the invoice or credit note sent
  
• Ref. - this is for the invoice or credit note numbers that were sent out - payments can be allocated the reference number given it in the cash book
  
• Description – describes the type of document or transaction affecting the customer
  
• Amount – this is the amount of each sales invoice or credit sent to the customer – note the credit has a negative sign in front of it
  
• Payment – the column in which to show any payments the customer made during the month
  
• Amount Due – the amount owing for that invoice minus payments, or the credit available which reduces the total due
  

Further Details on Statement of Account

Totals/Interest

The sub-total is the sum of all the invoice amounts due minus credits.

Then the interest can be added on – calculated on overdue payments from the previous month.

This ends up with the final total due by the customer. Make the final total stand out in a bold or larger font.

Extra Details

• Comments - The comments section can be used for a personalized message by the seller to the customer such as 'thank you for your business!'.
  
• Due date - enter the date you expect the total due to be paid by.
  
• Payment type – It is important to let the customer know when payment is expected. Give them options of type of payment you accept, like direct banking (be sure to include your full bank account number), check/cheque, credit card.
  
• Interest percentage – also often called 'finance charge'. Remind the customer how much interest will be charged on overdue payments. Go to accounts receivable collections to learn how to calculate interest charges.
  

Remittance

Entering your business name and address on the remittance makes it convenient for the customer – it means they don't have to do it and they can simply pop it into a window envelope to be posted to you.

Fill in your customer's name on the right so that when you receive the remittance you know which customer it's from when you open the envelope.

Note: many customers will pay by direct banking but will post the remittance so you know exactly what they are paying, but some customers will not use it which can be a nuisance if they only pay a portion of each invoice leaving you not knowing which invoices to allocate their payment against – ring them in this instance.

Customer Cut Off Dates

Many business customers have a cut off date (such as 5th of the month) beyond which any invoices you send will not be included in their current pay run but held over for the next month's one.

So be sure to process all your invoices as soon as the month has ended and issue your statement of account right away.

Invoice/Statement Adjustments

Once a statement of account has been sent to the customer, do not then go back and amend an invoice unless it has been discussed with the customer so they are aware of any issues.

If you have to amend an invoice, send the customer both the amended invoice and an amended statement of account showing the new balance.

Home > Bookkeeping Basics > Statement of Account

Download our free aging report template

This aging report template will help you categorize accounts receivables by how long invoices have been outstanding, as well as calculate your allowance for doubtful accounts.

Here is a preview of CFI’s aging report template:

Download the Free Template

Enter your name and email in the form below and download the free template now!

An aging report is a report that categorizes the balances of a company’s clients based on the length of time their invoices are outstanding – its age. These accounts are usually categorized into 30-day intervals. Thus, allowing the company to assess its clients in greater detail than if they only evaluated them based on their outstanding balances. This is important because it allows a company to take a step back and evaluate which of their clients are risky to do business with. In these cases, the company might contact their client to notify them of their outstanding invoices and further negotiate business terms if the client fails to pay for their invoices. Thus, the aging report is a tool that helps firms weed out bad clients and improve accounts receivable turnover.

Using the example shown in the preview above, you can see that Company C owes debts of 100, which have been outstanding for over 90 days. Alternatively, this means that Company C fails to pay for 16.7% (100/600= 0.1667) of its purchases. Further business with Company C will need to be evaluated.

The aging report can also be used for other purposes. Most companies usually have provisions for how they evaluate bad debts or doubtful accounts. The longer an invoice is outstanding, the higher the chance it will go unpaid. Companies need to represent these unpaid outstanding debts in their financials. This is referred to as a company’s allowance for doubtful accounts. This aging report template can be used to calculate a company’s allowance for doubtful accounts using assumptions for each time interval.

For example, looking at the preview again, we can see that this company has an allowance for doubtful accounts of $64, based on their assumptions for bad debts.

More Free Templates

For more resources, check out our business templates library to download numerous free Excel modeling, PowerPoint presentation, and Word document templates.