EXCEL在财务中的应用tiw.pptx

《EXCEL在财务中的应用tiw.pptx》由会员分享，可在线阅读，更多相关《EXCEL在财务中的应用tiw.pptx（100页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、陈海烽Excel在财务中的高级应用Excel在财务中的应用主讲内容：主讲内容：一一一一Excel在财务中的应用基础；在财务中的应用基础；二二二二公式的编辑；公式的编辑；公式的编辑；公式的编辑；三三三三函数的运用；函数的运用；函数的运用；函数的运用；五五五五应用应用应用应用EXCELEXCEL完成财务会计工作；完成财务会计工作；完成财务会计工作；完成财务会计工作；六六六六财务函数的应用；财务函数的应用；财务函数的应用；财务函数的应用；七七七七应用财务函数完成财务管理工作；应用财务函数完成财务管理工作；应用财务函数完成财务管理工作；应用财务函数完成财务管理工作；四四四四数组公式和函数的嵌套；数组公

2、式和函数的嵌套；数组公式和函数的嵌套；数组公式和函数的嵌套；Excel在财务中的应用1、Excel在财务中的应用基础；在财务中的应用基础；n1.1、代码化（数据标准化）n1.2、日期、分数的输入n1.3、引用的应用n1.4、数据清单n1.5、工作表的保护及安全打开Excel在财务中的应用1.1、代码化（数据标准化）n以电子计算机为计算工具处理财务数据的基本概念。n代码化对系统原始数据按一定的规律进行编码的处理。数据处理代码化，缩短了数据项的长度，减少数据占用的存储空间，提高会计数据处理的速度和精度。方便计算机进行排序、分类、汇总等操作。n输入方式：+代码；如：现金科目代码输入方式：1001Ex

3、cel在财务中的应用1.2、日期、分数的输入n日期在EXCEL中其实质为数字n1900-1-1代表1n分数与日期输入n5/8在EXCEL中被识别为5月8日n分数5/8的输入方式：0+空格+5/8Excel在财务中的应用1.3、引用的使用；n引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿中不同工作表上的单元格和其他工作簿中的数据。引用不同工作簿中的单元格称为链接。n相对引用n绝对引用（混合引用）n链接n名称Excel在财务中的应用1.3、引用的使用；n相

4、对引用-在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。随公式位置的改变，其引用的单元格也会相应发生变化。n绝对引用-复制公式时Excel不调整引用，如$C$1。包括绝对引用单元格的公式，无论将其复制到什么位置，总是引用特定的单元格。n链接-引用不同工作簿中的单元格。n每次按F4键时，Excel会在以下组合间切换：绝对列与绝对行（例如，$A$1）；相对列与绝对行(A$1)；绝对列与相对行($A1)以及相对列与相对行(A1)，当切换到用户所需的引用时，按回车键确认即可。Excel在财务中的应用1.4动态引用nOFFSET函数可以对动态数据进行查询，它以指定的引用为参

5、照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数，其语法格式如下：nOFFSET(reference,rows,cols,height,width)n可以看出，该函数最多包含五个参数，后两个参数为可选项。其中：Reference作为偏移量参照系的引用区域，它必须为对单元格或相连单元格区域的引用；否则，函数OFFSET返回错误值#VALUE!。Rows为相对于偏移量参照系左上角单元格，上（下）偏移的行数。Cols为相对于偏移量参照系左上角单元格，左（右）偏移的列数。Heigh为高度，即所要返回的引用区域的行数。Width为宽度，即所要返回的引用

6、区域的列数。Excel在财务中的应用1.4、数据清单n在Excel中，数据库是作为一个数据清单来看待。我们可以理解数据清单就是数据库。在一个数据库中，信息按记录存储。每个记录中包含信息内容的各项，称为字段。Excel提供了一整套功能强大的命令集，使得管理数据清单（数据库）变得非常容易。我们可以完成下列工作：排序在数据清单中，针对某些列的数据，我们可以用数据菜单中的排序命令来重新组织行的顺序。可以选择数据和选择排序次序，或建立和使用一个自定义排序次序。筛选可以利用“数据”菜单中的“筛选”命令来对清单中的指定数据进行查找和其它工作。一个经筛选的清单仅显示那些包含了某一特定值或符合一组条件的行，暂时

