charliepie
New Member
- Joined
- Aug 27, 2015
- Messages
- 2
There is so much info on this forum but it's quite hard to find exactly what I'm looking for so please bear with me
...I've agreed to help a friend out with putting together a cashflow spreadsheet as he has run into problems with his bank in the past due to unexpected variances in his account. Now I thought this was going to be fairly easy as I'm not totally new to excel but I may have overestimated my own abilities or underestimated the complexity of what we're trying to achieve!
It's probably going to sound complicated when I try to explain but here we go...
******* company(based in UK) with UK & German suppliers.
Figures entered at point-of-sale:- (monies out)
If German supplier then out payment = 2 weeks prior to fit date.
If UK = end of month following receipt where receipt is assumed to be 2 weeks prior to fit date.
UK monthly suppliers as above.
Worktops payments:-
Laminate = monthly supplier
Solid = the month after 1 week after fit date
Sub-contractor payments = 2 weeks after fit date.
Now we come to the monies in section...
Non-account fit:
Deposit 35% = date entered
Interim 55% = 7 days prior to fit date
Balance 10% = 3 weeks after fit date
Non-account supply only:
Deposit 35% = date entered
Balance 65% = 7 days prior to delivery date
Account sale:
Paid 1 week after end of month following installation date.
So I've been playing around a little but am stuck regarding what formulae to use to manipulate the date references required for all the different amounts going in and out at different times! No wonder the bank is giving him grief for being overdrawn!!
Has anyone got any experience of something similar or could at least point me in the right direction? I know that the members here are an eclectic and smart bunch of people! I can send you copies of the monthly sales ss and the weekly cash projections ss if that might make things clearer as there are other fixed outgoings(payroll, tax etc) on that spreadsheet.
Thanks in advance for any possible help as I'm at the end of my tether here!
Forgot to add that I need to add another sheet/column for the ******* designers commissions!
...I've agreed to help a friend out with putting together a cashflow spreadsheet as he has run into problems with his bank in the past due to unexpected variances in his account. Now I thought this was going to be fairly easy as I'm not totally new to excel but I may have overestimated my own abilities or underestimated the complexity of what we're trying to achieve!It's probably going to sound complicated when I try to explain but here we go...
******* company(based in UK) with UK & German suppliers.
Figures entered at point-of-sale:- (monies out)
If German supplier then out payment = 2 weeks prior to fit date.
If UK = end of month following receipt where receipt is assumed to be 2 weeks prior to fit date.
UK monthly suppliers as above.
Worktops payments:-
Laminate = monthly supplier
Solid = the month after 1 week after fit date
Sub-contractor payments = 2 weeks after fit date.
Now we come to the monies in section...
Non-account fit:
Deposit 35% = date entered
Interim 55% = 7 days prior to fit date
Balance 10% = 3 weeks after fit date
Non-account supply only:
Deposit 35% = date entered
Balance 65% = 7 days prior to delivery date
Account sale:
Paid 1 week after end of month following installation date.
So I've been playing around a little but am stuck regarding what formulae to use to manipulate the date references required for all the different amounts going in and out at different times! No wonder the bank is giving him grief for being overdrawn!!
Has anyone got any experience of something similar or could at least point me in the right direction? I know that the members here are an eclectic and smart bunch of people! I can send you copies of the monthly sales ss and the weekly cash projections ss if that might make things clearer as there are other fixed outgoings(payroll, tax etc) on that spreadsheet.
Thanks in advance for any possible help as I'm at the end of my tether here!
Forgot to add that I need to add another sheet/column for the ******* designers commissions!
