/Python & Data Analytics/Phase 6

Phase 6Days 61-72

Excel for Auditable Analysis

Use spreadsheets as controlled analytical systems

Phase Goal

Build reviewable Excel models with tables, formulas, pivots, controls, charts, and reconciliation checks.

Progress

Notes

Open the written lectures for this course before checking off the phase topics.

Projects

Festival Vendor Settlement Workbook

Day 61: Workbook Structure & Controls

Build the first-principles mental model for Raw, reference, calculation, output, and control sheets; name the question it answers and the unit of analysisPredict and trace a tiny example of Raw, reference, calculation, output, and control sheets before inspecting the resultApply Raw, reference, calculation, output, and control sheets to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Raw, reference, calculation, output, and control sheets; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Excel Tables and structured references; name the question it answers and the unit of analysisPredict and trace a tiny example of Excel Tables and structured references before inspecting the resultApply Excel Tables and structured references to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Excel Tables and structured references; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Data validation; name the question it answers and the unit of analysisPredict and trace a tiny example of Data validation before inspecting the resultApply Data validation to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Data validation; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Merged cells, hidden constants, and manual-format failures; name the question it answers and the unit of analysisPredict and trace a tiny example of Merged cells, hidden constants, and manual-format failures before inspecting the resultApply Merged cells, hidden constants, and manual-format failures to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Merged cells, hidden constants, and manual-format failures; diagnose the visible failure and perform the corrective check

Retrieve one recent mechanism and one older prerequisite before consulting notesComplete a guided example, then reconstruct its critical path from a blank artifactSolve a changed task that requires choosing among already-taught toolsRecord the decision changed, failure tested, help used, and adaptive 1/3/7/14/30-day review cue

Day 62: References & Formula Reasoning

Build the first-principles mental model for Relative, absolute, and mixed references; name the question it answers and the unit of analysisPredict and trace a tiny example of Relative, absolute, and mixed references before inspecting the resultApply Relative, absolute, and mixed references to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Relative, absolute, and mixed references; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Formula evaluation and dependency tracing; name the question it answers and the unit of analysisPredict and trace a tiny example of Formula evaluation and dependency tracing before inspecting the resultApply Formula evaluation and dependency tracing to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Formula evaluation and dependency tracing; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Arithmetic, units, and parentheses; name the question it answers and the unit of analysisPredict and trace a tiny example of Arithmetic, units, and parentheses before inspecting the resultApply Arithmetic, units, and parentheses to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Arithmetic, units, and parentheses; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Copied-reference drift and text numbers; name the question it answers and the unit of analysisPredict and trace a tiny example of Copied-reference drift and text numbers before inspecting the resultApply Copied-reference drift and text numbers to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Copied-reference drift and text numbers; diagnose the visible failure and perform the corrective check

Day 63: Logical & Error-Aware Formulas

Build the first-principles mental model for IF, IFS, AND, OR, and ordered rules; name the question it answers and the unit of analysisPredict and trace a tiny example of IF, IFS, AND, OR, and ordered rules before inspecting the resultApply IF, IFS, AND, OR, and ordered rules to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind IF, IFS, AND, OR, and ordered rules; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for IFERROR after root-cause diagnosis; name the question it answers and the unit of analysisPredict and trace a tiny example of IFERROR after root-cause diagnosis before inspecting the resultApply IFERROR after root-cause diagnosis to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind IFERROR after root-cause diagnosis; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for IS functions and validation flags; name the question it answers and the unit of analysisPredict and trace a tiny example of IS functions and validation flags before inspecting the resultApply IS functions and validation flags to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind IS functions and validation flags; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Nested logic and blank-versus-zero; name the question it answers and the unit of analysisPredict and trace a tiny example of Nested logic and blank-versus-zero before inspecting the resultApply Nested logic and blank-versus-zero to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Nested logic and blank-versus-zero; diagnose the visible failure and perform the corrective check

Day 64: Conditional Aggregation

