As an accountant now working in industry, if this is what you are doing, I would suggest you learn IF, VLOOKUP, HLOOKUP, SUMIF, SUMIFS and PIVOT TABLES
a) forecast each month's sales invoicing (i.e. forecast turnover plus increase in deferred income less increase in accrued income) = net sales invoiced plus VAT
b) using historic data, estimate the % of each months sales invoicing that is collected in the current month, 1 month later, 2 months later, etc.
c) apply the typical collection profile to the forecast sales invoicing to get your forecast of debtor collections.
d) ignore the debtor days as it is not appropriate to this type of forecast.
Many thanks, Paul.
This now works perfectly.
Thank you Paul.
However, if I try using /3 rather than /4 in the INT part of the formula, it works for dates in July 2016, but now shows "2016/17 Q2" rather than "2016/17 Q1" for dates in June 2016.
Many thanks for trying.
If I use /3 instead of /4 as you suggest, it works for dates in July 2016 but now it shows "2016/17 Q2" instead of "2016/17 Q1" for dates in June 2016. Many thanks for trying.
Alan, I have a slight problem with your formula. For dates in July 2016 it is returning "2016/17 Q1" instead of "2016/17 Q2". Any suggestions?
Thank you Paul Wakefield. I think your suggestion would only work for the year 2016/17. The solution provided by Alan Rolfe above, although a longer formula, works for any date in any year, which is really what I was looking for.
All the same, much appreciated.
Many thanks, Scalloway.
As I want to create a daily model from 01/12/2016 to 31/03/2021, I would prefer not to do it on a quarter by quarter basis, but rather use a generic formula that would work for any given date in any fiscal quarter.
Still, your suggestion is much appreciated.
Many thanks, Alan Rolfe. This is precisely what I wanted.
Hi there FD4Cast,
Interesting offer. I'd love to have a look at your no risk trial. I have seen your Brochure already.