Excel Models for Merchandise Financial Planning and Open-to-Buy
Merchandise financial planning in Excel gives fashion and retail planners one workbook for the open-to-buy, the buy plan and margin by month and market, instead of a new copy of the file every season. The model is structured, checked and documented, so the team can maintain it without the person who built it.
Merchandise Planning Problems These Excel Models Solve
A Buy Plan Workbook Only Its Author Understands
Hidden sheets, hard-coded numbers and links to files that no longer exist.
An Open-to-Buy Budget That Drifts Mid-Season
Receipts and sales are updated in different places, so the budget is never current.
Planning Scenarios Compared by Copying the File
Every what-if becomes a new version, and nobody knows which one was approved.
Margin Calculated Differently in Every Planning Tab
Cost, discounts and currency are handled inconsistently from sheet to sheet.
What the Excel Merchandise Financial Planning Work Includes
Four parts, delivered as one workbook and one short guide.
Open-to-Buy Sheet with Monthly Phasing
Planned sales, markdowns, stock and receipts by month, category and market.
Buy Plan and Margin Calculation by Market
Cost, selling price, discounts and currency handled by one consistent logic.
Scenario Comparison Without File Copies
Switchable assumptions and a side-by-side summary of the options.
Built-In Checks and a Maintenance Guide
Three checks that flag drift, and a guide for the next owner of the model.
Results of Excel Merchandise Planning Models
workbook instead of seasonal copies
time to update the open-to-buy
automatic checks against drift
Excel and Power Query Tools Used in Planning Models
Excel Tables, Dynamic Arrays and Named Ranges
Formulas that can be read and audited by the next planner.
Power Query Imports of Sales and Stock
Actuals loaded from exports without copy-paste.
Power BI for Plan vs Actual Monitoring
An optional weekly view of the plan against actual sales.
Related Case Study: Rebuilding the Womenswear Range Plan for a European Fashion Distributor
Rebuilding the Womenswear Range Plan for a European Fashion Distributor
Fewer options, more depth in proven sizes and colours, and a new open-to-buy plan for a womenswear distributor.
Free Retail Calculators for Margin, Sell-Through and Stock
Related Retail Analytics Articles
Excel Merchandise Financial Planning FAQ
Merchandise financial planning (MFP) sets the money side of the range: planned sales, margin, markdowns, stock and purchases by month, category and market. Its main output is the open-to-buy, the budget still available for buying. It links the buying team’s decisions to the company’s financial targets.
Many brand teams need a clear, working model before they need software, and Excel is the tool planners already know. A well-built Excel model is quick to change and easy to audit. If you move to a planning system later, it becomes the specification for that system.
Yes. I document the existing logic first and then restructure the workbook, so nothing that works today is lost. The team checks the new version against the old one before switching over.
Microsoft 365 is recommended, because dynamic arrays and Power Query make the model simpler and faster to update. Older versions can be supported with simpler formulas.
Each market has its own planning inputs, and a currency table converts everything to the reporting currency with agreed rates. Totals roll up by category and market, so head office and local teams read the same numbers.
Discuss an Excel Merchandise Planning Model with Tatsiana Bandziuk
Describe the planning file your team is afraid to touch. I will reply with how I would restructure it.
Tatsiana