7、隐藏其它行。数据记录单一个数据记录单提供了一个简单的方法，让我们从清单或数据库中查看、更改、增加和删除记录，或用你指定的条件来查找特定的记录。自动分类汇总利用“数据”菜单的“分类汇总”命令，在清单中插入分类汇总行，汇总你所选的任意数据。当你插入了分类汇总后，MicrosoftExcel自动为你在清单底部插入一个“总计”行。Excel在财务中的应用Excel数据库表格遵循的准则n建立和使用Excel数据库表格时，用户必须遵循以下的基本准则：n（1）一个数据库最好单独占据一个工作表，避免将多个数据库放到一个工作表上。n（2）数据记录紧接在字段名下面，不要使用空白行将字段名和第一条记录数据分开。n（

8、3）避免在数据库中间放置空白行或空白列，任意两行的内容不能完全相同。n（4）避免将关键数据放到数据库左右两侧，防止数据筛选时这些数据被隐藏。n（5）字段名的字体、对齐方式、格式、边框等样式，应当与其他数据的格式相区别。n（6）条件区域不要放在数据库的数据区域下方。因为用记录单添加数据时，Excel会在原数据库的下边添加数据记录，如果数据库的下边非空，就不能利用记录单添加数据。Excel在财务中的应用2、公式的编辑；n公式的组成：运算单元、运算符、函数及参数、引用、常数、文本、时间等n公式中元素的结构或次序决定了最终的计算结果。Excel中的公式遵循一个特定的语法或次序：最前面是等号（=），后面

9、是参与计算的元素（运算数），这些参与计算的元素又是通过运算符隔开的。每个运算数可以是不改变的数值（常量数值）、单元格或引用单元格区域、标志、名称、或工作表函数。Excel在财务中的应用2.1公式中的运算符（1）算术运算符：完成基本的数学运算。（2）比较操作符：比较运算符用于比较两个值。当用操作符比较两个值时，结果是一个逻辑值，不是TRUE就是FALSE。（3）文本运算符：使用和号（&）可以将文本连接起来。（4）引用操作符：引用运算符的作用是确定在公式中需要参与运算的数据在工作表中所处的位置，可以使用三个运算符：冒号、逗号和空格。Excel在财务中的应用2.1公式中的运算符：（冒号）：区域运算符

10、，对两个单元格之间，包括两个单元格在内的所有单元格参与运算。，（逗号）：联合运算符，可将多个引用合并为一个引用。空格：交叉运算符，它是将同时属于两个引用的单元格区域进行引用，即两个单元格引用相重叠的区域。Excel在财务中的应用2.2运算符及其优先级类别运算符运算功能优先级引用:区域运算符，用于引用单元格区域。1引用,联合运算符，用于将多个引用合并。2引用空格交叉运算符，用于引用两个单元格区域的重叠部分。3算术()括号4算术-负号5算术%百分号6算术乘方7算术*和/乘法和除法8算术+和加法和减法9文本&文本连接10逻辑=、=、等于、小于、大于、小于等于、大于等于、不等于11Excel在财务中的

11、应用2.3公式审核及出错检查1追踪引用单元格【工具】【公式审核】【追踪引用单元格】【工具】【公式审核】【取消所有追踪箭头】2追踪从属单元格【工具】【公式审核】【追踪从属单元格】【工具】【公式审核】【取消所有追踪箭头】3公式审核工具栏【工具】【公式审核】【显示“公式审核”工具栏】Excel在财务中的应用2.4常见的公式错误信息错误信息错误原因#DIV/0!公式的除数为零#N/A内部函数或自定义工作表函数中缺少一个或多个参数#NAME?不能识别的名字。#NULL!指定的两个区域不相交。#NUM!在需要数字参数的函数中使用了不能接受的函数#REF!公式中引用了无效的单元格。#VAULE!参数或操作数

12、的类型有错误。#!公式产生的结果太长，单元格容纳不下。Excel在财务中的应用3、函数的运用n3.1函数的语法格式n3.2常用函数 1数学函数 2文本函数 3子字符串函数 4逻辑函数 5查找和引用函数 6财务函数Excel在财务中的应用3.1函数的语法格式函数名（参数1，参数2，参数n）。在使用函数时，应注意以下几个问题：（1）函数名与其后的括号“（”之间不能有空格。（2）当有多个参数时，参数之间要用逗号“，”分隔。（3）参数部分总长度不能超过1024个字符。（4）参数可以是数值、文本、逻辑值、单元格地址或单元格区域地址，也可以是各种表达式或函数。（5）函数中的逗号“，”、引号“”等都是半角字

