---
skill: audit-xls
plugin: financial-analysis@claude-for-financial-services
target: 17_initiating-coverage_TEF_v2_model.xlsx
scope: model (full workbook + financial-model integrity checks)
date: 2026-05-06
total_cells_scanned: 975
formula_cells: 352
findings: 54
---

# Audit-xls report — v2 TEF model

**Model type:** DCF integrated into 3-statement-style structure
**Overall:** Major Issues — 3 critical, 32 warnings, 19 info

The audit ran the full audit-xls workflow: formula-level checks across all 10 tabs, plus model-integrity checks (BS balance, cash tie-out, IS roll-forward, DCF sanity). The model is structurally rich (10 tabs, formulas linking IS → CF → BS → DCF → Sensitivity → Valuation Summary) but has three critical integrity gaps that would prevent client distribution without remediation.

## Findings by severity

### Critical (model-integrity gaps)

| # | Sheet | Cell/Range | Issue | Fix |
|---|---|---|---|---|
| C1 | Balance_Sheet | `(structure)` | BS tab does not enforce Total Assets = Total Liabilities + Equity | Add assets line items + L+E line items + balance check formula =TA-(TL+TE) per period; BS as-is is a key-items reference, not a balanced 3-statement BS |
| C2 | Cash_Flow | `(structure)` | CF does not compute opening cash + ΔCash = closing cash, and BS Cash is hardcoded | Add 'Opening cash' and 'Closing cash' rows on CF; link BS!cash to CF!closing_cash; verify equality across all periods |
| C3 | DCF | `B5:G5` | DCF inputs FCF from Cash_Flow row 11 (company-reported FCF, which is LEVERED — net of interest/hybrids). DCF should use UNLEVERED FCF (pre-interest, post-tax) | Either (a) build unlevered FCF row in CF (= EBIT*(1-t) + D&A - capex - ΔNWC) and reference that, OR (b) discount levered FCF at cost of equity (FCFE-style). Current setup conflates the two |

### Warning (32 items)

