Cash flow help

Cash flow help

Didn't find your answer?

Ello.

Use Excel 2007 etc for preparing forecasts. Can anyone please suggest a formulae for payment of debtors/creditors over percentage profile eg, 30% one month, 50% two months, 20% three months. I'm sure you get the drift, so if you change the percentage, the cash flow amends.

I currently use long, tedious fixed cell references and -, but i'm sure there is a quicker way.

Thanks in anticipation.

Replies (7)

Please login or register to join the discussion.

avatar
By stevie
14th Jul 2010 13:47

Put Percentages in their own cells

Are you creating a formula that refers to a separate cell for the allocation percentage? You can then just change the percentages to change the profile of receipts/payments.

So instead of formula being, say, B2*20% it would be, say, B2*A3 where A3 holds the value 20%.

Thanks (0)
avatar
By gsgordon
14th Jul 2010 14:01

Drop-down box?

Not much detail in question, but you could use a drop-down box to select either 1,2,3 months or the choice of percentage.

Thanks (0)
avatar
By tugwilson
14th Jul 2010 14:57

Spreadsheet formulae

Thanks for these suggestions. I do use formulae already that includes the cell with the percentage as a $ fixed cell reference, but the formulae is still too long.

 

Not sure what drop down boxes are if you could please explain further?

 

Thanks (0)
avatar
By tugwilson
14th Jul 2010 14:57

Spreadsheet formulae

Thanks for these suggestions. I do use formulae already that includes the cell with the percentage as a $ fixed cell reference, but the formulae is still too long.

 

Not sure what drop down boxes are if you could please explain further?

 

Thanks (0)
avatar
By patvanaalst
14th Jul 2010 15:11

What else is in the formula?

Could you paste in an example?  With a key to what data is in which cell reference?  If your percentages are OK then what else is in there to make it so long?

 

Cheers

Pat

Thanks (0)
avatar
By tugwilson
14th Jul 2010 16:29

Excel help re formulae

Opening debtors   200000          Payments    Payment profile (%)24> 1 m   18> 2 m   34> 3 m   24> 4 m   100            

 

Hi all again. This is the payment profile I use, where I can change the percentages and alter the cash flow. My formula uses something like =-I50*$C$310%-J50*$C$309%-K50*$C$308%-L50*$C$307%, where the $ relates to the fixed cell reference where the percentage totals lie. I just wondered if there was a quicker way.

Also please

Using the payment profile above and with opening debtors of £ 200,000 for example, how do you calculate an aged opening balance for £ 200,000? I know it's possible, it's just my failing years and needs more knowledge than I possess at the mo!

 

Thanks again.

Thanks (0)
avatar
By stevie
14th Jul 2010 20:37

Use a Table?

I just use a table with month and turnover across the top (columns) and phasing/% down the side (rows). Then each cell is just a simple calculation of % x Turnover. When you have set up the initial formulae [correctly] you can copy them across to adjacent cells.  

Thanks (0)