13、符，而非全角的中文字符。Excel在财务中的应用3.2常用函数介绍1求和函数（1）无条件求和SUM函数该函数的功能是计算所选取的单元格区域中所有数值的和。（2）条件求和SUMIF函数SUMIF函数的功能是根据指定条件对若干单元格求和。（3）SUMPRODUCT函数功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。（3）DSUM函数功能是对数据库表格进行多条件汇总。Excel在财务中的应用Excel在财务中的应用3.2常用函数介绍2平均值函数AVERGAE函数功能是计算给定参数的算术平均值。3最小值与最大值函数MIN/MAX函数MIN函数的功能是给定参数表中的最小值MAX函数的功

14、能是给定参数表中的最大值。Excel在财务中的应用3.2常用函数介绍4数学函数（1）ABS函数：返回数字的绝对值。（2）SQRT函数：计算一个正数的正平方根。（3）ROUND函数：返回某个数字按指定位数舍入后的数字。Excel在财务中的应用3.2常用函数介绍5条件函数IF函数也称条件函数，它根据参数条件的真假，返回不同的结果。6计数函数COUNT函数：计算给定区域内数值型参数的数目COUNTIF函数：计算给定区域内满足特定条件的单元格的数目。Excel在财务中的应用3.2常用函数介绍7逻辑函数AND函数：表示逻辑与OR函数：表示逻辑或NOT函数：功能是对参数的逻辑值求反n这三个函数一般与IF函

15、数结合使用Excel在财务中的应用3.2常用函数介绍8查找函数（1）LOOKUP函数：返回向量（单行区域或单列区域）或数组中的数值。LOOKUP(lookup_value,lookup_vector,result_vector)（2）VLOOKUP函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)（3）HLOOKUP函数：从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。HLOOKUP(lookup_val

16、ue,table_array,row_index_num,range_lookup)Excel在财务中的应用3.2常用函数介绍9引用函数INDEX函数：返回表格或区域中的数值或对数值的引用。INDEX(array,row_num,column_num)Excel在财务中的应用4、数组公式和数据透析数组公式和数据透析；n数组公式n数据透析；Excel在财务中的应用4.1数组公式n1.数组公式的输入n数组公式与一般公式不同之处在于它被括在大括号（）中，其输入步骤如下：n（1）选中一个单元格或者单元格区域。n说明：说明：如果数组公式只是返回一个结果，需要选择保存用来保存结果的那一个单元格；如果数组公

17、式返回多个结果，则需要选中需要保留数组公式计算结果的单元格区域。n（2）按照前面介绍的公式输入规则，输入公式的内容。n（3）公式输完后，按【Ctrl+Shift+Enter】组合键结束操作。Excel在财务中的应用4.2数据透析数据透析n数据透视分析就是从数据库的特定字段中概括信息，从而方便从各个角度查看、分析数据，并可对数据库中的数据进行汇总统计，它在Excel中的实现工具是数据透视表。n数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表，而数据透视图是一种能够根据数据处理需要，查看部分数据的图表对比效果，有些类似前面介绍的动态图表功能，另外，Excel中还可以根据数据透视表制作不

18、同格式的数据透视报告。Excel在财务中的应用数据透析数据透析n用2007,发现数据数据透视表里面没有多重区域合并合并的选项了,那有什么办法用2007实现多表合并?n快捷键ALT+D+P，调用数据透视表向导。Excel在财务中的应用案案例例案例：工资表案例：工资表内容内容:一般公式的应用；一般公式的应用；嵌套函数的应用；嵌套函数的应用；统计函数的应用；统计函数的应用；引用的应用；引用的应用；数组公式的应用；数组公式的应用；Excel在财务中的应用5、应用应用EXCEL完成财务会计工作；完成财务会计工作；n应用应用EXCEL完成：期初余额，凭证清单完成：期初余额，凭证清单期末余额期末余额表表明细

19、账，总账明细账，总账报表报表报表分析等工作报表分析等工作Excel在财务中的应用案案例例案例：账务处理案例：账务处理内内容容:EXCEL:EXCEL高高级级应应用用用用EXCELEXCEL完完成成财财务务会会计计工工作。作。流流程程：期期初初余余额额表表、凭凭证证清清单单期期末末余余额额表表资产负债表、利润表、现金流量表资产负债表、利润表、现金流量表财务分析财务分析Excel在财务中的应用6、财务函数的应用；财务函数的应用；Excel提供了许多有关财务、投资、偿还、利息提供了许多有关财务、投资、偿还、利息及折旧方面的函数，在工作表中运用这些函数可以较及折旧方面的函数，在工作表中运用这些函数可以

