In a 3-statement financial model, accounts receivable can be calculated as a part of the balance sheet. Accounts receivable represents the amount of money that a company expects to receive from its customers for goods or services that it has sold on credit.
Relevant Templates:
- 3-Statement Model (for forecasting)
- Financial Statement Creator (for actuals and has cash-basis and accrual-basis logic)
Here's how you can calculate accounts receivable in a 3-statement model:
Start with the revenue line item in the income statement. This represents the total amount of sales made by the company during a given period.
Subtract any cash sales from the revenue number. Cash sales are not included in accounts receivable because they have already been received by the company. This is reconciled on the cash flow statement with a line item for net income and then reduce Sales on Account on one row and add back receipts from Sales on Account. For cash collected in the same period sales are recognized, the net income row will already have that included so there is no need for any additional logic on the cash flow statement for that.
Calculate the amount of credit sales by subtracting the cash sales from the total sales. This represents the amount of sales made on credit.
Apply a percentage to the credit sales number to estimate the amount of accounts receivable. This percentage is typically based on the historical collection period for the company's receivables. For example, if the company historically collects its receivables within 30 days, then you might assume that 90% of credit sales will be collected within 30 days and the remaining 10% will be collected in the following month.
Add any beginning accounts receivable balance to the calculated accounts receivable balance to arrive at the total accounts receivable for the period. Each new month, you reduce any cash that was collected on accounts receivable and increase by any new sales on account where cash was not collected.
Make a Cohort Matrix
You will have to assume some collection period for accounts receivable, which you can do by building a receivables cohort collection matrix and define the % of the total sale that is collected in the life of the sales on account. For example, month 1 50% is collected, month 2 25% is collected, month 3 10% is collected, and so on for as many months as it takes on average to collect the total cash for sales made on account. This will drive the balance of receivables as well as the cash coming in each period.
It's important to note that accounts receivable can have a significant impact on a company's cash flow. If customers take longer to pay their bills, the company may experience cash flow problems. Therefore, it's important to closely monitor accounts receivable and take steps to collect outstanding balances in a timely manner.
Article found in Accounting and Finance.
