Merchandise Financial Planning Software: Reconciling Top Down Targets With Bottom Up Plans Without Breaking the Workbook

Why planning collapses in week three of every quarter

A reforecast is called on a Tuesday. Sales came in soft in two divisions and hot in one, a vendor pushed a delivery by three weeks, and the chief merchant wants a revised inventory position by Thursday. Your planning team opens the master workbook. It links to eleven other workbooks, three of which are open on other people's laptops. Someone dragged a formula down one row too far in the accessories tab in March and nobody has noticed since. The bottom up class plans, when summed, come in 4 percent above the divisional target, and reconciling that gap means a planner manually adjusting subclass rows until the number matches, which destroys the reasoning behind the original plan.

Meanwhile open to buy, the number that governs whether a buyer may commit money this week, is computed monthly and is understood by everyone to be approximately wrong. Buyers commit against it anyway, because the alternative is not buying. Six months later the season closes with too much inventory in the wrong classes, and the correction is markdown, which is the most expensive way a retailer discovers a planning failure.

This is not a spreadsheet skill problem. It is that merchandise financial planning is a multidimensional calculation with real concurrency requirements, and a workbook is a single dimensional grid with no version control. Four hierarchy levels by 53 weeks by a dozen measures by several plan versions is tens of millions of cells before you plan a single store. Excel does not fail because your team is careless. It fails because you are asking it to be a database, a calculation engine and a workflow tool at once.

Problem 1: top down and bottom up cannot reconcile without shared versions

Finance sets sales, margin and inventory targets by division. Planners build class and subclass plans from history, promotional calendars and buyer intent. These two exercises happen in different files with different assumptions and are reconciled by argument.

What a real planning system does: hold one hierarchy and one calendar, with plans as versions rather than files. Original plan, current plan, working plan and last year all coexist, and a working plan is promoted to current through an explicit approval that snapshots it. Reconciliation becomes a mechanical operation: spread a top down target down the hierarchy using a seeding basis you choose, or aggregate bottom up and show the variance against target at every level with the drivers visible. The planner then negotiates on the classes that carry the gap, rather than nudging cells until the total agrees. That distinction is the entire value of the category, and it is why a workbook cannot get there no matter how well built.

Problem 2: the retail calendar is not a calendar and Excel does not know that

Retailers plan on the 4-5-4 calendar, where quarters are built from four and five week months and the year periodically contains 53 weeks. That creates comparability problems every planner knows and no generic tool handles: this year's week 14 does not align with last year's week 14 after a shift, Easter moves between periods, and a 53 week year makes every year over year comparison at a period level misleading unless it is restated.

What a real planning system does: hold the calendar as data with week to period to quarter mapping per year, and make comparability an explicit choice on the report rather than an assumption. When a planner asks for last year, the system knows whether to align on calendar week or on shifted week, and it says which it used. Building this into the foundation is cheap. Retrofitting it after the plans exist is expensive, and skipping it produces plans that are quietly wrong in exactly the weeks that matter most, which are the holiday weeks.

Problem 3: open to buy is a derived number and everybody treats it as a stored one

Open to buy is planned receipts less what is already on order, adjusted for in transit, actual sales against plan, markdown taken, shrink and returns. Every component moves daily. When it is computed monthly in a spreadsheet, buyers make commitments against a number that is up to four weeks stale, which is precisely how a retailer ends up over bought in a slow class and short in a hot one at the same time.

What a real planning system does: compute open to buy on demand from live purchase order and sales data, at whatever level the buyer works at, with the components visible so a buyer can see why it moved. It should also model a commitment before it is placed, so a buyer can test a purchase order against the position rather than discovering the impact afterwards. When open to buy becomes trustworthy, the behaviour that changes is not planning, it is buying, and that is where the money is.

Problem 4: the plan is worthless if it does not leave the planning system

An approved plan has to become targets in the merchandising system, receipt expectations for the supply chain, and a budget line the finance team can reconcile against the general ledger. In most retailers this handoff is a CSV and a phone call, so the plan of record diverges from the operational systems within weeks.

