Last year for budget I used a separate cube for inputting sales information - loads, kilometres, revenue, etc. (we’re in the transportation biz).
This year I was thinking it would be more efficient to use my regular sales reporting cube. The only problem is that the users forecast at a higher level - i.e., the sales come in at CustomerName-City1, CustomerName-City2, CustomerName-City3. But we only budget at the Customer level. So last year my cube was at the consolidated level, it was easy to build a template that did that. I’m not sure how to build the template that has historical data at the consolidated level, but allows you to input a budget (i.e., because you couldn’t load the budget to the consolidated level).
We have a DPM revenue cube that takes factors like rate * units * utilization percentage by client. This cube then feeds into our Finance cube for the budget at a consolidated level. Sounds like this is kind of similar to what you need! Feel free to contact me if you want me to walk you through our process.
1 Like
In cases like this I consider using placeholders in the dimensions that don’t get budgeted at the same level of detail. In your customer dimension, If ABC has children 1 2 3, I’d have a hierarchy with ABC having child nodes of 1 2 3 and “Aggregate” (or something). Then I’d forecast at the aggregate lead node and do my budget to actuals comparison at the parent level.
Whether this is preferable to a separate cube depends upon other factors, such as how many higher level budget dimensions you have to put in place. but if it’s a single dimension, or maybe a couple, I find this approach to be not only workable but intuitive for the users.
Hi Michael,
This is one of those problems that are wonderful to have with Prophix. So many different ways to do it.
I’ve done it in the ways that others have suggested (and not only revenue, but other below-the-GL budgeting exercises). What significantly drives the amount of detail I bring into Prophix is the reporting that I want to do (or the analysis the CEO is going to ask for when actual significantly deviates from plan). If you are never going to do a budget-to-actual by Customer Level, then don’t bring in the data at the City Level. Aggregate it before you import it. But, if you do want to do some analysis within Prophix on the City Level, then consider bringing it.
On a side note, you may want to create two dimensions for this - one for Customer and another for City. Makes it easier to roll-up by City (should that be of interest). If following Bob’s suggestion, you could have a generic “aggregate” City member for the non-allocated budgeting exercise.
2 Likes