This is a model for any small business that invoices their products and purchases and are looking for a way to track your Accounts Receivable and Accounts Payable. This template provides registers for both AR and AP accounts to log all invoices to track and organize outstanding accounts year to date. This will create a way to analyze your outstanding AR and AP without having to pay for a software program. The model includes a Summary, Charts, Top 10 Accounts, AR/AP Aging Reports, and AR/AP Invoice Registers to log all your invoices.
• In the AR and AP Register sections, each invoice is entered with the invoice date, account name, invoice number, and invoice amount. The age of the invoice will be automatically calculated based on the invoice date and the current date. The AR/AP Account Names, Category and Payment Terms can be selected from a drop-down menu; this input can be edited in the Drop Down Menu tab. The information can be filtered using the drop-down arrows next to each title.
• From there, the information is transferred to the Aging Receivables or Aging Payables where the model will total the invoices for each account in increments of 30 days up to 120 days. The template is set to categorized amounts as Past Due based on the terms set for each Account when input in the respective invoice registers. This information can also be filtered using the drop-down arrows next to each title.
• For a deeper analysis, tables of the top 10 AR/AP accounts are available in the Top 10 Accounts section. This will automatically rank the top 10 AR/AP accounts for the business based on the total outstanding amount.
• A Summary tab is provided for an outlook of total AR/AP balances and ratios for each. Enter your Net Sales and Beginning AR balance in cells B11 and B12 to calculate the current Receivables Turnover Ratio and Turnover Ratio in Days. Enter your Total Cost of Goods Sold and Beginning AP balance in cells B17 and B18 to calculate current Payables Turnover Ratio and Turnover Ratio in Days. This will tell you how often these balances are being paid off in a year’s time. The Charts tab provides a visual for the figures in the Summary, displaying AR/AP balances by the different ranges and the distribution of Net Sales/Cost of Goods Sold to their AR/AP balances to make up the Turnover Ratios. The figures in the text boxes for the Pie charts will need to be manually updated with the figures in the Summary section.
All cells in black font are input cells where custom information can be entered. All cells in blue font are formulas set to streamline the model.
