Fotolia
CPM software Prophix organizes Spacesaver sales info for success
CPM software helped consolidate financial data, which allowed Spacesaver to cut its monthly close time by two-thirds and replace Excel spreadsheets with OLAP-based analytics.
Corporate performance management software is a curious combination of a mundane-but-essential financial reporting tool and a potentially game-changing planning, forecasting and analytics powerhouse.
Companies often start using CPM software to get the monthly financial close under control then build on this new foundation of reliable data for better forecast and budget accuracy and financial analysis. Shelving and storage manufacturer Spacesaver Corp. has gone through that progression in the six years it has used Prophix software for CPM.
Spacesaver makes high-density mobile shelving and storage equipment, including shelves for libraries, museums and healthcare providers and weapons lockers for the U.S. Marine Corps. The company employs approximately 400 people in a single facility in Fort Atkinson, a small town in southeast Wisconsin.
When Steve Anderson -- now Spacesaver's director of finance -- arrived as controller in 2013, sales forecasting, budgeting and price analysis were handled in Microsoft Excel.
Data wasn't consolidated, and forecasts could only be done at the product or market level. The situation was very chaotic for gathering sales data and closing the books, Anderson said.
"We spent quite a bit of time compiling data and compiling reports and then had very little time in our budget-close schedule to do the analysis that I prefer to do," he said.
Analyzing each product's sales at a more detailed level, such as month and distributor, wasn't feasible.
Steve Anderson
"That's too much data to try to work through meaningfully in an Excel-based model," Anderson said. "You can't plan in a 3,000-row explosion of all those different factors."
Since the Prophix software was installed, a monthly close that used to take eight to 10 days now takes 2 1/2. Sales forecasts are more accurate because they're based on more detailed data, and the finance department has time to provide analytics that helps senior management with strategy, Anderson said.
"Our performance against budget … [and] our profitability by product tends to track the way we budgeted," Anderson said.
Implementing CPM software
Spacesaver had deployed an on-premises IFS ERP system at the beginning of 2013 but found the relatively basic reporting did not meet its needs, Anderson said.
"It wasn't as advanced as some of the reporting I had seen in CPM," he said.
Anderson had implemented the Prophix software at his previous employer three years earlier and knew CPM's capabilities and challenges. The experience helped him deploy Prophix on premises in less than a month and at a critical time, he said.
"We were looking right at our budget season [and] not being ready, since we had just gone through the ERP launch," he said.
There was one significant challenge, however. Before Anderson arrived, the finance team had remapped Spacesaver's chart of accounts and departments.
"That rendered the existing budget model in Excel pretty useless and made it so that we had to redo all of our financial reporting," he said. "Some of it was in IFS. Some of it was in Excel."
Adoption of the Prophix software still proceed quickly, in part, because Anderson functioned as a super user training a small group of users -- Spacesaver's nine-person finance department -- he said. The team spent the following 15 months tweaking Prophix and configuring modules it didn't have time for during the launch.
Spacesaver's CPM architecture
The Prophix software uses SQL Server data warehouse technology to interface with different brands of ERP. At Spacesaver, a SQL Server data warehouse is the intermediary between the manufacturer's IFS ERP system and Prophix. All of them run on premises.
"Part of why the implementation isn't super painful or super long is that you're not having to interface [directly] with any ERP," Anderson said. "It's grabbing the data that's in the warehouse."
Prophix consultants did, however, have to work with Spacesaver's IT department on extracting data from ERP tables in the data warehouse and pulling it into the Prophix software.
Sales projections using CPM software
The company had a clear need for more detailed sales data than it could get using Excel, especially breaking them out by distributor. It sells through about 50 North American distributors, each of which can have different price and product preferences and discounts, Anderson said. Foreign exchange rates also complicate financials. Customers fund purchases through their annual capital equipment budgets, so recurring revenue is minimal. Visibility into distributor and customer demand is essential in making accurate projections.
"Every year, we have to go out and sell projects for the next year, and the composition of those projects can be pretty different from year to year," Anderson said. "We go to market through seven vertical markets, and [all of these] have some of their own challenges and assumptions, profitability and product mix."
Key role of OLAP cubes in Prophix
Spacesaver's Prophix CPM software has financial and sales cubes with fields such as product, source and distributor. The online analytical processing (OLAP) cubes, which are essentially multidimensional spreadsheets that can receive data from sources such as ERP, are what make sophisticated analytics possible.
Each cube "harnesses the power of [an Excel] pivot table -- the ability to slice and dice your data based on any combination of your two members," Anderson said. "That is super helpful for quick ad-hoc analysis where you just want to say, 'Show me my manufacturing supplies for the last five years.'"
Some employees at Spacesaver use a business intelligence tool, QlikView, to analyze more detailed nonfinancial data from the ERP system. The company found QlikView had better synergies with operational data and dedicates the Prophix software to analyzing and reporting sales-related data such as product mix, pricing and profitability, Anderson said.
"We focus on the stuff that doesn't aggregate well out of a [business intelligence] tool," he said.
Prophix also helps the finance team search through ERP data more efficiently to, for example, identify the reasons for manufacturing variances by finding differences among product lines.
"They're not having to sift through all of the transactions because they've already found where the biggest portion of the variance comes from," Anderson said.
Alternatively, the team can look for the causes of variances by having QlikView sort large quantities of ERP transaction data.
"We have very accurate and relatively granular product data and market data that comes through from our ERP," Anderson said.
Breaking free of Excel
Anderson advised those considering a similar move not to try to reprogram their Excel models in the CPM tool, a lesson he learned at his former employer.
"You wind up programming in efficiencies and things that simply aren't needed," he said.
Instead, take time to understand the CPM software.
"Some folks that are sort of talented really, really, really love Excel," he said. "They [say] 'I need this. I need that because I still think in Excel brain.' I tell people try to break that as much as you can because it's a different tool."
The substantial change-management effort needed to break the Excel habit pays off on typical benefits of CPM, which are nonetheless significant for any company that struggles with the monthly close and laborious, imprecise budgeting and forecasting, Anderson said.
