New Store Capital Investment Appraisal with Macro-Free Sensitivity

Business challenge

The investment committee needed to know whether a proposed store would clear its hurdle rate, how sensitive the decision was to sales, margin and capex overruns, and to get the answer in a file that works without macros.

Outcome

Decision-makers see the base case and downside cases side by side, always in sync with the latest assumptions, in a file that opens safely where macros are blocked and can be audited formula by formula.

Overview

A capital investment appraisal for a new 3,200 m² rented-box supermarket on a 15-year lease. Sales intensity per m² and planned growth drive revenue; margin, rebates, shrink, labour, support costs, rent escalation, depreciation by asset class and working-capital days build a 15-year free cash flow. The one-page CIA summary reports IRR (28.3% in the base case), NPV at five discount rates, cash payback, return on investment at year 3 and the minimum first-year sales needed to reach the target return. How to use it: on the SiteDetails sheet, use the drop-down in cell C11 to pick the deal type: Freehold, Leasehold or Rented Box. The target return (12%, 20% or 30%) and the CIA summary update. Change the blue inputs on the Assumptions and SiteDetails sheets (sales intensity, growth, cost ratios, areas, rent escalation) and every case recalculates. On the CIA sheet, read IRR for the base case and seven sensitivities: sales ±10% and ±15%, cost of sales ±2 points and investment +10%.

Approach

Instead of a VBA loop that changed inputs and pasted each IRR, every sensitivity case has its own formula-driven cash-flow block that reads the live assumptions with that case's adjustment. Each block calculates its own IRR and the CIA page links to the results. The minimum first-year sales figure is interpolated live from those IRRs.

Capabilities

Skills

Loading the live model… usually about 10 seconds3%

Opens right here · no download · no sign-in

Screenshots