搜索
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基于地图的数据分析•作用–形象展示基于地理位置的区域数据分析。–容易理解，印象深刻，效果出众。•步骤–使用地图矢量图，为每一个区域定义名称，一般是该区域名称的拼音。选中区域，在名称框内输入即可。–输入宏，开发工具-visualbasic,双击thisworkbook,在右侧代码窗口输入以下代码：Subuser_click(region_name)ActiveSheet.Shapes(Range(m1).Value).Fill.ForeColor.SchemeColor=xlThemeColorDark1Range(m1).Value=region_nameActiveSheet.Shapes(region_name).Fill.ForeColor.SchemeColor=xlThemeColorAccent6EndSub袁志刚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_单击()Fori=3To34Range(province).Value=Range(data!a&i).ValueActiveSheet.Shapes(Range(province).Value).SelectSelection.ShapeRange.Fill.ForeColor.RGB=Range(Range(vbcode).Value).Interior.ColorNextiRange(f8).SelectEndSub袁志刚91.6复合增长率•概念–CAGR（CompoundAnnualGrowthRate）–一个指标（比如销售收入或净利润，投资回报等）在特定时期内的年度增长率–是较长时期内的测算，忽略个别年度的波动，将增长率平滑，反映指标的整体表现。•公式–=（当前数值/基期数值）^(1/年数）-1袁志刚101.7波士顿矩阵分析•简介–波士顿矩阵（BCGMatrix），又称市场增长率-相对市场份额矩阵，是由美国著名的管理学家、波士顿咨询公司创始人布鲁斯·亨德森于20世纪60年代末期首创的。–布鲁斯认为决定产品/业务结构的要素可分为2类，市场吸引力与企业实力。在反映市场引力的众多指标（销售增长率、目标市场容量、竞争对手强弱及利润高低）中，销售增长率是最具代表性的综合指标；而在反映企业实力的指标，如市场占有率，技术、设备、资金利用能力中，市场占有率是最能直接显示出企业竞争实力的指标。因此，波士顿矩阵选取的纵坐标与横坐标分别是“销售增长率”及“市场占有率”。–由以上2个因素相互作用，产生4个不同的象限，划分出4类性质的产品/业务：销售增长率和市场占有率都较高的产品/业务（简称“明星”）；销售增长率和市场占有率都较低的产品/业务（简称“瘦狗”）；销售增长率高而市场占有率低的产品/业务（简称“问号”）；销售增长率低而市场占有率高的产品/业务（简“现金牛”）。袁志刚11问题型业务现金型业务瘦狗型业务明星型业务高高低低市场增长市场份额1.7波士顿矩阵分析1.7波士顿矩阵分析•利用散点图制作波士顿矩阵–选中表格的数据列，制作散点图，行坐标为市场占有率，纵坐标为增长率–选中行坐标轴，设置坐标轴格式，勾选逆序刻度值，将纵坐标交叉选项改为坐标轴值：0.5，将刻度线类型与坐标轴标签设为无–选中纵坐标轴，除了不勾选逆序刻度值，其他设置与行坐标轴一样–Excel散点图的一个问题是无法为数据添加标签，可以使用一个第三方程序解决：xychartlabels–安装此程序后excel会出现一个新的选项卡xychartlabels,选择其中的addlabels命令–在其中的selectalabelrange选项中，选择数据表中的表前列即可–最后可为坐标轴添加标签或为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%，息税前利润增长（2×10%）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为变动