《Excel在财务分析中的应用》07Excel在财务分析中的应用

《Excel在财务会计中的应用》项目七Excel在财务分析中的应用工作描述财务分析可以揭示有关财务指标之间的关系、变动情况及其形成原因，从而将财务报表及相关数据转换为对特定决策有用的信息，以便报表使用者对企业的财务状况、经营成果及未来前景做出评价。本项目主要介绍如何使用Excel进行财务分析。工作目标掌握使用Excel进行比率分析的方法与步骤。掌握使用Excel进行比较分析的方法与步骤。掌握使用Excel进行综合分析的方法与步骤。知识目标技能目标锻炼独立思考、解决实际问题的能力。秉持专业精神，勤于学习，锐意进取。素质目标能够使用Excel的公式与函数计算财务比率。能够使用Excel的图表功能进行财务分析。工作资料为进行充分的财务分析，现将众诚有限责任公司（以下简称“众诚公司”）五年（2017年至2021年）的有关数据提供如下。【资料1】众诚公司2021年的资产负债表如表所示。资产负债表编制单位：众诚有限责任公司

2021年12月31日

单位：万元

资产期末

余额上年年末

余额负债和所有者权益

（或股东权益）期末

余额上年年末

余额流动资产：

流动负债：

货币资金

410

160短期借款

300

225交易性金融资产

30

60交易性金融负债

衍生金融资产

衍生金融负债

应收票据

应付票据

应收账款20301050应付账款

525

565资产负债表资产期末

余额上年年末

余额负债和所有者权益

（或股东权益）期末

余额上年年末

余额应收款项融资

预收款项

50

20预付款项

110

20合同负债

其他应收款

60

110应付职工薪酬

70

85存货

5951630应交税费

60

25合同资产

其他应付款

80

85持有待售资产

持有待售负债

一年内到期的非流动资产

225

0一年内到期的非流动负债

250

0其他流动资产

其他流动负债

140

50流动资产合计34603030流动负债合计14751055非流动资产：

非流动负债：

债权投资

150

225长期借款21951200其他债权投资

应付债券12001300长期应收款

其中：优先股

资产负债表资产期末

余额上年年末

余额负债和所有者权益

（或股东权益）期末

余额上年年末

余额长期股权投资

永续债

其他权益工具投资

租赁负债

其他非流动金融资产

长期应付款

250

300投资性房地产

预计负债

固定资产61904835递延收益

在建工程

50

125递延所得税负债

生产性生物资产

其他非流动负债

100

75油气资产

非流动负债合计37452875使用权资产

负债合计52203930无形资产

30

40所有者权益（或股东权益）：

开发支出

实收资本（或股本）

500

500商誉

其他权益工具

长期待摊费用

25

75其中：优先股

资产负债表资产期末

余额上年年末

余额负债和所有者权益

（或股东权益）期末

余额上年年末

余额递延所得税资产

永续债

其他非流动资产

15

0资本公积

80

50非流动资产合计64605300减：库存股

其他综合收益

专项储备

盈余公积

370

200

未分配利润37503650

所有者权益

（或股东权益）合计47004400资产总计99208330负债和所有者权益

（或股东权益）总计99208330【资料2】众诚公司2021年的利润表如表所示。利润表编制单位：众诚有限责任公司2021年12月

单位：万元项目本期金额上期金额一、营业收入1550014650减：营业成本1362012765税金及附加

140

140销售费用

110

110管理费用

230

200研发费用

财务费用

550

480其中：利息费用

550

480利息收入

加：其他收益

投资收益（损失以“−”号填列）

200

130其中：对联营企业和合营企业的投资收益

以摊余成本计量的金融资产终止确认收益（损失

以“−”号填列）

净敞口套期收益（损失以“−”号填列）

利润表项目本期金额上期金额公允价值变动收益（损失以“−”号填列）

信用减值损失（损失以“−”号填列）

资产减值损失（损失以“−”号填列）

资产处置收益（损失以“−”号填列）

二、营业利润（亏损以“−”号填列）

1050

1085加：营业外收入

50

85减：营业外支出

100

25三、利润总额（亏损总额以“−”号填列）

1000

