Excel formula for calculating Debtors & Creditors

Excel formula for calculating Debtors & Creditors

Didn't find your answer?

Dear All,

I am putting together a spreadsheet to calculate forecast P&L, B/S and Cashflow (I find the standard packages too rstrictive).

In the past I have used a similar speadsheet, but cannot remember the formula (I also didn't have the forsight to copy the details).

I want to be able to input Sales and Purchases data for the P&L and automatically calculate Debtors and Creditors for the B/S (ideally I would also like to be able to adjust the Debtor/Creditor days etc.)

Any help would be much appreciated.

Many Thanks.
Brian

Replies (5)

Please login or register to join the discussion.

avatar
By AnonymousUser
23rd Jul 2003 13:48

Other Way Round !!
Hi Neil,

Many Thanks for your reply - I actually want the calculation to work the other way round (i.e. debtor and creditor days are calculated from Sales and Purchases in the P&L, and the cashflow is then derived from B/S movements).

I have used a (mega ...) formula in a previous company, but failed to write it down.

I would like to see the effect on cashflow by tweeking debtor and creditor days.

Thanks (0)
avatar
By nkwayne
24th Jul 2003 09:47

Another way
=MAX(SUM($E6:K6)*(SUM($E3:K3)-$D6)/SUM($E3:K3),0)

does the trick where columns E onwards are months, row 3 holds days in the month, row 6 holds the sales invoiced (incl VAT), and col D holds the debtor days assumption for that sales line. Eg E6 is sales for month 1, D6 could be 45 Days, E3 could be 28 (days in the month).

The above example is the formula in cell for cash flow to month 7 (column K).

The formula says [total sales to date] x [total days to date less debtor days]/[total days to date] and therefore calculates the cumulative cash collected to date. The Max function prevents a minus figure in the first few months if debtor days exceeds cumulative sales days.

The debtor day assumption here is averaged across the whole period, however, not the last x days of sales.

The dollar signs (absolute ref) anchors the formula to commence in month 1 ($E6), but as you copy it across a row the end of the range moves with the formula to the current month ($E6:K6).

Its easy to have separate sales lines, with different debtor days assumptions, with one cash flow line per sales line, and aggregate them to get the total cash flow. That allows you to flex the cash flow with different debtor days assumptions by, eg geographical area or product line.

Also easy to apply to costs to get the cash flow for trade creditors.

Debtors & Creditors figures for the balance sheet then become opening debtors + [***] sales (costs) to date - cash flow to date.

If specific monthly figures are required it doesn't take a lot to deduct last months [***] to date from this months...

Thanks (0)
avatar
By AnonymousUser
23rd Jul 2003 14:02

Can do
Email me for some workings. Had exactly the same issue when I started my job 4 years ago and found a way round it that should suit.

[email protected]

Thanks (0)
avatar
By neileg
23rd Jul 2003 17:22

Ah, OK
When I have had a complex credit period to work with, I have used a number of rows in my cashflow or P&L sheet to hold a sort of debtors control.

Thus I would have a row that showed the amount of the current months sales collected this month, a second row for the amount of the previous month, etc. Then link this to a table with the relevant proportions in. This is much easier to trouble shoot when your forecasts don't balance.

However, I would still balance my balance sheet using the simplistic formula, and do a separate reconcilliation to make sure that the balance conformed to the model.

Thanks (0)
avatar
By neileg
22nd Jul 2003 10:48

Depends
You will have built in some assuptions in your cash flow as to how debtors and creditors will perform. You will also have made some assuptions about levels of purchases and sales.

At the simplest level, debtors = opening debtors + sales inc VAT - cash received

This formula remins true no matter what debtor days you apply, so you can ignore this in the b/s and just make sure your p&l and cashflow forecasts are properly in sync.

You can make debtor balance calculations based on your debtor days and the levels of sales in the last few months, but the formula above does the trick every time.

If this does not answer the question, please clarify.

Thanks (0)