Build the first-principles mental model for SUMIF/SUMIFS; name the question it answers and the unit of analysisPredict and trace a tiny example of SUMIF/SUMIFS before inspecting the resultApply SUMIF/SUMIFS to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind SUMIF/SUMIFS; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for COUNTIF/COUNTIFS; name the question it answers and the unit of analysisPredict and trace a tiny example of COUNTIF/COUNTIFS before inspecting the resultApply COUNTIF/COUNTIFS to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind COUNTIF/COUNTIFS; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for AVERAGEIFS and denominators; name the question it answers and the unit of analysisPredict and trace a tiny example of AVERAGEIFS and denominators before inspecting the resultApply AVERAGEIFS and denominators to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind AVERAGEIFS and denominators; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Criteria, mismatched ranges, and exclusions; name the question it answers and the unit of analysisPredict and trace a tiny example of Criteria, mismatched ranges, and exclusions before inspecting the resultApply Criteria, mismatched ranges, and exclusions to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Criteria, mismatched ranges, and exclusions; diagnose the visible failure and perform the corrective check

Day 65: Lookups & Reconciliation

Build the first-principles mental model for XLOOKUP exact matching; name the question it answers and the unit of analysisPredict and trace a tiny example of XLOOKUP exact matching before inspecting the resultApply XLOOKUP exact matching to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind XLOOKUP exact matching; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for INDEX/MATCH mental model; name the question it answers and the unit of analysisPredict and trace a tiny example of INDEX/MATCH mental model before inspecting the resultApply INDEX/MATCH mental model to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind INDEX/MATCH mental model; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Multi-key helper strategies; name the question it answers and the unit of analysisPredict and trace a tiny example of Multi-key helper strategies before inspecting the resultApply Multi-key helper strategies to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Multi-key helper strategies; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Approximate matches and duplicate keys; name the question it answers and the unit of analysisPredict and trace a tiny example of Approximate matches and duplicate keys before inspecting the resultApply Approximate matches and duplicate keys to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Approximate matches and duplicate keys; diagnose the visible failure and perform the corrective check

Day 66: Text, Dates & Dynamic Arrays

Build the first-principles mental model for TRIM, CLEAN, SUBSTITUTE, and TEXT; name the question it answers and the unit of analysisPredict and trace a tiny example of TRIM, CLEAN, SUBSTITUTE, and TEXT before inspecting the resultApply TRIM, CLEAN, SUBSTITUTE, and TEXT to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind TRIM, CLEAN, SUBSTITUTE, and TEXT; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for DATE, EOMONTH, and WORKDAY; name the question it answers and the unit of analysisPredict and trace a tiny example of DATE, EOMONTH, and WORKDAY before inspecting the resultApply DATE, EOMONTH, and WORKDAY to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind DATE, EOMONTH, and WORKDAY; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for FILTER, SORT, UNIQUE, and spills; name the question it answers and the unit of analysisPredict and trace a tiny example of FILTER, SORT, UNIQUE, and spills before inspecting the resultApply FILTER, SORT, UNIQUE, and spills to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind FILTER, SORT, UNIQUE, and spills; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Locale dates and blocked spills; name the question it answers and the unit of analysisPredict and trace a tiny example of Locale dates and blocked spills before inspecting the resultApply Locale dates and blocked spills to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Locale dates and blocked spills; diagnose the visible failure and perform the corrective check

Day 67: PivotTables & PivotCharts

Build the first-principles mental model for Pivot grain and fields; name the question it answers and the unit of analysisPredict and trace a tiny example of Pivot grain and fields before inspecting the resultApply Pivot grain and fields to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Pivot grain and fields; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Aggregation and show-values-as; name the question it answers and the unit of analysisPredict and trace a tiny example of Aggregation and show-values-as before inspecting the resultApply Aggregation and show-values-as to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Aggregation and show-values-as; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Refresh and source growth; name the question it answers and the unit of analysisPredict and trace a tiny example of Refresh and source growth before inspecting the resultApply Refresh and source growth to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Refresh and source growth; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Stale cache and double counting; name the question it answers and the unit of analysisPredict and trace a tiny example of Stale cache and double counting before inspecting the resultApply Stale cache and double counting to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Stale cache and double counting; diagnose the visible failure and perform the corrective check

Day 68: Power Query Foundations

