inputs-calcs-outputs-design
Inputs-Calcs-Outputs Design
When to use
Use this skill when building or restructuring any Excel model that needs to be shared, audited, or maintained over time. It is specifically the right tool when a model has assumptions mixed in with formulas, when it is unclear which cells are inputs vs. calculations, or when a client or reviewer has flagged that the model is "hard to follow." It is also the starting point for any new model where the three-tab principle will be applied from scratch.
What it does
Produces the complete structural design for three distinct zones: an Inputs tab (all user-editable assumptions, clearly labeled and color-coded blue), one or more Calculations tabs (all formula logic, no hardcoded numbers, referencing only the Inputs tab and other Calc tabs), and an Outputs tab (no calculations, only references to final Calc results, formatted for communication). Includes a conventions checklist to verify compliance.
Method
-
Understand the three-tab principle. The core rule is: separate what you control (inputs) from how the model works (calculations) from what you present (outputs). This separation means: any user can change assumptions without touching formulas; any reviewer can audit the logic without hunting through merged cells; any updated version can be handed over without a briefing. It is the single most important structural discipline in financial modelling.
-
Audit the existing model for violations. If restructuring an existing model, perform an audit first. In Excel, use Ctrl+` to toggle formula view. Identify: (a) hardcoded numbers inside formulas (e.g., =A1*0.15 where 0.15 should be an input), (b) formula cells on the Inputs tab, (c) raw data mixed with calculated results on the same tab. Flag each violation before redesigning.
-
Design the Inputs tab. The Inputs tab is the only place where numbers are hardcoded. Design it with the following structure:
Header block at top: Model name, version, date, analyst name.
Assumption sections, one per analytical module. Each section has: a bold section header row with dark fill, then rows for each assumption with three columns: Assumption Label (text, left-aligned) | Value (blue fill, right-aligned, editable) | Unit or Note (text, grey, right-aligned, e.g., "% per year", "2024 USD").