1145减：所得税费用

320

375四、净利润（净亏损以“−”号填列）

680

770（一）持续经营净利润（净亏损以“−”号填列）

（二）终止经营净利润（净亏损以“−”号填列）

五、其他综合收益的税后净额

（一）不能重分类进损益的其他综合收益

1．重新计量设定受益计划变动额

利润表项目本期金额上期金额2．权益法下不能转损益的其他综合收益

3．其他权益工具投资公允价值变动

4．企业自身信用风险公允价值变动

……

（二）将重分类进损益的其他综合收益

1．权益法下可转损益的其他综合收益

2．其他债权投资公允价值变动

3．金融资产重分类计入其他综合收益的金额

4．其他债权投资信用减值准备

5．现金流量套期储备

6．外币财务报表折算差额

……

六、综合收益总额

680

770七、每股收益

（一）基本每股收益

（二）稀释每股收益

众诚公司营业利润表年度2017年2018年2019年2020年2021年营业利润（万元）10001150130010851050【资料3】众诚公司2017年至2021年的营业利润如表所示。【资料4】标准财务比率如表所示。标准财务比率表项目标准财务比率流动比率2.57速动比率1.76资产负债率0.49利息保障倍数2.45应收账款周转率8.59营业净利率0.05总资产净利率0.06知识库财务分析：根据企业财务报表及其相关资料，采用一定的方法，系统分析和评价企业财务状况、经营成果及未来发展趋势的过程。财务分析能够反映企业在一定时期运营过程中的利弊得失和发展趋势，从而为改进企业财务管理工作和优化经济决策提供重要的财务信息。财务分析的数据不仅种类繁多，还涉及不同时期、不同企业之间的比较，使用Excel进行财务分析，可以迅速、准确地得出各种财务分析数据。目录任务一比率分析任务二比较分析任务三综合分析工作考核任务一比率分析一、比率分析的指标二、比率分析的操作步骤比率分析：将财务报表中的有关项目进行对比，通过得出各项财务比率来揭示企业财务状况的一种财务分析方法。一比率分析的指标（一）短期偿债能力分析的指标短期偿债能力分析衡量的是企业流动资产对流动负债的清偿能力，借此，报表使用者可以评估企业在短期内能够转换为现金的流动资产的多少。短期偿债能力分析的指标流动比率速动比率财务比率企业流动资产的变现能力企业偿还流动负债的能力越高越强越强（一）短期偿债能力分析的指标1．流动比率计算公式如下：流动比率=流动资产/流动负债流动资产货币资金、交易性金融资产、应收票据、应收账款、预付款项、其他应收款、存货、持有待售资产、一年内到期的非流动资产、其他流动资产等流动负债短期借款、交易性金融负债、应付票据、应付账款、预收款项、应付职工薪酬、应交税费、其他应付款、持有待售负债、一年内到期的非流动负债、其他流动负债等流动比率表明每1元流动负债有多少流动资产作为偿债保障。通常，流动比率越大，企业的短期偿债能力越强。一般认为，生产企业合理的最低流动比率为2。（一）短期偿债能力分析的指标2．速动比率计算公式如下：速动比率=速动资产/流动负债速动资产流动资产中可以在较短时间内变现或已属于现金形式的资产，主要包括货币资金、交易性金融资产、应收票据、应收账款、其他应收款等。非速动资产包括存货、预付款项、持有待售资产、一年内到期的非流动资产、其他流动资产等。速动比率表明每1元流动负债有多少速动资产作为偿债保障。通常，速动比率越大，企业的短期偿债能力越强。一般认为，企业的速动比率以1为宜。（二）长期偿债能力分析的指标长期偿债能力分析衡量的是企业偿付到期长期债务的能力。借此，报表使用者可以判断企业的资本结构是否健全、合理。长期偿债能力分析的指标资产负债率产权比率权益乘数利息保障倍数（二）长期偿债能力分析的指标1．资产负债率计算公式如下：资产负债率=负债总额/资产总额×100%资产负债率反映企业的资产总额中有多大比例是通过举债而取得的。通常，资产负债率越低，企业的长期偿债能力越强。（二）长期偿债能力分析的指标2．产权比率计算公式如下：产权比率=负债总额/所有者权益总额×100%产权比率反映由债权人提供的资本与所有者提供的资本的相对关系，从而揭示企业的财务结构是否合理，以及所有者权益对债务的保障程度。通常，产权比率越低，企业的长期偿债能力越强。资产负债率侧重于分析债务偿付安全性的物质保障程度产权比率侧重于揭示财务结构的稳健程度及自有资本对偿债风险的承受能力（二）长期偿债能力分析的指标3．权益乘数计算公式如下：权益乘数=资产总额/股东权益总额=1+产权比率=1/（1−资产负债率）权益乘数表明股东每投入1元可实际拥有和控制的资产。通常，权益乘数越小，企业的长期偿债能力越强。提示资产负债率、产权比率和权益乘数三者之间同向变动。（二）长期偿债能力分析的指标4．利息保障倍数计算公式如下：利息保障倍数=息税前利润/应付利息息税前利润=税前利润+利息费用=净利润+利息费用+所得税费用应付利息：本期发生的全部应付利息，不仅包括财务费用中的利息费用，还包括计入固定资产成本的资本化利息。我国2018年修订后的利润表已将财务费用中的利息费用单列，因此报表使用者可以直接使用利润表中的“利息费用”项目进行计算和分析。利息保障倍数反映企业用经营所得支付债务利息的能力。该比率越高，企业用经营所得按时按量支付债务利息的能力越强，即企业的长期偿债能力越强。从长期看，利息保障倍数至少要大于1。（三）营运能力分析的指标营运能力分析衡量的是企业资产管理的效率。借此，报表使用者能够对企业的资产是否在有效运转、资产结构是否合理、资产是否能得到有效利用、资产总量是否合理等问题做出较为客观的判断。营运能力分析的指标应收账款周转率存货周转率流动资产周转率固定资产周转率总资产周转率1．应收账款周转率又称“应收账款周转次数”，计算公式如下：应收账款周转率=营业收入/应收账款平均余额应收账款平均余额=（应收账款期初余额+应收账款期末余额）/2应收账款周转率反映企业应收账款在一年内转换为现金的平均次数，表明企业应收账款的周转速度。通常，该比率越高，企业应收账款的变现能力越强，应收账款管理效率越高。用时间表示的应收账款周转速度是应收账款周转天数，反映企业从销售开始到收回现金（即应收账款周转一次）所需要的时间，其计算公式如下。应收账款周转天数=365/应收账款周转率（三）营运能力分析的指标提示在计算应收账款周转率时，应收账款应为未扣除坏账准备的金额。应收账款在财务报表中按净额列示，计提坏账准备会使财务报表中列示的应收账款金额减少，而营业收入不变。其结果是，计提的坏账准备越多，应收账款的周转速度越快、周转天数越少，这样对应收账款实际管理欠佳的企业反而会得出应收账款周转情况更好的错误结论。2．存货周转率计算公式如下：存货周转率=营业成本/存货平均余额存货平均余额=（存货期初余额+存货期末余额）/2存货周转率反映企业存货周转速度，是衡量和评价企业购入存货、投入生产、销售收回等各环节管理效率的综合性指标。通常，该比率越高，企业存货的周转速度越快、变现能力越强。用时间表示的存货周转速度是存货周转天数，反映企业从取得存货到存货销售（即存货周转一次）所需要的时间，其计算公式如下。存货周转天数=365/存货周转率（三）营运能力分析的指标3．流动资产周转率又称“流动资产周转次数”，计算公式如下：流动资产周转率=营业收入/流动资产平均余额流动资产平均余额=（流动资产期初余额+流动资产期末余额）/2流动资产周转率反映企业全部流动资产的周转速度与利用效率。通常，该比率越高，企业流动资产的周转速度越快、利用效率越高。用时间表示的流动资产周转速度是流动资产周转天数，其计算公式如下。流动资产周转天数=365/流动资产周转率（三）营运能力分析的指标4．固定资产周转率计算公式如下：固定资产周转率=营业收入/平均固定资产平均固定资产=（期初固定资产+期末固定资产）/2固定资产周转率反映企业固定资产的周转速度与利用效率。通常，该比率越高，企业固定资产的结构越合理，固定资产的利用效率越高。用时间表示的固定资产周转速度是固定资产周转天数，其计算公式如下。固定资产周转天数=365/固定资产周转率（三）营运能力分析的指标5．总资产周转率又称“总资产周转次数”计算公式如下：总资产周转率=营业收入/平均资产总额平均资产总额=（期初资产总额+期末资产总额）/2总资产周转率反映企业总资产的周转速度与利用效率。如果该比率较低，那么企业应采取措施提高营业收入或处置资产，以提高总资产的利用效率。用时间表示的总资产周转速度是总资产周转天数，其计算公式如下。总资产周转天数=365/总资产周转率（三）营运能力分析的指标（四）盈利能力分析的指标盈利能力分析衡量的是企业获取利润的能力。盈利能力分析的指标营业毛利率营业净利率总资产净利率净资产收益率1．营业毛利率计算公式如下：营业毛利率=营业毛利/营业收入×100%营业毛利=营业收入−营业成本营业毛利率表明每1元营业收入扣除营业成本后，有多少剩余金额可以用于补偿企业各项期间费用并形成盈利。营业毛利率反映企业通过销售获取利润的能力。通常，该比率越高，企业通过扩大销售获取收益的能力越强。（四）盈利能力分析的指标2．营业净利率计算公式如下：营业净利率=净利润/营业收入×100%营业净利率表明每1元营业收入带来净利润的多少，能够反映企业通过营业收入获取利润的能力。通常，该比率越高，企业获取利润的能力越强。（四）盈利能力分析的指标3．总资产净利率计算公式如下：总资产净利率=净利润/平均资产总额×100%平均资产总额=（期初资产总额+期末资产总额）/2总资产净利率表明每1元资产带来净利润的多少，能够反映企业利用全部资产获取利润的能力。通常，该比率越高，企业的投入产出水平越高，资产盈利能力越强。（四）盈利能力分析的指标4．净资产收益率计算公式如下：净资产收益率=净利润/平均净资产×100%平均净资产=（期初所有者权益总额+期末所有者权益总额）/2净资产收益率反映所有者权益的收益水平，能够衡量企业利用自有资本获取投资收益的能力。通常，该比率越高，企业投资带来的收益越高。（四）盈利能力分析的指标二使用Excel进行比率分析时，首先需要建立比率分析表，然后根据已知的资产负债表和利润表，设置各项财务比率的计算公式，最后根据得出的财务比率数据进行分析与评价。比率分析的操作步骤资产负债表利润表（一）建立比率分析表使用Excel建立比率分析表的具体操作步骤见教材。（二）设置比率分析表的计算公式1．设置偿债能力分析指标的计算公式2．设置营运能力分析指标的计算公式使用Excel建立比率分析表的具体操作步骤见教材。偿债能力分析