What a real planning system does: write approved versions back to the merchandising system and post budget lines to the general ledger with the version and approval recorded, so a finance question about a variance can be traced to the specific plan version and the approver. This is also what makes an internal audit straightforward, and it is the part most builds underestimate because it is unglamorous integration rather than visible planning functionality.

Where AI helps and where it is theatre

The genuinely useful application is seeding. A new season plan starts from history adjusted for known drivers, and a model that seeds at subclass and week level from prior years, promotional calendars and store openings saves planners real time and produces a better starting point than a percentage uplift applied uniformly. The second useful application is variance detection: flagging the classes where actuals have diverged from plan in a way that is statistically unusual rather than just numerically large, so the reforecast conversation starts with the right ten classes.

What does not work is asking a model to make the merchandising judgement. A decision to back a new brand hard, exit a category or protect a vendor relationship is strategy, and the model has no access to the reasoning. Systems that hide the seeding assumptions behind an automated plan get abandoned by planners within two seasons, because a planner who cannot explain a number to a chief merchant will not defend it.

What this costs and how long it takes

Across the 2,000 plus projects Digital Heroes has delivered, this category sits at the higher end of enterprise work. A focused first release covering the hierarchy and calendar foundation, plan versioning, top down and bottom up reconciliation, and open to buy computed from live data runs $90,000 to $200,000 and ships in 16 to 24 weeks. A full platform adding markdown and margin planning, receipt flow and inventory projection, multi banner consolidation, write back to merchandising and general ledger posting runs $250,000 to $600,000 phased over 9 to 15 months.

What drives cost up in merchandise planning specifically: the number of banners and whether they share a hierarchy, because consolidation across differing hierarchies is a modelling problem rather than a report. Calculation performance at your data volume, since a planning grid that takes 40 seconds to recalculate will not be used and making it fast is real engineering. The state of your history, because plans seeded from data with unreconciled hierarchy restatements are worse than useless. And write back integration, where a merchandising system that was not designed to accept a plan needs careful work.

What keeps cost down: one banner, one division and weekly rather than daily granularity for release one. Planners will tell you they need daily. At plan level they almost never do.

Build versus buy, and this category has strong buy options

Buy, and take this seriously, because merchandise planning is one of the few areas where the packaged market is genuinely strong. Oracle Retail Merchandise Financial Planning is deep and proven if your process fits its model and you can carry the implementation. Blue Yonder and o9 Solutions are credible at enterprise scale. RELEX Solutions is excellent where grocery forecasting and replenishment are the centre of the problem. Anaplan deserves a specific mention: it is a modelling platform that will do more or less exactly what you build in it, which makes it a genuine alternative to a bespoke build, with the trade off that your logic lives inside a licensing model priced on workspace and users rather than on your own infrastructure.

The fair criticism of the packaged options is not capability, it is fit and cost of change. Each encodes a view of how a retailer should plan, and if your merchandise hierarchy, promotional structure or ownership model differs, you configure around it for a year and then live with the compromise. Implementation cost frequently exceeds licence cost, and changing a calculation later means a change request rather than a sprint.

Build when two or more of these are true. Your planning process is a genuine competitive difference rather than a standard retail cycle. You already have a data platform holding clean sales and inventory history, which removes the largest cost from a build. You operate banners whose hierarchies genuinely differ. You have implemented a packaged planning system before and abandoned it because adoption failed. Or your planning team is small and expert, and needs a tool that matches how they already think rather than retraining onto a vendor's model.

How to choose a developer for merchandise planning software

Ask them how they will handle a 53 week year and a shifted week comparison. If they have not encountered it, they have not built retail planning and your holiday comparisons will be wrong in year two.

Ask what happens when two planners edit overlapping parts of the hierarchy at once. A developer who has built planning tools will talk about locking granularity, working versions and conflict resolution. A developer who says the database handles it has not thought about a Tuesday reforecast with nine people in the system.

Ask about calculation performance at your cell counts specifically, with your hierarchy depth and week count, and ask what their target recalculation time is. Under a few seconds is the threshold where planners keep using a tool.

Ask who owns the code and get it in writing before kickoff, including the repository and the cloud accounts. At Digital Heroes the client owns it from the first commit. In a system that holds your buying budget, being unable to change a calculation without a vendor's consent is the exact dependency you were trying to escape.