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.
HardTechnical
0 practiced
A VBA macro in a shared workbook has overwritten and corrupted the master customer file. You're the on-call BI analyst. Describe your incident response: immediate containment, rollback strategy (snapshots/backups), data recovery steps, root-cause analysis of the macro, stakeholder communication plan, and long-term prevention measures (code reviews, permissions, staging/sandboxing).
MediumTechnical
0 practiced
You're building a control sheet that aggregates critical KPIs and links to detailed calculation cells across multiple sheets. Describe an approach for layout, methods to create robust links (direct references, named ranges, table references, avoiding volatile functions), and strategies to minimize broken links when source sheets are refactored or renamed.
EasyTechnical
0 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.
MediumSystem Design
0 practiced
Describe architecture and implementation options for adding automated validation checks that run during workbook open or dataset refresh, flag mismatches, and prevent report distribution until resolved. Cover approaches for Excel (VBA, Office Scripts), Power BI (measures, dataflows, pre/post refresh tests), and Looker (scheduled explores, data tests), including where to host check results and how to alert owners.
HardSystem Design
0 practiced
Design a scalable architecture to transition an analyst-developed Excel forecasting model into a managed BI pipeline. Describe components (data warehouse, ETL tool like Power Query/dbt, staging & mart layers, authoritative dataset such as Power BI dataset), how to maintain documentation/versioning, and trade-offs of moving logic to the warehouse vs keeping some logic in the BI layer.
Unlock Full Question Bank
Get access to hundreds of Analytical Modeling and Documentation interview questions and detailed answers.
