(本科）第5章 财务分析Excel模型.pptx

《(本科）第5章 财务分析Excel模型.pptx》由会员分享，可在线阅读，更多相关《(本科）第5章 财务分析Excel模型.pptx（46页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、课程主讲人：第5章 财务分析Excel模型2本章本章简介简介财务分析是指以财务报告和其他资料为依据和起点，采用专门方法，系统分析和评价企业过去和现在的经营成果、财务状况及其变动，目的是了解过去、评价现在、预测未来，帮助利益关系集团改善决策。本章首先讨论Excel获取数据的方法、利用Excel2016建立财务分析基本方法模型的技术和方法，然后介绍利用Excel2016进行财务分析的一个具体应用实例杜邦分析。3主要主要内容：内容： 5.1 Excel获取数据的方法 5.2 比率分析法 5.3 杜邦分析4 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法 利用Excel丰富的函数及图

2、表等功能，可以根据需要对公司的财务数据进行综合分析，而这些分析都需要获取会计核算数据及相关的辅助数据。这些数据可能存放在外部文件或数据库中，也可能存放在其它工作簿中，还可能存放在其它工作表中。 根据数据来源不同，Excel获取数据的方法包括三种，即：从外部数据库或文件中获取数据；从其他工作簿获取数据；从从外部数据库或文件中获取数据；从其他工作簿获取数据；从本工作簿中的其他工作表获取数据。本工作簿中的其他工作表获取数据。5 Excel可以读取的文件格式有很多种，归纳起来，常用的主要有如下几种类型：Excel自身当前和以前版本的文件。一般文本文件，即TXT格式文件和用“，”作为数据项分隔符的文本文

3、件，或称CSV格式文件以及打印文件（*.prn）。Access、Dbase等数据库文件。其他电子表格软件制作的文件，如Lotus1-2-3等。网页文件，即*.htm、*.html、*.mht、*.mhtml文件等。查询文件，即*.iqy、*.dqy、*.rqy文件。Xml文件（*.xml） 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法6利用Microsoft Query从外部获取数据Excel可以直接调用Microsoft Query，Microsoft Query是能够从外部多种数据源中检索数据的程序，它能自动调用ODBC驱动程序，从数据库中获取数据。利用VBA直接与OD

4、BC通信从外部获取数据利用VBA（Visual Basic for Application），可以直接与ODBC驱动程序通信，跳过Microsoft Query这一环节。这时要做的是与驱动程序进行链接、发出检索数据请求，然后将获得的数据放入Excel中。 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法7文本格式是通用的文件格式。如果你不了解其存储格式，Excel仍不能正确地将其读出，除非有明显的分隔符号（如“，”、“|”等）来划分字段。 （1）从外部文本文件获取数据 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法8（1）从外部文本文件获取数据 这里的每一行是

5、一笔分录，它们的含义并非一目了然。要正确了解其内容，必须了解其格式，其字段定义如表5-1所示： 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法9（1）从外部文本文件获取数据具体操作步骤如下。 选择【数据】菜单中的【获取外部数据】子菜单下的【自文本】命令。弹出相应的对话框，选定欲导入的文本文件所在的文件夹和文件名，单击【导入】按钮，弹出【文本导入向导第1步，共3步】对话框，如图5-1所示。 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法10（1）从外部文本文件获取数据 单击【下一步】按钮，出现【文本导入向导第2步，共3步】对话框，选择“分隔符号”栏中的“空格”

6、，预览分析结果分为3列，如图5-2所示；图5-2 【文本导入向导第2步，共3步】对话框 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法11（1）从外部文本文件获取数据 单击【下一步】按钮，出现【文本导入向导第3步，共3步】对话框，单击第1列，出现反白显示，单击各列数据，设置各列的“列数据格式”， 如图5-3所示；图5-3 【文本导入向导第3步，共3步】对话框 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法12（1）从外部文本文件获取数据单击【完成】按钮，选择数据在工作表中存放的起始位置后，单击【确定】按钮，出现结果如图5-4所示。至此，文本文件导入完毕。但也

7、出现明显的错误，单元格B3中的“1234”为结算方式编码，应为单独一列，由于定义时只以空格为分界符，所以Excel不能明确加以判断而导致这种结果。 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法13（2）从外部数据库文件获取数据实际工作中，很多企业都应用了会计核算软件，比如用友、金蝶等。进行财务分析所需的会计核算数据及相关的辅助数据来源于会计软件相应的数据库。Excel可以利用Microsoft Query引擎，自动获取这些外部数据库文件数据，而不需在Excel中重新输入数据。 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法14（2）从外部数据库文件获取数据

8、例：从Access数据库“D:帐务处理和报表.ACCDB”中将表BZ_KMK的数据导入Excel工作表。图5-6 Access表结构及数据 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法15（2）从外部数据库文件获取数据从外部数据库文件中获取数据的步骤如下。单击【数据】菜单上的【获取外部数据】子菜单，然后单击【自其他来源】下的【来自Microsoft Query】命令，启动Microsoft Query，出现【选择数据源】对话框，如图5-7所示。图5-7 【选择数据源】对话框 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法16（2）从外部数据库文件获取数据在

9、指定的数据库文件中选择欲导入的数据。使用Microsoft Query可以检索多种类型数据库中的数据，包括Microsoft Access、Microsoft SQL Server和OLAP Services等等。我们在这里选择数据库中的“MS Access Database*”，按【确定】按钮，出现【选择数据库】对话框，如图5-8所示。 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法17（2）从外部数据库文件获取数据选择D盘下的“帐务处理和报表.mdb”数据库，按【确定】按钮，出现【查询向导选择列】对话框，如图5-9所示。选择BZ_KMK，按照向导进行操作。图5-9 【查询

10、向导选择列】对话框 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法18（2）从外部数据库文件获取数据数据返还给Excel工作表。在查询向导即将完成查询时，如图5-10所示。在“请确定下一步的动作”栏中选择“将数据返回Microsoft Office Excel”，单击【完成】按钮，出现数据返回位置对话框，如图5-11所示。 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法19（2）从外部数据库文件获取数据选择数据存放位置后，单击【确定】按钮，导入结果如图5-12所示。图5-12 从Access数据库导入数据结果 5.1 5.1 ExcelExcel获取数据的方

11、法获取数据的方法20（3）从外部网页获取数据用户可以通过单击一个按钮，即可从网页站点上直接获取数据（如最新的股票报价信息），将其导入到Excel工作表中进行分析，并能在以后更新数据，从而与网页上的最新数据保持一致。Excel除了可以从外部数据库和文本文件中导入数据外，还可以从网页导入所需要的数据，并且可以通过设置刷新频率实现数据的自动更新。 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法21（3）从外部网页获取数据例：从网易财经频道导入海尔股份股票行情信息，可按以下方法进行操作。在需要导入数据的工作表中，选择【数据】菜单中【获取外部数据】子菜单下的【自网站】命令，弹出【新建W

12、eb查询】对话框一，如图5-13所示。图5-13 【新建Web查询】对话框一 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法22（3）从外部网页获取数据在该对话框的地址栏输入海尔股份股票行情的Web地址，然后单击【转到】按钮，便会打开该网页，如图5-14所示。图5-14 【新建Web查询】对话框二 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法23（3）从外部网页获取数据用鼠标单击窗口右上角的【选项】按钮，出现Web查询选项对话框，单击选中其中的“完全HTML格式”选项，并单击【确定】按钮返回图5-14所示的【新建Web查询】对话框二。图5-14 【新建We

13、b查询】对话框二 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法24然后，单击海尔股份资产负债表数据区域左上角报告日期那一栏的 按钮，此时该按钮会变成 按钮，表示已选中该数据区域。（3）从外部网页获取数据 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法25（3）从外部网页获取数据选中数据区域后，单击【导入】按钮，出现【导入数据】对话框，如图5-15所示。图5-15 【导入数据】对话框 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法26（3）从外部网页获取数据设置数据的存放位置，然后单击【属性】按钮，出现【外部数据区域属性】对话框，如图5-1

14、6所示。图5-16【外部数据区域属性】对话框 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法27（3）从外部网页获取数据在该对话框中可以设置某些重要属性，如刷新频率等。设置完毕后单击【确定】按钮返回图5-15所示对话框，接着在该对话框中单击【确定】按钮。稍等片刻，海尔股份资产负债表报告日期那一列的信息便出现在工作表中。同理，重复，把图5-14中资产负债表各年度数据都导入工作表中。 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法28Excel可以从其它工作簿获取单元格数据。获取数据的方法为，在需获取其它工作簿数据的单元格输入“=路径被取数的工作簿名.xlsx工

15、作表名!单元格名称”。注意：被取数的工作簿可以打开，也可以不打开；当两个工作簿都打开时，被取数的工作簿的数据发生改变，取数工作簿中的数据立即更新；当取数工作簿关闭时更改了被取数工作簿中的数据，取数工作簿在下次打开时将询问是否更新数据。 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法29Excel从其它工作表中获取数据是指从同一工作簿的其他工作表中获取数据。获取数据的方法为，在需获取其它工作表数据的单元格输入“=被取数的工作表名!单元格名称”。注意：被取数的工作表数据改变，取数工作表立即自动更新。 5.1 5.1 ExcelExcel获取数据的方法获取数据的方法30主要主要内容

16、：内容： 5.1 Excel获取数据的方法 5.2 比率分析法 5.3 杜邦分析31 5.2 5.2 比率比率分析法分析法 比率分析法是将比率分析法是将企业同一时期的财务报表中的相关项目企业同一时期的财务报表中的相关项目进行对比进行对比, ,得出一系列财务比率得出一系列财务比率, ,以此来揭示企业的财务状况以此来揭示企业的财务状况的分析方法的分析方法. .32 5.2 5.2 比率比率分析法分析法变现能力比率变现能力比率( (短期偿债能力短期偿债能力) )资产管理比率资产管理比率( (营运能力营运能力) )负债比率负债比率( (长期偿债能力长期偿债能力) )盈利能力盈利能力流动比率流动比率2:

17、12:1速动比率速动比率1:11:1营业周期营业周期存货周转率存货周转率( (天数天数)应收帐款周转率应收帐款周转率( (天数天数)流动资产周转率流动资产周转率 产权比率产权比率有形净值债务率有形净值债务率已获利息倍数已获利息倍数资产负债率资产负债率销售销售净净利率利率销售销售毛毛利率利率资产净利率资产净利率净资产收益率净资产收益率流动资产流动资产/ /流动负债流动负债( (流动资产流动资产- -存货存货)/)/流动负债流动负债存货周转天数存货周转天数+ +应收帐款周转天数应收帐款周转天数销售成本销售成本/ /平均存货平均存货 (360/(360/存货周转率存货周转率) )销售收入销售收入/

18、/平均应收帐款平均应收帐款 (360/(360/应收帐款周转率应收帐款周转率) )销售收入销售收入/ /平均流动资产平均流动资产总资产周转率总资产周转率 销售收入销售收入/ /平均资产总额平均资产总额负债总额负债总额/ /资产总额资产总额负债总额负债总额/ /股东权益股东权益负债总额负债总额/(/(股东权益股东权益- -无形资产净值无形资产净值) )息税前利润息税前利润/ /利息费用利息费用净利净利/ /销售收入销售收入( (销售收入销售收入- -销售成本销售成本)/)/销售收入销售收入净利净利/ /平均资产总额平均资产总额净利净利/ /平均净资产平均净资产33 5.2 5.2 比率比率分析法

19、分析法比率分析法主要是以资产负债表、利润表等财务报表为依据，对其各项目计算出各种比率，从而进行比率分析，揭示企业的财务状况。要建立通用的比率分析模型，主要完成两项工作：一是与企业财务报表数据建立动态数据链接，财务报表数据一旦发生变化，比率分析模型的数据就随之发生相应的变化；二是在动态数据源的基础之上，建立各种财务分析比率模型。34 5.2 5.2 比率比率分析法分析法财务比率公式的定义方法：1.直接用单元格定义公式 优点：简单 缺点：公式不直观、不容易理解2.给单元格起名字，用名字定义公式 优点：a、使公式更加直观和容易理解 b、每个工作薄可以共用一组名字。 c、公式定义不受工作表结构变化的影

20、响。如果工作表结构有所变化，只需重新定义某单元格的名字，其它所有使用该名字的公式都会自动更新其值。35 5.2 5.2 比率比率分析法分析法（1）根据各财务分析比率的公式中所用数据的含义，）根据各财务分析比率的公式中所用数据的含义，对各报表的数据单元格定义适当的名字。对各报表的数据单元格定义适当的名字。 （2）定义财务比率的计算公式。）定义财务比率的计算公式。图5-18 流动比率计算公式定义36 5.2 5.2 比率比率分析法分析法 比率分析模型设计完成之后，各种比率分析数据便自动计算比率分析模型设计完成之后，各种比率分析数据便自动计算出来。出来。 当会计期间发生变化时，由于会计核算数据源的数

21、据库与财当会计期间发生变化时，由于会计核算数据源的数据库与财务分析模型中的各财务报表的工作表之间建立了数据库与工作表务分析模型中的各财务报表的工作表之间建立了数据库与工作表的数据链接，比率分析模型工作表与各报表工作表建立了工作表的数据链接，比率分析模型工作表与各报表工作表建立了工作表与工作表之间的动态数据链接，所以会计核算数据源数据库的数与工作表之间的动态数据链接，所以会计核算数据源数据库的数据发生变化，各财务报表工作表数据将被自动更新，比率分析公据发生变化，各财务报表工作表数据将被自动更新，比率分析公式的比值等数据将会随之自动作相应的更新。式的比值等数据将会随之自动作相应的更新。 37 5.

22、2 5.2 比率比率分析法分析法图5-20 海尔股份公司核心指标分析模型结果 我们也可以根据导入的海尔股份2006年12月31日至2019年12月31日的报表数据，建立海尔股份财务指标分析模型，此时，建议直接用单元格定义公式的方法来计算各个财务指标值。根据公式计算的结果如图5-20所示。38 5.2 5.2 比率比率分析法分析法 比率分析模型设计完成之后，各种比率分析数据便自动计算出来。当会计期间发生变化时，可以通过刷新数据来更新财务分析模型中的各财务报表数据。由于比率分析模型工作表与各报表工作表建立了工作表与工作表之间的动态数据链接，所以网页上的报表数据发生变化，各财务报表工作表数据将被自动

23、更新，比率分析公式的比值等数据将会随之自动做相应的更新。39主要主要内容：内容： 5.1 Excel获取数据的方法 5.2 比率分析法 5.3 杜邦分析40 5.3 5.3 杜邦分析 杜邦分析法就是利用主要的几种财务比率之间的关杜邦分析法就是利用主要的几种财务比率之间的关系来综合地分析企业的财务状况。系来综合地分析企业的财务状况。 权益净利率权益净利率=资产净利率资产净利率权益乘数权益乘数41 5.3 5.3 杜邦分析 权益净利率权益净利率是一个综合性极强、最有代表性的财务比率，是一个综合性极强、最有代表性的财务比率，它是它是杜邦系统的核心杜邦系统的核心。 权益净利率权益净利率反映了所有者投入

24、资金的获利能力反映了所有者投入资金的获利能力，它这一，它这一比率反映了比率反映了企业筹资、投资和生产运营等各方面经营活动的企业筹资、投资和生产运营等各方面经营活动的效率。效率。 权益净利率权益净利率取决于企业资产报酬率和权益乘数。资产报取决于企业资产报酬率和权益乘数。资产报酬率主要反映企业在运用资产进行生产经营活动的效率如何，酬率主要反映企业在运用资产进行生产经营活动的效率如何，权益乘数反映企业的筹资情况，即企业资金的来源结构。权益乘数反映企业的筹资情况，即企业资金的来源结构。42 5.3 5.3 杜邦分析 权益乘数权益乘数表示表示企业的负债程度企业的负债程度，通常的财务比率是除数，通常的财务

25、比率是除数，除数的倒数叫乘数。权益除以资产是资产权益率，除数的倒数叫乘数。权益除以资产是资产权益率，其倒数就其倒数就是权益乘数，即资产除以权益。是权益乘数，即资产除以权益。 权益乘数权益乘数=资产总额资产总额/股东权益总额股东权益总额=1/（1-资产负债率）资产负债率） 公式公式中的中的资产负债率是指全年平均资产负债率资产负债率是指全年平均资产负债率，它是企，它是企业业全年平均负债总额与全年平均资产总额的百分比全年平均负债总额与全年平均资产总额的百分比。 从公式中可知，从公式中可知，权益乘数主要受资产负债比率的影响权益乘数主要受资产负债比率的影响。负债比例大，权益乘数就高，说明企业有较高的负债

26、程度，负债比例大，权益乘数就高，说明企业有较高的负债程度，能给企业带来较大的杠杆利益，同时也给企业带来较大的风能给企业带来较大的杠杆利益，同时也给企业带来较大的风险。险。 43 5.3 5.3 杜邦分析 资产净利率是资产净利率是销售净利率和总资产周转率的乘积销售净利率和总资产周转率的乘积，可以，可以从企业的销售活动和资产管理两个方面来进行分析。从企业的销售活动和资产管理两个方面来进行分析。 销售净利率销售净利率=净利润净利润/销售销售收入，收入，提高提高销售净利率销售净利率：（1 1）开拓市场，增加销售收入）开拓市场，增加销售收入（2 2）加强成本费用控制，降低耗费，增加利润。）加强成本费用控

27、制，降低耗费，增加利润。资产管理方面资产管理方面：（1 1）资产结构是否合理）资产结构是否合理（2 2）结合销售收入，分析企业的资产周转情况）结合销售收入，分析企业的资产周转情况44 5.3 5.3 杜邦分析定义各报表数据单元格的名字。 将财务比率进行分解，定义各财务指标的计算公式。 图5-22 杜邦分析指标计算公式定义451仿照课本本章内容，建立“pz.txt”和“pz.csv”两个文本文件，输入部分数据，要求：将这两个文件的内容引入到Excel中。2建立数据库查询，将Microsoft Office中Access示例数据库Northwind.mdb（一般安装在Program FilesMicrosoft OfficeOfficeSamples文件夹中）的部分表数据引入到Excel工作表中。3建立WEB查询，地址栏中输入“http:/