点击此处播放微课营运能力分析

点击此处播放微课（二）设置比率分析表的计算公式3．设置盈利能力分析指标的计算公式

点击此处播放微课盈利能力分析任务二比较分析比较分析：对两个或两个以上的可比数据进行对比，找出企业财务状况与经营成果中的差异与问题的一种财务分析方法。企业在一个会计年度中可能会发生一些偶然事项，这些偶然事项既不能直接代表企业的过去，也不能简单说明企业的未来。因此，只分析企业一个会计年度的财务数据往往不够全面。如果将企业不同时期的财务数据进行比较分析，或者与同类型企业进行比较分析，那么将有助于报表使用者明确企业存在的问题，判断企业的发展趋势，从而做出正确的决策。一、比较分析的方法二、比较分析的操作步骤比较分析的方法一根据比较对象的不同，比较分析可分为趋势分析、横向比较分析和结构百分比分析。（一）趋势分析趋势分析：通过对比两期或连续数期财务报表中的相同指标，确定其增减变动的方向、数额和幅度，以说明企业财务状况或经营成果变动趋势的一种比较分析方法。趋势分析通常以图表的形式来直观、清晰地展示各项财务指标的变动趋势。（二）横向比较分析横向比较分析：与同类企业的相关财务比率进行比较的一种比较分析方法。如果仅将本企业不同时期的财务比率数据进行比较，那么只能看出本企业的变化，无法得知本企业在市场竞争中所处的地位。如果将本企业的财务比率数据与同行业、同规模企业的财务比率数据进行比较，那么可以看出本企业与对方的区别，从而为发现问题与查找差距提供线索。行业平均水平的财务比率通常被作为比较的标准，即标准财务比率。横向比较分析的计算公式如下。横向比较比率=本企业财务比率−标准财务比率（三）结构百分比分析结构百分比分析：把常规的财务报表换算成结构百分比分析表，然后对企业的资产、负债、所有者权益、收入、费用和利润等项目的构成情况进行分析的一种比较分析方法。同一财务报表中不同项目的结构百分比分析的计算公式如下。结构百分比=部分/总体×100%通常情况下，资产负债表的“总体”：总资产，利润表的“总体”：营业收入。比较分析的操作步骤二（一）趋势分析的操作步骤下面根据已知的营业利润表对营业利润进行趋势分析，具体操作步骤见教材。营业利润表（二）横向比较分析的操作步骤下面根据表中的标准财务比率，以及任务一得出的众诚公司2021年的各项财务比率，具体操作步骤见教材。根据横向比较分析的结果，可以看出该企业与行业平均水平相差不大，说明该企业在行业中的竞争力尚可。该企业的会计人员与管理人员可以结合企业实际情况进一步分析差异原因，并提出改进措施。（三）结构百分比分析的操作步骤1．资产负债表的结构百分比分析在Excel中对资产负债表进行结构百分比分析的具体操作步骤见教材。下面以已知的资产负债表与利润表为数据源进行结构百分比分析。2．利润表的结构百分比分析在Excel中对利润表进行结构百分比分析的具体操作步骤见教材。结构百分比分析

