A clever formula req for lead time revenue calc

[stavs]

Hi All

I'm Really struggling with a formula. I wondered if any of you coul
put me on the right track. Its not a big complicated one - its jus
that I can't see how to attack it.

I want a worksheet to calculate revenue generated by a team o
telesales people.

The worksheet is here;

'telesales-rev.xls
(http://www.orlando-hotels-service.com/telesales-rev.xls)

I want to be able to change the number of telesales people each mont
(in blue text) and the worksheet will calculate the figures for tota
revenue (red question marks).

I know how much one telesales person can generate as you can see on th
chart (row 10).

I added the grey rows to show how to calculate the total revenue th
'long way' - its just to demonstrate how the revenue stream applie
when each new telesales persons start.

I posted this a while ago on another forum and no joy - but a fe
questions were asked - I answered them as follows;

So why does the revenue from 1 sales person (row 10) increase ever

month?

As time goes by the telesales person has made more phone calls an
therefore the revenue increases over time. The product is not one tha
clients would want to buy on the spot - they would buy as and when
need arises. So as more and more people are exposed to the product th
revenue starts increasing (a snowball effect), although it does pea
and stabilise eventually. (hope that answers that one).

why not add a line saying monthly total revenue (say line six) wit

the formula: =B10*B3 (copied across)

The reason this won't work is due to the lead time. When new telesale
people join the team, they bring in zero revenue for 4 months becaus
of the lead time - but meanwhile the existing telesales people ar
earning revenue, the amount of which depending upon when they started
Hope that clarifies the complexity.

Can anyone lend a hand with this?

Many thanks in advance

 

[Biff]

Hi stavs!

I looked at your ws, how do differentiate the salespeople
that just started from those that haven't?

All you have is an entry that indicates the total number
of salespeople. Say that figure is 5. How do know if 3
are "new" and 2 are not?

I think you need to define a way to to seperate "new
starts" from "veterans". ie: list the salespeople along
with start dates. Then it should be a piece of cake!

Biff

 

[stavs]

HI Biff

Thanks for your interest!

Good question. For this worksheet, we are assuming that no telesale
persons leave. So you know when a new person has started by looking a
the amount of persons working in the previous month.

So if the number of telesales persons for October is 1

and,

the number of telesales persons for November is 4, then we assume ther
are 3 new starters in November.

Hope that helps.

Thank

 

[Guest]

Hi Stavs
I have reorganised the data to get the results you wanted. This is the way I have done this:

1. First, like you already have done, put the monthly revenue from each person in a list.
Lets assume it is in the row starting from B10 - £0.00,£0.00,£0.00,£0.00, £1,512.00, £3,200.40, £4,319.28... until Y10

2. Create a table with these columns (starting at B33)

Start Month No. added Revenue till date
1 1 130447.30
5 3 301771.01
8 1 77625.58
14 1 32959.58
15 1 26205.23
Total Revenue 569008.69

The start month column contains the month in with you added callers. So you added people in the 1st, 5th, 8th month etc.

No Added contains the no of people you added in that month. So u added 3 in the 5th month, 1 in the 8th month ass so on.

Revenue till date is the revenue you get from each set of people - the result you want.

Assume you have the last month in cell D31 (I have assumed it to be 24 for the above results).

In the first cell below "Revenue Till Date" column (assuming that is D33), put the formula
=SUM($B$10:OFFSET($B$10,0,$D$31-B33))*C33
where B33 is the first start month (1) and C33 is the first no added (1).
Drag this for all the rows.
Add these, and you have your results!

Ciao
Sukhjeet

 

[stavs]

Hi Sukhjeet

Thank you very much for your help on this - much appreciated.

I'm finding it very difficult to follow your instructions. Is i
possible that you could attach the xls file you worked on, so that
can see more clearly your solution.

Many thanks

Davo

 

[stavs]

Also I would like to be able to alter the number of telesales persons t
try different scenarios - will your solution allow me to do that

 

[Guest]

Hi Stavs
Could you please send me a mail at sukhjeet_at_excite_dot_com and I can send you the excel.
Dont have anywhere I can upload the file to.
Sukhjeet

 

[Guest]

yes, it will.
Sukhjeet