I have a cube that contains revenue and expenses related to the month in which the revenue or expense occurred. These revenue and expenses are attributed to widgets that, once purchased, generate revenue until they are disposed of.
To determine return on investment one must have the cost of the widget --- the investment part of the equation.
The revenue period has nothing to do with the widget cost since the widget could have been purchased years ago and still be generating revenue. So how do I include the cost in my cube with regard to the revenue period? I have it in there now connected to the latest period but that won't work for any analysis that isn't looking at the current period.
I thought before experimenting blindly I'd see if anyone had thoughts on a good way to go about it. Thanks!
Lynn -
Won't this depend on the management as to how they want to calculate their ROI? There are different ways to do this, some companies use the original cost, some use a depreciated cost (not zero even if becomes so) and some use a certain mimimal cost after a full depreciation.
They were using book value (cost less depreciation) but are now moving toward the original cost as the basis for calculating ROI. Either way, the timing of those metrics is unrelated to revenue period. In theory book value changes monthly but the latest book value would still be the figure to use (my operational -ugh - source has no history so the latest figure is what everyone would go by regardless of the revenue period in question).
If I associate the cost to the latest time period I could get the ROI for any period that includes the latest, but what if I want to look at last year? Cost gets filtered out in that scenario. I suppose I could just compute the ROI in my source and bring it in that way but I thought others might have run into a similar situation so figured I'd ask and see what turns up.