Data Modelling Inside a Spreadsheet: Build Interview-Ready Excel Models That Do Not Break
A famous economics paper once influenced global austerity debates - then reviewers found a spreadsheet range had accidentally excluded some countries. The lesson was brutal: in Excel, the model is not just the numbers; it is the structure that decides which numbers are even allowed to speak.
- Spreadsheet data modelling means structuring data, assumptions, calculations and outputs so a business question can be answered reliably.
- The golden rule: separate raw data, assumptions, calculations and outputs. Never mix them casually on one sheet.
- Define the grain first - one row equals one order, one SKU-month, one store-day, or one customer?
- Use keys to connect tables: Product ID, Store ID, Customer ID, Date, City. Avoid repeated manual lookups wherever a relationship can do the job.
- Good models are transparent, traceable, flexible and auditable - not just visually neat.
- Always add checks: reconciliation, missing keys, duplicate IDs, formula consistency, hardcoded numbers and scenario outputs.
- In interviews, explain your workbook like a business system: question → data → assumptions → logic → output → controls.
Big Picture: A Spreadsheet Model Is a Small Business System
A spreadsheet model should behave like a clean factory line: raw business events enter at one end, decisions come out at the other. The danger starts when candidates treat Excel as a blank canvas instead of a governed system.
Core Explanation: The Four Layers of a Reliable Spreadsheet Model
The big idea is simple: separate what changes from what calculates, and separate both from what gets presented. That one discipline prevents most spreadsheet disasters.
For MBA interviews, think of the workbook as four layers:
The Five-Step Process to Build the Model
When you are given a business problem, do not open Excel and start typing formulas. First design the model on paper or in your head.
Model Design Choices: Flat Sheet vs Real Data Model
A beginner builds one giant sheet. A strong analyst asks: which facts belong together, and which dimensions should sit separately?
Worked Example: A Simple Store Profitability Model
Suppose you are modelling a new food outlet. The numbers below are illustrative - the skill is in the structure, not the specific values.
A weak model types these numbers into one block. A strong model keeps orders per day, operating days, AOV, variable cost percentage and fixed costs as assumptions, then lets the calculation sheet update automatically when a scenario changes.
Model Health Checks: Six Measures to Track
Spreadsheet modelling is not complete until you can prove the model is safe to use. These checks are the difference between “I made a model” and “I trust this model.”
Definitions You Should Be Able to Say in One Breath
- Spreadsheet data model: A structured workbook that connects data, assumptions and calculations to answer a business question.
- Grain: The exact level represented by one row of data.
- Key: A field used to uniquely identify or connect records across tables.
- Fact table: A table containing measurable business events such as sales, orders, costs or visits.
- Dimension table: A descriptive table that explains facts, such as product, customer, store, region or date.
- Scenario analysis: Comparing model outputs under different assumption sets such as base, upside and downside cases.
Nykaa: Modelling Omnichannel Beauty Economics in a Spreadsheet
Nykaa is a useful Indian case because its beauty business forces a model to connect SKUs, channels, inventory, margins and customer demand - exactly what spreadsheet data modelling is meant to organise.
Situation: Beauty retail in India is SKU-heavy, promotion-sensitive and channel-diverse. A business like Nykaa has to think across online demand, physical stores, private labels, inventory availability, fulfilment costs and category margins.
The modelling challenge: If you model the business as one flat sales sheet, the answer becomes fragile. A lipstick sold online, a skincare product sold in-store and a private-label item may differ in margin, fulfilment cost, inventory logic and discounting. The model needs separate tables that can still talk to each other.
The strategic move to model: Nykaa’s omnichannel approach can be represented through a clean spreadsheet architecture: sales facts by order or SKU-period, product master, channel master, inventory assumptions, margin drivers and scenario outputs. The primary driver is category and channel economics; supporting drivers include assortment depth, brand relationships, private-label mix, fulfilment discipline and customer repeat behaviour.
Outcome or lesson: The lesson is not that one formula explains Nykaa. The lesson is that a business with many SKUs and channels needs a model where facts, dimensions and assumptions are separated. That is how the spreadsheet stays flexible when the business question changes from “What were sales?” to “Which channel and category are actually profitable?”
How AI Changes Data Modelling Inside a Spreadsheet
AI does not remove the need to understand spreadsheet modelling. It raises the bar: now the analyst who wins is the one who can ask better questions, check AI-generated logic and explain the model clearly.
- Natural-language model building: Excel Copilot, ChatGPT and Claude can help draft formulas, Power Query steps, pivot logic and scenario tables from plain English. The risk is that they may create a formula that looks right but uses the wrong grain.
- Faster data cleaning and classification: AI can standardise messy product names, classify expenses, flag unusual values and suggest joins between tables. You still must verify keys, duplicates and reconciliation totals.
- AI-assisted insight generation: Tools can summarise drivers behind variance, create first-draft dashboards and generate “what changed?” explanations. The analyst’s job is to validate causality, not merely repeat the summary.
Load your workbook structure, assumptions sheet and a company annual report into NotebookLM or Claude. Ask: “What are the likely model risks, missing assumptions and interview questions for this spreadsheet?” Then manually verify every formula and business assumption before using the answer.
Interview Relevance
“Suppose you need to build an Excel model to estimate profitability for launching a new store in a city. How would you structure the spreadsheet?”
Use the phrase “I would first define the grain”. It signals that you understand modelling, not just Excel formulas.
Common Mistake
The biggest mistake is mixing raw data, assumptions, formulas and outputs in the same sheet. It costs candidates because the model becomes impossible to audit, scenario-test or explain. One-line fix: create separate tabs for raw data, assumptions, calculations, checks and dashboard before writing complex formulas.
What to Revise Next
Once your spreadsheet model is structurally sound, revise how to present it and how to protect it from errors. Move next to Charts in Excel, and When Not to Use Them, then Spreadsheet Auditing: Errors, Traceability & Version Control. The journey is: build the model, communicate the model, then audit the model.