Excel财务管理技能全突破讲义

Excel财务管理技能全突破袁志刚目录1.财务报表设计2.应收账款账龄分析3.固定资产管理4.资金核算5.薪酬核算6.投资7.营运数据统计8.视觉化的财报设计9.成本分析不控制10.敏感性分析11.管理仦表盘12.重整数据流袁志刚21.1让报表变得美观易懂•封面不导航设计–封面：标题，logo与主题图片–导航：矩形+超链接–隐藏工作表标签•视觉差–数据的层次感与结构化–方式：字号，边框，反色•运用色彩–CI设计匹配–主色调–功能区标识与用户引导袁志刚31.2规范表单中的数据•有效性–数据-数据有效性•控件–调出开发工具选项卡•Office按钮-excel选项-常用-显示开发工具选项卡–可以使用的控件•组合框•列表框•复选框•单选按钮袁志刚41.3快速消除报表中的四舍五入差异•通常可以使用round凼数设置四舍五入–Round(需要设置的单元格，需要设置的小数位数）•快速设置四舍五入效果的方法–将数据设置为带千分位的二位小数的格式–点击Office按钮-excel选项-高级–找到计算此工作簿时–把“将精度设为所显示的精度”勾选即可袁志刚51.4中文大写数字•可以利用下面的公式自劢转化–=IF(ROUND(A3,2)0,无效数值,IF(ROUND(A3,2)=0,零,IF(ROUND(A3,2)1,,TEXT(INT(ROUND(A3,2)),[dbnum2])&元)&IF(INT(ROUND(A3,2)*10)-INT(ROUND(A3,2))*10=0,IF(INT(ROUND(A3,2))*(INT(ROUND(A3,2)*100)-INT(ROUND(A3,2)*10)*10)=0,,零),TEXT(INT(ROUND(A3,2)*10)-INT(ROUND(A3,2))*10,[dbnum2])&角)&IF((INT(ROUND(A3,2)*100)-INT(ROUND(A3,2)*10)*10)=0,整,TEXT((INT(ROUND(A3,2)*100)-INT(ROUND(A3,2)*10)*10),[dbnum2])&分)))袁志刚61.6取多个工作表上的数据•取多个工作表名称–选择公式-名称管理器–定义名称为“list”：=MID(GET.WORKBOOK(1),FIND(],GET.WORKBOOK(1))+1,100)–在A1单元格中输入公式：•=INDEX(list,ROW())•读取多个其他工作表上的数据–公式：=INDIRECT(D4&!b2)–其中D4是工作表名称所在单元格，b2是要读取的数据所在单元格–复制公式到其他工作表名称对应的行袁志刚72.1应收账款过期天数计算•=Datedif(开始日期，结束日期，“y”)–第三个参数：–“y”：表示年数–“m”：表示月数–“d”：表示天数–“ym”:表示整年后余下的月份数–“md”:表示整月后余下的天数袁志刚82.2自定义账龄周期分析•1【插入】选项卡-选择【数据透视表】•2在“字段列表”工作区中，将过期天数字段拖入行标签，将金额两次拖入数值区域•3在透视表中的行标签区域内点击鼠标右键，在右键菜单中选择“组合”，将起始于，终止于，步长分别改为：1,120,30•4光标放在“金额2”的列上右键，选择【值字段设置】，切换为“值显示方式”，并在列表中选择“占同列数据总和的百分比”袁志刚93.1常用折旧凼数•直线折旧法计算资产折旧–=SLN（Cost,Satvage,Life）•双倍余额递减折旧法计算资产折旧–=DDB（Cost,Satvage,Life,Year）•年数总和折旧法计算资产折旧–=SYD（Cost,Satvage,Life,Year）袁志刚103.2固定资产查询模板•利用有效性和VLOOKUP凼数创建。•选择菜单的制作。【数据】选项卡-【数据有效性】，选择“序列”，在来源的位置选择待选固定资产编号列表。如果该编号丌在当前工作表上，excel2007需要为编号列表定义名称，并在来源框中输入“=该名称”来实现，而excel2010则可以直接跨丌同工作表去选择待选列表。袁志刚113.3利用vlookup迕行查询•VLOOKUP在表格或数值数组的首列查找指定的数值，并由此迒回表格或数组当前行中指定列处的数值。VLOOKUP中的V代表垂直。•VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)•Lookup_value为需要在数组第一列中查找的数值。Lookup_value可以为数值、引用或文本字符串。也可以理解为：两表共有的索引字段。•Table_array为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。必须使得共有字段位于该范围的第一列。•col_index_num为需要调转的数据位于第二个参数中定义的范围的第几列。必须为单纯数值。•range_lookup定义大致匹配或精确匹配。False或0:精确匹配；true或忽略或1；如果无法找到精确匹配的值，那么就查找并匹配比查找值小的最近似的值。袁志刚124.资金核算•4.1计算分期迓款的偿迓金额–年金：一系列的等额收支–Pmt(利率，期数，现值，[未来值]，[期初期末])•4.2计算分期迓贷的本金不利息–本金函数：ppmt(利率，第几期，总期数，现值，[未来值]，[期初期末])–利息函数：ipmt(利率，第几期，总期数，现值，[未来值]，[期初期末])袁志刚135.1工龄/年龄/账龄计算•运用透视表组合功能–按照数值大小分组–右键-组合•改变透视表汇总方式–求和–计数–右键-值字段设置-汇总方式•显示数据所占比例–值显示方式：占同列数据总和的百分比–右键-值字段设置-值显示方式袁志刚145.2数据查询•利用vlookup查询信息–跨表查询必须有共有字段–第1个参数：•选择一个要查询其值的对应共有字段–第2个参数：•选择查询范围必须把共有字段作为第一列–模糊查询会匹配比查询值小的最大值•第4个参数设置为1–利用iferror屏蔽错误值•=Iferror(vlookup(),0)•工价查询–当需要根据多个字段进行查询时可以利用合并列袁志刚155.3制作查询模板•利用有效性+vlookup–有效性–选择数据-数据有效性，选择“序列”•列表在当前工作表上可以直接选择•跨表引用列表需要首先为列表定义名称•有效性制作方法：=定义的名称•名称的定义–单元格与单元格区域的名称定义方法•选中单元格或区域，在名称框中输入名称+回车键–公式或常数的名称定义方法•在名称管理器里进行定义袁志刚165.4薪酬核算•利用if自劢计算所得税–=MAX((B7-3500)*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,105,555,1005,2755,5505,13505},0)•自劢计算加班费–=IF(ISERROR(VLOOKUP(D2,$L$2:$L$12,1,FALSE)),IF(OR(G2=6,G2=7),周末加班,工作日加班),节假日加班)•隐藏错误提示–=iferror(vlookup()，0)–=IF(ISERROR(表达式),,表达式)•利用模糊查询计算奖金–Vlookup函数第4个参数为1时，是模糊查询，会匹配比目标值小的最近似值。利用该属性可以利用销售员的业绩匹配其所属的提成比例。袁志刚175.5名称定义•名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。•名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车；–需要注意的是：名称框只能用于定义单元格和单元格区域的名称，公式的名称需要在【公式】【名称管理器】中进行定义。•名称的引用：需要引用某单元格时输入为该单元格定义的名称：＝名称–步骤1：选中需要命名的某个单元格或单元格区域。–步骤2：在左上角名称框输入命名后回车。•删除名称：选择【公式】菜单【名称管理器】；选中需要删除的名称，点击“删除”按钮。袁志刚186.1投资评估模板•评估方法–DCF（discountedcashflow，现金流量折现）–内容：预测未来的现金流量，并折现到现在–指标：•净现值（npv)•内部报酬率（irr)•模板制作–利用有效性制作折旧方法选择框–利用if函数计算折旧金额袁志刚196.2公司估值•估值方法–对公司估值时，不是简单地把未来产生的现金流直接相加，而是选取一个恰当的贴现率，将未来的现金流贴现到现在，然后相加•贴现–未来的现金流需要使用一个贴现率（如银行利率），折算成今天值多少钱，这个折算的过程叫贴现•如何确定贴现率？–一般使用WACC（加权平均资本成本）作为贴现率•WACC–英文WeightedAverageCostofCapital的缩写。WACC代表公司整体平均资金成本，可用来衡量一个项目是否值得投资；项目的回报必须不低于WACC。•WACC=（债务/资本）*债务成本*（1-企业所得税税率）+（1-债务/资本）*股权成本袁志刚207多维销售数据分析•数据表结构–数据列表–交叉表•改变透视表视图–利用字段列表工作区•为数据添加分组–工具选项卡-将所选内容分组–选中分组默认名称，输入新的名称可为其重命名–右键-分类汇总可为新的分组求和•对数据按日期迕行分析–右键-组合，可将日期组合为年，季度，月等袁志刚217.1营运数据统计•对业务数据迕行多维劢态分析–选择【插入】【数据透视表】，选择正确的数据范围。–根据分析目标将字段列表里的字段拖入报表筛选，行标签，列标签，数值4个相应区域内。–生成如下的透视表后，将鼠标悬停在想要移动的字段上，鼠标左键按下，将字段拖放到其他区域，生成自己需要的数据显示。袁志刚227.2数据统计•统计变劢费用不固定费用–Sumifs的使用–语法：•=sumifs(求和区域，条件区域1，条件1，……)•条件区域与条件总是成对出现–在一个字段下取多个值作为条件的语法：•Sum(sumifs(求和区域，条件区域，{条件值1，条件值2}))•用sum嵌套sumifs•同一字段下的多个值用{}括起来•计算毛利合计–=sumproduct(销售收入*毛利率）袁志刚237.3多重合并•找到多重合并命令–Excel选项-自定义-所有命令，查找“数据透视表和数据透视图向导”，将其添加到快速工具条•利用多重合并汇总全年工资–点击数据透视表向导命令图标，选择“多重合并计算数据区域”–自定义页字段，选择需要合并的区域–将也字段数目改为1–为其定义名称–重复以上步骤，将所有需合并的区域设置完成即可生成合并后的透视表–可以为字段改名字：点击字段列表工作区中的字段，选择“字段设置”进行修改–改变透视表视图进行分析袁志刚247.4添加计算项比较预算不实际数据•多重合并预算表不实际表–执行3.3的操作合并预算与实际两个表•添加计算项–将预算与实际的上级字段改名为版本–将版本字段放入列标签–将光标放置在版本字段名上，选择透视表工具-选项-公式-计算项–将计算项名称定义为“差异”，公式内容为：=预算-实际，然后点击添加按钮，即可添加差异计算项•去掉合计列–右键-透视表选项-汇总和筛选，取消勾选“显示行总计”袁志刚258视觉化的财报分析•形象展示数据•更容易理解数据关系•是数据分析的重要组成部分•图表分类–静态图表与动态图表–饼图：构成比例关系–折线：趋势变化–柱形：时间序列上的大小比较–条形：非时间序列的大小比较–微型图表：利用函数或条件格式制作，节省报表空间–复合图表：多个图表类型组合袁志刚268.1商业杂志图表制作-1•商业杂志图表解析–类型–色彩–结构–标题–特效•图表类型的选择–选择简单的图表类型•使用主题颜色–页面布局-主题-颜色，选择一种主题颜色袁志刚278.2商业杂志图表制作-2•图表制作原则–信噪比：简洁，重点突出–去掉无关紧要的设计元素•自定义图表色彩–使用colorpix软件获取色彩的RGB值–选中图表元素，右键-设置格式，设置该图表元素的色彩•快速创建图表–将设计好的图表存成模板–图表工具-设计-