# DuPont Analysis Decompose Return on Equity into the operating, asset-efficiency, and financing levers that produce it. Run the classic 3-step DuPont (Net Margin x Asset Turnover x Equity Multiplier) and the extended 5-step DuPont (Tax Burden x Interest Burden x EBIT Margin x Asset Turnover x Equity Multiplier) across five years, with residual checks that prove the algebraic identity holds cell by cell, then benchmark every driver against four peers in the current year. - Canonical: https://finamodel.com/templates/dupont-analysis - Excel download: https://finamodel.com/templates/dupont-analysis.xlsx - Category: Capital Markets - Model type: Operating model - Difficulty: Intermediate - Audiences: Investors & analysts, CFOs & FP&A, Equity analysts, Credit officers, Corporate finance, MBA students - Tags: roe, dupont, ratio analysis, profitability, benchmarking ## Overview A DuPont analysis template decomposes Return on Equity into the operating, asset-efficiency, and financing levers that produce it, then benchmarks each lever against a peer set in the current year. The model carries five years of company financials and a four-peer comparison, computes both the classic 3-step and the extended 5-step decomposition, and reconciles each year against a direct NI / Equity reference within a user-set tolerance band. The Assumptions sheet holds a base year input, five years of company P&L (revenue, COGS, operating expenses, D&A, interest expense, tax expense), five years of company balance sheet (total assets, total equity), four peer columns with the same six financial lines (sales, EBIT, EBT, net income, total assets, total equity), and a tolerance value. The Inputs_Summary sheet pulls every line through, derives EBIT as revenue minus operating costs, EBT as EBIT minus interest, and net income as EBT minus tax, with formula-driven year labels keyed off the base year input. The DuPont_3Step sheet computes Net Margin (NI / Revenue), Asset Turnover (Revenue / Total Assets), and Equity Multiplier (Total Assets / Total Equity) across all five years, multiplies them to derive a 3-step ROE, and runs a residual row against NI / Total Equity. The DuPont_5Step sheet extends the decomposition with Tax Burden (NI / EBT), Interest Burden (EBT / EBIT), and EBIT Margin (EBIT / Revenue), multiplies all five terms, and runs a residual row against the 3-step ROE - proving the algebraic identity Net_Margin = Tax_Burden x Interest_Burden x EBIT_Margin cell by cell. The Peer_Compare sheet drops the company's latest year alongside four peers and a peer-average column, computes the full 5-step decomposition and ROE for every entity, and surfaces where the company is above or below the peer set on each driver. The Summary sheet rolls the latest-year decomposition, a five-year ROE trend, a peer-vs-company spread block per driver, and a reconcile status cell that prints Reconciles or Off based on ABS(residual) versus tolerance. Equity analysts, credit officers, corporate-finance teams, and MBA / CFA students use the workbook for idea generation, credit diagnostics, and teaching the DuPont identity. ## What's included - Five-year company P&L inputs (revenue, COGS, opex, D&A, interest, tax) and balance sheet (total assets, total equity) - Inputs_Summary sheet that derives EBIT, EBT, and net income line-by-line with formula-driven year labels - DuPont_3Step: Net Margin x Asset Turnover x Equity Multiplier with a NI / Equity residual check - DuPont_5Step: Tax Burden x Interest Burden x EBIT Margin x Asset Turnover x Equity Multiplier with a 3-step residual check - Peer_Compare: company current year versus four peers with a Peer Avg column - Summary with latest-year drivers, 5-year ROE trend, peer-vs-company spread per driver, reconcile status - Clean Inputs_Summary that derives EBIT, EBT, and net income line-by-line - DuPont_3Step sheet: Net Margin x Asset Turnover x Equity Multiplier with a NI/Equity residual check - DuPont_5Step sheet: Tax Burden x Interest Burden x EBIT Margin x Asset Turnover x Equity Multiplier with a 5-step vs 3-step residual check - Peer_Compare sheet: company current year versus four peers with a Peer Avg column - Summary with latest-year drivers, five-year ROE trend, peer-vs-company spread per driver, and a reconcile status cell ## DuPont Analysis: How the Model Decomposes Return on Equity This DuPont analysis template breaks down Return on Equity into its operating, asset-efficiency, and financing components, using both the classic three-step and the extended five-step identities over a five-year horizon. It includes parallel ROIC decomposition, segment views, peer benchmarking, sensitivity grids, and reconciliation checks, giving analysts a clear view of which levers drive returns. ### Operating Drivers Behind the Decomposition The model separates ROE into five operating and financing drivers. Tax Burden measures profit retained after tax, while Interest Burden shows the share of EBIT kept after interest expense. - EBIT Margin captures operating profitability, Asset Turnover reflects revenue generated per dollar of assets, and Equity Multiplier indicates balance sheet leverage. Together these show whether returns come from profitable operations, efficient asset use, or financial leverage. - The three-step version collapses the first three into Net Margin. ### How the Calculation Flows Through the Model Inputs are entered on the Assumptions sheet, including scenario selection, industry benchmarks, and the choice between end-of-period and average balance conventions. - The Inputs_Summary sheet consolidates P&L and balance sheet data, computes average balances, and provides EBIT, EBT, and net income figures. - The DuPont sheets then apply the multiplicative formulas year by year, referencing those inputs. - The balance convention toggle changes asset turnover and equity multiplier to use either closing or average balances, affecting how growth companies are assessed. ### Outputs and Reconciliation Checks The model produces per-year ROE from both three-step and five-step decompositions, an ROIC variant that removes capital structure effects, segment-level five-step views for three regions, and a driver bridge showing contributions from year one to year five. - The Checks sheet contains 62 PASS/FAIL formulas covering residual tolerances, equity positivity, interest coverage, tax and interest burden ranges, peer integrity, and bridge reconciliation. - These checks verify the algebraic identities and flag data issues. ### Practical Use for Evaluating Drivers This template is built for equity analysts, credit officers, and CFOs who need to identify which lever moved ROE and compare it to industry bands and peers. - The Summary sheet provides verdicts against sector benchmarks and peer means. - Sensitivity grids show how ROE responds to changes in EBIT margin, asset turnover, equity multiplier, and net margin. - Scenario toggles for Base, Bull, and Bear illustrate how drivers can move adversely under stress, but the model does not predict outcomes. ## Built for the DuPont identity Both decompositions sit side-by-side and the residual rows prove the algebra holds. If a formula drifts, the residual moves off zero and the Summary status cell flips to Off. Hard to break silently. ## Peer benchmarking on every lever Peer_Compare runs the full 5-step decomposition for the company and four peers, then averages the peers so the Summary can call out which driver is doing the work versus the benchmark. ## Audit-friendly mechanics Every input is in Assumptions, every formula is one or two operations, year labels are formula-driven from a base year input, and the workbook passes static-value, self-reference, and dead-assumption scans. ## Built for the DuPont identity Both decompositions sit side-by-side and the residual rows prove the algebra holds. If a formula drifts, the residual moves off zero and the Summary status cell flips to Off. Hard to break silently. ## Peer benchmarking on every lever Peer_Compare runs the full 5-step decomposition for the company and four peers, then averages the peers so the Summary can call out which driver is doing the work versus the benchmark. ## Audit-friendly mechanics Every input is in Assumptions, every formula is one or two operations, year labels are formula-driven from a base year input, and the workbook passes static-value, self-reference, and dead-assumption scans. ## Workbook structure ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: base year, 5-year company financials, peer inputs, tolerance. - Base year (calendar year of Y1) - 5-year company P&L: revenue, COGS, opex, D&A, interest, tax - 5-year company BS: total assets, total equity - 4 peer entities x 6 financial lines (sales, EBIT, EBT, NI, total assets, total equity) - Reconciliation tolerance ### Inputs_Summary Clean 5-year P&L roll plus BS pull-through. - Revenue, COGS, opex, D&A pulled from Assumptions - EBIT = revenue - COGS - opex - D&A - EBT = EBIT - interest - Net income = EBT - tax - Total assets and total equity from Assumptions ### DuPont_3Step Three-driver ROE decomposition with a direct-ROE residual check. - Net Margin = NI / Revenue - Asset Turnover = Revenue / Total Assets - Equity Multiplier = Total Assets / Total Equity - ROE_3Step = product of the three drivers - ROE_Direct = NI / Total Equity - Residual = ROE_3Step - ROE_Direct (expect 0) ### DuPont_5Step Five-driver decomposition with a 3-step residual check. - Tax Burden = NI / EBT - Interest Burden = EBT / EBIT - EBIT Margin = EBIT / Revenue - Asset Turnover = Revenue / Total Assets - Equity Multiplier = Total Assets / Total Equity - ROE_5Step = product of the five drivers - Residual = ROE_5Step - ROE_3Step (expect 0 by identity) ### Peer_Compare Latest-year decomposition for the company and four peers, plus a peer-average column. - Company column pulls Y5 from Inputs_Summary - Peer A-D columns pull from Assumptions peer block - Peer Avg column = AVERAGE(D:G) per row - Full 5-step decomposition: Tax, Interest, EBIT Margin, Turnover, Multiplier, ROE - Net margin and 3-step ROE shown at the bottom ### Summary Single-page snapshot with status check. - Latest-year 5-step decomposition with interpretation column - 5-year ROE trend pulled from DuPont_5Step - Peer spread block: Company vs Peer Avg per driver, with a Spread column - Status cell: Reconciles or Off based on ABS(residual) <= Tolerance, both sheets ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: base year, 5-year company financials, peer inputs, tolerance. - Base year (calendar year of Y1) - 5-year company P&L: revenue, COGS, opex, D&A, interest, tax - 5-year company BS: total assets, total equity - 4 peer entities x 6 financial lines (sales, EBIT, EBT, NI, total assets, total equity) - Reconciliation tolerance ### Inputs_Summary Clean 5-year P&L roll plus BS pull-through. - Revenue, COGS, opex, D&A pulled from Assumptions - EBIT = revenue - COGS - opex - D&A - EBT = EBIT - interest - Net income = EBT - tax - Total assets and total equity from Assumptions ### DuPont_3Step Three-driver ROE decomposition with a direct-ROE residual check. - Net Margin = NI / Revenue - Asset Turnover = Revenue / Total Assets - Equity Multiplier = Total Assets / Total Equity - ROE_3Step = product of the three drivers - ROE_Direct = NI / Total Equity - Residual = ROE_3Step - ROE_Direct (expect 0) ### DuPont_5Step Five-driver decomposition with a 3-step residual check. - Tax Burden = NI / EBT - Interest Burden = EBT / EBIT - EBIT Margin = EBIT / Revenue - Asset Turnover = Revenue / Total Assets - Equity Multiplier = Total Assets / Total Equity - ROE_5Step = product of the five drivers - Residual = ROE_5Step - ROE_3Step (expect 0 by identity) ### Peer_Compare Latest-year decomposition for the company and four peers, plus a peer-average column. - Company column pulls Y5 from Inputs_Summary - Peer A-D columns pull from Assumptions peer block - Peer Avg column = AVERAGE(D:G) per row - Full 5-step decomposition: Tax, Interest, EBIT Margin, Turnover, Multiplier, ROE - Net margin and 3-step ROE shown at the bottom ### Summary Single-page snapshot with status check. - Latest-year 5-step decomposition with interpretation column - 5-year ROE trend pulled from DuPont_5Step - Peer spread block: Company vs Peer Avg per driver, with a Spread column - Status cell: Reconciles or Off based on ABS(residual) <= Tolerance, both sheets ## Features - **3-step and 5-step side-by-side:** Every year carries both decompositions, and a residual row on each sheet proves that the algebraic identity holds within tolerance. No silent sign errors slip through. - **Peer benchmarking on every driver:** Peer_Compare runs Tax Burden, Interest Burden, EBIT Margin, Asset Turnover, and Equity Multiplier for the company and four peers, then averages the peers so the Summary can call out which lever is above or below the peer set. - **Tolerance-driven status check:** Summary prints Reconciles or Off based on ABS(residual) versus a user-set tolerance. Default is one basis point - tight enough to catch a structural error, loose enough to absorb float noise. ## Use cases - **Equity research idea generation:** Run a target company and three or four peers through the model to see whether a stretched ROE is driven by margin, turnover, or leverage - and whether the peer set is doing the same thing or pulling on a different lever. - **Credit committee diagnostic:** Drop a borrower's five-year financials in and watch the Equity Multiplier and Interest Burden. A rising multiplier with a falling interest burden is the early warning that leverage is starting to dominate operating performance. - **MBA / CFA teaching aid:** Side-by-side 3-step and 5-step with an explicit residual check makes the algebraic identity (Net Margin = Tax Burden x Interest Burden x EBIT Margin) visible cell by cell. Edit any input and watch every dependent driver re-rate. ## Frequently asked questions ### What is DuPont analysis? DuPont analysis decomposes Return on Equity into the operating, asset-efficiency, and financing levers that produce it. The 3-step identity is ROE = Net Margin x Asset Turnover x Equity Multiplier; the 5-step identity extends it to Tax Burden x Interest Burden x EBIT Margin x Asset Turnover x Equity Multiplier. Together they let an analyst answer which lever is doing the work behind a headline ROE number. ### Does this use end-of-period or average balances? End-of-period throughout. End-of-period is simpler, common in textbooks and equity research, and avoids the opening-balance dependency in Y1. To switch to average balances, replace each Total_Assets and Total_Equity reference in the turnover and leverage formulas with AVERAGE of the prior and current period and add a Y0 column to the inputs. ### Why is Tax Burden a ratio rather than the tax rate? Tax Burden is the keep ratio: Net Income / EBT. A profitable company always has Tax Burden between 0 and 1 (it cannot keep more than 100% of pretax income). Effective tax rate is the complement: 1 minus Tax Burden. The model uses the keep-ratio convention because that is what multiplies through the DuPont identity. ### Can I swap in different peers? Yes. Peer names are inputs in the Assumptions sheet, and the six financial lines per peer (sales, EBIT, EBT, net income, total assets, total equity) flow straight into Peer_Compare. Pre-screen peers for fiscal calendar and accounting standard so the asset turnover and equity multiplier comparisons are like-for-like. ### What does the residual check actually test? On the 3-step sheet, residual equals ROE_3Step minus NI / Total_Equity. By construction it must be zero. On the 5-step sheet, residual equals ROE_5Step minus ROE_3Step, which is zero by the algebraic identity Net_Margin = Tax_Burden x Interest_Burden x EBIT_Margin. A non-zero residual outside tolerance is the signal that a formula has been changed, a row reference has drifted, or an opening balance has crept in. ## Related templates - [Financial Health Dashboard](https://finamodel.com/templates/financial-health) - [3 Statement Model](https://finamodel.com/templates/3-statement-model) - [Comparable Companies Analysis](https://finamodel.com/templates/comparable-company-analysis)