I
Ian R
I need some help with a formulae for calculating account receivables. In
essance I am trying to calculate the monthly cash released on the basis of
collection days for each month of a calander year.
The formulae should be flexible enough to handle collections up to 120 days.
Data on hand would be monthly sales i.e.
Heading:
Row 1 Column B = Jan, Row 1 column 2= Feb etc etc.
Row 2 Column A = Opening balance
Data:
Sales values: Row 2 Column B = Jan monthly Sales, Row 2 Column C = Feb
monthly sales etc
Calculation:
In row three (against corresponding month sales value) I need to calculate
cash collections through the formulae described above for every month of the
year. (Cash collected should be calculated in the formulae by referancing a
cell that contains the number of Accounts receivale days.)
In row 4 I will need to calculate my monthly closing receivables value. I.e.
Opening Sales less collections = Closing balance or the carry over to be
added to the next month sales.
In row 5 I will simply subtract each subsequent monthly closing value from
the previouse month to determine the change in Acounts receivable.
Hope this is clear --- hard to explain. Thx anyway.
essance I am trying to calculate the monthly cash released on the basis of
collection days for each month of a calander year.
The formulae should be flexible enough to handle collections up to 120 days.
Data on hand would be monthly sales i.e.
Heading:
Row 1 Column B = Jan, Row 1 column 2= Feb etc etc.
Row 2 Column A = Opening balance
Data:
Sales values: Row 2 Column B = Jan monthly Sales, Row 2 Column C = Feb
monthly sales etc
Calculation:
In row three (against corresponding month sales value) I need to calculate
cash collections through the formulae described above for every month of the
year. (Cash collected should be calculated in the formulae by referancing a
cell that contains the number of Accounts receivale days.)
In row 4 I will need to calculate my monthly closing receivables value. I.e.
Opening Sales less collections = Closing balance or the carry over to be
added to the next month sales.
In row 5 I will simply subtract each subsequent monthly closing value from
the previouse month to determine the change in Acounts receivable.
Hope this is clear --- hard to explain. Thx anyway.