| # | Sheet | Cell/Range | Issue | Fix |
|---|---|---|---|---|
| W1 | Revenue_Model | `H16` | Formula contains numeric literal(s): ['35120'] | formula: =G12-35120 | Replace literal with a reference to a driver/input cell |
| W2 | Income_Statement | `H10` | Formula contains numeric literal(s): ['0.215'] | formula: =-H5*0.215 | Replace literal with a reference to a driver/input cell |
| W3 | Income_Statement | `I10` | Formula contains numeric literal(s): ['0.21'] | formula: =-I5*0.21 | Replace literal with a reference to a driver/input cell |
| W4 | Income_Statement | `J10` | Formula contains numeric literal(s): ['0.205'] | formula: =-J5*0.205 | Replace literal with a reference to a driver/input cell |
| W5 | Income_Statement | `K10` | Formula contains numeric literal(s): ['0.2'] | formula: =-K5*0.2 | Replace literal with a reference to a driver/input cell |
| W6 | Income_Statement | `L10` | Formula contains numeric literal(s): ['0.195'] | formula: =-L5*0.195 | Replace literal with a reference to a driver/input cell |
| W7 | Income_Statement | `B15` | Formula contains numeric literal(s): ['0.22'] | formula: =-B14*0.22 | Replace literal with a reference to a driver/input cell |
| W8 | Income_Statement | `C15` | Formula contains numeric literal(s): ['0.22'] | formula: =-C14*0.22 | Replace literal with a reference to a driver/input cell |
| W9 | Income_Statement | `D15` | Formula contains numeric literal(s): ['0.22'] | formula: =-D14*0.22 | Replace literal with a reference to a driver/input cell |
| W10 | Income_Statement | `E15` | Formula contains numeric literal(s): ['0.22'] | formula: =-E14*0.22 | Replace literal with a reference to a driver/input cell |
| W11 | Income_Statement | `F15` | Formula contains numeric literal(s): ['0.22'] | formula: =-F14*0.22 | Replace literal with a reference to a driver/input cell |
| W12 | Income_Statement | `G15` | Formula contains numeric literal(s): ['0.22'] | formula: =-G14*0.22 | Replace literal with a reference to a driver/input cell |
| W13 | Income_Statement | `H15` | Formula contains numeric literal(s): ['0.22'] | formula: =-H14*0.22 | Replace literal with a reference to a driver/input cell |
| W14 | Income_Statement | `I15` | Formula contains numeric literal(s): ['0.22'] | formula: =-I14*0.22 | Replace literal with a reference to a driver/input cell |
| W15 | Income_Statement | `J15` | Formula contains numeric literal(s): ['0.22'] | formula: =-J14*0.22 | Replace literal with a reference to a driver/input cell |
| W16 | Income_Statement | `K15` | Formula contains numeric literal(s): ['0.22'] | formula: =-K14*0.22 | Replace literal with a reference to a driver/input cell |
| W17 | Income_Statement | `L15` | Formula contains numeric literal(s): ['0.22'] | formula: =-L14*0.22 | Replace literal with a reference to a driver/input cell |
| W18 | Scenarios | `B11` | Formula contains numeric literal(s): ['1500'] | formula: =B8-B10-1500 | Replace literal with a reference to a driver/input cell |
| W19 | Scenarios | `C11` | Formula contains numeric literal(s): ['1500'] | formula: =C8-C10-1500 | Replace literal with a reference to a driver/input cell |
| W20 | Scenarios | `D11` | Formula contains numeric literal(s): ['1500'] | formula: =D8-D10-1500 | Replace literal with a reference to a driver/input cell |
| W21 | DCF_Inputs | `B6` | Formula contains numeric literal(s): ['1000'] | formula: =B4*B5/1000 | Replace literal with a reference to a driver/input cell |
| W22 | DCF | `B23` | Formula contains numeric literal(s): ['1000'] | formula: =-DCF_Inputs!B7*1000 | Replace literal with a reference to a driver/input cell |
| W23 | DCF | `B24` | Formula contains numeric literal(s): ['1000'] | formula: =-DCF_Inputs!B8*1000 | Replace literal with a reference to a driver/input cell |
| W24 | DCF | `B25` | Formula contains numeric literal(s): ['1000'] | formula: =-DCF_Inputs!B9*1000 | Replace literal with a reference to a driver/input cell |
| W25 | DCF | `B26` | Formula contains numeric literal(s): ['1000'] | formula: =-DCF_Inputs!B10*1000 | Replace literal with a reference to a driver/input cell |
| W26 | Valuation_Summary | `B15` | Formula contains numeric literal(s): ['0.15'] | formula: =0.15/B13 | Replace literal with a reference to a driver/input cell |
| W27 | Cash_Flow | `(structure)` | CF has Operating CF (implicit), Capex, Dividends, Buybacks, Net debt — but no consolidated CFO + CFI + CFF = Δ Cash | Add CFO, CFI, CFF subtotals and Δ Cash check |
| W28 | Income_Statement | `B15:L15` | Tax provision formula uses literal 0.22 instead of referencing DCF_Inputs!B15 (tax rate cell) | Replace =-{col}14*0.22 with =-{col}14*DCF_Inputs!$B$15 so tax rate flexes |
| W29 | Income_Statement | `B7:G7` | Historical EBITDA (B7:G7) hardcoded; forward EBITDA (H7:L7) computed as revenue × margin | Either keep all EBITDA as hardcoded reported figures with margin row as derived check, OR drive all years from revenue × margin with margin as input; current setup mixes the two |
| W30 | Revenue_Model | `G6:G12` | FY25A segment estimates sum to ~€37bn (ex-UK) vs reported €35.12bn — tie-out variance ~€1.9bn | Refine segment estimates from actual segment disclosure in TEF FY25 PR; current values are illustrative |
| W31 | DCF_Inputs | `B27:B28` | FY27 FCF growth 7.0% and FY28-30 7.5% vs revenue growth of 1.5-3.5% per Transform & Grow plan | Optimistic — relies on cost takeout phasing through. Stress test with FCF growth = revenue growth shows downside case |
| W32 | Valuation_Summary | `F10` | Weighted blend computes €3.67 (0.5*3.85 + 0.3*3.60 + 0.2*3.30); stated PT €4.10 is a judgment overlay | Either (a) revise method weights/mids to match €4.10, or (b) document the +€0.43 overlay as a 'plan execution premium' — current setup has computed PT but headline differs |

### Info (19 items)

