EXCEL在财务管控中的36个运用.ppt

EXCELEXCEL在在财务管控中的管控中的3636个运用个运用目录目录1 Excel在销售收入管控中的在销售收入管控中的8个应用个应用2 Excel在成本费用管控中的在成本费用管控中的8个应用个应用3 Excel在资产管控中的在资产管控中的7个应用个应用4 Excel在负债管控中的在负债管控中的3个应用个应用5 Excel在财务报表分析中的在财务报表分析中的6个应用个应用6 Excel在量本利分析中的在量本利分析中的4个应用个应用 1 Excel在销售收入管控中的在销售收入管控中的8个应用个应用•案例案例1 订单数据整理与筛选分析订单数据整理与筛选分析•案例案例2 销售数据整理与分类汇总分析销售数据整理与分类汇总分析•案例案例3 销售数据的分级显示销售数据的分级显示•案例案例4 使用函数制作销售月报和周报使用函数制作销售月报和周报•案例案例5 销售数据整理与多维度透视分析销售数据整理与多维度透视分析•案例案例6 各分公司多重区域销售数据汇总分析各分公司多重区域销售数据汇总分析•案例案例7 制作销售预算完成情况进度图制作销售预算完成情况进度图•案例案例8 制作动态销售图表制作动态销售图表 案例案例1 订单数据整理与筛选分析订单数据整理与筛选分析•数据分列数据分列–设置分隔符号；设置列数据格式设置分隔符号；设置列数据格式•自动筛选自动筛选–筛选某个项目；空白筛选某个项目；空白/非空白；自定义筛选非空白；自定义筛选•高级筛选高级筛选–逻辑与条件逻辑与条件–逻辑或条件逻辑或条件–筛选不含重复记录的数据表筛选不含重复记录的数据表案例案例2 销售数据整理与分类汇总分析销售数据整理与分类汇总分析•将文本型数据转换为纯数字将文本型数据转换为纯数字–利用智能标记利用智能标记–利用选择性粘贴工具利用选择性粘贴工具 •单级分类汇总单级分类汇总–按分类字段排序；建立分类汇总表按分类字段排序；建立分类汇总表•复制与删除分类汇总结果复制与删除分类汇总结果–定位可见单元格定位可见单元格•多级分类汇总多级分类汇总–求和；计数；求平均值；最大值；最小值等方式求和；计数；求平均值；最大值；最小值等方式 •嵌套分类汇总嵌套分类汇总–由外向内进行由外向内进行案例案例3 销售数据的分级显示销售数据的分级显示•定位空单元格定位空单元格•快速填充小计数快速填充小计数•自动建立分级显示自动建立分级显示•分级显示表的使用方法分级显示表的使用方法•清除分级显示清除分级显示案例案例4 使用函数制作销售月报和周报使用函数制作销售月报和周报•为单元格或单元格区域定义名称的方法为单元格或单元格区域定义名称的方法•MONTH函数：返回一个日期型数据中的月份数字函数：返回一个日期型数据中的月份数字•WEEKNUM函数：返回一个日期型数据中的周次函数：返回一个日期型数据中的周次数字数字•SUMIF函数：根据指定条件对若干单元格求和函数：根据指定条件对若干单元格求和•SUMPRODUCT函数：将给定的几个数组中的对应函数：将给定的几个数组中的对应元素相乘并返回乘积之和元素相乘并返回乘积之和•建立与复制公式建立与复制公式•引用单元格的方式引用单元格的方式•创建列表的方法创建列表的方法 案例案例5 销售数据整理与多维度透视分析销售数据整理与多维度透视分析•销售数据整理销售数据整理–LEFT函数：从文本字符串左边提取指定数目的字函数：从文本字符串左边提取指定数目的字符符–MID函数：从文本字符串的指定位置开始提取特定函数：从文本字符串的指定位置开始提取特定数目的字符数目的字符–RIGHT函数：从文本字符串右边提取若干个字符函数：从文本字符串右边提取若干个字符–DATE函数：返回代表特定日期的序列号函数：返回代表特定日期的序列号 案例案例5 销售数据整理与多维度透视分析销售数据整理与多维度透视分析•制作与编辑数据透视表制作与编辑数据透视表–合理设置数据透视表的布局合理设置数据透视表的布局——页字段；行字段；页字段；行字段；列字段；数据字段列字段；数据字段–修改字段名称修改字段名称–设置字段的数字格式设置字段的数字格式–取消数据透视表行总计或列总计取消数据透视表行总计或列总计–取消字段的分类汇总取消字段的分类汇总–合并数据标志合并数据标志–设置错误值或空值的显示方式设置错误值或空值的显示方式–更新数据透视表的数据更新数据透视表的数据案例案例5 销售数据整理与多维度透视分析销售数据整理与多维度透视分析•使用数据透视表多维度分析数据使用数据透视表多维度分析数据–修改数据透视表布局修改数据透视表布局–显示明细数据显示明细数据–通过组合字段创建多维数据汇总表通过组合字段创建多维数据汇总表•按日期、地区、商品类别分组按日期、地区、商品类别分组–通过设置字段显示方式创建各种比较分析表通过设置字段显示方式创建各种比较分析表•数据的显示方式数据的显示方式——差异；差异百分比；占同行或同列差异；差异百分比；占同行或同列百分比；占总合百分比；累计值显示百分比；占总合百分比；累计值显示……•创建各种比较分析表创建各种比较分析表案例案例5 销售数据整理与多维度透视分析销售数据整理与多维度透视分析•制作与使用数据透视图制作与使用数据透视图–在数据清单基础上制作数据透视图在数据清单基础上制作数据透视图–在数据透视表基础上制作数据透视图在数据透视表基础上制作数据透视图案例案例6 各分公司多重区域销售数据汇总分析各分公司多重区域销售数据汇总分析•利用合并计算工具制作汇总表利用合并计算工具制作汇总表–设置引用区域设置引用区域–创建连至源数据的链接创建连至源数据的链接•利用数据透视表汇总多重区域的分公司销售数据利用数据透视表汇总多重区域的分公司销售数据–设置引用区域设置引用区域–编辑与美化汇总表编辑与美化汇总表案例案例7 制作销售预算完成情况进度图制作销售预算完成情况进度图•制作条形图制作条形图•编辑与美化图表编辑与美化图表案例案例8 制作动态销售图表制作动态销售图表•INDEX函数：返回列表或数组中的元素值函数：返回列表或数组中的元素值•设置组合框控件设置组合框控件 2 Excel在成本费用管控中的在成本费用管控中的8个应用个应用•案例案例9 整理费用报销数据与制作费用汇总表整理费用报销数据与制作费用汇总表•案例案例10 工资与个人所得税计算工资与个人所得税计算•案例案例11 实发工资尾数处理与快速制作工资条实发工资尾数处理与快速制作工资条•案例案例12 制作费用报销单制作费用报销单•案例案例13 费用数据自动归类费用数据自动归类•案例案例14 多种产品材料成本汇总表多种产品材料成本汇总表•案例案例15 制作多期营业费用自动汇总表制作多期营业费用自动汇总表•案例案例16 制作管理费用预算执行情况追踪分析图制作管理费用预算执行情况追踪分析图 案例案例9 整理费用报销数据与制作费用汇总表整理费用报销数据与制作费用汇总表•整理费用报销数据整理费用报销数据–利用分列工具整理日期数据利用分列工具整理日期数据–使用函数提取经手人信息使用函数提取经手人信息–使用查找替换功能转换文本数字使用查找替换功能转换文本数字•FIND函数：返回一个字符串在另一个字符串中的函数：返回一个字符串在另一个字符串中的起始位置起始位置•费用报销数据透视分析费用报销数据透视分析案例案例10 工资与个人所得税计算工资与个人所得税计算•VLOOKUP函数：在表格或数值数组的首列查找函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指指定的数值，并由此返回表格或数组当前行中指定列处的数值定列处的数值•TODAY函数：返回系统当前的日期函数：返回系统当前的日期•INT函数：无条件舍去小数，只保留整数函数：无条件舍去小数，只保留整数•IF函数：执行真假值判断，根据逻辑计算的真假函数：执行真假值判断，根据逻辑计算的真假值，返回不同结果值，返回不同结果 案例案例11 实发工资尾数处理与快速制作工资条实发工资尾数处理与快速制作工资条•实发工资尾数处理实发工资尾数处理–ROUND函数：返回按指定位数对数值进行四舍五入处函数：返回按指定位数对数值进行四舍五入处理后的结果理后的结果–ROUNDUP函数：朝着远离零值的方向按指定位数向函数：朝着远离零值的方向按指定位数向上舍入数字上舍入数字–ROUNDDOWN函数：朝着靠近零值的方向按指定位数函数：朝着靠近零值的方向按指定位数向下舍入数字向下舍入数字–CEILING函数：沿着绝对值增大的方向向上舍入为最函数：沿着绝对值增大的方向向上舍入为最接近的指定数字的倍数接近的指定数字的倍数–FLOOR函数：沿着绝对值减小的方向向下舍入为最接函数：沿着绝对值减小的方向向下舍入为最接近的指定数字的倍数近的指定数字的倍数 案例案例11 实发工资尾数处理与快速制作工资条实发工资尾数处理与快速制作工资条•快速制作工资条快速制作工资条案例案例12 制作费用报销单制作费用报销单•设置数据的有效性设置数据的有效性•NUMBERSTRING函数：将数字按指定的函数：将数字按指定的格式转换成大写格式转换成大写•删除网格线删除网格线•隐藏部分行和列隐藏部分行和列•保护部分单元格保护部分单元格•保护工作表保护工作表案例案例13 费用数据自动归类费用数据自动归类•设置数据有效性设置数据有效性•运用条件格式运用条件格式•自动设置边框线自动设置边框线•正确设置引用单元格的方式正确设置引用单元格的方式–费用按部门归类费用按部门归类–费用按性质归类费用按性质归类案例案例14 多种产品材料成本汇总表多种产品材料成本汇总表•IF函数；函数；VLOOKUP函数；函数；SUMIF函数函数•ISERROR函数：判断所引用的参数是否为函数：判断所引用的参数是否为错误值错误值 •ROW函数：返回所引用的单元格的行号函数：返回所引用的单元格的行号•INDIRECT函数：返回由文本字符串指定的函数：返回由文本字符串指定的引用引用 案例案例15 制作多期营业费用自动汇总表制作多期营业费用自动汇总表•新增月份工作表中的数据自动列示在汇总新增月份工作表中的数据自动列示在汇总表中表中–IF函数函数–ISERROR函数函数–INDIRECT函数函数–ROW函数函数案例案例16 制作管理费用预算制作管理费用预算执行情况追踪分析图执行情况追踪分析图•新增月份的数据自动显示在图中新增月份的数据自动显示在图中3 Excel在资产管控中的在资产管控中的7个应用个应用•案例案例17 普通日记账规范整理与制作现金日记账普通日记账规范整理与制作现金日记账•案例案例18 应收账款余额计算及信用政策自动调整应收账款余额计算及信用政策自动调整•案例案例19 制作应收账款账龄分析表制作应收账款账龄分析表•案例案例20 快速编制暂付款期末余额明细表快速编制暂付款期末余额明细表•案例案例21 编制存货的收发存汇总表并设置库存量预警编制存货的收发存汇总表并设置库存量预警•案例案例22 存货信息动态查询存货信息动态查询•案例案例23 固定资产折旧计算与台账管理固定资产折旧计算与台账管理案例案例17 普通日记账规范整理与制作现金日记账普通日记账规范整理与制作现金日记账•整理日期数据整理日期数据•整理凭单号数据整理凭单号数据•快速填充缺失的数据快速填充缺失的数据•筛选现金日记账筛选现金日记账•计算现金余额计算现金余额案例案例18 应收账款余额计算及信用政策自动调整应收账款余额计算及信用政策自动调整•应收账款期末余额的计算应收账款期末余额的计算–为单元格区域定义名称为单元格区域定义名称–运用条件求和函数运用条件求和函数•信用政策自动调整信用政策自动调整–IF函数函数–OR函数：进行函数：进行“逻辑或逻辑或”关系的判断关系的判断 案例案例19 制作应收账款账龄分析表制作应收账款账龄分析表•为单元格区域指定名称为单元格区域指定名称•HLOOKUP函数：在表格或数值数组的首函数：在表格或数值数组的首行查找指定的数值，并由此返回表格或数行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值组当前列中指定行处的数值•SUMPRODUCT函数：多条件求和函数：多条件求和 案例案例20 快速编制暂付款期末余额明细表快速编制暂付款期末余额明细表•自动筛选自动筛选•COUNTIF函数：计算区域中满足给定条件函数：计算区域中满足给定条件的单元格的个数的单元格的个数 案例案例21 编制存货的收发存汇总表编制存货的收发存汇总表并设置库存量预警并设置库存量预警•设置数据有效性设置数据有效性•IF函数函数•ISERROR函数函数•VLOOKUP函数函数•设置条件格式设置条件格式 案例案例22 存货信息动态查询存货信息动态查询•定义名称定义名称•VLOOKUP函数函数•COLUMN函数：返回引用的参数所在列的序号函数：返回引用的参数所在列的序号•INDEX函数函数•MATCH函数：返回在指定方式下与指定数值匹配函数：返回在指定方式下与指定数值匹配的数组中元素的相应位置的数组中元素的相应位置•创建列表创建列表 案例案例23 固定资产折旧计算与台账管理固定资产折旧计算与台账管理 •计算固定资产折旧计算固定资产折旧–SLN函数：按直线法计算某项资产在一个期间函数：按直线法计算某项资产在一个期间中的折旧值中的折旧值–SYD函数：返回某项资产按年限总和法计算的函数：返回某项资产按年限总和法计算的指定期间的折旧值指定期间的折旧值–DDB函数：使用双倍余额递减法或其他指定方函数：使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值法，计算一笔资产在给定期间内的折旧值 案例案例23 固定资产折旧计算与台账管理固定资产折旧计算与台账管理 •登记固定资产台账登记固定资产台账–使用使用“记录单记录单”功能录入和编辑数据功能录入和编辑数据–DATEDIF函数：计算两个日期之间间隔的年、函数：计算两个日期之间间隔的年、月或天数月或天数•制作固定资产数据汇总表制作固定资产数据汇总表4 Excel在负债管控中的在负债管控中的3个应用个应用•案例案例24 应付账款到期日提前提醒设置应付账款到期日提前提醒设置•案例案例25 编制付款合同执行进度汇总表编制付款合同执行进度汇总表•案例案例26 编制分期还款计划表编制分期还款计划表案例案例24 应付账款到期日提前提醒设置应付账款到期日提前提醒设置•计算应付账款到期日计算应付账款到期日•设置条件格式设置条件格式案例案例25 编制付款合同执行进度汇总表编制付款合同执行进度汇总表•定义名称定义名称•设置数据有效性设置数据有效性•创建列表创建列表•运用条件求和函数运用条件求和函数案例案例26 编制分期还款计划表编制分期还款计划表•PMT函数：基于固定利率及等额分期付款方式，函数：基于固定利率及等额分期付款方式，返回贷款的每期偿还额返回贷款的每期偿还额•IPMT函数：基于固定利率及等额分期付款方式，函数：基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额返回给定期数内对投资的利息偿还额•PPMT函数：基于固定利率及等额分期付款方式，函数：基于固定利率及等额分期付款方式，返回一项投资在某一给定期间内的本金偿还额返回一项投资在某一给定期间内的本金偿还额5 Excel在财务报表分析中的在财务报表分析中的6个应用个应用•案例案例27 编制比较财务报表和共同比报表编制比较财务报表和共同比报表•案例案例28 绘制资产结构分析图绘制资产结构分析图•案例案例29 绘制收入和利润趋势分析图绘制收入和利润趋势分析图 与净利润因素分析图与净利润因素分析图•案例案例30 绘制两期财务指标对比分析图绘制两期财务指标对比分析图•案例案例31 计算财务比率及绘制杜邦系统图计算财务比率及绘制杜邦系统图•案例案例32 制作财务指标雷达图及报表分析模型的封面制作财务指标雷达图及报表分析模型的封面 案例案例27 编制比较财务报表和共同比报表编制比较财务报表和共同比报表•缩位显示数字自定义格式代码：缩位显示数字自定义格式代码：–缩小小1百位百位 0"."00–缩小缩小1千位显示数字：千位显示数字： 0.00，，–缩小缩小1万位显示数字：万位显示数字： 0!.0,–缩小缩小10万位显示数字：万位显示数字： 0!.00,–缩小缩小100万位显示数字：万位显示数字： 0.00,,–缩小缩小1000万位显示数字：万位显示数字： 0!.0,,–缩小缩小1亿位显示数字：亿位显示数字： 0!.00,,–缩小缩小10亿位显示数字：亿位显示数字： 0.00,,,•通过组合方式设置分级显示通过组合方式设置分级显示 案例案例28 绘制资产结构分析图绘制资产结构分析图案例案例29 绘制收入和利润趋势分析图绘制收入和利润趋势分析图与净利润因素分析图与净利润因素分析图案例案例29 绘制收入和利润趋势分析图绘制收入和利润趋势分析图与净利润因素分析图与净利润因素分析图案例案例30 绘制两期财务指标对比分析图绘制两期财务指标对比分析图案例案例31 计算财务比率及绘制杜邦系统图计算财务比率及绘制杜邦系统图•计算财务比率计算财务比率–偿债能力比率偿债能力比率–获利能力比率获利能力比率–营运能力比率营运能力比率–成长性比率成长性比率案例案例31 计算财务比率及绘制杜邦系统图计算财务比率及绘制杜邦系统图•绘制杜邦系统图绘制杜邦系统图–录制宏录制宏–查看宏代码查看宏代码–运行宏运行宏–使用命令按钮运行宏使用命令按钮运行宏案例案例32 制作财务指标雷达图及报表制作财务指标雷达图及报表分析模型的封面分析模型的封面•制作财务指标雷达图制作财务指标雷达图案例案例32 制作财务指标雷达图及报表制作财务指标雷达图及报表分析模型的封面分析模型的封面•制作报表分析模型的封面制作报表分析模型的封面–插入艺术字插入艺术字–插入超链接插入超链接6 Excel在量本利分析中的在量本利分析中的4个应用个应用•案例案例33 分解混合成本与计算保本点分解混合成本与计算保本点•案例案例34 绘制动态盈亏分析图绘制动态盈亏分析图•案例案例35 实现目标利润的单一措施分析实现目标利润的单一措施分析•案例案例36 利润多因素敏感性分析利润多因素敏感性分析 案例案例33 分解混合成本与计算保本点分解混合成本与计算保本点•分解混合成本分解混合成本–SLOPE函数：计算回归直线的斜率函数：计算回归直线的斜率 –INTERCEPTE函数：计算回归直线的截距函数：计算回归直线的截距–绘制散点图绘制散点图•计算保本点计算保本点–利用计算公式利用计算公式–运用单变量求解工具运用单变量求解工具案例案例34 绘制动态盈亏分析图绘制动态盈亏分析图•设置滚动条控件设置滚动条控件•动态显示标签值动态显示标签值案例案例35 实现目标利润的单一措施分析实现目标利润的单一措施分析•目标利润的影响因素目标利润的影响因素–销售量销售量–单价单价–单位变动成本单位变动成本–固定成本固定成本•运用单变量求解工具运用单变量求解工具案例案例36 利润多因素敏感性分析利润多因素敏感性分析•目标利润的影响因素目标利润的影响因素–销售量销售量–单价单价–单位变动成本单位变动成本–固定成本固定成本•设置滚动条控件设置滚动条控件结束结束 。