Excel在财务管理中的应用

Excel在财务管理中的应用主讲教师韩良智教授北京科技大学经济管理学院[e-mail]:010-82375303本课程将学到的Excel知识•Excel的基本操作•运用公式•使用函数•创建与编辑图表•排序•筛选•分类汇总•模拟运算表•单变量求解工具•规划求解工具•数据分析工具•数据透视表•数据透视图•录制宏1基于Excel的数据处理基本知识1.1Excel的数据类型及输入方法1.2文本型数据的处理方法1.3数值型数据的处理方法1.4日期型数据的处理方法1.5时间型数据的处理方法1.1Excel的数据类型及输入方法•文本型数据•数值型数据•日期型数据•时间型数据1.2文本型数据的处理方法1.2.1文本运算符及其应用&1.2.2常用文本函数的应用–LEFT函数、MID函数、RIGHT函数–DOLLAR函数、RMB函数–TEXT函数、ISTEXT函数1.2.2常用文本函数的应用•LEFT函数：从左边取指定长度的字符。=LEFT(text,num_chars)•RIGHT函数：从右边取指定长度的字符。=RIGHT(text,num_chars)•MID函数：从指定位置取指定长度的字符。=MID(text,start_num,num_chars)1.2.2常用文本函数的应用•DOLLAR函数：将数值转换为带美元符号的文本。•RMB函数：将数值转换为带人民币符号的文本。•TEXT函数：将数值转换为指定格式的文本。•ISTEXT函数：检验是否为文本。1.3数值型数据的处理方法1.3.1与数值型数据有关的函数的运用1.3.2使用自定义数字格式缩位显示数据1.3.3将小写金额转换为大写1.3.1与数值型数据有关的函数的运用•INT函数：无条件舍去小数部分，保留整数部分。•ROUND函数：四舍五入。•ROUNDDOWN函数：靠近零值，向下舍入数字。•ROUNDUP函数：远离零值，向上舍入数字。1.3.2使用自定义数字格式缩位显示数据•使用自定义数字格式缩位显示数据代码：–缩小1百位0.00–缩小1千位显示数字：0.00，–缩小1万位显示数字：0!.0,–缩小10万位显示数字：0!.00,–缩小100万位显示数字：0.00,,–缩小1000万位显示数字：0!.0,,–缩小1亿位显示数字：0!.00,,–缩小10亿位显示数字：0.00,,,1.3.3将小写金额转换为大写•使用特殊格式显示大写数字•NUMBERSTRING函数：将小写数字转换为大写•IF函数：根据参数条件的真假，返回不同的结果。–语法格式为：=IF(logical_test,value_if_true,value_if_false)1.4日期型数据的处理方法1.4.1日期型数据的显示格式1.4.2日期型数据的计算1.4.3与日期型数据有关的函数与日期型数据有关的函数•TODAY函数：返回系统当前日期的序列号。–快捷方式：Ctrl+;•DATE函数：将三个数字组成一个日期序列号。•YEAR函数：获取指定日期序列号的年份数字。•MONTH函数：获取指定日期序列号的月份数字。•DAY函数：获取指定日期序列号的日数字。•WEEKDAY：获取某日期为星期几。•DAYS360函数：返回两个日期相差的天数（一年按360天计）1.5时间型数据的处理方法1.5.1时间型数据的显示格式1.5.2时间型数据的计算1.5.3与时间型数据有关的函数与时间型数据有关的函数•NOW函数：返回当前日期和时间所对应的序列号。–快捷方式：Ctrl+;Ctrl+Shift+;•HOUR函数：用于获取时间值的小时数。•MINUTE函数：用于获取时间值的分钟数。•SECOND函数：用于获取时间值的秒数。2资金时间价值的计算2.1一笔现金流的终值与现值2.2年金的终值与现值2.3终值与现值系数表的编制2.1一笔现金流的终值与现值2.1.1一笔现金流的终值2.1.2一笔现金流的现值2.1.1一笔现金流的终值•单利终值•复利终值或–式中：P为现在的一笔资金；n为计息期限；iS为单利年利率；i为复利年利率；FS为单利终值；F为复利终值；FVIFi,n=(1+i)n称为复利终值系数，它表示现在的1元钱在n年后的价值。)1(niPniPPFSSSniPF)1(niFVIFPF,单利终值的计算【例2-1】–输入公式–公式审核–显示公式与显示计算结果–输入序列数据–绝对引用与相对引用单元格–复制公式复利终值的计算【例2-2】调用函数的方法；FV函数的运用•FV函数——基于固定利率及等额分期付款方式，返回某项投资的未来值。公式为：=FV(rate,nper,pmt,pv,type)式中：–rate——各期利率，是一固定值。–nper——总投资（或贷款）期。–pmt——各期相等的收付金额。–pv——现值，也称为本金。–type——数字0或1，用以指定各期的付款时间是在期初还是期末，type为0表示期末，type为1表示期初。如果省略type，则默认其值为零。2.1.2一笔现金流的现值•单利现值：•复利现值：或：–式中：F为未来值；n为期限；iS为单利年利率；i为复利年利率；PS为单利现值；P为复利现值；称为复利现值系数)1/(niFPSsniFP)1/(niPVIFFP,nniiPVIF)1/(1,PV函数的功能•PV函数的功能是基于固定利率及等额分期付款方式，返回某项投资的现值，现值为一系列未来付款的当前值的累积和。•语法格式=PV(rate,nper,pmt,fv,type)式中各参数的含义如前所述。现值的计算【例2-3】•PV函数的应用•定义名称与建立名称公式–插入/名称/定义–插入/名称/指定–利用名称框定义2.2年金终值与年金现值2.2.1年金的种类–普通年金–先付年金–延期年金–永续年金2.2.2普通年金的终值与现值2.2.3先付年金的终值与现值2.2.2普通年金的终值与现值•普通年金终值的计算公式为：•普通年金现值的计算公式为：•式中：A为年金；F为年金终值；P为普通年金的现值；i为年利率；n为期限；FVIFAi,n称为年金终值系数；PVIFAi,n称为年金现值系数。ntnintnFVIFAAiiAiAF1,)(1)1()1(ntninntPVIFAAiiiAiAP1,)()1(1)1()1(普通年金终值计算过程示例假定年金为1元，年利率为10%，期数为4年11111203411.11.211.3314.641普通年金现值计算过程示例假定年金为1元，年利率为10%，期数为4年1111120340.90910.82640.75130.68303.16982.2.3先付年金的终值与现值•先付年金的终值Vn的计算公式为：或者：•先付年金的现值V0的计算公式为：或者：)1()(,iFVIFAAVnin)1(1,ninFVIFAAV)1()(,0iPVIFAAVni)1(1,0niPVIFAAV年金终值与现值的计算【例2-4】•通过本例学习：–利用FV函数和PV函数计算年金终值和年金现值的方法2.3终值与现值系数表的编制2.3.1复制公式的方式2.3.2数组公式的运用方法•按【Shift+Ctrl+Enter】组合键【例2-5】3筹资管理3.1长期银行借款管理3.2租赁筹资管理3.3流动负债管理3.1长期银行借款管理3.1.1银行借款的偿还方式–到期一次还本付息–分期付息到期还本–分期等额还本余额计息–分期等额还本付息3.1.2四种还款方式的比较3.1.3还款额的模拟运算分析3.1.2四种还款方式的比较•【例3-1】–IF函数与FV函数嵌套使用–PMT函数–PPMT函数–IPMT函数相关的函数•PMT函数——基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。=PMT(rate,nper,pv,fv,type）•IPMT函数——基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。=IPMT(rate,per,nper,pv,fv,type)•PPMT函数——基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的本金偿还额。=PPMT(rate,per,nper,pv,fv,type)3.1.3还款额的模拟运算分析【例3-2】每期还款额的模拟运算•通过本例学习：–CUMPRINC函数和CUMIPMT函数–使用模拟运算表•【数据】/【模拟运算表】–单变量模拟运算表–双变量模拟运算表相关的函数•CUMPRINC函数——返回一笔贷款在给定的期间累计偿还的本金数额。公式为=CUMPRINC（rate，nper，pv，start_period，end_period，type）•CUMIPMT函数——返回一笔贷款在给定期间累计偿还的利息数额。公式为CUMIPMT（rate，nper，pv，start_period，end_period，type）•为了使用以上两个函数需要首先加载分析工具库3.2租赁筹资管理3.2.1租金支付方式及计算方法–平均分摊法每次应付的租金=（租赁资产的购置成本-租赁资产的预计残值+租赁期间的利息费用+租赁手续费）/租赁期内支付租金的次数–等额年金法首先根据利率和手续费率确定综合租费率作为贴现率每期末应付的租金=租赁资产的购置成本/年金现值系数3.2.2租金支付方式选择计算模型3.2.2租金支付方式选择计算模型【例3-3】•通过本例学习：–组合框控件的设置【视图】/【工具栏】/【窗体】3.3流动负债管理3.3.1短期借款有效年利率的计算3.3.2应付账款管理模型3.3.1短期借款有效年利率的计算•如果给定的年利率为i，每年计息m次，那么现在的一笔资金P在n年末的终值的计算公式为：mnmiPF)1(名义年利率与有效年利率•有效年利率r与名义年利率i之间的关系为：式中：m为每年计息的次数•连续计息时：–常数e等于2.71828，是自然对数的底数。1)1(mmir1ier名义年利率与有效年利率【例3-4】–EFFECT函数——用于计算有效年利率。=EFFECT(nominal_rate,npery)–NOMINAL函数——用于计算名义年利率。=NOMINAL(effect_rate,npery)式中：nominal_rate为名义年利率；effect_rate为有效年利率；npery为每年的复利计息期数。–EXP函数——返回e的n次幂。=EXP(number)式中：number为底数e的指数。3.3.2应付账款管理模型【例3-5】–应付帐款到期日的计算•EDATE函数•EOMONTH函数–应付账款到期日提前提醒•运用条件格式：格式/条件格式4流动资产管理4.1货币资金管理4.2应收账款管理4.3存货管理4.1货币资金管理4.1.1从普通日记账中筛选现金日记账–【例4-1】数据/筛选4.1.2货币资金余额的合并计算–【例4-2】数据/合并计算4.2应收及暂付款管理4.2.1建立应收账款台账4.2.2应收账款排序4.2.3应收账款筛选4.2.4应收账款分类汇总4.2.5编制应收账款账龄分析表4.2.1建立应收账款台账【例4-3】•利用记录单输入和编辑数据–数据/记录单•拆分工作表–窗口/拆分4.2.2应收账款排序【例4-4】•数据/排序–一个关键字的情况–多个关键字的情况4.2.3应收账款筛选【例4-5】•数据/筛选–自动筛选–高级筛选•一个条件的情况•多个条件的情况4.2.4应收账款分类汇总【例4-6】•建立分类汇总–数据/分类汇总•查看分类汇总结果•删除分类汇总4.2.5编制应收账款账龄分析表【例4-7】•SUMIF函数——根据指定条件对若干单元格求和。=SUMIF(range,criteria,sum_range)•SUMPRODUCT函数——将给定的几个数组的对应的元素相乘，并返回乘积之和。=SUMPRODUCT(array1,array2,array3,...)4.3存货管理4.3.1建立存货收发存明