20、较松地完成相关的财务运算，或者对其他财务管理软件松地完成相关的财务运算，或者对其他财务管理软件的运算输出数据进行验证。的运算输出数据进行验证。Excel在财务中的应用6、财务函数的应用；财务函数的应用；6.1投资计算函数6.2折旧计算函数6.3偿还率计算函数Excel在财务中的应用6.1投资计算函数n投资计算函数常见参数说明n贷款定额还款期数计算函数NPERn贷款分期偿还金额计算函数PMTn贷款利息、本金金额计算函数IPMT、PPMTn投资未来值计算函数FVn投资偿还额现值计算函数PVn投资定期现金流净现值计算函数NPVn投资不定期现金流净现值计算函数XNPVExcel在财务中的应用投资计算函

21、数常见参数说明Excel投资分析方面的函数，这些函数使用的参数大致相同，意义相近，如下表所示。参参 数数说说 明明rate为为各期利率，各期利率，为为固定固定值值per用于用于计计算其本金数算其本金数额额的期次，必的期次，必须须在在 1nper 之之间间nper为总为总投投资资（或（或贷贷款）期次，即款）期次，即该项该项投投资资（或（或贷贷款）的付款期款）的付款期总总数数pv为现值为现值，即从，即从该项该项投投资资（或（或贷贷款）开始款）开始计计算算时时已已经经入入账账的款的款项项，或一系列未来付款当前，或一系列未来付款当前值值的累的累积积和，也称和，也称为为本金本金fv为为未来未来值值，或在

22、最后一次付款后希望得到的，或在最后一次付款后希望得到的现现金余金余额额，如果省略，如果省略 fv，则则假假设设其其值为值为零（如一笔零（如一笔贷贷款的未来款的未来值值即即为为零）零）type数字数字 0 或或 1，用以指定各期的付款，用以指定各期的付款时间时间是在期初是在期初还还是期末，是期末，0：期末；：期末；1：期初：期初value1,value2value1,value29代表代表129个个偿还偿还金金额额不相等不相等时时的分期的分期偿还额偿还额Excel在财务中的应用一、贷款定额还款期数计算函数NPER1.NPER函数知识介绍1）NPER函数功能：基于固定利率及等额分期付款方式，返回一

23、项投资或贷款的期数。2）语法形式：NPER（rate，pmt，pv，fv，type）3）参数说明4）注意点：第一所有期数的单位要统一 第二所有的入帐用正数表示，支付额用 负数表示Excel在财务中的应用一、贷款定额还款期数计算函数NPER2.实例 超市贷款还款期数预测n【实例内容】：由于拓展业务需要，欲向银行贷款1,000,000元。根据新风超市目前的经济与经营状况进行判断，再综合其他各种因素考虑，新风超市每月有能力且最适合的还款额为12,000元，若以7.50%的平均年利率作为参考，选择期（月）末付款方式，至少需要多久能还清贷款？Excel在财务中的应用一、贷款定额还款期数计算函数NPERn

24、【实例操作步骤】：步骤1：构造数据模型。如图6-1所示。步骤2：选定B4单元格，在辑栏中输入“=NPER(B2/12,-B3,B1)”。步骤3：单击“输入”按钮，此时可以这笔贷款方案需要偿还的时间（月）。图图6-1 例例6.1数据模型数据模型Excel在财务中的应用二、贷款分期偿还金额计算函数PMT1.PMT函数知识介绍1）PMT函数功能：基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。2）语法形式：PMT(rate，nper，pv，fv，type)3）参数说明Excel在财务中的应用二、贷款分期偿还金额计算函数PMT2.实例2超市贷款分期偿还金额计算n【实例内容】：君华银行向新风

25、超市提供了四种基于固定利率及等额分期期末付款的贷款方案，还款期数及年利率如表6-2所示，新风超市应该选择哪一种贷款方案比较合适？表6-2 君华银行贷款方案表银行名称还款期数（年）贷款利率（年）君华银行57.20%107.48%157.66%207.92%Excel在财务中的应用二、贷款分期偿还金额计算函数PMTn【实例操作步骤】：步骤1：构造数据模型。如图6-3所示。步骤2：选定D8单元格，在编辑栏中输入“=PMT(C8/12,B8*12,$B$1)”。步骤3：单击“输入”按钮，此时可以看到第一种贷款方案的每月还款额。图图6-36-3例例6.26.2数据模型数据模型 Excel在财务中的应用三

