Posts

This article is part 3 of an 8-part series on evaluating the best CPM tools for your business. Part 3 focuses on Excel functionalities that strengthen the best planning and financial reporting software applications.

 

Almost every financial professional both loves and hates Microsoft Excel. It seems like we can’t live with it and we can’t live without it!

So, when you are looking to acquire a new financial reporting or planning solution and you are comparing your vendor finalists, how well each solution interacts with Excel tends to be part of the top user requirements.

It should be noted that while an increasing number of cloud solutions emerged between 2010 and 2015, the popular sentiment from the browser-based vendors was that their tools would completely get rid of Excel. But, in short order, their customers started to miss the formatting and calculation power of Excel (as well as its familiarity) when they were creating reports and budget models. As a result, the early cloud vendors added Excel add-ins to provide an alternative design experience for Excel fans, and several newer vendors such as Vena and Solver made Excel an integral part of their cloud solutions.

For most companies, Excel functionality is THE key to success when using the best planning and financial reporting software.

Here are some of the top features to look for in reporting and budgeting Excel add-ins

Most finance, accounting, and budgeting users would consider strong Excel integration to be one of the top 5 or top 10 features when evaluating and comparing planning and reporting vendors. Below, we will discuss some important features to consider in this regard.

  1. The difference between Excel export/import and Excel add-ins

While pretty much every reporting, planning, and corporate performance management (CPM) solution can import data from and export reports to Excel, these processes should not be confused with Excel add-ins, which are plugins to Microsoft Excel that appear on its ribbon.

With an Excel add-in design tool, users build dynamic, parameter-driven reports that pull data from the ERP or CPM database. Data can be refreshed and written back (budgeting and forecasting) without any type of export and import process, which provides self-service for end users.

On this page you can find hundreds of examples of reports and budgeting forms built with an Excel add-in and made to run in a browser. Reviewing vendor examples of the types of templates you want to build with your new solution can be a very good assurance that the design and layout capability in your chosen solution is going to take care of your organization’s needs.

  1. Examples of pure Excel tools versus web portals with Excel add-ins

In the past, Excel add-ins were stand-alone reporting tools that needed to be installed on every user’s desktop (or virtual machine). So, if you had 50 users your IT department would be maintaining and upgrading 50 Excel add-ins. Luckily, this has changed. Since cloud architectures emerged in the CPM market, almost all vendors have built multi-tenant web portals that are managed and automatically upgraded by the vendor.

Today, there are still a few of the classic Excel add-ins left and most have the ability to connect to cloud ERPs and “trickle” the data to Excel when reports are processed. All major vendors, however, have web portals where reports are stored and, in some cases, also executed in the user’s browser without requiring Excel to run them. This architecture is particularly useful for planning processes where a significant number of end users can simply open their browsers and enter budgets and forecasts that are stored directly in the CPM solution’s cloud database.

  1. The importance of dynamic Excel rows and columns versus static ones

Most organizations add accounts, departments, and other dimension members to their ERP during the year. For older Excel add-ins, this usually requires manual maintenance of reports and budget templates in order to insert new rows or columns, or to maintain the content of dropdowns for parameters (e.g., a list of departments). This is because these add-ins can only put formula references at the cell-level in the spreadsheet.

With modern Excel add-ins, you can have dynamic listing of rows or columns and global report parameters.

Here are some quick examples:

  • Dynamic rows

If you have, for example, 20 Operating Expense accounts, you can create a range formula on a single row in Excel that automatically expands out to 20 rows. If someone adds 1 new account number in that range, you automatically get 21 rows in the report (or budget form). In older Excel add-ins, you have to manually create each row and, since they are static, new accounts will not become new rows automatically.

  • Dynamic columns

Let’s say you want actual data from January up to the current month and a forecast from next month through December. In a modern Excel add-in this report can be done dynamically, regardless of which month you run it for. With static, legacy Excel add-ins, you will need to manually change the formulas each month, make 11 versions of the report, or do major “tricks” in the report to make it more dynamic.

Template design with dynamic rows and dynamic columns

Template design with dynamic rows and dynamic columns

  • Dynamic global parameters

Typical examples of these are report filters for company, department, period, or budget version. In legacy Excel add-ins, these are designed as regular Excel dropdown boxes that populate based on data (e.g., a list of months or departments) hidden somewhere in the workbook. In modern Excel add-ins, these parameter selectors are dropdowns on a side menu or in the Excel sheet that pulls their content directly from the database.

In other words, they are always fresh and don’t need hidden dimension lists in each report or budget template.

In summary, modern Excel add-ins with dynamic rows, columns, and global parameters provide quicker report design and less maintenance work. They are also less likely to produce wrong reports because they can automatically include new accounts and other ERP dimension members that tend to change.

  1. Is Excel the primary design tool or an additional solution to learn?

Finance and accounting people tend to be very busy and few like to spend more time than necessary to build or maintain reports. As we discussed earlier, this is a major reason that Excel add-ins are so popular and have returned as part of cloud CPM solutions. However, when you compare vendors with Excel-based report designers, it is important to research whether their Excel tool is their second (“optional”) report writer or THE report writer.

Cloud solutions where there is a proprietary web designer, plus an optional Excel designer, result in users having to not only learn two tools but also having to constantly deal with decisions regarding whether a new report should be built in Excel or in the proprietary browser tool. Making a monthly package of reports from both tools, if possible, can be messy and lead to too much manual effort.

