第九章j《Excel在财务管理中的应用》北邮.docx
- 文档编号:24981980
- 上传时间:2023-06-03
- 格式:DOCX
- 页数:16
- 大小:990.13KB
第九章j《Excel在财务管理中的应用》北邮.docx
《第九章j《Excel在财务管理中的应用》北邮.docx》由会员分享,可在线阅读,更多相关《第九章j《Excel在财务管理中的应用》北邮.docx(16页珍藏版)》请在冰豆网上搜索。
第九章j《Excel在财务管理中的应用》北邮
第九章Excel在财务分析中的应用
1主要概念
财务分析——又称财务报表分析,是以收集、处理与企业财务状况和经营成果有关的信息为起点,以企业对外提供的财务报告为主要依据,结合财务相关环境资料,对企业已完成的经营成果进行考核,对企业目前财务状况进行评估,对企业未来的发展趋势进行预测,据以为相关决策者提供决策信息支持的系统分析、评判过程。
比较分析法——是对不同时期和空间的同质财务指标进行对比,以确定其增减差异,用以评价财务指标状况优劣的方法。
因素分析法——是指为深入分析某一指标,而将该指标按构成因素进行分解,分别测定各因素变动对该项指标影响程度的一种分析方法。
比率分析法——是通过计算互为相关的经济指标之间的相对数值,从而考察和衡量企业经营活动效果的分析方法。
杜邦财务分析体系——是一种比较实用的财务比率分析体系。
这种分析方法首先由美国杜邦公司的经理创造出来,故称之为杜邦财务分析体系。
这种财务分析方法从评价企业绩效最具综合性和代表性的指标——股东权益报酬率出发,层层分解至企业最基本生产要素的使用,成本与费用的构成和企业风险,从而满足通过财务分析进行绩效评价的需要,在经营目标发生异动时经营者能及时查明原因并加以修正,同时为投资者、债权人及政府评价企业提供依据。
2知识拓展
拓展1:
Excel财务分析7步走
第一步:
设计报表格式:
设计报表格式要以现行财务报表为基础,然后在项目上进行一些必要的调整,如增加变动成本、贡献毛益、固定成本、税息前利润、速动资产、长期资产等项目。
需要注意,如果采用自动填列财务数据的方法,调整的项目必须列于会计软件或Excel模板提供的报表之下,以免自动填列财务数据时对不上行次。
第二步:
针对主栏,增设必要栏目:
增设栏目包括本期数栏、上期数或计划数栏、行业平均数栏、比较栏。
比较栏可分绝对数与相对数,即差额和百分比。
当然,为了清晰起见,栏目也不要设置过多,可以将不同的比较内容置于同一工作簿的不同工作表中。
第三步:
针对主栏,确定分析指标:
在这一步,首先要注意指标的内容,可以根据企业的具体需要确定。
如经济效益综合指标体系、杜邦财务分析体系、破产测试比率体系,另外可以考虑增加利息保证倍数、现金比率、固定比率、固定长期适合率等指标。
其次应注意指标的位置,必须是在给定的报表格式之下。
在确定分析指标时,要遵循以下原则:
要根据各种分析目的确定指标;分析指标应与国际接轨,如美国邓氏公司的14种比率,美国《报表研究》年刊中登载的11种比率,再如国际著名评估机构的评估指标,都可纳入其中;不仅要考虑目前需要,而且要考虑未来需要,在财务指标上也应顺应时代进行创新;为了避免未来损失,一方面应该尽可能将需要的指标纳入其中,另一方面指标不能太多太滥,以免喧宾夺主。
第四步:
录入计算公式:
要迅速、及时地计算财务指标,就要事先将每个计算公式录入到某一个单元格中。
这里需要注意两个问题:
一是公式的位置,必须与要计算的指标在同一行;二是公式的写法,如果引用其他单元格的资料要用该单元格的地址来表示,至于用绝对单元格还是用相对单元格则视需要而定,一般说来用相对单元格即可。
第五步:
填列财务数据:
填列报表数据有两种方法,即手工录入和自动生成。
其中自动生成又分两种情况,一是利用电算会计报表,其基本作法是:
首先调出电算会计报表文件,然后另存为“.Excel”文件,接着保存、关闭,最后再用Excel调出所另存的文件即可。
二是利用数据库,其基本作法是:
首先在Excel下调出数据库文件,然后输入分析内容,最后另存下来。
需要说明的是,在Excel程序中,一般装有工业、商业、旅游业、运输业、金融业等几个行业财务报表模板,具体包括资产负债表、损益表、利润分配表、财务状况变动表。
如果手工输入,应该先打开某报表模板,然后录入原始数据,生成报表后,再复制到分析文件中。
这种方法的特点是无需计算流动资产、固定资产净值、资产总值、负债总额等合计栏,由系统自动生成。
但是由于财务状况变动表已被现金流量表所取代,故现金流量表需自行编制或自制模板。
如果是施工、房地产、外商投资等企业,由于系统未提供各种报表模板,企业只能自制。
第六步:
生成分析结果:
当完成了上述步骤后,各分析指标的结果就自动生成了。
如果为了直观,还可以将结构分析、趋势分析的结果在图表中反映出来。
第七步:
采取保护措施:
为了防止已输入单元格中的公式被误删、误改,或人为篡改,为了防止报表数据的泄密和修改,同时为了工作表下次继续使用,可以将分析表的格式即各项目单元格和带有公式的单元格设定密码保护,还可将公式予以隐藏。
具体做法是:
首先,将某些单元格排除在保护范围之外,即在“格式──单元格──保护”中对上述单元格不选择为“锁定”;然后,再对拟保护的带有公式的单元格设定为“锁定”、“隐藏”,方法同上;最后,再对工作表进行保护,即在“工具──保护──保护工作表”中选定对话框的“内容”等项,然后设定密码,这样就实现了保护。
当下次使用时,必须输入密码才能打开。
当然,对整个分析文件还可设定打开口令和修改权口令保护。
需要注意的是,如果原来设计的栏次、指标不敷应用,则可对财务报表进行调整,增加主栏或增加宾栏,追加分析项目。
但设定保护后,必须撤销对工作表的保护,才能修改,修改后应对文件重新命名。
拓展2:
财务分析模型的数据导入探析
1财务分析模型设计的数据源
1.1会计核算数据源
财务分析模型设计是以本单位会计核算资料为基础,用Excel等软件通过提取、加工和整理会计核算数据,生成财务分析模型设计所需的财务报表,再对其进行加工、整理,得出一系列科学、系统的财务指标,通过各种财务分析模型进行比较、分析和评价。
目前就我国会计电算化实施情况来看,会计核算电算化的环境不尽相同。
有的单位在单机上实现会计核算电算化,有的单位在网络环境中实现会计核算电算化,有的单位在客户/服务器环境中实现会计电算化,还有一部分单位仍处于手工会计核算状况。
我国会计核算环境大致可分为4种类型,见图1。
1.1.1手工会计核算环境。
会计核算工作仍处于手工状况,会计数据全部保存在手工账簿上。
1.1.2单机环境。
会计核算工作是在单机上进行,大部分会计数据保存在本地计算机中。
数据资源不能共享。
一般来说开展会计电算化的初期或业务量较小的单位,会计核算是在单机环境中进行的。
1.1.3网络环境。
会计核算工作是在由若干个工作站和网络服务器构成的网络环境中进行。
会计数据保存在网络环境中,能够满足对数据资源共享的需要。
因此,一些大中型企业和事业单位会计核算工作是在网络环境中进行的。
1.1.4客户/服务器环境。
会计核算工作是在由客户、服务器和连接件组成的客户&服务器体系结构的环境中进行的。
客户&服务器结构是在计算机网络技术和分布式计算的基础上形成的。
客户服务器结构除了具有数据资源共享方式的特点外,更能充分发挥服务器的作用,处理任务由客户和服务器双方分担,较高级、较复杂的任务由服务器完成,这样,客户端具备了过去高档机才能具备的计算能力。
目前大型企业和集团公司会计核算工作是在客户/服务器环境中进行的。
1.2辅助数据源
财务分析模型设计除了需要获取本单位的会计核算数据外,还需要获取本单位会计核算之外的数据,如同行业主要经营比率等,除本单位会计核算数据之外、可供财务分析模型设计使用的数据源,称之为辅助数据源,只有数据源提供最基础的数据,才能使财务分析模型设计成为可能,如何从这些外部数据源中获取数据,并使这些数据为财务分析模型所用,是财务分析模型设计中的一个关键问题。
在手工会计核算环境中,会计核算数据的获取可以通过人工输入的方式,将会计帐簿上的数据送入财务分析模型。
但在电算会计核算环境中,会计核算数据源的数据一般放在数据库文件中,这些数据库文件包括Dbase数据库文件、Oracle数据库文件Paradox数据库文件、SQLServer数据库文件等多种数据库文件。
从财务分析模型设计来看,应该能够从多种数据库中获取财务分析模型所需的数据。
在Windows环境中可以利用Excel的数据链接、MicrosoftQuery程序、ODBC开放式数据库链接、VisualBasic宏程序等技术和方法,自动、准确、及时地从各种会计核算数据源获取财务分析所需的数据。
目前财务分析数据所需的辅助数据大部分都没有实现计算机管理,一般刊登在杂志、报刊和文件上。
因此,辅助分析数据可以通过手工查阅杂志、报刊和文件等获取,然后输入财务分析模型。
但是,随着我国计算机网络通信技术的发展,随着信息公路的开通,财务分析模型设计所需的各种辅助数据都可以在信息公路上得到。
因此,财务分析模型可以根据信息公路提供的标准接口,获取和采集大量辅助信息。
2从数据库中获取数据的方法
2.1获取数据的基本概念
2.1.1EXCEL利用MicrosoftQuery从外部数据库中获取数据。
EXCEL可以直接调用MicrosoftQuery,MicrosoftQuery是能够从多种数据库源中检索数据的程序,它能自动调用ODBC驱动程序,从数据库中获取数据。
因此,在EXCEL中通过选择[获取外部数据]的命令,使EXCEL与Query通讯,Query可以与ODBC驱动程序通讯,ODBC又可与数据库通讯,实现直接从数据库中获取数据,其过程如图2所示。
2.1.2利用VBA直接与ODBC通信从外部数据库中获取数据。
利用VisualBasicForApplication,我们可以直接与ODBC驱动程序通信,跳过Query这一环节。
这时要做的是与驱动程序进行链接、发出检索数据的请求,然后将获取得的数据放入EXCEL中,其过程如图3所示。
发出检索数据的请求是通过运行宏程序来完成的。
2.2利用MicrosoftQuery从外部数据库获取数据
MicrosoftQuery是一种用于将外部数据源中的数据引入MicrosoftExcel的程序。
使用Query可以检索企业数据库和文件中的数据,而不必重新键入需要在Excel中进行分析的数据。
当数据库更新数据时,还可以根据原始数据库中的数据自动更新Excel中的数据。
电算化会计核算数据源的数据一般是以一定的组织形式存放于多个数据库文件中I如总账数据库文件(zz.mdb)存放会计核算总账数据、凭证数据库文件(pz.mdb)存放会计核算所需的凭证数据等。
在基于Excel财务报表分析模型中,从电算化会计核算数据源的数据库文件中获取数据的方法是:
(1)在包含资产负债表等财务报表的工作簿中增加一张新工作表,改名为zz。
选择[文件]菜单的[打开]命令,选择你已建立的包含财务报表的工作簿cwgl.xls;选择[插入]菜单的[工作表]命令增加一张空工作表,准备存放会计核算数据源的总账库文件(zz.mdb)的数据;单击鼠标右按,从快捷菜单上选择[重新命名]命令,把该空白表改名为zz;将活动单元变为A1单元。
(2)从电算化会计核算数据源获取数据。
选择[数据]菜单的[获取外部数据]子菜单的[新建数据库查询]命令,屏幕上显示出现“选择数据源”对话框。
(3)选择会计核算数据源的类型。
各单位会计核算数据源的数据库类型不尽相同,目前我国会计核算数据源的数据库有:
DBase数据库、Foxplus数据库、Foxpro数据库、Oracle数据库、Paradox数据库、SQLServer数据库等多种数据库。
因此,财务分析模型在获取数据时要根据本单位的会计核算数据源的类型选择相应的数据库类型。
如果有你需要的,则可以选择其中一种数据库源,如本例中总帐数据库文件(zz.ndb)是MicrosoftAccess数据库文件,则选择MSAccessDatabase;如果没有你需要的,则单击[新数据源]建立你所需的数据源类型。
(4)选择会计核算数据库文件。
选定数据库,可以从驱动器列表下、目录列表下、文件名列表下依次选择会计核算数据库文件所在驱动器、目录,最后选择数据库文件。
(5)从会计核算数据库中检索财务报表分析所需数据。
会计核算数据库文件(zz.mdb)的所有字段列在zz窗口中。
需要强调的是,不同的单位其会计核算数据库文件的结构不尽相同,作为财务分析模型的设计者必须事先掌握会计核算数据库的结构及各字段的含义,这样才能根据需要选择所需的字段。
Zz.mdb数据库各字段的含义是:
KMMC(科目名称);KMDM(科目代码);JDFX(借贷方向);KMNCYE(科目年初余额);KMQMYE(科目期末余额);BQFSE(本期发生额)。
选择所需的字段名时应双击,每双击一个字段名,该字段名被检索,并出现在查询表中。
(6)将选择的数据返回到财务分析模型的工作表中。
按照查询向导的提示逐步进行操作,最后一步将选择数据的保存方式,选择“将数据返回MicrosoftExcel”,并点击[完成]按钮,几秒钟后,你所需的总帐数据库的数据都返回到cwgl.xls工作簿的zz工作表。
之后,应用Excel动态数据链接技术,完成用于财务分析模型设计的财务报表数据的采集工作。
3案例分析
案例1:
EXCEL构建财务分析数据库
用友、金蝶等财务软件以及企事业其他信息系统的应用和普及,大大提高了中小企事业单位会计信息化程度,减轻了会计人员的负担,节约了财务成本。
但实务工作者也发现,财务软件更偏重于会计账务的处理及标准财务报表的提供,而分析功能不足,未提供良好的分析工具,分析方法也不够灵活,其他信息系统更偏重于业务处理。
这时就需要借助EXCEL等外部工具进行个性化的财务分析。
一、财务分析数据库的构建
用EXCEL建立财务分析数据库时,可以充分发挥其灵活性,从各个维度、各个层次对企事业单位数据进行分析。
尽管EXCEL2007已经解除了以前版本只能处理65536行数据的限制,但是也要考虑到数据库如果过于庞大,运行速度会降低。
因此,建立数据库时如果数据库偏大,可以将数据库拆分成几个小的数据库。
明确了数据库的构建目标后,就可以从不同地方收集财务分析数据。
这些数据既可以从用友、金蝶等财务软件获取,也可以从企事业单位的其他信息系统导入,如医院的HIS系统、药品管理系统、材料进销存系统等等。
导入的数据既可以是用友等财务软件整个科目数据,也可以是固定资产数据等等。
财务软件和其他信息系统一般都有导出成EXCEL电子表格的功能,如果这些信息系统软件可以导出成分隔符区分的文本文件,也可以通过EXCEL中的分栏工具进行拆分。
(一)基础数据字段整理
在进行基础数据整理时,经常会用到这些函数:
1.时期处理函数。
如根据日期提取年、月、周的数据:
YEAR(),MONTH(),WEEK()。
2.字符处理函数。
如提取字符串中的某一内容:
LEFT(),MID(),RIGHT()。
字符串联结:
CONCATENATE()。
3.数据格式转换函数。
如将数字强制转换成文本:
TEXT()。
4.逻辑判断函数。
如条件判断:
IF()。
信息提取:
ISNA(),ISTEXT()。
(二)数据的归类
财务分析时经常会对数据进行分类,这时可以借助函数VLOOKUP()来实现。
如对食品进行分类时,可以首先建立一张字典表,如表1。
然后在数据库里面在目标单元格输入函数vlookup()并设置函数参数,再对整个字段进行填充,就可以根据字段食品名称获取其对应分类了。
(三)数据库的定义
数据收集整理完后,可以在EXCEL里形成一张表单(sheet),可将有数据的区域定义为一张表,并为之命名。
本文以医院收入分析数据库为例,基础数据通过医院用友、HIS系统收集整理后,存放在一张名为“医疗收入”的表单内。
这时先选择所有数据区域,点击右键,选择“命名单元格区域”,命名为如“数据表”。
定义数据区域可以更方便地对数据库进行维护。
建立的数据库如图1。
二、财务数据的分析
数据库建立好之后,就可以充分利用EXCEL的数据分析挖掘工具,灵活方便地对数据进行分析。
下面介绍几种常用的分析方法。
(一)对比分析
建立的数据库如果包含了一段时期的数据并进行归类后,可方便地实现对比分析。
这里面需要用到EXCEL的数据透视表工具,首先将光标定位到“数据表”,单击“插入数据透视表”,并根据需要选择报表筛选字段、行标签、列标签字段以及值字段。
在作对比分析时,应该在列标签选择时间字段,并选择需要进行对比的时间段。
透视表建立后,可以通过报表等字段筛选数据的范围,例如既可以看全院收入,也可关注某一个或几个科室的收入分析。
同时,在邻近单元格建立公式对数据进行比较分析。
建立的数据对比分析如图2。
(二)趋势分析
趋势分析也是财务分析中的一个常用方法,分析人员可以从数据变化趋势看出数据的长期变化趋势及季节趋势等。
用EXCEL建立趋势分析时,也要建立数据透视表并选择要分析的字段。
作趋势分析时,可以辅以图片更能直观地进行分析。
将光标定位在数据透视表内,单击“插入折线图”或其他图形模板,并调整图形格式就可以制作出专业的图表。
如图3。
(三)结构分析
结构分析可以看出数据之间的构成分析。
在EXCEL里面作数据的结构分析时也可以通过建立数据透视表,选择要分析的数据字段进行分析。
同时,还可以制作数据饼图等进行直观的观察分析。
数据的结构分析如图4。
三、财务分析数据库的维护与更新
财务分析数据库建立完成后,可以根据个性需求灵活地对数据进行抽取和分析。
同时,这个数据库也是可以动态更新的。
更新数据库时,只需在数据表末端追加数据记录,数据库就会智能地将数据加入。
当然,这个数据库也支持对以前数据记录进行修改,批量修改数据时可以使用EXCEL的批量替换功能;具备支持增加和删减字段的功能。
修改数据库后,应确保增加的记录在数据表内可以通过扩大数据表区域来实现。
同步更新数据透视表和数据图时,只需要在任意表内,单击“更新数据源”,则所有基于数据库的透视表和数据图都可以同步进行更新,操作非常方便。
从上述研究可以看出,EXCEL具备灵活、简便而功能强大的数据分析和挖掘功能,而基于EXCEL数据库的维护和更新也非常简单,财务人员通过简单的操作,就可以从各个维度、范围对财务数据进行深入的分析。
当然,EXCEL还具备更多的数据分析潜能,需要财务人员在工作中发挥主动性和创造力,从而提高财务分析的效率和作用。
案例2:
财务比率分析的Excel应用
建电文华股份有限公司为一中小型工业企业,2005年度财务会计报告内容如图表1所示,以此作为本文应用案例,说明在财务比率分析中如何使用Excel来处理财务报表数据。
(一)建立“财务比率分析”表。
1.建立一空白工作表。
打开“2005年度财务会计报告”工作薄,左单击“插入”菜单,从弹出的下拉式菜单中选择“工作表”,则新建了一张空白工作表。
然后,将其调整到工作薄中最后一页。
2.对新工作表命名。
光标指向新工作表标签,右单击,从弹出式菜单中选择“重命名”,然后再在标签栏中输入“财务比率分析”名,按回车键即可。
3.编制“财务比率分析”表。
在“财务比率分析”表中,A1单元格输入“建电文华股份有限公司”;A2单元格输入“财务比率分析”。
然后,将A1B1选中,左单击工具栏中“合并居中”按钮;同理设置A2B2区域。
即成图表2形式。
如图表2所示项目,设置A3—A19区域的内容,最后设置表的显示格式(格式由设置者自定)。
这样建立了一张财务比率分析表。
(二)建立比率分析模型
1.变现能力比率
变现能力比率是企业产生现金的能力,它取决于可以在近期转变为现金的流动资金的多少。
①流动比率=流动资产/流动负债
②速动比率=(流动资产-存货)/流动负债
在“财务比率分析”表的B4单元格中,输入“=资产负债表!
C17/资产负债表!
F19”;B4单元格中,输入“=(资产负债表!
C17-资产负债表!
C12)/资产负债表!
F19”。
2.资产管理比率
资产管理比率是用平来衡量企业在资金管理方面效益的财务比率。
①存货周转率=主营业务成本/平均存货
②应收账款周转率=主营业务收入/平均应收账款
③流动资产周转率=主营业务收入/平均流动资产
④总资产周转率=主营业务收入/平均资产总额
在“财务比率分析”表的B7单元格中,输入“=利润表!
C5/((资产负债表!
B12+资产负债表!
C12)/2)”;B8单元格中,输入“=利润表!
C4/((资产负债表!
B8+资产负债表!
C8)/2)”;B9单元格中,输入“=利润表!
C4/((资产负债表!
B17+资产负债表!
C17)/2)”;B10单元格中,输入“=利润表!
C4/((资产负债表!
B34+资产负债表!
C34)/2)”。
3.负债比率
负债比率是指债务与资产、净资产的关系。
反映了企业偿付到期长期债务的能力。
①资产负债率=负债总额/资产总额
②产权比率=负债总额/股东权益
③有形净值债务=负债总额/(股东权益-无形资产净值)
在“财务比率分析”表的B12单元格中,输入“=资产负债表!
F27/资产负债表!
C34”;B13单元格中,输入“=资产负债表!
F27/资产负债表!
F33”;B14单元格中,输入“=资产负债表!
F27/(资产负债表!
F33-资产负债表!
C33)”。
4.盈利能力比率
盈利能力比率是企业赚取利润的能力。
①销售净利率=净利润/主营业务收入
②销售毛利率=(主营业务收入-主营业务成本)/主营业务收入
③净资产收益率=净利润/平均净资产
④资产净利率=净利润/平均资产总额
在“财务比率分析”表的B16单元格中,输入“=利润表!
C20/利润表!
C4;B17单元格中,输入“=(利润表!
C4-利润表!
C5)/利润表!
C4”;B18单元格中,输入“=利润表!
C20/
((资产负债表!
E33+资产负债表!
F33)/2)”;B19单元格中,输入“=利润表!
C20/((资产负债表!
B34+资产负债表!
C34)/2)”。
定义完成模型后,系统自动计算出对应的比率值(见图表3)。
(三)不同会计期间报表数据的应用
财务比率模型建立后,各会计期间均能使用。
如计算2006年度财务比率分析数据,可以通过以下操作来完成。
1.复制“2005年度财务会计报告”文档,并重命名为“2006年度财务会计报告”。
2.从财务软件或自己编制的报表中,将会计报告以Excel格式存盘。
3.将引出Excel格式的会计报告复制入“2006年度财务会计报告”中。
则在其“财务比率分析”工作表中,自动得到2006年度的财务比率分析数据。
Excel的应用说明,建立财务比率分析模型后,当资料数据有变化时,计算结果会随着财务比率模型自动地实现同步刷新,从而有效地实现Excel对财务数据的管理,提高财务比率分析的实际效率,充分体现Excel在财务比率分析中的实用性。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel在财务管理中的应用 第九 Excel 财务管理 中的 应用 北邮