excel在财务管控中的运用实用教案.ppt

目录(mùlù)1￿￿Excel在销售收入管控(ɡuǎn￿kònɡ)中的8个应用2￿￿Excel在成本费用管控(ɡuǎn￿kònɡ)中的8个应用3￿￿Excel在资产管控(ɡuǎn￿kònɡ)中的7个应用4￿￿Excel在负债管控(ɡuǎn￿kònɡ)中的3个应用5￿￿Excel在财务报表分析中的6个应用6￿￿Excel在量本利分析中的4个应用￿￿第1页/共52页第一页，共53页1￿￿Excel在销售收入管控(ɡuǎn￿kònɡ)中的8个应用•案例1￿订单数据整理与筛选分析•案例2￿销售数据整理与分类汇总分析•案例3￿销售数据的分级显示•案例4￿使用函数(hánshù)制作销售月报和周报•案例5￿销售数据整理与多维度透视分析•案例6￿各分公司多重区域销售数据汇总分析•案例7￿制作销售预算完成情况进度图•案例8￿制作动态销售图表￿第2页/共52页第二页，共53页案例1￿订单数据整理(zhěnglǐ)与筛选分析•数据分列•设置分隔符号；设置列数据格式•自动筛选(shāixuǎn)•筛选(shāixuǎn)某个项目；空白/非空白；自定义筛选(shāixuǎn)•高级筛选(shāixuǎn)•一个条件•多个条件——逻辑与；逻辑或•筛选(shāixuǎn)出不含重复记录的数据表第3页/共52页第三页，共53页。

案例(àn￿lì)2￿销售数据整理与分类汇总分析•将文本型数据转换为纯数字•利用智能标记•利用选择性粘贴工具￿•单级分类汇总•按分类字段排序；建立分类汇总表•复制与删除分类汇总结果•定位可见单元格•多级分类汇总•求和；计数；求平均值；最大值；最小值等方式(fāngshì)￿•嵌套分类汇总•由外向内进行第4页/共52页第四页，共53页案例3￿销售(xiāoshòu)数据的分级显示•定位空单元格•快速填充小计数( jì￿shù)•自动建立分级显示•分级显示表的使用方法•清除分级显示第5页/共52页第五页，共53页案例(àn￿lì)4￿使用函数制作销售月报和周报•为单元格或单元格区域定义名称的方法•MONTH函数：返回一个日期型数据中的月份数字•WEEKNUM函数：返回一个日期型数据中的周次数字•SUMIF函数：根据指定(zhǐdìng)条件对若干单元格求和•SUMPRODUCT函数：将给定的几个数组中的对应元素相乘并返回乘积之和•建立与复制公式•引用单元格的方式•创建列表的方法￿第6页/共52页第六页，共53页案例(àn￿lì)5￿销售数据整理与多维度透视分析•销售数据整理•LEFT函数：从文本字符串左边提取指定数目的字符•MID函数：从文本字符串的指定位置开始提取特定数目的字符•RIGHT函数：从文本字符串右边提取若干个字符•DATE函数：返回代表(dàibiǎo)特定日期的序列号￿￿第7页/共52页第七页，共53页。

案例(àn￿lì)5￿销售数据整理与多维度透视分析•制作与编辑数据透视表•合理设置数据透视表的布局——页字段；行字段；列字段；数据字段•修改字段名称•设置字段的数字格式•取消数据透视表行总计(zǒngjì)或列总计(zǒngjì)•取消字段的分类汇总•合并数据标志•设置错误值或空值的显示方式•更新数据透视表的数据第8页/共52页第八页，共53页案例5￿销售数据(shùjù)整理与多维度透视分析•使用数据透视表多维度分析数据•修改数据透视表布局•显示明细数据•通过(tōngguò)组合字段创建多维数据汇总表•按日期、地区、商品类别分组•通过(tōngguò)设置字段显示方式创建各种比较分析表•数据的显示方式——差异；差异百分比；占同行或同列百分比；占总合百分比；累计值显示……•创建各种比较分析表第9页/共52页第九页，共53页案例5￿销售数据整理(zhěnglǐ)与多维度透视分析•制作(zhìzuò)与使用数据透视图•在数据清单基础上制作(zhìzuò)数据透视图•在数据透视表基础上制作(zhìzuò)数据透视图第10页/共52页第十页，共53页案例6￿各分公司多重区域(qūyù)销售数据汇总分析•利用合并计算工具(gōngjù)制作汇总表•设置引用区域•创建连至源数据的链接•利用数据透视表汇总多重区域的分公司销售数据•设置引用区域•编辑与美化汇总表第11页/共52页第十一页，共53页。

