《Excel在财务管控中的扩展应用thq.pptx》由会员分享，可在线阅读，更多相关《Excel在财务管控中的扩展应用thq.pptx（91页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Excel 在财务管控中的扩展应用目录1 Excel 在量本利分析中的应用2 Excel 在资金时间价值计算中的应用3 Excel 在长期投资决策中的应用4 Excel 在投资风险分析中的应用5 Excel 在日常财务管理中的应用1 Excel 在量本利分析中的应用1.1 量本利分析的基本假定1.2 混合成本的分解1.3 单品种盈亏平衡点的计算1.4 多品种盈亏平衡点的计算1.5 实现目标利润的单一措施分析1.6 目标利润的多因素敏感性分析1.7 绘制动态盈亏分析面积图1.8 绘制动态盈亏分析折线图 1.1 量本利分析的基本假定 量本利分析 又称本量利分析（CVP 分析）是指对业务量、成本和利
2、润之间的关系进行分析 产销平衡假定 生产量=销售量 成本按性态（习性）分类 变动成本总额随业务量正比例变动 固定成本总额不随业务量变动1.2 混合成本的分解 混合成本 总额随业务量变化，但不成正比例变化。分解混合成本的常用方法 高低点法 回归分析法 使用相关函数分解混合成本 SLOPE 函数：计算回归直线的斜率 INTERCEPT 函数：计算回归直线的截距 使用绘图工具分解混合成本 绘制散点图 添加趋势线【例1-1】分解混合成本1.3 单品种盈亏平衡点的计算 盈亏平衡点 又称保本点 是指不亏不盈即利润为0的那一点 盈亏平衡点的表示方法 盈亏平衡点销售量（保本点销售量）盈亏平衡点销售额（保本点销
3、售额）盈亏平衡点作业率（保本点作业率）1.3 单品种盈亏平衡点的计算 利润=销售收入变动总成本固定成本=销售量(单价单位变动成本)固定成本 当利润=0 时，可得：盈亏平衡点销售量=固定成本/(单价单位变动成本)盈亏平衡点销售额=盈亏平衡点销售量 单价1.3 单品种盈亏平衡点的计算 其他相关指标：单位边际贡献=单价单位变动成本 边际贡献总额=销售收入变动总成本=销售量(单价单位变动成本)边际贡献率=单位边际贡献/单价=边际贡献总额/销售收入1.3 单品种盈亏平衡点的计算 其他相关指标：安全边际量=正常销售量盈亏平衡点销售量 安全边际额=正常销售额盈亏平衡点销售额 安全边际率=安全边际量/正常销售
4、量=安全边际额/正常销售额 盈亏临界点作业率=盈亏平衡点销售量/正常销售量=1 安全边际率1.3 单品种盈亏平衡点的计算【例1-2】单品种盈亏平衡点的计算 直接利用公式计算 运用单变量求解工具 2003:工具/单变量求解 2007:数据/数据工具/假设分析/单变量求解1.4 多品种盈亏平衡点的计算 多品种盈亏平衡点的计算方法 加权平均法 顺序法 分算法 联合单位法 主要产品法 1.4 多品种盈亏平衡点的计算 加权平均法以各种产品的预计销售收入占总收入的比重为权数，确定企业加权平均的综合边际贡献率，再计算盈亏平衡点。【例1-3】多品种盈亏分析加权平均法 SUM 函数：无条件求和 SUMPRODU
5、CT 函数:返回数组间对应元素乘积之和 输入数组公式：【Shift+Ctrl+Enter】单变量求解1.4 多品种盈亏平衡点的计算 顺序法按照事先规定的品种顺序，依次用各种产品的边际贡献补偿企业的固定成本，直至全部固定成本由产品的边际贡献补偿完为止，从而得到盈亏平衡点。【例1-4】多品种盈亏分析顺序法 IF函数：根据逻辑判断的真假值返回不同的结果 AND 函数：进行逻辑与条件的判断 ABS 函数：返回数值的绝对值1.4 多品种盈亏平衡点的计算 分算法将企业的全部固定成本按一定标准分配给各种产品，然后再对每种产品按单品种的情况分别进行量本利分析。联合单位法根据各种产品间产销量的最小比例，将其作为
6、一个联合单位，确定每一联合单位的单价、单位变动成本，进行量本利分析。主要产品法在多种产品中，将边际贡献所占比重较大的产品作为主要产品，以该产品为主按单品种方法进行量本利分析。1.5 实现目标利润的单一措施分析 逐个考虑的单一因素 销售量 单价 单位变动成本 固定成本【例1-5】实现目标利润的单一措施分析 单变量求解1.6 目标利润的多因素敏感性分析 目标利润的影响因素 销售量、单价、单位变动成本、固定成本【例1-6】目标利润的多因素敏感性分析 设置滚动条控件 2003：【视图】/【工具栏】/【窗体】2007：【开发工具】/【控件】/【表单控件】1.7 绘制动态盈亏分析面积图1.8 绘制动态盈亏
7、分析折线图2 Excel 在资金时间价值计算中的应用2.1 单笔现金流的终值计算2.2 单笔现金流的现值计算2.3 年金终值的计算2.4 年金现值的计算2.5 求解利率2.6 名义年利率与有效年利率计算2.7 编制分期还款计划表2.8 分期还款额的敏感性分析 2.1 单笔现金流的终值计算 单利终值的计算 式中：FS 为单利终值；P 为现在的一笔资金；iS为单利年利率；n 为计息期限。2.1 单笔现金流的终值计算 复利终值的计算公式 或 式中：P 为现在的一笔收款或付款；i 为复利年利率；n 为年限；F 为复利终值；FVIFi,n=(1+i)n为复利终值系数。2.1 单笔现金流的终值计算【例2-
8、1】单利与复利终值的对比分析 FV 函数基于固定利率及等额分期付款方式返回某项投资的未来值。公式为：=FV(rate,nper,pmt,pv,type)建立与复制公式 显示公式与显示计算结果 公式审核2.1 单笔现金流的终值计算 创建图表2.1 单笔现金流的终值计算【例2-2】系列不规则现金流的终值 使用FV 函数计算 运用数组公式计算【例2-3】浮动利率情况下的终值计算 FVSCHEDULE 函数：基于一系列复利利率计算未来值。公式为=FVSCHEDULE(principal,schedule)式中：principal 为现值；schedule 为利率数组。2.2 单笔现金流的现值计算 单利
9、现值 式中：P 为现值，F 为未来值，is为单利年利率，n 为期限。复利现值 或：式中：PVIFi,n为复利现值系数2.2 单笔现金流的现值计算【例2-4】单利与复利现值的比较分析 PV 函数基于固定利率及等额分期付款方式返回某项投资的现值。公式为：=PV(rate,nper,pmt,fv,type)绘制图表/使用控件【例2-5】系列不规则现金流的现值 使用PV 函数计算 运用数组公式计算2.3 年金终值的计算 年金每期等额发生的收付款系列 普通年金：每期期末发生（后付年金）先付年金：每期期初发生（即付年金）延期年金：一定时期后才开始发生的年金 永续年金：无限期发生的年金2.3 年金终值的计算
10、【例2-6】年金终值的计算 普通年金的终值计算 先付年金的终值计算 年金终值的选择计算2.4 年金现值的计算【例2-7】年金现值的计算 普通年金的现值计算 先付年金的现值计算 年金现值的选择计算2.5 求解利率 在已知现值、终值、年金三个参数中的任意两个参数的情况下，可求解未知的利率 RATE 函数返回未来款项的各期利率。公式为：=RATE(nper,pmt,pv,fv,type,guess)2.5 求解利率【例2-8】求解利率 假定您有一位刚好1岁的孩子，您希望在17年后资助他60,000元上大学。如果您目前只有5,000可用来投资，那么您需要找到一个报酬率为多高的投资项目，才能实现您的这个
11、心愿？如果您每年年末都有2000元可用于投资，那么需要多高的投资报酬率才够用？2.6 名义年利率与有效年利率计算若给定的年利率为i，每年计息m 次，那么现在的一笔资金P 在n 年末的终值为：在这种情况下，给定的年利率i 为名义年利率，按每年实际获得的利息计算的年利率为有效年利率。2.6 名义年利率与有效年利率计算 有效年利率r 与名义年利率i 之间的关系为：式中m 为每年计息次数 连续计息情况下：式中e 为自然对数的底数，e 2.718282.6 名义年利率与有效年利率计算【例2-9】有效年利率与名义年利率的计算 EFFECT 函数计算有效年利率 NOMINAL 函数计算名义年利率 2003：
12、需要首先加载分析工具库 EXP函数返回 e 的 n 次幂2.7 编制分期还款计划表【例2-10】编制分期还款计划表 还本付息方式 等额本金法：分期等额还本余额计息 等额摊还法：分期等额还本付息 还本付息的频率 按年偿还 按月偿还 2.7 编制分期还款计划表 基于固定利率及等额分期付款方式计算PMT 函数：返回投资或贷款的每期付款额。=PMT(rate,nper,pv,fv,type)IPMT 函数：返回投资或贷款在某一给定期次内的利息偿还额。=PPMT(rate,per,nper,pv,fv,type)PPMT 函数：返回投资或贷款在某一给定期次内的本金偿还。=IPMT(rate,per,np
13、er,pv,fv,type)2.7 编制分期还款计划表 基于固定利率及等额分期付款方式计算CUMIPMT 函数：返回一笔贷款在给定的start_period 到end_period 期间累计偿还的利息数额。公式为：=CUMIPMT(rate,nper,pv,start_period,end_period,type)CUMPRINC 函数：返回一笔贷款在给定的start_period 到end_period 期间累计偿还的本金数额。公式为：=CUMPRINC(rate,nper,pv,start_period,end_period,type)2.8 分期等额还款额的敏感性分析【例2-11】分期还
14、款额的模拟运算分析 单因素/双因素敏感性分析 2003：数据/模拟运算表 2007：数据/数据工具/假设分析/数据表【例2-12】分期等额还贷计算器 多因素敏感性分析 使用窗体/表单控件3 Excel 在长期投资决策中的应用3.1 财务预测3.2 固定资产折旧的计算3.3 投资项目的现金流量计算3.4 计算平均报酬率3.5 计算投资回收期3.6 计算净现值3.7 计算获利指数与现值指数3.8 计算内部收益率3.9 互斥投资方案的比较分析3.10 资本限量条件下的投资决策 3.1 财务预测 运用Excel 进行财务预测的方法 利用绘图工具预测 利用相关函数预测 利用数据分析工具预测3.1 财务预
15、测【例3-1】利用绘图工具预测 创建散点图 添加趋势线3.1 财务预测 常用的线性趋势预测函数 INTERCEPT 函数求回归直线的截距 SLOPE 求回归直线的斜率。TREND函数返回一条线性回归拟合线的值。【例3-2】利用相关函数预测3.1 财务预测 利用数据分析工具预测 2003：工具/加载宏/分析工具库 2007：Excel 选项/加载项/转到/分析工具库【例3-3】基于移动平均法的销售预测 2003：工具/数据分析/移动平均 2007：数据/分析/数据分析/移动平均【例3-4】基于回归分析法的销售预测 2003：工具/数据分析/回归 2007：数据/分析/数据分析/回归3.2 固定资
16、产折旧的计算 固定资产折旧的计算方法 直线折旧法 平均年限法 工作量法 加速折旧法 余额递减法 双倍余额递减法 年数总和法直线折旧法平均年限法工作量法某期折旧额=该期实际工作量 每单位工作量折旧额 直线折旧函数与折旧计算模型 直线折旧函数SLN 函数 SLN 函数：返回一项资产每期的直线折旧额。公式为：=SLN(cost,salvage,life)【例3-5】采用直线折旧法计算折旧模型 加速折旧法余额递减法双倍余额递减法年数总和法加速折旧函数与折旧计算模型 定率余额递减法折旧函数DB 函数 DB 函数：使用固定余额递减法，计算一笔资产在给定期间内的折旧值。公式为：=DB(cost,salvag
17、e,life,period,month)年数总和法折旧计算函数SYD函数 SYD 函数：返回某项资产按年限总和折旧法计算的某期折旧额。公式为：=SYD(cost,salvage,life,per)加速折旧函数与折旧计算模型 双倍余额递减法DDB 函数和VDB 函数 DDB 函数：使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。公式为=DDB(cost,salvage,life,period,factor)当按直线法计算的折旧值大于按余额递减计算的折旧值时，如果希望转换到直线折旧法，应使用 VDB 函数。VDB 函数：使用双倍余额递减法或其他指定的方法，返回指定的任何期间内的
18、资产折旧值。公式为=VDB(cost,salvage,life,start_period,end_period,factor,no_switch)【例3-6】采用加速折旧法计算折旧模型折旧选择计算模型【例3-7】使用列表框控件 IF函数的应用 加速折旧函数的应用 3.3 投资项目的现金流量计算 初始现金流量 营业现金流量 终结现金流量3.3 投资项目的现金流量计算 初始现金流量 固定资产投资 流动资产投资 其他投资费用 原有固定资产的变价净收入v 终结现金流量 固定资产的残值净收入或变价净收入 原来垫支的流动资金回收额3.3 投资项目的现金流量计算 每年营业净现金流量的计算方法：年营业净现金流
19、量=营业收入付现成本所得税年营业现金流量=净利润+折旧=(营业收入付现成本折旧)(1 税率)+折旧=营业收入(1 税率)付现成本(1 税率)+折旧 税率=税后营业收入税后付现成本+折旧抵税额3.3 投资项目的现金流量计算【例3-8】运用SLN 函数 运用数组公式 绘制现金流量图3.4 计算平均报酬率 平均报酬率（ARR）是投资项目寿命周期内平均的年投资报酬率。计算公式为：【例3-9】平均报酬率计算与查询模型3.4 计算平均报酬率相关函数 A VERAGE 函数计算给定参数的算术平均值。公式为：=A VERAGE(number1,number 2,number N)ABS 函数返回数字的绝对值。
20、公式为：=ABS(number)3.4 计算平均报酬率相关函数 MAX 函数给定参数表中的最大值。公式为：=MAX(number1,number 2,number N)MIN 函数给定参数表中的最小值。公式为：=MIN(number1,number 2,number N)VLOOKUP 函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。公式为：=VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)3.4 计算平均报酬率相关函数 MATCH 函数返回在指定方式下与指定数值匹配的数组中元素的相应
21、位置。公式为：=MATCH(lookup_value,lookup_array,match_type)INDEX 函数返回表格或区域中的数值或对数值的引用。该函数有以下两种形式：返回数组中指定单元格或单元格数组的数值。公式为：=INDEX(array,row_num,column_num)返回引用中指定的单元格。公式为：=INDEX(reference,row_num,column_num,area_num)3.5 计算投资回收期 静态投资回收期在不考虑资金时间价值的情况下，用投资项目经营期的净现金流量回收初始投资所用的时间。在初始投资以后未来各期净现金流量相等的情况下：在初始投资以后未来各期
22、净现金流量不相等的情况下：式中：n 为累计净现金流量第一次出现正值的年份3.5 计算投资回收期 动态投资回收期在考虑资金时间价值的情况下，用投资项目经营期的净现金流量的现值回收初始投资所用的时间。计算公式为：式中：n 为累计净现金流量现值第一次出现正值的年份3.5 计算投资回收期 相关函数 NPER 函数基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。公式为:=NPER（rate，pmt，pv，fv，type）COUNTIF 函数计算给定区域内满足特定条件的单元格的数目。公式为：=COUNTIF(range,criteria)【例3-10】3.6 计算净现值 净现值的计算公式为
23、：式中：CIt为投资项目第t 年的现金流入量，COt为投资项目第t年的现金流出量，NCFt为投资项目第t 年的净现金流量，i 为贴现率，n 为投资项目的寿命期。若初始投资为K，且在投资期初一次投入，则：3.6 计算净现值 相关函数 NPV 函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。这里的投资净现值是指未来各期现金流的现值总和。公式为：=NPV(rate,value1,value2,.)XNPV 函数返回一组现金流的净现值，这些现金流不一定定期发生。该函数的公式为：=XNPV(rate,values,dates)【例3-11】3.7 计算获利指数与现值指数 获利指数的计算公式
24、为：式中：PI为获利指数；m 为投资期；Kt为投资期内第t 年的净现金流出量；其他各符号的含义如前所述。如果项目的投资额在期初一次性发生，并且初始投资额为K，则：【例3-12】3.8 计算内部收益率 内部收益率（IRR）使投资项目的净现值为零时的贴现率，计算公式为：修正内部收益率（MIRR）的计算公式为：式中：CPt为第t 年正的净现金流量；CNt为第t 年负的净现金流量；n 为项目的寿命期；r 为对正的净现金流量进行再投资的年收益率；k为对负的净现金流量进行贴现的资金成本率；MIRR 为项目的修正内部收益率。3.8 计算内部收益率 相关的函数 RATE 函数返回未来款项的各期利率。公式为=R
25、ATE（nper，pmt，pv，fv，type，guess）IRR 函数返回由数值代表的一组现金流的内部收益率。公式为：=IRR(values,guess)MIRR 函数返回某一连续期间内现金流的修正内部收益率。公式为：=MIRR（values，finance_rate，reinvest_rate）XIRR 函数返回一组现金流的内部收益率，这些现金流不一定定期发生。公式为：=XIRR(values,dates,guess)【例3-13】3.9 互斥投资方案的比较分析 寿命期相同的互斥投资比较【例3-14】使用相关函数计算 运用单变量求解工具 寿命期不同的互斥方案比较 最小公倍寿命法（更新链法）
26、【例3-15】LCM 函数：返回整数的最小公倍数。公式为：=LCM(number1,number2,.)等值年金法（净年值法）【例3-16】3.10 资本限量条件下的投资决策 建立0-1 规划模型3.10 资本限量条件下的投资决策【例3-17】资本限量条件下的投资决策 SUMPRODUCT 函数：返回数组间对应元素的相乘之和 规划求解工具的运用首先需要加载宏 2003：工具/规划求解 2007：数据/数据分析/规划求解4 Excel 在投资风险分析中的应用4.1 投资项目的盈亏平衡分析4.2 基于净现值的敏感性分析4.3 基于净现值的情境分析4.4 投资项目的风险度量与处置 4.1 投资项目的
27、盈亏平衡分析 会计盈亏平衡点相关指标的计算公式：安全边际率=（现有产销量-盈亏平衡点产销量）/现有产销量4.1 投资项目的盈亏平衡分析 基于净现值的盈亏平衡点（现值盈亏平衡点）产销量应该能够满足下面的公式：式中：Qt为第t 年的产销量；pt为第t 年的产品单价；vt为第t年的单位变动成本；Fct 为第t 年的付现固定成本；T 为所得税税率；Dt为第t 年的折旧额；i 为项目的基准收益率；I 为项目的初始投资；n 为项目的寿命期；t 为年份。【例4-1】投资项目盈亏平衡分析4.2 基于净现值的敏感性分析 影响项目净现值的因素 销售量、销售单价 单位变动成本、付现固定成本 初始投资额、寿命期 基准
28、收益率【例4-2】单因素敏感性分析 2003：数据/模拟运算表 2007：数据/数据工具/假设分析/数据表4.2 基于净现值的敏感性分析【例4-3】双因素敏感性分析 2003：数据/模拟运算表 2007：数据/数据工具/假设分析/数据表【例4-4】多因素敏感性分析 2003：窗体控件 2007：表单控件4.3 基于净现值的情境分析 情境分析 乐观情境 最可能情境 悲观情境【例4-5】基于净现值的情境分析 建立名称公式 运用方案管理器 2003：工具/方案 2007：数据/数据工具/假设分析/方案管理器4.4 投资项目的风险度量与处置 度量投资项目风险的有关计算公式：式中：E 为投资收益分布的期
29、望值；为投资收益分布的标准差；V 为变差系数（标准离差率）；Kt为第t 种情况下的投资收益（率）；Pt为第t 种情况出现的概率；n 为出现可能情况的种数。4.4 投资项目的风险度量与处置【例4-6】投资风险的度量 相关函数SQRT 函数计算一个正数的正平方根。公式为：=SQRT(number)NORMDIST函数返回指定平均值和标准偏差的正态分布函数，公式为：=NORMDIST(x,mean,standard_dev,cumulative)4.4 投资项目的风险度量与处置【例4-7】投资风险的处置 根据标准离差率的大小确定相应的贴现率 相关函数 VLOOKUP 函数 NPV 函数5 Excel
30、 在日常财务管理中的应用5.1 制作费用报销高效记录表5.2 处理计算误差5.3 计算收入与利润增长率5.4 规范整理与快速核对数据表5.5 高效汇总财务数据5.6 应付账款到期日计算与提前提醒5.7 存货的ABC 分类5.8 存货经济订货批量计算5.9 固定资产台账管理 5.1 制作费用报销高效记录表【例5-1】制作费用报销高效记录表 数据有效性设置 INDIRECT函数：返回由文本字符串指定的引用 条件格式设置 ROW函数：返回所引用的单元格的行号 MOD 函数：返回两数相除的余数5.2 处理计算误差【例5-2】处理计算误差 ROUND 函数四舍五入 IF函数逻辑判断 MAX 函数取最大值
31、5.3 计算收入与利润增长率【例5-3】收入与利润增长率计算 A VERAGE 函数：返回参数的算数平均值 GEOMEAN 函数：返回正数数组或区域的几何平均值 ABS 函数：取绝对值5.4 规范整理与快速核对数据表【例5-4】规范整理数据表 数据分列 快速拆分数据 快速删除数据表中的空行 高效填充缺失的数据【例5-5】快速核对收款数据 使用COUNTIF 函数核对 运用数据透视表工具核对5.5 高效汇总财务数据【例5-6】编制付款合同执行进度汇总表 定义名称 设置数据有效性 创建列表 运用条件求和函数5.5 高效汇总财务数据【例5-7】快速汇总多个工作表的财务数据 使用SUM 函数 运用合并
32、计算工具 运用数据透视表工具5.6 应付账款到期日计算与提前提醒【例5-8】应付账款到期日计算与提前提醒 计算应付账款到期日 EDATE 函数：计算所指定月数之前或之后的日期 EOMONTH 函数：计算指定日期到月底的天数 应付账款到期日提前提醒 运用条件格式设置5.7 存货的ABC 分类 存货的分类 A 类存货：关键的少数 C 类存货：不重要的多数 B 类存货：居中的类别 常见的分类标准5.7 存货的ABC 分类【例5-9】存货的ABC 分类 COUNTA 函数：返回参数列表中非空值的单元格个数 COUNTIF 函数：计算区域中满足给定条件的单元格的个数 绘制两轴线柱形图5.8 存货经济订货
33、批量计算年订储费用经济订货批量 最佳的订货次数 最低的年订储费用 式中：Q 为一次订货批量；D 为一定时期存货的需求量；A 为一次订货费；P 为存货单价；K 为存货的存储费率；P K即为单位存储费用。5.8 存货经济订货批量计算【例5-10】计算经济订货批量 SQRT 函数：开平方 建立和使用自定义函数 2003：工具/宏/Visual Basic 编辑器 2003：工具/代码/Visual Basic 编辑器 插入/模块 插入/过程5.9 固定资产台账管理【例5-11】固定资产台账管理 登记固定资产台账 使用“记录单”录入和编辑数据 DATEDIF 函数：计算两个日期之间间隔的年、月或天数 制作固定资产数据汇总表