How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4463
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

I need to create a formula in excel that will calculate income

Resolved Question:

I need to create a formula in excel that will calculate income over time. We have an insurance product that we receive an initial income on sign up then a recurring monthly income. Stats show that there is a 30% drop off on the policy monthly. An example is as follows:
1st Premium is $ 579.00 Commission is $ 289.50 the cost of signing the client is $ 356.00 so we have a shortfall of -$ 66.50 on the first premium. We will sign on 40 new members per month. Our annuity revenue is $28.95 / policy per month. As said the drop off rate would be 30% per month.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Jess M. replied 1 year ago.
Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

Can you send me the sample Excel file with dummy data so that I can work on the formula directly with your dummy data? If this is possible, you can attach the Excel file here when you reply, or you can upload it in the link below:

http://wikisend.com

After uploading, give me the download link or the 6-digit File ID so that I can get your file.


Best regards,
Jess
Customer: replied 1 year ago.
Expert:  Jess M. replied 1 year ago.
Andrew,

Thank you for writing back with the link. I got the file. Where exactly you want the formulas and what you want to calculate? Is it in the blue cells?

Jess
Customer: replied 1 year ago.

Yes the blue cells. You will see my formula are in the blue cells. The data runs along those cells.

Expert:  Jess M. replied 1 year ago.
Andrew,

Please give me a moment to check your formulas and your actual requirements.

Jess
Customer: replied 1 year ago.

OK

Expert:  Jess M. replied 1 year ago.
Andrew,

So you are saying that the current formulas you have in the blue cells are incorrect? Im tracing the formulas to see what you really want to compute there.

Jess
Customer: replied 1 year ago.

I need to confirm them because Im getting negative values. are they correct?

Expert:  Jess M. replied 1 year ago.
Andrew,

I am understanding it. So you are calculating income with the conditions you described. Please give me a moment to try various figures and other approaches to calculate the income. I will get back to you shortly.

Jess
Customer: replied 1 year ago.

thanks

Expert:  Jess M. replied 1 year ago.
Hi Andrew,

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:
=E5+(C5*D5)-(C5*D5*0.3)

I made it as:
=E5+F2

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! Cool

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.


Thank you!

Best regards,
Jess
Customer: replied 1 year ago.


Hi,


 


It doesn't seem as if you are adding the previous month or have I missed something?

Expert:  Jess M. replied 1 year ago.
Andrew,

Thank you for writing back. The original sheet that you gave me is now named "Original Sheet". Now, what I created I named the sheet as 30%DropOff. It contains ALL of your formulas except that I corrected the application of the 30% drop off. Instead of calculating 30% off the original figure (C5*D5, for example), the 30% is based on the previous month.

For instance:

Month 1: 100
Month 2: 70 (70% of 100)
Month 3: 49 (70% of 70)
Month 4: 34.3 (70% of 49)

So instead of taking 30% OFF of 100 always, it is taken in the previous month.

What are you referring to about not adding the previous month? Are you referring to the "Income_Only" sheet? Well, yes, that is how I understood the scenario. The income for the second to the twelfth month are taken and no expenses incurred for commission and acquisition cost since I believe these costs are only incurred once and that is in the first month.

If you take commission and acquisition cost every month, then the sheet I created called Income_Only is incorrect.

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated! Cool

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.


Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4463
Experience: Computer Software Specialist for more than 10 years
Jess M. and 4 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
Jess M.
Jess M.
329 Satisfied Customers
Computer Software Specialist for more than 10 years