26、、贷款利息、本金金额计算函数IPMT、PPMT1.IPMT函数知识介绍1）IPMT函数功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。2）语法形式：IPMT(rate，per，nper，pv，fv，type)3）参数说明Excel在财务中的应用三、贷款利息、本金金额计算函数IPMT、PPMT2.PPMT函数知识介绍1）PPMT函数功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的本金偿还额。2）语法形式：PPMT(rate，per，nper，pv，fv，type)3）参数说明Excel在财务中的应用三、贷款利息、本金金额计算函数IPMT、P

27、PMT3.实例3超市贷款分期偿还利息与本金金额计算n【实例内容】：在实例2中新风超市确定贷款方案后，第一年每月偿还的利息和本金分别是多少？Excel在财务中的应用三、贷款利息、本金金额计算函数IPMT、PPMTn【实例操作步骤】：步骤1：构造数据模型。如图6-5所示。步骤2：选定B14单元格，在编辑栏中输入“=IPMT($C$9/12,A14,$B$9*12,$B$1)”。步骤3：选定C14单元格，在编辑栏中输入“=PPMT($C$9/12,A14,$B$9*12,$B$1)”。步骤4：用填充柄填充B和C两列，可以看到结果。图图6-5 6-5 例例6.36.3数据模型数据模型 Excel在财务

28、中的应用四、投资未来值计算函数FV1.FV函数知识介绍 1）FV函数功能：基于固定利率及等额分期付款方式，返回某项投资的未来值。2）语法形式：FV(rate，nper，pmt，pv，type)3）参数说明Excel在财务中的应用四、投资未来值计算函数FV2.实例4超市存款投资未来值计算n【实例内容】：新风超市计划在5年后创办一所新风希望小学，需要资金200,000元。从本年开始筹备资金，打算在每月月初存入5,000元，在整个投资期间内，平均年投资回报率为7.6%，3年后这笔存款是否足够创办新风希望小学？Excel在财务中的应用四、投资未来值计算函数FVn【实例操作步骤】：步骤1：构造数据模型。

29、如图6-7所示。步骤2：选定B30单元格，在编辑栏中输入“=FV(B27/12,B28*12,-B29,1)”。步骤3：单击“输入”按钮，此时可以看到这笔存款3年后的价值。图图6-7 例例6.4数据模型数据模型 Excel在财务中的应用五、投资偿还额现值计算函数PV1.PV函数知识介绍1）PV函数功能：返回投资的现值。现值为一系列未来付款当前值的累积和，可以作为一种权衡长期投资的方法。2）语法形式：PV(rate，nper，pmt，fv，type)3）参数说明Excel在财务中的应用五、投资偿还额现值计算函数PV2.实例5超市保险投资决策n【实例内容】：新风超市计划为高级员工提高福利，购买一笔

30、医疗保险，购买的成本为28,000元，该保险可以在今后30年中每月末回报150元，投资年回报率为5.6%。新风超市购买这份保险是否划算？Excel在财务中的应用五、投资偿还额现值计算函数PVn【实例操作步骤】：步骤1：构造数据模型。如图6-9所示。步骤2：选定B35单元格，在编辑栏中输入“=PV(B32/12,B33*12,B34)”。步骤3：单击“输入”按钮，此时可以看到这份保险的现值。图图6-9 例例6.5数据模型数据模型 Excel在财务中的应用六、投资定期现金流净现值计算函数NPV1.NPV函数知识介绍1）NPV函数功能：计算一组定期现金流的净现值。通过使用贴现率以及一系列未来支出（负

31、值）和收入（正值），返回一项投资的净现值（当前纯利润）。2）语法形式：NPV(rate，value1，value2，.)3）参数说明：Rate是应用于现金流的贴现率，为一固定值。value1，value2，.代表1到29笔支出及收入的参数值，value1，value2，.所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。如果支付的时间发生在期初，不能作为value参数，而是将收入转换成净现值后再扣除支付金额。Excel在财务中的应用六、投资定期现金流净现值计算函数NPV2.实例6超市收购投资决策n【实例内容】：新风超市计划收购一小超市，收购成本为500,000元，必须预先付清，经过市

