Excel在财务管理中的应用课件

1、Excel在财务管理中的应用1 Excel在财务管理中的应用在财务管理中的应用 Tel:Excel在财务管理中的应用2 本课程将学到的本课程将学到的Excel知识知识 Excel的基本操作的基本操作 运用公式运用公式 使用函数使用函数 创建与编辑图表创建与编辑图表 排序排序 筛选筛选 分类汇总分类汇总 模拟运算表模拟运算表 单变量求解工具单变量求解工具 规划求解工具规划求解工具 数据分析工具数据分析工具 数据透视表数据透视表 数据透视图数据透视图 录制宏录制宏 Excel在财务管理中的应用3 1 基于基于Excel的数据处理基本知的数据处理基本知 识识 1.1 Ex

2、cel的数据类型及输入方法的数据类型及输入方法 1.2 文本型数据的处理方法文本型数据的处理方法 1.3 数值型数据的处理方法数值型数据的处理方法 1.4 日期型数据的处理方法日期型数据的处理方法 1.5 时间型数据的处理方法时间型数据的处理方法 Excel在财务管理中的应用4 1.1 Excel的数据类型及输入方法的数据类型及输入方法 文本型数据文本型数据 数值型数据数值型数据 日期型数据日期型数据 时间型数据时间型数据 Excel在财务管理中的应用5 1.2 文本型数据的处理方法 1.2.1 文本运算符及其应用文本运算符及其应用 DATE函数：将三个数字组成一个日期序列号。函数：将三个数字

3、组成一个日期序列号。 YEAR函数：获取指定日期序列号的年份数字。函数：获取指定日期序列号的年份数字。 MONTH函数：获取指定日期序列号的月份数字。函数：获取指定日期序列号的月份数字。 DAY函数：获取指定日期序列号的日数字。函数：获取指定日期序列号的日数字。 WEEKDAY：获取某日期为星期几。：获取某日期为星期几。 DAYS360函数：返回两个日期相差的天数（一年按函数：返回两个日期相差的天数（一年按360天计）天计） Excel在财务管理中的应用14 1.5 时间型数据的处理方法时间型数据的处理方法 1.5.1 时间型数据的显示格式时间型数据的显示格式 1.5.2 时间型数据的计算时间

4、型数据的计算 1.5.3 与时间型数据有关的函数与时间型数据有关的函数 Excel在财务管理中的应用15 与时间型数据有关的函数与时间型数据有关的函数 NOW函数：返回当前日期和时间所对应的序列号。函数：返回当前日期和时间所对应的序列号。 快捷方式：快捷方式： Ctrl+; Ctrl+Shift+; HOUR函数：用于获取时间值的小时数。函数：用于获取时间值的小时数。 MINUTE函数：用于获取时间值的分钟数。函数：用于获取时间值的分钟数。 SECOND函数：用于获取时间值的秒数。函数：用于获取时间值的秒数。 Excel在财务管理中的应用16 2 资金时间价值的计算资金时间价值的计算 2.1

5、一笔现金流的终值与现值一笔现金流的终值与现值 2.2 年金的终值与现值年金的终值与现值 2.3 终值与现值系数表的编制终值与现值系数表的编制 Excel在财务管理中的应用17 2.1 一笔现金流的终值与现值一笔现金流的终值与现值 2.1.1 一笔现金流的终值一笔现金流的终值 2.1.2 一笔现金流的现值一笔现金流的现值 Excel在财务管理中的应用18 2.1.1 一笔现金流的终值一笔现金流的终值 单利终值单利终值 复利终值复利终值 或或 式中：式中：P 为现在的一笔资金；为现在的一笔资金；n 为计息期限；为计息期限； iS 为单利年利率；为单利年利率； i 为复利年利率；为复利年利率；FS

