TECHNOLOGY EXCEL - Strategic Finance

[Pages:2]TECHNOLOGY

EXCEL By Bill Jelen

Year-Over-Year Analysis Using Excel

It's a new year, and you'd like to do a year-over-year analysis showing if each customer bought more or less in 2009 than in 2008. This month, we'll look at four different methods for building the report.

Let's say that you can gather an invoice register for 2008 on one worksheet and an invoice register for 2009 on another worksheet. The structure of the report will vary depending on your system, but let's assume that each report at least has a column for Customer Name and another for Revenue.

Method 1: Pivot Table to Compare Two Lists

Add a new blank worksheet to your workbook. Add three columns: Customer, Revenue, and Year. Copy the Customer and Revenue columns from the 2008 worksheet to columns A and B of the blank workbook. Fill column C with "2008" for all of these records. Below that data, paste the Customer and Revenue from the 2009 worksheet into columns A and B, filling column C with "2009" for all of those records.

Choose one cell in your new data set. Select the Pivot Table command from

either the Data menu (Excel 2003) or the Insert tab (Excel 2007). Click OK or Finish to create a blank pivot table. Drag the Customer field to the Row area, the Year field to the Column area, and the Revenue field to the Data Items area. To improve the report, remove the grand total column by unchecking Grand Total for Rows in the PivotTable Options dialog. Add a Calculated Item called Delta, which calculates `2009'/`2008'-1. In PivotTable Options, add a checkmark next to "For Error Values Show," and leave the textbox blank to hide the division by zero errors. You now have a report as shown in Figure 1.

Method 2: Pivot Table with Date Grouping

This method works particularly well if your 2008 and 2009 columns are in the same format and if your data has an invoice date field. Copy the 2009 data (minus the headings) beneath the 2008 data. Choose one cell and add a pivot table. Drag the Invoice Date field to the row area. Right-click the first date and choose Group. In the Grouping dialog box, select both months and years. Click OK.

The PivotTable Field list now offers a new virtual field called Years. Drag the Years field to the column area. Remove the Date field from the Row area. Add Customer to the row area. Add Revenue to the Data area. As in Method 1, you can remove the Grand Total for Rows from PivotTable Options. Since you've grouped the date field, however, you can't add a calculated item to calculate the Delta. This calculation will have to be entered as a formula next to the pivot table. While building the formula, don't point to cells using the mouse or the arrow keys. Instead, type the formula. Otherwise, you might get the annoying GetPivotData functions, which don't copy well.

Both methods 1 and 2 will fail if the combined data from the invoice registers contains more rows than your version of Excel offers. In that case, you can use the consolidation method.

Method 3: Consolidation

The Consolidation command works with a single column of labels in the left column and any number of numeric fields going across. You can then consolidate multiple ranges. Excel will match up the

54

STRATEGIC FINANCE I January 2010

Figure 1

Figure 2

customers in the first column of each range and present a summary.

Set up a range with Customer in column A and 2008 revenue in column B. Make sure that the heading in B1 includes the year in the heading. For example, "2008 Revenue." It's okay to have each customer appear multiple times in this range, so you can copy directly from your invoice register.

Set up a similar range with Customer in column D and 2009 Revenue in Column E.

Select a blank section of your worksheet, perhaps cell G1. From the Data menu, select Consolidate.

In the Consolidate dialog, choose the range in columns A and B. Click Add. Then choose the range in columns D and E. Click Add. In the bottom of the dialog, choose both Top Row and Left column. Click OK. Figure 2 shows the Consolidate dialog.

The result will be a superset of all customers found in either list. Because columns B and E had different headings, you'll have two numeric columns to the right of the customers. Note that the customer heading will always be missing from cell G1. Also note that the cus-

tomers won't be sorted. Select cell G1 and click the Sort button to sort by customer. Then you can add the Delta column.

Method 4: Use a Third-Party Tool

If all of the above methods seem too intimidating, there are several third-party utilities that will allow you to compare the two worksheets in a couple of clicks. All of them offer a free trial period that will easily allow you to get the year-end reporting done while you try them out. Easy-XL (Easy-) offers the Compare Sheets command. Start on the 2008 invoice register. Select Compare Sheets. Choose to compare to the 2009 invoice register. Group by Customer, and select Revenue from both sheets. The popular Active Data audit software () offers similar steps with their Compare Sheets command. And DigDB (DigDB. com) offers the Roll up command. First,

manually combine both years' data into a single worksheet, as in Method 2. Then go to Column, Convert, Date To, Year. Finally, use the Roll up command.

After performing this analysis with any of the four methods, you'll be able to focus on two specific classes of customer. Look for customers who had revenue in 2008 and now have no revenue in 2009. Why did these customers leave? Also focus on the customers who purchased for the first time in 2009. Find out how these customers found you and if they are happy with their new relationship with you. SF

Bill Jelen is the host of and the author of 25 books, including Excel Gurus Gone Wild. Send questions for future articles to IMA@.

January 2010 I STRATEGIC FINANCE 55

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download