201

201 Intermediate model building - Section 12 - Activity #17

Region - PARENT(PARENT(PARENT(ITEM('Reps L4'))))

Role - Rep Setup.Role

Goal - IF Active? THEN Role Goals.Goal Quota[LOOKUP: Region, LOOKUP: Role] / 12 ELSE 0

Active? - IF ISBLANK(Rep Setup.Start Date) THEN FALSE ELSE IF Rep Setup.Start Date > END() THEN FALSE       ELSE IF ISNOTBLANK(Rep Setup.End Date) AND Rep Setup.End Date < START() THEN FALSE ELSE TRUE

Headcount - IF Quota > 0 THEN 1 ELSE 0

Tenure - IF NOT Active? THEN 0 ELSE IF YEAR(START()) - YEAR(Rep Setup.Start Date) = 0 THEN MONTH(START()) - MONTH(Rep Setup.Start Date) + 1 ELSE 12 - MONTH(Rep Setup.Start Date) + (YEAR(START()) - YEAR(Rep Setup.Start Date)) * 12 - (12 - MONTH(START())) + 1

Text Tenure - TEXT(Tenure)

Ramp Period - IF Active? THEN IF Tenure > 18 THEN Ramping Periods.'18' ELSE FINDITEM(Ramping Periods, Text Tenure) ELSE BLANK

Ramp - Ramping Profile.Ramp Percent[LOOKUP: Ramp Period, LOOKUP: Scenario Selection.Ramp]

Ramp Status - Headcount * Ramp

Quota - Goal * Ramp

Above are the formulas I used for Rep Quota modules. However, during activity check I can see zero as entry for all Goal, Headcount, Ramp, Ramp Status and Quota. Can someone help me with formulas for Goal and Headcount?

I have attached screenshot too.

For some reason I could not start a new topic, I replied to your comment.