So, say you have a standard debtors report in Excel.
Customer, Pre May, May, June, Current, Balance, Days Credit
And in some further columns to the right I want to forecast money due this month, next month and the following month.
How can I do that? Driving me mad as basically depending on the days credit I need to sum different cells! Tried with an offset too but I cant find one that will work consistent across all ranges.
Someone must have done before!
IF is the function you want to use, you can nest them so that they work through each scenario in turn and do different things depending on certain conditions.
It doesn't sound overly complicated, but you need to be 100% on top of what the mathematical model is that will give the output that you're after if it's going to be accurate and useful.
I presume your customers usually pay x days after you issue an invoice and that x is fairly static? If that's the case then I'd be happy relying on the cash-flow forecast, but the more x varies, the less reliable it will be.
I would do this by starting with a system that records the number of days taken to pay by each customer. For each future invoice I would then calculate an expected payment date based on the invoice date and the usual payment pattern. I would then use the IF statements to pull together the values of all invoices that you expect to be paid in the time periods that you're interested in. I would probably also look at a measure of the consistency of invoice payment times and use this to feedback into a level of confidence for the output figure and then apply this by highlighting the cell green for good, red for less reliable etc.
That any help?
its a bit of a tricky one, because you have to get the sheet to "keep count" as its passing along a timeline. Very hard to do in two dimensions. It's doable if you only need it to keep count for say 2 periods I think.
Write out in words what your logic is, using names for cells and put it up here, I might be able to have a look later.
Thanks - both useful, have decided to use a actual invoice transaction list which includes date, calc due date and then its simple to ascertain what is due when based on the month. Easier than using a debtor report!
Yeah, always start from the source data if you can!