Excel财务应用XXXX讲义.pptx
Excel高效财务数据分析及财务管理应用袁志刚2015 目录1.销售管理与分析2.进销存管理3.财务模型分析4.薪酬计算与分析5.费用分析6.预算管理7.融资分析8.往来分析9.报表设计10. 链接数据库袁志刚21.1 销售周报分析 计算周次函数 Weeknum =weeknum(日期，2） 2表示一周从星期一开始 根据周次统计收入 Sumifs函数 多条件求和函数，与之同类的还有countifs,averageifs 除了sumifs，sumproduct函数也可用于多条件求和 =sumifs(求和区域，条件区域1，条件1，条件区域2，条件2,) 制作折线图 趋势分析袁志刚31.2 客户分布分析 Frequency函数 自定义统计区间 设定参照值，取区间最小值作为参照值 选中与统计区间数量相同的连续单元格 输入frequency函数=frequency(数据区域，参照值区域） 同时按下ctrl+shift+enter键 最后按下的组合键将创建一个数组公式 不可以单独删除数组公式中的任一公式，需要全选后才可删除 构成比例分析 绘制饼图 设置数据标签袁志刚41.3 客户数变动分析 生成透视表 选择日期放入行区域 右键-创建组，选择年 在透视表外输入公式计算客户增长的比例 制作图表 光标放在透视表内，点击数据透视表工具-选项-数据透视图 生成透视图 光标放在客户增长率表内，选择“插入”- 折线图 客户增量分析 创建透视表，将日期放入行区域，将销量字段放入数值区域 右键-创建组，选择年和月 光标放在销量列，邮件- 值汇总依据-选择计数 数据透视表工具-选项-数据透视图，生成折线图袁志刚51.4 基于地图的数据分析作用 形象展示基于地理位置的区域数据分析。 容易理解，印象深刻，效果出众。步骤 使用地图矢量图，为每一个区域定义名称，一般是该区域名称的拼音。选中区域，在名称框内输入即可。 输入宏，开发工具-visual basic,双击thisworkbook,在右侧代码窗口输入以下代码：Sub user_click(region_name)ActiveSheet.Shapes(Range(m1).Value).Fill.ForeColor.SchemeColor = xlThemeColorDark1Range(m1).Value = region_nameActiveSheet.Shapes(region_name).Fill.ForeColor.SchemeColor = xlThemeColorAccent6End Sub袁志刚61.4 基于地图的数据分析 步骤 为每个区域图形指定宏，选中该区域图形，右键-指定宏，输入以下宏名称：thisworkbook.user_click(“区域名称”)其中，区域名称为为区域图形定义的名称 在m1单元格中输入一个区域的名称代码，比如北京地区的代码为beijing,此时即可点击区域实现变色效果。 准备好原始数据 写一个vlookup函数对地图上选中的区域数据进行查询，该查询的索引字段为m1单元格，该单元格可以任意指定。 根据查询结果制作图表袁志刚71.5 基于地图的色阶分析 步骤 准备地图矢量文件 为每一个区域图形定义名称，选中图形，在名称框内输入名称，名称设为该区域的拼音 为数据设置区间，假设分为以下5个区间： 为上面5种色彩所在单元格定义名称，分别设为code1-5 为这5个区间设置参照值和颜色代码：袁志刚81.5 基于地图的色阶分析步骤 为下图中的3个单元格定义名称，分别为province,vbdata,vbcode,其中数据和颜色代码为vlookup查询公式，根据省份在数据表中查询数据和所属颜色代码 制作控件按钮，开发工具-控件-插入-按钮-指定宏-新建，输入以下宏代码：Sub 按钮35_单击()For i = 3 To 34Range(province).Value = Range(data!a & i).Value ActiveSheet.Shapes(Range(province).Value).Select Selection.ShapeRange.Fill.ForeColor.RGB = Range(Range(vbcode).Value).Interior.ColorNext iRange(f8).SelectEnd Sub袁志刚91.6 复合增长率 概念 CAGR（Compound Annual Growth Rate） 一个指标（比如销售收入或净利润，投资回报等）在特定时期内的年度增长率 是较长时期内的测算，忽略个别年度的波动，将增长率平滑，反映指标的整体表现。 公式 =（当前数值/基期数值）(1/年数）-1袁志刚101.7 波士顿矩阵分析简介 波士顿矩阵（BCG Matrix），又称市场增长率-相对市场份额矩阵，是由美国著名的管理学家、波士顿咨询公司创始人布鲁斯亨德森于20世纪60年代末期首创的。 布鲁斯认为决定产品/业务结构的要素可分为2类，市场吸引力与企业实力。在反映市场引力的众多指标（销售增长率、目标市场容量、竞争对手强弱及利润高低）中，销售增长率是最具代表性的综合指标；而在反映企业实力的指标，如市场占有率，技术、设备、资金利用能力中，市场占有率是最能直接显示出企业竞争实力的指标。因此，波士顿矩阵选取的纵坐标与横坐标分别是“销售增长率”及“市场占有率”。 由以上2个因素相互作用，产生4个不同的象限，划分出4类性质的产品/业务：销售增长率和市场占有率都较高的产品/业务（简称“明星”）；销售增长率和市场占有率都较低的产品/业务（简称“瘦狗”）；销售增长率高而市场占有率低的产品/业务（简称“问号”）；销售增长率低而市场占有率高的产品/业务（简“现金牛”）。袁志刚11问题型业务现金型业务瘦狗型业务明星型业务高高低低市场增长市场份额1.7 波士顿矩阵分析1.7 波士顿矩阵分析 利用散点图制作波士顿矩阵 选中表格的数据列，制作散点图，行坐标为市场占有率，纵坐标为增长率 选中行坐标轴，设置坐标轴格式，勾选逆序刻度值，将纵坐标交叉选项改为坐标轴值：0.5，将刻度线类型与坐标轴标签设为无 选中纵坐标轴，除了不勾选逆序刻度值，其他设置与行坐标轴一样 Excel散点图的一个问题是无法为数据添加标签，可以使用一个第三方程序解决：xy chart labels 安装此程序后excel会出现一个新的选项卡xy chart labels,选择其中的add labels命令 在其中的select a label range选项中，选择数据表中的表前列即可 最后可为坐标轴添加标签或为4个象限添加图片说明。袁志刚132.1 存货ABC管理 概念 又称巴雷特分析法，按照价值和库存数量的高低，依据一定的分类标准，确定关键的少数和次要的多数。其分清主次，抓住重点的思想广泛应用于存货管理与成本管理。 A类物资是指品种少、实物量少而价值高的物资，其成本金额约占70%，而实物量不超过20%。 C类物资是指品种多、实物量多而价值低的物资，其成本金额约占10，而实物量不低于50。 B类物资介于A类、C类物资之间。其成本金额约占20，而实物量不超过30。 管理表制作步骤 计算存货金额及数量所占比例，并按金额降序排列 计算存货金额和数量所占累计比例袁志刚142.1 存货ABC管理 管理表制作步骤 依据一定标准，进行ABC分类。一般将关键的少数作为A类，次要的多数作为B和C类，对A类存货进行重点管理。 制作帕累托图 以意大利经济学家pareto命名 选择存货名称，金额，累计金额百分比3列制作柱形图 选择图表工具-布局，左上角点开图表元素选择框，在其中选择累计金额百分比 点击设置所选内容格式按钮，将“系列绘制在”选项由“主坐标轴”改为“次坐标轴” 将累计金额百分比数据系列的图表类型改为折线图 选中副坐标轴，将最大值改为1 选中金额数据系列，右键-设置数据系列格式，将分类间距改为0%袁志刚152.2 进销存表设计 “表”功能 表格与数据区域 表内的数据可以独立和更方便的进行管理，比如自动扩展数据与公式，添加汇总行，筛选，应用表格格式等。 创建与编辑 将光标放在表中，选择“插入”- 表格 通过顶部的表格工具，可以对表格进行各种编辑 为表格更换样式：表格工具-表格样式，点击即应用新的样式 将表格转化为区域：表格工具-转化为区域 在表格最后一行下面输入数据，即可自动扩展表格范围袁志刚162.3 进销存管理 数据表设计 进出字段 用if函数控制显示与否=if(b2=”,”,c2) Vlookup查询产品价格=vlookup(产品名称，基础信息表，价格所在的列，查询方式） 统计库存 创建透视表，行设置为产品，列设置为“进出”，数值为数量 数据透视表工具 - 域、项目和集 - 计算项 输入名称“库存”，公式设置为：=进货 - 发货 标识低于安全库存的存货：开始-条件格式-新建规则-突出显示单元格规则袁志刚173.1 杠杆平衡模型分析 经营杠杆 =边际贡献/（边际贡献-固定成本） 假设经营杠杆系数为2，则表明销量增长10%，息税前利润增长（210%）20%，即高度的经营杠杆，表示销货量的小幅变动，会引起利润的较大变动。 一家公司的固定成本愈高，其企业风险也愈高。故有大量固定成本的公司，即具有较高的营业杠杆。 企业一般可通过增加销售额，降低单位变动成本和固定成本等措施来降低经营杠杆和经营风险。 财务杠杆 是指由于债务的存在而导致每股利润的变动大于息税前利润变动的杠杆效应。 财务杠杆系数=息税前利润/(息税前利润-资本总额*负债比例*利率）袁志刚183.1 杠杆平衡模型分析 复合杠杆 复合杠杆是指由于固定成本和固定财务费用的存在而导致的普通股每股利润变动率大于产销量变动率的杠杆效应。 复合杠杆系数=经营杠杆系数*财务杠杆系数 杠杆平衡模型 当其他因素变动时，测算销量的变动 文件-选项-自定义功能区，在主选项卡勾选“开发工具” 开发工具-控件-插入，选择“数值调节钮”，为变量添加调节钮控件 右键选择数值调节钮，设置控件格式，设置单元格链接，并使用链接公式使得该单元格链接可以控制变量值袁志刚193.2 盈亏平衡分析 分析内容 销量平衡分析 单价平衡分析 变动成本平衡分析 固定成本平衡分析 控件应用 开发工具-控件-插入-滚动条 右键-单元格链接 输入变量计算公式：=当前值*(（1+单元格链接/50-1)/2)，此公式使得变量以每次1%的幅度变化袁志刚204.1 加班计算 返回星期几 =Weekday(日期，2) 判断加班类型 =IF(ISERROR(VLOOKUP(B4,$J$2:$J$11,1,FALSE),IF(OR(E4=6,E4=7),周末加班,工作日加班),节假日加班) 提取不重复姓名 =INDEX(A$2:A$32,MATCH(0,COUNTIF(M$1:M1,A$2:A$32),0) 统计加班合计数 =SUMPRODUCT($A$2:$A$32=M2)*($H$2:$H$32)袁志刚214.2 薪酬计算与分析 奖金匹配 hlookup：水平查询 设置奖金比例表，其中的参照值应该取区间的最小值 =hlookup(某销售员奖金，奖金比例表，比例所在列，查询方式）其中查询方式应该设置为1，意为模糊查询。 工资变动分析 将工资与销售收入数据转化为指数，即基期为100，其他期间为：当期数据/基期数据*100 制作折线图 计算工资与销售收入相关性：=correl(工资，销售收入） 计算工资与销售收入各自的复合增长率=（当期/基期）(1/年数）-1袁志刚225.1 折旧费用计算 已经计提折旧月份计算 =datedif(启用资产日期，today()，”m”) 其中m表示月份，还可以是y、d，分别表示年，天。 折旧额计算 直线法=sln(原值，残值，使用年限） 双倍余额递减法=ddb(原值，残值，使用年限，第几期） 年数总和法=syd(原值，残值，使用年限，第几期）袁志刚235.2 混合成本分解 制作散点图 添加趋势线 右键-添加趋势线，选择线性 勾选“显示公式”和“显示R平方值”选项 公式Y=7.85x-6，其中7.85为变动成本，6为固定成本 函数方式 也可使用函数计算变动成本与固定成本 变动成本=slope(成本，产量） 固定成本=intercept(成本，产量）袁志刚245.3 动态费用查询 制作控件 开发工具-控件-插入，制作组合框和列表框 右键-设置控件格式，为其设置数据源区域和单元格链接 写公式，将单元格链接里的序号变为具体值=index(参数列表，单元格链接） 写公式查询数据=SUMIFS(OFFSET(数据!$B$3:$B$42,0,参数!$C$1),数据!$B$3:$B$42,参数!$E$2,数据!$A$3:$A$42,统计!E8) （请参照案例） 绘制饼图显示结果袁志刚255.4 拆分科目与余额 分析 没有规律，不在同样的位置，没有统一的分隔符 不能使用分列工具 公式 =MID(A2,LENB(A2)-LEN(A2)+1,LEN(A2) Len函数用于返回字符个数，一个汉字作为一个字符 Lenb中，一个汉字作为2个字符 Mid函数用于分拆文本=mid(需拆分单元格，从第几位拆分，拆分长度）袁志刚265.5 费用与产量相关性分析 相关系数 =correl(费用，产量） 图表方法 绘制折线图 图表工具-布局-图表元素选择框，选择系列“销量” 设置所选内容格式-系列绘制在改为“次坐标轴”袁志刚276.1 利润测算 单变量求解 数据-模拟分析-单变量求解 目标单元格设置为利润所在的单元格 目标值设置为目标利润 可变单元格设置为要求解的变量，比如销量或价格等 点击确定即可看到计算结果袁志刚286.2 添加计算项比较预算与实际数据 多重合并预算表与实际表 合并预算与实际两个表 添加计算项 将预算与实际的上级字段改名为版本 将版本字段放入列标签 将光标放置在版本字段名上，选择透视表工具-选项-公式-计算项 将计算项名称定义为“差异”，公式内容为：=预算-实际，然后点击添加按钮，即可添加差异计算项 去掉合计列 右键-透视表选项-汇总和筛选，取消勾选“显示行总计”袁志刚296.3 预算模板制作 思路 预算制作-跟踪预算-比较分析 模板制作 色彩的运用：区分和强调，引导作用 控件的使用：选择要显示的不同的数据内容 图表：直观的展示 导航：便利的操作 名称的运用：高效的引用袁志刚307.1 加权平均资金成本 加权平均资金成本 WACC(weighted average cost of capital) WACC=（债务/资本）*债务成本*（1-企业所得税税率）+（1-债务/资本）*股权成本 控件应用 开发工具-控件-滚动条 右键-设置控件格式-单元格链接 创建公式关联变量与单元格链接 Sumproduct 用于计算乘积之和，也可以用于计算多条件求和与计数 =sumproduct(区域1，区域2)袁志刚317.2 贷款还款计算 等额分期还款方式 每期偿还金额：=pmt(利率，期数，贷款金额） 每期偿还的利息：=ipmt(利率，第几期，期数，贷款金额） 每期偿还的本金：=ppmt(利率，第几期，期数，贷款金额） 计算利率：=rate(期数，每期还款额，贷款额） 名义利率与实际利率 将名义利率转化为实际利率：=effect(名义利率，每年复利期数） 将实际利率转化为名义利率：=nominal(实际利率，一年内计息次数）袁志刚328.1 银行存款余额调节表 思路 利用vlookup函数在银行对账单和银行存款日记账之间双向查询，出现#N/A即未达账项，然后利用sumproduct函数将未达账项分类求合计。 公式 银行已收/企业未收款：=SUMPRODUCT(ISERROR($D$3:$D$19)=TRUE)*($A$3:$A$190)*($A$3:$A$19) 其中，iserror()=true意味着企业未达，a3:a190意味着是企业未收款 对账单上的其他项目调节公式以此类推。袁志刚338.2 应收账款管理 统计收款金额合计 =sumifs(合同金额列，合同编号列，要统计的合同编号） 分客户的账龄分析 创建透视表，账龄字段放进行区域，金额放进数值区域 光标在行标签区域，右键-创建组，将参数设为1,90,30 将账龄字段拖进列标签 将客户字段拖进行标签 此时可以看到每家客户的应收账款分布汇总情况 还可双击希望查看明细的行，对明细数据进行查看袁志刚348.3 应收款账龄分析 1【插入】选项卡-选择【数据透视表】 2 在“字段列表”工作区中，将过期天数字段拖入行标签，将金额两次拖入数值区域 3 在透视表中的行标签区域内点击鼠标右键，在右键菜单中选择“组合”，将起始于，终止于，步长分别改为：1,120,30 4 光标放在“金额2”的列上右键，选择【值字段设置】，切换为“值显示方式”，并在列表中选择“占同列数据总和的百分比”袁志刚358.4 应付账款提醒 思路 利用条件格式进行应付款提醒 步骤 计算到期天数：=付款日期-today() 选中到期天数列, 开始-条件格式-新建规则-只为包含以下内容的单元格设置格式 设置单元格值0, 为单元格设置红色填充色 设置单元格值10, 为单元格设置蓝色填充色袁志刚369.1 报销单制作为分公司，部门，币种，支付类型等信息设置有效性 在参数表上创建部门列表 数据-数据有效性 设置有效性条件为“序列”，然后在下方的来源框选择参数表上的部门列表 提示：excel2010之前的版本不能跨表选择有效性序列，需要为首先为序列定义名称设置填写提示信息 数据-数据有效性，切换到“输入信息”标签，填写标题和输入信息保护 选中需要填写的单元格，右键-设置单元格格式-保护，取消勾选“锁定” 审阅-保护工作表-输入密码袁志刚379.2 名称定义 名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。 名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车； 需要注意的是：名称框只能用于定义单元格和单元格区域的名称，公式的名称需要在【公式】【名称管理器】中进行定义。 名称的引用：需要引用某单元格时输入为该单元格定义的名称：名称 步骤1：选中需要命名的某个单元格或单元格区域。 步骤2：在左上角名称框输入命名后回车。 删除名称：选择【公式】菜单【名称管理器】；选中需要删除的名称，点击“删除”按钮。袁志刚389.3 报表汇总 快速汇总表 =sum(起始表：结束表！需汇总的单元格） 要求：各个表结构必须一致 利用透视表多重合并计算数据区域汇总 调出多重合并计算数据区域功能快捷键：文件-选项-快速访问工具栏，选择所有命令，在列表中找到“数据透视表和数据透视图向导”，选到右侧 点击快速访问工具栏上的透视表向导按钮，选择“多重合并计算数据区域”选项，选择创建单页字段，逐个选择需合并的表，完成即可。 优点：数据可多可少，可在一个视图上选择查看各个表的数据袁志刚399.4 数字大写转化 转化效果 公式 中文小写公式：=numberstring(b3,1) 中文大写公式：=numberstring(b3,2) 逐字转化： =numberstring(b3,3) 另外的写法 中文小写公式：=text(b3,”dbnum1”) 中文大写公式：=text(b3,”dbnum2”) 逐字转化： =text(b3,”dbnum10”)袁志刚409.5 应用技巧 格式编号 要求：在编号前加N0.字符，并使得编号为8位，不足前面补零 =NO.&TEXT(A2,00000000) 读取多个其他工作表上的数据 公式：=INDIRECT(D4&!b2) 其中D4是工作表名称所在单元格，b2是要读取的数据所在单元格 复制公式到其他工作表名称对应的行 取多个工作表名称 选择公式-名称管理器 定义名称为“list”：=MID(GET.WORKBOOK(1),FIND(,GET.WORKBOOK(1)+1,100) 在A1单元格中输入公式： =INDEX(list,ROW()袁志刚419.6 指数化图表 应用场景 数据系列的数量级差异较大 制作指数表 将第一期数据设为100 后面期间的数据=数据源当期数据/第一期数据*100 利用指数表数据制作图表袁志刚429.7 动态图表 控件的制作 绘制控件 设置数据源区域 设置单元格链接 写查询函数 常用查询函数：index,vlookup,offset,indirect,if Index函数的语法： Index(在哪找，找第几行的数，找第几列的数） 把index函数的位置参数与控件的单元格链接关联 制作图表 把图表与控件摆放好位置或组合在一起袁志刚439.8 自定义格式 缩位显示数值： 缩小1百位 0.00 缩小1千位： 0.00， 缩小1万位： 0!.0, 缩小10万位： 0!.00, 缩小100万位： 0.00, 缩小1000万位： 0!.0, 缩小1亿位： 0!.00, 缩小10亿位： 0.00,袁志刚4410.1 链接数据库 作用 直接访问财务系统数据库，取数做表，效率高。 方法 数据-自其它来源-来自microsoft query, 选择新数据源，输入名称与对应的驱动，选择数据库 进入查询向导后，选择要查询的主表中需要的字段，选择在query中查看数据或编辑查询，进入query界面：袁志刚4510.1 链接数据库 方法 如果需要在多个表中查询数据，点击“表”-条件表，然后选择需要的表，query会自动添加两个表之间的索引连接 如果发现未能自动添加，需要手工添加：按住表1索引字段，拖拽到表2的索引字段上。 将表中需要查询的字段拖拽到下面的预览框中 点击文件-将数据返回excel，即可在excel中查看查询结果。袁志刚46谢谢！47
