15.1 Basics

К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 
85 86 87 88 89 90 91 92 93 94 95 96 97 

Problem. A company's Net Profit is $170, Pretax Profit is $260, EBIT is $470, Sales is $4,590, Assets is

$4,190, and Equity is $4,340. Calculate the company's ROE and decompose the ROE into its components

using the Du Pont System.

FIGURE 15.1 Spreadsheet Model of Du Pont System of Ratio Analysis - Basics.

How To Build This Spreadsheet Model.

1. Enter The Inputs. Enter the inputs into the range B4:B9.

2. ROE = Net Profit / Equity. Return on Equity is defined as Net Profit / Equity. Enter =B4/B9 in

the cell B12.

3. Components of ROE. The essence of the Du Pont System is decomposing Return On Equity into

five components:

Net Profit / Pretax Profit. Enter =B4/B5 in the cell B14.

Rest of the Components. Copy the cell B14 to the range B15:B18.

The ROE = 3.9%. The decomposition helps us see where this comes from. Here is an intuitive

interpretation of the components:

Net Profit / Pretax = 65.4% is a tax-burden ratio.

Pretax Profits / EBIT = 55.3% is an interest-burden ratio.

EBIT / Sales = 10.2% is the profit margin.

Sales / Assets = 109.5% is the asset turnover.

Asset / Equity = 96.5% is the leverage ratio.

Problem. A company's Net Profit is $170, Pretax Profit is $260, EBIT is $470, Sales is $4,590, Assets is

$4,190, and Equity is $4,340. Calculate the company's ROE and decompose the ROE into its components

using the Du Pont System.

FIGURE 15.1 Spreadsheet Model of Du Pont System of Ratio Analysis - Basics.

How To Build This Spreadsheet Model.

1. Enter The Inputs. Enter the inputs into the range B4:B9.

2. ROE = Net Profit / Equity. Return on Equity is defined as Net Profit / Equity. Enter =B4/B9 in

the cell B12.

3. Components of ROE. The essence of the Du Pont System is decomposing Return On Equity into

five components:

Net Profit / Pretax Profit. Enter =B4/B5 in the cell B14.

Rest of the Components. Copy the cell B14 to the range B15:B18.

The ROE = 3.9%. The decomposition helps us see where this comes from. Here is an intuitive

interpretation of the components:

Net Profit / Pretax = 65.4% is a tax-burden ratio.

Pretax Profits / EBIT = 55.3% is an interest-burden ratio.

EBIT / Sales = 10.2% is the profit margin.

Sales / Assets = 109.5% is the asset turnover.

Asset / Equity = 96.5% is the leverage ratio.