案例(àn￿lì)7￿制作销售预算完成情况进度图•制作条形图•编辑与美化(měihuà)图表第12页/共52页第十二页，共53页案例8￿制作(zhìzuò)动态销售图表•INDEX函数：返回列表(liè￿biǎo)或数组中的元素值•设置组合框控件￿第13页/共52页第十三页，共53页2￿￿Excel在成本费用管控(ɡuǎn￿kònɡ)中的8个应用•案例9￿整理费用报销数据与制作费用汇总表•案例10￿工资(gōngzī)与个人所得税计算•案例11￿实发工资(gōngzī)尾数处理与快速制作工资(gōngzī)条•案例12￿制作费用报销单•案例13￿费用数据自动归类•案例14￿多种产品材料成本汇总表•案例15￿制作多期营业费用自动汇总表•案例16￿制作管理费用预算执行情况追踪分析图￿第14页/共52页第十四页，共53页案例9￿整理费用(fèi￿yong)报销数据与制作费用(fèi￿yong)汇总表•整理费用(fèi￿yong)报销数据•利用分列工具整理日期数据•使用函数提取经手人信息•使用查找替换功能转换文本数字•FIND函数：返回一个字符串在另一个字符串中的起始位置•费用(fèi￿yong)报销数据透视分析第15页/共52页第十五页，共53页。

案例(àn￿lì)10￿工资与个人所得税计算•VLOOKUP函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值•TODAY函数：返回系统当前的日期•INT函数：无条件舍去小数，只保留整数•IF函数：执行真假值判断，根据逻辑计算(jì￿suàn)的真假值，返回不同结果￿￿第16页/共52页第十六页，共53页案例(àn￿lì)11￿实发工资尾数处理与快速制作工资条•实发工资尾数处理(chǔlǐ)•ROUND函数：返回按指定位数对数值进行四舍五入处理(chǔlǐ)后的结果•ROUNDUP函数：朝着远离零值的方向按指定位数向上舍入数字•ROUNDDOWN函数：朝着靠近零值的方向按指定位数向下舍入数字•CEILING函数：沿着绝对值增大的方向向上舍入为最接近的指定数字的倍数•FLOOR函数：沿着绝对值减小的方向向下舍入为最接近的指定数字的倍数￿第17页/共52页第十七页，共53页案例11￿实发工资尾数(wěishù)处理与快速制作工资条•快速(kuài￿sù)制作工资条第18页/共52页第十八页，共53页案例(àn￿lì)12￿制作费用报销单•设置数据的有效性•NUMBERSTRING函数：将数字按指定的格式转换成大写•删除(shānchú)网格线•隐藏部分行和列•保护部分单元格•保护工作表第19页/共52页第十九页，共53页。

案例13￿费用数据自动(zìdòng)归类•设置数据有效性•运用条件格式•自动设置边框线•正确设置引用单元格的方式•费用按部门(bùmén)归类•费用按性质归类第20页/共52页第二十页，共53页案例(àn￿lì)14￿多种产品材料成本汇总表•IF函数；VLOOKUP函数；SUMIF函数•ISERROR函数：判断所引用的参数是否为错误值￿•ROW函数：返回(fǎnhuí)所引用的单元格的行号•INDIRECT函数：返回(fǎnhuí)由文本字符串指定的引用￿第21页/共52页第二十一页，共53页案例(àn￿lì)15￿制作多期营业费用自动汇总表•新增月份工作表中的数据(shùjù)自动列示在汇总表中•IF函数•ISERROR函数•INDIRECT函数•ROW函数第22页/共52页第二十二页，共53页案例16￿制作管理费用预算执行情况追踪(zhuīzōng)分析图•新增月份的数据(shùjù)自动显示在图中第23页/共52页第二十三页，共53页3￿￿Excel在资产(zīchǎn)管控中的7个应用•案例17￿普通日记账规范整理与制作现金日记账•案例18￿应收账款余额计算及信用政策自动调整•案例19￿制作应收账款账龄分析表•案例20￿快速编制暂付款期末余额明细表•案例21￿编制存货的收发存汇总表并设置库存量预警•案例22￿存货信息(xìnxī)动态查询•案例23￿固定资产折旧计算与台账管理第24页/共52页第二十四页，共53页。