Solutions with a single, purpose-built Excel designer have the advantage of delivering one single tool to learn for users, and packaging and distributing reports can all be done in one place.

  1. Built for cloud

While some legacy Excel add-ins are still pure on-premises tools, most can now connect to cloud ERPs. The latest generation of Excel add-ins can not only be managed (installation, user security, etc.) from cloud portals, but some, like Solver, can even “convert” templates automatically from Excel to web reports and input forms. In these tools, users can choose to run the same report as a web report without requiring an Excel add-in. Alternatively, they can use the Excel add-in to open the report in Excel and execute there.

In other words, part of the research and comparison of the vendors’ reporting and budgeting solutions should be to find out if their planning and financial software relies on legacy tools or purpose-built cloud architectures.

How much does a CPM solution with Excel-based reporting and planning cost?

While it is important to do your homework to ensure that the vendor you choose has the key features needed for a successful deployment, your total cost and savings in time and effort matter – as does your improved capacity for better, faster decision-making at your company.

Here are some factors to consider when you get prices from your vendor finalists:

  • Does the annual subscription from each vendor contain the same user count and modules?
  • If you are receiving a discount, how long until it resets to the list price?
  • Does the vendor have a written policy for annual price increases?
  • Are the implementation estimates from each vendor for exactly the same work?

A good rule of thumb is to ask each vendor for the total subscription cost for the first 5 years. Make sure this includes any potential price increases. Also, if the vendor is owned by a private equity firm, chances are that they will be sold while you are still a customer, so you will want to ensure that you receive a document that states their policy for price increases in the future (including if they are sold to another company).

Here is a free vendor comparison and return on investment (ROI) tool to help you compare vendors across a number of different features. This tool also includes a simple return on investment (ROI) calculator that is part of the total vendor score.

Conclusion

In summary, choosing one of the best financial reporting software solutions to automate monthly reporting, as well as to cover other management reporting needs, will ultimately drive better and faster decision making at your company. This is why the task of finding the best planning and financial reporting tools is increasingly becoming a strategic priority for organizations across all industries.

As we discussed earlier, certain features are more important than others in the evaluation process and can act as key drivers of success – in addition to a well-executed implementation process, of course.

Links to useful software research and evaluation assets

This article is part 2 of an 8-part series on evaluating the best CPM tools for your business. Part 2 focuses on feature sets within the best financial reporting software applications.

 

Financial reporting software that also offers consolidations and planning functionality belongs to a software category typically referred to as Corporate Performance Management (CPM). Some also refer to this as Enterprise Performance Management (EPM). Since most organizations are either currently planning to or have recently moved their ERP systems to the cloud, cloud-based financial reporting solutions are now more popular than ever.

While native ERP report writers are able to produce financial statements and sub-ledger reports, they are generally not great at formatting and advanced formulas. Plus, since they are built into the ERP system, they can’t report on data in other data sources. So, almost always, companies export some or many of their reports to Excel to finalize and assemble them there.  As a result, an entire industry of cloud-based reporting solutions has sprung up to take companies’ reporting automation and month-end close processes to the next level.

There are numerous software vendors that now deliver independent reporting solutions, either stand-alone or as part of a CPM suite. As a result, during a software selection process you’ll need to carefully choose the solution that is RIGHT for your business. This means that the functionality must be right for your unique business and that it should support your industry-specific requirements. And, of course, the return on investment (ROI) needs to be positive.

When working through a software selection process to find the best financial reporting software for your organization, there are always some features that are more important than others.

Here are some of the top features to look for to find the best financial reporting software

While most vendors can probably showcase more than 100 features in their product (something which can make software selection a painful process), the main success criteria can be narrowed down to a few key areas. Each one is listed and discussed below.

  1. Advanced report formatting

Per definition, all good report writers have a “template designer” to create reusable, parameter-driven reports. In addition to the pre-built reports that leading vendors should provide out of the box, the report designer is where a trained user or consultant can build new templates such as Profit & Loss reports and Balance Sheets specific to the needs of the business.

About half of the CPM vendors offer add-ins to Microsoft Excel where templates are designed. This typically provides the best formatting and layout flexibility. Plus, almost all finance department team members are very familiar with Excel already, so having a report writer built into Excel shortens the learning curve. Other vendors have built proprietary report designers so users can design financial reports, but these never contain all the formatting capability of Excel. A few vendors not only provide Excel report design, but they provide a cloud web portal where end users can run the templates as web reports from any device and with no Excel add-ins needed.

P&L – Variance Report

P&L – Variance Report (Copyright – Solver, Inc.)

Note 1: Be aware of CPM vendors that offer two report designers because that means twice as much training for power users. It can become messy in the month-end close process, report packages, and other areas if templates are created with two different technologies. The reason for two tools is almost always that the functionality in the vendor’s proprietary designer was not enough for their customers, so they later added an Excel designer to handle complex customer models with a lot of formatting.

Note 2: In addition, be aware of sales pitches that use “sexy” dashboards to draw your attention away from questions around great formatting in financial statements. While dashboards are awesome management tools focused on graphical analysis, they are NOT built to be financial report writers that can easily develop and maintain things like GL account structures behind Profit & Loss, Balance Sheets, Cash Flow Statements, and other critical reports.  

Without a report designer in your new planning solution, you are at high risk of significantly having to change your favorite report formats to fit the capability of the vendor’s tool. In many cases, you may even find yourself and your staff relying still on your overloaded Excel spreadsheets because you’ll be exporting reports to Excel and then manually reformatting them every month.

Here is list of about 500 examples of reports, dashboards, and budgeting templates. It is a good idea to ask your vendor candidates if you can see examples from their template libraries. The more examples they provide, the more you can be assured that their solution has a good report designer.

  1. Advanced formulas

Financial reports are some of the hardest reports to build due to diverse accounting calendars, complex or changing charts of accounts, and the custom ratios and calculations needed to measure performance. These formulas and ratios, typically created in Excel, can be difficult or impossible to translate into a solution that uses proprietary formulas or pre-set calculations.

Formula familiarity is also important. Again, Excel is the de facto standard, not just in formatting but in formulas. For this reason, most reporting vendors either create their report designer in Excel or try to mimic Excel formulas in their proprietary tool.

Without a strong and familiar formula capability in your new report designer, there is a high risk that you will be dependent on consultants to help with report design on an ongoing basis, and/or that your finance team will waste hours every month dumping reports into Excel to “fix” them.

  1. Advanced consolidations

While all financial report writers can aggregate data across accounts and departments, a much smaller number of solutions can perform true consolidations. In addition to consolidating financials across subsidiaries and divisions to produce high-quality corporate reports, important features include:

  • Manual intercompany elimination entries with comments and audit trail
  • Automatic intercompany eliminations
  • Currency conversion
  • Roll-up of balances from subsidiaries with different chart of accounts
  • Ability to enter and track additional “topside” adjustments where needed

A best-of-breed solution will also have business rules such as trees and/or dimension attributes that automatically include new accounts without having to manually update reports or run the risk that monthly financial reports are wrong or incomplete.

Some ERP systems do a pretty good job at handling this internally, but usually with a lot less flexibility than a true CPM reporting solution.

  1. Advanced closing and reporting process checklist

While most modern cloud-based CPM reporting solutions offer a workflow module, checklists are rarer. A financial reporting process checklist is typically a chronologically organized list of all the items a controller has to perform or oversee in the month-end close as it relates to reporting. Sometimes this includes 100 or more individual tasks with many people involved.

Major steps include:

  • Closing of the books in the ERP system and transferring the data to the reporting solution
  • Viewing exception and reconciliation reports to flag issues and reconcile items
  • Running trial balance reports
  • Adjusting entries (in ERP or CPM tool)
  • Running of all month-end reports
  • Performing variance and trend analysis with comments
  • Publishing (web viewing, email, or Excel) monthly reports with comments
  • Sending PowerPoint presentations to the executive team

The top reporting solutions offer interactive checklists to help ensure that everything gets done on time. They often include functionality like checkboxes, descriptions, responsible people, links to activity, deadlines, and notifications.

Interactive Checklist for Monthly Reporting

Interactive Checklist for Monthly Reporting

Without a good workflow module and checklists, chances are good that you are spending a lot more time reminding people of deadlines and asking them for feedback or comments. This level of constant follow up can get even more frustrating and complex if you are stuck managing tasks manually in companies that have multiple subsidiaries and large accounting teams involved in the month-end close.

  1. Pre-built ERP integrations

All good CPM reporting solutions, by definition, have to be able to integrate with your ERP system’s General Ledger balances at the minimum. However, not all tools are great at reporting on other data. Along the same lines, some reporting solutions have better integrations to certain ERPs than others.

Here are some questions to ask different vendors during your evaluation process:

  • Is the integration to your ERP in real time or is data exported to the CPM solution’s cloud database? (There are pros and cons of both.)
  • How frequently can the data from the ERP be refreshed within the reporting tool?
  • Is the ERP integration pre-configured or do you have to map and configure it?
  • Does the report writer only work well with GL data or can you also bring in sub-ledger transactions and non-ERP data?
  • Will the integration pick up changes such as new accounts and companies?

When you are evaluating different CPM vendors for your financial and operational reporting needs, you should request detailed information about each solution’s integration to your systems, including estimates on the time and cost it takes to get them configured. A really good, pre-built integration should take at the most an hour to configure, while “toolboxes” can take days to set up and connect to each data source. Which one your vendor offers will therefore usually become clear when you see their estimates for the integration step in the implementation.

Without good, automated integrations to your source systems, your users will end up spending a lot of wasted time on loading and possible “cleaning” of data.

  1. Built for cloud

While on-premises financial reporting solutions were the standard technology for decades, today it is cloud solutions that rule. CPM reporting tools that are built with native cloud architecture offer many benefits over the classic on-premises solutions. These benefits include back-end functionality such as multi-tenancy to allow for efficient and frequent upgrades, spreading of processing and data loads across hardware resources, and otherwise taking advantage of what large public cloud data centers and platforms have to offer.

As an example, in the old on-premises world, it was normal to do an annual upgrade for your software, while in the cloud world it is the norm to expect completely free and automated monthly updates. These regular updates also provide users with a continuous stream of new features and bug fixes.

Highly flexible cloud solutions will still allow end users to run and view reports in both Excel and their web browser (no software installation needed on users’ computers). Make sure that both interfaces use the same report definitions (not two different reporting technologies) and allow for live drill-down into transaction details whenever the user needs to analyze at a deeper level.

Without a purpose-built cloud architecture, a vendor will fall behind their competitors over time. A number of the legacy on-premises vendors did not rebuild their technologies to be optimized for the cloud and, as a result, they will at some point have to rebuild their product or their customers will migrate to other vendors.

How much does a financial reporting solution cost?

While it is important to do your homework to ensure that the vendor you choose has the key features needed for a successful deployment, it is also important to consider your total cost and savings in time and effort, as well as the solution’s potential to improve decision making at your company.

Here are some things to think about when you get prices from your vendor finalists:

  • Does the annual subscription from each vendor contain the same user count and modules?
  • If you are receiving a discount, how long until it resets to the list price?
  • Does the vendor have a written policy for annual price increases?
  • Are the implementation estimates from each vendor for exactly the same work?

A good rule of thumb is to ask each vendor for the total subscription cost for the first 5 years. Make sure this includes any potential price increases. And, if the vendor is owned by a private equity firm, chances are that they will be sold while you are still a customer. In these cases, it is a smart choice to ensure that you receive a document that states their policy for price increases in the future, including if they are sold to another company.

Here is a tool to help you compare vendors and calculate return on investment (ROI).

Why not use Excel or the financial reporting functionality in my ERP system?

Excel is by far the world’s most popular reporting tool because it is free (if you already own Excel), incredibly flexible, and almost all accounting and finance professionals know how to use it. If you have a simple chart of accounts and not too many business units and departmental users, Excel may very well be the best financial reporting software for your business.

However, everyone knows when it is time to replace their homegrown spreadsheet reports. Warning signals include:

  • Painful manual report distribution
  • Troubles with consolidation of spreadsheets
  • Broken links
  • Poor reporting flexibility
  • Lack of user security
  • Versioning issues
  • and so on…

All ERP systems have basic reporting functionality, but these tools usually fail at great formatting, their ability to include non-ERP data, and their flexibility to manage account and company hierarchies dynamically within report definitions. The truth is that, regardless of an ERP vendor’s promotion of their built-in reporting features, companies almost always end up back in Excel for some of their reporting even after they buy a brand-new cloud ERP solution. Then, when Excel gets too painful, they acquire a CPM solution.

Conclusion

In summary, choosing a new financial reporting solution to automate monthly reporting, as well as to cover other management reporting needs, promises to ultimately drive better and faster decisions at a company. This is why financial reporting ease is increasingly becoming a strategic priority for organizations across all industries. As we discussed earlier, certain features are more important than others and can be key drivers of success in addition to a well-executed implementation process.

Links to useful software research and evaluation assets

This article is part 1 of an 8-part series on evaluating the best CPM tools for your business. Part 1 focuses on evaluating planning capabilities within the best budgeting and forecasting software applications.

 

Planning software, used to streamline budgeting and forecasting processes, belongs to a software category typically referred to as Corporate Performance Management (CPM). Whether caused by economic uncertainty or a more competitive marketplace, this type of cloud software is rapidly increasing in popularity right now. After all, companies that can deliver accurate budgets to plan for their resource allocations, and that continuously update their forecasts to help predict results, get an edge over businesses that don’t have this capability.

However, just implementing a CPM software to streamline the planning processes is not the entire solution for delivering an optimized system for budgeting and forecasting. While there are now dozens of software vendors that can deliver CPM software with planning features, you also need to make sure that the solution is RIGHT for your business. This means that the functionality must be right for your unique company and its budgeting and forecasting needs, and it also has to support industry-specific requirements. And of course, the return on investment (ROI ) needs to be positive.

When working through a software selection process to find the planning software for your organization, there are some features to look for that are more important than others.

Here are some of the top features to look for to find the best budgeting and forecasting app

While most vendors can probably showcase more than 100 features in their product (something which can make software selection a virtual nightmare), there is a clear 80/20 rule that can be applied when you are ready to zoom in on critical success factors.

Here is a list of five major functionality areas:

  1. Flexible input form and report designer

All the best budgeting and forecasting solutions have a “template designer.” In addition to the pre-built input forms and reports that a CPM solution should provide out of the box, the template designer is where a trained user or consultant can build new templates or tailor existing ones to the specific needs of the business.

About half of the CPM vendors have built add-ins to Microsoft Excel where templates are designed. This typically provides the best layout and the richest formula choices, and, of course, most finance departments are very familiar with Excel already which shortens the learning curve. Other vendors have built a proprietary template designer, often with formulas that are similar to Excel.

Payroll Example from Excel add-in

Note: Be aware of CPM vendors that have two template designers because that means twice as much training for power users. It can become messy in workflows, report packages, and other areas if templates are created with two different technologies. The reason for two tools is almost always that the functionality in the vendor’s proprietary designer was not enough for their customers, so they then added an Excel designer to handle complex customer models with a lot of formatting.

Without a strong template designer in your new planning solution, you are at high risk of either significantly having to change your favorite budget input formats to fit the capability of the vendor’s tool or, in many cases, of keeping a portion of your planning models in manual spreadsheets.

Having a flexible and user-friendly template designer also allows for the creation of budget reports that can be run throughout the budget process to see real-time budget updates and identify potential areas of concern. This helps you address issues quickly and early in the process – meaning you will have better visibility and more accurate projections.

Here is list of about 500 examples of reports, budgeting, and forecasting forms, as well as dashboards. It is a good idea to ask your vendor candidates if you can see examples from their template libraries. The more examples they provide, the more you can be assured that their solution has a good template designer.

  1. Line item detail and comments

Especially in annual budget processes, these are very important features to ensure proper documentation and justification of sales figures, expenses, or other figures that a user enters in their department. Typical examples of areas where line item detail and comments are frequently used include Travel Expenses and Office Supplies. When end users are actively using line item detail and they enter comments wherever an explanation is needed, it also tends to increase their sense of ownership and accountability in the budget they are submitting.

corporate forecasting software dashboard

Input of expenses at the GL account level. Includes line item detail, spreading, etc.

Without line item detail, an end user may have to keep detailed build-ups in spreadsheets. Alternatively, they may simply enter higher level figures with no bottom-up calculation behind their numbers. Both of these approaches may result in more inaccurate budgets, as well as delays in the budget process as budget approvers and reviewers often have to ask department heads and other end users to provide explanations and additional detail to back up their numbers.

  1. Workflow and checklists

Organizations that decide to buy a planning software typically have 10 or more users and sometimes hundreds of users. Strong workflow functionality can be a huge time saver by managing:

  • Deadlines
  • Open and closed budgets
  • Budget statuses (submitted, approved, rejected, etc.)
  • Which input forms should be used by different departments
  • And more

While most modern cloud-based planning solutions offer workflow modules, checklists are rarer. A budget checklist is typically a chronologically organized list of all the items a budget manager has to perform or oversee in a corporate planning process. A checklist could start with strategic goals and steps to forecast the rest of the current year, then go into the actual budget process, and finish with reporting and consolidation of the final budget and executive presentations. Checklists help ensure that everything gets done on time and often include functionality like checkbox, description, responsible person, a link to activity, deadline, and notifications.

Planning and reporting process manager

Planning and reporting process manager

 

Without a good workflow module and checklists, chances are good that you are spending a lot more time reminding people of deadlines, asking if their numbers are final and ready for approval, and otherwise managing the process. Processes can get even more frustrating and complex to manage manually in companies that have multiple budget versions to keep track of.

  1. ERP and payroll integrations

While all good CPM solutions can import actual data from source systems such as ERPs and payroll software, the quality and complexity to configure automated integrations varies a lot. Complexity grows when you have data sources spread between cloud and on-premises. For example, it is not unusual that a company’s ERP system is a legacy on-premises or hosted solution and they have other data sources such as payroll that may be a cloud solution. This results in multiple different direct and/or file-based integrations.

When you are evaluating different CPM vendors for your budgeting and forecasting needs, you should request detailed information about each solution’s integration to your systems, including the time and cost it takes to get them configured. A really good, pre-built integration should take at the most an hour to configure, while “toolboxes” can take days to set up and connect to each data source. Which one your vendor offers will therefore usually become clear when you see their estimates for the integration step in the implementation.

Without good, automated integrations to your source systems, your users will end up spending a lot of wasted time on loading and possible “cleaning” of data.

  1. Built for cloud

While on-premises planning solutions were the standard technology for decades, today it is cloud solutions that rule. Planning solutions that are built with native cloud architecture offer many benefits over the classic on-premises solutions. These include back-end functionality such as multi-tenancy to allow for efficient and frequent upgrades, spreading of processing and data loads across hardware resources, and otherwise taking advantage of what large public cloud data centers and platforms have to offer.

As an example, in the old on-premises world it was normal to perform an annual upgrade of a software, while in the cloud world, it is the norm to provide completely free and automated monthly updates. This practice also provides users with a continuous stream of new features and bug fixes.

Without a purpose-built cloud architecture, a vendor will fall behind their competitors over time. A number of the legacy on-premises vendors did not rebuild their technologies to be optimized for the cloud and, as a result, they will at some point have to rebuild their product or their customers will migrate to other vendors.

How much does budgeting and forecasting software cost?

While it is important to do your homework to ensure that the vendor you choose has the key features needed for a successful deployment, the total cost and the savings in time and effort, as well as improved decision making, are just as important.

Here are some things to think about when you get prices from your vendor finalists:

  • Does the annual subscription from each vendor contain the same user count and modules?
  • If you are receiving a discount, how long until it resets to the list price?
  • Does the vendor have a written policy for annual price increases?
  • Are the implementation estimates from each vendor for exactly the same work?

A good rule of thumb is to ask each vendor for the total subscription cost for the first 5 years. Make sure this includes any potential price increases.

Also, if the vendor is owned by a private equity firm, chances are that they will be sold while you are still a customer. It is a smart idea to ensure that you receive a document from the vendor that states their policy for price increases in the future, including if they are sold to another company.

Here is a tool to help you compare vendors and calculate return on investment (ROI).

Why not use Excel or the budgeting and forecasting functionality in my ERP system?

Excel is by far the world’s most popular budgeting tool because it is free (if you already own Excel), incredibly flexible, and almost all accounting and finance professionals know how to use it. If you have a simple budget model and few users, Excel may very well be the best planning software for your business – but everyone knows when it is time to replace their homegrown spreadsheets with something better.

Warning signals include:

  • Painful distribution
  • Troubles with collection and consolidation of spreadsheets
  • Broken links
  • Poor reporting
  • Lack of user security
  • Versioning issues

All ERP systems have basic budgeting functionality, but that is rarely enough to handle full-fledged budgeting and forecasting processes. And, regardless of an ERP vendor’s promotion of their built-in planning features, almost always companies end up back in Excel with their budget models even after they buy a brand-new cloud ERP solution. When Excel gets too painful, they acquire a CPM solution.

Conclusion

In summary, choosing a new CPM solution to automate the planning process, as well as to support better and faster decisions, is increasingly becoming a strategic priority for organizations across all industries. As we discussed earlier, certain features are more important than others and can be key drivers of success, in addition to a well-executed implementation process.

Here is a free vendor comparison tool to help you compare vendors across a number of different features. This tool also includes a simple return on investment (ROI) calculator that is part of the total vendor score.

Links to useful software research and evaluation assets

Subscription Revenue by Customer Report Example

What is a Subscription Revenue by Customer Report?

Subscription revenue reports with customer detail are considered customer reports and are often used by sales managers to analyze monthly sales and revenue per customer. Key functionality in this type of report shows monthly subscription revenue per customer. It also calculates average revenue per customer as well each customer’s % of the average. The figures and the column chart are ranked from high performer to low performer. You will find an example of this type of report below.

Purpose of Customer-Focused Subscription Revenue Reports

Companies and organizations use Customer-Focused Subscription Revenue Reports to track each customer’s contribution to the current month’s revenue. When used as part of good business practices in a sales department, a company can improve its ability to quickly spot top and bottom performers, as well as, reduce the chances that sales issues at the customer level go undetected.

Customer-Focused Subscription Revenue Report Example

Here is an example of a Subscription Revenue by Customer.

Subscription Revenue by Customer Report Example

Subscription Revenue by Customer Report Example

You can find hundreds of additional examples here.

Who Uses This Type of Report?

The typical users of this type of report are: Sales Managers.

Other Reports Often Used in Conjunction with Customer-Focused Subscription Revenue Reports

Progressive Sales Departments sometimes use several different Customer-Focused Subscription Revenue Reports, along with subscription dashboards, detailed customer sales reports and other management and control tools.

Where Does the Data for Analysis Originate From?

The Actual (historical transactions) data typically comes from enterprise resource planning (ERP) systems like: Binary Stream, Microsoft Dynamics 365 (D365) Finance, Microsoft Dynamics 365 Business Central (D365 BC), Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP, Microsoft Dynamics SL, Sage Intacct, Sage 100, Sage 300, Sage 500, Sage X3, SAP Business One, SAP ByDesign, Acumatica, Netsuite and others.

In analyses where budgets or forecasts are used, the planning data most often originates from in-house Excel spreadsheet models or from professional corporate performance management (CPM/EPM) solutions.

What Tools are Typically used for Reporting, Planning and Dashboards?

Examples of business software used with the data and ERPs mentioned above are:

  • Native ERP report writers and query tools
  • Spreadsheets (for example Microsoft Excel)
  • Corporate Performance Management (CPM) tools (for example Solver)
  • Dashboards (for example Microsoft Power BI and Tableau)

Corporate Performance Management (CPM) Cloud Solutions and More Examples

Subscription Revenue by Product Report Example

What is a Subscription Revenue by Product Report?

Subscription revenue reports with product detail are considered analysis tools and are often used by product managers and sales executives to review product performance. Key functionality in this type of report shows monthly subscription revenue per item. It also calculates average revenue per item as well as each product’s % of the average. The figures and the column chart are ranked from high to low performer. You will find an example of this type of report below.

Purpose of Subscription Revenue by Product Reports

Companies and organizations use Subscription Revenue by Product reports to track each item’s revenue for the current month. When used as part of good business practices in a sales department, a company can improve its ability to quickly spot top and bottom performers as well as reduce the chances that sales issues at the item level go undetected.

Subscription Revenue by Product Example

Here is an example of a Subscription Revenue by Product.

Subscription Revenue by Product Report Example

Subscription Revenue by Product Report Example

You can find hundreds of additional examples here.

Who Uses This Type of Report?

The typical users of this type of report are: Product Managers, Sales Managers.

Other Reports Often Used in Conjunction with Subscription Revenue by Product

Progressive Sales Departments sometimes use several different Subscription Revenue by Product, along with subscription dashboards, detailed product sales reports and other management and control tools.

Where Does the Data for Analysis Originate From?

The Actual (historical transactions) data typically comes from enterprise resource planning (ERP) systems like: Microsoft Dynamics 365 (D365) Finance, Microsoft Dynamics 365 Business Central (D365 BC), Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP, Microsoft Dynamics SL, Sage Intacct, Sage 100, Sage 300, Sage 500, Sage X3, SAP Business One, SAP ByDesign, Acumatica, Netsuite and others.

In analyses where budgets or forecasts are used, the planning data most often originates from in-house Excel spreadsheet models or from professional corporate performance management (CPM/EPM) solutions.

What Tools are Typically used for Reporting, Planning and Dashboards?

Examples of business software used with the data and ERPs mentioned above are:

  • Native ERP report writers and query tools
  • Spreadsheets (for example Microsoft Excel)
  • Corporate Performance Management (CPM) tools (for example Solver)
  • Dashboards (for example Microsoft Power BI and Tableau)

Corporate Performance Management (CPM) Cloud Solutions and More Examples

Recurring Revenue and Churn Analysis Report Example

What is a Recurring Revenue and Churn Analysis Report?

Recurring revenue and churn analysis reports are considered subscription management tools and are often used by sales managers and accountants to track important customer and contract metrics. Key functionality in this type of report tracks trends and variances for Monthly Recurring Revenue (MRR), new sales, upgrades, downgrades and churn. It also shows customer count at the beginning and end of the current period. Charts and traffic lights simplifies the analysis. You will find an example of this type of report below.

Purpose of Recurring Revenue and Churn Reports

Companies and organizations use Recurring Revenue and Churn Reports to keep a keen eye on how their MRR and the underlying drivers are trending. When used as part of good business practices in a sales and customer service department, a company can improve its managers’ ability to quickly track the performance of their subscription business, as well as, reduce the chances of losing valuable time if any opportunities or issues are discovered late.