| # | Sheet | Cell/Range | Issue | Fix |
|---|---|---|---|---|
| I1 | Income_Statement | `I10` | Formula pattern differs from row peer at H10 | Verify intent — could be hist/forecast boundary or a real inconsistency |
| I2 | Income_Statement | `J10` | Formula pattern differs from row peer at H10 | Verify intent — could be hist/forecast boundary or a real inconsistency |
| I3 | Income_Statement | `K10` | Formula pattern differs from row peer at H10 | Verify intent — could be hist/forecast boundary or a real inconsistency |
| I4 | Income_Statement | `L10` | Formula pattern differs from row peer at H10 | Verify intent — could be hist/forecast boundary or a real inconsistency |
| I5 | DCF | `C5` | Formula pattern differs from row peer at B5 | Verify intent — could be hist/forecast boundary or a real inconsistency |
| I6 | DCF | `D5` | Formula pattern differs from row peer at B5 | Verify intent — could be hist/forecast boundary or a real inconsistency |
| I7 | DCF | `E5` | Formula pattern differs from row peer at B5 | Verify intent — could be hist/forecast boundary or a real inconsistency |
| I8 | DCF_Sensitivity | `C4` | Formula pattern differs from row peer at B4 | Verify intent — could be hist/forecast boundary or a real inconsistency |
| I9 | DCF_Sensitivity | `D4` | Formula pattern differs from row peer at B4 | Verify intent — could be hist/forecast boundary or a real inconsistency |
| I10 | DCF_Sensitivity | `E4` | Formula pattern differs from row peer at B4 | Verify intent — could be hist/forecast boundary or a real inconsistency |
| I11 | Income_Statement | `B19:L19` | Diluted shares projected as hardcoded values (5,710 → 5,440); no link to buyback assumption in CF tab | Link shares decline to buyback row in CF (row 11) divided by avg share price; minor for this model |
| I12 | Income_Statement | `H22:L22` | Forward DPS (€0.15→€0.24) hardcoded; should ideally derive from 40-60% FCF payout policy from 2027 | Forward DPS = FCF * payout_ratio / shares; would let you flex payout assumption |
| I13 | (workbook) | `(global)` | Workbook does not enable iterative calculation; correct since no intentional circs | Confirm if any ND→interest→cash→ND cycle is needed for full 3-statement integration |
| I14 | DCF | `B16:B17` | Terminal value PV (~€16bn) is ~30% of EV (~€53bn); below 75% concentration threshold | Confirm via formula evaluation in Excel; current setup looks reasonable |
| I15 | DCF_Inputs | `B22` | WACC override 8.5% vs CAPM-implied ~6.1% = +240bps gap. Consistent with realised cost of capital but worth explicit comment | Add cell comment explaining override rationale (peer realised returns, sector risk premium) |
| I16 | DCF | `B8:F8` | DCF uses end-of-year discount factors (1, 2, 3, 4, 5); mid-year convention (0.5, 1.5, ...) gives ~4% higher PV | Confirm intent — most institutional DCFs use mid-year for typical assets; end-of-year is more conservative |
| I17 | DCF_Inputs | `B16` | Debt/capital target 50% used for WACC; current market values imply 55% debt weight (€26.8bn debt / €48.6bn EV) | If using current weights, WACC = 0.45*8.18% + 0.55*4.06% = 5.92%; if forward target, current 50% appropriate |
| I18 | DCF_Sensitivity | `B4:F8` | Sensitivity grid uses fixed WACC and growth axes; flexing DCF_Inputs!B22 (WACC override) does not update grid | Correct behaviour — sensitivity is meant to vary axes independently. To shift the grid range, change wacc_axis/g_axis in source script |
| I19 | Comps | `(check)` | TEF row uses company-reported FCF (9.5% yield), 2026 dividend (3.9%), current EV/EBITDA (4.50x) | Verified against facts pack file 14 sources |


## Critical findings — explained

### C1 — BS does not balance (no balance check in model)

The Balance_Sheet tab does not enforce Total Assets = Total Liabilities + Equity per period. The current BS is a "key items reference" (cash, PP&E, debt, equity, net debt) rather than a balanced 3-statement balance sheet.

**Why this matters:** A 3-statement DCF model must integrate IS → CF → BS with each tying to the next. Without a balance check, errors in working capital movements, dividends, or debt sweeps go undetected.

**Production fix:** Add lines for total assets (cash + receivables + inventory + PP&E + goodwill + other), total liabilities (AP + ST debt + LT debt + hybrids + other), total equity (common + retained earnings + minority), and a balance check formula `=TA - (TL + TE)` per period that should equal zero.

### C2 — Cash does not tie between CF and BS

The Cash_Flow tab does not compute opening + ΔCash = closing cash. The Balance_Sheet tab has Cash as a hardcoded series. The two are not linked.

**Why this matters:** The most important integrity check in any 3-statement model is that CF closing cash = BS cash for every period. Without it, the model can produce internally inconsistent numbers without warning.