点击此处播放微课任务三综合分析综合分析：系统地分析与评价企业各项财务指标的一种财务分析方法。单一财务指标只能反映企业某一方面的情况，并不能说明企业整体的财务状况，而将各项财务指标综合起来进行分析，可以帮助报表使用者对企业的财务状况和经营成果做出全面、合理的评价。一、综合分析的方法二、综合分析的操作步骤综合分析的方法一综合分析的方法有很多，常用的方法主要有沃尔评分法与杜邦分析法。（一）沃尔评分法沃尔评分法：把多项财务比率用线性关系结合起来，以此来评价企业的信用水平的一种分析方法。其具体分析步骤如下：先选择7种财务比率，即沃尔评分指标，分别给定其在总评价中所占的比重，总和为100分，然后确定标准比率，并与实际比率相比较，评出各项指标的得分，最后求出总评分。其中，标准比率应以本行业平均数为基础，并结合实际进行适当的调整。（一）沃尔评分法沃尔评分指标财务比率比重标准比率流动比率252净资产/负债251.5资产/固定资产152.5营业成本/存货108营业收入/应收账款106营业收入/固定资产104营业收入/净资产53（二）杜邦分析法杜邦分析法：利用各项财务比率之间的内在联系，对企业财务状况和经济效益进行综合系统分析评价的一种分析方法。杜邦分析法相关财务比率的关系式如下。

