• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Simple xls customers cash flow samples

H

hitman

New Member
Hi!! First at all my best congratulations for the brilliant and user friendly work you're doing here. I'm currently working in accounting and financial area of a big italian group. Well, it seems they dont want invest further money on developing of accounting and mngmt softwares I'm using. So, I created a personal one for the forecast consolidation but needs implementation and time. Expecially for cash flow concerning customers and suppliers; we have hundreds so I'd like to create a monthly customers and suppliers cash flow related to payment terms I first decided.

The enclosed file:

https://skydrive.live.com/edit.aspx?id=documents&resid=CD31EC8AC9CB8D4F!146&wd=cpe

shows a sample monthly customers turnover (upper part) and I'd like to convert in a monthly cash flow (lowest part) according to macro or excel functions. Do you think is possible to report it ?? thanks in advance!!

Daniele
 
Hi Daniele ,


Your link is not working ; can you please see whether you have shared your file , and then posted the access link ?


Narayan
 
Hi Narayan, thanks for the reply.

I think this one should work.


https://skydrive.live.com/#!/edit.aspx?cid=CD31EC8AC9CB8D4F&resid=CD31EC8AC9CB8D4F%21146&app=Excel


or


https://skydrive.live.com/#!/edit.aspx?cid=CD31EC8AC9CB8D4F&resid=CD31EC8AC9CB8D4F%21151&app=Excel
 
Hi Daniele ,


I have downloaded your file ; can you explain how the figures in the upper table should be processed and displayed in the lower table ?


Narayan
 
Hi Narayan!!

Please download this version in which I reported an example of what I'm looking for.


http://wikisend.com/download/142706/CUSTOMERS_CF_2.0.xls


The column B include customer invoices aging days- For example in January I'm going to issue 150$ invoice and payment term is zero days, so it means I probably receive payment in the same month (january 2013).

In march-13 I'll issue 11.111$ invoice and I'll receive my money after 150 days, so in August.

I'd like to create an automatic tool in which whatever figures I fill the in the upper part the cash flow lower part will be updated.

Hope it helps.

Thanks.


Daniele
 
Hi Daniele ,


Can you check this workbook with some more data , and let me know what the results are ?


http://sdrv.ms/UYM0Rr


Narayan
 
Hi Narayan, I checked and it perfectly works. Now I want try by myself if is possible to obtain the same output changing manually "payments days" in column B. For example, in B4 cell choosing 90 days instead of 0 days I'd like cash flow were automatically updated. Do you any formula to advice for that matter??

Thanks for your superb work.


Daniele
 
Hi Daniele ,


At present , the formulae in your output table ( C15:R19 ) are dependent on the values in the range ( B15:B19 ).


Also , at present , the data in B15:B19 has been entered manually ; if it will always be the same as the data in your input table ( in the range B4:B8 ) , then what you can do is put in formulae in B15:B19 to copy the data in B4:B8 i.e. in B15 , put in the formula =B4 , and copy downwards. Whenever you change the data in your input table , it will get reflected in your output table.


Once you do this , the formulae in your output table will work correctly ; if you change the value in B15 to 90 days ( from the 0 days at present ) , the amount of 150 will shift from C15 to F15. If you put in the above formulae in B15 through B19 , and change the value in B4 , again , your output table will change appropriately.


Narayan
 
Hi Narayan!

Sorry for coming with a new request on the same matter but I use excel 2003 at work and the formula in the sheet doesn't work there (probably it was not included in former MS office excel versions).

Thanks for the tips.

Daniele
 
Hi Daniele ,


The reason for this is that Excel 2003 and previous versions of Excel do not have the IFERROR function ; I am reproducing below the existing formula in C15 :


=IFERROR(INDEX($C4:$R4,MATCH(TRUE,DATE(YEAR(C$14-$T15),MONTH(C$14-$T15),1)=DATE(YEAR($C$3:$R$3),MONTH($C$3:$R$3),1),0)),0)


In case you do not have Excel 2007 and later , where this function is available , you can rewrite the above formula as follows :


=IF(ISERROR(MATCH(TRUE,DATE(YEAR(C$14-$T15),MONTH(C$14-$T15),1)=DATE(YEAR($C$3:$R$3),MONTH($C$3:$R$3),1),0)),0,INDEX($C4:$R4,MATCH(TRUE,DATE(YEAR(C$14-$T15),MONTH(C$14-$T15),1)=DATE(YEAR($C$3:$R$3),MONTH($C$3:$R$3),1),0)))


Remember to enter it as an array formula , using CTRL SHIFT ENTER.


Put this formula in C15 , and copy it across and down.


Narayan
 
You must log in or register to reply here.
Back
Top