Managing Sales Quota

[cardantim]

Hello,

I have a model for sales revenue where Sales Exec, Region, Products, Accounts, Version and Time are the DIMS with revenue, qty sold, margin are measures. I want to measure this activity against a pre-determined quota (ie, $1M) and wondering if anyone has approached this before. The quota is by sales executive over time (Annual and Quarterly). I don't think the sales excutive's quota would be broken dowwn by product, account, etc. I'm thinking that I would have a "quota" cube separate from my sales planning cube. Quota cube would have Time and Sales Exec as DIMS. Measure here would be Revenue. Not sure about including Version here as I see Quota as a static value. I could compare quota to sales (cube to cube) and enable Drill from quota cube to sales cube.

Thanks in advance for any feedback.

Tony

[Alan Kirk]

Hello,

I have a model for sales revenue where Sales Exec, Region, Products, Accounts, Version and Time are the DIMS with revenue, qty sold, margin are measures. I want to measure this activity against a pre-determined quota (ie, $1M) and wondering if anyone has approached this before. The quota is by sales executive over time (Annual and Quarterly). I don't think the sales excutive's quota would be broken dowwn by product, account, etc. I'm thinking that I would have a "quota" cube separate from my sales planning cube. Quota cube would have Time and Sales Exec as DIMS. Measure here would be Revenue. Not sure about including Version here as I see Quota as a static value. I could compare quota to sales (cube to cube) and enable Drill from quota cube to sales cube.

I think you're overcomplicating things. Really this sounds like just a plain ol' Actuals Vs Budget comparison. You may need to add one dimension to the cube which will hold the elements Actuals and Budget (or in your case Quota). (This depends on what your idea of a "version" is; where I am the version would be Actuals, Budget etc whereas some other places use the term "version" for what we call a "scenario". Obviously the Version dimension is the first place that you'd look to store your quota if you have that option.) It doesn't matter that the quotas are less granular than the actual number; you just assign the Quota value to some representative product, account and region (as well as the last period of the quarter) so that when you do the comparison at a Totals level (which is all you seem to want to be able to do) you'll be able to get comparison numbers.

[cardantim]

I've found that since I've been using TM1 I have a tendency to over complicate things. I do have a version DIM and it holds Budget, Actuals, Forecast. I could simply add Quota and give it a whirl. But, in getting to my sales budget I have units sold * price by product, sales exec, account, region. Quota shouldn't be that granular. At least I don't think it should be.

Thanks

[tomok]

But, in getting to my sales budget I have units sold * price by product, sales exec, account, region. Quota shouldn't be that granular. At least I don't think it should be.Thanks

It doesn't have to be. For starters you can STET out the rule calculation for the Quota so that you can just load the quota values themselves into the cube. Then you can add an N level element called "No x" to any of the dimensions where you don't want to load quota detail. For example, No region, No product, etc., and you load the quota to those. Just make sure these "No x" elements roll up to the total in these dims.

[lotsaram]

While I agree with Alan and Tomok that while you don't have to have a separate cube to store a differently dimensioned budget or target, or "quota" in your case, it often makes sense to store such data in an appropriately dimensioned cube. This is even more the case when end users may have responsibility for entering such data or have a need to report separately on it outside the context of the more detailed actuals data as users can find it quite confusing to need to remember which arbitrary element data is loaded to in the dimensions not applicable for the quota/target and how to navigate to it.

I would still go for the option of using either rules or TI to bring the data in to a more detailed cube for comparative reporting (and loading against arbitrary or proxy elements as there isn't any alternative) but I wouldn't dismiss the idea of a separate cube as often there is quite a valid case for having one.