Build the first-principles mental model for Connect-transform-load; name the question it answers and the unit of analysisPredict and trace a tiny example of Connect-transform-load before inspecting the resultApply Connect-transform-load to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Connect-transform-load; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Applied steps and types; name the question it answers and the unit of analysisPredict and trace a tiny example of Applied steps and types before inspecting the resultApply Applied steps and types to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Applied steps and types; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Append versus merge; name the question it answers and the unit of analysisPredict and trace a tiny example of Append versus merge before inspecting the resultApply Append versus merge to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Append versus merge; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Step order and source-path failures; name the question it answers and the unit of analysisPredict and trace a tiny example of Step order and source-path failures before inspecting the resultApply Step order and source-path failures to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Step order and source-path failures; diagnose the visible failure and perform the corrective check

Day 69: Power Query Reshaping

Build the first-principles mental model for Unpivot to tidy records; name the question it answers and the unit of analysisPredict and trace a tiny example of Unpivot to tidy records before inspecting the resultApply Unpivot to tidy records to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Unpivot to tidy records; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Group, pivot, merge, and custom columns; name the question it answers and the unit of analysisPredict and trace a tiny example of Group, pivot, merge, and custom columns before inspecting the resultApply Group, pivot, merge, and custom columns to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Group, pivot, merge, and custom columns; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Folder combine and schema checks; name the question it answers and the unit of analysisPredict and trace a tiny example of Folder combine and schema checks before inspecting the resultApply Folder combine and schema checks to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Folder combine and schema checks; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Parameters and unexpected columns; name the question it answers and the unit of analysisPredict and trace a tiny example of Parameters and unexpected columns before inspecting the resultApply Parameters and unexpected columns to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Parameters and unexpected columns; diagnose the visible failure and perform the corrective check

Day 70: Excel Charts & Decision Layout

Build the first-principles mental model for Question-led chart choice; name the question it answers and the unit of analysisPredict and trace a tiny example of Question-led chart choice before inspecting the resultApply Question-led chart choice to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Question-led chart choice; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Direct labels and reference lines; name the question it answers and the unit of analysisPredict and trace a tiny example of Direct labels and reference lines before inspecting the resultApply Direct labels and reference lines to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Direct labels and reference lines; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Dashboard layout and reading order; name the question it answers and the unit of analysisPredict and trace a tiny example of Dashboard layout and reading order before inspecting the resultApply Dashboard layout and reading order to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Dashboard layout and reading order; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for 3D, dual axes, and chartjunk; name the question it answers and the unit of analysisPredict and trace a tiny example of 3D, dual axes, and chartjunk before inspecting the resultApply 3D, dual axes, and chartjunk to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind 3D, dual axes, and chartjunk; diagnose the visible failure and perform the corrective check

Day 71: Spreadsheet Audit & Protection

Build the first-principles mental model for Control totals and exception flags; name the question it answers and the unit of analysisPredict and trace a tiny example of Control totals and exception flags before inspecting the resultApply Control totals and exception flags to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Control totals and exception flags; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Precedent and consistency scans; name the question it answers and the unit of analysisPredict and trace a tiny example of Precedent and consistency scans before inspecting the resultApply Precedent and consistency scans to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Precedent and consistency scans; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Protected inputs and version notes; name the question it answers and the unit of analysisPredict and trace a tiny example of Protected inputs and version notes before inspecting the resultApply Protected inputs and version notes to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Protected inputs and version notes; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Overrides, hidden sheets, and undocumented macros; name the question it answers and the unit of analysisPredict and trace a tiny example of Overrides, hidden sheets, and undocumented macros before inspecting the resultApply Overrides, hidden sheets, and undocumented macros to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Overrides, hidden sheets, and undocumented macros; diagnose the visible failure and perform the corrective check

Day 72: Phase Project — Vendor Settlement

Build the first-principles mental model for Contract, sales, fee, refund, and payment design; name the question it answers and the unit of analysisPredict and trace a tiny example of Contract, sales, fee, refund, and payment design before inspecting the resultApply Contract, sales, fee, refund, and payment design to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Contract, sales, fee, refund, and payment design; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Power Query ingestion and settlement formulas; name the question it answers and the unit of analysisPredict and trace a tiny example of Power Query ingestion and settlement formulas before inspecting the resultApply Power Query ingestion and settlement formulas to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Power Query ingestion and settlement formulas; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Reconciliation and exception workflow; name the question it answers and the unit of analysisPredict and trace a tiny example of Reconciliation and exception workflow before inspecting the resultApply Reconciliation and exception workflow to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Reconciliation and exception workflow; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Approval dashboard and changed-rate transfer; name the question it answers and the unit of analysisPredict and trace a tiny example of Approval dashboard and changed-rate transfer before inspecting the resultApply Approval dashboard and changed-rate transfer to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Approval dashboard and changed-rate transfer; diagnose the visible failure and perform the corrective check