Recurring Revenue and Churn Report Example

Here is an example of a Subscription Revenue Trend Report with MRR and churn analysis.

Recurring Revenue and Churn Analysis Report Example

Recurring Revenue and Churn Analysis Report Example

You can find hundreds of additional examples here.

Who Uses This Type of Report?

The typical users of this type of report are: Boards, Executive Teams, CFOs, Sales Managers, Customer Success Managers.

Other Reports Often Used in Conjunction with Recurring Revenue and Churn Reports

Progressive Sales and Customer Service Departments sometimes use several different Recurring Revenue and Churn Reports, along with subscription dashboards, detailed customer billing reports, deferred revenue reports and other management and control tools.

Where Does the Data for Analysis Originate From?

The Actual (historical transactions) data typically comes from enterprise resource planning (ERP) systems like: Microsoft Dynamics 365 (D365) Finance, Microsoft Dynamics 365 Business Central (D365 BC), Binary Stream, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP, Microsoft Dynamics SL, Sage Intacct, Sage 100, Sage 300, Sage 500, Sage X3, SAP Business One, SAP ByDesign, Acumatica, Netsuite and others.

In analyses where budgets or forecasts are used, the planning data most often originates from in-house Excel spreadsheet models or from professional corporate performance management (CPM/EPM) solutions.

What Tools are Typically used for Reporting, Planning and Dashboards?

Examples of business software used with the data and ERPs mentioned above are:

  • Native ERP report writers and query tools
  • Spreadsheets (for example Microsoft Excel)
  • Corporate Performance Management (CPM) tools (for example Solver)
  • Dashboards (for example Microsoft Power BI and Tableau)

Corporate Performance Management (CPM) Cloud Solutions and More Examples

Quarterly Trend Report for Subscription Revenue Example

What is a Quarterly Trend Report for Subscription Revenue?

Recurring revenue trend reports are considered key analysis tools and are often used by sales managers and executives to review multi-year growth trends for their subscription business. Key functionality in this type of report displays the past 8 quarters of recurring revenue by customer. Each quarterly column can be expanded to reveal the monthly detail behind it. The chart on top of the report shows the revenue trend by quarter.

Purpose of Multi-year Subscription Revenue Trend Reports

Companies and organizations use Multi-year Subscription Revenue Trend Reports to quickly get a big picture analysis of their recurring revenue by customer. When used as part of good business practices in a sales and customer service department, a company can improve its cash flow and related customer account planning, as well as, reduce the chances that managers low visibility to tracking individual customer contribution to revenue over time.

Multi-year Subscription Revenue Trend Report Example

Here is an example of an Eight Quarter Rolling Subscription Revenue trend report.

Quarterly Trend Report for Subscription Revenue Example

Quarterly Trend Report for Subscription Revenue Example

You can find hundreds of additional examples here.

Who Uses This Type of Report?

The typical users of this type of report are: Sales and Customer Service Managers, Account Managers.

Other Reports Often Used in Conjunction with Multi-year Subscription Revenue Trend Reports

Progressive sales and customer service Departments sometimes use several different Multi-year Subscription Revenue Trend Reports, along with subscription dashboards, detailed customer billing reports, deferred revenue reports and other management and control tools.

Where Does the Data for Analysis Originate From?

The Actual (historical transactions) data typically comes from enterprise resource planning (ERP) systems like: Binary Stream, Microsoft Dynamics 365 (D365) Finance, Microsoft Dynamics 365 Business Central (D365 BC), Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP, Microsoft Dynamics SL, Sage Intacct, Sage 100, Sage 300, Sage 500, Sage X3, SAP Business One, SAP ByDesign, Acumatica, Netsuite and others.

In analyses where budgets or forecasts are used, the planning data most often originates from in-house Excel spreadsheet models or from professional corporate performance management (CPM/EPM) solutions.

What Tools are Typically used for Reporting, Planning and Dashboards?

Examples of business software used with the data and ERPs mentioned above are:

  • Native ERP report writers and query tools
  • Spreadsheets (for example Microsoft Excel)
  • Corporate Performance Management (CPM) tools (for example Solver)
  • Dashboards (for example Microsoft Power BI and Tableau)

Corporate Performance Management (CPM) Cloud Solutions and More Examples

Subscription Revenue Trend by Customer Report Example

What is a Subscription Revenue Trend by Customer Report?

Subscription revenue trend reports are considered essential for recurring revenue analysis and are often used by sales managers and accountants to review past or future monthly recurring revenue (MRR). Key functionality in this type of report shows selected customers in the rows and the MRR in the columns. In the example seen below, the Total column expands to display the past 48 individual months of MRR. The report can be run for any future or past month and will automatically present the prior 48 periods.

Purpose of Subscription Revenue Trend Reports

Companies and organizations use Subscription Revenue Trend Reports to easily see the month-by-month trend in MRR with customer detail. When used as part of good business practices in a sales and customer service department, a company can improve its cash flow planning, as well as, reduce the chances that managers have low visibility to individual customer contributions to MRR.

Subscription Revenue Trend Report Example

Here is an example of a Subscription Revenue Trend Report with a Total column that expands to see the 48 months of MRR by customer.

Subscription Revenue Trend by Customer Report Example

Subscription Revenue Trend by Customer Report Example

You can find hundreds of additional examples here.

Who Uses This Type of Report?

The typical users of this type of report are: Sales and Customer Service Managers, Account Managers.

Other Reports Often Used in Conjunction with Subscription Revenue Trend Reports

