D-Cube Depreciation Calculation

[malley]

Hello,

Has anyone in this forum had to implement a MACRS depreciation schedule in Cognos 8 Planning - Analyst? The basic cube I'm able to build follows:

	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7
Year 1 Capital Expenditure
Year 2 Capital Expenditure
Year 3 Capital Expenditure
Year 4 Capital Expenditure
Year 5 Capital Expenditure
Year 6 Capital Expenditure
Year 7 Capital Expenditure
Year 1 Depreciation
Year 2 Depreciation
Year 3 Depreciation
Year 4 Depreciation
Year 5 Depreciation
Year 6 Depreciation
Year 7 Depreciation

Note that 'Year 1, Year 2.....' d-list is my timescale d-list and 'Year 1 Capital Expenditure, Year 2 Capital Expenditure....' is my calculating d-list.

At the end of the day, both my d-lists will have 100-200 items. I need to be able to calculate depreciation by year. So, for example, my depreciation for year 3 should equal the sum of 'Year 1 Depreciation / Year 3', 'Year 2 Depreciation / Year 2' and 'Year 3 Depreciation / Year 1'. Make sense? Any ideas on how to implement?

Thanks in advance! Matthew

[arun08]

I am just wonderinh whether you have tried all the available BIF's? Did you try Movingsum or Timesum?

Arun

[mrobby]

I saw your post in the Cognos Forums as well.  Did you want to actually calculate the depreciation? or were you ok with using a MACRS table and multiplying the %s listed in the MACRS table by the Capex spend?

[malley]

Sorry, I started working on another portion of the model and am not just coming back to this problem. Unfortunately, I cannot use the MACRs table in this calculation. The solution I'm searching for actually has nothing to do with depreciation, but an implementation of the MACRs depreciation calculation would essentially give me insight into a solution for my problem.

I'm an analyst trying to model oil & gas production from oil & gas reserves. I have a series of capital expenditures over time (this will be the "original" timescale), and I calculate a  "reserves" value for each capital expenditure. On another timescale (this will be the "production" timescale), I need to multiple that reserves value by a decline rate to get my production by month (i.e. 1000 reserves * 5% decline rate in month 1 = 50 units produced in month 1). Each period has a different decline rate.

At the end of the day, I want to add all of the units produced for each month in the original timescale. So, for example, my result for third month in my original timescale should be the sum of:
production for month 3 (production timescale) of the capital expenditure for month 1 (orig. timescale)
production for month 2 (production timescale) of the capital expenditure for month 2 (orig. timescale)
production for month 1 (production timescale) of the capital expenditure for month 3 (orig. timescale)

Any ideas on how to implement in Cognos?

Thanks in advance! Matthew

[overflow.au]

Hi Malley,

I would suggest that you try the delay BiF; this will allow you to specify proportional uptake of the input over ensuing time periods.  Although the wizard only allows a few inputs (five from memory); this can be manually expanded to a much greater amount (have seen 90).