# Modelling working capital adjustments in Excel

Spreadsheet modellers love to forecast income and expenditure items for the company’s income statement and then tack on the cash flow statement as an afterthought. As we are all fully aware given the economic hardships caused by the COVID-19 pandemic, it is essential to look after cash. Following are some tips for deriving cash flow figures from existing profit-and-loss projections.

To best explain this, we need to start at the beginning. Please feel free to use this Excel file to help clarify the ideas discussed below.

Consider the following example: Imagine a company just starts off in business (ie, it has no amounts due) and generates sales of \$1,000 in the period. At the end of the period, assuming no bad debts, \$753 has been paid, leaving a closing debtor balance of \$247. This difference is what I refer to as the working capital adjustment.

If we had modelled the sales of \$1,000 in the period, how might we generate the cash receipts forecast such that, as assumptions changed, the receipts would calculate appropriately?

Clearly, if we are given the closing debtor balances, the problem becomes trivial, so this example assumes we are not given those balances. Therefore, let’s consider an alternative approach and some of the associated underlying issues that need to be considered when modelling.

Let’s assume that the sales accrue evenly over a period of time and, for the sake of this example, that period is one year (365 days). Presuming (1) all sales are made on credit terms; (2) all customers pay their invoices on the day the amounts fall due; and (3) no bad debts are incurred, this can be reflected graphically as follows: Clearly, the credit period is the “gap” at the beginning of the time period, ie, 247 ÷ 1,000 × 365 days = 90 days. This can be represented formulaically as:

Days Receivable = (Closing Debtors × Days in Period) ÷ Sales in Period

Rearranging, this becomes:

Closing Debtors = (Sales in Period × Days Receivable) ÷ Days in Period,

eg, in our example: 247 = (1,000 × 90) ÷ 365.

Therefore, in modelling, we often set the number of days receivable (and days payable) as key assumptions for cash flow forecasting.

However, it’s not always as simple as that. Consider we are planning to build a monthly model (assuming 30 days in a month), and sales for the month are again \$1,000. Debtor days remain at 90.

Based on these calculations, we would generate the following control account: Erm, that’s right: make sales of \$1,000 and have \$3,000 (= 90 ÷ 30 × 365) owing to you by the end of the month. Welcome to Loan Sharks R Us. That’s nonsense — and yet, as an experienced model auditor, I have seen this erroneous calculation crop up on a regular basis.

Monthly forecasting

The problem is that in this current economic climate most businesses want to prepare monthly — more likely weekly and even daily — cash flow projections. Clearly, if the days receivable or days payable assumption exceeds the number of days in each forecast period, this approach is inappropriate and will lead to calculation errors.

So what do we do in this situation?

For example, in a monthly model, if payments are made exactly one month or two months or three months later (and so on), the resolution is simple: The receipts can be calculated using a simple OFFSET (displacement) formula.

Therefore, let’s complicate the scenario slightly. Imagine we are building a monthly forecast model but that the days receivable is 75. For the purposes of keeping this article reasonably brief, I will simplify the problem by assuming an average number of days in a month (say, 30). Using this simplifying assumption, this means that payments are made on average 2.5 (2.5 = 75 ÷ 30) months after the sale has been made.

That 2.5 months figure is important. The integer part (2) denotes how many complete months (including the current month) have sales payments outstanding. The residual (0.5) shows the proportion of the month preceding these complete months that is also outstanding. With this borne in mind, the OFFSET function can now come to the rescue, as shown in the screenshot below. In the illustration above, cells J18 and K18 break the number of days receivable (cell G18) into the number of whole months and residual proportion, respectively, assuming that each month has 30 days (cell H13).

The key formula here is the calculation for Closing Debtors (Cash Receipts is simply the balancing figure). For example, the formula in cell J28 (above) is:

=IF(\$J\$18,SUM(OFFSET(J26,,,1,-MIN(\$J\$18,J\$23))),)
+IF(J\$23-\$J\$18<=0,,OFFSET(J26,,-\$J\$18)*\$K\$18)

It may seem a little complex upon first inspection, but it’s not as bad as it seems. Essentially, there are two parts to this formula identified by the two added IF statements:

1. IF(\$J\$18,SUM(OFFSET(J26,,,1,-MIN(\$J\$18,J\$23))),) considers the completed number of months where sales remain outstanding and adds up the sales for these periods.

In essence, this part of the formula checks that the number of completed months is not zero (in this case the amount is just zero), and assuming this is not the case, it sums the sales for the relevant number of completed months (ie, starts with the current month and then considers the sales in previous months, working from right to left in the spreadsheet). The MIN formula is required to ensure that the model does not try to include periods prior to the beginning of the forecast period).

2. IF(J\$23-\$J\$18<=0,,OFFSET(J26,,-\$J\$18)*\$K\$18) considers the residual (remaining) amount for the month before the earliest completed month. For example, if the credit period is 2.5 months and the current month is April, then March and April will be “whole months” where no payment has been received, with half of February’s monies still outstanding, too. The reason for the IF statement here is to prevent calculations considering periods before the beginning of the forecast period.

To clarify, consider the Closing Debtor figure of \$1,050 in Period 5 (above, cell N28 in the illustration). This is calculated as the sales for Periods 4 and 5 (400 + 500 respectively), plus half of the Period 3 sales (300 × 0.5 = 150), ie, 400 + 500 + 150 = 1,050.

More complex scenarios

It only gets more complicated. What if payments are not made evenly? Or some sales are written off as payments are never made (ie, bad debts)? Depreciation anyone ...?

Think about it: Depreciation arises when cash is laid out in one period but the costs are allocated over multiple periods. Debtors arise when sales are made in one period, but the cash receipts are allocated over multiple periods. It’s the same logic, just different labelling! The calculations can be reviewed in depth in this Excel file, but what can be clearly seen is the similarity to a depreciation calculation. In this illustration, the Cash Receipt Profile percentages do not add up to 100%. This is deliberate — the missing 5% is the assumed bad debt here.

Word to the wise