财务分析模型实验报告

年级： 2013级 学号： _________________________ 姓名： 陈婷 实验时间：_

【实验目的】

1. 掌握应用Excel 获取数据的方法；

2. 掌握财务分析模型（比率分析模型、趋势分析模型以及杜邦财务分析模型）的设计方法

3. 掌握Excel 软件中相关函数、功能的应用

【实验内容】

1. 编制1月、2月资产负债表；

2. 编制1月、2月利润表；

3. 建立比率分析模型，包括：

① 偿债能力（流动比率、速动比率、现金比率、资产负债率、股东权益比率、负债股权比率、 净值债务率、利息保障倍数）；

② 营运能力（存货周转率、应收账款周转率、流动资产周转率、总资产周转率

）；

③ 获利能力（资产报酬率、股东权益报酬率、销售毛利率、销售净利率、成本费用净利率 并对企业2月份的财务状况进行评价。

4. 根据比率分析模型分析：

① 总资产、总负债变化原因；②流动比率变动原因；③分析资产净利率变化原因；

【实验器材】

硬件：处理器：Intel(R) Celeron ( R CPU420 @ 1.60G H z

主板：Len ovo In tel 945GZ (Lakeport-GZ ) +ICH7

内存：DDR400 504MB

硬盘：Hitachi80G.

显卡：In ter ( R ) 82945G Express Chipset Family ； 显示器：Lenovo 17.1吋液晶显示器； 电源：ATX 300SD

网卡：Realtek RTL8139/810Xa Family Fast Ethernet NIC 系统：Microsoft Win dows XP Professio nal Build 2600 软件：Excel 软件

【预备知识】

1. 《财务管理学》

2. Excel 软件的应用

2015.10

有形

【实验步骤】

、数据引入打开Excel软件，单击【数据】，选择【导入外部数据】菜单的【导入数据】按钮，选择第四章练习资料.txt文件，单击右下角的【打开】。自动弹出【文本导入向导-3步骤之1】，单击

【下一步】，接着弹出【文本导入向导-3步骤之2】，在【分隔符号】项目中，选择【Tab键】和【空

格】，再单击【下一步】，接着弹出【文本导入向导-3步骤之3】，看看数据浏览、符号要求，单击【完

成】，弹出【导入数据】对话框，将数据放在现有工作表中的=$A$1,单击右

上角【确定】。完成导入数据后，在Excel中显示出来的第四章练习资料.txt文件。对导入的数据进行试算

平衡，以确保数据无误。同时对导入的数据进行格式调整调整，以更加美观整洁，调整完毕即表示数据引入完

成。

4 5 6 7 8 3 10 1L

12 13 14 15 16

17 1?19

20 21 22 23 24 25 26 27 23 29 30 3L

A B C D E F G H I J

齐目名称1月悄2月份

期初余颔期末余瓠本期发生颔期末余额本期发生瓠

借方惜方贷万惜方贷方

库存现金7500005053750039478. 520 "存款1200000021111000024905800

J性金融资产5000000500000002040000

应6000000120030000172445S. 170

应25000003523556. 92004B00Q0

熔司900000362258. 920D200000

E公司0400000264702004000

C公司2000000342600000300000

坏帐准酱07500750009.79

预付帐款10000003983630094000

D公司100000040000000100000

E公司000163200600

其他应收款6000006610000160000啄购004656000000

［料30000003004268. 24002482239.450

材1成本差异BODOQ067m 18005S2&0Q

库存商品00451885. 41007840000

固定资产1130000013B07600018807600

累计折旧0608000063805k 3600669000

在建工程3200000330297003191600

无形资产120000001200000001630730

递延贸产3000000300000002500000

待处理财产损溢0051544* 30000

待处理固定资产扌贞溢0051544. 3

短期借款0700000070000000126000

应付票据0500000506683. 330055260

应付帐款0150000050425760020000

.F公司01QQOQO036S2QQQ D014Q0Q

竝f 0 k科目名称

下图是第四章练习资料中所附的要求，也就是本次实验的内容:

1. 编制1月、2月资产负债表］

2. 编制1月、2月釉同表；

3. 建立比率分析模型，包括：

① 偿债能力（涼动比率、速动比氢现金比率、资产负债率"股东权益比率八

② 善运能力Off 货周转率r 应收账款周转率、流动资产周转率r 总童产周转率）;

③ 联利能力（资产报酬军、股东杈益报酬率、销售毛刑率、销售浄利睾h 并对企业2月优的财务状况进行评桥亠 4. 根据比率分析模型分析2月粉=

①总资产、总负债变化原因；②號动比率变动原因；③分析资产净利率变化原因; 5. 采用趋势分析法（图解法）分析企业的偿债能力、营运能力叹及茯利能力； E ?建立杜邦财务分析模型.并运用杜邦分析法分析权益净利率变动原因*

二、编制1月和2月资产负债表

1、在Excel 中插入新的工作表，以第四章练习资料.txt 文件，按照会计制度要求、资产负 债表基本原理和基本方法来编制报表。在新插入的工作表中设计资产负债表基本框架，设置 相关科目。根据资料来编制该公司的资产负债表时，按照填列方法和填列说明，有些项目要 多加留意，不能多加数据或少加数据，比如说： ① 货币资金项目是根据库存现金和银行存款科目期末余额的合计数填列的

② 应收账款项目是根据应收账款和预收账款科目所属各明细的期末借方余额合计减去坏账准 备科目中有关应收账款计提的坏账准备期末余额后的金额填列， （应付账款、预收账款。预付

账款这三个项目的填列也要留意）

③ 存货项目是根据材料采购、原材料、库存商品、生产成本等科目的期末余额合计填列，但 因为该公司的材料是采用计划成本核算，那么还应该再加上材料成本差异的借方金额后的余 额合计填列。

④ 其他非流动资产项目，因为该企业有待处理财产损溢-待处理固定资产损溢科目，待处理财 产损溢在未报

31 32 33 34

35

36 3? 3E

39 4D

41

42

43

44 45 46 47

4g 49 50

51

52 53 54 55

56 57 58 59

加 酬 入本加

入本 失 款

薪 款」

」

收成及收

成 播入

岀

用

付工

费

款付本积积

润配本务务金务务用用用益值收支

费

司

应职税借应资会金利分成业业税业业费费费收碱外外税

皈他付交期期收本余年润鬥莒营业他他售理务资产业业得

必其应应扫长实资盈本利主主主莒其其销管财a

资曽营所

5000

0 0 0 0

0 0 0 0

2034 D

0 0 0

D

0 0 0

0 0 0

100000 D 3692000

0 50000

2233774

0 0 0 0 3589350 0 35000 0 232000 0 40000

0 320000

0 0 88000

0 0 0 0 185000 0 0 3 6323. 75 0 0 0 381124. 06 0 350000 0 350000 0 500000 0 600000 0 3082340 0 3082340 0 700000 0 753160 0 100000

0 100000 0 0 0 2716458. 17

0 359250

359250

0 0

96723. 62

0 0 6877948.72 0 0 0 3897245. 37

0 0 0 70000 0 0 0 100000 0 0 0 10000 0 0 0 90000 0

0 0 56137. 99 0

0 0 108107.19

0 0 0 0 0 0 0 10000 0 0 0 24000 0 D 0 14000 0

0 30000

10000

0 0

6000

0 0

0 0 0

0 0 44D00

0 0

0 0

10000

D

14000 0 16000 2000 BOO O 0 18000 69400 120000 128000 40000 3082340 753160 2X702.18 4865287. IT

359250

0 0 0 0 0 0 0 0 0 0

5500000

2719391

190000 380000 90000 190000 150000 110000 uoooo 10000 60000 24000

417780

61

62要求: 63 64 65 曲 67 C3 69 70

U 72 |

73

H 公司

F 公司

G 公

司

经批准前与资产相关，在报批准后与当期损溢直接相关，损溢可以将未报经批

⑤准前待处理固定资产损溢科目余额，列示在其他非流动资产项目中。

未分配利润项目是根据本年利润科目和利润分配科目的余额计算填列。

2、根据资产负债表各科目与原始账之间的勾稽关系，在资产负债表中各科目的对应位置填写公式，例如“ B6 =源数据!B4+源数据!B5”即为其中一个公式，该公式表示1月份货币资金的期初数等于1月库存现金和银行存款之和。将1、2月份资产负债表分别和1、2月份原始数据建立联系。公式填写无误后，Excel软件即可自动汇总计算资产负债表。

3、分别编制好1、2月份的资产负债表，进行试算平衡，以确保数据无误

4、对资产负债表格式进行调整，以更加美观

一月份资产负债表

二月份资产负债表

三、编制1月和2月利润表

1、 按照会计制度要求，在 Excel 软件中设计利润表基本框架，设置相关项目

2、 根据利润表各项目与原始账之间的勾稽关系，在利润表中各项目的对应位置填写公式，例 如“B5 =源数据!F48+源数据!F51 ”即为其中一个公式，该公式表示1月份营业收入等于1月 主营业务收入和其他业务收入之和。将 1、2月份利润表分别和1、2月份原始数据建立联系。 公式填写无误后，Excel 软件即可自动汇总计算利润表。

3、 确认利润表编制无误后，对利润表格式进行调整，以更加美观

一月份利润表

31

32 33 34 35

朋

37 38 39 40 41

&国寻直公"▼国1 005陆婷朋* X +

AI d A—亦润n

四.建立比率分析模型

1插入一张新的工作表，在新插入的工作表中，设计比率分析模型的基本框架结构

2、以1、2月原始数据和1、2月份资产负债表、利润表为基础，根据比率分析模型的计算方法，分别在比率分析模型中各比率对应的单元格中填入公式，建立比率分析模型与先前各项数据之间的勾稽关系。例如“ B3 ='1月份资产负债表'!C18/'1月份资产负债表'!F18 ”即为其中的一个公式，表示1月份流动比率等于1月流动资产期末数合计除以1月流动负债期末数合计。公式填写无误后，Excel软件即可自动汇总计算各比率。

3、根据比率分析模型的结果，对2月份的财务状况进行评价。

4、对设计好的比率分析模型进行格式调整，以更加美观。

对企业2月份的财务

苜先分析短期偿债能力指标：流动比率皿3気速动比率m阪现金比率&.34

与一月指标相比有大幅度提高，说明企业资产济动性较大，短期偿债能力较强。

但是.比率太大表明凉动资产占用较多*可能会导致企业机会成本増加’影响经营資金周转效率和获利能力。其次分析长期偿债能力指标：资产负债比率0. 06,股东权益比率O.gq ____________

比一月俭大幅扌是高，企业长期偿债能力增强。_____________________________________________________ 这说明债权人权益保障程度较高，但对于企业来说，役有充分利用财务杠杆来获得更多利润。

从营运能力指标来看：存货周转率0.75,应收账款周转率2. 99,流动资产周转率0.51,总资产周转率0. 43

相比一月份，这几项指标均有不同程度的降低，说明企业存货管理、收账速度等营运能力整体诚弱，观察几项获制能力扌駕可以发现| ______

虽然梢售毛制率较一月份有小幅度提高，___________________________________________________________ 但资产报酬率，股东权益报酬率、销售净利率均下降，说明企业获利能力变弱，可能影响企业的市场竞争能力。

五.变化原因分析

1、根据比率分析模型的变动，结合资产负债表、利润表，分别分析总资产、总负债变化原因, 流动比率变动原因和资产净利率变化原因。

2、在Excel中插入图表，与变化原因相结合，使分析结果更加直观，更具说明性。

Gr S *& [\ * ** T ? 005陈婷d 弓 *

①总资产"总负债变化瓯因

六. 采用趋势分析法（图解法）分析企业的偿债能力、营运能力以及获利能力

1根据比率分析模型的计算结果，分别建立偿债能力分析、营运能力分析和获利能力分析的 相关图表，如下图：

2根据图表，分析各项指标的变动趋势，再结合资产负债表、利润表等资料，分别对企业的 偿债能力、营运能力以及获利能力进行分析。

① 从偿债能力分析图可以看出，企业流动比率、速动比率、现金比率均有大幅度提高，说明 企业短期偿债能力大幅提高，说明企业短期偿债能力增强。但这些指标明显是过分地高，表 明流动资产占用较多，会导致企业机会成本增加，影响经营资金周转效率和获利能力。利息 保障倍数与一月份相比无太大变化；股东权益比率比一月份大幅提高，资产负债率、负债股 权比例和有形净值债务率则大幅降低，这几项指标的变化综合说明债务相对于股东权益的减 少，企业在当期偿还大量债务后，企业长期偿债能力增强。这说明债权人权益保障程度较高, 但对于企业来说，没有充分利用财务杠杆来获得更多利润。

② 从营运能力分析图来看，存货周转率、应收账款周转率、流动资产周转率、总资产周转率, 相比一月份，这几项指标均有不同程度的降低，说明企业存货管理、收账速度等营运能力整 体减弱。

A1

丄

A

1

0 A 根据比率分析模型分析2月份

10 11

12

13 14 15 16 17 18 19

20 21 22 23 流动比率（从1.553204156变动到18. 35553112）和速动比率 （Ml-1296119口变动到10. 55165538）的大幅变訪可以推测 流动资产、速动资产或流动负债发生了变动。结合资产负债 表，我们可以发现，应收账款从3875508, 92诚少到 55990,21,预付账款从2638774减少到20000,使流动资产和 速动资产发生了巨大变化。无形资产从1200000诚少到 163075,造成了非济动资产的犬幅减少，流动资产和固定资 产均减少，导致总资产的减少。

②流动比率变动原因 流动比率的变化，可能是因为流动资产或流动负债的变化。 结合资产负债表分析可知，流动比率的变动主要是因为流动 负债的大幅减少，；流动负债各项目均有不同程度的碱少，尤 其是应彳寸账款从7282982^少到30600,是流动気命碱少的主 要原因。说明企业本期偿还了大量应付账款"短期负债等， 使流动比率大幅扌星高。

③分析谥产净利率变化原因 从数字上来看"二月份净利润略有下降，而平均资产有所上 升，共同作用引起资产净利率的降低。分析深层次原因，可 以发现，2月份营业收入勻营业成本相比1月份均有所下降， 鏡明企业销售下滑，盈利台幼诚弱。

七. 建立杜邦财务分析模型

1插入新的工作表，在新工作表中，通过公示，将杜邦分析所需的各项数据引入当前工作表， 如图： 2根据杜邦分析原理，设计杜邦分析基本框架，并在相应的单元格键入公式，建立杜邦分析 与各数据之间的联系。键入公式过程中，结合

Excel 内置函数（如INDEX 和控件、宏的使 用，以达到更好的效果。下图为设计好公式后， Excel 完成自动计算的结果（其中的组合框

控件可以选择月份）：

3、对比1 2月份杜邦分析结果，对权益净利率变动原因进行分析。

通过杜邦分析，比较一、二月份数据，可以发现二月份权益净利率的变动是因为总资产收益 率和权益成数都变小。总资产收益率的变小主要是因为总资产周转率的变化（从

58.94%减少

到42.81%），总资产周转率的变化主要原因则是流动资产的增加。可以认为权益净利率的变 化主要原因是流动资产的增加造成。

③通过观察获利能力分析图的几项获利能力指标可以发现，虽然销售毛利率较一月份有小幅 度提高，但资产报酬率、股东权益报酬率、销售净利率均下降，说明企业获利能力变弱，可 能影响企业的市场竞争能力。

3、调整格式，以使分析模型更加美观

偿债能力分析图

20 LE

10 5 0

■ 一月楷 ■

二月苗

1 ---------

i

1 --------

-i

i

------------

流动比率 速动比率 現金比率

资产负喷比 率 骰东权益比 率

[J 一月紛 L 553204156 L 129611977 (X 32305739S 0.601817419 a 398182581

■二月档 1& 35553112 10.5516653S 6. 33 9698S36 0. 06077668 0. ■93922312

从桧债能力分析图可叹看岀.企业删比率、速动 出翠、

现釧弊苴肓丈幅蹈帛高一说E 冃企业趣期伫 债能力大幅提高.说胡企业短期偿匿能力抱强.怛 迖些指标明显昙过她高.克明济动资产占用较 多，舎导致企业机会成本増如彩响疑营资金周转 效率和获利能巧”利E 傑障低超匕月供粕出无太 大支化，股东权益L 淳比一月份大嗨提高.黄产负 愤率、负愦股収比创和有刑鏗值债务率则光幅肆 ■1氐这几项指标的变代芽合说明债务相对干肢※权 益的减少.企业在当期信还丈量传务岳，企业长期 偿债能力増强*这说明愤収人敕益保囲程度较■葛， 冋幵企业来说，段有充的蝴财务杠杆来获得見

背运能力子卜析團

从管运能力分析團来看.存费周转率、应收欄周 转率，襦动资产周转率、总明产周转率，招比一月 祢 订几顶指机闻不同程废的眸低，说明企业存 货营理、1|姗谡屢等营运能力整肃咸弱*

0.6 0.5 0.4 6 3 0.2 0. 1

获利能力分析图

1

■一月曲 ■二

月恂

□ 二 JE

Eta= 社

资产报酬率

股东权益报酬率 销售毛刑率

销售浄刹率

□-月卅 O L 229134335 0.402&0531 6 4<00501.71 0.3&9(2 91793 ■三月忖

0. 15646040T7

a 2&4114544

?7522212415

0.365447109

通过观倉获禾能Z ）芳折圏的儿顶获和能山楷标町认 发现，虽轶雀警毛禾悴转一月恃有小幅度提高，e 资产拇酬率、股东祝益捋酗至、销窖淨刑率均F 降，说明企业蔬刑能力变弱”可能務响企业曲帀场 芫争能力.

|_|一月忖 ■二月悅

￡ 005弼品■

II

? ' ?

；ttl737.1

? ?;? ? J2*e? ? ?.? 5

amu ziJ

5 Liillll ?訂）?：*

?：? if

Q A 杜邦财务分析横型

:■复?a ?M -?<*?5U 53V. <：i. 直"“工丿 、=■么乂?

k ■長

t ?>e ：c-,??-<*?：'. a 厶 ? rt^ssM ??nn^ieg

a?**? x ^^-?s=-v?=二. ? ?*acrx*- 汁二鸟衣??二■?:▲罗HE 昱,?=!?■二巴*5

*々込

? :

. ￡? = Kh?c?rrjt- s.e t.? ?*3wK-v>>*T?*a? 9< xx * 杜邦财务分析模型

1

丄

~|

IZ

□\

“??3工:*\ 1 - ,5 +

x-?

X

”&、, ,?"

_匚

+

Lu.. 丁：二讥 J

??*■ z-i*xe :: 佃>：

? R G R - FFL OOS

JW JC H

?

II <3 A 出P 财务分析模型 B C

净贲产收益李：一戶份为48.531,二月份下降到26.411,下降幅度较大，通过分解可以明显地看 出.该企业净资产收芸率的妾动罡竇本结构（权益剩S ）支訪和贸产哥用效果（总费产净禾李）吏 动两个

万面共同作庄的效臬.而衣企业的忌费产净哥率太低.且示岀很签的资产利用效果.权益弦 数下降说明企业的痰本结构发生了变动.二月份权益巒较一月份有所减小.仅茲索數趣小.企业

杜邦财务分析模型

8 9_ 10 11 12

13 14 15 16 17 18 19 20

21 22 23 24 25 26

27 28

29

负債程虞題任，宦（I 祗力垃强.财务甌险有所降临.也同事反映了财务杠杆对利和水平的?形响. 总茨产目利率；涌￡：分解fij 以看岀二月份总罚产戍转率大幅茨下降王變斥由言业收入下PS,但是平 均贪产总额的上升.再猱入分解可看出彩响平均贸产上升的主戛捺因泉流动资产上升所适成的?说 明資产的刑用控制福弱?且示岀比上月较差的效果.茨明该企业利用苴总贪产产生销售收入的效李 在猱少总贺产周转军下陈的同时销3净刊率也裱少.18碑了总费产净刹睾的増加?

净资产收Q 率

4& 28%

26,41?

幕费产净利率

22,94#

净硼

1

A

甘业枚入

待业收入

271645&17

2148829 I

卞

$977948.72

5880000

6977% & 72

5380000

____ I

1 月宜5P!M?

谒 MiWt

苴用刮涸 I

匕如粉

◎ A 社茹财务分析抵型23

24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40勿42的认45 46 47關49 50 51

净利和

2716458.17 2148829

G I K 』

?MJ 1

営业收入

6977948.72 5^80000

俏色咬入全部成丰+亘他刊和

697T948.72 5880000 425S490.55弘8339124000 170000 50000 417780

营业成本

3907245,刘2809391 I

聊!费用

刘000 190000

管理费用

56137.99 150000

财势费用

10810T.】9 11Q000

资产减值摂失

10000 10000

营业税金及附加

7000Q 190000

営业外支出

HOOO24000

1月tmpiw I月冷利卿2月tmptw 2月冷仙f 比事分杆?a 肛a?越労分窑

去

> R G R - tf) ooslWQ ?x + II 社邦财务分析複型

福之州，青之运

5

6

7 第四童

8 9

10

11 12

1月粉利刑盂

1月旳资严负恃袤

13

対

15

2月佛产负嶺表 16

17 18

t 辱分析模率

据模率分析2月俯

19

20

趋酚析柱

杜弼財奔分祈模墜 21

2月佛产负嶺表

杜弗财奔分祈模塑

in

八. 建立目录，美化分析模型

1经过以上的分析，Excel 工作簿中已经建立了九张工作表。为使分析模型简单明了，特编 制目录，以方便模型使用者的使用。首先插入一张新的工作表，并命名为“目录”

。

2、 在目录页中插入自选图形，并建立目录页与各相关工作表的超链接，同时可以在各工作表 中插入命令控件，通过录制宏的方法，为控件指定宏，建立各页与“目录”之间的链接关系。

3、 调整各工作表格式等细节部分，使分析模型格式统一，设置背景图案，以达到更加良好的 视觉效果。

4、 保存分析模型，实验完成。

B CDEFGHI TKL

职业技术枝肓学院 财务管退

些 R 劭存 呼筍 OOSBafjds ■■ X +

IL - A 吐和财册析慣型

J 1 E L 1

H

Ch

P

良 S! T 11 I 丫 ■

23 甘业收人 ______ 24 申伽BL72 囲8000。

所潯税芟用

长期戈产

+

30 30000 41T7&3

24E3274.

225^3^. 32

31 羽 33 34 35 36 37 3? 39 Ju ■31 21 ______________________ 忙 曲 __________ 济动资产

9356531■閒呂 11449T39L06

甚池济动滇产

2332&S? 2fl32Sie. OSS

20關SfT 乩46- 1%丽虧?565

2493J2T. 725 3725^10?- 45

244&737. 5 2幻睨閒?76

￡5 46 騎 50

51 52

23 24

25

26

实验结果总结】

净资产收益率：一月份为48.53%，二月份下降到26.41%，下降幅度较大，通过分解可以明显地看出，该企业净资产收益率的变动是资本结构（权益乘数）变动和资产利用效果（总资产净利率）变动两个方面共同作用的效果，而改企业的总资产净利率太低，显示出很差的资产利用效果，权益乘数下降说明企业的资本结构发生了变动，二月份权益乘数较一月份有所减小，权益乘数越小，企业负债程度越低。偿债能力越强，财务风险有所降低，也同事反映了财务杠杆对利润水平的影响。

总资产净利率：通过分解可以看出二月份总资产周转率大幅度下降主要是由营业收入下降，但是平均资产总额的上升，再深入分解可看出影响平均资产上升的主要原因是流动资产上升所造成的，说明资产的利用控制减弱，显示出比上月较差的效果，表明该企业利用其总资产产生销售收入的效率在减少总资产周转率下降的同时销售净利率也减少，阻碍了总资产净利率的增加。

实验教材】