湖南金指数五金机电贸易有限公司基于EXCEL进行杜邦分析的方案设计

湖南商务职业技术学院毕业设计

目录

1公司简介........................................................1

2杜邦分析模型与EXCEL的结合运用..................................1

3模型层次构建步骤................................................2

3.1新建一个工作簿..............................................2

3.2设计杜邦分析表..............................................3

3.3编辑计算公式及关闭工作表网格线..............................4

3.4保护工作表..................................................6

4数据验证........................................................6

5小结............................................................7

湖南商务职业技术学院毕业设计

湖南金指数五金机电贸易有限公司基于EXCEL进行

杜邦分析的方案设计

1公司简介

湖南金指数五金机电贸易有限公司成立于2021年06月10日，注册地位

于湖南省长沙市岳麓区观沙岭街道岳北渔场安置小区A16栋104。经营范围包

括五金产品批发；五金建材、机电设备、水泵、发电机、铁丝螺丝、劳保耗材、

专业电动工具、电子产品、环保工程设备、五金机电产品销售；机电设备安装

服务；五金产品、五金工具、建材的零售；五金建材批发；电动工具维修；机

电设备的维修及保养服务，是集多品类形成的五金机电建材装饰一站式采购超

市。

2杜邦分析模型与EXCEL的结合运用

1.杜邦分析方法是由杜邦公司的经理创立的一种分析手段。它和我国常用

的财务计算分析体系相比较，最大的优势是把大量的财务分析指标通过公式整

合在一起，呈现在模型内，因此便于展开分析企业在财务活动中的运营状况，

从而为企业制定正确的融资规划。在我国，综合财务指标分析体系则着重于综

合评价企业财务活动，反映企业获利、营运和偿债等方面的情况，供相关单位

和投资者进行参考，所以杜邦分析体系对于企业经营管理者来说更加合适。

2.杜邦分析法以权益净利率为核心指标，其他财务指标通过与其相应的关

系联系起来，指标间的联系是有层次、有分支的。如果手工绘制杜邦分析图，

或者在日常工作中将数据记录在手工本上，是一件非常费事的事情，计算量也

很冗杂。由此我们可以借助办公软件EXCEL的一般功能，来将数据与数据之间

的关系连接起来，将分类的数据储存在工作簿，形成相比与手工录入更加科学

清晰的归纳。在EXCEL中建立杜邦分析模型，可以使复杂的工作立刻变得简单

易操作，在遇到以后月份数据变更，只需点击一下数据更新，EXCEL会自动将

新的结果重算，此方法能大大提高办公效率，省时省力。现将杜邦分析模型结

构层次进行说明。

3.杜邦模型旨在深入剖析各指标间的深层次联系。在EXCEL中，分别对不

同的计算指标进行设置与定义。在图1中，第一层指标是净资产收益率，它是

综合性最强的财务指标及核心，能明确反应企业的获利能力大小。经过扣除相

1

湖南商务职业技术学院毕业设计

关的费用、固定利息费用、所得税费用之后，最终都要归所有者所拥有，所以，

和其他利益相关主体比较，投资者更加重视净资产收益率。在图示第二层指标

有总资产收益率，即销售净利率乘以总资产周转率的乘积；以及权益乘数等于

总资产除以股东权益；第三层指标有销售净利率等于净利润除以销售收入；总

资产周转率等于营业收入除以平均资产总额。总之，各项资产的周转指标用于

衡量企业运用资产赚取收入的能力，有效控制成本，为提高竞争能力打下基础。

在实践中，运用杜邦分析模型结合EXCEL展开深度挖掘企业内在价值成为财务

管理的关键一环。通过EXCEL设置好模型，定义名和计算公式，对资产、负债、

权益的精准分解，EXCEL的数据处理功力得以充分发挥，更使财务决策更加精

细化。如图1所示。

图1杜邦分析一般模型

3模型层次构建步骤

3.1新建一个工作簿

在EXCEL软件新建财务分析工作簿，命名为“杜邦分析模型”，包含三张

工作表，分别取名为“资产负债表”“利润表”以及“杜邦分析表”。然后根

据湖南金指数五金机电公司11期的业务，把涉及项目和金额填列进去。新建

的“资产负债表”工作表用于存放资产和负债类相应的财务数据，“利润表”

工作表用于存放有关企业经营状况计算财务指标,杜邦分析工作表用于进行杜

邦分析法的模型的建立及计算。利用杜邦分析方法对资本构成和盈利潜力进行

深入探讨，以揭示隐藏的商业机遇和潜在风险。该模型为公司未来的决策和发

展方向提供了清晰且科学的参考依据。以湖南金指数五金机电贸易有限公司

2023年11期的年报为例，把资产负债表与利润表的数据分别下载到EXCEL工

2

湖南商务职业技术学院毕业设计

作簿中，为了行文方便，对于表中部分无数据栏进行了隐藏处理。如图2-图3

所示。

图2资产负债表

图3利润表

