Avoiding the OFFSET function

By Kenny Chew on September 2 2009

Continuing from Nick’s recent post on the multi lingual translator, I have recently learnt that for some reason the German version of Excel does not translate the OFFSET function, which makes it difficult although not impossible to use. There are of course add-on packs and macros that can be written to replace this function but not everybody in every organisation will be able to use this approach. So the challenge was to find a way of avoiding the use of OFFSET when building models for a German audience.

Of course the easiest answer to that question would be to stay away from them completely. Ask a financial model auditor and they’ll tell you why! However, in many cases, OFFSET appears to be the only sensible solution. The classic example would be in depreciation where you have multiple additions throughout the depreciable life. For those in project finance, I am sure you would agree that once you know how to use it that OFFSET is the only solution.

Financial modelling of depreciation without using OFFSET.

I dug deep into my thoughts to solve this problem. Some say that this method is more transparent than the SUM(OFFSET) approach we would usually use, while some will disagree. Well, whichever side you are on, especially if your PC does not recognise ‘OFFSET’, for whatever reason, this would be one alternative that you can use.

1. Create an account called “Depreciation Base” with all the asset additions.
2. For each asset addition, have an end date for the addition (i.e if an asset starts in Jan 09 and has a 10 year useful life, the n you would expect that asset to be fully depreciated (expired) on Dec 18)
3. Use the SUMIF to identify amounts that are expired in each period
4. Remove assets from the “Depreciation Base” as they expire.
5. Calculate the SL depreciation on the opening balance of the “Depreciation Base”.

What has happened here is that the above account has replicated the SUM (OFFSET) function.

Another application

For those whose machine can understand OFFSET, you may be wondering if this has any other use apart from replicating the offset. I have recently also used a similar method for working capital where your debtor days/creditor days is greater than the period of the model. For example, if your model is monthly and you don’t pay your bills until after 2 months that they are invoiced, then this method can be used as an alternative. For those modellers who have time on their hands, do try this method. I’d be interested to know what you think or if you know of another way!

Comments