T
Tan
Hi all, i needs help urgently to finish up my company report but encounter
the following problems:
I have a revenue file containing a list of thousand customer names and their
respective revenues for each of the 4 quarters in Year 2008 and Year 2009.
That is Q108, Q208, Q308, Q408 in Year 2008 and Q109, Q209, Q309 ,Q409 in
Year 2009. But in each Quarter, i also hv columns to breakdown by months
follow by the Quarter TOTAL, that is JAN, FEB, MAR, Q108 TOTAL, APR, MAY,
JUN, Q208 TOTAL....etc..Thus, the quarter total columns are not side by side.
Below is an extracted sample of my revenue file with each quarter TOTAL
revenue$ (quarter TOTAL column are not side by side) and customer name:
Cust Nm Seg Cov Q108 Q208 Q308 Q408 Q109 Q209 Q309
ABC Ltd 1000 2000 3000 4000 2000 2000 7000
XYZ Ltd 2000 4000 6000 8000 0 0 5000
JKL Ltd 1000 1000 3000 5000 7000 4000 2000
Now, i have inserted two columns after Cust Nm, which is Seg (short for
Segmentation) and Cov (short for Coverage type by sales reps).
For Segmentation, i needs to segment each customer into A or B or C or D and
put its segments into Seg column.
To be in segment A, two conditions:
(1)TOTAL revenue for Q408 + Q109+ Q209 + Q309 must be equal or above 10K
(2)Y-on-Y growth comparison on TOTAL revenue for Q108+Q208+Q308 versus
Q109+Q209+Q309 must be positive.
To be in segment B, two conditions:
(1)TOTAL revenue for Q408 + Q109+ Q209 + Q309 must be equal or above 10K
(2)Y-on-Y growth comparison on TOTAL revenue for Q108+Q208+Q308 versus
Q109+Q209+Q309 is negative.
To be in segment C, two conditions:
(1)TOTAL revenue for Q408 + Q109+ Q209 + Q309 is below 10K
(2)Y-on-Y growth comparison on TOTAL revenue for Q108+Q208+Q308 versus
Q109+Q209+Q309 must be positive.
To be in segment D, two conditions:
(1)TOTAL revenue for Q408 + Q109+ Q209 + Q309 is below 10K
(2)Y-on-Y growth comparison on TOTAL revenue for Q108+Q208+Q308 versus
Q109+Q209+Q309 is negative.
Next on Coverage type by sales reps, i needs to assign a coverage type for
each customer, that is FTF (short for face2face) or PT (short for Proactive
Tele) or RT (short for Reactive Tele) into Cov column. Segments mentioned
below are derived from above segmentation exercise
To be in FTF, 2 conditions:
(1) All segment A must be of FTF type coverage by sales reps
(2) Only segment B with "High Frequency of sales" will also be FTF type.
"High Frequency of sales" is defined as having Revenue $ in at least 3 out of
the last 4 quarters. That is, any of the 3 quarters having revenue$ from Q408
to Q309 (Q408, Q109, Q209 and Q309).
To be in PT (Proactive Tele), 2 conditions:
(1) All segment C must be of PT type coverage by sales reps
(2) Only segment B with "Low Frequency of sales" will also be PT type. "Low
Frequency of sales" is defined as NOT having Revenue $ in at least 3 out of
the last 4 quarters. That is, only 1 or 2 quarters having revenue$ from Q408
to Q309 (Q408, Q109, Q209 and Q309).
To be in RT (Reactive Tele), 1 condition:
(1) All segment D must be of RT type of coverage by reps.
Thanks!
the following problems:
I have a revenue file containing a list of thousand customer names and their
respective revenues for each of the 4 quarters in Year 2008 and Year 2009.
That is Q108, Q208, Q308, Q408 in Year 2008 and Q109, Q209, Q309 ,Q409 in
Year 2009. But in each Quarter, i also hv columns to breakdown by months
follow by the Quarter TOTAL, that is JAN, FEB, MAR, Q108 TOTAL, APR, MAY,
JUN, Q208 TOTAL....etc..Thus, the quarter total columns are not side by side.
Below is an extracted sample of my revenue file with each quarter TOTAL
revenue$ (quarter TOTAL column are not side by side) and customer name:
Cust Nm Seg Cov Q108 Q208 Q308 Q408 Q109 Q209 Q309
ABC Ltd 1000 2000 3000 4000 2000 2000 7000
XYZ Ltd 2000 4000 6000 8000 0 0 5000
JKL Ltd 1000 1000 3000 5000 7000 4000 2000
Now, i have inserted two columns after Cust Nm, which is Seg (short for
Segmentation) and Cov (short for Coverage type by sales reps).
For Segmentation, i needs to segment each customer into A or B or C or D and
put its segments into Seg column.
To be in segment A, two conditions:
(1)TOTAL revenue for Q408 + Q109+ Q209 + Q309 must be equal or above 10K
(2)Y-on-Y growth comparison on TOTAL revenue for Q108+Q208+Q308 versus
Q109+Q209+Q309 must be positive.
To be in segment B, two conditions:
(1)TOTAL revenue for Q408 + Q109+ Q209 + Q309 must be equal or above 10K
(2)Y-on-Y growth comparison on TOTAL revenue for Q108+Q208+Q308 versus
Q109+Q209+Q309 is negative.
To be in segment C, two conditions:
(1)TOTAL revenue for Q408 + Q109+ Q209 + Q309 is below 10K
(2)Y-on-Y growth comparison on TOTAL revenue for Q108+Q208+Q308 versus
Q109+Q209+Q309 must be positive.
To be in segment D, two conditions:
(1)TOTAL revenue for Q408 + Q109+ Q209 + Q309 is below 10K
(2)Y-on-Y growth comparison on TOTAL revenue for Q108+Q208+Q308 versus
Q109+Q209+Q309 is negative.
Next on Coverage type by sales reps, i needs to assign a coverage type for
each customer, that is FTF (short for face2face) or PT (short for Proactive
Tele) or RT (short for Reactive Tele) into Cov column. Segments mentioned
below are derived from above segmentation exercise
To be in FTF, 2 conditions:
(1) All segment A must be of FTF type coverage by sales reps
(2) Only segment B with "High Frequency of sales" will also be FTF type.
"High Frequency of sales" is defined as having Revenue $ in at least 3 out of
the last 4 quarters. That is, any of the 3 quarters having revenue$ from Q408
to Q309 (Q408, Q109, Q209 and Q309).
To be in PT (Proactive Tele), 2 conditions:
(1) All segment C must be of PT type coverage by sales reps
(2) Only segment B with "Low Frequency of sales" will also be PT type. "Low
Frequency of sales" is defined as NOT having Revenue $ in at least 3 out of
the last 4 quarters. That is, only 1 or 2 quarters having revenue$ from Q408
to Q309 (Q408, Q109, Q209 and Q309).
To be in RT (Reactive Tele), 1 condition:
(1) All segment D must be of RT type of coverage by reps.
Thanks!