## Welcome to a new series of applied tips and tricks in Excel for accountants and financial analysts wherever you may be. As a professional modeller, FCA and Excel MVP **Liam Bastick** highlights some of the more useful functions for financial modelling/Excel spreadsheeting.

In the second part of a two-part discussion on OFFSET, Liam explains how OFFSET can assist with change your perspective about modelling depreciation.

### OFFSET reset

Last time I explained the **OFFSET** function:

**OFFSET(Reference,Rows,Columns,[Height],[Width])**

The arguments in square brackets (**Height** and **Width**) can be omitted from the formula – but they will prove to be useful in this article.

As I explained last time, **OFFSET(Reference,Rows,Columns)** will select a reference **Rows** rows down (**-Rows** would be **Rows** rows up) and **Columns** rows to the right (**-Columns** would be **Columns** rows to the left) of the **Reference**. For example, consider the following grid:

**OFFSET(A1,2,3)** would take us two rows down and three columns across to cell **D3**. Therefore, **OFFSET(A1,2,3)** = 16, *viz.*

**OFFSET(D4,-1,-2)** would take us one row up and two rows to the left to cell **B3**. Therefore, **OFFSET(D4,-1,-2)** = 14, *viz.*

For Part 2 of this discussion, let me extend the formula to **OFFSET(D4,-1,-2,-2,3)**. It would again take us to cell **B3** but then we would select a range based on the **Height** and **Width** parameters. The **Height** would be two rows going up the sheet, with row 14 as the base (*i.e.* rows 13 and 14), and the **Width** would be three columns going from left to right, with column **B** as the base (*i.e.* columns **B**, **C** and **D**).

Hence **OFFSET(D4,-1,-2,-2,3)** would select the range **B2:D3**, *viz.*

Note that **OFFSET(D4,-1,-2,-2,3)** = *#VALUE!* since Excel cannot display a matrix in one cell, but it does recognise it. However, if after typing in **OFFSET(D4,-1,-2,-2,3)** we press **CTRL + SHIFT + ENTER**, we turn the formula into an array formula: **{OFFSET(D4,-1,-2,-2,3)}** (do not type the braces in, they will appear automatically as part of the Excel syntax). This gives a value of 8, which is the value in the top left hand corner of the matrix, *but Excel is storing more than just that*. This can be seen as follows:

**SUM(OFFSET(D4,-1,-2,-2,3))**= 72 (*i.e.***SUM(B2:D3)**)**AVERAGE(OFFSET(D4,-1,-2,-2,3))**= 12 (*i.e.***AVERAGE(B2:D3)**).

I am going to use these ideas to build on the scenario table example I demonstrated last time. This time, I am going to consider a calculation many accountants have to perform regularly: depreciation of capital expenditure.

### Example

Let’s imagine I have been charged with creating a depreciation schedule in an Excel spreadsheet with the following assumptions:

You can follow this example in the __attached Excel file__. All cells in yellow are assumptions that may be changed.

I wish to build a depreciation grid to calculate my total amortised cost as follows:

My first challenge is to link the assumptions in cells **J21:O21** to the grey cells **H30:H35**, converting row assumptions into column data. Converting from rows to columns (or *vice versa*) is known as **transposing**. There are several ways you can do it, but only one way I truly recommend.

### Method 1: Cut and paste a formula

For the purposes of simplification, I am going to assume that we are looking to transpose data from going across a row to going down a column (the concepts are similar if columns are transposed into rows).

This method is very simple. First, create a formula that links to the data to be transposed, say, in the row beneath:

Second, once the formulae have all been created, cut and paste each formula individually into its appropriate position on the spreadsheet:

This is a very simple method, but certainly would be an ill-advised approach if you need to transpose 1,000 data points, for example.

However, simplicity is often a highly-underrated attribute in modelling. If you only have a few data items and you require the original inputs to remain (in our example, row 12, above), this method can often be deemed “simplest bestest”.

### Method 2: Copy and paste special, transpose

Another very simple approach is to copy and paste special as follows:

In this instance, simply highlight the data and copy the range in the usual way (*e.g.* **CTRL + C**). Next, simply select the first cell (*i.e.* the top left hand corner) of the intended range and Paste Special, Transpose (**ALT + E + S + E + ENTER**). As can be plainly seen from the illustration (above), the formatting as well as the content will be transposed.

This is an ideal approach for copying and transposing data from one source to another where links are not required. Aside from the inherited formatting, the main disadvantage here though is that depending upon the nature of the source data and how it is copied, updates in the original data will not flow through to the destination range.

If the data needs to be linked to the source, then this approach is probably inappropriate. However, if inherited formatting is all that is putting you off, make the appropriate adjustments to the ‘Paste Special’ dialog box before pressing ‘OK’ / **ENTER**, *e.g.* set the dialog box as follows to copy only the formulae before transposing:

### Method 3: Using the TRANSPOSE function

On first glance, this looks like the ‘best’ method. Once you discover there is a **TRANSPOSE** function, you think life is simple and your problems (well, your modelling problems anyway!) are over. Unfortunately, this function is not without its limitations.

Consider the following example:

Here, the intention is to transpose the values in cells **G12:K12** into the range **F16:F20**. To do this, simply highlight cells **F16:F20** and then type in the following formula:

**=TRANSPOSE(G12:K12)**

