Saturday, December 6, 2008

Excel Offset function

One thing I've noticed with beginning (and some advanced) financial analysts is that they either haven't been taught how to use the Offset formula or they're not clear what its usefulness is when creating models in Excel.

I use it all the time to create schedules that are dynamic and allows you to work with ranges based on some criteria.

The most common use for offset to me is to provide a way to get the annual total of some monthly range without hardcoding the range itself (ie. not using =sum(a1:a12)).

To create an annual schedule from a monthly schedule, in a new sheet, I first create a row that has the year in the columns B:xx (assume in row 1), where xx is the number of years. They should be numerical (ie. 1, 2, 3, etc.). Rather than type "Year 1", enter 1 into the cell and set the format to be custom. In the cell custom format, use the format: "Year" 0 (include the quote and notice the space between the close quote and the zero).

Then I set the row labels to point to the corresponding label in the monthly schedule (=MonthlySchedule!A2) starting in cell A2 of the annual sheet (It doesn't have to start in the same row, but you'll want to make sure each row label is relatively located in the same place as the first row label. You'll see why later).

Under year 1, for the first row of data, I place the formula as such:
=sum(offset(MonthlySchedule!$B2,0,(B$1-1)*12, 1, 12))

B2 represents the first row's first month data in the monthly schedule. B1 is the annual sheet's year 1 heading value.

Now you can copy that formula to every cell under each year for each row labeled. As long as the label rows in the annual sheet are in the same relative positions as in the monthly sheet, the data will be correct. Now you've just created an annual report without having to manually set each 12 month range! If you add more months, just copy the last column more years over! If you add more rows of monthly data, just copy the rows in the annual (making sure it corresponds to the monthly row's relative position).

What the offset function lets you do is to not only move your starting position of a range, but also how many rows and columns worth of data to return. And because it returns a range of data, summing the returned array is possible.

In this case, I want to move over 12 cells for every year, minus 1 (since in year 1, we don't want to shift over at all). Then I want 1 row, 12 columns worth of cells returned in an array. That array is summed and the result is the 12 month summation starting in month 1.

Other useful ways for Offset include using a drop-down to select a year for display, determining how many months or cells you want some calculation done on, etc.

I've used Offset in my models not only to create my annual schedule, but to display the total value of a trailing x month worth of finances, given a selected month, using drop-downs and offset into my monthly financial schedule. I've also created reports that show the yield on investment for any given year selected without having to predefine the results (I calculate it based on a schedule by using offset to span the selected amount of periods).

Offset is a very powerful and useful function for creating dynamic models where static formulas are used.

No comments: