Transferring budget data to actuals or actuals to budget

[Wim Gielis]

Hello all,

I was just thinking about the following, maybe it's an interesting question for the Board.

With customers I see that most of the time, we have for the financial data either:

- a detailed actuals cube to which we transfer budget data coming from a less detailed budget cube (we put the data on dummy elements if the detail does not match)
- a less detailed budget cube to which we transfer actuals data coming from a more detailed actuals cube (we carry over totals if the detail does not match)

I do not see a clear pattern in the choices of customers. Is one choice better than the other, or does it depend on what you want to do in the model?
Other factors? Other things that come into play?

Have a nice Sunday,

Wim

[tomok]

For me it depends on what the client has hired me to do. If it is strictly a planning and forecasting model then I wouldn't build a separate cube to hold actuals. I just have an Actual element in the version dimension, along with budget, forecast_1, forecast_2, etc. so they can compare the two in the same view. If I have separate cubes then the analysis would have to be done in an active form or something like that. Not really optimal in my opinion. Since I know most companies don't want to plan down to the GL account level, I ususally recommend creating some "planning" accounts in the account hierarchy. They can either choose a single existing GL account in each rollup node to plan to or I create a dummy account in the rollup node to hold the plan, putting the actuals into the real GL account. All this depends on the client's preference since either way works fine.

If I am creating both a planning system and a reporting system I will often have a separate reporting cube and I will pipe over the budget numbers into the reporting cube. This is because sometimes there are different hierarchical needs in a reporting tool, as well as different data retention needs. I generally give the client their options, laying out the pros and cons of each approach, and let them make the decision. IMO, you get better buy-in when clients make their own informed decision.

[mattgoff]

I'm not a consultant, so my experience is limited to my own model, but we use a structure like tomok's. We have one GL cube with five types of versions:

1. Actuals. Pulled in from Oracle.
2. Preview. Nuance to our accounting system since we have a set of books for each country. It's similar to actuals, but instead of pulling from our Global Consolidated set of books, it pulls from all local sets of books and consolidates in TM1. It's an "unofficial" number we use before Global is consolidated in Oracle (usually not until day 3-4 of close).
3. Forecast. Actuals replace forecast, via a rule, as periods are closed. I use a rule here for convenience and to avoid cell-level security since we have separate month and year dimensions.
4. Budget. We actually budget in Forecast, but once we have a board-approved budget I copy just that one year into the Budget scenario.
5. Snapshots. These are copies of the Forecast scenario taken on the date forecasts are due. The plan was to only keep a year or so of these, but they're pretty small so we haven't purged any yet (five years worth).

We don't have special "forecasting" accounts or depts. We really only have 40-50 active I/S accounts (at least at an operating dept level), so it's not really necessary. Analysts are free to forecast at whatever level they want, and some do, for example, forecast all Travel under "Travel - Misc" instead of splitting up Airfare, Hotel, etc. Many also use an admin dept to hold bulk costs like education, supplies, T&E, instead of forecasting at a dept level.

I'd be interested to hear how others have their models set up.

Matt

[ajain86]

I have developed both types of models.

A detailed actuals cube can be set as read only as well, which can help when working with a large set of users working concurrently.
The planning cube would be set as write and only users that will be inputting would be given access to it. Data would be copied over to the actuals cube at specific time intervals.
This helped in preventing users from locking each other out.
Another plus for this model is that it allows you to use less and smaller dimensions for input as it is only focused on how the user would be inputting. Data is mapped accordingly in the copy process.

I prefer 1 cube if the user set is small as it is easier to maintain.