案例(àn￿lì)17￿普通日记账规范整理与制作现金日记账•整理日期数据•整理凭单号数据•快速填充缺失(quē￿shī)的数据•筛选现金日记账•计算现金余额第25页/共52页第二十五页，共53页案例18￿应收账款余额计算及信用(xìnyòng)政策自动调整•应收账款期末余额的计算•为单元格区域定义名称•运用(yùnyòng)条件求和函数•信用政策自动调整•IF函数•OR函数：进行“逻辑或”关系的判断￿第26页/共52页第二十六页，共53页案例19￿制作应收账款(zhànɡ￿kuǎn)账龄分析表•为单元格区域(qūyù)指定名称•HLOOKUP函数：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值•SUMPRODUCT函数：多条件求和￿第27页/共52页第二十七页，共53页案例20￿快速编制(biānzhì)暂付款期末余额明细表•自动筛选•COUNTIF函数：计算区域(qūyù)中满足给定条件的单元格的个数￿第28页/共52页第二十八页，共53页案例(àn￿lì)21￿编制存货的收发存汇总表并设置库存量预警•设置(shèzhì)数据有效性•IF函数•ISERROR函数•VLOOKUP函数•设置(shèzhì)条件格式￿第29页/共52页第二十九页，共53页。

案例(àn￿lì)22￿存货信息动态查询•定义名称•VLOOKUP函数•COLUMN函数：返回引用的参数(cānshù)所在列的序号•INDEX函数•MATCH函数：返回在指定方式下与指定数值匹配的数组中元素的相应位置•创建列表￿第30页/共52页第三十页，共53页案例(àn￿lì)23￿固定资产折旧计算与台账管理￿•计算固定资产折旧•SLN函数：按直线法计算某项资产在一个期间中的折旧值•SYD函数：返回某项资产按年限总和法计算的指定期间的折旧值•DDB函数：使用双倍余额(yú￿é)递减法或其他指定方法，计算一笔资产在给定期间内的折旧值￿第31页/共52页第三十一页，共53页案例23￿固定资产(gùdìngzīchǎn)折旧计算与台账管理￿•登记固定资产台账•使用(shǐyòng)“记录单”功能录入和编辑数据•DATEDIF函数：计算两个日期之间间隔的年、月或天数•制作固定资产数据汇总表第32页/共52页第三十二页，共53页4￿￿Excel在负债(fù￿zhài)管控中的3个应用•案例24￿应付账款到期日提前(tíqián)提醒设置•案例25￿编制付款合同执行进度汇总表•案例26￿编制分期还款计划表第33页/共52页第三十三页，共53页。

案例(àn￿lì)24￿应付账款到期日提前提醒设置•计算应付账款到期日•设置条件(tiáojiàn)格式第34页/共52页第三十四页，共53页案例25￿编制付款(fù￿kuǎn)合同执行进度汇总表•定义名称•设置数据(shùjù)有效性•创建列表•运用条件求和函数第35页/共52页第三十五页，共53页案例26￿编制分期(fēn￿qī)还款计划表•PMT函数：基于固定利率及等额分期付款方式，返回贷款的每期偿还额•IPMT函数：基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额•PPMT函数：基于固定利率及等额分期付款方式，返回一项投资在某一给定期间(qījiān)内的本金偿还额第36页/共52页第三十六页，共53页5￿￿Excel在财务报表分析(fēnxī)中的6个应用•案例27￿编制比较财务报表和共同比报表•案例28￿绘制资产结构分析图•案例29￿绘制收入和利润趋势分析图•￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿与净利润因素分析图•案例30￿绘制两期财务指标对比分析图•案例31￿计算财务比率(bǐlǜ)及绘制杜邦系统图•案例32￿制作财务指标雷达图及报表分析模型的封面￿第37页/共52页第三十七页，共53页。

