Home

I need help calculating the number of sales reps necessary to establish future clients in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-805937%22%20slang%3D%22en-US%22%3EI%20need%20help%20calculating%20the%20number%20of%20sales%20reps%20necessary%20to%20establish%20future%20clients%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20a%20problem%20I'm%20trying%20to%20analyze.%20We%20have%20to%20do%20an%20analysis%20on%20how%20many%20new%20sales%20reps%20to%20hire%20based%20on%20the%20number%20of%20expected%20onboarded%20clients.%20The%20dynamic%20part%20of%20the%20problem%20is%20that%20when%20we%20hire%20a%20sales%20rep%2C%20they%20have%20a%202%20quarter%20training%20period%2C%20after%20which%20they%20are%20able%20to%20make%20a%20client%20which%20takes%203%20quarters%2C%20and%20there%20is%20an%20implementation%20period%20of%201%20quarter%2C%20during%20which%20the%20sales%20rep%20can%20still%20make%20another%20client.%20I%20am%20trying%20to%20come%20up%20with%20a%20formula%20that%20determines%20the%20number%20of%20sales%20reps%20to%20be%20hired%20and%20when%20they%20need%20to%20be%20hired.%20The%20training%20period%20only%20happens%20once%20per%20rep.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20made%20a%20spreadsheet%20that%20models%20my%20current%20thinking%20on%20this%2C%20but%20this%20doesn't%20mean%20there%20are%20other%20ways%20it%20could%20be%20solved.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20manually%20done%20the%20first%205%20years%20based%20on%20the%20current%20estimate%2C%20and%20colored%20it%20to%20help%20give%20an%20idea%20as%20to%20what%20kind%20of%20formula%20I%20need.%20Red%20indicates%20a%20training%20period%20over%202%20quarters%20which%20happens%20only%20once%20for%20any%20sales%20agent%2C%20any%20of%20the%20blue%20colors%20(I%20used%20several%20blues%20to%20help%20separate%20things)%20means%20that%20the%20sales%20agent%20is%20working%20on%20establishing%20a%20client%2C%20and%20green%20means%20that%20they%20have%20finished%20establishing%20the%20client.%20If%20I%20had%20some%20sort%20of%20IF%20formula%20that%20had%20a%20binary%20result%2C%200%20or%201%2C%20and%20still%20matched%20this%20table%20then%20I'd%20be%20set.%20Here's%20the%20updated%20link%20%26amp%3B%20picture!%20Please%20let%20me%20know%20if%20any%20of%20this%20is%20confusing%20and%20I%20will%20try%20to%20clarify!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fforum%2Fredirect-to%2F%3Fredirect%3Dhttps%253A%252F%252Fdrive.google.com%252Ffile%252Fd%252F16ihO__kDMw_uMGJpBDV9Ba0Mw2b40fx2%252Fview%253Fusp%253Dsharing%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdrive.google.com%2Ffile%2Fd%2F16ih...ew%3Fusp%3Dsharing%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127078i4265217F6D261E30%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22SalesReps2.JPG%22%20title%3D%22SalesReps2.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-805937%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
TragicReindeer
Occasional Visitor

I have a problem I'm trying to analyze. We have to do an analysis on how many new sales reps to hire based on the number of expected onboarded clients. The dynamic part of the problem is that when we hire a sales rep, they have a 2 quarter training period, after which they are able to make a client which takes 3 quarters, and there is an implementation period of 1 quarter, during which the sales rep can still make another client. I am trying to come up with a formula that determines the number of sales reps to be hired and when they need to be hired. The training period only happens once per rep.

I have made a spreadsheet that models my current thinking on this, but this doesn't mean there are other ways it could be solved.

 

I have manually done the first 5 years based on the current estimate, and colored it to help give an idea as to what kind of formula I need. Red indicates a training period over 2 quarters which happens only once for any sales agent, any of the blue colors (I used several blues to help separate things) means that the sales agent is working on establishing a client, and green means that they have finished establishing the client. If I had some sort of IF formula that had a binary result, 0 or 1, and still matched this table then I'd be set. Here's the updated link & picture! Please let me know if any of this is confusing and I will try to clarify!

 

 

https://drive.google.com/file/d/16ih...ew?usp=sharing

 

SalesReps2.JPG

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies