News Stay informed about the latest enterprise technology news and product updates.

From Excel to financial analytics software: Users, experts share tips

When preparing for the transition, companies should review existing processes, involve end users and clean up the data.

For many businesses, Microsoft Excel is the application that handles financial tracking and analytics. As businesses...

grow, many run into Excel’s limitations for forecasting, profit and loss statements and consider implementing financial analytics software to provide more manageable tracking and reports. For a successful transition, experts and users alike recommend evaluating the processes that are involved with using Excel for finance and including end users throughout the selection and implementation process.

The indication that it’s time to move from Excel for finance to financial analytics software is the number of users, said Robert Kugel, senior vice president and research director at Ventana Research in San Ramon, Calif. “The main things to consider … is how many people are sharing the spreadsheet and how often is it being used,” he said. “If there are a lot of people contributing to this spreadsheet, then you've got the wrong technology.”

Organizations already using business analytics systems can benefit from performing analytics in software made for the task instead of Excel. Many business users download information from their existing business intelligence system into Excel and then perform additional analysis, Kugel said. “One of the advantages of working with a dedicated [financial analytics] application is that it’s directly connected to [the company’s] data store” and not modified by an end user, he added.

Inventory existing spreadsheet processes
To prepare financial data to move off Excel, inventory the spreadsheets the organization is using, said Max Henrion, CEO of Lumina Decision Systems Inc. in Los Gatos, Calif. “A lot of times, there’s no central place where the spreadsheets are tracked, so the challenge is just to identify them,” he said.

In fact, much of the financial data preparation process involves just understanding the thought processes behind the data analysis. “What [companies have] to start to do to move off Excel is figure out what they're trying to do, [such as] build more flexibility [or] reduce costs associated with financial reports,” said Toffer Winslow, general manager at Lavastorm Analytics in Boston. Companies need “to start with a firm understanding of what they’re trying to accomplish,” he added.

Involve end users in the transition
After companies determine the financial data analysis process, they should involve end users in the decision making, Henrion said. “If [the transition to software] happens without their buy-in, it's probably not going to be a happy outcome.”

Andrew Schrage, co-owner of Money Crashers, a personal finance website based in Chicago, switched from Excel to financial analytics software and began six months in advance. “About three months prior to the implementation of the system, we began extensive training sessions, and I think about two weeks prior to the switch is when I finally saw that my staff realized the overall benefit,” he said. Schrage also involved the IT department throughout the decision-making process.

Ensure financial data consistency
Data needs to be consistent in the spreadsheets before porting it over to financial analytics software, said Chris Arndt, a partner at wealth management company Red Granite LLC in Chicago. Financial software typically uses a relational database as its back end, and these databases employ strict data integrity rules. “Unfortunately, these rules are usually not enforced in Excel, so it takes some work to prepare for the transition. For example, make sure in Excel that the name of a certain vendor is always spelled exactly the same way anywhere it appears on that spreadsheet,” he said.

Arndt also suggested ensuring that Excel data is in a “table-like” structure, with one header row at the top and data listed in the appropriate column. Finally, if the company has already organized the Excel data, it can also use a vendor-supplied template to copy and paste Excel data directly into its analytics software, Arndt said.

When the data is ready, the IT department can port it over to the financial analytics software. “One notable tactic [the IT department] employed was to pare down all of our existing financial data by eliminating colored text and cells, which made the transition a little smoother,” Schrage said.

The transition from Excel may be met with some resistance, but using a dedicated package can have benefits. “You get your answers a whole lot faster and you don't have delays in coming to the answers because people find errors” in the data, Ventana Research’s Kugel said.

Dig Deeper on ERP financials