Progressive Sales and Customer Service Departments sometimes use several different Subscription Revenue Trend Reports, along with subscription dashboards, detailed customer billing reports, deferred revenue reports and other management and control tools.

Where Does the Data for Analysis Originate From?

The Actual (historical transactions) data typically comes from enterprise resource planning (ERP) systems like: Binary Stream, Microsoft Dynamics 365 (D365) Finance, Microsoft Dynamics 365 Business Central (D365 BC), Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP, Microsoft Dynamics SL, Sage Intacct, Sage 100, Sage 300, Sage 500, Sage X3, SAP Business One, SAP ByDesign, Acumatica, Netsuite and others.

In analyses where budgets or forecasts are used, the planning data most often originates from in-house Excel spreadsheet models or from professional corporate performance management (CPM/EPM) solutions.

What Tools are Typically used for Reporting, Planning and Dashboards?

Examples of business software used with the data and ERPs mentioned above are:

  • Native ERP report writers and query tools
  • Spreadsheets (for example Microsoft Excel)
  • Corporate Performance Management (CPM) tools (for example Solver)
  • Dashboards (for example Microsoft Power BI and Tableau)

Corporate Performance Management (CPM) Cloud Solutions and More Examples

pic 1

An increasing number of companies are adding subscriptions as one of the revenue models to their business. Two different examples of this service include:

  • Netflix, which is a 100% subscription-based company focused on B-to-C, or business to consumers.
  • Microsoft, which originated in an era where software sold as one-time licenses, has today become an increasingly subscription-based software provider, with most of their software delivered in a software-as-a-service (SaaS) model using their Azure cloud.

As companies begin to adopt subscription-based revenue models for their customers, accounting departments and finance teams find themselves in a new struggle. Tasks that once were relatively easy in a world of “one and done” sales deals, like invoicing, reporting and analysis, and budgeting for various types of subscription contracts can now be quite complex and time consuming. On top of that, companies now have to deal with monthly, annual and multi-year subscriptions. Other challenges center around existing customers and adding to or removing from their contracts, as well as, customer churn (cancelled subscriptions), and other complexities.

However, today’s customers using Microsoft’s two cloud accounting systems, Dynamics 365 Finance and Dynamics 365 Business Central are in luck. A Canadian software vendor by the name of Binary Stream has developed an add-in module called “Subscription Billing Management” (SBM), which in short allows accountants to elegantly manage their subscription contracts right within their Dynamics ERP system.

So, What About the Reporting, Budgeting and Forecasting for Subscription Data?

In collaboration with Binary Stream, the U.S.-based company, Solver, has expanded its Microsoft Dynamics 365 Finance and Dynamics 365 Business Central integrations to include key information from Binary Stream’s Subscription Billing Management app.

Solver is a modern Microsoft Azure-based, multi-tenant cloud solution that automates and streamlines reporting and planning processes. It is often referred to as a Corporate Performance Management (CPM) solution.

With Solver integrated to the Dynamics ERPs, as well as the Binary Stream app, customers benefit from financial and operational reporting that includes their subscription data, all in one single cloud portal. Furthermore, they can use Binary Stream SBM’s subscription data as a basis for single and multi-year revenue forecasts.

What are Examples of Subscription Reports and Budgets?  

The following are examples of specific reports and budget input forms created in Solver and based on actual subscription data flowing in from the integration available through Binary Stream’s SMB for Microsoft Dynamics app.

This first example shows a multi-year forecast going up to 10 years into the future. Based on the drivers seen in the yellow cells in the top left corner, the user can quickly generate monthly and annual subscription revenue forecasts.

Multi-year subscription revenue and churn budgeting and forecasting with monthly detail

Multi-year subscription revenue and churn budgeting and forecasting with monthly detail

When it comes to reporting, a flexible report writer like the one in Solver, can provide a multitude of report types to slice and dice subscription related data by metric, customer, and product.

The report below shows monthly recurring revenue (MRR) with metrics for new sales, upgrades/downgrades and churn. It also shows the monthly trend for the ending MRR.

Dashboard analysis of subscription metrics like MRR and Churn

Dashboard analysis of subscription metrics like MRR and Churn

The report below shows a chart and data on an eight-quarter rolling basis. This makes it easy for users to analyze subscription revenue over multiple years. Any individual quarterly column can be expanded to display detail for the three underlying months.

Eight quarter rolling subscription revenue report with monthly detail

Eight quarter rolling subscription revenue report with monthly detail

The Scheduled Billing report example seen below can be run for any given period. It helps accountants see the specific payment status per invoice per customer, including total, balance, deferred and recognized amounts.

Scheduled billing report showing recognized and deferred revenue per customer

Scheduled billing report showing recognized and deferred revenue per customer

Sometimes the finance team needs to analyze deferred revenue by customer. The example below shows deferrals by month for the remainder of the current year and then displays the summary for the next three years into the future.

Multi-year deferred revenue by customer report for the next three years

Multi-year deferred revenue by customer report for the next three years

In conclusion, as a company with a subscription-driven business model, you have a solid cloud-based accounting system with Microsoft Dynamics Finance or Dynamics Business Central. When adding Binary Stream’s app for Subscription Billing Management, you get a specialty add-in that is completely focused on helping you manage your subscriptions. By connecting Solver’s cloud-based reporting and planning solution, you now have a best-in-class suite to not only help with day-to-day accounting and analysis, but can also keep a keen eye on the future with world-class budgeting and forecasting functionality.