A few more last-minute bookings meant additional tables were required for the third roadshow in a row. The questions and contributions were up to the usual high standard. One question that stood out concerned automating the inclusion of year-to-date figures in a summary management accounts page.

In recognition of the several 'should we be using Excel for this at all' discussions that have featured on IT Counts, Excel should perhaps be seen more as a last resort than a first choice for management accounts. As soon as you extract the data from the control of an accounting system and start manipulating it in Excel's very flexible, but unstructured, environment so the chances of error increase. The price to pay for the flexibility is the need to build in adequate checks and controls. That said, many accounting systems don't make it easy, or even possible, to construct reports in a way that people may wish to see them, and Excel fills the gap.

In this particular case, separate sheets contained monthly actual and monthly budget figures. A summary page showed the actual/budget comparison for the current month and the year-do-date equivalent. As far as the monthly figures are concerned, Excel's OFFSET() function could be used to select a particular month just by entering the month number into a chosen cell. The simpler form of the OFFSET() function takes three arguments: the 'base' cell reference; a number of rows and a number of columns. The numbers of rows and columns define the position of the 'result' cell:

In this example we start at B2 and then move 3 columns to the right so that the OFFSET() function returns the value in cell E4. The column number is calculated by taking the number entered in cell B15 and subtracting 1. To get to month 4, you need to start at month one then move three 'months' to the right (4-1). We stay on the same row, hence the 0 row number:

 

OFFSET() is definitely one of the Excel functions that needs to be used with care. Some of the issues were raised in an AccountingWeb post on the use of OFFSET() . An obvious issue in this case would be the indiscriminate insertion of columns which would leave the formula pointing at completely the wrong place.

To return to the original question, let's see how we might extend this idea to coping with year-to-date totals. We could construct a SUM() formula where the first reference in the range is to B2, but where the second reference uses OFFSET() to 'pick' a month using the value entered in B15 again:

=SUM(B2:OFFSET(B2,0,$B$15-1))

Depending on the circumstances, a safer alternative may be to use a combination of INDEX() and MATCH() – a suggestion that was made with regard to an earlier post on the VLOOKUP() function. I'll look at this soon, including the use of the Lookup Wizard and Data Validation.

My non-IT bit for this post follows on from the Leeds beer lament. As I was sitting by the bar in the hotel in Lancaster, carefully entering the different beer sales into my Excel spreadsheet (no, not really), the one 'proper' beer available outsold all the other types put together by a factor of about 5 to 1.