Hope somebody can help me with this.
What I am attempting to do is set up a cash flow projection and I need to capture when the actual cash comes in.
(I’ve attached a worksheet to help explain what I am trying to do)
Basically, I have the monthly billing. So for instance if I bill 3,000 in January, I plan on getting the first of that cash in 30 days after January (which is Feb). What I am saying is in February, I will collect 30% of January billing, in March I will collect 60% of January billing and in April I will collect the remaining 10%. I can get this to work fine the first month, but I can’t find anyway to copy this formula that will work for all the months. When I get into Feb billing..30 days from that is March so the first 30% would come in March, the next 60% in April and 10% in May.
I think I need a formula that is rolling forward, if you know what I mean. I think it needs to say something like take Jan billing + 30 days (which would put it in Feb) @30% then 30 days from Feb (which would be March for the the next 60% that would be collected etc.
I had posted this to the forum earlier, but after I read the response, I realized I hadn't made clear enough what it was I was looking for, so I have corrected the original posting question and the worksheet. The attached worksheet (highlighted in yellow)includes a formula I had received originally. This formula however, did not take into consideration that there is a rolling 30 days to each percentage (my fault for not clarfiying more clearly)
I'd appreciate any help or suggestions anyone can offer.
Thanks
Bookmarks