Deliver Festival Vendor Settlement Workbook for a nonprofit festival finance manager; support the decision: which vendor settlements can be approved and which require investigation?Reconstruct the critical workflow without the example open and disclose help usedFind and repair a planted data, logic, or communication failure with evidenceRespond to a changed requirement without being told which technique to useState limitations, non-goals, uncertainty, and what evidence would change the recommendation

Capstone

Festival Vendor Settlement Workbook

Build for a nonprofit festival finance manager. The result must support this decision: which vendor settlements can be approved and which require investigation? Include reproducible inputs, validation evidence, one tested failure, a changed requirement, and an explicit non-goal.

Reproducible source-to-output workflow
Documented quality checks and assumptions
Decision-ready output for a named user
Independent reconstruction and transfer evidence

Phase Complete!

After this phase, you'll be able to:

Workbook Structure & Controls
References & Formula Reasoning
Logical & Error-Aware Formulas
Conditional Aggregation
Lookups & Reconciliation
Text, Dates & Dynamic Arrays
PivotTables & PivotCharts
Power Query Foundations

Independently deliver Festival Vendor Settlement Workbook, diagnose a planted failure, explain the decision logic, and transfer the workflow to a changed requirement.

Back to all phases

Phase Goal

Day 61: Workbook Structure & Controls

Raw, reference, calculation, output, and control sheets

Excel Tables and structured references

Data validation

Merged cells, hidden constants, and manual-format failures

Practice, retrieval, and analyst judgment

Day 62: References & Formula Reasoning

Relative, absolute, and mixed references

Formula evaluation and dependency tracing

Arithmetic, units, and parentheses

Copied-reference drift and text numbers

Practice, retrieval, and analyst judgment

Day 63: Logical & Error-Aware Formulas

IF, IFS, AND, OR, and ordered rules

IFERROR after root-cause diagnosis

IS functions and validation flags

Nested logic and blank-versus-zero

Practice, retrieval, and analyst judgment

Day 64: Conditional Aggregation

SUMIF/SUMIFS

COUNTIF/COUNTIFS

AVERAGEIFS and denominators

Criteria, mismatched ranges, and exclusions

Practice, retrieval, and analyst judgment

Day 65: Lookups & Reconciliation

XLOOKUP exact matching

INDEX/MATCH mental model

Multi-key helper strategies

Approximate matches and duplicate keys

Practice, retrieval, and analyst judgment

Day 66: Text, Dates & Dynamic Arrays

TRIM, CLEAN, SUBSTITUTE, and TEXT

DATE, EOMONTH, and WORKDAY

FILTER, SORT, UNIQUE, and spills

Locale dates and blocked spills

Practice, retrieval, and analyst judgment

Day 67: PivotTables & PivotCharts

Pivot grain and fields

Aggregation and show-values-as

Refresh and source growth

Stale cache and double counting

Practice, retrieval, and analyst judgment

Day 68: Power Query Foundations

Connect-transform-load

Applied steps and types

Append versus merge

Step order and source-path failures

Practice, retrieval, and analyst judgment

Day 69: Power Query Reshaping

Unpivot to tidy records

Group, pivot, merge, and custom columns

Folder combine and schema checks

Parameters and unexpected columns

Practice, retrieval, and analyst judgment

Day 70: Excel Charts & Decision Layout

Question-led chart choice

Direct labels and reference lines

Dashboard layout and reading order

3D, dual axes, and chartjunk

Practice, retrieval, and analyst judgment

Day 71: Spreadsheet Audit & Protection

Control totals and exception flags

Precedent and consistency scans

Protected inputs and version notes

Overrides, hidden sheets, and undocumented macros

Practice, retrieval, and analyst judgment

Day 72: Phase Project — Vendor Settlement

Contract, sales, fee, refund, and payment design

Power Query ingestion and settlement formulas

Reconciliation and exception workflow

Approval dashboard and changed-rate transfer

Phase gate: independent delivery and transfer

Festival Vendor Settlement Workbook

Phase Complete!