but rather than press **ENTER**, press **CTRL + SHIFT + ENTER** to enter the formula as an array formula.

This method is very simple, as long as you ensure that the destination range contains precisely the same number of cells in the column as there are cells in the source row. If you change the source data, the outputs will update accordingly too.

So, what’s the problem?

Array formulae can consume memory exponentially, which in turn can soon prevent Excel from calculating correctly, especially if you are working on a Windows 32-bit operating system, which is the platform most businesses employ.

Further, if you wanted to insert additional rows between rows 16 and 20 (*e.g.* to extend the range), you will find that this is not possible:

Only Chuck Norris can change part of an array, and unfortunately, he’s not available.

Interestingly, if you insert columns __between__ **G** and **K** in the illustration above, this is possible, but the array formulae will not act like other Excel calculations: cell references in the **TRANSPOSE** formulae will not update (so the references will always link to what is in cells **G12:K12** in our example). However, if you insert columns before column **G**, the formula will update. This can be confusing.

Therefore, **TRANSPOSE** is useful where you wish to protect the destination range’s structure, but it can be seen as inflexible and inefficient, particularly with larger Excel files, slowing calculation times down unnecessarily.

### Method 4: OFFSET approach

Used with the **ROWS** function (which simply counts the number of rows in a specified range), transpositions may be performed quickly and effectively using **OFFSET**:

In this example, the following formula has been typed into cell **F16**:

**=OFFSET($F$12,,ROWS($E$16:$E16))**

As the formula is copied down, the number increases in the **columns **argument of **OFFSET**, as the number of rows increases. This is a neat trick for transposing without using array formulae and can be seen as a good “general” solution, being quite flexible. The disadvantages here are twofold:

- Often modellers make mistakes in the absolute and semi-absolute references required to make this formula calculate correctly. This is easily overcome with practice;
*and* - The formula can look unnecessarily complex to the inexperienced or uninitiated. There are many end users (and modellers for that matter) unfamiliar with the
**OFFSET**function in particular. It may be worthwhile to educate them accordingly.

### Returning to the example

It is this last method I have employed in my depreciation example:

The formula in cell **H30** (highlighted) is

**=OFFSET(BC_Capex,,$E30)**

**BC_Capex** is cell **I21**. The prefix “**BC**” in this range name stands for “Base Cell” and is used to acknowledge the fact that **OFFSET** is non-auditable, as explained last time. This cell is styled speckled blue to emphasise that this cell must deliberately remain blank and not be deleted. I use this “Empty Cell” style regularly in modelling.

The reference **$E30** points to the label “Year 1” which is actually the number 1 formatted to look like text. This dispenses with the need to use the **ROWS **function explained earlier.

Now that I have successfully transposed the assumptions, I can calculate the depreciation grid:

Assuming depreciation is to be calculated on a straight-line basis (*i.e. *the capital expenditure is apportioned equally across all periods it will provide economic benefit), the depreciation may be calculated across the grid using the formula in cell **J30**:

**=IF(J$28>=$E30,MIN($H30-SUM($I30:I30),$H30*Depn_Rate),)**

The **IF **statement checks that depreciation does not commence before the capital expenditure has occurred. The **MIN** function takes a proportion of the capital expenditure but ensures that the cumulative total (“accumulated depreciation”) does not exceed the amount spent.

It is a simple method, recognised by many financial analysts and accountants. The main problem with this method concerns how many calculations are required in the grid. For example, if you built a 20-year monthly model, you would need 240 rows by 240 columns in the grid – a total of 57,600 calculations!

This is where **OFFSET **can come to the rescue…

In this example, row 44 contains the formula **=MIN(J42,Economic_Life)** which restricts the period counter to not exceed the **Economic_Life** (here, four years). With row 48 calculating each period’s representative depreciation, the total depreciation charge in row 50 becomes trivial. For example, the formula in cell **J50 **is:

**=SUM(OFFSET(J$48,,,,-J44))**

This formula simply refers to the corresponding periodic charge in row 48 and then sums a range of cells of width the negative value given in row 44. This means that:

- In Period 1, depreciation is simply given by cell
**J48** - In Period 2, the depreciation is the sum of the first two charges in row 48 (namely, cells
**J48:K48**) - In Period 3, the depreciation is the sum of the first three charges in row 48 (namely, cells
**J48:L48**) - In Period 4, the depreciation is the sum of the first four charges in row 48 (namely, cells
**J48:M48**) - In Period 5, the depreciation is the sum of the last four charges in row 48 (namely, cells
**K48:N48**).

Note that the first period is now excluded (the width is -4) as the first period’s capital expenditure is now fully depreciated.

A simple comparison of the two approaches to depreciation will determine that the two methods give the same result. The **OFFSET** approach may not be as transparent upon first glance, but it does reduce the number of formulae required.

When I model depreciation in reality, I usually model the first asset class __both__ ways so that end users may see they give the same result. After that, I adopt the **OFFSET** approach exclusively as end users now “trust” this less familiar approach.

I recommend if you are not familiar with **OFFSET** you should do the same. Play with the function for a while until you trust it and realise how useful it can be in your spreadsheets going forward.

### You might also be interested in

Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...

### Replies (3)

### Please login or register to join the discussion.

Under the "Example" sub heading, there is mention of an attached file. Does anyone know how to access this?

Thanks.

There is no attachment. Please provide the link.