excel在财务管理中的28个应用

Excel在财务管理中的28个应用袁志刚主讲1.Excel表格美学让阅读报表的过程变得赏心悦目Excel报表外观并非千篇一律用Excel模版体现公司及个人风格让晦涩的数据变得容易理解2Excel在财务管理中的28个应用袁志刚2.Excel表格内在结构类型数据与维度的区分•数据：被分析的数据•维度：分析视角数据列表•数据库中的表结构•特性：批量分析行列式表格•用于观察和查看数据•特性：难以快速处理3Excel在财务管理中的28个应用袁志刚2.Excel在数据管理中的位置后台数据库存储数据Excel作为前台报表工具4Excel在财务管理中的28个应用袁志刚2.Excel报表的二元结构工作底稿（用于数据整理）业务系统（ERP,数据库）其他表格（二维表，多维表，带有格式的报表）原始数据层中间整理层最终报表层数据流最终报表（用于分发打印）目标：报表的自动化和自我维护（报表的勾稽关系和关联性）5Excel在财务管理中的28个应用袁志刚2.Excel数据处理的三个环节•录入•数据库•查询•统计•图表•报表数据获取数据处理数据呈现6Excel在财务管理中的28个应用袁志刚3.国际通用财务预测与预算模版构成要素•假设（Asumption)通货膨胀，税率，利率，折旧方法•项目预测收入（Sales)，成本(Cost)，费用(Expense)，固定资产预测(FA)•损益表（P&L）,资产负债表（BS)，现金流量表（Cashflow）当前数（Current)累计数（Yeartodate)实际数（Actual）预算数（Budget）•比较（Comparation）•仪表盘（Dashboard）7Excel在财务管理中的28个应用袁志刚3.三主表关联设计设计要点•放在一个页面上•相关项目链接•资产负债表项目余额公式•把报表做平的方法8Excel在财务管理中的28个应用袁志刚3.三主表关联设计资产负债表的现金=现金流量表的现金余额损益表的净利润=现金流量表的净利润现金流量表的固定资产支出=资产负债表的固定资产计算所得现金流量表的往来项目=资产负债表的往来项目计算所得资产负债表的本年利润=损益表的净利润资产负债表的留存收益=资产负债表的本年利润计算所得现金流量表的折旧=损益表的折旧费用资产负债表的累计折旧=损益表的折旧费用计算所得9Excel在财务管理中的28个应用袁志刚4.调整分录报表调整出现的情形•调表与调账•审计•内部管理如何调整报表？•公式+注释•制作调整分录报表调整分录的制作•报表结构•调整分录示例10Excel在财务管理中的28个应用袁志刚5.财务模型模型•模型是被研究对象的一种抽象。模型反映了对象最本质的东西，略去了枝节，是被研究对象实质性的描述和某种程度的简化，目的在于便于分析研究。•分析模型是对客观事物或现象的一种描述。财务分析模型•财务分析模型就是通过一些财务指标建立一些模型，使得复杂的财务信息通俗易懂。11Excel在财务管理中的28个应用袁志刚5.第一个简单的财务模型变动成本分析模型•构成要素分析表控件分析结果输出展示（图表）12Excel在财务管理中的28个应用袁志刚控件类型与选择•组合框：用于项目较多时•列表框：用于项目较少时•滚动条：用于需要调整数值时，长用于测算模型•按钮：用于执行命令或宏•复选框：用于多选创建窗体控件•调出开发工具选项卡Office按钮–excel选项–常用–勾选“在功能区显示开发工具选项卡”•创建窗体开发工具-控件–插入–表单控件–选择一个窗体5.第一个简单的财务模型13Excel在财务管理中的28个应用袁志刚5.第一个简单的财务模型单元格链接•作用•链接方法联动机制的形成•与公式的组合•与函数的组合Indexvlookup14Excel在财务管理中的28个应用袁志刚6.费用测算与控制模型模型用途设计要点•变量设计方法•模拟运算表•滚动条控件•条件格式15Excel在财务管理中的28个应用袁志刚6.模拟运算表的制作模拟运算表概念•其实是把若干个相同公式的输入生成对应的值简化成一个公式的输入产生对应的值。模拟运算表与普通公式的区别•模拟运算表一次性输入公式，如有更改也只需要改一个地方不用过多考虑在公式中使用绝对引用还是相对引用表格中的数据无法单独修改公式中引用的参数必须引用“输入引用列/行的单元格”指向的单元格16Excel在财务管理中的28个应用袁志刚6.模拟运算表的制作普通的运算方式（公式，然后复制到各个对应的地方）•公式需要复制到每个对应的单元格•需要详细考虑每个参数在复制中需要发生的/不发生的变化，以决定使用绝对引用还是相对引用•每次如果需要更改公式，就要将所有的地方再重新复制一遍•表中的数据可以单独修改•公式中引用的参数直接指向数据列/行17Excel在财务管理中的28个应用袁志刚6.盈亏临界分析模型概念与作用•是指企业收入和成本相等的经营状态，即边际贡献等于固定成本时企业所处的既不盈利又不亏损的状态。通常用一定的业务量来表示这种状态。•为企业经营决策提供在何种业务量下企业将盈利，以及在何种业务量下企业会出现亏损等总括性的信息•提供在业务量基本确定的情况下，企业降低多少成本，或增加多少收入才不至于亏损的特定经济信息。制作要点•公式设计•滚动条制作•模拟运算表•盈亏临界销量计算：固定成本/(单价-单位变动成本)18Excel在财务管理中的28个应用袁志刚7.利润敏感性分析模型概念•研究和制约利润的有关因素发生某种变化时，利润变化程度的一种分析方法。那些对利润影响大的因素称为敏感因素，反之，称为非敏感因素•一般情况下，影响利润的因素有4个：价格、单位变动成本、销售量和固定成本敏感系数：•敏感系数是反映敏感程度的指标•某因素的敏感系数＝利润变化(％)／该因素变化(％)•判断标准：敏感系数的绝对值＞1，即当某影响因素发生变化时，利润发生更大程度的变化，该影响因素为敏感因素敏感系数的绝对值＜1，即利润变化的幅度小于影响因素变化的幅度，该因素为非敏感因素敏感系数的绝对值＝1，即影响因素变化合导致利润相同程度的变化，该因素亦为非敏感因素19Excel在财务管理中的28个应用袁志刚7.利润敏感性分析模型如何使用一般而言，在对利润产生影响的各因素中，灵敏度最高的为单价，最低的是固定成本，销量和单位变动成本介于两者之间。利润敏感性分析法能够预测各种不利因素对项目的影响，从而判断项目可能容许的风险程度。利润敏感性分析法的主要作用之一是进行利润的预测和规划。制作要点•变化速率设计•滚动条制作•模拟运算表制作•条件格式20Excel在财务管理中的28个应用袁志刚8.仪表盘（Dashboard）什么是仪表盘（Dashboard）？•是将多个仪表、图表、报表等内容整合在一个页面上进行显示的工具，这个概念在BusinessIntelligence方向有普遍的应用。•可以展示具有重要影响力的信息，以监视、衡量和管理企业绩效。21Excel在财务管理中的28个应用袁志刚8.仪表盘的类型与构成要素仪表盘的类型•静态仪表盘•动态仪表盘仪表盘的构成要素•数据表格•控件•链接公式•图表22Excel在财务管理中的28个应用袁志刚8.仪表盘实例债务仪表盘生产仪表盘酒店管理仪表盘产业分析仪表盘人力资源仪表盘财务指标仪表盘……23Excel在财务管理中的28个应用袁志刚9.应收账款账龄分析所需掌握的功能•透视表基础数据表格式创建透视表添加计算字段•IF函数If函数嵌套And，Or函数应收账款回收率分析•以透视表为基础执行额外计算24Excel在财务管理中的28个应用袁志刚9.透视表透视表基础•优势：灵活，迅速，智能•功能：多维分析，分类汇总•所要求的数据表结构•基本操作：鼠标拖拽计算字段•透视表工具选项卡–公式–选择计算字段，在“公式栏”内可以写公式，并可以调用函数比率分析•右键-值字段设置–值显示方式，根据需要选择相应项目25Excel在财务管理中的28个应用袁志刚10.自动审计公式资产负债表•勾稽关系校验合计数校验资产=负债+所有者权益•表内数字合理性校验科目其中项目合计无形资产土地使用权盈余公积法定公益金未分配利润现金股利26Excel在财务管理中的28个应用袁志刚10.自动审计公式损益表•勾稽关系校验合计数校验上年年末未分配利润=今年年初未分配利润未分配利润=资产负债表的未分配利润•表内数字合理性校验科目其中项目合计27Excel在财务管理中的28个应用袁志刚10.自动审计公式现金流量表•勾稽关系校验合计数校验现金期末余额=资产负债表货币资金期末余额现金期初余额=资产负债表货币资金期初余额净利润=损益表净利润少数股东损益=损益表少数股东损益未确认的投资损失=损益表未确认的投资损失•表内数字合理性校验科目其中项目合计28Excel在财务管理中的28个应用袁志刚11.Excel表单设计结构化去掉网格线添加必要的边框色彩引导区分手工输入区域与公式，链接注释与帮助Readme页面按钮，超链接，控件，VBA第三方元素权限与保护29Excel在财务管理中的28个应用袁志刚12.固定资产投资模型用于分析固定资产投资带来的现金流量的变化。设计要点•不同折旧方法及对应函数的使用直线法（SLN）双倍余额递减法（DDB）年数总和法（SYD）•不同折旧方法的选择功能30Excel在财务管理中的28个应用袁志刚12.投资评估指标净现值计算（NPV)•=npv(rate,value1,value2…)•Rate为贴现率•Value1,value2…为现金流，间隔必须相等，且都发生于期末内部收益率计算(IRR)•=IRR(values,guess)•Values为现金流，必须固定间隔，且至少1个正数和1个负数NPV与IRR的关系•IRR是使得NPV=0的比率31Excel在财务管理中的28个应用袁志刚12.公司估值（CompanyValuation）企业价值•账面价值资产负债表上的资产，反映历史成本，用于会计目的•市场价值如股票价值，反映未来收益，投资者关注的是市场价值公司估值•公司估值是指着眼于公司本身，对公司的内在价值进行评估。公司内在价值决定于公司的资产及其获利能力。•公司估值是投融资、交易的前提。一家投资机构将一笔资金注入企业，应该占有的权益首先取决于企业的价值。32Excel在财务管理中的28个应用袁志刚12.公司估值的作用公司估值有利于我们对公司或其业务的内在价值进行正确评价，从而确立对各种交易进行订价的基础。•筹集资本（capitalraising)；•收购合并（mergers&acquisitions)；•公司重组（corporaterestructuring)；•出售资产或业务（divestiture)财务模型和公司估值是投资银行的重要方法，广泛运用于各种交易。33Excel在财务管理中的28个应用袁志刚12.公司估值估值的不确定性•巴菲特：“如果一项资产目前市价只是略低于其内在价值，我们没有兴趣买入它；只有在有‘显著折扣’时我们才会买入”估值最根本的方法：彻底了解这家公司主要估值方法•可比公司法•历史交易法•现金流贴现法（DCF）34Excel在财务管理中的28个应用袁志刚12.现金流量折现（DCF）DCF：DiscountedCashFlow现金流量折现•DCF的方法是指：预测未来现金流，并用贴现率和终值计算方法计算现金流的贴现值，以此计算企业价值。•使用情形在发达国家市场中，各种数据比较完善，公司日趋成熟，DCF成为通用的估值方法。中国DCF方法似乎受到许多局限，一方面是基础数据缺乏，另一方面是一部分公司的持续经营能力令人怀疑。•优点完整的评价模型，理论上最完善考虑公司发展的长期性，承认资金时间价值•缺点数据估算具有高度的主观性与不确定性35Excel在财