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.
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...
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.
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.