• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting from "closing 50% of an account" each month to closing whole # every Y months

mymeeshell

New Member
Hi all,

I am building a sales model where I'm trying to determine how many accounts we'll bring on board and when. I have three scenarios, high, medium, and low. To simplify things, I said that one sales person will close 1 accounts per month in the high scenario, .5 in the mid scenario, and .25 in the low scenario.

So let's say I'm in the mid scenario and I have whats in the sample file (.5 in each month). Now, it's not realistic to close HALF an account in a given month (you either get one or you don't). So I want to convert this info into "Actual Accounts Closed" as you'll see on the sample. What I'm trying to do is come up with ONE formula that will automate this calculation for "Actual Accounts Closed" that will work across all scenarios. In the example, I typed out how it should look for the Mid scenario manually. Obviously in the High scenario, the value will be 1 each month and for Low it will be 1 every 4 months.

I tried
=IF(INT(C2),C2,IF(INT(SUM(B2:C2)),SUM(B2:C2),0))

which worked great for cell C3 (month 2), b
ut this completely breaks down after the 2nd month (in month 3 for example, it thinks I'm adding a whole new account when really I'm waiting til month 4 to close that account). It also breaks down completely in other scenarios.

I might be overcomplicating this, so if anyone has a simpler set up to achieve the same result (having new accounts come online with different frequency depending on the scenario), please let me know as I'm currently driving myself bananas.
 

Attachments

  • Sample MB Excel Issue.xlsx
    41.3 KB · Views: 2
Back
Top