Dear data analysts,
many an author seems to have great trouble with plans. “The more planful humans act”, Friedrich Dürrenmatt writes in the interpretation of his “Physicians”, “the more drastically chance may hit them”. Or Erich Kästner, who warns grown-ups: “The more voluptuous the plans may blossom, the more complicated the deeds will turn out.” Does their scepticism result from the fact that they had no suitable tool to forge their plans?
You, dear data analysts, have at least the tool: DeltaMaster! From release 5.1.6 that has come out recently, a new planning option is available. With it, budget calculations, revenue plans, liquidity or product range forecasts, and other future contemplations smoothly fit into your Analytical Reporting. And if you allow us to sacrifice the written language to metaphorics for a moment: The planning option is, to be plain, “a hammer” – with which you forge your plans in future. And we’ll be pleased to show you the new functionalities personally.
Your Bissantz & Company team
Analyzing, reporting and planning are time and again quoted as essential duties in the field of corporate controlling. It is commonly known that Analytical Reporting is a primary domain of DeltaMaster. Particularly charming solutions come into being when DeltaMaster is put into action also for planning and prognosis in the sense of a closed loop process: A whole bundle of tools that were crafted specifically for these purposes is at your disposal. And certainly you can treat your budgeted figures in DeltaMaster in exactly the same way as all other measures and like a flash, for instance by further analyzing them, inserting them in pretty reports, combing them for remarkable context etc.
In release 5.1.6, we have extended the range of functionality considerably and defined a new Planning Option in which we unite the entirety of functions required for planning. Thus, the DeltaMaster Planning Option is not a separate module, but rather a group of features that allow you to plan data with DeltaMaster in an integrated manner. To use the functions of the Planning Option, a license of its own is required. We’ll be pleased to provide you with a free test license – please contact your consultants.
In the following, we’d like to give you an overview of the main areas included.
Data Input with Dependencies
A basic prerequisite for the planning process is the ability to enter values into the application. This is possible with DeltaMaster’s planning option in all user modes from Viewer up. Aside from absolute values, modification values may be entered directly, too, for instance an increase in revenues by 1,000,000, a cost cut of 3%, or a doubling of monthly customer visits. For details, please refer to the January 2006 issue of DeltaMaster clicks!. Processes like splashing (distributing aggregated values) and copying data are described there as well.
Something new is the possibility to enter values for calculated measures. Supposed a user-defined measure called “Revenues/sqft” has been defined as the ratio between revenues and floorspace. If you now enter a planned revenue per sqare foot, you’ll normally want to express something about the revenue trend and not have the floorspace incresed or decreased. You can determine the way in which changes are passed on to involved operands in the Measure Properties in the Data Input tab. In the simplest case, the modification will be forwarded proportionally. For more complex connections, the destination value may be calculated via MDX.
After the input, you update the display with the F9 key. So you can immediately see how your plans effect the target values.
Especially when several organisational units or employees are participating in the planning process, or when the budget is entered on multiple different levels, it may occur that certain values are “preset” or “capped”: Even if subordinate values or higher aggregations change, these values are supposed to remain constant. The tool needed here is called Cell Fixing. You switch it on or off via the context menu in the pivot table (or the F6 key). The way fixed cells are displayed complies with your settings in the Extras, Options, Data Input menu where you may determine font, size, colours and a symbol.
The following table shows a basic example how cell fixing works:
|Segment||Old Value||Fixing||Input||New Value|
|– North||120||30+||150||increased directly|
|– South||90||yes||90||constant, fixed|
|– East||60||40||2/3 of the change|
|– West||30||20||1/3 of the change|
The aggregated value for Total (300) is fixed; thus an increase for North by 30 to the new value of 150 must not affect the sum. So the additional 30 units from North need to be compensated by objects on the same level and in the same group. South is fixed as well, thus may not be modified either and keeps its value of 90. Consequently, only the two objects East and West remain to receive the added 30. They are decreased proportionally, so that after the modification, they remain in the same ratio to each other as before. – But fortunately these arithmetics are concealed from you, because DeltaMaster automatically takes care that all fixations remain valid. Despite the complex procedure, you just press the F9 key here, too, to receive an updated display with freshly recalculated figures.
To allow for fixed values, two additional conditions must be met apart from a license for the planning option:
- The fixing must be explicitly permitted for the desired mesure in the Data Input tab of the Measure Properties.
- In the Table Properties, also on the Data Input tab, a dimension must be selected to define the fixing axis.
In each saved cockpit, report and analysis result, there is a comment area in which you may enter your own annotations, explanations, warnings, etc. The planning option extends these possibilites and allows to assign comments even to individual data cells. A small red triangle in the top right cell corner serves as a hint on a comment, similarly to the representation in Excel. Cell comments are saved in the relational database and are thus accessible to all persons involved in the process.
A special feature is the fact that DeltaMaster is even able to aggregate comments if desired. If you activate the corresponding option (settings in the Extras, Options, General menu), DeltaMaster does not only display the texts saved in the aggregated cell itself, but also all comments in subordinate areas. While for creating and editing cell comments, the planning options has to be licensed, reading the comments is possible in all user modes without the additional license.
Budgeting with OLAP Databases
Presently, the planning option can be used in connection with Microsoft SQL Server Analysis Services, MIS Alea, and Applix TM1. Depending on the database, your entered budget values are stored in so-called writeback tables (Analysis Services), or directly in the cube (Alea, TM1). All users working on this data immediately see the effects of modifications in the front end, i.e. in the DeltaMaster analysis session.
Most frequently, the data models will be partly adjusted during the implementation of a planning solution. An auxiliary dimension, like the Value Type shown opposite, allows for manifold scenarios in which multiple budget values may be managed in parallel, and flexible change calculations may be performed. The green elements are calculated by DeltaMaster itself; only the individual budget versions are recommended to be created as elements in the OLAP model so that values may be written to them.
We’ll be pleased to assist you in building your budgeting applications!