What is Ending Receivable Balance?
Ending receivable balance is the amount of money that a business is owed by its customers at the end of a period, such as a month, a quarter, or a year. It is an important indicator of the business’s liquidity, profitability, and cash flow.
To calculate the ending receivable balance, we need to know the following information:
- The beginning receivable balance, which is the amount of money that the business was owed by its customers at the start of the period.
- The sales on credit, which are the sales that the business made to its customers on account, meaning that they will pay later.
- The collections, which are the payments that the business received from its customers during the period.
The formula for ending receivable balance is:
How to Calculate Ending Receivable Balance in Excel
To calculate the ending receivable balance in Excel, we can use the SUM function, which adds up a range of cells or values. The syntax of the SUM function is:
=SUM (number1, [number2], ...)
where number1, number2, … are the cells or values that we want to add up.
For example, suppose we have the following data in an Excel worksheet:
| A | B | C | D | E |
|---|---|---|---|---|
| Month | Beginning Balance | Sales on Credit | Collections | Ending Balance |
| Jan | 10,000 | 15,000 | 12,000 | ? |
| Feb | ? | 18,000 | 16,000 | ? |
| Mar | ? | 20,000 | 19,000 | ? |
To calculate the ending balance for January, we can use the following formula in cell E2:
=SUM (B2,C2,-D2)
This formula adds up the beginning balance, the sales on credit, and the negative of the collections, which is equivalent to subtracting the collections. The result is 13,000, which is the ending balance for January.
To calculate the ending balance for February, we can use the following formula in cell E3:
=SUM (E2,C3,-D3)
This formula adds up the previous ending balance, which is the beginning balance for February, the sales on credit, and the negative of the collections. The result is 15,000, which is the ending balance for February.
To calculate the ending balance for March, we can use the following formula in cell E4:
=SUM (E3,C4,-D4)
This formula adds up the previous ending balance, which is the beginning balance for March, the sales on credit, and the negative of the collections. The result is 16,000, which is the ending balance for March.
Alternatively, we can use the fill handle to copy the formula from cell E2 to cells E3 and E4. The fill handle is the small square at the bottom-right corner of a selected cell. To use the fill handle, we can do the following steps:
- Select cell E2 and drag the fill handle down to cell E4.
- Release the mouse button and the formula will be copied to the cells below.
The final result is:
| A | B | C | D | E |
|---|---|---|---|---|
| Month | Beginning Balance | Sales on Credit | Collections | Ending Balance |
| Jan | 10,000 | 15,000 | 12,000 | 13,000 |
| Feb | 13,000 | 18,000 | 16,000 | 15,000 |
| Mar | 15,000 | 20,000 | 19,000 | 16,000 |
Other Approaches to Calculate Ending Receivable Balance in Excel
Besides using the SUM function, we can also use other approaches to calculate the ending receivable balance in Excel, such as:
- Using the arithmetic operators (+, -, *, /) to perform the calculation directly. For example, in cell E2, we can use the formula:
=B2+C2-D2
- Using the OFFSET function, which returns a reference to a cell or a range of cells that is offset from a given reference by a specified number of rows and columns. For example, in cell E3, we can use the formula:
=OFFSET (E2,0,-3)+OFFSET (E2,0,-2)-OFFSET (E2,0,-1)
This formula returns the value of the cell that is 0 rows and 3 columns to the left of E2, which is B3, plus the value of the cell that is 0 rows and 2 columns to the left of E2, which is C3, minus the value of the cell that is 0 rows and 1 column to the left of E2, which is D3.
- Using the INDEX function, which returns the value of a cell or a range of cells in a given array or table, based on a row and column number. For example, in cell E3, we can use the formula:
=INDEX (B2:E4,2,1)+INDEX (B2:E4,2,2)-INDEX (B2:E4,2,3)
This formula returns the value of the cell in the second row and first column of the range B2:E4, which is B3, plus the value of the cell in the second row and second column of the range B2:E4, which is C3, minus the value of the cell in the second row and third column of the range B2:E4, which is D3.
- Using the SUMIF function, which adds up the cells that meet a certain condition. For example, in cell E2, we can use the formula:
=SUMIF (A2:A4,"Jan",B2:D4)
This formula adds up the cells in the range B2:D4 that are in the same row as the cell that contains “Jan” in the range A2:A4. The result is 13,000, which is the ending balance for January.
