The False Confidence of a Retail Expansion Forecast

By Basem Fawzy

A convincing sales forecast can still support a poor expansion decision when its uncertainty, displaced sales, and investment commitments remain unexplored.

A retail expansion forecast earns confidence when it survives scrutiny of the decision it supports. A precise sales number alone cannot establish whether another store will add value to the business. Leaders commissioning retail analytics consulting should ask what the forecast measures, how it has been tested and which assumptions could reverse the recommendation.

The example below follows Maple Row Homewares, a fictional retailer selling home furnishings and kitchenware, considering another location. Its annual figures are illustrative assumptions in euros, not observed results or industry benchmarks. They expose a gap between forecasting store sales and justifying expansion.

What You Will Learn

• Why can a positive new-store result conceal a negative group contribution?
• How should forecast uncertainty affect an expansion decision?
• Which assumptions deserve further evidence before committing capital?

What Does The Forecast Actually Predict?

Start by naming the forecast’s target. Annual net sales at a new store are different from additional sales for the whole business. Some customers may move purchases from an existing location. The new store records those transactions, while the group gains only the change in their economics. A forecast can be accurate about the first measure and unhelpful about the second.

Maple Row’s base case assumes €4 million in new-store net sales and a 30% contribution margin after merchandise and other variable costs. New fixed operating costs are €1 million a year. The location therefore appears to retain €200,000 after those costs. But €800,000 of its sales would otherwise have occurred at existing stores, where those purchases also contribute 30% before fixed costs. Losing that €240,000 leaves the group’s annual incremental operating contribution at negative €40,000.

Existing fixed costs remain unchanged in this example. Deducting displaced sales without recognising their avoided variable costs would exaggerate the loss. A data analytics brief should define which variable costs the margin includes and use the margin earned on the displaced purchases, rather than an unrelated chain average. Retail Analytics for Multi Store Profitability explains why documenting a cost boundary matters before comparing locations. Here, the board needs the additional group contribution, rather than a new-store revenue ranking.

Figure 1. Maple Row’s illustrative annual inputs and results separate store performance from the group operating increment. Values are euros.

Which Assumptions Could Reverse The Decision?

The base case is already below operating break-even. Holding its margins, €800,000 transfer estimate and fixed costs unchanged, new-store net sales would need to reach approximately €4.133 million to bring the operating increment to zero. That is a conditional threshold, not a demand forecast. If transferred sales increase with new-store sales, the threshold changes; the assumption about customer movement must travel with the number.

A second view holds new-store sales at €4 million. Transferred sales above approximately €667,000 would make the operating increment negative under the stated margins and costs. The current transfer estimate exceeds that boundary. This tells the team which estimate deserves attention before it spends more time polishing the headline sales forecast.

The UK Treasury’s Green Book^[1] uses sensitivity analysis and switching values to examine when an appraisal changes direction. That public-sector framework offers a useful analytical principle here; its social discount rates and generic adjustments are not retail investment benchmarks. The relevant question is how far an assumption can move before this specific proposal fails the business’s own criteria.

Require every scenario to carry its assumptions and contribution bridge when commissioning Power BI consulting for retail. A switchable sales input with a hidden transfer assumption would invite a misleading comparison.

Figure 2. The transfer threshold holds new-store sales, both margins and new fixed costs at the base assumptions. It is not a demand forecast.

Has The Forecast Been Tested At The Right Horizon?

Ask the analyst to recreate earlier forecasts using only information available when each decision was made. Compare subsequent outcomes over a horizon similar to the proposed commitment. A lease justified by annual demand needs evidence beyond predicting next week’s trading. The test should resemble the question being put to the board.

Forecasting: Principles and Practice^[2] describes rolling-origin evaluation, where each forecast uses observations from before its test period. It also allows assessment over multiple steps ahead. This avoids using later information to make a historical forecast look stronger than it was. A seasonal baseline provides a useful comparison before assuming that a more complex method earns its additional maintenance cost.

