Budgeting beyond the spreadsheet: how to move without losing control

Budgeting often starts in a spreadsheet. The file grows until the budget is one sheet that is hard to read and slow to recalculate, and the company decides to move it into a system. The move goes well in steps, and the first step is not about software.

Why the sheet cannot be moved as it is

A budget sheet that has grown for years holds items in the rows and indicators, periods and balances in the columns. A sheet of 500 rows and 200 columns has 100,000 cells. In most of them the row and the column are unrelated, and no value makes sense there.

Such a sheet is not one budget but a store of plan data, read through filters and summary tables. Copied into a system unchanged, it is hard to set up and slow to calculate. The first step is to split it into several simple tables, each with one purpose.

Items, indicators and dimensions

A spreadsheet mixes three things that a budget model keeps apart.

• A budget item is turnover for a period: sales, payments received, purchases.
• An indicator is a balance at a date: receivables, cash, stock.
• An analytic dimension is the detail of an item or an indicator: customer, product group, department.

Items change indicators: sales increase receivables, payments reduce them. A table with sales in a row and the opening balance of receivables in a column produces a cell that can hold nothing.

For doubtful lines there is a simple test. Lines that affect the indicators in the same way are values of one dimension. Lines that affect them differently are separate items.

The model first or the system first

A company with a working budget model and a written process looks for a system that covers them, and its forms and regulation serve as the specification. A company without a model expects the system to bring a method, which saves time when the built-in model fits and costs more when it does not.

Either choice should be deliberate, and two shortcuts are better avoided. One is to carry the old forms over unchanged: only a small part of the system is then used. The other is to plan in a copy of the accounting database, which demands details that planning does not need and has no scenarios.

How long a cycle takes

A planning cycle runs from the first draft to the end of the plan against actual analysis. In a large company preparing the budgets can take 3 to 6 months, and the more detailed a budget is, the less likely its figures are to be met. A common answer is rolling planning with a short horizon, for example a quarter by months.

Under any approach the dates belong in a budget regulation: who drafts and approves each budget, in what order and in how many days. As an illustration, a quarterly cycle can start 17 working days before the quarter, with 5 days for the functional budgets, 1 day for each approval and 5 days after each month for comparing plan and actual figures. In 1C:ERP such a regulation becomes tasks with deadlines for each participant.

A plan for the first cycle

Five actions keep the plan under control while the sheet is replaced.

• Split the big sheet into simple tables and mark every line as an item, an indicator or a dimension.
• Write the regulation with dates, participants and the number of days for each step.
• Keep spreadsheet input and import the files for the first cycle: the plan is in the system for control and reports. Enter plans in the system from the next period.
• If plan formulas need past actual figures that the new system does not hold, enter the first plan explicitly or load those figures in condensed form.
• Name an employee who sets the requirements and accepts the result.