Evaluating CPM/FP&A System: Nine Key Areas for Consideration
by Michael Coveney, co-author of "Budgeting, Planning, and Forecasting in Uncertain Times"
There are many software products that claim to support CPM, but often they only support some aspects, for example financial planning and reporting. One of the issues is that the term CPM is synonymous with budgeting, forecasting and management reporting which by itself cannot provide a complete solution.
Similarly, some vendors have multiple products covering different parts of CPM. For example, many have a scorecard application that they deem suitable for strategy management; a separate solution for collecting budgets and forecasts; and yet another for reporting and analysis. In the context of this framework, these multiple solutions can only work if they are truly integrated and can be made to operate as a single system. Without this level of integration system maintenance becomes an unbearable nightmare that cannot suitably adapt to the dynamics of the economic environment.
It is worth pointing out that CPM from a vendor’s point of view is a mature market and most have similar capabilities. For organisations looking for discreet planning, budgeting, forecasting and reporting solutions, there is a wide range of choice with vendors offering good solutions. But for organisations wanting to implement a complete CPM solution it is easy to be misled on what product suites can and can't do.
This evaluation section of the framework outlines the key areas to be investigated so that those evaluating solutions can accurately assess whether the products selected will meet their planned and future needs of CPM.
One area not covered in this evaluation is a detailed assessment of the technology that underpins a CPM solution, whether that is SQL, OLAP, ROLAP, or In-memory BI. One of the reasons is that having a common technology platform or a system built on a leading ‘open’ database is no guarantee that the vendor has an integrated solution or that it will perform. Solutions should be assessed on their ability to solve a business problem over a period of time. The choice of technology should be up to the vendor to provide the best application they can for the price being offered and that from a customer point of view gives the best return on its investment.
The evaluation areas here are primarily concerned with a product's capability to meet the CPM framework discussed in this document. They include:
- Data Model – the kind of data that the model can hold
- Dimensions, Members and Structures – the way in which dimensions and dimension members are managed
- Functionality – the functions required to support each process within
- CPM Reporting capability – the types of reports required to support decision-making
- Workflow – how users are led and directed through CPM processes
- Audit capabilities – how data and structure changes can be tracked and reported
- Affordability – how to assess the cost of ownershi
- Vendor Ability – how to assess the commitment and expertise of a vendor
- Demonstrations – what things to look for and test in a workshop session
1. Data Model
At the heart of every CPM system is a data model that stores information relating to:
- Strategy and the organisational objectives being supported
- The business environment in which it operates
- The way that business value is generated
- How financial resources have been allocated
- Forecasts on where the business is heading, and
- Financial results that have been/will be achieved
Each type of data is different and contains both structured (e.g. numbers) and unstructured (e.g. text) information that is held at different levels of granularity and for different time periods. Some data will be required on a monthly basis while other – for example forecasts involving contracts – are better handled by assigning a date. This means that if the date changes, then the system should know how to roll this up into the appropriate reporting time period. For organisations that deal in seasons, for example retail, the ability to define time as a span between specific dates is important, while results would still need to be accumulated on traditional monthly basis. All these requirements means that a single multi-dimensional model cannot (without serious compromises) meet the different needs of CPM, and yet this data still needs to be bought together if performance is to be planned and managed.
Things to look for
- Ability to hold multiple data sets with different levels of granularity, dimensions and member combinations to support strategy, tactical plans, budgets, forecasts, management reports and risk.
- Ability to define and accumulate time in multiple ways.
- Ability to store data at a day level, with the system aggregating data into the right time hierarchies for reporting.
- Able to define and hold individual initiatives with associated dimensions and members, along with flexible start and end dates.
- Ability to attach unstructured data – comments, notes, documents, responsibility – with any data item.
- Ability to combine different data types for reporting and analysis, along with any unstructured data.
- Ability to define financial accounts as to type e.g. Debit/Credit, Balance Sheet, P&L, Statistical, etc. This information is used to ensure data rolls up through time and other dimension hierarchies correctly and that variances respect natural sign conventions.
- Ability to define statistical accounts/measures so that any assigned calculations take place in the correct order and at the appropriate level in a hierarchy.
2. Dimensions, Members and Structures
Much of the data within a CPM model will be organised as hierarchical structures e.g. organisation; strategy, product families. Many members will be common across different CPM data sets and so any change in definition should automatically be applied to related sets.
Hierarchies change with time, but in order to preserve historic reports, original structures need to be retained. For assessing potential changes, the CPM system should allow alternative structures that can be used to report the impact of change, should that change be adopted.
Finally, hierarchies are not the only way to select and analyse data. The ability to use attributes where members are selected and grouped according to a range of ‘tags’ assigned to them is an important capability. For example:
- Members in the account dimension can have attributes that identify them as being measures of success, resource, risk, etc.
- Members in the strategy dimension can have attributes that identify whether they are objectives, themes, initiatives, etc.
- Product hierarchy members can have attributes that denote colour, or whether the product is ‘new’. These attributes can then be used to create an alternative grouping of members, irrespective of where they fit in their respective hierarchy.
Things to look for:
- Master data management of dimensions and members. It should be possible to apply a change in a member and have it automatically update any data set where it is used
- Ability to hold ‘cause and effect’ structures for strategy modelling. These should allow data to be accumulated to multiple parents according to the methodology being used (e.g. what is the total cost of all initiatives supporting objective A), as well as by organisational unit, etc.
- Structure changes that are time stamped with previous versions being held
- Ability to consolidate and compare the impact of different structure versions for reporting and analysis purposes
- Ability to assign and analyse data by multiple attributes and not just by a dimensions’ physical structure.
Functionality can be split into two areas – that which is common to all areas of CPM and that which is specific to a particular CPM process. With the latter, a capability to support a specific process may well be useful in other processes. Reporting capabilities are covered in section 5.5. Here are the things to look for in a CPM solution:
- Ability to convert local currency data into base currency(ies). This should be a true financial conversion supporting multiple rate types (e.g. opening rate, average rate, closing rate); detection and posting of exchange gain/losses; and the ability to choose from multiple rate versions (e.g. actual, budget, forecast)
- Consolidation of data from ‘leaf-node’ members into hierarchical groups
- Ability to load data from external systems including the mapping of account and department codes into the appropriate CPM data set members
- Ability to delete data from a CPM data set
- Ability to copy data between periods, versions and other combinations of dimensions and members
- Full security system that automatically restricts users access to functionality and specific areas of data
- Ability to present data entry screens to users along with restricted areas that cannot be changed
- Ability to support multi-languages for both help and setup information
- Ability to support web, social media, and mobile access
Strategic planning specific functionality:
- Supports the methodology being used by the organisation e.g. Balanced Scorecard, Performance Prism, Six Sigma, etc. This means that the system uses the same terminology as defined by the methodology.
- Ability to set up ‘strategy maps’ that show cause and effect and allows senior managers to define, for example, themes and objectives; to allocate measures of success, implementation and resources.
- Support for ‘Driver-based’ planning where entering a few key data items generates related data such as a summary P&L.
Tactical planning specific functionality:
- Ability for users to add initiatives that support defined strategies, comprising of combinations of dimensions and members that describe a course of action.
- Ability to assess combinations of initiatives in terms of resources consumed and their impact on corporate goals.
- Ability to ‘Time shift’ initiatives i.e. to change their start and end data which then causes all associated data to be ‘moved’ in time.
- Ability to ‘approve’ and ‘reject’ and ‘lock’ initiatives from change
- Ability to select initiative combinations based on their ability to optimise resources to meet a specific goal
- Ability to hold combinations of initiatives as different versions of a contingency plan. These do not consolidate with other data and can be ‘re-called’ as required.
Financial planning specific functionality:
- Ability to perform a ‘Top-down’ spread where a single number can be allocated back from a top- level node through to leaf-nodes in a hierarchy. It should be possible to ‘exclude’ members during the spreading process.
- Ability to spread data across members of a dimension according to a range of profiles. It should be possible to exclude members when carrying out the spread.
- Ability to perform allocations according to set rules that can be invoked by an administrator
- Ability to define an approval process with appropriate levels of data locking.
Forecasting specific functionality:
- Ability to hold data by contract, project and status
- Ability to perform time-series analysis and use results to predict future periods
Risk Management specific functionality:
- Ability to hold 'risk' and 'impact' factors
- Ability to calculate risk profile
- Ability to conduct 'What if?' analyses without affecting data held
- Ability to hold contingency plans that can be invoked when risk materialises
Statutory reporting specific functionality:
- Ability to collect, match and eliminate ‘intercompany’ information
- Ability to store ownership data that can be used to adjust results for minority or joint ownership interests
- Ability to eliminate third party ownerships
- Ability to support ad-hoc and recurring journals that ensure Balance Sheet integrity
- Audit trail and reporting – see section 5.7
- Ability to analyse statutory impact over more than a one year time horizon.
- XBRL support
4. Reporting Capability
Reporting occurs throughout all CPM processes, while the Management reporting process brings together a range of information that determines whether the plan is on track and what decisions can be taken to improve performance. Audit report capabilities are covered in section 5.7
Things to look for:
- Ability to create dynamic Strategy maps. I.e. as initiatives are added, the system should be able to position that initiative within the strategy map without having to redefine the report.
- Interactive Dashboards / strategy maps. i.e. users should be able to select ‘off-grid’ members such as the time period being shown or the department for which data is being shown.
- Ability to generate Financial statements and accepted layouts / formatting
- Ability to generate variances that respect Debit/Credit and account type (e.g. Balance Sheet, P&L, Statistical) assignments.
- Ability to ‘drill-back’ to underlying detail/source systems from which the results were produced.
- Ability to automatically match common dimensions and members from multiple data sets for the purpose of generating a report.
- Ability to bring any information including unstructured data, from any data set and place it anywhere on a page.
- Ability to perform simple calculations between multiple data sets on a page.
- Ability to change common dimension member selections from one place where multiple data sets are on a page.
- Ability to generate charts that are linked to reported data sets.
- Ability to sort / group data according to content, dimension member and assigned attributes
- Ability to omit rows/columns where combinations return ‘blank’ data.
- Ability to set up ‘key-words’ that can then be used to drive the content of a report. E.g. Current Month, Current Year – can be set in one place that is then used to generate report content for that setting.
- Ability to group different types of reports into books that can be run as a single action. These respect user security and will omit any data/pages the user is not allowed to access.
- Ability to access data securely from a spreadsheet
- Ability to create pdf, Word, Web and other output formats
- Ability to deliver content via Mobile devices
- Ability to alert users/managers when a specific variance has been exceeded
- Ability to request a response to an alert
- Ability to chase up users who have not responded to an alert
- Ability to review all alerts to determine: Which areas generate the most alerts, the status of response to alerts; how quickly users respond to alerts
Workflow relates to the way users are directed through the different tasks involved in a process. There are two types of process:
- Structured processes are those that are followed through a specific task e.g. budgeting. It’s structured in that the activities, the people involved and the timescales are known in advance.
- Unstructured processes are those that occur when an event or an exception are encountered. In this case the particular activities to be triggered and the people involved are only known when the exception occurs.
Structured processes can be dealt with through menus but it does requires users to know where to look and to choose the right option. This can make it hard in creating an efficient process as there is no way to prioritise options for specific users.
Unstructured processes cannot be realistically handled by menus and will require dynamic workflow capabilities to trigger activities as and when they arise. As these activities are completed they themselves will trigger other activities to be carried out. For example, a sales forecast that is 10% outside of a limit, may trigger a request for more information and confirmation of the levels expected. When this is approved it could trigger re-planning by the factory, or an action by marketing to increase advertising spend. These will then have a knock-on effect onto other departments.
Both types of process should be supported if CPM is to become a continuous, efficient process aimed at managing corporate performance.
Things to look for:
- Ability to define tasks i.e. discreet pieces of work such as enter data, load data, run consolidation, run report, etc.
- Ability to combine tasks into sub-processes that are automatically configured for individual users
- Automatic generation of “To do” lists that are specific for each user and contains the work to be done, the deadline and any approval process
- Automatic warning of task deadlines Automatic escalation of non-action on a task Automatic triggering of tasks based on dates, an alert, exceptions or events Overview of active processes/tasks, highlighting deadlines and status Ability to ‘unwind’ a task or series of tasks with full audit trails.
6. Audit Capabilities
The ability to audit any plan or result is a key requirement of any corporate system if the numbers shown are to be trusted. This includes collecting comments on what the numbers actually mean as well as how they were gathered and transformed.
Things to look for:
- Audit trail on structure changes. All changes should be date-stamped along with the user making the change. Ideally there should be provision for storing comments on why the change was made.
- Historic results should remain with historic structures for audit purposes, however it should be possible to consolidate historic data with newer structures for comparative purposes that do not overwrite historic results.
- Audit trail on processes. It should be possible to review all planning tasks and activities over a period of time. This can help identify bottlenecks that can improve process times in the future and help document the way in which decisions were made.
- Audit trail on all data changes. It should be possible to track how each number was entered; any adjustments and transformations it went through; and how it consolidated into any total. All changes should be time and date stamped, along with user details and the process activity that changed it. As with structure changes, there should be a provision for storing comments on changes that are then available in reports.
This area of the evaluation considers all the costs involved from initial purchase, through implementation and to the resulting systems’ on-going cost. It also looks at the expertise required to setup and maintain the system, and what else an organisation may have to purchase in order to realise their vision for a complete CPM solution.
Things to look for:
- The initial purchase cost
- The cost of increasing the number of users
- The length and cost of training for an administrator and end user
- Estimate of implementation cost
- On-going software maintenance cost
- Other things that need to be purchased to make the solution complete
- The level of expertise to use and maintain the resulting system.
- Does the system provide ‘Best practice’ templates, to kick-start an application
- Can ‘users extend the application for their own local reporting purposes.
8. Vendor Ability
This looks at the vendor’s commitment to CPM and the maturity of the product. Maturity can be both a blessing and a curse – Mature products can offer a large reference base but also may be coming to the end of their life, which then may require an upgrade or redevelopment cost.
Things to look for:
- How CPM fits into the future of the company
- The other products the company sells and supports, and how much is dedicated to the CPM application being sold.
- Other products the vendor may have that seem to compete with the solution being proposed
- How long the product has been around and where will it be in 5 to 10 years time.
- The level of business expertise they can offer in helping redesign processes and metrics
- The level of technical expertise to implement the solution
- The financially stability of the company providing the solution.
- The experience and capability of the company installing the system
9. Demonstration Workshop
So far the software evaluation has focused on capabilities that can be viewed in isolation to each other. However, to support CPM these capabilities must be delivered in a seamless way so that the solution operates as single application. The only way this can be properly evaluated is by performing a number of scenarios that cross different processes. This is the purpose of the demonstration workshop.
It is unlikely this can be accomplished without some planning from the vendor and so the scenarios to be evaluated should be communicated in advance and covered in detail during an interactive workshop. The aim is to gauge the levels of integration that exist and the effort required to maintain them.
Strategic Planning scenarios
- Create a strategy map showing actual measures for the current and target measures for the next 3 years
- Define a new objective. Set targets for success and the limit on resources that can be consumed by supporting initiatives.
- Show how the new objective updates the strategy map
Tactical Planning scenarios
- Combine a number of initiatives to review the resources they would consume if approved. (
- Review initiatives by start date
- Delaying an initiative by 7 weeks: Show the impact of this delay on planned resources
Financial Planning scenarios
- Show how the system directs departments through the planning process.
- Show how a top level strategic financial goal is spread down to individual departments as a target
- Move a business unit between divisions and compare the impact on plans.
- Show how a department has its plans approved.
- Show how to create an alternative version of the budget which has a different departmental structure
- Show how potential sales contracts and/or projects are recorded.
- Change the status of a potential future contract (e.g. from tentative to approved) and how that updates the overall forecast
- Change the date of a potential future contract and how that updates the timing of the overall forecast
- Show how to add a new product, associated forecast data and how this updates the overall forecast
- Show how the system handles issues such as when a forecast misses a future target
Management reporting scenarios
- Show how the system handles the mapping and import of actual data
- Show how actual results updates:
o Comparison with strategic plan (i.e. updates the strategy map)
o Comparison with tactical plan o
o Comparison with budget / forecast
- Show how to report the impact of initiatives on the achievement of strategic goals
- Show report that sorts initiatives according to overspend against budget
- Show how to create a report that combines strategic goals, initiative status vs. budget
- Show basic financial statements: Balance Sheet, P&L, Cash Flow
The aim of this framework has been to clarify the purpose of a CPM system; ways in which it can be implemented; how it relates to supporting BI/Reporting applications; and how to evaluate CPM software solutions.
It outlines an ideal implementation although it is unlikely that a company will ever implement it in this way. However, it provides a goal for an IT systems strategy that will genuinely help managers and executives manage performance.
By adopting such a framework, organisations will realise a number of high-level benefits it provides, including:
- A single, consistent framework by which the whole organisation manages performance
- Focus on the things that are critical to the organisation as a whole
- Reports and analyses that tells the story of ‘what and why’
- Information in context that links strategy with resources and monitors the effectiveness of processes
- A documented audit trail that shows the decision-making process
- A mechanism by which IT projects can be assessed and placed in the context of improving