EXCEL在财务中的应用

Excel,在财务中的应用,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,陈海烽,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Excel,在财务中的高级应用,主讲内容：,一,Excel,在财务中的应用基础；,二,公式的编辑；,三,函数的运用；,五,应用,EXCEL,完成财务会计工作；,六,财务函数的应用；,七,应用财务函数完成财务管理工作；,四,数组公式和函数的嵌套；,1,、,Excel,在财务中的应用基础；,1.1,、代码化（数据标准化）,1.2,、日期、分数的输入,1.3,、引用的应用,1.4,、数据清单,1.5,、工作表的保护及安全打开,1.1,、代码化（数据标准化）,以电子计算机为计算工具处理财务数据的基本概念。,代码化,对系统原始数据按一定的规律进行编码的处理。数据处理代码化，缩短了数据项的长度，减少数据占用的存储空间，提高会计数据处理的速度和精度。方便计算机进行排序、分类、汇总等操作。,输入方式：,+,代码；如：现金科目代码输入方式：,1001,1.2,、日期、分数的输入,日期在,EXCEL,中其实质为数字,1900-1-1,代表,1,分数与日期输入,5/8,在,EXCEL,中被识别为,5,月,8,日,分数,5/8,的输入方式：,0+,空格,+ 5/8,1.3,、引用的使用；,引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿中不同工作表上的单元格和其他工作簿中的数据。引用不同工作簿中的单元格称为链接。,相对引用,绝对引用（混合引用）,链接,名称,1.3,、引用的使用；,相对引用,-,在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。,随公式位置的改变，其引用的单元格也会相应发生变化。,绝对引用,-,复制公式时,Excel,不调整引用，如,$C$1,。,包括绝对引用单元格的公式，无论将其复制到什么位置，总是引用特定的单元格。,链接,-,引用不同工作簿中的单元格。,每次按,F4,键时，,Excel,会在以下组合间切换：绝对列与绝对行（例如，,$A$1,）；相对列与绝对行,(A$1),；绝对列与相对行,($A1),以及相对列与相对行,(A1),，当切换到用户所需的引用时，按回车键确认即可。,1.4,动态引用,OFFSET,函数可以对动态数据进行查询，它以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数，其语法格式如下：,OFFSET(reference,rows,cols,height,width),可以看出，该函数最多包含五个参数，后两个参数为可选项。其中：,Reference,作为偏移量参照系的引用区域，它必须为对单元格或相连单元格区域的引用；否则，函数,OFFSET,返回错误值,#VALUE!,。,Rows,为相对于偏移量参照系左上角单元格，上（下）偏移的行数。,Cols,为相对于偏移量参照系左上角单元格，左（右）偏移的列数。,Heigh,为高度，即所要返回的引用区域的行数。,Width,为宽度，即所要返回的引用区域的列数。,1.4,、数据清单,在,Excel,中，数据库是作为一个数据清单来看待。我们可以理解数据清单就是数据库。在一个数据库中，信息按记录存储。每个记录中包含信息内容的各项，称为字段。,Excel,提供了一整套功能强大的命令集，使得管理数据清单（数据库）变得非常容易。我们可以完成下列工作： 排序,在数据清单中，针对某些列的数据，我们可以用数据菜单中的排序命令来重新组织行的顺序。可以选择数据和选择排序次序，或建立和使用一个自定义排序次序。 筛选,可以利用“数据”菜单中的“筛选”命令来对清单中的指定数据进行查找和其它工作。一个经筛选的清单仅显示那些包含了某一特定值或符合一组条件的行，暂时隐藏其它行。 数据记录单,一个数据记录单提供了一个简单的方法，让我们从清单或数据库中查看、更改、增加和删除记录，或用你指定的条件来查找特定的记录。 自动分类汇总,利用“数据”菜单的“分类汇总”命令，在清单中插入分类汇总行，汇总你所选的任意数据。当你插入了分类汇总后，,Microsoft Excel,自动为你在清单底部插入一个“总计”行。,Excel,数据库表格遵循的准则,建立和使用,Excel,数据库表格时，用户必须遵循以下的基本准则：,（,1,） 一个数据库最好单独占据一个工作表，避免将多个数据库放到一个工作表上。,（,2,）数据记录紧接在字段名下面，不要使用空白行将字段名和第一条记录数据分开。,（,3,）避免在数据库中间放置空白行或空白列，任意两行的内容不能完全相同。,（,4,）避免将关键数据放到数据库左右两侧，防止数据筛选时这些数据被隐藏。,（,5,）字段名的字体、对齐方式、格式、边框等样式，应当与其他数据的格式相区别。,（,6,）条件区域不要放在数据库的数据区域下方。因为用记录单添加数据时，,Excel,会在原数据库的下边添加数据记录，如果数据库的下边非空，就不能利用记录单添加数据。,2,、公式的编辑；,公式的组成：运算单元、运算符、函数及参数、引用、常数、文本、时间等,公式中元素的结构或次序决定了最终的计算结果。,Excel,中的公式遵循一个特定的语法或次序：最前面是等号（,=,），后面是参与计算的元素（运算数），这些参与计算的元素又是通过运算符隔开的。每个运算数可以是不改变的数值（常量数值）、单元格或引用单元格区域、标志、名称、或工作表函数。,2.1,公式中的运算符,（,1,）算术运算符：完成基本的数学运算。,（,2,）比较操作符：比较运算符用于比较两个值。当用操作符比较两个值时，结果是一个逻辑值，不是,TRUE,就是,FALSE,。,（,3,）文本运算符：使用和号（,&,）可以将文本连接起来。,（,4,）引用操作符：引用运算符的作用是确定在公式中需要参与运算的数据在工作表中所处的位置，可以使用三个运算符：冒号、逗号和空格。,2.1,公式中的运算符,：（冒号）：区域运算符，对两个单元格之间，包括两个单元格在内的所有单元格参与运算。,，（逗号）：联合运算符，可将多个引用合并为一个引用。,空格：交叉运算符，它是将同时属于两个引用的单元格区域进行引用，即两个单元格引用相重叠的区域。,2.2,运算符及其优先级,类别,运算符,运算功能,优先级,引用,:,区域运算符，用于引用单元格区域。,1,引用,联合运算符，用于将多个引用合并。,2,引用,空格,交叉运算符，用于引用两个单元格区域的重叠部分。,3,算术,( ),括号,4,算术,-,负号,5,算术,%,百分号,6,算术,乘方,7,算术,*,和,/,乘法和除法,8,算术,+,和,加法和减法,9,文本,&,文本连接,10,逻辑,=,、,=,、,等于、小于、大于、小于等于、大于等于、不等于,11,2.3,公式审核及出错检查,1,追踪引用单元格,【,工具,】 【,公式审核,】 【,追踪引用单元格,】,【,工具,】 【,公式审核,】 【,取消所有追踪箭头,】,2,追踪从属单元格,【,工具,】 【,公式审核,】 【,追踪从属单元格,】,【,工具,】 【,公式审核,】 【,取消所有追踪箭头,】,3,公式审核工具栏,【,工具,】 【,公式审核,】 【,显示“公式审核”工具栏,】,2.4,常见的公式错误信息,错误信息,错误原因,#DIV/0!,公式的除数为零,#N/A,内部函数或自定义工作表函数中缺少一个或多个参数,#NAME?,不能识别的名字。,#NULL!,指定的两个区域不相交。,#NUM!,在需要数字参数的函数中使用了不能接受的函数,#REF!,公式中引用了无效的单元格。,#VAULE!,参数或操作数的类型有错误。,#!,公式产生的结果太长，单元格容纳不下。,3,、函数的运用,3.1,函数的,语法格式,3.2,常用,函数,1,数学函数,2,文本函数,3,子字符串函数,4,逻辑函数,5,查找和引用函数,6,财务函数,3.1,函数的语法格式,函数名（参数,1,，参数,2,，,，参数,n,）。,在使用函数时，应注意以下几个问题：,（,1,）函数名与其后的括号“（”之间不能有空格。,（,2,）当有多个参数时，参数之间要用逗号“，”分隔。,（,3,）参数部分总长度不能超过,1024,个字符。,（,4,）参数可以是数值、文本、逻辑值、单元格地址或单元格区域地址，也可以是各种表达式或函数。,（,5,）函数中的逗号“，”、引号“,”,等都是半角字符，而非全角的中文字符。,3.2,常用函数介绍,1,求和函数,（,1,）无条件求和,SUM,函数,该函数的功能是计算所选取的单元格区域中所有数值的和。,（,2,）条件求和,SUMIF,函数,SUMIF,函数的功能是根据指定条件对若干单元格求和。,（,3,）,SUMPRODUCT,函数,功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。,（,3,）,DSUM,函数,功能是对数据库表格进行多条件汇总。,3.2,常用函数介绍,2,平均值函数, AVERGAE,函数,功能是计算给定参数的算术平均值。,3,最小值与最大值函数,MIN/ MAX,函数,MIN,函数的功能是给定参数表中的最小值,MAX,函数的功能是给定参数表中的最大值。,3.2,常用函数介绍,4,数学函数,（,1,）,ABS,函数：返回数字的绝对值。,（,2,）,SQRT,函数：计算一个正数的正平方根。,（,3,）,ROUND,函数：返回某个数字按指定位数舍入后的数字。,3.2,常用函数介绍,5,条件函数,IF,函数也称条件函数，它根据参数条件的真假，返回不同的结果。,6,计数函数,COUNT,函数：计算给定区域内数值型参数的数目,COUNTIF,函数：计算给定区域内满足特定条件的单元格的数目。,3.2,常用函数介绍,7,逻辑函数,AND,函数：表示逻辑与,OR,函数：表示逻辑或,NOT,函数：功能是对参数的逻辑值求反,这三个函数一般与,IF,函数结合使用,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_value,table_array,row_index_num,range_lookup),3.2,常用函数介绍,9,引用函数,INDEX,函数：返回表格或区域中的数值或对数值的引用。,INDEX(array,row_num,column_num),4,、,数组公式和数据透析,；,数组公式,数据透析；,4.1,数组公式,1.,数组公式的输入,数组公式与一般公式不同之处在于它被括在大括号（,）中，其输入步骤如下：,（,1,）选中一个单元格或者单元格区域。,说明：,如果数组公式只是返回一个结果，需要选择保存用来保存结果的那一个单元格；如果数组公式返回多个结果，则需要选中需要保留数组公式计算结果的单元格区域。,（,2,）按照前面介绍的公式输入规则，输入公式的内容。,（,3,）公式输完后，按,【Ctrl+Shift+Enter】,组合键结束操作。,4.2,数据透析,数据透视分析就是从数据库的特定字段中概括信息，从而方便从各个角度查看、分析数据，并可对数据库中的数据进行汇总统计，它在,Excel,中的实现工具是数据透视表。,数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表，而数据透视图是一种能够根据数据处理需要，查看部分数据的图表对比效果，有些类似前面介绍的动态图表功能，另外，,Excel,中还可以根据数据透视表制作不同格式的数据透视报告。,数据透析,用,2007,发现,数据,透视表里面没有多重区域,合并,的选项了,那有什么办法用,2007,实现多表合并,?,快捷键,ALT+D+P,，调用数据透视表向导。,案,例,案例：工资表,内容,:,一般公式的应用；,嵌套函数的应用；,统计函数的应用；,引用的应用；,数组公式的应用；,5,、,应用,EXCEL,完成财务会计工作；,应用,EXCEL,完成：期初余额，凭证清单,期末余额表,明细账，总账,报表,报表分析等工作,案,例,案例：账务处理,内容,:EXCEL,高级应用,用,EXCEL,完成财务会计工作。,流程：期初余额表、凭证清单,期末余额表,资产负债表、利润表、现金流量表,财务分析,6,、,财务函数的应用；,Excel,提供了许多有关财务、投资、偿还、利息及折旧方面的函数，在工作表中运用这些函数可以较松地完成相关的财务运算，或者对其他财务管理软件的运算输出数据进行验证。,6,、,财务函数的应用；,6.1,投资计算函数,6.2,折旧计算函数,6.3,偿还率计算函数,6.1,投资计算函数,投资计算函数常见参数说明,贷款定额还款期数计算函数,NPER,贷款分期偿还金额计算函数,PMT,贷款利息、本金金额计算函数,IPMT,、,PPMT,投资未来值计算函数,FV,投资偿还额现值计算函数,PV,投资定期现金流净现值计算函数,NPV,投资不定期现金流净现值计算函数,XNPV,投资计算函数常见参数说明,Excel,投资分析方面的函数，这些函数使用的参数大致相同，意义相近 ，如下表所示。,参 数,说 明,rate,为各期利率，为固定值,per,用于计算其本金数额的期次，必须在,1nper,之间,nper,为总投资（或贷款）期次，即该项投资（或贷款）的付款期总数,pv,为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金,fv,为未来值，或在最后一次付款后希望得到的现金余额，如果省略,fv,，则假设其值为零（如一笔贷款的未来值即为零）,type,数字,0,或,1,，用以指定各期的付款时间是在期初还是期末，,0,：期末；,1,：期初,value1,value2,value1,value29,代表,129,个偿还金额不相等时的分期偿还额,一、贷款定额还款期数计算函数,NPER,1. NPER,函数知识介绍,1,）,NPER,函数功能：基于固定利率及等额分期付款方式，返回一项投资或贷款的期数。,2,）语法形式：,NPER,（,rate,，,pmt,，,pv,，,fv,，,type,）,3,）,参数说明,4,）,注意点：,第一所有期数的单位要统一,第二所有的入帐用正数表示，支付额用,负数表示,一、贷款定额还款期数计算函数,NPER,2.,实例,超市贷款还款期数预测,【,实例内容,】,：由于拓展业务需要，欲向银行贷款,1,000,000,元。根据新风超市目前的经济与经营状况进行判断，再综合其他各种因素考虑，新风超市每月有能力且最适合的还款额为,12,000,元，若以,7.50%,的平均年利率作为参考，选择期（月）末付款方式，至少需要多久能还清贷款？,一、贷款定额还款期数计算函数,NPER,【,实例操作步骤,】,：,步骤,1,：构造数据模型 。如图,6-1,所示。,步骤,2,：选定,B4,单元格，在辑栏中输入“,=NPER(B2/12,-B3,B1)”,。,步骤,3,：单击,“,输入,”,按钮，此时可以这笔贷款方案需要偿还的时间（月）。,图,6-1,例,6.1,数据模型,二、贷款分期偿还金额计算函数,PMT,1. PMT,函数知识介绍,1,）,PMT,函数功能：,基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。,2,）语法形式：,PMT(rate,，,nper,，,pv,，,fv,，,type),3,）,参数说明,二、贷款分期偿还金额计算函数,PMT,2.,实例,2,超市贷款分期偿还金额计算,【,实例内容,】,：,君华银行向新风超市提供了四种基于固定利率及等额分期期末付款的贷款方案，还款期数及年利率如表,6-2,所示，新风超市应该选择哪一种贷款方案比较合适？,表,6-2,君华银行贷款方案表,银行名称,还款期数（年）,贷款利率（年）,君华银行,5,7.20%,10,7.48%,15,7.66%,20,7.92%,二、贷款分期偿还金额计算函数,PMT,【,实例操作步骤,】,：,步骤,1,：构造数据模型 。如图,6-3,所示。,步骤,2,：,选定,D8,单元格，在编辑栏中输入,“,=PMT(C8/12,B8*12,$B$1),”,。,步骤,3,：,单击,“,输入,”,按钮，此时可以看到第一种贷款方案的每月还款额。,图,6-3,例,6.2,数据模型,三、,贷款利息、本金金额计算函数,IPMT,、,PPMT,1. IPMT,函数知识介绍,1,）,IPMT,函数功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。,2,）语法形式：,IPMT(rate,，,per,，,nper,，,pv,，,fv,，,type),3,）,参数说明,三、,贷款利息、本金金额计算函数,IPMT,、,PPMT,2. PPMT,函数知识介绍,1,）,P,PMT,函数功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的本金偿还额。,2,）语法形式：,PPMT(rate,，,per,，,nper,，,pv,，,fv,，,type),3,）,参数说明,三、,贷款利息、本金金额计算函数,IPMT,、,PPMT,3.,实例,3,超市贷款分期偿还利息与本金金额计算,【,实例内容,】,：,在实例,2,中新风超市确定贷款方案后，第一年每月偿还的利息和本金分别是多少？,三、,贷款利息、本金金额计算函数,IPMT,、,PPMT,【,实例操作步骤,】,：,步骤,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.3,数据模型,四、投资未来值计算函数,FV,1. FV,函数知识介绍,1,）,FV,函数功能：基于固定利率及等额分期付款方式，返回某项投资的未来值。,2,）语法形式：,FV(rate,，,nper,，,pmt,，,pv,，,type),3,）,参数说明,四、投资未来值计算函数,FV,2.,实例,4,超市存款投资未来值计算,【,实例内容,】,：新风超市计划在,5,年后创办一所新风希望小学，需要资金,200,000,元。从本年开始筹备资金，打算在每月月初存入,5,000,元，在整个投资期间内，平均年投资回报率为,7.6%,，,3,年后这笔存款是否足够创办新风希望小学？,四、投资未来值计算函数,FV,【,实例操作步骤,】,：,步骤,1,：构造数据模型 。如图,6-7,所示。,步骤,2,：,选定,B30,单元格，在编辑栏中输入,“,=FV(B27/12,B28*12,-B29,1),”,。,步骤,3,：,单击,“,输入,”,按钮，此时可以看到这笔存款,3,年后的价值。,图,6-7,例,6.4,数据模型,五、投资偿还额现值计算函数,PV,1. PV,函数知识介绍,1,）,PV,函数功能：返回投资的现值。现值为一系列未来付款当前值的累积和，可以作为一种权衡长期投资的方法。,2,）语法形式,：,PV(rate,，,nper,，,pmt,，,fv,，,type),3,）,参数说明,五、投资偿还额现值计算函数,PV,2.,实例,5,超市保险投资决策,【,实例内容,】,：,新风超市计划为高级员工提高福利，购买一笔医疗保险，购买的成本为,28,000,元，该保险可以在今后,30,年中每月末回报,150,元，投资年回报率为,5.6%,。新风超市购买这份保险是否划算？,五、投资偿还额现值计算函数,PV,【,实例操作步骤,】,：,步骤,1,：构造数据模型 。如图,6-9,所示。,步骤,2,：,选定,B35,单元格，在编辑栏中输入,“,=PV(B32/12,B33*12,B34),”,。,步骤,3,：,单击,“,输入,”,按钮，此时可以看到这份保险的现值。,图,6-9,例,6.5,数据模型,六、投资定期现金流净现值计算函数,NPV,1. NPV,函数知识介绍,1,）,NPV,函数功能：计算一组定期现金流的净现值。通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值（当前纯利润）。,2,）语法形式：,NPV(rate,，,value1,，,value2,，,.),3,）参数说明,：,Rate,是应用于现金流的贴现率，为一固定值。,value1,，,value2,，,.,代表,1,到,29,笔支出及收入的参数值，,value1,，,value2,，,.,所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。如果支付的时间发生在期初，不能作为,value,参数，而是将收入转换成净现值后再扣除支付金额。,六、投资定期现金流净现值计算函数,NPV,2.,实例,6,超市收购投资决策,【,实例内容,】,：,新风超市计划收购一小超市，收购成本为,500,000,元，必须预先付清，经过市场调查，预计今后,6,年的营业收入可达到,110,000,元，,130,000,元，,160,000,元，,180,000,元，,190,000,元和,210,000,元，每年的贴现率为,8.7%,。这项收购计划是否可行？,六、投资定期现金流净现值计算函数,NPV,【,实例操作步骤,】,：,步骤,1,：构造数据模型 。如图,6-11,所示。,步骤,2,：,选定,B45,单元格，在编辑栏中输入,“,=NPV(B38,B39:B44)-B37,”,。,步骤,3,：,单击,“,输入,”,按钮，此时可以看到收购小超市投资计划的当前纯利润。,图,6-11,例,6.6,数据模型,七、,投资不定期现金流净现值计算函数,XNPV,1. XNPV,函数知识介绍,1,）,XNPV,函数功能：返回一组现金流的净现值，这些现金流不一定定期发生。,2,）语法形式：,XNPV(rate,，,values,，,dates),3,）参数说明,：,rate,是应用于现金流的贴现率，为一固定值。,dates,是与现金流支付相对应的支付日期表。,values,是与,dates,中的支付时间相对应的一系列现金流转。,七、,投资不定期现金流净现值计算函数,XNPV,2.,实例,7,超市项目投资决策,【,实例内容,】,：,新风超市计划在,2008,年,5,月,3,日拿出,500,000,元资金用于某项投资，预计可于如下时间内分,4,次获取返回资金：,2008,年,12,月,3,日返回,120,000,元，,2009,年,2,月,3,日返回,160,000,元，,2009,年,5,月,3,日返回,160,000,元，,2009,年,12,月,3,日返回,90,000,元，资金流转折扣为,8.2%,，这项投资是否划算？,七、,投资不定期现金流净现值计算函数,XNPV,【,实例操作步骤,】,：,步骤,1,：构造数据模型 。如图,6-13,所示。,图,6-13,例,6.7,数据模型,步骤,2,：,将,A50,：,A54,单元格区域,格式设置为,“,日期,”,。结果如图,6-14,所示。,步骤,3,：,选定,B55,单元格，在编辑栏中输入,“,=XNPV(B47,B50:B54,A50:A54),”,。,步骤,4,：单击,“,输入,”,按钮，此时可以看到这项投资计划的当前纯利润。,图,6-14,转换日期序列,6.2,折旧计算函数,折旧计算函数常见参数说明,直线折旧函数,SLN,固定余额递减折旧函数,DB,双倍余额递减折旧函数,DDB,年限总和折旧函数,SYD,可变余额递减折旧函数,VDB,折旧计算函数常见参数说明,Cost,：固定资产原值,Salvage,：固定资产使用年限终了时的估计残值,Life,：固定资产进行折旧计算的周期总数,Period,：进行折旧计算的期次，必须与,life,使用的单位相同,上一页,一、五种折旧计算函数,.,实例,8,计算超市货架的折旧金额,【,实例内容,】,：新风超市计划将现有一批已使用,3,年的货架转手卖出，,3,年前的购买价值为,50,000,元，使用年限为,8,年，预计报废价值为,15,000,元，使用直线折旧，固定余额递减折旧，双倍余额递减折旧，年限总和折旧和可变余额递减折旧,5,种不同的折旧方法计算出这批货架这,3,年的折旧金额。,一、五种折旧计算函数,【,实例操作步骤,】,：,步骤,1,：构造数据模型 。如图,6-16,所示。,图,6-16,例,6.8,数据模型,一、五种折旧计算函数,步骤,2,：选定,B7,单元格，在编辑栏中输入“,=SLN($B$1,$B$2,$B$3)”,。,步骤,3,：选定,C7,单元格，在编辑栏中输入,“,=DB($B$1,$B$2,$B$3,A7),”,。,步骤,4,：选定,D7,单元格，在编辑栏中输入,“,=DDB($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,列，可以看到结果,。,一、五种折旧计算函数,. SLN,函数知识介绍,1,）,SLN,函数功能：用直线折旧的计算方法，返回一项资产每期的直线折旧费。,2,）语法形式：,SLN(cost,，,salvage,，,life),3,）,参数说明,一、五种折旧计算函数,3. DB,函数知识介绍,1,）,DB,函数功能：用固定余额递减的计算方法，返回一项资产在给定期间内的折旧值。用于计算固定利率下的资产折旧金额。,2,）语法形式：,DB(cost,，,salvage,，,life,，,period,，,month),3,）,参数说明,：,month,为第一年的月份数，如省略，则假设为,12,。,一、五种折旧计算函数,4. DDB,函数知识介绍,1,）,DDB,函数功能：用双倍余额递减法或其他指定计算方法，返回一笔资产在给定期间内的折旧值。,2,）语法形式：,DDB(cost,，,salvage,，,life,，,period,，,factor),3,）,参数说明,：,factor,参数为余额递减速率，也称为折旧因子。如果,factor,被省略，则假设为,2,，即双倍余额递减法。这,5,个参数都必须为正数。,一、五种折旧计算函数,5. SYD,函数知识介绍,1,）,SYD,函数功能：用年限总和折旧计算方法，返回一笔资产在给定期间内的折旧值。,2,）语法形式,：,SYD(cost,，,salvage,，,life,，,per),3,）,参数说明,：,per,参数为期间，单位与,life,相同。,一、五种折旧计算函数,6. VDB,函数知识介绍,1,）,VDB,函数功能：用可变余额递减计算方法，返回一笔资产在给定期间内的折旧值。,2,）语法形式：,VDB(cost,，,salvage,，,life,，,star_period,，,end_period,，,factor,，,no_switch),3,）,参数说明,：,star_period,参数为进行折旧计算的起始期间，单位与,life,相同。,End_period,参数为进行折旧计算的截止期间，单位与,life,相同。,factor,参数为余额递减速率，也称为折旧因子。如果,factor,被省略，则假设为,2,，即双倍余额递减法。,No_switch,参数指定当折旧值大于余额递减计算值时，是否转用直线折旧法，为一逻辑值。,6.3,偿还率计算函数,投资偿还率计算函数,RATE,投资定期现金流内部收益率计算函数,IRR,投资修正收益率计算函数,MIRR,一、投资偿还率计算函数,RATE,1.RATE,函数知识介绍,1,）,RATE,函数功能：返回年金的各期利率。,2,）语法形式：,RATE(nper,，,pmt,，,pv,，,fv,，,type,，,guess),3,）,参数说明,:guess,参数为预期利率。如果省略，则假设该值为,10%,。,一、投资偿还率计算函数,RATE,2.,实例,9,银行的贷款偿还率计算,【,实例内容,】,：新风超市于,5,年前创建，创建初期向银行贷款,200,000,元，期限,5,年，每个月偿还,4,000,元，这笔贷款银行的年收益率是多少？,一、投资偿还率计算函数,RATE,【,实例操作步骤,】,：,步骤,1,：构造数据模型 。如图,6-18,所示。,步骤,2,：选定,B4,单元格，在编辑栏中输入“,=RATE(B2*12,-B3,B1)”,单击“输入”按钮 。,步骤,3,：,选定,B5,单元格，在编辑栏中输入,“,=B4*12,”,，单击,“,输入,”,按钮，此时可以看到银行的年收益率。,图,6-18,例,6.9,数据模型,二、,投资定期现金流内部收益率计算函数,IRR,按照传统的内部收益率（,IRR,）法，项目的净现值（,NPV,）为零时的贴现率，就是,IRR,。通常，我们以此,IRR,与市场利率或预定的收益率相比较，对该项目的可行性进行评价。,比如，一个简单的两年到期项目，初始投入,1000,元；第一年净流入,500,元；第二年,600,元。市场利率为,10%,。采用传统的,IRR,法：,NPV=-1000+500/,（,1+IRR,）,+600/,（,1+IRR,）*（,1+IRR,）,=0,。由此模型可求出,IRR,。,二、,投资定期现金流内部收益率计算函数,IRR,1.IRR,函数知识介绍,1,）,IRR,函数功能：返回由数值代表的一组现金流的内部收益率。,2,）语法形式：,IRR(values,，,guess),3,）,参数说明,:,Values,参数为数组或单元格的引用，包含用来计算返回的内部收益率的数字。,Guess,参数为对函数,IRR,计算结果的估计值。,二、,投资定期现金流内部收益率计算函数,IRR,2.,实例,10,超市投资定期现金流内部收益率计算,【,实例内容,】,：,5,年来新风超市每年的收入分别为,50,000,元，,70,000,元，,100,000,元，,110,000,元，,150,000,元和,180,000,元，期初投资的,200,000,元现在内部收益率是多少？,二、,投资定期现金流内部收益率计算函数,IRR,【,实例操作步骤,】,：,步骤,1,：构造数据模型 。如图,6-21,所示。,步骤,2,：,选定,B13,单元格，在编辑栏中输入,“,=IRR(B7:B12),”,。,步骤,3,：,单击,“,输入,”,按钮，此时可以看到新风超市近,5,年的内部收益率。,图,6-21,例,6.10,数据模型,三、,投资修正收益率计算函数,MIRR,传统的,IRR,法是建立在以一个“虚拟的再投资利率”再投资的假设基础之上的。随着现代经济系统的波动性日益频繁，传统的,IRR,法已经很难正确地评价项目的真实可行性。而,MIRR,法，就是对,IRR,法的一个简单合理的修正，使之更加有效与真实。,三、,投资修正收益率计算函数,MIRR,初始投入,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%,。而对应的,1000,元项目的总收益率（,R,）为，,1000*,（,1+R,）,=500*,（,1+10%,）,+600,；令（,1+R,）,=,（,1+MIRR,）*（,1+IRR,），由此可求出,MIRR,。对于多期拓展，就可得到：期末收益,/,期初投资,=,（,1+MIRR,）,n,。,n,是幂次数，等于投资期数。,三、,投资修正收益率计算函数,MIRR,1.MIRR,函数知识介绍,1,）,MIRR,函数功能：返回某一连续期间内现金流的修正内部收益率。函数,MIRR,同时考虑了投资的成本和现金再投资的收益率。,2,）语法形式：,MIRR(values,，,finance_rate,，,reinvest_rate),3,）参数说明,:,Values,参数为一个数组或对包含数字的单元格的引用。,Finance_rate,参数为现金流中使用的资金支付的利率。,Reinvest_rate,参数为将现金流再投资的收益率。,三、,投资修正收益率计算函数,MIRR,2.,实例,11,超市投资修正收益率计算,【,实例内容,】,：,5,年来，新风超市将每年所获得的收益用于重新投资，每年的收益率为,10%,，那开业,5,年后的修正收益率是多少？,三、,投资修正收益率计算函数,MIRR,【,实例操作步骤,】,：,步骤,1,：构造数据模型 。如图,6-23,所示。,步骤,2,：,选定,B16,单元格，在编辑栏中输入,“,=MIRR(B7:B12,B5,B15),”,。,步骤,3,：,单击,“,输入,”,按钮，此时可以看到新风超市近,5,年的内部修正收益率。,图,6-23,例,6.11,数据模型,7,、,应用,EXCEL,完成管理会计工作；,7.1,本量利分析,7.2,回归分析,7.3,规划求解,7.1,本量利分析,量本利分析，即成本,数量,利润分析，是通过数学分析和图示分析等形式对销售数量、销售单价、变动成本、固定成本等因素与利润指标的内在联系进行研究，以协助管理者进行项目规划和期间计划的预测分析方法。,（,1,）量本利分析法的基本假设, 假设能够将企业的所有成本都按照成本习性精确地划分为变动成本和固定成本。, 假设销售单价和销售结构在相关范围内保持不变。,7.1,本量利分析,假设企业的生产能力和生产效率在相关范围内保持不变。, 假设企业当期的产销量基本平衡，存货水平保持不变。,（,2,）量本利的基本关系式,利润,=,销售收入,-,总成本,=,销售收入,-,销售成本,-,期间费用,=,边际贡献,-,固定成本,=,销售量*（销售单价,-,单位变动成本）,-,固定成本,案,例,案例：本量利分析,内容：本量利分析图模型,流程：录入数据,设滚动条,生成本量利分析图,7.2,回归分析,（,1,）一元线性回归模型,（,2,）一元非线性回归模型,7.2,回归分析,（,3,）多元线性回归模型,（,4,）多元非线性回归模型,例如，柯柏,道格拉斯生产函数,式中：,L,和,K,分别为劳动力和固定资本,预测函数,1 LINEST,函数,2 LOGEST,函数,3 TREND,函数,4 GROWTH,函数,5 FORECAST,函数,6 SLOPE,函数,7 INTERCEPT,函数,1 LINEST,函数,LINEST,使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。,LINEST(known_ys,known_xs,const,stats),直线的公式为：,y = mx + b or,y = m1x1 + m2x2 + . + b,（如果有多个区域的,x,值）,2 LOGEST,函数,在回归分析中，计算最符合数据的指数回归拟合曲线，并返回描述该曲线的数值数组。因为此函数返回数值数组，故必须以数组公式的形式输入。,LOGEST(known_ys,known_xs,const,stats),此曲线的公式为：,y = b*mx,或,y = (b*(m1x1)*(m2x2)*_),（如果有多个,x,值）,3 TREND,函数,返回一条线性回归拟合线的值。即找到适合已知数组,known_ys,和,known_xs,的直线（用最小二乘法），并返回指定数组,new_xs,在直线上对应的,y,值。,TREND,(,known_ys,known_xs,new_xs,const),4 GROWTH,函数,根据现有的数据预测指数增长值。根据现有的,x,值和,y,值，,GROWTH,函数返回一组新的,x,值对应的,y,值。可以使用,GROWTH,工作表函数来拟合满足现有,x,值和,y,值的指数曲线。,语法,GROWTH,(,known_ys,known_xs,new_xs,const),5 FORECAST,函数,根据已有的数值计算或预测未来值。此预测值为基于给定的,x,值推导出的,y,值。已知的数值为已有的,x,值和,y,值，再利用线性回归对新值进行预测。可以使用该函数对未来销售额、库存需求或消费趋势进行预测。,语法,FORECAST,(,x,known_ys,known_xs,),6 SLOPE,函数,返回根据,known_ys,和,known_xs,中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的重直距离与水平距离的比值，也就是回归直线的变化率。,语法,SLOPE,(,known_ys,known_xs,),7 INTERCEPT,函数,利用现有的,x,值与,y,值计算直线与,y,轴的截距。截距为穿过已知的,known_xs,和,known_ys,数据点的线性回归线与,y,轴的交点。当自变量为,0,（零）时，使用,INTERCEPT,函数可以决定因变量的值。例如，当所有的数据点都是在室温或更高的温度下取得的，可以用,INTERCEPT,函数预测在,0C,时金属的电阻。,语法,INTERCEPT,(,known_ys,known_xs,),7.3,规划求解,在计划管理中，经常会遇到各种规划问题，例如：人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批次的确定等。这类问题有一个共同要求，那就是：如何合理利用各种约束资源实现最佳的经济效益，也就是达到常量最高、利润最大、成本最低、费用最省等目标。这就是本节要解决的在约束条件下寻求目标函数最优的规划问题。一般来讲，这类规划问题都具有如下三个特点：,（,1,）所求问题都有单一的目标，如求生产的最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间以及求其他目标函数的最佳值等。,（,2,）总是有明确的不等式约束条件。比如库存不能低于一定的数量，否则造成原料短缺或产品缺货；生产产品不能超过一定额度，否则会造成商品积压等。,（,3,）问题都有直接或间接影响约束条件的一组输入值。,7.3,规划求解,规划求解工具用于解决复杂的方程求值及各类线性或非线形有约束优化问题。规划问题种类繁多。从数学角度来看，规划问题都有下述共同特征，这些特征也构成了,Excel,规划求解工具界面中的主要部分：,决策变量：每个规划问题都有一组需要求解的未知数，称作决策变量。这组决策变量的一组确定值就代表一个具体的规划方案。在,Excel,规划求解工具中，可变,单元格,代表的是决策变量。,约束,条件,：对于规划问题的决策变量通常都有一定的限制条件，称作约束条件。约束条件可以用与决策变量有关的不等式或等式来表示。,目标：每个问题都有一个明确的目标，如利润最大或成本最小。目标通常可用与决策变量有关的,函数,表示。在,Excel,规划求解工具中，包含,公式,的目标,单元,格代表的是目标函数。,案,例,案例：规划求解,内容,:,运输模型,流程：建模,设条件,运用,