Design and document analytical models and spreadsheets so they are auditable, maintainable, and easy for others to review and update. Core practices include structuring workbooks with a dedicated assumptions or inputs section, clearly separating raw data, detailed calculations, and summary outputs or key performance indicators, and applying consistent formatting, headers, and naming conventions. Avoid hard coded numbers by centralizing inputs, using named ranges and descriptive cell references, and documenting complex formulas with cell comments or explanatory notes. Maintain a documentation or readme sheet that explains model purpose, layout, assumptions, how to update inputs, and known limitations. Build validation checks and error flags, modularize logic for reuse, and design for scalability across larger data sets or additional time periods. Be prepared to explain sensitivities and scenario analysis, demonstrate how the model supports audit and review, and describe processes for versioning and change tracking.
MediumTechnical
65 practiced
Given raw transactional CSV files with inconsistent date formats, nulls, and duplicated rows, outline a Power Query ETL process to produce a clean staging table suitable for modeling and downstream dashboards. Explain step order, transformations (type conversion, deduplication, trimming, date normalization), query folding considerations, and how you'd document each transformation step for auditability.
HardTechnical
120 practiced
How would you produce an audit-ready calculation lineage report that traces each KPI back to the originating raw fields and transformation steps (including SQL views, Power Query steps, and Excel formulas)? Describe the tools and extraction techniques you would use, the desired output format, and the maintenance approach to keep lineage up-to-date.
EasyTechnical
80 practiced
List and explain the typical components of an 'Assumptions & Inputs' sheet for a sales forecasting model. Describe how you'd present inputs (parameter tables, named cells, dropdowns), how you'd group and format them for reviewers, how you'd document input sources and update cadence, and how inputs should be exposed to business users while minimizing risk of accidental edits.
MediumTechnical
80 practiced
Describe how you would build a parameterized template model to support rolling forecasts up to 60 months. Cover structured Excel tables, dynamic named ranges or Table formulas for horizon expansion, parameter tables for start/end dates, and how you would expose the template to Power BI or other reporting layers.
EasyTechnical
73 practiced
What are the essential sections and minimum content you would include on a README sheet embedded in a model workbook so another analyst can review, update, or audit it? Describe purpose, high-level layout, assumptions, how to update inputs, known limitations, version history, owner and contact details, and a short test procedure to validate the workbook after edits.
Unlock Full Question Bank
Get access to hundreds of Analytical Modeling and Documentation interview questions and detailed answers.
