How to Transition out of Excel as a Planning Tool

#tldr: Pace yourself. Add value with the initial implementation.

Planning tools have been around for a while now, the market is relatively mature, but that doesn’t mean all companies have adopted a planning platform.?Many companies, especially mid-sized companies and even departmental solutions in larger companies, continue to rely predominantly on Excel files for planning expenses, revenue, headcount and more.

If you’re working at a company using Excel for planning, then you know the issues:

• Spreadsheet errors are not only common, but annoyingly persistent
• Knowing which version of the spreadsheet model, and which version of the forecast is the most recent is a constant issue
• Iterating on changes, especially collaborative changes, is very painful, and sometimes not possible
• Consolidating across the organization (departments, projects, regions) is manual and time consuming

All of these limitations mean that most of the analysts’ time is spent inputting and checking data, and hoping everything is correct. Excel is an excellent modeling environment. And it does an ok job at being a database, at least for small models or prototypes. But Excel doesn’t really cut it as an enterprise database, or as a collaborative planning and reporting tool.

Prototyping how your organization forecasts, be it operating expenses, people, or capital, is a great activity to do in a spreadsheet application. But once you have a repeatable system, it’s time to thing about something that can address the issues listed above.

The benefits of switching to a planning tool, like IBM Planning Analytics (TM1) directly address the issues listed above:

• No hidden formulas or hardcoded numbers hidden in system
• Transparency of calculations
• No multiple versions of model – one model online, available to all with only the current version “open for entry” so no confusion about versions
• Collaborating on inputs, commentary, and review is built-in
• Consolidating across your organization is built-in, and flexible to meet the needs of managing the business

But where to start? How do you transition from an Excel-based planning world to one based in a system like IBM Planning Analytics (aka TM1)?

1.?????Decide which modules you need to get started

Spreadsheet applications can be notoriously open-ended. Meaning, there are usually lots of sub-models, sometimes hidden away, that support the final product. So, the question becomes: what parts do we put in a Planning system first??Here’s a guide:

• P&L :?Putting your entire P&L (not just operating expenses) in from the beginning will give you the biggest return for your investment in a new planning system. Even if you might have offline sub-models for some parts, having the entire P&L allows you to do a lot more reporting and analysis right out of the gate. You can import your monthly actuals from your ERP system, input many items directly, and, even if your Phase 1 model is relatively simple, you now have the structure to add more supporting models as you go.
• Balance Sheet: This one is not as obvious as you think. Many service companies do not really pay a lot of attention to their balance sheet, when it comes to managing the business on a day to day basis. You may not need this right away. But it might be helpful to bring in the Actuals at least, from the start, to make reporting a bit more seamless. You’ll need this one if you plan on doing Cash Flow forecasting, and you’ll want it if you have a capital-intensive business.
• Headcount Sub-model: Workforce planning is generally a must, from the start. Managing our people resources is one of the most time consuming and expensive parts of any business. Generally, a simple salary/FTE model by person is desirable from the beginning.?Employee-related expenses all get summarized in your P&L model.
• Capital Expenditure Sub-model: You’ll know if you need this right away or not. For capital intensive businesses, it’s a must from day 1. For many other types of business, it may be a lower priority.
• Revenue Sub-model: Often, companies need large, complex models to fully analyze and forecast revenue. There is often a lot of detail here, including things like by Product, by Salesperson, by Region, etc. Sometimes the Revenue model is built first, especially if your business is growing quickly and very revenue-driven.?You have a lot of options here, including either keeping revenue models in other systems and importing the summary numbers at the GL Account level into your P&L model, or building a simpler Revenue Forecast, to be expanded upon later.

2.?????Identify your audience of initial users

Pinpointing whom you will be initially serving with your new model will help immensely. Understanding what your initial users will do with the system, and how it is meant to benefit them, will ensure that the capabilities they need are there in Phase 1.

How many people will be consuming reports, and on what frequency? The project’s reporting priorities will be vastly different for an FP&A model that 5 people are looking at vs. 500 people. A small number of report consumers means the reports can be more informal, and, may also mean that the report consumers learn to make their own reports directly in the system. A larger report viewing group means a more formal, vetted set of reports that are structured from the beginning, and will drive many of the requirements of the underlying model.

How many people will be inputting directly into the model? Inputs are generally forecasted items like FTE and operating expense, and ?also include things like commentary explaining variance analysis. Understanding who the data contributors are will help you tailor the model to their needs. For example, department managers frequently use the planning system to track their new hires, layoffs, merit, and other related items for all their employees. They will want information about employees at a fairly detailed level to allow them to also forecast accurately. Most forecasters want some sort of prior data, such as actuals or previous forecasts, easily available to them while they are forecasting. Understanding this ensures that we architect the planning model to accommodate user needs such as highly referential data.?And, as with report consumers, the initial implementation priorities are different with smaller vs. larger initial user counts.

3.?????Plan a Reasonable Phase 1

I’ve referred to “Phase 1” a couple of times in this article, so I thought I’d talk about what I mean by “Phase 1.”?It’s easy to get overwhelmed with everything you’d like included in your FP&A model. Driver-based calculations, ability to analyze the business from multiple angles, sharing key information with your colleagues, and including business logic from your end-users all vie for attention, and are all important.

It'll help you keep your cool to remember that Phase 1 doesn’t have to be everything you want the final model to be. It’s a starting point, not an ending point. Here are some things to keep in mind to keep your initial implementation manageable, yet also worthwhile:

• Address a few key pain points that will make your Phase 1 a “win” as an internal project, and provide a clear foundation. For example, having your Actuals and Forecast data even just at the General Ledger level (IS, BS, etc.) will be a great start to getting out of spreadsheets.
• Make sure your initial launch has production-worthy deliverables. You want something that end users can start using right way, for report consumption, forecast inputs, variance analysis, or some other critical business activity.
• Keep it simple. It’s tempting to build in a lot of calculations, but you don’t want to lock down the model too early. Remember that automations, in terms of calculations, data loads, and reporting, can continue to be added to your model overtime.

I like to shoot for something that can be completed in about a 3-4 month timeline, as well, so that your team has a relevant model to work with within a reasonable timeframe.

Transitioning from Excel to a system-built FP&A model offers significant advantages for companies seeking a streamlined and error-free planning process. By adopting a robust planning tool like IBM Planning Analytics, businesses can establish a single version of the truth, reduce spreadsheet errors, and facilitate easy data sharing across departments. When considering the modules to prioritize during the transition, companies should align their choices with their specific business phase and objectives, whether it's revenue growth, cost reduction, or capital management. Moreover, a successful Phase 1 implementation is essential, focusing on addressing key pain points and delivering production-worthy deliverables, all while keeping the model flexible for future enhancements. Engaging closely with end-users throughout the roll-out process ensures a smooth and successful move from a spreadsheet application to a collaborative, system-based planning model.

For those of you who have already been through this, what words of wisdom do you have to offer your peers in other companies?