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