Mirrored categories in single matrix

3.19K viewsGeneral Discussion
0

Hi,

Is it possible to have a category ‘mirrored’ within one matrix so that it appears twice (or alternatively, can two categories within one matrix be linked so that they have the same items).

eg.

….A1 A2 A3 [A]
A1
A2
A3

[A_copy]

I can link categories between matrixes with ctrl-drag (but not within the same matrix). I can also copy a category, but it doesn’t link the old category, so that if I add items to the old category they don’t also add to the new.

Thanks,

Andrew.

0

Hello Andrew – thank you for posting.

This is a request we have received before and I have added your vote in our database for this feature. I am curious if you have a specific use case you can share on the forum…?

-Mike

0

Mike,

The context is trying to calculate straight line depreciation for an asset class that has new assets added each year (and potentially removed as they reach their useful life).

I can do this in a single line formula using Quantrix (based on the application note on depreciation):

Year[THIS]:Real depreciation = Year[PREV]:Real depreciation +if(Asset classes::Standard life > 0,Year[PREV]:Real capex / Asset classes::Standard life,0) – if(Asset classes::Standard life > 0, Indirect(“Year[PREV-“&Asset classes::Standard life&”]:Real capex”)/Asset classes::Standard life,0) skip ‘1’,Total

But that approach isn’t exactly satisfactory. Using indirect() makes the formula unreadable, and a whole lot harder to verify. It needs to be even more complex to handle non-integer asset lives. So you lose some of the key benefits of Quantrix.

A common idiom used in excel is a triangle matrix, where the investment schedule is transposed so that it runs down the page, then depreciation is calculated separately for each year’s new assets. Although the result is the same as the indirect style formula above, this method has the advantage of being more readily verifiable, and more common. To do this though, you really need a matrix that is Year x Year. Although even then the formula would be quite complex I think.

Having said all that, depreciation like this is always a bit tricky, and I am open to suggestions for how else you might do this.

Andrew.

0

Thank you for the replay and use case Andrew. I have added your comments in our database for this improvment request.

I don’t have anything additional to add on how to approach depreciation – although I understand what you are trying to do.

Do any other users have a different approach to depreciation using Quantrix?

-Mike

0

I’ve just tried to tackle depreciation myself. The example in Quantrix’s IntegratedFinancials.model isn’t very flexible, as the depreciation periods for each asset class are hard-coded in the formulae on the Capex Worksheet.

It would be much more useful to have a fully parameterised model, where the depreciation periods are explicit assumptions. This would also allow for different accounting standards to be used, by treating them as different scenarios, as requested by LloydL in Oct 2006 in the “Quantrix Feature and Improvement Requests” thread.

The only I can see that stops the IntegratedFinancials approach being generalised is that it does not seem possible to set up recurrence relations with anything other than a hard-coded offset. (You can do [THIS-3], but not [THIS-Depreciation Periods:::], or even [THIS-(1+1)].)

You therefore have to work around this using indirect, as Andrew showed. It would be very helpful if a future release could allow calculated offsets in recurrence relations.

Cheers, Paul.

I don’t think you need to have a Year x Year triangular matrix to do this, though.