3.2设计杜邦分析表

杜邦模型图可以由多个形状的分析框和连线架构组成。利用EXCEL，在每

个分析框中标出分析项目的名称、比率公式和相应计算结果，用连线将分析框

连接起来，以体现分析框中各类分析项目的内在关系。首先给“净资产报酬率”

3

湖南商务职业技术学院毕业设计

创建一个分析框。选择F2与F3单元格，为了行文方便，图中将F2,G2单元格

与F3，G3进行了合并。从菜单栏中单击单元格的命令，选择边框标签，选择

外侧框线。然后选择“形状”中的括号箭头，依次做一个形状框架搭起来。选

择确定按钮，选中分析框中的单元，直接输入项目名称“净资产报酬率”。用

同样的方法建立“总资产净利率”、“权益乘数”、“销售净利率”、“总资产周转

率”、“资产总计”、“股东权益合计”、“净利率”、“营业总收入”等分析框。形

成具体框架，方便后面公式的嵌套。如图4所示。

图4杜邦分析表模型

3.3编辑计算公式及关闭工作表网格线

在EXCEL中有着大量的计算公式，通过公式运用可以辅助处理好数据和制

图等，更好的体现出与传统手工录入的便捷性与高效性，减少可能在人工计算

产生的错误，为接下来进行杜邦分析做好进一步准备。本次模型主要使用的是

VLOOKUP函数，公式中的参数说明：=VLOOKUP（要查找的值，要在其中查找值

的区域，区域中包含返回值的列号，FALSE/TRUE）。把从资产负债表与利润表

中对应的数据通过计算公式引入到杜邦分析模型中。

这样做的目的是只要引用的单元格名称对应的公式存在，无论资产负债表

与利润表所填项目是否删除省略了或顺序发生变化，都不会影响计算所需的数

据。以净利润指标为例：在杜邦分析表中单击净利润单元格，输入“=”号，

打开公式定义单元栏，找到VLOOKUP函数，在第一列要查找的值中，鼠标单击

“利润表!A35”;在第二列数据表范围鼠标拖动选择“利润表!A4:C35”；列叙

述填“3”，则会返回数据表中第3列的数值；匹配条件输入“FALSE”，单击确

4

湖南商务职业技术学院毕业设计

定即可。其他的指标以同样的操作步骤进行函数公式定义。定义过程与引用结

果如图5、6所示。

图5VLOOKUP函数定义过程

图6杜邦分析计算公式

其次，在通常情况下，EXCEL的工作表是由多个单元格组成，这些单元是

通过网格线进行分隔的。如果想把单元格中每个数据点都能准确地定位出来，

则必须使用表格功能。EXCEL提供了一个命令，目的是为了根据用户的具体需

求，调整工作表的显示状态，使其能够以多种方式展现。如果在表格中添加新

内容时，不知道如何对应调整单元格大小就会造成一些不必要的操作，此时可

将工作表进行适当地移动以实现这一目的。在[工作]菜单的[选项]命令下，

[视图]标签有能力改变工作表的显示模式，包括打开或关闭网格线、行和列的

标题、概要符号、自动分页符的显示、公式或数值的展示，以及网格线和不同

行列标题颜色的调整等。在表格中设置好这些参数后，就能方便地修改工作表

的结构。在杜邦系统图的设计中，建议采用关闭工作表网格线的功能，让其展

5

湖南商务职业技术学院毕业设计

示更为清晰和直观。

3.4保护工作表

在EXCEL软件中有的“保护工作表”功能，可以把我们所要保护的表格全

部写保护，以免误删公式或者数据被修改，对数据核算产生影响，增强其安全

性。首先鼠标单击工作表进行全选，在设置单元格中点击保护，将需要被锁定

的区域选中，点击保护，勾选-锁定确定，单击菜单栏[审阅]-[保护工作表]-

[输入密码]，再次点击保护的单元格时，EXCEL会提醒你不允许更改。之后，

把该工作簿保存为“杜邦分析模型表”。

4数据验证

1.在EXCEL中，通过杜邦模型在由建立与设计公式到引入数据的一系列过

程，得到了由EXCEL自动计算出来的湖南五金机电贸易有限公司2023年的财

务指标数据。结果如图7所示

图7杜邦模型结果

2.模型主要检验以下五个指标的关系：

一是销售净利率：=净利润÷营业总收入

二是总资产周转率：=营业总收入÷资产总额

三是权益乘数：=资产总计÷股东权益合计

四是总资产净利率:=销售净利率×总资产周转率

五是净资产收益率:=总资产净利率×权益乘数

3.通过EXCEL进行构建杜邦模型所计算出的结果与传统手工计算录入数据

对比，数据一致，结果如下：

6

湖南商务职业技术学院毕业设计

销售净利率：0.043=20721.07÷477864.38

总资产周转率：0.304=477864.38÷1571848.63

总资产净利率：0.013=0.043×0.304

权益乘数：-51.758=1571848.63÷（-3