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.
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.
MediumTechnical
0 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.
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.
MediumTechnical
0 practiced
Describe how you would implement scenario analysis supporting 'best', 'base', and 'worst' case forecasts in both Excel and Power BI. Cover how to structure parameter tables (scenario ID, multipliers, descriptive notes), how scenarios connect to calculation logic, visualization approaches (slicers, toggles, scenario matrices), and how to document assumptions so auditors can reproduce results.
MediumTechnical
0 practiced
Write a single SQL query (Postgres) to deduplicate the transactions table by keeping the latest row per (user_id, transaction_id) based on updated_at timestamp. Table schema: transactions(id PK, user_id int, transaction_id varchar, amount numeric, updated_at timestamp). Use window functions and show the approach in one statement (CTE + DELETE or SELECT for new table).
Unlock Full Question Bank
Get access to hundreds of Analytical Modeling and Documentation interview questions and detailed answers.