32、场调查，预计今后6年的营业收入可达到110,000元，130,000元，160,000元，180,000元，190,000元和210,000元，每年的贴现率为8.7%。这项收购计划是否可行？Excel在财务中的应用六、投资定期现金流净现值计算函数NPVn【实例操作步骤】：步骤1：构造数据模型。如图6-11所示。步骤2：选定B45单元格，在编辑栏中输入“=NPV(B38,B39:B44)-B37”。步骤3：单击“输入”按钮，此时可以看到收购小超市投资计划的当前纯利润。图图6-11 例例6.6数据模型数据模型 Excel在财务中的应用七、投资不定期现金流净现值计算函数XNPV1.XNPV函数知识介

33、绍1）XNPV函数功能：返回一组现金流的净现值，这些现金流不一定定期发生。2）语法形式：XNPV(rate，values，dates)3）参数说明：rate是应用于现金流的贴现率，为一固定值。dates是与现金流支付相对应的支付日期表。values是与dates中的支付时间相对应的一系列现金流转。Excel在财务中的应用七、投资不定期现金流净现值计算函数XNPV2.实例7超市项目投资决策n【实例内容】：新风超市计划在2008年5月3日拿出500,000元资金用于某项投资，预计可于如下时间内分4次获取返回资金：2008年12月3日返回120,000元，2009年2月3日返回160,000元，20

34、09年5月3日返回160,000元，2009年12月3日返回90,000元，资金流转折扣为8.2%，这项投资是否划算？Excel在财务中的应用七、投资不定期现金流净现值计算函数XNPVn【实例操作步骤】：步骤1：构造数据模型。如图6-13所示。图图6-13 6-13 例例6.76.7数据模型数据模型 Excel在财务中的应用步骤2：将A50：A54单元格区域格式设置为“日期”。结果如图6-14所示。步骤3：选定B55单元格，在编辑栏中输入“=XNPV(B47,B50:B54,A50:A54)”。步骤4：单击“输入”按钮，此时可以看到这项投资计划的当前纯利润。图图6-14 6-14 转换转换日期

35、序列日期序列 Excel在财务中的应用6.2折旧计算函数n折旧计算函数常见参数说明n直线折旧函数SLNn固定余额递减折旧函数DBn双倍余额递减折旧函数DDBn年限总和折旧函数SYDn可变余额递减折旧函数VDBExcel在财务中的应用折旧计算函数常见参数说明nCost：固定资产原值nSalvage：固定资产使用年限终了时的估计残值nLife：固定资产进行折旧计算的周期总数nPeriod：进行折旧计算的期次，必须与life使用的单位相同上一页Excel在财务中的应用一、五种折旧计算函数.实例8计算超市货架的折旧金额 n【实例内容】：新风超市计划将现有一批已使用3年的货架转手卖出，3年前的购买价值为

36、50,000元，使用年限为8年，预计报废价值为15,000元，使用直线折旧，固定余额递减折旧，双倍余额递减折旧，年限总和折旧和可变余额递减折旧5种不同的折旧方法计算出这批货架这3年的折旧金额。Excel在财务中的应用一、五种折旧计算函数n【实例操作步骤】：步骤1：构造数据模型。如图6-16所示。图图6-16 例例6.8数据模型数据模型 Excel在财务中的应用一、五种折旧计算函数步骤2：选定B7单元格，在编辑栏中输入“=SLN($B$1,$B$2,$B$3)”。步骤3：选定C7单元格，在编辑栏中输入“=DB($B$1,$B$2,$B$3,A7)”。步骤4：选定D7单元格，在编辑栏中输入“=DD

37、B($B$1,$B$2,$B$3,A7)”。步骤5：选定E7单元格，在编辑栏中输入“=SYD($B$1,$B$2,$B$3,A7)”。步骤6：选定F7单元格，在编辑栏中输入“=VDB($B$1,$B$2,$B$3,0,A7,2)”。步骤7：用填充柄工具填充B、C、D、E、F列，可以看到结果。Excel在财务中的应用一、五种折旧计算函数.SLN函数知识介绍1）SLN函数功能：用直线折旧的计算方法，返回一项资产每期的直线折旧费。2）语法形式：SLN(cost，salvage，life)3）参数说明Excel在财务中的应用一、五种折旧计算函数3.DB函数知识介绍1）DB函数功能：用固定余额递减的计算

