ANAHEIM, Calif.–Boring, dull old Excel doesn’t have to be so boring or dull, according to a trio of credit union execs.
The widely used spreadsheet application offers far more functionality and analytics than most credit unions ever utilize, although that may be changing if the level of interest at one CU meeting here is any indication.
From left, Ryan Fisher, Brandon Smith, Matt Lehman
Getting particular attention during the CUNA CFO Council annual conference here were the balance sheet insights available to credit unions through PowerPivot, an add-in for Microsoft Excel 2010 that enables users to import millions of rows of data from multiple data sources into a single Excel workbook to create relationships between heterogeneous data, and to create calculated columns and measures.
Demonstrating how they use Excel during the CFO meeting were Brandon Smith, CFO with Reliant FCU in Casper, Wyo.; Ryan Fisher, VP-finance and accounting with University of Illinois Community CU, and Matt Lehman, accounting manager with Everence FCU in Lancaster, Penn.
“Imagine having all your data and being able to quickly determine how much each age group, for instance, has in balances,” said Fisher. “You can get that in Pivot Tables in seconds if you do it right. That data that feeds into it first is very important. It has to be good, quality data first.”
4 General Rules
Fisher, who discussed using advanced array formulas and also building dashboards using PowerPivot, said that using Pivot Tables requires four general rules:
1)Ensure labels and headers are on all data.
2)Do not have any blank columns or rows, or it won’t select properly.
3)Try to isolate a data set as much as possible with link cells around the data set. Excel will be able to automatically select your data set as a result.
4)All data sets should be put a vertical format, not horizontal.
“If you can follow those, your pivot table will work well for you,” said Fisher.
Fisher said he likes to use the Vertical Lookout function, which can be used for many things, such as looking at two lists or categorizing something in Excel.
“When looking to create a Pivot Table, I like to channel Steven Covey and begin with the end in mind,” he said, noting that a numbers-heavy Excel spreadsheet can be very difficult for many people to understand or engage with, but a graphic representation can solve that challenge.
Fisher said there are two steps to creating a Pivot Table:
1. Activate the data table by clicking on any cell in the data.
2. Click “insert” and “Pivot Table.” “It will automatically select the data and default to a worksheet. This is the design step. You go to the ‘fill table’ list and you have access to every data point in your data set. Select the ones you want, and you create your first Pivot Table.”
“Pivot Table is one of the most powerful tools in Excel,” said Fisher, before acknowledging, “The name really doesn’t make much sense. If they had named it the ‘Flexible Analysis’ or ‘Data Summary’ tool, it might get more usage.”
Similarly, filters are known as “slicers” within the application.
Fisher urged credit union execs to spend five minutes a day in Excel experimenting with PowerPivot when they return to their offices. PowerPivot is part of the Microsoft Power BI (Business Intelligence) suite, which includes Power Query (for discovery and pulling in the data), Power Pivot (for analysis) and Power View (for visualization) and Power Map.
'You Already Have It'
Reliant FCU’s Smith said the reason more CFOs and finance execs should expand their knowledge of Excel is that among the benefits are the “ability to import millions of rows of data, use multiple data sources, create relationships, create calculated columns, measures and KPIs, and you already have it.”
Smith recommended working with a 64-bit processor when in the application, saying he often crashes when using PowerPivot.
“Creating relationships is one of the biggest changes in PowerPivot,” said Smith. “The relationships look a lot more like Access and a lot less like Excel.”
The final member of the trio speaking to the CFO Council meeting, Matt Lehman of Everence CU, spoke to using VBA, or Visual Basic for Applications. That is the programming language used in Excel that is used to write macros.
“The macros are important, because they can automate repetitive tasks, which saves time and increases productivity, and other programs and software can be utilized in VBA, such as Outlook or a data extraction software such as Monarch Pro,” said Lehman.
The Flash Report
At Everence CU, for instance, Lehman said it uses VBA in conjunction with reports such as its Master Daily Branch Report in which it reviews cash balances, G/L balances, shared branching transactions, teller overs/shorts, and more. It automatically generates a report that is sent to members of the management team and branch managers.
“One of the things we do everyday for management is the Flash Report, a mini cash flow that shows the changes month to date and year to date, in shares, loans, cash position and investments. It allows us to see what we might need to monitor a little more,” he said. “It uses a template, but we save a daily copy that can be gone back to and reviewed.”
Other ways to use VBA.
- Bank reconciliations, to import transaction history from corporate and G/L using a macro and Monarch.
- Upload journal entries to the core processor. Most cores have an import feature that allows import of a G/L journal entry, he said.
Smith spoke briefly of the power of Excel Add-Ins. He often uses Crystal Ball, which is a risk analysis using Monte Carlo simulation.
“I’ve used it for capital budgeting for years before I got into credit unions, and I’ve used it for analysis,” he said. “If you are modeling anything and you start making assumptions, in those assumptions you are deciding a single point of data. That’s a deterministic model. With Crystal Ball you can build a probability distribution function on each one of those cells and see it’s actually a range.”