案例27￿编制比较(bǐjiào)财务报表和共同比报表•缩位显示数字自定义格式代码：•缩小1百位￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿0"."00•缩小1千位显示数字：￿￿￿￿￿￿￿，•缩小1万位显示数字：￿￿￿￿￿￿￿0!.0,•缩小10万位显示数字：￿￿￿￿￿0!.00,•缩小100万位显示数字：￿￿￿0.00,,•缩小1000万位显示数字：￿0!.0,,•缩小1亿位显示数字：￿￿￿￿￿￿￿0!.00,,•缩小10亿位显示数字：￿￿￿￿￿0.00,,,•通过(tōngguò)组合方式设置分级显示￿第38页/共52页第三十八页，共53页案例28￿绘制资产结构(jiégòu)分析图第39页/共52页第三十九页，共53页案例29￿绘制收入和利润(lìrùn)趋势分析图与净利润(lìrùn)因素分析图第40页/共52页第四十页，共53页案例29￿绘制收入和利润趋势分析(fēnxī)图与净利润因素分析(fēnxī)图第41页/共52页第四十一页，共53页案例30￿绘制两期财务指标对比(duìbǐ)分析图第42页/共52页第四十二页，共53页案例31￿计算(jì￿suàn)财务比率及绘制杜邦系统图•计算财务比率(bǐlǜ)•偿债能力比率(bǐlǜ)•获利能力比率(bǐlǜ)•营运能力比率(bǐlǜ)•成长性比率(bǐlǜ)第43页/共52页第四十三页，共53页。

案例(àn￿lì)31￿计算财务比率及绘制杜邦系统图•绘制杜邦系统图•录制宏•查看宏代码•运行(yùnxíng)宏•使用命令按钮运行(yùnxíng)宏第44页/共52页第四十四页，共53页案例32￿制作(zhìzuò)财务指标雷达图及报表分析模型的封面•制作财务指标(cái￿wù￿zhǐ￿biāo)雷达图第45页/共52页第四十五页，共53页案例(àn￿lì)32￿制作财务指标雷达图及报表分析模型的封面•制作(zhìzuò)报表分析模型的封面•插入艺术字•插入超链接第46页/共52页第四十六页，共53页6￿￿Excel在量本利(běn￿lì)分析中的4个应用•案例33￿分解混合成本与计算保本点•案例34￿绘制动态盈亏分析(fēnxī)图•案例35￿实现目标利润的单一措施分析(fēnxī)•案例36￿利润多因素敏感性分析(fēnxī)￿第47页/共52页第四十七页，共53页案例33￿分解混合成本(chéngběn)与计算保本点•分解混合成本•SLOPE函数：计算回归直线的斜率￿•INTERCEPTE函数：计算回归直线的截距•绘制散点图•计算保本点•利用计算公式•运用单变量求解(qiú￿jiě)工具第48页/共52页第四十八页，共53页。

案例34￿绘制(huìzhì)动态盈亏分析图•设置(shèzhì)滚动条控件•动态显示标签值第49页/共52页第四十九页，共53页案例35￿实现目标利润的单一(dānyī)措施分析•目标利润的影响因素•销售量•单价•单位变动成本(chéngběn)•固定成本(chéngběn)•运用单变量求解工具第50页/共52页第五十页，共53页案例(àn￿lì)36￿利润多因素敏感性分析•目标(mùbiāo)利润的影响因素•销售量•单价•单位变动成本•固定成本•设置滚动条控件第51页/共52页第五十一页，共53页感谢您的观看(guānkàn)！第52页/共52页第五十二页，共53页内容(nèiróng)总结目录6 Excel在量本利分析中的4个应用6 Excel在量本利分析中的4个应用多个条件——逻辑与合理设置数据透视(tòushì)表的布局——页字段按日期、地区、商品类别分组数据的显示方式——差异案例9 整理费用报销数据与制作费用汇总表FIND函数：返回一个字符串在另一个字符串中的起始位置案例17 普通日记账规范整理与制作现金日记账00,,案例29 绘制收入和利润趋势分析图与净利润因素分析图。

感谢您的观看第五十三页，共53页。