38、方法，返回一项资产在给定期间内的折旧值。用于计算固定利率下的资产折旧金额。2）语法形式：DB(cost，salvage，life，period，month)3）参数说明：month为第一年的月份数，如省略，则假设为12。Excel在财务中的应用一、五种折旧计算函数4.DDB函数知识介绍1）DDB函数功能：用双倍余额递减法或其他指定计算方法，返回一笔资产在给定期间内的折旧值。2）语法形式：DDB(cost，salvage，life，period，factor)3）参数说明：factor参数为余额递减速率，也称为折旧因子。如果factor被省略，则假设为2，即双倍余额递减法。这5个参数都必须为正数

39、。Excel在财务中的应用一、五种折旧计算函数5.SYD函数知识介绍1）SYD函数功能：用年限总和折旧计算方法，返回一笔资产在给定期间内的折旧值。2）语法形式：SYD(cost，salvage，life，per)3）参数说明：per参数为期间，单位与life相同。Excel在财务中的应用一、五种折旧计算函数6.VDB函数知识介绍1）VDB函数功能：用可变余额递减计算方法，返回一笔资产在给定期间内的折旧值。2）语法形式：VDB(cost，salvage，life，star_period，end_period，factor，no_switch)3）参数说明：star_period参数为进行折旧计算

40、的起始期间，单位与life相同。End_period参数为进行折旧计算的截止期间，单位与life相同。factor参数为余额递减速率，也称为折旧因子。如果factor被省略，则假设为2，即双倍余额递减法。No_switch参数指定当折旧值大于余额递减计算值时，是否转用直线折旧法，为一逻辑值。Excel在财务中的应用6.3偿还率计算函数n投资偿还率计算函数RATEn投资定期现金流内部收益率计算函数IRRn投资修正收益率计算函数MIRRExcel在财务中的应用一、投资偿还率计算函数RATE1.RATE函数知识介绍 1）RATE函数功能：返回年金的各期利率。2）语法形式：RATE(nper，pmt，

41、pv，fv，type，guess)3）参数说明:guess参数为预期利率。如果省略，则假设该值为10%。Excel在财务中的应用一、投资偿还率计算函数RATE2.实例9银行的贷款偿还率计算 n【实例内容】：新风超市于5年前创建，创建初期向银行贷款200,000元，期限5年，每个月偿还4,000元，这笔贷款银行的年收益率是多少？Excel在财务中的应用一、投资偿还率计算函数RATEn【实例操作步骤】：步骤1：构造数据模型。如图6-18所示。步骤2：选定B4单元格，在编辑栏中输入“=RATE(B2*12,-B3,B1)”,单击“输入”按钮。步骤3：选定B5单元格，在编辑栏中输入“=B4*12”，单

42、击“输入”按钮，此时可以看到银行的年收益率。图图6-18例例6.9数据模型数据模型 Excel在财务中的应用二、投资定期现金流内部收益率计算函数IRRn按照传统的内部收益率（IRR）法，项目的净现值（NPV）为零时的贴现率，就是IRR。通常，我们以此IRR与市场利率或预定的收益率相比较，对该项目的可行性进行评价。n比如，一个简单的两年到期项目，初始投入1000元；第一年净流入500元；第二年600元。市场利率为10%。采用传统的IRR法：NPV=-1000+500/（1+IRR）+600/（1+IRR）*（1+IRR）=0。由此模型可求出IRR。Excel在财务中的应用二、投资定期现金流内部收

43、益率计算函数IRR1.IRR函数知识介绍 1）IRR函数功能：返回由数值代表的一组现金流的内部收益率。2）语法形式：IRR(values，guess)3）参数说明:Values参数为数组或单元格的引用，包含用来计算返回的内部收益率的数字。Guess参数为对函数IRR计算结果的估计值。Excel在财务中的应用二、投资定期现金流内部收益率计算函数IRR2.实例10超市投资定期现金流内部收益率计算 n【实例内容】：5年来新风超市每年的收入分别为50,000元，70,000元，100,000元，110,000元，150,000元和180,000元，期初投资的200,000元现在内部收益率是多少？Exc

44、el在财务中的应用二、投资定期现金流内部收益率计算函数IRRn【实例操作步骤】：步骤1：构造数据模型。如图6-21所示。步骤2：选定B13单元格，在编辑栏中输入“=IRR(B7:B12)”。步骤3：单击“输入”按钮，此时可以看到新风超市近5年的内部收益率。图图6-21 6-21 例例6.106.10数据模型数据模型 Excel在财务中的应用三、投资修正收益率计算函数MIRRn传统的IRR法是建立在以一个“虚拟的再投资利率”再投资的假设基础之上的。随着现代经济系统的波动性日益频繁，传统的IRR法已经很难正确地评价项目的真实可行性。而MIRR法，就是对IRR法的一个简单合理的修正，使之更加有效与真