Select comparable openings with a documented rule, then review them separately. Include disappointing launches and closures where the records permit; a comparison based only on surviving stores can flatter the proposal. Separate early ramp-up from settled trading, and note differences in catchment, format and operating conditions. An existing-store forecasting test cannot by itself establish the demand that an unopened site will attract.

Preserve a dated copy of the forecast issued at each approval date in any Power BI consultancy handover. Replacing it with the latest revised forecast destroys the comparison needed to judge earlier decisions.

Does The Range Describe Uncertainty Honestly?

An upside and downside pair is a set of scenarios unless it comes from a supported probability model. Calling the endpoints a 95% range would imply unearned certainty. A model-based prediction interval needs a stated horizon and assumptions, tested against later outcomes. A calibrated sales interval still leaves margin and investment uncertainty to be addressed.

Maple Row’s illustrative downside combines €3.6 million of net sales with a 28% new-store contribution margin and €600,000 of displaced sales. Existing-store contribution remains 30%, and new fixed costs stay at €1 million. The operating increment falls to negative €172,000. Lower transferred sales soften that loss, but the weaker new-store sales and margin outweigh the benefit. Treating each change in isolation would conceal their combined effect.

Ask for visible assumptions, comparable units and a clear zero boundary when commissioning data visualization consulting. The diagram should identify scenarios as scenarios. A coloured confidence band or a green status label needs an explanation of what it represents; colour cannot supply missing evidence.

Ask whether the downside is plausible as a joint case. Heavy discounting could change both sales and margin, while a new location could affect purchases at nearby stores. Use evidence to describe those relationships rather than assuming that every favourable input can occur together.

What Evidence Justifies Committing The Capital?

Operating contribution is one part of the investment case. Maple Row’s bridge excludes opening expenditure, working capital, financing, tax and any additional central costs. Reaching zero on this measure would not establish an acceptable return or sufficient cash. Finance still needs a cash-flow appraisal over the investment’s life, with assumptions consistent with the operating forecast and the business’s actual approval criteria. Use dated investment cash flows to assess payback, and apply an appropriate discount rate when calculating present value.

Use the remaining uncertainty to choose the next investigation. Where transfers drive the decision, examine catchment overlap and purchase patterns without treating address proximity as proof of customer switching. Where the ramp-up assumption matters, compare suitable openings over the same elapsed period. A short trial may provide useful evidence, but its trading conditions and costs must be explained before extrapolating to a permanent store.

Before approving the commitment, the board should be able to establish:

• Which group-level outcome the forecast is meant to support.
• Which uncertain assumption is closest to reversing the recommendation.
• What additional evidence could change that assumption.
• Whether the full investment remains acceptable after that change.

Assign an owner and review date to the investigation. Preserve the original proposal so later reviews can compare decisions with outcomes. Use the forecast to decide what to test and what exposure the business can accept before proceeding.

Before Committing To Expansion

An expansion proposal deserves a forecast that can be questioned without losing its meaning. Maple Row’s apparently positive store result becomes a negative group increment once displaced contribution is included. The next step is to strengthen the evidence behind customer movement and complete the investment appraisal, then revisit the commitment. A useful board paper makes those unresolved questions visible before a lease is signed, while there is still room to change course.

Acknowledgements

ChatGPT assisted with drafting, structure and analytical presentation. The illustrative calculations and cited sources were checked separately during preparation.

About the Author

Basem Fawzy is the founder of Data Pivot Consulting. He is a Microsoft Certified Power BI Developer and MBA graduate in E-Business with more than 10 years of experience in business intelligence, data modeling, dashboard development and reporting automation. He has worked with more than 200 clients and regularly translates operational data into management-level reporting frameworks.

References

^[1] HM Treasury. The Green Book 2026, Chapter 6, Sensitivity analysis and switching values.

^[2] Hyndman and Athanasopoulos. Forecasting: Principles and Practice, third edition, Sections 5.5 and 5.10.