Cashflow spreadsheet help needed please!

[charliepie]

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!

 

[oldbrewer]

ask us for just one of the things first, then we build it up

eg worktops from Germany - invoice to be paid 14 days before planned commencement of fitting

is this correct

 

[oldbrewer]

UK payment due table

(mytable)

01/01/2015

28/02/2015

01/02/2015

31/03/2015

01/03/2015

30/04/2015

01/04/2015

31/05/2015

item

deliv date

supplier location

supplier

cost

planned fitting start

payment due date (German)

payment due date (UK)

worktop

01/04/2015

Germany

xxxxx

100

24/05/2015

10/05/2015

cupboard

07/04/2015

UK

yyyyy

75

24/05/2015

31/05/2015

formula giving 10/5/2015

=IF(E8="Germany",H8-14,"")

formula giving 31/05/2015

=IF(E9="UK",VLOOKUP(D9,mytable,2),"")

<colgroup><col><col><col><col><col><col><col><col><col span="2"><col span="2"><col span="2"></colgroup><tbody>
</tbody>

 

[charliepie]

yes that's correct, I guess just about everything hinges around the fitment date regarding when money is coming in/out of bank account - I will have a look at your formula above shortly many thanks!