I'm sure this question is asked a lot but i'm tying myself in knots trying to get a decent debtors calculation for a budgeted balance sheet and cashflow.
I usualy use goal-seek in excel to predict the receipts figure based on set debtor days. So if the b/f figure is £2,000,000 and sales in the period were £800,000 and debtor days are 77, I would use goal seek to set the debtor days cell to '77' by changing the receipts cell in the calculation.
I'm just wondering if there is a more scientific approach to this?
I started to figure out the phasing of the £2,000,000 and then tried to figure out when the monthly balances would be paid. So, assuming 77 days, the £2m would be £779K from Month 12 (prev year), £779K from Month 11 and the remaining £441K from Month 10 (assuming that the £2m is made up of 30 days + 30 days + 17 days with the 17 days being the oldest). At the end of month 1 (new year) you would have these balances...
I then tried to figure out what would be received in month 1.
Assuming everything that's older than 77 days, It should be all of the 90-120 row (£441,558) plus 13 days worth of the 60-90 row (£779,221 * (13/30)). This is equal to 30 days worth of the opening balance (£2,000,000 * (30/77)).
When I drag this formula accross the year I get varying debtor days - when I use the goal seek method I get fixed debtor days and a more varied receipts figure.
Again, is there a better way to do this? ...am I even close? ...are there other factors that I'm missing?