Excel在财务管理中的高级运用-8项内容－张敦力.ppt

1,2019/9/16,1,Excel在财务管理中的高级运用,主讲：张敦力 单位：中南财经政法大学会计学院 电邮： 电话：158 0719 2108,2,2019/9/16,2,内容提要：,一、时间价值系数表的编制 二、常用财务指标计算器的设计 三、贷款偿付计划/租金计划的编制 四、数据库的构建和运用,五、数据的整理和分析 六、数据透视表的编制 七、数据关系的图形描述 八、好好学习、天天向上,3,2019/9/16,3,（一）复利终值系数（PVIF） （二）复利现值系数（FVIF） （三）普通年金终值系数（PVIFA） （四）普通年金现值系数（FVIFA）,一、时间价值系数表的编制,4,2019/9/16,4,（一）复利终值系数（FVIF）,5,2019/9/16,5,1）输入基本数据 2）输入基本计算公式（难点） 3）拖拽,1.混合引用法,6,2019/9/16,6,1）输入基本数据 2）在A2中输入基本计算公式 3）选中单元格区域A2：K12 4）执行运算,2.模拟运算表/数据表法,7,2019/9/16,7,1.通过混合引用拖拽生成 2.通过模拟运算表/数据表编制,（二）复利现值系数（PVIF）,8,2019/9/16,8,1）输入基本数据（如下图）,9,2019/9/16,9,2）输入基本计算公式（如下图A2）,10,2019/9/16,10,Excel 2003： 数据模拟运算表 Excel2010： 数据数据工具假设分析模拟运算表,11,2019/9/16,11,在对话框“输入引用行的单元格”和 “输入引用列的单元格”右侧的空格中分别选定所引用的单元格D1和F1 5）确定,12,2019/9/16,12,13,2019/9/16,13,14,2019/9/16,14,（三）普通年金终值系数（PVIFA）,15,2019/9/16,15,（四）普通年金现值系数（FVIFA）,16,2019/9/16,16,（一）投资回收期 （二）终值 （三）现值或净现值,二、常用财务指标计算器的设计,（四）内含报酬率 （到期收益率/实际利率） （五）有效利率与名义利率,17,2019/9/16,17,如下页图，在B10中输入下列公式即可： =LOOKUP（0,B9:G9,B3:G3）+（-LOOKUP（LOOKUP（0,B9:G9,B3:G3）,B3:G3,B9:G9）/LOOKUP（LOOKUP（0,B9:G9,B3:G3）+1,B3:G3,B4:G4）,（一）投资回收期,18,2019/9/16,18,（四） 债券定价 （五）债券溢折价摊销,19,2019/9/16,19,（二）终值,20,2019/9/16,20,1.表达式法列表求解法 2.PV函数法 3.NPV函数法容易出错 4.XNPV函数法,（三）现值或净现值,21,2019/9/16,21,1.单变量求解法 2.Irr函数法 3.Rate函数法 4.Mirr函数法 5.Xirr函数法,（四）内含报酬率（到期收益率/实际利率）,（五）有效年利率与名义年利率,筹资1000万元，3年期，到期还本,09:51,22,09:51,23,24,2019/9/16,24,Nominal_rate ：名义利率 Npery ：每年的复利期数 Effect_rate ：有效利率,25,2019/9/16,25,利用给定的名义年利率和每年的复利期数，计算有效的年利率。 EFFECT(nominal_rate, npery),1.有效利率,26,2019/9/16,26,#NAME？ Excel2003:工具加载宏分析工具库 Excel2010:文件选项加载项转到加载宏分析工具库,可能的问题,27,2019/9/16,27,Npery 将被截尾取整。 如果任一参数为非数值型，函数 EFFECT 返回错误值 #VALUE!。 如果 nominal_rate 0 或 npery 1，函数 EFFECT 返回错误值 #NUM!。,28,2019/9/16,28,基于给定的实际利率和年复利期数，返回名义年利率。 NOMINAL(effect_rate, npery),2.名义利率,29,2019/9/16,29,（一）等额本金还款法 本金在整个还款期内按借款期限平均偿还，利息按期初待偿本金和利率计算 （二）等额本息还款法 每次等额还本付息,三、贷款偿付计划/租金计划的编制,30,2019/9/16,30,企业借入5年期的长期借款1000万元，固定年利率6%，本金在每年末按借款期限平均偿还，利息按年初待偿本金和利率计算。 问： 每次还款总额为多少？其中有多少是当期承担的利息？有多少用于偿还本金？,简例1（等额本金还款法）：,31,2019/9/16,31,企业借入5年期的长期借款1000万元，固定年利率6%，每年末等额还本付息。 问： 每次还款总额为多少？其中有多少是当期承担的利息？有多少用于偿还本金？,简例2（等额本息还款法）：,32,2019/9/16,32,（一）二/三维数据库 （二）应收账款备查账 （三）薪酬管理（人事档案） （四）存货管理（进销存） （五）试题库与自测,四、数据库的构建和运用,33,2019/9/16,33,1.一般表格：考勤表/通讯录/销售统计表 2.财务报表 3.预算报表,（一）二/三维数据库,为提供以下信息如何设计报表？,每个省的销售额及销售数量 每个市的销售额及销售数量 每个人的销售额及销售数量 不同日期的销售额及销售数量 每个省的每个市的销售额及销售数量 每个省的每个市的每个人的销售额及销售数量 每个省的每个市的每个人的相应日期的销售额及销售数量,35,2019/9/16,35,（二）应收账款备查账,36,2019/9/16,36,1.结构 2.根据身份证号自动生成“出生日期”、“年龄”和“性别” 3.薪酬查询,（三）薪酬管理（人事档案）,37,2019/9/16,37,38,2019/9/16,38,39,2019/9/16,39,（四）存货管理（进销存）,40,2019/9/16,40,（一）计数 （二）合并计算 （三）排序、排位与百分比排位 （四）求最值、平均值、方差和标准差,五、数据的整理和分析（1/2）,41,2019/9/16,41,（五）回归分析和方差分析 （六）列出符合条件的数据 （七）分别计算和展示每一部门的业绩,五、数据的整理和分析（2/2）,42,2019/9/16,42,符合特定条件的单元格有多少个 符合特定条件的数据有多少个 1.单条件计数 2.多条件计数,（一）计数,43,2019/9/16,43,（1）空白单元格的个数：countblank （2）非空白单元格的个数：counta （3）包含数字的单元格个数：count （4）特定内容的单元格个数：Countif frequency,1.单条件计数,44,2019/9/16,44,例：第一行为表头，A列是“姓名”，B列是“性别”，D列是“年龄”，F列是“工作部门”，H列是“基本工资”。 现在要统计： “基本工资”为20003000元、年龄为2030岁的男性的人数。统计结果存放在本工作表的其他列。,2.多条件计数,45,2019/9/16,45,（1）COUNTIFS（Excel2010） =COUNTIFS(B2:B301，“男“，D2:D301，“=20“，D2:D301，“=2000“，H2:H301，“3000“),2.多条件计数,46,2019/9/16,46,（2）SUM（IF（B2:B301=“男“）*（D2:D301=20） *（D2:D301=2000）*（H2:H3013000）,1,0） 输入完公式后，按Ctrl+Shift+Enter键,2.多条件计数,47,2019/9/16,47,将多个相似格式的工作表或数据区域，按指定的方式进行自动匹配计算。,（二）合并计算,48,2019/9/16,49,2019/9/16,49,1.主要目的 2.专用术语 3.合并方式 4.注意事项,（二）合并计算,50,2019/9/16,50,1.单表数据汇总（透视表） 2.多表数据汇总（布局同否？） 3. 明细与汇总并存（结果显示在新表中） 4.多表数据合并 多张两列的表合并成一张多列的新表,（二）合并计算主要目的,51,2019/9/16,51,1.源数据：源工作表/源区域 单个、多个工作表或多重工作簿 2.目标数据：目标工作表/目标区域 不一定与源数据在同一张工作表/工作簿中,（二）合并计算专用术语,52,2019/9/16,52,3.标志：列标签/行标签 4.标志位置：首行/最左列 5.创建指向源数据的链接,（二）合并计算专用术语,53,2019/9/16,53,检查源数据，根据数据结构与布局来确定合适的方式 1.按公式进行合并计算 2.按位置进行合并计算 3.按分类进行合并计算 4.从多个合并区域创建数据透视表,（二）合并计算合并方式,54,2019/9/16,54,对于所有类型或排列的数据，尤其对布局不同的数据，推荐使用公式中的三维引用 在合并计算工作表上，复制或输入待合并计算数据的标志 单击用来存放合并计算数据的单元格 键入合并计算公式，通过三维引用，使公式中的引用指向每张工作表中包含待合并数据的源单元格,1.按公式进行合并计算,55,2019/9/16,55,源数据按相同顺序排列并使用相同标签，合并各区域中相同单元格里的数据,2.按位置进行合并计算,56,2019/9/16,56,设置要合并的数据 为每个区域命名（也可不命名，在引用时用窗口选定区域） 在“数据”菜单中，单击“合并计算” 在“函数”框中，单击需要用来对数据进行合并的汇总函数,2.按位置进行合并计算,57,2019/9/16,57,单击“引用位置”框，再单击要进行合并的第一个区域的工作表标签，键入区域的给定名称，再单击“添加”。对每个区域重复这一步骤 如果要在源区域的数据更改的任何时候都自动更新合并表，并且确认以后在合并中不需要包括不同的或附加的区域，请选中“创建连至源数据的链接”复选框,2.按位置进行合并计算,58,2019/9/16,58,单击“引用位置”框，再单击要进行合并的第一个区域的工作表标签，键入区域的给定名称，再单击“添加”。对每个区域重复这一步骤 如果要在源区域的数据更改的任何时候都自动更新合并表，并且确认以后在合并中不需要包括不同的或附加的区域，请选中“创建连至源数据的链接”复选框,2.按位置进行合并计算,59,2019/9/16,59,根据位置进行合并时，请将“标签位置”下的复选框设为空，源数据中的行或列标志不被复制到合并中，如果需要合并数据的标志，请从源区域之一进行复制或手工输入 这是与按分类进行合并的区别,2.按位置进行合并计算,60,2019/9/16,60,源数据未按顺序排列但使用相同标签，计划合并来自包含匹配标志的行或列中的数据 在“标签位置”下，选中指示标志在源区域中位置的复选框：首行、最左列或两者都选。,3.按分类进行合并计算,61,2019/9/16,61,1.确保每个数据区域为列表（包含相关数据的一系列行）格式：第一行中的每一列都具有标志，同一列中包含相似的数据，并且在列表中没有空行或空列。 2.根据位置进行合并时，请确保每个区域具有相同的布局，请将“标签位置”下的复选框设为空,（二）合并计算注意事项,62,2019/9/16,62,3. 根据分类进行合并时，确保要合并的行或列的标志具有相同的拼写（大小写、缩写、空格）。任一与其他源数据区域中的标志不匹配的标志都会导致合并中出现单独的行或列,（二）合并计算注意事项,63,2019/9/16,63,1.排序 2.排位 3.百分比排位,（三）排序、排位与百分比排位,64,2019/9/16,64,（1）关键词：主要、次要 （是否包括标题） （2）依据：数值、颜色、图标 （3）次序：升、降,1.排序：条件,65,2019/9/16,65,rank函数（ Rank.EQ） 如果多个值具有相同的排位，则返回该组数值的最高排位，对重复数的排位相同，但重复数的存在将影响后续数值的排位,2.排位：列出位次,66,2019/9/16,66,RANK.AVG:如果有一个以上的值排位相同，则返回平均排位 如有4个数并列第5，RANK.EQ为4个5，RANK.AVG4个为6.5（5+6+7+8）/4,2.排位：列出位次,67,2019/9/16,67,1.排名前20%奖励2000 2.排在31%50%的奖励1000 3.排在5180%奖励500 大于该数据的数据个数/（大于该数据的数据个数+小于该数据的数据个数）,思考：如何按名次确定奖金？,68,2019/9/16,68,（1）借助percentrank函数 1 PERCENTRANK（array，x，significance） （2）排位与百分比排位 文件选项加载项转到分析工具库数据分析,3.百分比排位,69,2019/9/16,69,（1）点：原数据的序数 （2）排序后的数据系列：名称随数据的标志而变化 （3）排位：重复数据占用同一位置 （4）百分比：按照降序排列,3.百分比排位,70,2019/9/16,70,1.最值 2.平均值 3.方差和标准差 4.求协方差和相关系数,（四）求最值、平均值、方差和标准差,71,2019/9/16,71,（1）最大值：max函数 （2）最小值：min函数 （3）第几大：large函数 （4）第几小：small函数 2.平均值 3.方差 4.标准差,1.求最值（可能不只1个）,72,2019/9/16,72,average函数 DAVERAGE,2.求平均值,73,2019/9/16,73,（1）总体方差：VarP函数 （2）样本方差：Var函数 （3）总体标准差：StDevp函数 （4）样本标准差：StDev函数,3.求方差和标准差,74,2019/9/16,74,（1）协方差：covar函数 =covar（数据系列1，数据系列2） （2）相关系数：CORREL函数,4.求协方差和相关系数,75,2019/9/16,75,Excel2003:工具数据分析分析工具 Excel2010:数据数据分析分析工具,（五）回归分析和方差分析,76,2019/9/16,76,查询、列示、打印符合一个（或多个）条件的数据项 1.中南地区的销售情况 2.中南地区男、女销售代表的销售情况,（六） 列出符合条件的数据,77,2019/9/16,77,1.自动筛选 2.高级筛选 3.数据透视表,（六） 列出符合条件的数据,78,2019/9/16,78,1.各地区的销售总额 2.男、女销售代表的销售总额 3 .中南地区男、女销售代表的销售总额,（七）分别计算和展示每一部门的业绩,79,2019/9/16,79,1.分类汇总（必须先按汇总的标志排序） 2.分级显示 3.数据透视表,（七）分别计算和展示每一部门的业绩,80,2019/9/16,80,（一）数据透视表的性质 （二）数据透视表的构成要素 （三）数据透视表的编制 （四）多重合并计算数据区域数,六、数据透视表,81,2019/9/16,81,1.一种交互式报表 2.把繁杂的数据简洁化 3.最常用、功能最全的Excel数据分析工具之一,（一）数据透视表的性质（1/3）,82,2019/9/16,82,可以快速分类汇总、比较大量的数据 可以随时选择其中页、行和列中的不同元素，以达到快速查看源数据的不同统计结果 可以随意显示和打印出你所感兴趣区域的明细数据。,（一）数据透视表的性质（2/3）,83,2019/9/16,83,一张“数据透视表”仅靠鼠标移动字段位置，即可变换出各种类型的报表。,（一）数据透视表的性质（3/3）,84,2019/9/16,84,1.页字段/报表筛选页 2.行字段/标签行 3.列字段/标签列 4.数据项/数值值,（二）数据透视表的构成要素,可以自定义这些字段设置以便对其重命名，并将其格式化为需要的格式,85,2019/9/16,85,1.明确目的 2.设定结构 3.鼠标移至数据源中（定位于数据区域内任一单元格）,（三）数据透视表的编制（1/3）,86,2019/9/16,86,4.插入数据透视表 5.设计数据透视表 6.美化版面,（三）数据透视表的编制（2/3）,87,2019/9/16,87,5.设计数据透视表 （1）根据目的和结构拖拽页字段、行字段、列字段和数据项 （2）调整页字段、行字段、列字段、数据项 （3）重新设定汇总项,88,2019/9/16,88,2007和2010中数据透视表向导被隐藏在后台 通过快捷键调出使用（Alt+D+P） 直接自定义在快速访问工具栏中,（四）多重合并计算数据区域数,89,2019/9/16,89,2007：Office按钮Excel选项自定义不在功能区中的命令数据透视表和数据透视图向导添加确定 2010：文件选项快速访问工具栏不在功能区中的命令数据透视表和数据透视图向导添加确定,（四）多重合并计算数据区域数,90,2019/9/16,90,1.页：多重合并计算区域数据透视表中的新字段，分析时可以通过拖拽实现数据分析的目的 页字段数目的最大值为4,（四）多重合并计算数据区域数,91,2019/9/16,91,2.行：始终由数据源中的第一列组成 3.列：是一项高级字段，为数据源中的第一列之外其他列的合并打包,（四）多重合并计算数据区域数,92,2019/9/16,92,4.值：数据源中除首字段外的所有“项”。“值”字段默认数据源的文本项为零值,（四）多重合并计算数据区域数,93,2019/9/16,93,（一）资金习性示意图 （二）投资组合机会集曲线 （三）利润构成分析饼形图 （四）产品销售对比分析柱形图,七、数据关系的图形描述,94,2019/9/16,94,（五）动态图表 （六）主次坐标轴图表 （七）甘特图,七、数据关系的图形描述,95,2019/9/16,95,（一）资金习性示意图,96,2019/9/16,96,（二）投资组合机会集曲线,97,2019/9/16,97,（三）利润构成分析饼形图,98,2019/9/16,98,（四）产品销售对比分析柱形图,99,2019/9/16,99,（五）动态图表,视图