Monthly Archives: April 2013

Data Warehouse and allocated cost data

In a previous post, I showed that costs data may be allocated to other departments or levels of granularity to enable cost analysis.

The allocated cost data are artificial data: the data are only a part of costs for which the data are collected at other levels of the organisation, These data are then intermediate data that are halfway between original data that are collected in the business process and the final output data that are used in the final profit and loss analysis.

In my opinion, such intermediate data should not be included in the data warehouse as they are artificially created data. If we would store such data in the data warehouse, such data could be misinterpreted as real input data. Someone from another department may use these data as being equivalent to real input data thus leading to unnecessary confusion. Anyone who does so, cannot be blamed. The data warehouse is a central data store that may be used by everyone for reporting purposes. Once included in the data warehouse, one may assume that the data represent a well analysed view upon real business events.

Therefore, to maintain a good data quality, the data warehouse is not a good environment to store such intermediate allocated data.

It is also true that the business rules to create the allocated costs can be quite complex. The owner of these business rules is the accounting department (and not the IT department). If the IT department is lured into trap to calculate such intermediate data, it is exposed at an immensely complicated situation as it must rely on specifications from the accounting department while at the same time it may be held responsible for the quality of such data. We then have a reason not to agree to calculate such allocations within the data warehouse: it is simply too complicated and the IT department does not have the knowledge to do so.

An IT department is good at three things: retrieving data, storing data and propagating data. It is not good at programming complicated business rules that may be not fully known by the owners. Hence, the IT department should not attempt to do so.


Cost Allocation

In most organisations, cost data are collected on a different level of granularity or attached to another dimension as we would like to see the cost data.

Two examples.

Cost data, such as advertisements or publicity may be collected at product group level whereas we would like to present the cost data on individual products. This can be understood as advertisements are made for, say, chocolate whereas we would like to see the cost data that are attached to an individual bar. This would allow to compare the sales price to the total cost price of a chocolate bar to see if it is sold at a profitable price or not.

Cost data may also be acquired with cost centres (such as the HR department) whereas we would like to know how much costs is made with the sales data that are collected with the profit centres. In that case we would like to infer if the profit centre sells their products at a price that covers all direct and indirect costs.

We are confronted with the question on how to spread costs to individual products. Or alike: how to divide the costs on, say, HR to sales departments.

I have read on three different methods how to do this. The first method is almost trivial: do not do it. Do not spread the costs, do not allocate the costs. Just look at total turnover, direct costs and total indirect costs and see if profits are made.

The second method is more adequate as then a factor is taken that is used to divide the costs. One may think of sales turnover. For each product and every profit department, one may calculate the percentage turnover in total turnover. The resulting set of factors can then be used in the division process. If a chocolate bar takes 2% of total chocolate sales, we may account 2% of advertisement costs on chocolate as being attributed to indirect advertisements costs for chocolate bars. Likewise: if department A sells 10% of total sales, we take 10% of HR costs as being attributable to department A.

The third method assumes that we have different allocation factors for different cost groups. Hence advertisements may be allocated on base of turn over, HR on base of the number of employees, maintenance on capex outlays etc. The idea is that such allocation is closely related to the actual driver for which costs are incurred. This would then be more acceptable for the managers of a profitcentres who would like to see a close relation between his activities and the internal costs for which he is held attributable. It is not a pleasant idea to receive a large internal costs invoice for warehouse activities if the department does not have activities for which a warehouse is necessary.

A good method for allocation can be immensely important for an organisation as allocation methods have behavioural effects within the organisation. A department may try to avoid attributed indirect costs to improve departmental profit figures. I once worked in an organisation where It costs were allocated according to the number of working network sockets. The idea is good as it may be assumed the IT costs have a relation to the number of network connections. However the negative consequence was that network access was denied to people who needed it for their work to avoid IT costs.

Hence the important point is that cost allocation is important but one should create such a method to avoid unwanted behaviour. This is certainly not a trivial task as it may attribute how activities are assessed. The consequence is that the allocation mechanisme might become quite complex as multiple goals are aimed for: a fair representation of the cost structure without unwanted behavioural consequences.