Business working days & Cash FLow

  • Thread starter Thread starter tr2yhb
  • Start date Start date
T

tr2yhb

I have been asked to build a custom spreadsheet that will help an
entity determine expected cash flow from customers.

If I have 4 customers and each pay their bills in a different amount
of business days, I want excel to tell me the day that payment should
be expected and put that amount in a column with that date.

EG:
If I bill customers A, B C & D $5000 each on Jan 2, and they pay in
30, 45, 50 & 60 days, respectively, excluding Saturday, Sundays, and
Holidays, on what day can I expect payment?

I am thinking of laying the spreadsheet out in this manner:

A B C D E F --> AJ AK --> etc
Cust # of Inv Inv
Name day Date Amt 1/1 1/2 --> 2/1 2/2 --> etc

For customer A, I need the $5000 to automatically show up in the
correct cell, 30 business days after Jan 2.

I will be adding customers each day.
I need to be able to use the Inv Date.

What formula do I need in E, F, G --> to allow this to happen?

THANK YOU FOR YOUR HELP!
 
If I bill customers A, B C & D $5000 each on Jan 2,
and they pay in 30, 45, 50 & 60 days, respectively,
excluding Saturday, Sundays, and Holidays, on what
day can I expect payment?
Click to expand...

See the WORKDAY function.
 
EG:
If I bill customers A, B C& D $5000 each on Jan 2, and they pay in
30, 45, 50& 60 days, respectively, excluding Saturday, Sundays, and
Holidays, on what day can I expect payment?
Click to expand...

You can use the NETWORKDAYS function.
 
Back
Top