Bagi menambah baik laman blog ini, saya memerlukan maklum balas dari para pembaca:
Atau apa jua yang anda ingin kongsikan…
This month we examine the use of OFFSET for scenario analysis and other calculations. A spreadsheet chart is attached to the bottom of this page for you to practice your new skills. By Liam Bastick, associate director with BPM Analytical Empowerment.
I am aware of Excel’s Scenario Manager, but I’m looking for something more transparent. I want to do some scenario analysis in my financial model, where some key assumptions can be changed. I need to keep the alternative scenarios and I may need to add to them. Any suggestions?
Last month, we talked about INDEX and MATCH, functions concerned with position. This month, we look at OFFSET, a function which considers displacement. OFFSET is a function well suited to this scenario (if you will excuse the pun!).
The syntax for OFFSET is:
OFFSET(Reference,Rows,Columns,[Height],[Width]).
The arguments in square brackets (height and width) can be omitted from the formula (they both have a default value of 1, which is explained below).
In its most basic form, OFFSET(Ref,x,y) will select a reference x rows down (-x would be x rows up) and y rows to the right (-y would be y rows to the left) of the reference Ref.
Consider the following grid:
Figure 1: Example table
OFFSET(A1,2,3) would take us two rows down and three columns across to cell D3. Therefore, OFFSET(A1,2,3) = 16.
Figure 2: OFFSET(A1,2,3)
OFFSET(D4,-1,-2) would take us one row up and two rows to the left to cell B3. That means OFFSET(D4,-1,-2) = 14.
Figure 3: OFFSET(D4,-1,-2)
We can use these mechanics to answer this month’s query and construct a very simple scenario table:
Figure 4: OFFSET scenario illustration
This example is included in the attached Excel file. Essentially, the assumptions used in the model are linked from cells J14:J20 (in yellow). These values are drawn from the scenario table to the right of the highlighted yellow range (for example, cells L14:L20 constitute Scenario 1, cells M14:M20 constitute Scenario 2).
The Scenario Selector is located in cell J11. Using OFFSET, we can retain all scenarios and select as we see fit. For example, the formula in cell J14 is simply OFFSET(K14,,$J$11) – that is, start at cell K14 and displace zero rows and the value in J11 columns across. In the illustration above, the formula locates the cell one column to the right, which is Scenario 1.
The advantage of OFFSET over other functions such as INDEX, CHOOSE and LOOKUP is that the range of data can be added to. While the other functions require a specified range, we can keep adding scenarios without changing the formula/making the model inefficient.
OFFSET has other practical uses in Excel, taking advantage of the height and width arguments.
Consider our OFFSET example above. If we 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 (that is, rows 13 and 14), and the width would be three columns going from left to right, with column B as the base (that is, columns B,C and D).
Hence OFFSET(D4,-1,-2,-2,3) would select the range B2:D3.
Figure 5: OFFSET(D4,-1,-2,-2,3)
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 that. This can be seen as follows:
Indeed, we can construct a simple depreciation calculation, transpose references or even build a dynamic chart (one that displays more/less categories of information as required) using OFFSET’s height and width functionalities. The attached workbook provides several examples as illustrations.
While OFFSET is an extremely useful and flexible function, it is not transparent. The syntax is difficult for the user to grasp, and Excel’s auditing tools have problems too. The reference cell in the OFFSET arguments is the sole component that will be identified – for example, OFFSET(A1,2,3) will trace cell A1 only.
So spreadsheet users do get confused – in particular, regarding the reference. When calculating the rows and columns components, the reference cell is excluded, but it is included in determining the height and width.
By popular demand we examine the pivot table, one of Excel’s most useful features. By Liam Bastick, associate director with BPM Analytical Empowerment.
I work with large tables of data which I need to interrogate on a regular basis. A work colleague suggested I set up a pivot table, but I’m not sure how they work. Could you give me guidance please?
A pivot table is a semi dynamic, tabular summary of data. It is one of Excel’s most flexible tools and can give results that would take time to reconstruct with sophisticated uses of functions such as SUMIF, SUMPRODUCT, etc.
Excel 2007 has increased pivot table flexibility significantly from earlier incarnations. However, as with many pre-Excel 2007 features, the location of various tools and options needs to be re-learned. This article gives a basic overview for both Excel 2003 (and earlier) users as well as Excel 2007 users.
To describe the basics of a pivot table, I will use the attached Excel examples:
In this illustration, imagine we are a head office analyst reviewing the electrical sales of four stores – imaginatively called North, South, East and West.
Creating a pivot table on the same worksheet is fairly straightforward, albeit a little different between the Excel versions. If the database is in one block (a ‘contiguous’ range), simply select any cell within the database and then:
Notice that cell L9 is not the top left of the pivot table, as you would expect. In fact, L7 (same column, two rows higher) is the first cell for the Page Field insertion (see Figure 1, below). When selecting where to place a pivot table, always remember to select a cell two rows further down than you might otherwise think.
Figure 1: Pivot Table Construct
The pivot table has four areas where data fields may be placed (a data field is simply any one of the columns of the database – eg Date, Store, Item in this example):
Figure 2: Pivot Table Layout
More than one field can be placed in any of these locations – the effect is a hierarchical grouping depending upon the order of placement.
In essence, a pivot table is a three-dimensional summary of a database. Consider it as a book: each page has a summary table of a selection of the data (for instance, in our example, each page could show the sales of each electrical item for each store on a particular date, so Page 1 is 1 April, Page 2 is 2 April, etc.).
To populate the layout, it is simply a case of dragging and dropping. How this is done exactly depends on which version of Excel you have:
Using the Field List, drag the Date field into the Page Area, the Store field into the Row Area, the Item field into the Column Area and the Amount Invoiced field into the Data Area (Values in Excel 2007), viz.
Figure 3: Example Pivot Table (1)
The power of the pivot table should already be apparent. From the humble origins of a list of data, we can now see which store is leading in sales, enquire why there are no stereo sales in the South and West and so on. Using the drop down arrows in the table (you may need to make them visible in Excel 2007, PivotTable Tools -> Options -> Field Headers icon) or in the Field List pane in Excel 2007, we can filter for certain selections – for example, North’s sales on 2 April:
Figure 4: Example Pivot Table (2)
But let’s not stop there. Resetting the pivot table, one key report would be to determine the amounts outstanding. To do this, we need to construct a calculated field.
Figure 5: Insert Calculated Field Dialog Box
As a consequence of doing this, Excel may ask whether you want the destination cells to be replaced (the software thinks you want to replace or add to the Data Field). Rather than worry about choosing ‘Yes’ or ‘No’ (obviously, choose one!), simply go to the Field List and de-select the Amount Invoiced field (or drag it off the layout from the top left hand corner) and choose the ‘Amount Outstanding’ calculated field instead:
Figure 6: Example Pivot Table (3)
One more thing we’ll do while we’re here: the business has a North West division and a South East division. To see how these divisions have performed, we need to group the stores:
Figure 7: Example Pivot Table (4)
Figure 8: Example Pivot Table (5)
Figure 9: Example Pivot Table (6)
This article was intended only as an introduction to pivot tables – there is plenty more you can do with them.
The Pivot Table options are well worth playing with (Excel 2007, on the Ribbon. Earlier versions: from the PivotTable toolbar, choose PivotTable -> Table Options…). It doesn’t take long to spot:
Earlier I mentioned that pivot tables were semi dynamic. But they do not automatically update. That is, if you change the source data, the data in the pivot table does not automatically recalculate (F9 doesn’t work here). In earlier versions of Excel, if you have more than one pivot table, each pivot table has to be selected and then the refresh icon clicked on the PivotTable toolbar 
, or PivotTable -> Refresh Data. This is quite cumbersome. However, in Excel 2007, you can refresh them all (in the Ribbon, on the PivotTable Tools Options tab, select the Refresh icon in the ‘Data’ section and choose ‘Refresh All’).
Pivot Tables can play havoc with underlying worksheet formats (for example, the underlying grey background keeps disappearing)
The Calculated Fields calculations do not always work as intended, especially with ‘*’ and ‘/’: it is a case of suck it and see
Get it right though and a pivot table can provide useful analyses that would take half a day to write as a formula (and about the same time for Excel to calculate…).
Look out for more on pivot tables in future editions.
