You are currently browsing the tag archive for the ‘spreadsheet’ tag.
Our benchmark research on enterprise spreadsheets explores the pitfalls that await companies that use desktop spreadsheets such as Microsoft Excel in repetitive, collaborative enterprise-wide processes. Because people are so familiar with Excel and therefore are able to quickly transform their finance or business expertise into a workable spreadsheet for modeling, analysis and reporting, desktop spreadsheets became the default choice. Individuals and organizations resist giving up their spreadsheets, so software vendors have come up with adaptations that embrace and extend their use. I’ve long advocated finding user-friendly spreadsheet alternatives.
One of the first adaptations was for application vendors to use a spreadsheet (either a grid format or Excel itself) as a user interface. In these products users seem to be working in a familiar spreadsheet environment, but the interface is tied to an application that has controlled business logic, formulas and workflows, and the data is held in a relational or multidimensional database. This approach can give organizations the best of both worlds: the familiarity of a spreadsheet but in a structure that addresses most of the technological flaws inherent in desktop spreadsheets. Yet this approach isn’t always enough. It is fine for business processes in which a third-party application is the appropriate choice, but in many other situations where people collaborate using the same model, analytical methods and data, a spreadsheet – not an application – is the better choice. Moreover, our research finds multiple reasons why companies continue to rely on spreadsheets. More than half (56%) of participants pointed to user resistance to change, and many others cited a business case that wasn’t strong enough (that is, the benefits of switching did not merit the costs) and a related issue: that alternatives are too expensive.
In collaborative processes where a spreadsheet is the most practical tool, another alternative is a technology developed by Boardwalktech. The company’s Collaboration Platform (BCP) products support a secure, two-way exchange of data between multiple users.
Instead of having to collect multiple spreadsheets through the email system and then combine them, BCP users can automatically share information at the individual cell level when they want. For instance, working offline in a spreadsheet model individuals can enter actual results and evaluate changes to a forecast or plan, playing with whatever what-if scenarios they see fit. When finished, they can connect to the Boardwalktech server and click to share the updated information with others in the organization. Those people will have immediate access to the changed data.
This approach offers advantages to the way most organizations collaborate with spreadsheets. For example, the exchange of data between spreadsheet users is immediate and takes place at the cell level rather than replacing the entire spreadsheet. Thus, unlike when spreadsheets are exchanged through email, updates can be automatic and far more secure. When spreadsheets are connected through a server, contention (that is, two people trying to change the same data at roughly the same time) is an issue. Most server-based spreadsheets (such as applications built on an Excel server) deal with contention by controlling changes at the file or record-object level, employing a check-in and check-out methodology or record locking to control concurrency. This means that an entire spreadsheet or large portions of it cannot be altered until one person has finished making changes. This process can cause substantial delays. In contrast, BCP enables concurrent, multiuser collaboration at the cell level. Especially in larger spreadsheets shared among multiple users, that can cut down on delays in updates and changes because multiple people can be making updates to different parts of the spreadsheet at the same time.
Another attractive feature of Boardwalktech’s approach – especially when compared with collaborating on spreadsheets over email – is that individuals can share only a portion of their spreadsheet (even just the contents of a single cell) with other individuals. Adam, for example, may want to share only a few lines of summarized information from his forecast with Betsy, who needs it to drive some – but not all – of her projections in her part of the business. Adam and Betsy have different spreadsheets with different row and column structures, yet the shared data remains synchronized regardless of the changes they make to their individual spreadsheets. Colleen, a business analyst, may have a complex formula that every other analyst must use, and this formula will evolve over time because of changing business conditions. David and Ed will always be using the same, correct and up-to-date formula in their own, individual spreadsheets that used by others in the organization without having to check for updates.
Boardwalktech offers several prebuilt templates that support inter- and intra-business collaborative processes. For the latter, one area in which a third-party application often is not a viable solution is where analytical models of data and reports must be shared between companies. Cost, implementation times, existing software environments and licensing issues often make that impractical. Browser-based solutions may be more difficult for people to navigate through compared with a spreadsheet, especially if substantial amounts of data must be updated and people need to enter data across multiple dimensions. As well, people in different organizations may use incompatible approaches to modeling that reflect the different needs of their organizations. The ability to share only essential elements of spreadsheets without having to homogenize models and data structures eliminates serious barriers to collaboration. In addition, even within companies these issues can come into play, especially for cross-functional processes or among different business units.
Boardwalktech’s products include configurations for processes where spreadsheets are heavily used today. These include sales and operations planning (S&OP), trading partner collaboration, supply and demand planning and sales and revenue forecasting. For finance organizations the company offers treasury and cash management and tax planning as well as budgeting and planning. There is also a project and portfolio management offering, which can be used by IT organizations, facilities management, R&D and others to plan, assess and forecast projects and project-like efforts. These can be deployed singly or in combination. One of the advantages of implementing, say, a sales and revenue forecasting application along with budgeting and planning is that the sales forecasting can easily tie in with the budgeting, meaning that these top-line numbers, which are managed by the sales organization, can be updated instantly in the budget and at whatever level of granularity is necessary. As well, Boardwalktech’s IT Process Platform allows companies to take any spreadsheet-driven collaborative process and eliminate many of the inherent defects.
In 2013 Boardwalktech had couple of key steps forward with new integration framework using its ‘SuperMerge’ technology and advancements to configuring templates that are used for access and input. Both of which help further embrace and extend use of spreadsheets. For most organizations, spreadsheets are an indispensable tool but they are not always the appropriate technology, especially when used in repetitive, collaborative enterprise-wide processes. It’s important to understand their limitations and not abuse them. In some cases, third-party or internally developed dedicated applications are the right choice. In others, embracing and extending existing spreadsheet-driven processes is the most practical approach. If your organization is currently using desktop spreadsheets for some collaborative business process, it probably is putting up with a host of issues that are the inevitable result of the spreadsheet’s inherent shortcomings. If so, I recommend evaluating Boardwalktech’s collaboration platform.
Robert Kugel – SVP Research
Host Analytics has introduced AirliftXL, a new feature of its cloud-based financial performance management (FPM) suite that enables its software to translate users’ spreadsheets into the Host Analytics format. I find it significant in three respects. First, it can substantially reduce the time and resources it takes for a company to go live in adopting the Host Analytics suite, lowering the cost of implementation and accelerating time to value. Second, it enables Host Analytics users who have the appropriate permissions to create and modify models and templates that they use in planning, budgeting, consolidation and reporting. This can enhance the value of the system by making it easier to maintain. Third, it can make it far easier to routinely collect and connect planning and analytical models used by all departments and business users as it has outlined in its planning cloud offering. Although it has limitations in its initial release, AirliftXL gives corporations a workable alternative to stand-alone spreadsheets and has the potential to substantially increase productivity and effectiveness of an organization in the full range of budgeting, planning, consolidation and reporting functions.
AirliftXL addresses a fundamental issue that diminishes the productivity of companies, especially finance departments. I’ve noted in the past that desktop spreadsheets are indispensable tools for individual tasks and ad-hoc analysis and reporting, but they are poorly suited to repetitive collaborative enterprise-wide functions such as planning, budgeting, consolidation and reporting. Spreadsheets are seductive because so many people are well trained in using them that they can translate their ideas into even complex models, do analysis and create reports. However, the productivity that spreadsheets afford in authoring is more than offset when they are used over time. Desktop spreadsheets have fundamental technological shortcomings that make them unwieldy for any repetitive, collaborative task. After more than a few people become involved and a file is used and reused, cracks begin to appear. Very quickly, a large percentage of the time spent with the file is devoted to maintaining and updating them, as our spreadsheet research has shown with up to 18.1 hours per month in maintenance that I have analyzed. Spreadsheets are notoriously error-prone. In addition to monetary losses, some of which have been spectactular, there is a drag on productivity as users try to locate the source of errors and discrepancies that routinely occur in spreadsheets and then fix those mistakes.
AirliftXL enables ordinary users to create spreadsheet models and reports in Microsoft Excel and then quickly convert these to the Host Analytics enterprise system. Those that “own” a model, analysis, report or process can control these in Host Analytics. This speeds the process of setting up Host Analytics because there’s no need for someone to “translate” the company’s current set of spreadsheet models, analyses and reports. This cuts the time (and therefore the cost) of setting up the new system.
It also means that whenever changes need to be made, those responsible can make the changes themselves. Allocations and analytical models used in planning and consolidations can become part of a company’s system almost immediately. These alterations can be effected by “exporting” the Host Analytics object to a spreadsheet, modifying it and uploading back to the system. As well, users can create new models, analytics and reports in Excel and import them into the Host Analytics system. There’s no need for resident expertise or consulting time to make such changes. AirliftXL provides an organization with the best of both worlds: first, the up-front productivity that comes from enabling the author, a subject-matter expert, to quickly translate his or her ideas into a spreadsheet and, second, the ongoing productivity that is achieved when the plan, analytic model or report is kept in a centralized, easily accessible and controlled environment. These same authors can update and expand their analytical models or reports. Host Analytics can render models back into an Excel spreadsheet and the owner – not a consultant or trained IT person – can make the necessary changes and then upload them back into the system. This is an important capability because change is a constant in businesses and these changes must be reflected in financial performance management systems.
Organizations have hundreds, sometimes thousands, of spreadsheets circulating that support a multitude of processes and users in every department and business unit. AirliftXL can help incorporate them into a controlled enterprise software environment. Information that today is kept in one part of an organization can be viewed and used by others. Budgets and integrated business plans can quickly incorporate the most up-to-date information. Complex models now held in spreadsheets can be more controllable, consistent and safely accessible to a wider group of users. Creating links across individual spreadsheets (say, sales forecasts prepared by the sales organization and a company income statement forecast prepared by Finance) is straightforward, although linking to external data sources (say from a spreadsheet to a relational data store) is a little trickier. As well, Excel’s built-in financial, statistical and logical functions are all maintained in Host Analytics.
AirliftXL has the potential to be an important differentiator for the company. IBM Cognos has had something like this in Cognos Insight but from my analysis it is not as easy to use as the Host Analytics feature. Some corporations have finance IT professionals with deep subject-matter expertise as well as IT systems skills, but even their presence does not address the root cause of the misuse of spreadsheets. Most people who understand the needs of the business lack the IT skills necessary to use their company’s systems. They default to using spreadsheets because it is more expedient than trying to transfer their knowledge to someone who understands IT systems.
While AirliftXL is an important first step in taming the spreadsheet problem, it has limitations. For one, it’s not possible at this point to create a dynamic model such as an integrated income statement, balance sheet and statement of cash flows. This is a snap in a two-dimensional spreadsheet grid but much harder when working with a relational or multidimensional database. Moreover, there’s no guarantee that the spreadsheets imported into Host Analytics will be free of formulaic errors or even if it is well constructed. Thus, companies will need to put quality control processes in place, especially if a spreadsheet can have a material impact on the accuracy of financial statements or could defeat controls for fraud. It also would be handy if some vendor would create a product that could automate the digestion of masses of spreadsheets floating around companies as described in this patent for extracting semantics from data.
Despite these reservations Host Analytics’ AirliftXL provides an important capability that can cut costs of deploying and maintaining its software and increase its value to a company. This advancement builds on top of its recent rating as a Hot Vendor in the 2013 Value Index on Financial Performance Management. I recommend that corporations looking to change or upgrade all or some of their financial performance management suite consider Host Analytics and how AirliftXL helps transition the use of spreadsheets to a dedicated application approach.
Robert Kugel – SVP Research