净资产收益率=净利润/平均净资产

=总资产净利率×权益乘数

=营业净利率×总资产周转率×权益乘数

杜邦分析法以净资产收益率为起点，以总资产净利率与权益乘数为基础，将有关财务比率按其内在联系加以排列，建立杜邦分析体系，重点揭示企业盈利能力和权益乘数对净资产收益率的影响，以及各相关指标间的相互影响和作用关系。（二）杜邦分析法杜邦分析体系（二）杜邦分析法提示净资产收益率反映了企业所有者投入资本的盈利能力，说明了企业筹资、投资、资产营运等各项财务管理的效率。不断提高净资产收益率是使所有者权益最大化的基本保证。因此，净资产收益率是企业所有者、经营者都十分关心的财务分析指标。净资产收益率的影响因素主要有营业净利率、总资产周转率和权益乘数。在对净资产收益率进行分解之后，可以分析出净资产收益率变化的具体原因，这样比只用一项综合性指标更能说明问题。二综合分析的操作步骤（一）沃尔评分法的操作步骤在Excel中使用沃尔评分法进行相关财务分析的具体操作步骤见教材。沃尔评分法

点击此处播放微课（二）杜邦分析法的操作步骤在Excel中使用杜邦分析法进行相关财务分析的具体操作步骤见教材。会计人员在工作中不仅要自觉遵纪守法，还要敢于制止违法乱纪行为。我国《会计法》第六章第四十六条规定：“单位负责人对依法履行职责、抵制违反本法规定行为的会计人员以降级、撤职、调离工作岗位、解聘或者开除等方式实行打击报复，构成犯罪的，依法追究刑事责任；尚不构成犯罪的，由其所在单位或者有关单位依法给予行政处分。对受打击报复的会计人员，应当恢复其名誉和原有职务、级别。”因此，会计人员应当坚决抵制任何违法乱纪行为，维护好国家利益和社会公众利益。修身笃学工作考核通过完成本项目的考核，学生能够掌握比率分析、比较分析和综合分析的具体方法，并且能够使用Excel进行财务分析。考核目的考核资料某公司为一家中小型工业企业，其财务分析的有关资料如下。【资料1】该公司2021年的资产负债表如表。资产负债表编制单位：×××公司2021年12月31日