6、为单利终值；为单利终值； F为复利终值；为复利终值；FVIFi,n=(1+i)n称为复利终值系数，称为复利终值系数， 它表示现在的它表示现在的1元钱在元钱在n年后的价值。年后的价值。 )1 (niPniPPF SSS n iPF)1 ( ni FVIFPF , Excel在财务管理中的应用19 单利终值的计算单利终值的计算 【例例2-1】 输入公式输入公式 公式审核公式审核 显示公式与显示计算结果显示公式与显示计算结果 输入序列数据输入序列数据 绝对引用与相对引用单元格绝对引用与相对引用单元格 复制公式复制公式 Excel在财务管理中的应用20 复利终值的计算复利终值的计算 【例例2-2】调用

7、函数的方法；调用函数的方法；FV函数的运用函数的运用 FV函数函数基于固定利率及等额分期付款方式，返回基于固定利率及等额分期付款方式，返回 某项投资的未来值。公式为：某项投资的未来值。公式为： = FV(rate,nper,pmt,pv,type) 式中：式中： rate 各期利率，是一固定值。各期利率，是一固定值。 nper 总投资（或贷款）期。总投资（或贷款）期。 pmt 各期相等的收付金额。各期相等的收付金额。 pv 现值，也称为本金。现值，也称为本金。 type 数字数字0或或1，用以指定各期的付款时间是在，用以指定各期的付款时间是在 期初还是期末，期初还是期末，type为为0表示期末

8、，表示期末，type为为1表示期表示期 初。如果省略初。如果省略 type，则默认其值为零。，则默认其值为零。 Excel在财务管理中的应用21 2.1.2 一笔现金流的现值一笔现金流的现值 单利现值：单利现值： 复利现值：复利现值： 或：或： 式中：式中： F为未来值；为未来值；n为期限；为期限； iS为单利年利率；为单利年利率； i为复利年利率；为复利年利率；PS为单利现值；为单利现值；P为复利现值；为复利现值； 称为复利现值系数称为复利现值系数 )1/(niFP Ss n iFP)1/( ni PVIFFP , n ni iPVIF)1/(1 , Excel在财务管理中的应用22 PV函

9、数的功能函数的功能 PV函数的功能是基于固定利率及等额分期函数的功能是基于固定利率及等额分期 付款方式，返回某项投资的现值，现值为付款方式，返回某项投资的现值，现值为 一系列未来付款的当前值的累积和。一系列未来付款的当前值的累积和。 语法格式语法格式 = PV(rate,nper,pmt,fv,type) 式中各参数的含义如前所述。式中各参数的含义如前所述。 Excel在财务管理中的应用23 现值的计算现值的计算 【例例2-3】 PV函数的应用函数的应用 定义名称与建立名称公式定义名称与建立名称公式 插入插入/名称名称/定义定义 插入插入/名称名称/指定指定 利用名称框定义利用名称框定义 Ex

10、cel在财务管理中的应用24 2.2 年金终值与年金现值年金终值与年金现值 2.2.1 年金的种类年金的种类 普通年金普通年金 先付年金先付年金 延期年金延期年金 永续年金永续年金 2.2.2 普通年金的终值与现值普通年金的终值与现值 2.2.3 先付年金的终值与现值先付年金的终值与现值 Excel在财务管理中的应用25 2.2.2 普通年金的终值与现值普通年金的终值与现值 普通年金终值的计算公式为：普通年金终值的计算公式为： 普通年金现值的计算公式为：普通年金现值的计算公式为： 式中：式中：A为年金；为年金；F为年金终值；为年金终值；P为普通年金的现为普通年金的现 值值 ；i为年利率；为年利

11、率；n为期限；为期限；FVIFAi,n称为年金终值称为年金终值 系数；系数；PVIFAi,n称为年金现值系数称为年金现值系数 。 n t ni n tn FVIFAA i i AiAF 1 , )( 1)1 ( )1 ( n t ni n n t PVIFAA ii i A i A P 1 , )( )1 ( 1)1 ( )1 ( Excel在财务管理中的应用26 普通年金终值计算过程示例普通年金终值计算过程示例 假定年金为1元，年利率为10%，期数为4年 1111 12034 1 1.1 1.21 1.331 4.641 Excel在财务管理中的应用27 普通年金现值计算过程示例普通年金现值

12、计算过程示例 假定年金为1元，年利率为10%，期数为4年 1111 12034 0.9091 0.8264 0.7513 0.6830 3.1698 Excel在财务管理中的应用28 2.2.3 先付年金的终值与现值先付年金的终值与现值 先付年金的终值先付年金的终值Vn的计算公式为：的计算公式为： 或者：或者： 先付年金的现值先付年金的现值V0的计算公式为：的计算公式为： 或者：或者： )1 ()( , iFVIFAAV nin ) 1( 1, nin FVIFAAV )1 ()( ,0 iPVIFAAV ni ) 1( 1,0 ni PVIFAAV Excel在财务管理中的应用29 年金终值

13、与现值的计算年金终值与现值的计算 【例例2-4】 通过本例学习：通过本例学习： 利用利用FV函数和函数和PV函数计算年金终值和函数计算年金终值和 年金现值的方法年金现值的方法 Excel在财务管理中的应用30 2.3 终值与现值系数表的编制终值与现值系数表的编制 2.3.1 复制公式的方式复制公式的方式 2.3.2 数组公式的运用方法数组公式的运用方法 按按【Shift+Ctrl+Enter 】组合键组合键 【例例2-5】 Excel在财务管理中的应用31 3 筹资管理筹资管理 3.1 长期银行借款管理长期银行借款管理 3.2 租赁筹资管理租赁筹资管理 3.3 流动负债管理流动负债管理 Exc

14、el在财务管理中的应用32 3.1 长期银行借款管理长期银行借款管理 3.1.1 银行借款的偿还方式银行借款的偿还方式 到期一次还本付息到期一次还本付息 分期付息到期还本分期付息到期还本 分期等额还本余额计息分期等额还本余额计息 分期等额还本付息分期等额还本付息 3.1.2 四种还款方式的比较四种还款方式的比较 3.1.3 还款额的模拟运算分析还款额的模拟运算分析 Excel在财务管理中的应用33 3.1.2 四种还款方式的比较四种还款方式的比较 【例例3-1】 IF函数与函数与FV函数嵌套使用函数嵌套使用 PMT函数函数 PPMT函数函数 IPMT函数函数 Excel在财务管理中的应用34

15、相关的函数相关的函数 PMT函数函数基于固定利率及等额分期付款方式，返回投资基于固定利率及等额分期付款方式，返回投资 或贷款的每期付款额。或贷款的每期付款额。 = PMT(rate,nper,pv,fv,type） IPMT函数函数基于固定利率及等额分期付款方式，返回投资基于固定利率及等额分期付款方式，返回投资 或贷款在某一给定期次内的利息偿还额。或贷款在某一给定期次内的利息偿还额。 = IPMT(rate,per,nper,pv,fv,type) PPMT函数函数基于固定利率及等额分期付款方式，返回投基于固定利率及等额分期付款方式，返回投 资或贷款在某一给定期次内的本金偿还额。资或贷款在某一

16、给定期次内的本金偿还额。 = PPMT(rate,per,nper,pv,fv,type) Excel在财务管理中的应用35 3.1.3 还款额的模拟运算分析还款额的模拟运算分析 【例例3-2】每期还款额的模拟运算每期还款额的模拟运算 通过本例学习：通过本例学习： CUMPRINC函数和函数和CUMIPMT函数函数 使用模拟运算表使用模拟运算表 【数据数据】/ 【模拟运算表模拟运算表】 单变量模拟运算表单变量模拟运算表 双变量模拟运算表双变量模拟运算表 Excel在财务管理中的应用36 相关的函数相关的函数 CUMPRINC函数函数返回一笔贷款在给定的期间累计偿返回一笔贷款在给定的期间累计偿

17、还的本金数额。公式为还的本金数额。公式为 =CUMPRINC（rate，nper，pv，start_period，end_period， type） CUMIPMT函数函数返回一笔贷款在给定期间累计偿还的返回一笔贷款在给定期间累计偿还的 利息数额。公式为利息数额。公式为 CUMIPMT（rate，nper，pv，start_period，end_period，type） 为了使用以上两个函数需要首先加载分析工具库为了使用以上两个函数需要首先加载分析工具库 Excel在财务管理中的应用37 3.2 租赁筹资管理租赁筹资管理 3.2.1 租金支付方式及计算方法租金支付方式及计算方法 平均分摊法平均

18、分摊法 每次应付的租金每次应付的租金=（租赁资产的购置成本（租赁资产的购置成本-租赁资产的预计残值租赁资产的预计残值+ 租赁期间的利息费用租赁期间的利息费用+租赁手续费）租赁手续费）/租赁期内支付租金的次数租赁期内支付租金的次数 等额年金法等额年金法 首先根据利率和手续费率确定综合租费率作为贴现率首先根据利率和手续费率确定综合租费率作为贴现率 每期末应付的租金每期末应付的租金=租赁资产的购置成本租赁资产的购置成本/年金现值系数年金现值系数 3.2.2 租金支付方式选择计算模型租金支付方式选择计算模型 Excel在财务管理中的应用38 3.2.2 租金支付方式选择计算模型租金支付方式选择计算模型

19、 【例例3-3】 通过本例学习：通过本例学习： 组合框控件的设置组合框控件的设置 【视图视图】/ 【工具栏工具栏】/ 【窗体窗体】 Excel在财务管理中的应用39 3.3 流动负债管理流动负债管理 3.3.1 短期借款有效年利率的计算短期借款有效年利率的计算 3.3.2 应付账款管理模型应付账款管理模型 Excel在财务管理中的应用40 3.3.1 短期借款有效年利率的计算短期借款有效年利率的计算 如果给定的年利率为如果给定的年利率为i，每年计息，每年计息m次，那么次，那么 现在的一笔资金现在的一笔资金P在在n年末的终值的计算公式年末的终值的计算公式 为：为： mn m i PF)1 ( E

20、xcel在财务管理中的应用41 名义年利率与有效年利率名义年利率与有效年利率 有效年利率有效年利率r与名义年利率与名义年利率i之间的关系为：之间的关系为： 式中：式中：m为每年计息的次数为每年计息的次数 连续计息时：连续计息时： 常数常数 e 等于等于 2.71828，是自然对数的底数。，是自然对数的底数。 1)1 ( m m i r 1 i er Excel在财务管理中的应用42 名义年利率与有效年利率名义年利率与有效年利率 【例例3-4】 EFFECT函数函数用于计算有效年利率。用于计算有效年利率。 = EFFECT(nominal_rate,npery) NOMINAL函数函数用于计算名

21、义年利率。用于计算名义年利率。 = NOMINAL(effect_rate,npery) 式中：式中：nominal_rate 为名义年利率；为名义年利率；effect_rate为有效年利率；为有效年利率； npery 为每年的复利计息期数。为每年的复利计息期数。 EXP函数函数返回返回 e 的的 n 次幂。次幂。 =EXP(number) 式中：式中：number为底数为底数 e 的指数。的指数。 Excel在财务管理中的应用43 3.3.2 应付账款管理模型应付账款管理模型 【例例3-5】 应付帐款到期日的计算应付帐款到期日的计算 EDATE函数函数 EOMONTH函数函数 应付账款到期日

22、提前提醒应付账款到期日提前提醒 运用条件格式：格式运用条件格式：格式/条件格式条件格式 Excel在财务管理中的应用44 4 流动资产管理流动资产管理 4.1 货币资金管理货币资金管理 4.2 应收账款管理应收账款管理 4.3 存货管理存货管理 Excel在财务管理中的应用45 4.1 货币资金管理货币资金管理 4.1.1 从普通日记账中筛选现金日记账从普通日记账中筛选现金日记账 【例例4-1】 数据数据/筛选筛选 4.1.2 货币资金余额的合并计算货币资金余额的合并计算 【例例4-2】数据数据/合并计算合并计算 Excel在财务管理中的应用46 4.2 应收及暂付款管理应收及暂付款管理 4.

23、2.1 建立应收账款台账建立应收账款台账 4.2.2 应收账款排序应收账款排序 4.2.3 应收账款筛选应收账款筛选 4.2.4 应收账款分类汇总应收账款分类汇总 4.2.5 编制应收账款账龄分析表编制应收账款账龄分析表 Excel在财务管理中的应用47 4.2.1建立应收账款台账建立应收账款台账 【例例4-3】 利用记录单输入和编辑数据利用记录单输入和编辑数据 数据数据/记录单记录单 拆分工作表拆分工作表 窗口窗口/拆分拆分 Excel在财务管理中的应用48 4.2.2 应收账款排序应收账款排序 【例例4-4】 数据数据/排序排序 一个关键字的情况一个关键字的情况 多个关键字的情况多个关键字

24、的情况 Excel在财务管理中的应用49 4.2.3 应收账款筛选应收账款筛选 【例例4-5】 数据数据/筛选筛选 自动筛选自动筛选 高级筛选高级筛选 一个条件的情况一个条件的情况 多个条件的情况多个条件的情况 Excel在财务管理中的应用50 4.2.4 应收账款分类汇总应收账款分类汇总 【例例4-6】 建立分类汇总建立分类汇总 数据数据/分类汇总分类汇总 查看分类汇总结果查看分类汇总结果 删除分类汇总删除分类汇总 Excel在财务管理中的应用51 4.2.5 编制应收账款账龄分析表编制应收账款账龄分析表 【例例4-7】 SUMIF函数函数根据指定条件对若干单元根据指定条件对若干单元 格求和

25、。格求和。 =SUMIF(range,criteria,sum_range) SUMPRODUCT函数函数将给定的几个数将给定的几个数 组的对应的元素相乘，并返回乘积之和。组的对应的元素相乘，并返回乘积之和。 =SUMPRODUCT(array1,array2,array3, .) Excel在财务管理中的应用52 4.3 存货管理存货管理 4.3.1 建立存货收发存明细表建立存货收发存明细表 4.3.2 存货台帐及存货查询存货台帐及存货查询 4.3.3 存货的存货的ABC分类模型分类模型 4.3.4 经济订货批量模型经济订货批量模型 Excel在财务管理中的应用53 4.3.1 建立存货收发

26、存明细表建立存货收发存明细表 【例例4-8】 为整列定义名称为整列定义名称 删除网格线删除网格线 运用条件格式设置边框和字体颜色运用条件格式设置边框和字体颜色 利用自定义格式快速输入序列数字利用自定义格式快速输入序列数字 Excel在财务管理中的应用54 4.3.2 存货查询存货查询 【例例4-9】 VLOOKUP函数函数在表格或数值数组的首列查在表格或数值数组的首列查 找指定的数值，并由此返回表格或数组当前行找指定的数值，并由此返回表格或数组当前行 中指定列处的数值。中指定列处的数值。 =VLOOKUP(lookup_value,table_array,col_in dex_num,rang

27、e_lookup) COLUMN函数函数返回给定引用的列标。返回给定引用的列标。 =COLUMN(reference) Excel在财务管理中的应用55 4.3.3 存货的存货的ABC分类模型分类模型 常见的分类标准：常见的分类标准： A类存货：品种数约占类存货：品种数约占10%15%，存货金额约占，存货金额约占80%； B类存货：品种数约占类存货：品种数约占20%30%，存货金额约占，存货金额约占15%； C类存货：品种数约占类存货：品种数约占55%80%，存货金额约占，存货金额约占5%。 Excel在财务管理中的应用56 4.3.3 存货的存货的ABC分类模型分类模型 【例例4-10】 C

28、OUNTA函数函数返回参数列表中非空值的单元返回参数列表中非空值的单元 格个数。利用函数格个数。利用函数 COUNTA 可以计算单元格区可以计算单元格区 域或数组中包含数据的单元格个数。公式为：域或数组中包含数据的单元格个数。公式为： =COUNTA(value1,value2,.) COUNTIF函数函数计算区域中满足给定条件的计算区域中满足给定条件的 单元格的个数。单元格的个数。 =COUNTIF(range,criteria) 绘制两轴线柱形图绘制两轴线柱形图 Excel在财务管理中的应用57 4.3.3 经济订货批量模型经济订货批量模型 存货的相关总成本（总订储费用）存货的相关总成本（

29、总订储费用） 经济订货批量经济订货批量 最佳的订货次数最佳的订货次数 最低的总订储费用最低的总订储费用 式中：式中：Q为一次订货批量；为一次订货批量；D为一定时期存货的需求量；为一定时期存货的需求量；A 为一次订货费；为一次订货费；P为存货单价；为存货单价；K为存货的存储费率；为存货的存储费率；P K即即 为单位存储费用。为单位存储费用。 KP Q A Q D T 2 PK DA Q 2 * * * Q D N DAPKT2* Excel在财务管理中的应用58 4.3.1 经济订货批量模型经济订货批量模型 【例例4-11】 SQRT函数函数开平方开平方 规划求解规划求解 工具工具/规划求解规划

30、求解 建立和使用自定义函数建立和使用自定义函数 工具工具/宏宏/Visual Basic编辑器编辑器 插入插入/模块模块 插入插入/过程过程 Excel在财务管理中的应用59 5 成本费用管理成本费用管理 5.1 成本费用预测成本费用预测 5.2 成本费用的日常管理成本费用的日常管理 5.3 成本报表的合并计算成本报表的合并计算 5.4 成本费用分析成本费用分析 Excel在财务管理中的应用60 5.1 成本费用预测成本费用预测 5.1.1 利用高低点法预测成本费用利用高低点法预测成本费用 【例例5-1】 MAX、MIN、INDEX函数函数 5.1.2 利用回归分析法预测成本费用利用回归分析法

31、预测成本费用 【例例5-2】 INTERCEPT、SLOPE、TREND函数函数 Excel在财务管理中的应用61 相关的函数相关的函数 MAX函数函数给定参数表中的最大值。公式为：给定参数表中的最大值。公式为： = MAX(number1, number 2, number N) MIN函数函数给定参数表中的最小值。公式为：给定参数表中的最小值。公式为： = MIN(number1, number 2, number N) INDEX函数函数返回表格或区域中的数值或对数值的返回表格或区域中的数值或对数值的 引用。该函数有以下两种形式：引用。该函数有以下两种形式： 返回数组中指定单元格或单元格

32、数组的数值。公式为：返回数组中指定单元格或单元格数组的数值。公式为： = INDEX(array,row_num,column_num) 返回引用中指定的单元格。公式为：返回引用中指定的单元格。公式为： =INDEX(reference,row_num,column_num,area_num) Excel在财务管理中的应用62 相关的函数相关的函数 INTERCEPT函数函数求回归直线的截距。求回归直线的截距。 公式为：公式为： = INTERCEPT (known_ys,known_xs) SLOPE求回归直线的斜率。求回归直线的斜率。 公式为：公式为： = SLOPE(known_ys,k

33、nown_xs) TREND函数函数返回一条线性回归拟合线的值。返回一条线性回归拟合线的值。 公式为：公式为： = TREND(known_ys,known_xs,new_xs,const) Excel在财务管理中的应用63 5.2 成本费用的日常管理成本费用的日常管理 5.2.1 材料费用管理材料费用管理 5.2.2 工资费用管理工资费用管理 5.2.3 制造费用和期间费用管理制造费用和期间费用管理 Excel在财务管理中的应用64 5.2.1 材料费用管理材料费用管理 【例例5-3】制作限额领料单制作限额领料单 数据有效性的设置数据有效性的设置 隐藏行和列隐藏行和列 保护单元格和保护工作表

34、保护单元格和保护工作表 为工作簿设置密码为工作簿设置密码 Excel在财务管理中的应用65 5.2.1 材料费用管理材料费用管理 【例例5-4】计算原材料成本计算原材料成本 SUMPRODUCT函数函数 【例例5-5】编制材料需求量预算表编制材料需求量预算表 MMULT函数函数返回两数组的矩阵乘积。结果矩返回两数组的矩阵乘积。结果矩 阵的行数与阵的行数与 array1 的行数相同，矩阵的列数与的行数相同，矩阵的列数与 array2 的列数相同。的列数相同。 =MMULT(array1,array2) Excel在财务管理中的应用66 5.2.2 工资费用管理工资费用管理 【例例5-6】计算个人

35、所得税计算个人所得税 使用使用IF函数计算函数计算 利用数组公式计算利用数组公式计算 利用自定义函数计算利用自定义函数计算 Excel在财务管理中的应用67 5.2.2 工资费用管理工资费用管理 【例例5-7】计算基本工资和工龄工资计算基本工资和工龄工资 根据职称确定基本工资根据职称确定基本工资 根据工龄计算工龄工资根据工龄计算工龄工资 【例例5-8】计算销售人员年度工资和奖金计算销售人员年度工资和奖金 HLOOKUP函数函数在表格或数值数组的首行查在表格或数值数组的首行查 找指定的数值，并由此返回表格或数组当前列中找指定的数值，并由此返回表格或数组当前列中 指定行处的数值。指定行处的数值。

36、=HLOOKUP(lookup_value,table_array,row_index_n um,range_lookup) Excel在财务管理中的应用68 5.2.2 工资费用管理工资费用管理 【例例5-9】工资数据管理工资数据管理 对单元格区域数据进行相同的修改对单元格区域数据进行相同的修改 圈示无效数据圈示无效数据 标示最大值和最小值标示最大值和最小值 【例例5-10】快速制作工资条快速制作工资条 Excel在财务管理中的应用69 5.2.3 制造费用和期间费用管理制造费用和期间费用管理 【例例5-11】费用项目分类过账费用项目分类过账 【例例5-12】管理费用的预算管理管理费用的预算

37、管理 将负数显示为正数将负数显示为正数 缩位显示数据缩位显示数据 运用名称公式运用名称公式 拆分与冻结窗口拆分与冻结窗口 Excel在财务管理中的应用70 5.3 成本报表的汇总计算成本报表的汇总计算 【例例5-13】成本报表合并计算成本报表合并计算 【例例5-14】营业费用汇总表营业费用汇总表 INDIRECT函数函数返回由文本字符串指定的返回由文本字符串指定的 引用。引用。 ROW函数函数返回行号返回行号 【例例5-15】带控件的成本汇总表带控件的成本汇总表 ISERROR函数函数检测一个值是否为错误值。检测一个值是否为错误值。 =INDIRECT(ref_text,a1) Excel在财

38、务管理中的应用71 5.4 成本费用分析成本费用分析 【例例5-16】费用折线图费用折线图 在图上显示最大和最小值在图上显示最大和最小值 将图表绘制到将图表绘制到PPT文件文件 【例例5-17】产品成本动态图表产品成本动态图表 带有单选按钮带有单选按钮 OFFSET函数函数以指定的引用为参照系，通过给定偏以指定的引用为参照系，通过给定偏 移量返回新的引用移量返回新的引用 【例例5-18】选定月份的费用折线图选定月份的费用折线图 可选择费用项目可选择费用项目 INDIRECT、ADDRESS、CELL、ROW、COLUMN函函 数数 Excel在财务管理中的应用72 6 销售收入管理销售收入管理

39、 6.1 销售收入预测销售收入预测 6.2 销售数据的透视分析销售数据的透视分析 6.3 绘制销售图表绘制销售图表 Excel在财务管理中的应用73 6.1 销售收入预测销售收入预测 6.1.1 利用相关函数预测销售收入利用相关函数预测销售收入 6.1.2 利用数据分析工具预测销售收入利用数据分析工具预测销售收入 6.1.3 利用绘图工具预测销售收入利用绘图工具预测销售收入 Excel在财务管理中的应用74 6.1.1 利用相关函数预测销售收入利用相关函数预测销售收入 相关的函数相关的函数 LINEST函数的功能是使用最小二乘法对已知数据进行最函数的功能是使用最小二乘法对已知数据进行最 佳直线

40、拟合，并返回描述此直线的数组。佳直线拟合，并返回描述此直线的数组。 LOGEST函数函数在回归分析中，计算最符合数据的指数在回归分析中，计算最符合数据的指数 回归拟合曲线，并返回描述该曲线的数值数组。回归拟合曲线，并返回描述该曲线的数值数组。 FORECAST函数函数根据已有的数值计算或预测未来值。根据已有的数值计算或预测未来值。 此预测值为基于给定的此预测值为基于给定的 x 值推导出的值推导出的 y 值。值。 GROWTH函数函数根据现有的数据预测指数增长值。根据现有的数据预测指数增长值。 Excel在财务管理中的应用75 6.1.2 利用数据分析工具预测销售收入利用数据分析工具预测销售收入

41、 【例例6-1】基于移动平均法的销售预测基于移动平均法的销售预测 工具工具/数据分析数据分析/移动平均移动平均 【例例6-2】基于回归分析法的销售预测基于回归分析法的销售预测 工具工具/数据分析数据分析/回归回归 Excel在财务管理中的应用76 6.1.3 利用绘图工具预测销售收利用绘图工具预测销售收 入入 【例例6-3】 创建散点图创建散点图 添加趋势线添加趋势线 Excel在财务管理中的应用77 6.2 销售数据的透视分析销售数据的透视分析 6.2.1 整理数据表整理数据表 6.2.2 建立销售数据透视表建立销售数据透视表 6.2.3 创建销售数据透视图创建销售数据透视图 【例例6-4】

42、 Excel在财务管理中的应用78 6.2.1 整理数据表整理数据表 取消合并单元格取消合并单元格 删除空行和空列删除空行和空列 删除字符串前后的空格删除字符串前后的空格 适用合法的日期数据适用合法的日期数据 Excel在财务管理中的应用79 6.2.2 建立销售数据透视表建立销售数据透视表 数据数据/数据透视表和数据透视图数据透视表和数据透视图 数据透视表的布局和修改数据透视表的布局和修改 修改字段名称修改字段名称 设置字段的数字格式设置字段的数字格式 取消数据透视表和行或列汇总取消数据透视表和行或列汇总 取消字段的分类汇总取消字段的分类汇总 合并数据标志合并数据标志 设置错误值或空值的显示

43、方式设置错误值或空值的显示方式 更新数据透视表的数据更新数据透视表的数据 Excel在财务管理中的应用80 数据透视表的高级应用数据透视表的高级应用 分组查看数据分组查看数据 按日期、地区、商品类别等分组按日期、地区、商品类别等分组 设置字段的显示方式设置字段的显示方式 差异、差异百分比差异、差异百分比 占同行、同列、总合百分比占同行、同列、总合百分比 累计值显示累计值显示 纵向差异比较纵向差异比较 添加自定义计算字段或计算项添加自定义计算字段或计算项 Excel在财务管理中的应用81 6.2.3 创建销售数据透视图创建销售数据透视图 创建数据透视图创建数据透视图 使用数据透视图使用数据透视图

44、 编辑数据透视图编辑数据透视图 6.2.4 快速合并计算多重区域的销售数据快速合并计算多重区域的销售数据 【例例6-5】 Excel在财务管理中的应用82 6.3 绘制销售图表绘制销售图表 6.3.1 组合销售图表组合销售图表 【例例6-6】 6.3.2 动态销售图表动态销售图表 【例例6-7】 Excel在财务管理中的应用83 7 量本利分析量本利分析 7.1 本量利分析本量利分析 【例例7-1】单品种保本点的计算单品种保本点的计算 【例例7-2】 多品种保本点的计算多品种保本点的计算 【例例7-3】绘制盈亏平衡分析图绘制盈亏平衡分析图 7.2 利润敏感性分析利润敏感性分析 【例例7-4】

45、7.3 确保实现目标利润的措施分析确保实现目标利润的措施分析 【例例7-5】 Excel在财务管理中的应用84 8 财务报表分析模型财务报表分析模型 8.1 资产负债表分析模型资产负债表分析模型 8.2 利润表分析模型利润表分析模型 8.3 现金流量表分析模型现金流量表分析模型 8.4 财务比率分析模型财务比率分析模型 8.5 杜邦系统分析模型杜邦系统分析模型 8.6 综合财务分析模型综合财务分析模型 8.7 绘制财务指标雷达图绘制财务指标雷达图 Excel在财务管理中的应用85 8 财务报表分析模型财务报表分析模型 【例例8-1】-【例例8-7】财务报表分析模型财务报表分析模型 制作模型的封

46、面制作模型的封面通过本例学习通过本例学习 插入图形对象、插入艺术字、插入超链接插入图形对象、插入艺术字、插入超链接 AND、ISBLANK等函数等函数 制作雷达图制作雷达图 录制宏录制宏 Excel在财务管理中的应用86 9 长期投资决策长期投资决策 9.1 投资项目现金流量的计算投资项目现金流量的计算 9.2 评价投资项目的指标与方法评价投资项目的指标与方法 9.3 几种不同类型的长期投资决策几种不同类型的长期投资决策 9.4 投资项目的风险分析投资项目的风险分析 Excel在财务管理中的应用87 9.1 投资项目现金流量的计算投资项目现金流量的计算 9.1.1 投资项目现金流量的构成与计算

47、投资项目现金流量的构成与计算 9.1.2 折旧的计算方法与折旧函数折旧的计算方法与折旧函数 9.1.3 绘制现金流量图绘制现金流量图 Excel在财务管理中的应用88 9.1.1 投资项目的现金流量构成与计算投资项目的现金流量构成与计算 v现金流量的构成现金流量的构成 初始现金流量初始现金流量 营业现金流量营业现金流量 终结现金流量终结现金流量 现金流量的概念现金流量的概念 现金流入量现金流入量 现金流出量现金流出量 现金净流量现金净流量 Excel在财务管理中的应用89 初始和终结现金流量初始和终结现金流量 初始现金流量初始现金流量 固定资产投资固定资产投资 流动资产投资流动资产投资 其他投

48、资费用其他投资费用 原有固定资产的变价原有固定资产的变价 净收入净收入 v终结现金流量终结现金流量 固定资产的残值净收固定资产的残值净收 入或变价净收入入或变价净收入 原来垫支的流动资金原来垫支的流动资金 回收额回收额 Excel在财务管理中的应用90 营业现金流量营业现金流量 年营业现金净流量年营业现金净流量=营业收入付现成本所得营业收入付现成本所得 税税 年营业现金流量年营业现金流量=净利润净利润+折旧折旧 年营业现金流量年营业现金流量=税后收入税后成本税后收入税后成本+税负减税负减 少少 =营业收入营业收入（1税率）付现成本税率）付现成本（1税率）税率） +折旧折旧税率税率 其中：折旧其

49、中：折旧税率被称为税率被称为“折旧抵税额折旧抵税额”或或“税收档板税收档板” Excel在财务管理中的应用91 9.1.2 折旧的计算方法与折旧函数折旧的计算方法与折旧函数 直线折旧法直线折旧法SLN函数函数 平均年限法平均年限法 工作量法工作量法 加速折旧法加速折旧法 余额递减法余额递减法DB函数函数 双倍余额递减法双倍余额递减法DDB和和VDB函数函数 年数总和法年数总和法SYD函数函数 Excel在财务管理中的应用92 9.1.3 绘制现金流量图绘制现金流量图 【例9-1】 计算折旧计算折旧 计算净现金流量计算净现金流量 绘制现金流量图绘制现金流量图 Excel在财务管理中的应用93 9

50、.2 评价长期投资项目的指标和方法评价长期投资项目的指标和方法 9.2.1 计算平均报酬率计算平均报酬率 9.2.2 计算投资回收期计算投资回收期 9.2.3 计算净现值计算净现值 9.2.4 计算获利指数计算获利指数 9.2.5 计算内部收益率计算内部收益率 Excel在财务管理中的应用94 9.2.1 计算平均报酬率计算平均报酬率 平均报酬率的计算公式平均报酬率的计算公式 【例例9-2】平均报酬率计算与查询平均报酬率计算与查询 初始投资 年均净现金流量 平均报酬率 Excel在财务管理中的应用95 相关函数相关函数 AVERAGE函数函数 ABS函数函数 VLOOKUP函数函数 MATCH

51、函数函数 Excel在财务管理中的应用96 9.2.2 计算投资回收期计算投资回收期 静态投资回收期静态投资回收期在不考虑资金时间价值的情况在不考虑资金时间价值的情况 下，用投资项目经营期的净现金流量回收初始投资下，用投资项目经营期的净现金流量回收初始投资 所用的时间。所用的时间。 在初始投资以后未来各期净现金流量相等的情况下，静在初始投资以后未来各期净现金流量相等的情况下，静 态投资回收期的计算公式为：态投资回收期的计算公式为： 在初始投资以后未来各期净现金流量不相等的情况下，在初始投资以后未来各期净现金流量不相等的情况下， 静态投资回收期的计算公式为：静态投资回收期的计算公式为： 式中：式

52、中：n为累计净现金流量第一次出现正值的年份为累计净现金流量第一次出现正值的年份 年净现金流量 初始投资额 静态投资回收期 年的净现金流量第 年末尚未回收的投资 ）（静态投资回收期 n ) 1n( 1n Excel在财务管理中的应用97 9.2.2 计算投资回收期计算投资回收期 动态投资回收期动态投资回收期在考虑资金时间价值的在考虑资金时间价值的 情况下，用投资项目经营期的净现金流量的情况下，用投资项目经营期的净现金流量的 现值回收初始投资所用的时间。计算公式为：现值回收初始投资所用的时间。计算公式为： 年净现金流量的现值第 年末尚未回收的投资 ）（动态投资回收期 n ) 1n( 1n 式中：式

53、中：n为累计净现金流量现值第一次出现正值的年份为累计净现金流量现值第一次出现正值的年份 Excel在财务管理中的应用98 9.2.2 计算投资回收期计算投资回收期 【例例9-3】 NPER函数函数基于固定利率及等额分期付款方基于固定利率及等额分期付款方 式，返回某项投资（或贷款）的总期数。公式为式，返回某项投资（或贷款）的总期数。公式为 = NPER（rate，pmt，pv，fv，type） COUNTIF函数函数计算给定区域内满足特定条计算给定区域内满足特定条 件的单元格的数目。公式为：件的单元格的数目。公式为： = COUNTIF(range,criteria) Excel在财务管理中的应用99 9.2.3 计算净现值计算净现值 净现值的计算公式为：净现值的计算公式为： 式中：式中：CIt为投资项目第为投资项目第t年的现金流入量，年的现金流入量，COt为为 投资项目第投资项目第t年的现金流出量，年的现金流出量，NCFt为投资项目为投资项目 第第t年的净现金流量，年的净现金流量，i为贴现率，为贴现率，n为投资项目的为投资项目的 寿命期。寿命期。 n