Excel is a great tool, but it is not a suitable platform for company planning. A modern planning process must be quick, flexible, and capable of responding rapidly to changes. The Covid crisis and the disruptions in supply chains due to the conflict in Ukraine have vividly demonstrated this. The economic environment can change in an instant, and to respond to it, plans must be adjusted, often more than once. Excel-based planning processes require a lot of resources for non-value-creating work such as preparing, consolidating, and verifying files and data. A planning platform allows one to focus on planning, i.e. consolidating the best knowledge, streamlining support activities, and creating a suitable working environment for planners. Let’s look at the advantages of planning software based on examples from practice:

• Sales plan of an international company (SPIC) - 5000 products, 3000 customers, and 60 salespeople around the world. The sales plan is prepared by consolidating customers and products, grouping less important customers into customer groups and products into product groups. Each salesperson needs to receive their clients and the important combinations of products with basic data derived from previous period’s sales data, such as average sales quantities and prices. The product cost prices are added in the later stages of planning based on the cost model and apply to all customers.
• Budget of a medium-sized company (MSC) - a few hundred employees, 20 departments. 100 expense accounts, 100 resources -> each department can use up to 200 planning combinations out of a potential 10,000. The budget is planned by department and resource (what is resource consumption accounting), and central functions responsible for planning costs for parts of the expenses, such as IT and personnel, coordinate with the department head to plan the expenses for all departments.
• Continuous product plan of a media company (CPP) - about 500 products, all products are planned continuously through performers. Each product/product group uses different performers and calculations - revenue can equal quantity * price or can be input-based, etc. The cost accounts for the product group change over time, as do the performers used for planning. Planning is continuous, and at any given moment, the best understanding of next year’s revenue and costs must be available

The planning process can be divided into logical steps:

Guidelines for Planning

Guidelines for planning are provided by management based on which the underlying data is prepared. The guidelines specify the expected changes in market conditions, market share, inflation and wage growth rates, etc.

Creation of Planning Model and Rules

Preparation of the planning process involves the creation of necessary forms for the users, where each user is provided with a customized form with only the relevant data fields for them. This simplifies and streamlines the data entry process. During preparation, the forms are pre-populated with data based on guidelines, rules are created for calculations based on the running conditions, and workflows are established, as needed. The result of preparation should be a tailored planning form for each user pre-populated with data, guidelines, and deadlines for conducting the planning process.

• Excel - Excel files containing necessary forms are prepared for all users.
  • SPIC - Customized files are prepared for each salesperson, which require comparison data. Each file is unique, with hundreds (or even thousands) of rows, making them unwieldy to use.
  • MSC - Individualized files are prepared for each user, with hundreds of combinations.
  • CPP - The file(s) are 500 (!) pages long, making them very difficult to manage and populate with comparison data. In the case of one file, there is a high probability of it breaking at some point.
• Planning Application - Planning rules are created to determine who can see and modify which data, calculations for planning based on the running conditions are described, and queries for pre-populating the data are established. The data is based on a unified master dataset, and generating comparison data is straightforward.
  • SPIC - Importing product-client-salesperson combinations, loading data with the application of guidelines, and creating a single planning form that displays the correct data combination and data based on the user.
  • MSC - Importing combinations of accounts, departments, and resources, loading data with the application of guidelines, and creating two planning forms: one for the account and resource, and the other for the resource and account.
  • CPP - Rules for connecting accounts and running conditions to product groups are created, determining whether a specific account/salesperson is involved in planning for a given product group. Calculation rules based on product groups. Planning forms based on product groups that display a selection of products for planning and dynamically display the correct accounts/salespeople.

The planning application requires initial setup time - the rules need formalization and the calculations need to be described. Subsequent cycles can be prepared in hours, or even minutes. In Excel solutions, preparation takes days, and implementing changes to the guidelines is a time-consuming task. Calculations in files cannot be changed at once - the choice is often between having a uniform basis (the same for everyone) or user-centric (only relevant for each individual), the former makes planning cumbersome and the latter is challenging to manage.

Planning software models are reusable and require minimal changes periodically, enabling faster planning preparation and frequent planning.

Planning

• Excel - Users enter data in Excel. Quality control must be ensured by checks in the files. The need to change checks means changes in all files.

• Planning Application - Users enter data into a centralized system based on a data warehouse. Data quality is ensured by rules and a unified master data. When entering data, users can import data from Excel. The forms are user-centric, that is, they do not display irrelevant rows or columns. The data is immediately available in reporting.

  • SPIC - When selecting a customer, each salesperson is shown the necessary product lines with pre-filled data and can adjust the quantity and/or sales price. Costs, amounts, margins, etc. are calculated in real-time centrally.
  • MSC - The department head selects an appropriate view for planning, such as a resource-account view for planning job positions, surfaces, and car costs, and plans the costs of each resource by account. The account-resource view can be used for control.
  • CPP - The person responsible for each product opens the product group planning form and keeps the throughput and amounts up-to-date.

Planning software ensures efficient data entry - users are shown only the necessary data, and if necessary, in different views.

Data Consolidation, Control, and Analysis

After users have entered their data, the next step is to consolidate and validate the data.

• Excel - Files are usually consolidated via email or a unified directory. The presence of the correct versions must be ensured. After consolidation, data must be aggregated into one file, for example, to create a complete picture. After consolidation, data quality and completeness can be verified. Time-consuming process that takes days.
• Planning Application - Planning data is available in reporting in real-time. Control reports can be continuously updated, and data verification can be ensured at the end of data entry.

Planning software eliminates the need for cumbersome data aggregation and control. This allows focus on meaningful analysis.

Process Control

It is in the nature of people (perhaps not everyone, but many) to leave tasks until the last minute. The best result is achieved through continuous planning, where everyone is accustomed to planning as part of their daily work. Planning software allows for setting short-term deadlines - for example, reviewing your plan over the course of a week or having the last week of the quarter as the planning week - and the results can be monitored in real time, with guidelines provided for the planner. Excel does not allow for checking the situation during the process, and workflows are not significantly helpful here.

• Excel - Information on deadlines and instructions for planning is sent along with the files. It is possible to control the process with an external workflow.
• Planning Application - The process can be controlled with a workflow, which can also manage the status of the data - the data is confirmed/closed at the end of a stage, for example. Additionally, it allows for checking the amount of work done in real time - who has changed how much data, what is the status compared to guidelines, and so on. In matrix planning, multiple parties can change/coordinate the same data - for example, a department head and HR manager can coordinate training costs, etc.

Continuous planning ensures the best control over the planning process - users become accustomed to the process (competence is developed), the workload is small, and activities are not postponed. The usefulness of implementing a complex workflow should always be evaluated, as it may not bring the expected benefits.

Added Value

The planning process creates value - by planning, the best action plan is drawn up, and the same level of results cannot be achieved through impulsive decision-making. Planning software should primarily create value by allowing for more frequent and efficient planning, freeing up controllers for analysis. This is especially important in a constantly changing economic environment. More frequent planning increases competence, and better planning competence leads to better plans and results.

The planning software must be cost-effective; there is no point in spending more on planning than the activity will bring back. Optime is an efficient and readily available planning software