45、实。Excel在财务中的应用三、投资修正收益率计算函数MIRRn初始投入1000元；第一年净流入500元；第二年600元。市场利率为10%。采用传统的IRR法：NPV=-1000+500/（1+IRR）+600/（1+IRR）*（1+IRR）=0。由此模型可求出IRR。我们对此模型进行一些“变动”，但仍以NPV为零：1000（1+IRR）*（1+IRR）=500*（1+IRR）+600。该等式经济含义为，一个项目（证券）面值为1000元的第二年（末）总价值，可分拆成面值500元一年期的，与面值600元二年期的项目（价值）之和。此时，第一年收到的500元再投资利率应当是市场利率10%。而对应的1

46、000元项目的总收益率（R）为，1000*（1+R）=500*（1+10%）+600；令（1+R）=（1+MIRR）*（1+IRR），由此可求出MIRR。对于多期拓展，就可得到：期末收益/期初投资=（1+MIRR）n。n是幂次数，等于投资期数。Excel在财务中的应用三、投资修正收益率计算函数MIRR1.MIRR函数知识介绍 1）MIRR函数功能：返回某一连续期间内现金流的修正内部收益率。函数MIRR同时考虑了投资的成本和现金再投资的收益率。2）语法形式：MIRR(values，finance_rate，reinvest_rate)3）参数说明:Values参数为一个数组或对包含数字的单元格的

47、引用。Finance_rate参数为现金流中使用的资金支付的利率。Reinvest_rate参数为将现金流再投资的收益率。Excel在财务中的应用三、投资修正收益率计算函数MIRR2.实例11超市投资修正收益率计算 n【实例内容】：5年来，新风超市将每年所获得的收益用于重新投资，每年的收益率为10%，那开业5年后的修正收益率是多少？Excel在财务中的应用三、投资修正收益率计算函数MIRRn【实例操作步骤】：步骤1：构造数据模型。如图6-23所示。步骤2：选定B16单元格，在编辑栏中输入“=MIRR(B7:B12,B5,B15)”。步骤3：单击“输入”按钮，此时可以看到新风超市近5年的内部修正

48、收益率。图图6-23 6-23 例例6.116.11数据模型数据模型 Excel在财务中的应用7、应用应用EXCEL完成管理会计工作；完成管理会计工作；n7.1本量利分析n7.2回归分析n7.3规划求解Excel在财务中的应用7.1本量利分析n量本利分析，即成本数量利润分析，是通过数学分析和图示分析等形式对销售数量、销售单价、变动成本、固定成本等因素与利润指标的内在联系进行研究，以协助管理者进行项目规划和期间计划的预测分析方法。n（1）量本利分析法的基本假设n假设能够将企业的所有成本都按照成本习性精确地划分为变动成本和固定成本。n假设销售单价和销售结构在相关范围内保持不变。Excel在财务中的

49、应用7.1本量利分析n 假设企业的生产能力和生产效率在相关范围内保假设企业的生产能力和生产效率在相关范围内保持不变。持不变。n 假设企业当期的产销量基本平衡，存货水平保持不假设企业当期的产销量基本平衡，存货水平保持不变。变。n（2）量本利的基本关系式）量本利的基本关系式n利润利润=销售收入销售收入-总成本总成本n =销售收入销售收入-销售成本销售成本-期间费用期间费用n =边际贡献边际贡献-固定成本固定成本n =销售量销售量*（销售单价（销售单价-单位变动成本）单位变动成本）-固定成本固定成本Excel在财务中的应用案案例例案例：本量利分析案例：本量利分析内容：本量利分析图模型内容：本量利分析

50、图模型流流程程：录录入入数数据据设设滚滚动动条条生生成成本本量量利利分分析析图图Excel在财务中的应用7.2回归分析（1）一元线性回归模型（2）一元非线性回归模型Excel在财务中的应用7.2回归分析（3）多元线性回归模型（4）多元非线性回归模型例如，柯柏道格拉斯生产函数式中：L和K分别为劳动力和固定资本Excel在财务中的应用预测函数1LINEST函数2LOGEST函数3TREND函数4GROWTH函数5FORECAST函数6SLOPE函数7INTERCEPT函数Excel在财务中的应用1LINEST函数nLINESTn使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。nL