After studying your scenario for some time, I believe that the formulas that you are using are correct
. However, the focal point on the correctness of your calculation is derived on HOW you calculate the 30% drop off.
Imagine, 30% drop off monthly can mean that EVERY month, 30% of the CURRENT figure (sales or premium paid) is lost because the policy holder discontinues. So, if this is the correct interpretation of the "30% drop off", the formula you are using is incorrect, in the sense that the 30% is always based on the FIRST month which is C5*D5.
If you are deducting 30% based on C5*D5, then in the fourth month, it is already 120% which can mean that all 40 policy holders discontinued with their plan.
So if 30% is deducted off the current month, yes you can go negative, but the figures are realistic.
Here is the completed and modified file I made for you:http://wikisend.com/download/478084/Sampleforformula_rev1.xlsx
In that file, I corrected the formula that is suppose to deduct 30% for the drop off so that the 30% is taken from the previous month value, not the original value.
What I did was instead of deducting 30% from the previous month value, I took 70% and then added to the original formula that you have.
For instance, you have the formula:
I made it as:
Where F2 represents 70% of previous month's value since this is equivalent to adding the value less 30%. Therefore, all operations now are addition.
The sheet called 30%DropOff is a complete copy of your original work correcting only the calculation for the 30% Drop Off. Instead of calculating it always
from the first month, it is calculated based on the value on the previous month.
I also created a sheet called 30%DropOff Income_Only. This only reflects all income figures, that is, payment from policy holders. There is no expenses deducted on the thought that commission and acquisition cost are incurred only in the first month.
Please remember to rate my service positively (3-5 stars/faces)
if this helped. Tips
are always highly appreciated!
If you need further assistance, please do not rate me negatively
. Instead, please ask me more or reply to me so that I can help you further.