单位：万元资产期末

余额上年年末

余额负债和所有者权益

（或股东权益）期末

余额上年年末

余额流动资产：

流动负债：

货币资金

900

800短期借款

2300

2000交易性金融资产

500

1000交易性金融负债

衍生金融资产

衍生金融负债

应收票据

应付票据

应收账款

1300

1200应付账款

1200

1000应收款项融资

预收款项

400

300预付款项

70

40合同负债

其他应收款

80

60应付职工薪酬

20

20存货

5200

4000应交税费

合同资产

其他应付款

80

80持有待售资产

持有待售负债

一年内到期的非流动资产

一年内到期的非流动负债

资产负债表资产期末

余额上年年末

余额负债和所有者权益

（或股东权益）期末

余额上年年末

余额其他流动资产

其他流动负债

流动资产合计

8050

7100流动负债合计

4000

3400非流动资产：

非流动负债：

债权投资

400

400长期借款

2500

2000其他债权投资

应付债券

长期应收款

其中：优先股

长期股权投资

永续债

其他权益工具投资

租赁负债

其他非流动金融资产

长期应付款

投资性房地产

预计负债

固定资产1400012000递延收益

在建工程

递延所得税负债

生产性生物资产

其他非流动负债

油气资产

非流动负债合计

2500

2000资产负债表资产期末

余额上年年末

余额负债和所有者权益

（或股东权益）期末

余额上年年末

余额使用权资产

负债合计

6500

5400无形资产

550

500所有者权益（或股东权益）：

开发支出

实收资本（或股本）10000

9000商誉

其他权益工具

长期待摊费用

其中：优先股

递延所得税资产

永续债

其他非流动资产

资本公积

2000

1600非流动资产合计1495012900减：库存股

其他综合收益

专项储备

盈余公积

1600

1200

未分配利润

2900

2800

所有者权益

（或股东权益）合计1650014600资产总计2300020000负债和所有者权益

（或股东权益）总计2300020000【资料2】该公司2021年的利润表如表所示。利润表编制单位：×××公司2021年12月

单位：万元项目本期金额上期金额项目本期金额上期金额一、营业收入2120018800一、营业收入2120018800减：营业成本1240010900减：营业成本1240010900税金及附加

1200

1080税金及附加

1200

1080销售费用

1900

1620销售费用

1900

1620管理费用

1000

800管理费用

1000

800研发费用

研发费用

财务费用

300

200财务费用

300

200其中：利息费用

300

200其中：利息费用

300

200利息收入

利息收入

加：其他收益

加：其他收益

投资收益（损失以“−”号填列）

300

300投资收益（损失以“−”号填列）

300

300利润表项目本期金额上期金额其中：对联营企业和合营企业的投资收益

以摊余成本计量的金融资产终止确认收益（损失

以“−”号填列）

净敞口套期收益（损失以“−”号填列）

公允价值变动收