AFP’s latest FP&A Guide, underwritten by Vena Solutions, looks at how Excel has evolved, and continues to persevere over more than 30 years. AFP spoke with FP&A practitioners, each with a different approach to Excel, to get different perspectives on the software’s strengths and limitations.
Travis Lockhart, CTP, FP&A, finance manager for CALIBRE Systems, relies on Excel nearly exclusively. Early in his career, Lockhart was responsible for various spreadsheet-based processes, like budgeting exercises, and creating a spreadsheet-based forecasting system, where tracking changes across numerous inputs was important. Excel is ideal for these types of duties.
These days, Lockhart serves as more of an “information broker.” He uses Excel to develop and maintain reports that project managers, directors, and senior leadership use to obtain information, develop insights, build their own views of the future, and/or monitor performance, depending on their role and needs.
Over the years, he has trained fellow employees across the organization in Excel, and is more or less the go-to person for providing Excel support as needed. “It’s vital to have people who deeply understand Excel and who can develop tools or processes that fit within its limits,” he said.
Lockhart noted that the advancements Microsoft has made in recent years have drastically improved his experience when using Excel. New versions of Excel tend to create new opportunities for him; there is not so much a data update required, but more of a discussion around what can be done better with that data, how to create efficiencies, etc.
For example, Slicers in Excel have greatly improved data analytics for Lockhart. First introduced in 2010, Slicers provide clickable buttons that allow users to filter PivotTable data. They also indicate the current filtering state, which helps the user understand what is displayed in a filtered PivotTable.
“Slicers made it easier to add a PivotTable that users can control,” Lockhart said. “It puts more power into the hands to the users.”
Sparklines have also proven highly useful for Lockhart. Introduced in 2010, Sparklines are tiny charts in Excel that represent data visually. Much like in traditional charts, data can be displayed via lines or columns. Sparklines collect data from a selection of cells and display it in graph form in a separate cell on the same line.
“Sparklines can go side by side with the data,” Lockhart said. “So rather than having one line chart that has 10 different lines on it, each row of data would have its own individual small, cell-sized line chart. It’s very useful as a practitioner to have your data and your picture, side-by-side. What you’ll do is, you’ll get the idea from one picture, and then you might want to dig back into the data.”
Excel is really a user’s tool, Lapidus added. So for presentations, FP&A practitioners might want to take those pictures that they created with Sparklines and do something else with them—put them on an output tab or move them over to Word or PowerPoint. “But as the user who is gathering and intimately involved with the data, having it side-by-side is great,” he said.
Whenever Lockhart makes a PivotTable that is forward-facing to an internal client, Lockhart typically uses both Slicers and Sparklines. “I would use those specifically if I was creating dashboard-type reports for my internal clients,” he said. “So it wouldn’t just be for a standard report or a chart. If I wanted to create something that was more of a dashboard that users could interact with, that’s where I’d use those tools.”
Microsoft has also introduced autofill tools that enable Excel to recognize patterns. “You don’t have to tell it what you’re doing,” Lockhart said. “It just recognizes patterns and makes suggestions to you. It will just know what you’re doing.”
However, for Lockhart, one of the factors that makes Excel so effective is the freedom it gives him. This is an important point; for complex models that run on dedicated, robust platforms, people will test calculations in Excel; for looking at large data sets, people will test cleanup methods and sample data in Excel. Hence the “Export to Excel” button.
“The reason why Excel is so powerful to me is because it’s flexible; it’s where I get to be creative and do what I want to do,” he said. “It’s where I prototype, it’s where I design—it’s a playground. And that’s something Microsoft can’t make easier in any way, because it needs to be an open slate.”
Even when Lockhart applies add-ins in Excel, they are largely just used to enhance what he’s already been able to do freely in the program. He uses Zebra BI, a third-party solution that provides stronger visualization in certain instances, as well as a Monte Carlo simulation analysis tool. “Those are add-ins that work well for me in Excel, because Excel is where I’m building my models,” he said. “They’re things that I can use with something I’ve already built.”
For more insights, download AFP’s latest FP&A Guide, Making Excel Work for FP&A, here.