Excel在财务管控中的36个应用

纳税人俱乐部400-650-9936Excel在财务管控中的36个应用主讲教师韩良智教授[e-mail]纳税人俱乐部400-650-9936目录1Excel在销售收入管控中的11个应用2Excel在成本费用管控中的8个应用3Excel在资产管控中的8个应用4Excel在财务报表分析中的9个应用纳税人俱乐部400-650-99361Excel在销售收入管控中的11个应用•案例1订单数据整理与筛选分析•案例2销售数据整理与分类汇总分析•案例3制作销售数据的多级与嵌套分类汇总表•案例4销售数据的分级显示•案例5使用函数制作销售月报和周报（不分商品反映）•案例6使用函数制作销售月报和周报（分商品反映）•案例7销售数据整理与多维度透视分析•案例8运用数据透视表工具制作销售数据比较分析表•案例9各分公司多重区域销售数据汇总分析•案例10制作销售预算完成情况进度图•案例11制作动态销售图表纳税人俱乐部400-650-9936案例1订单数据整理与筛选分析•数据分列–设置分隔符号；设置列数据格式•自动筛选–筛选某个项目；空白/非空白；自定义筛选•高级筛选–逻辑与条件–逻辑或条件–筛选不含重复记录的数据表纳税人俱乐部400-650-9936案例2销售数据整理与分类汇总分析•将文本型数据转换为纯数字–利用智能标记–利用选择性粘贴工具•制作分类汇总表–按分类字段排序；–建立分类汇总表•复制与与编辑二级分类汇总结果–定位可见单元格–快速删除多余的信息•删除分类汇总结果纳税人俱乐部400-650-9936案例3制作销售数据的多级与嵌套分类汇总表•多级分类汇总表–求和–计数–求平均值–最大值–最小值–……..•嵌套分类汇总表–按多个关键字排序–由外向内进行纳税人俱乐部400-650-9936案例4销售数据的分级显示•定位空单元格•快速填充小计数•自动建立分级显示•分级显示表的使用方法•清除分级显示纳税人俱乐部400-650-9936案例5使用函数制作销售月报和周报（不分商品反映）•整理数据清单–MONTH函数：返回一个日期型数据中的月份数字–WEEKNUM函数：返回一个日期型数据中的周次数字•为单元格或单元格区域定义名称的方法–在名称框里定义–利用菜单命令定义–批量定义名称的方法•SUMIF函数：根据指定条件对若干单元格求和•创建列表的方法纳税人俱乐部400-650-9936案例6使用函数制作销售月报和周报（分商品反映）•SUMIFS函数：多条件求和（Excel2003没有这个函数）•SUMPRODUCT函数：将给定的几个数组中的对应元素相乘并返回乘积之和•建立与复制公式•引用单元格的方式纳税人俱乐部400-650-9936案例7销售数据整理与多维度透视分析•销售数据整理–LEFT函数：从文本字符串左边提取指定数目的字符–MID函数：从文本字符串的指定位置开始提取特定数目的字符–RIGHT函数：从文本字符串右边提取若干个字符–DATE函数：返回代表特定日期的序列号纳税人俱乐部400-650-9936案例7销售数据整理与多维度透视分析•制作数据透视表–2003：数据/数据透视表与数据透视图–2007：插入/数据透视表–合理设置数据透视表的布局•页字段（报表筛选）•行字段（行标签）•列字段（列标签）•数据字段（数值）纳税人俱乐部400-650-9936案例7销售数据整理与多维度透视分析•编辑数据透视表–修改字段名称–设置字段的数字格式–取消数据透视表行总计或列总计–取消字段的分类汇总–合并数据标志–设置错误值或空值的显示方式–更新数据透视表的数据纳税人俱乐部400-650-9936案例7销售数据整理与多维度透视分析•使用数据透视表多维度分析数据–修改数据透视表布局–显示明细数据–通过组合字段创建多维数据汇总表•按日期、地区、商品类别等分组纳税人俱乐部400-650-9936案例7销售数据整理与多维度透视分析•制作与使用数据透视图–在数据清单基础上制作数据透视图–在数据透视表基础上制作数据透视图纳税人俱乐部400-650-9936案例8运用数据透视表工具制作销售数据比较分析表•通过设置字段显示方式创建各种比较分析表–数据的显示方式•普通•差异•差异百分比•占同行或同列百分比•占总合百分比•按某一字段汇总……–创建各种比较分析表纳税人俱乐部400-650-9936案例9各分公司多重区域销售数据汇总分析•利用合并计算工具制作汇总表–设置引用区域–创建连至源数据的链接•利用数据透视表汇总多重区域的分公司销售数据–设置引用区域–编辑与美化汇总表纳税人俱乐部400-650-9936案例10制作销售预算完成情况进度图•制作条形图•编辑与美化图表2013年上半年各分公司销售预算完成情况72%59%45%52%67%0%20%40%60%80%100%北京分公司沈阳分公司武汉分公司太原分公司上海分公司纳税人俱乐部400-650-9936案例11制作动态销售图表•INDEX函数：返回列表或数组中的元素值•设置组合框控件动态销售图表0100002000030000甲产品乙产品丙产品丁产品戊产品产品元5月纳税人俱乐部400-650-99362Excel在成本费用管控中的8个应用•案例12整理费用报销数据与制作费用汇总表•案例13工资与个人所得税计算•案例14实发工资尾数处理•案例15快速制作工资条•案例16制作费用报销单•案例17费用数据自动归类•案例18制作多期营业费用自动汇总表•案例19制作管理费用预算执行情况追踪分析图纳税人俱乐部400-650-9936案例12整理费用报销数据与制作费用汇总表•整理费用报销数据–利用分列工具整理日期数据–使用函数提取经手人信息–使用查找替换功能转换文本数字•FIND函数：返回一个字符串在另一个字符串中的起始位置•费用报销数据透视分析纳税人俱乐部400-650-9936案例13工资与个人所得税计算•VLOOKUP函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值•TODAY函数：返回系统当前的日期•INT函数：无条件舍去小数，只保留整数•IF函数：执行真假值判断，根据逻辑计算的真假值，返回不同结果纳税人俱乐部400-650-9936案例14实发工资尾数处理•ROUND函数：返回按指定位数对数值进行四舍五入处理后的结果•ROUNDUP函数：朝着远离零值的方向按指定位数向上舍入数字•ROUNDDOWN函数：朝着靠近零值的方向按指定位数向下舍入数字•CEILING函数：沿着绝对值增大的方向向上舍入为最接近的指定数字的倍数•FLOOR函数：沿着绝对值减小的方向向下舍入为最接近的指定数字的倍数纳税人俱乐部400-650-9936案例15快速制作工资条纳税人俱乐部400-650-9936案例16制作费用报销单•设置数据的有效性•NUMBERSTRING函数：将数字按指定的格式转换成大写•删除网格线•隐藏部分行和列•保护部分单元格•保护工作表纳税人俱乐部400-650-9936案例17费用数据自动归类•设置数据有效性•运用条件格式•自动设置边框线•正确设置引用单元格的方式–费用按部门归类–费用按性质归类纳税人俱乐部400-650-9936案例18制作多期营业费用自动汇总表•新增月份工作表中的数据自动列示在汇总表中–IF函数：逻辑判断–ISERROR函数：判断所引用的参数是否为错误值–ROW函数：返回所引用的单元格的行号–INDIRECT函数：返回由文本字符串指定的引用纳税人俱乐部400-650-9936案例19制作管理费用预算执行情况追踪分析图•新增月份的数据自动显示在图中管理费用预算执行情况追踪分析图-1000-5000500100015002000250030001月2月3月4月5月6月7月8月9月10月11月12月元差异数预算数实际数纳税人俱乐部400-650-99363Excel在资产管控中的8个应用•案例20普通日记账规范整理与制作现金日记账•案例21应收账款余额计算•案例22信用政策自动调整•案例23制作应收账款账龄分析表•案例24快速编制暂付款期末余额明细表•案例25编制存货的收发存汇总表并设置库存量预警•案例26动态查询一条存货信息•案例27动态查询某一项存货信息纳税人俱乐部400-650-9936案例20普通日记账规范整理与制作现金日记账•整理日期数据•整理凭单号数据•快速填充缺失的数据•筛选现金日记账•计算现金余额纳税人俱乐部400-650-9936案例21应收账款余额计算•为单元格区域定义名称•运用条件求和函数纳税人俱乐部400-650-9936案例22信用政策自动调整•IF函数•OR函数：进行“逻辑或”关系的判断纳税人俱乐部400-650-9936案例23制作应收账款账龄分析表•为单元格区域指定名称•HLOOKUP函数：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值•SUMPRODUCT函数：多条件求和纳税人俱乐部400-650-9936案例24快速编制暂付款期末余额明细表•自动筛选•COUNTIF函数：计算区域中满足给定条件的单元格的个数纳税人俱乐部400-650-9936案例25编制存货的收发存汇总表并设置库存量预警•设置数据有效性•IF函数•ISERROR函数•VLOOKUP函数•设置条件格式纳税人俱乐部400-650-9936案例26动态查询一条存货信息•定义名称•VLOOKUP函数•COLUMN函数：返回引用的参数所在列的序号•创建列表纳税人俱乐部400-650-9936案例27动态查询某一项存货信息•INDEX函数•MATCH函数：返回在指定方式下与指定数值匹配的数组中元素的相应位置纳税人俱乐部400-650-99364Excel在财务报表分析中的9个应用•案例28编制比较财务报表和共同比报表•案例29绘制资产结构分析图•案例30绘制收入和利润趋势分析图•案例31绘制净利润影响因素分析图•案例32绘制两期财务指标对比分析图•案例33计算财务比率•案例34绘制杜邦系统图•案例35制作财务指标雷达图•案例36报表分析模型的封面纳税人俱乐部400-650-9936案例28编制比较财务报表和共同比报表•缩位显示数字自定义格式代码：–缩小1百位0.00–缩小1千位显示数字：0.00，–缩小1万位显示数字：0!.0,–缩小10万位显示数字：0!.00,–缩小100万位显示数字：0.00,,–缩小1000万位显示数字：0!.0,,–缩小1亿位显示数字：0!.00,,–缩小10亿位显示数字：0.00,,,•通过组合方式设置分级显示纳税人俱乐部400-650-9936案例29绘制资产结构分析图ME公司大类资产构成图非流动资产合计,397亿元,78%流动资产合计,112亿元,22%ME公司资产构成图货币资金3%交易性金融资产2%应收账款5%存货12%长期股权投资11%固定资产净值46%在建工程15%无形资产6%纳税人俱乐部400-650-9936案例30绘制收入和利润趋势分析图GM公司收入和利润变动趋势分析图0.0050.00100.00150.00200.002011年2010年2009年2008年2007年年度亿元0%10%20%30%40%50%60%70%80%营业收入净利润销售净利率纳税人俱乐部400-650-9936案例31净利润因素分析图GM公司2011年净利润影响因素分析图184.0215.5124.777.2016.748.515.8322.0312.8713.1930.84113.350.0050.00100.00150.00200.00营业收入营业成本营业税金及附加销售费用管理费用财务费用资产减值损失投资收益营业外收入营业外支出所得税费用净利润亿元纳税人俱乐部400-650-9936案例32绘制两期财务指标对比分析图NK公司两年财务指标对比分析图38.21%13.76%21.88%23.96%32.45%33.56%15.11%23.62%20.65%29.91%资产负债率净资产收益率销售净利率销售收入增