**Production fix:** Add CF rows for "Opening cash", "Δ Cash" (= sum of CFO + CFI + CFF), "Closing cash" (= Opening + Δ). Then BS cash = CF closing cash via formula link.

### C3 — Unlevered FCF mismatch in DCF

The DCF discounts FCF from the Cash_Flow tab (€2.07bn FY25A, €3.0bn FY26E). These figures are TEF's company-reported FCF — which is LEVERED (net of interest expense, hybrid coupons, lease principal). The DCF discounts at WACC, which is the correct rate for UNLEVERED FCF only.

**Why this matters:** Discounting levered FCF at WACC double-counts the tax shield (already baked into the lower FCF number, then implicitly priced in the lower WACC vs Ke). The intrinsic value calculation is theoretically inconsistent.

**Production fix:** Either (a) build a true unlevered FCF row: `EBIT × (1-t) + D&A - capex - ΔNWC` and discount at WACC, or (b) discount the levered FCF at cost of equity (FCFE approach) and bridge to equity value directly without subtracting net debt. The current setup mixes the two methodologies.

In practice, for TEF the magnitude of the error is modest (~5-10% on intrinsic value) but the methodology is inconsistent and would not pass institutional review.

## Warning findings — categorised

**Hardcoded values where formulas should be (4 items):**
Tax rate literal in IS row 15, segment estimates not tying to reported FY25A revenue, FCF growth assumptions optimistic vs revenue growth, computed PT vs stated PT mismatch.

**Inconsistent derivation (1 item):**
EBITDA historical hardcoded but forward derived — mixes input and derived treatments.

**Cash flow structure (1 item):**
No CFO/CFI/CFF subtotals; CF tab is "key items" rather than full structured statement.

## Info findings — categorised

Style and convention items (8 total) including end-of-year discount convention, WACC override magnitude, sensitivity table axis hardcoding by design, missing comments on input cells, and forward-looking assumptions documentation.

## What the audit-xls skill actually does well

1. **Catches the common silent bugs** — hardcoded literals in formulas, missing balance checks, cash tie-out gaps. Each of these is a real structural issue in this model.
2. **Distinguishes severity** — separates critical (wrong output) from warning (risky) from info (style). The 3 critical findings here are genuine integrity gaps; the 8 info items are mostly stylistic.
3. **Forces the operator to articulate intent** — many "issues" are flags for "is this intentional?" rather than definitive bugs. Useful prompt for review discipline.

## What the audit-xls skill misses (and why)

1. **Cell-level color convention compliance** — the SKILL.md mentions Blue=input, black=formula, green=link. My v2 model uses yellow=input which deviates from convention. The audit didn't catch this (would need a colour-aware scan).
2. **Comment coverage** — the SKILL.md emphasises adding cell comments to every hardcoded input citing source. My v2 has only ~30% comment coverage. The audit doesn't audit comment density.
3. **Cross-tab unit consistency** — DCF tab uses €m; some bridge items use €B converted ×1000. The audit caught this implicitly via the unlevered FCF check but not as a unit-mismatch finding.
4. **Reasonableness benchmarks** — the SKILL.md says "growth rates >100% without explanation" but doesn't define what "without explanation" means programmatically. My model doesn't have absurd growth rates so this didn't fire.

## Honest takeaway on the v2 model

The v2 model is structurally rich and useable as a directional research artifact, but it is **not a true 3-statement model** in the institutional sense. The three critical findings would each need remediation before this could be sent to a client or published as part of an initiation report. Specifically:

- Without BS balance check, the model can produce internally inconsistent forecasts
- Without cash tie-out, errors in working capital / financing / capex compound silently
- The unlevered FCF mismatch in the DCF is a methodology error that would be caught by any senior analyst review

Each is fixable in 30-60 minutes of focused work. The build script (`_build_v2_model.py`) is a good starting point — extending the BS tab with full asset/liability line items and adding the CF cash-tie row would close C1 and C2. Building unlevered FCF row in CF and pointing the DCF at it would close C3.

## What this exercise demonstrates about the audit-xls skill

**The skill works.** Running the workflow against my own model surfaced three genuinely critical integrity issues that I had not flagged at build time (despite knowing the model was simplified). It also surfaced 6 warnings and 8 info items, most of which are real refinement opportunities.

**The audit is genuinely useful in production.** A senior analyst running this on an associate's model would catch issues before client distribution. The findings table format is concise and actionable.

**It needs the operator to interpret.** Many findings ask "is this intentional?" rather than asserting bugs — appropriate for a workflow tool but means the audit doesn't replace senior review, it scaffolds it.