Copying formulas?

[Janie]

Hi,
I am trying to set up a formula that will allow me to allocate dates that I can expect to collect cash from bills I have sent. I have all the bills listed by months. The collections formula I am using is
January bills will be collected as follows"
In 60 days 30% will be collected
In 90 days 60% will be collected
In 120 days remaining 10% will be collected.
The percentages will not be changing, however the amounts of the billing each month will as well as the placement of 60 days from billing date.
Is there a way I can do this without retyping the formula month my month? It will take me forever and I have to apply this formula to other billings (with different percentages) so am hoping there is another way to go about this.
I've attached the wkst as an example.
Thanks,

Re: Copying formulas?



I am trying to develop a formula for a cash projection. I have a billing amount for each month. I also have a percentage of collections that will be collected each month for these billings. Collection start 30 days (or 1 month) after billing date. So for instance if billing in January was 5,000,
the first collection would be 30% in Feb, 60% of the total billing would be collected in Mar and the remaining 10% would be collected in April.
Feb billing's collection would begin 30 days (or 1 month) after billing, so the first collection at 30% would come in March, 60% in April, then 10% in May. The percentage doesn't change but the billing months and amounts do as well as the months the collections fall in.
I am trying to create one formula that I can apply for all the collections to fall into the proper months.
I posted this question earlier, then realized my worksheet was not clear enough to understand and redid it in the hopes it will be easier to follow this time.
I appreciate any suggestion anyone could offer.
Thanks!

[dluhut]

Is this what you're looking?

If it is, remember to change "$E$3:$J$3" when there's more months.

[Janie]

That is not what I am looking for, but after viewing your suggestion, I see the confusion stems from my original email not being precise enough. I didn't clarify that collections begin 30 days after billing date. I have corrected my worksheet to give you a better understanding of what I am trying to obtain.
I hope I have made it clearer.
I appreciate your help regarding this nightmare!

[dluhut]

Thanks for the clarification!

I believe attached is what you're looking for.

[Janie]

Oh my! Good thing I got I asked for help. I would never have come up with that formula! I don't even know how to read it. It works though. Thanks very much for your help.

Any recommendation where I can go to break this formula down step by step so I'll have some idea of how to creat one in the future if needed?

[dluhut]

Glad I could help!

I could explain to you how the code works (by coloring)

1st it looks to the green code and look at the calculated result of the month. It'll give a value from -11 to 11.

2nd on the purple code, the value will be assign to blank (noted by double quotation) if it's <=-12, -8 to 0 and >=4. If it's -11 or 1, it'll assign 30%, -10 or 2, assign 60% and -9 or 3 assign 10%.

3rd, the red code, it'll multiply the billing amount (which is in cell B2) based on the assign percentage and round it up to the nearest whole number.

Lastly, the light blue, if it's an error, due to the assign blank (blank multiply any numbers give error), I ask it to give blank.

Please Login or Register  to view this content.

[Janie]

That's great! Thanks again for your help.