1、Excel在财务管理中的28个应用袁志刚 主讲1.Excel表格美学 让阅读报表的过程变得赏心悦目 Excel报表外观并非千篇一律 用Excel模版体现公司及个人风格 让晦涩的数据变得容易理解 2Excel在财务管理中的28个应用 袁志刚2.Excel表格内在结构类型 数据与维度的区分 数据：被分析的数据 维度：分析视角 数据列表 数据库中的表结构 特性：批量分析 行列式表格 用于观察和查看数据 特性：难以快速处理3Excel在财务管理中的28个应用 袁志刚2.Excel在数据管理中的位置后台数据库存储数据 Excel作为前台报表工具4Excel在财务管理中的28个应用 袁志刚2.Excel报
2、表的二元结构工作底稿（用于数据整理）业务系统（ERP,数据库） 其他表格（二维表，多维表，带有格式的报表）原始数据层中间整理层最终报表层 数据流最终报表（用于分发打印）目标：报表的自动化和自我维护（报表的勾稽关系和关联性）5Excel在财务管理中的28个应用 袁志刚2.Excel数据处理的三个环节录入数据库 查询统计 图表报表数据获取 数据处理 数据呈现6Excel在财务管理中的28个应用 袁志刚3.国际通用财务预测与预算模版 构成要素 假设（Asumption)通货膨胀，税率，利率，折旧方法 项目预测收入（Sales)，成本(Cost)，费用(Expense)，固定资产预测(FA) 损益表（
3、P&L）,资产负债表（BS)，现金流量表（Cash flow）当前数（Current)累计数（Year to date)实际数（Actual）预算数（Budget） 比较（Comparation） 仪表盘（Dash board）7Excel在财务管理中的28个应用 袁志刚3.三主表关联设计 设计要点 放在一个页面上 相关项目链接 资产负债表项目余额公式 把报表做平的方法8Excel在财务管理中的28个应用 袁志刚3.三主表关联设计 资产负债表的现金 = 现金流量表的现金余额 损益表的净利润 = 现金流量表的净利润 现金流量表的固定资产支出 = 资产负债表的固定资产计算所得 现金流量表的往来项目
4、 = 资产负债表的往来项目计算所得 资产负债表的本年利润 = 损益表的净利润 资产负债表的留存收益 = 资产负债表的本年利润计算所得 现金流量表的折旧 = 损益表的折旧费用 资产负债表的累计折旧 = 损益表的折旧费用计算所得9Excel在财务管理中的28个应用 袁志刚4.调整分录 报表调整出现的情形 调表与调账 审计 内部管理 如何调整报表？ 公式+注释 制作调整分录 报表调整分录的制作 报表结构 调整分录示例 10Excel在财务管理中的28个应用 袁志刚5.财务模型 模型 模型是被研究对象的一种抽象。模型反映了对象最本质的东西，略去了枝节，是被研究对象实质性的描述和某种程度的简化，目的在于
5、便于分析研究。 分析模型是对客观事物或现象的一种描述。 财务分析模型 财务分析模型就是通过一些财务指标建立一些模型，使得复杂的财务信息通俗易懂。 11Excel在财务管理中的28个应用 袁志刚5.第一个简单的财务模型 变动成本分析模型 构成要素分析表控件分析结果输出展示（图表）12Excel在财务管理中的28个应用 袁志刚 控件类型与选择 组合框：用于项目较多时 列表框：用于项目较少时 滚动条：用于需要调整数值时，长用于测算模型 按钮： 用于执行命令或宏 复选框：用于多选 创建窗体控件 调出开发工具选项卡Office按钮 excel选项 常用 勾选“在功能区显示开发工具选项卡” 创建窗体开发工
6、具 - 控件 插入 表单控件 选择一个窗体5.第一个简单的财务模型13Excel在财务管理中的28个应用 袁志刚5.第一个简单的财务模型 单元格链接 作用 链接方法 联动机制的形成 与公式的组合 与函数的组合Indexvlookup14Excel在财务管理中的28个应用 袁志刚6.费用测算与控制模型 模型用途 设计要点 变量设计方法 模拟运算表 滚动条控件 条件格式15Excel在财务管理中的28个应用 袁志刚6.模拟运算表的制作 模拟运算表概念 其实是把若干个相同公式的输入生成对应的值 简化成 一个公式的输入产生对应的值。 模拟运算表与普通公式的区别 模拟运算表一次性输入公式，如有更改也只需
7、要改一个地方不用过多考虑在公式中使用绝对引用还是相对引用表格中的数据无法单独修改公式中引用的参数必须引用“输入引用列/行的单元格”指向的单元格16Excel在财务管理中的28个应用 袁志刚6.模拟运算表的制作 普通的运算方式（公式，然后复制到各个对应的地方） 公式需要复制到每个对应的单元格 需要详细考虑每个参数在复制中需要发生的/不发生的变化，以决定使用绝对引用还是相对引用 每次如果需要更改公式，就要将所有的地方再重新复制一遍 表中的数据可以单独修改 公式中引用的参数直接指向数据列/行17Excel在财务管理中的28个应用 袁志刚6.盈亏临界分析模型 概念与作用 是指企业收入和成本相等的经营状
8、态，即边际贡献等于固定成本时企业所处的既不盈利又不亏损的状态。通常用一定的业务量来表示这种状态。 为企业经营决策提供在何种业务量下企业将盈利，以及在何种业务量下企业会出现亏损等总括性的信息 提供在业务量基本确定的情况下，企业降低多少成本，或增加多少收入才不至于亏损的特定经济信息。 制作要点 公式设计 滚动条制作 模拟运算表 盈亏临界销量计算：固定成本/(单价-单位变动成本) 18Excel在财务管理中的28个应用 袁志刚7.利润敏感性分析模型 概念 研究和制约利润的有关因素发生某种变化时，利润变化程度的一种分析方法。那些对利润影响大的因素称为敏感因素，反之，称为非敏感因素 一般情况下，影响利润
9、的因素有4个：价格、单位变动成本、销售量和固定成本 敏感系数： 敏感系数是反映敏感程度的指标 某因素的敏感系数利润变化()该因素变化() 判断标准：敏感系数的绝对值1，即当某影响因素发生变化时，利润发生更大程度的变化，该影响因素为敏感因素 敏感系数的绝对值1，即利润变化的幅度小于影响因素变化的幅度，该因素为非敏感因素 敏感系数的绝对值1，即影响因素变化合导致利润相同程度的变化，该因素亦为非敏感因素 19Excel在财务管理中的28个应用 袁志刚7.利润敏感性分析模型 如何使用一般而言，在对利润产生影响的各因素中，灵敏度最高的为单价，最低的是固定成本，销量和单位变动成本介于两者之间。利润敏感性分
10、析法能够预测各种不利因素对项目的影响，从而判断项目可能容许的风险程度。利润敏感性分析法的主要作用之一是进行利润的预测和规划。 制作要点 变化速率设计 滚动条制作 模拟运算表制作 条件格式20Excel在财务管理中的28个应用 袁志刚8.仪表盘（Dash board） 什么是仪表盘（Dash board）？ 是将多个仪表、图表、报表等内容整合在一个页面上进行显示的工具，这个概念在Business Intelligence方向有普遍的应用。 可以展示具有重要影响力的信息，以监视、衡量和管理企业绩效。21Excel在财务管理中的28个应用 袁志刚8.仪表盘的类型与构成要素 仪表盘的类型 静态仪表盘
11、动态仪表盘 仪表盘的构成要素 数据表格 控件 链接公式 图表22Excel在财务管理中的28个应用 袁志刚8.仪表盘实例 债务仪表盘 生产仪表盘 酒店管理仪表盘 产业分析仪表盘 人力资源仪表盘 财务指标仪表盘 23Excel在财务管理中的28个应用 袁志刚9.应收账款账龄分析 所需掌握的功能 透视表基础数据表格式创建透视表添加计算字段 IF函数If函数嵌套And，Or函数 应收账款回收率分析 以透视表为基础执行额外计算24Excel在财务管理中的28个应用 袁志刚9.透视表 透视表基础 优势：灵活，迅速，智能 功能：多维分析，分类汇总 所要求的数据表结构 基本操作：鼠标拖拽 计算字段 透视表工
12、具选项卡 公式 选择计算字段，在“公式栏”内可以写公式，并可以调用函数 比率分析 右键 - 值字段设置 值显示方式，根据需要选择相应项目25Excel在财务管理中的28个应用 袁志刚10.自动审计公式 资产负债表 勾稽关系校验合计数校验资产=负债+所有者权益 表内数字合理性校验科目其中项目合计无形资产土地使用权盈余公积法定公益金未分配利润现金股利26Excel在财务管理中的28个应用 袁志刚10.自动审计公式 损益表 勾稽关系校验合计数校验上年年末未分配利润=今年年初未分配利润未分配利润=资产负债表的未分配利润 表内数字合理性校验科目其中项目合计27Excel在财务管理中的28个应用 袁志刚1
13、0.自动审计公式 现金流量表 勾稽关系校验合计数校验现金期末余额=资产负债表货币资金期末余额现金期初余额=资产负债表货币资金期初余额净利润=损益表净利润少数股东损益=损益表少数股东损益未确认的投资损失=损益表未确认的投资损失 表内数字合理性校验科目其中项目合计28Excel在财务管理中的28个应用 袁志刚11.Excel表单设计 结构化去掉网格线添加必要的边框 色彩引导区分手工输入区域与公式，链接 注释与帮助Read me页面 按钮，超链接，控件，VBA 第三方元素 权限与保护29Excel在财务管理中的28个应用 袁志刚12.固定资产投资模型 用于分析固定资产投资带来的现金流量的变化。 设计
14、要点 不同折旧方法及对应函数的使用直线法（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.公司估值（Company Val
15、uation） 企业价值 账面价值资产负债表上的资产，反映历史成本，用于会计目的 市场价值如股票价值，反映未来收益，投资者关注的是市场价值 公司估值 公司估值是指着眼于公司本身，对公司的内在价值进行评估。公司内在价值决定于公司的资产及其获利能力。 公司估值是投融资、交易的前提。一家投资机构将一笔资金注入企业，应该占有的权益首先取决于企业的价值。32Excel在财务管理中的28个应用 袁志刚12.公司估值的作用 公司估值有利于我们对公司或其业务的内在价值进行正确评价，从而确立对各种交易进行订价的基础。 筹集资本（capital raising)； 收购合并（mergers & acquisiti
16、ons)； 公司重组（corporate restructuring)； 出售资产或业务（divestiture) 财务模型和公司估值是投资银行的重要方法，广泛运用于各种交易。 33Excel在财务管理中的28个应用 袁志刚12.公司估值 估值的不确定性 巴菲特：“如果一项资产目前市价只是略低于其内在价值，我们没有兴趣买入它；只有在有显著折扣时我们才会买入 ” 估值最根本的方法：彻底了解这家公司 主要估值方法 可比公司法 历史交易法 现金流贴现法（DCF）34Excel在财务管理中的28个应用 袁志刚12.现金流量折现（DCF） DCF：Discounted Cash Flow 现金流量折现
17、DCF的方法是指：预测未来现金流，并用贴现率和终值计算方法计算现金流的贴现值，以此计算企业价值。 使用情形在发达国家市场中，各种数据比较完善，公司日趋成熟，DCF成为通用的估值方法。中国DCF方法似乎受到许多局限，一方面是基础数据缺乏，另一方面是一部分公司的持续经营能力令人怀疑。 优点完整的评价模型，理论上最完善考虑公司发展的长期性，承认资金时间价值 缺点数据估算具有高度的主观性与不确定性 35Excel在财务管理中的28个应用 袁志刚12.货币的时间价值与贴现 货币具有时间价值 当前的一元钱比未来的一元钱值钱 原因：货币投资可以产生增值 比如存入银行可以产生利息，未来就不止一元钱了。 贴现
18、未来的现金流需要使用一个贴现率（如银行利率），折算成今天值多少钱，这个折算的过程叫贴现。 估值 对公司估值时，不是简单地把未来产生的现金流直接相加，而是选取一个恰当的贴现率，将未来的现金流贴现到现在，然后相加。 36Excel在财务管理中的28个应用 袁志刚12.加权平均资本成本（WACC） 如何确定贴现率？ 一般使用WACC（加权平均资本成本）作为贴现率 巴菲特使用美国长期国债利率 WACC概念 英文Weighted Average Cost of Capital的缩写。WACC代表公司整体平均资金成本，可用来衡量一个项目是否值得投资；项目的回报必须不低于WACC。 WACC计算 计算WAC
19、C时，先算出构成公司资本结构的各个项目如普通股、优先股、公司债及其他长期负债各自的资金成本或要求回报率，然后将这些回报率按各项目在资本结构中的权重加权，即可算出加权平均资本成本。 37Excel在财务管理中的28个应用 袁志刚12.加权平均资本成本（WACC） 计算公式 WACC=（债务/资本）*债务成本*（1-企业所得税税率）+（1-债务/资本）*股权成本 其中，债务成本和股权成本用债务人和股东要求的收益率表示。 公司估值现金流贴现法就是把公司所有投资者的现金流，包括债权人 和股权投资者的现金流折现，而折现率则是加权平均资本成本。因此，WACC是投资公司价值评估的一个重要的计算参数。 38E
20、xcel在财务管理中的28个应用 袁志刚12.WACC计算因子 股本成本的计算 股权成本=无风险收益率+风险溢价*Beta无风险收益率：国债利率或银行利率风险溢价：股票市场长期平均的回报率高出无风险收益率的部分Beta：特定企业相对于股票市场的波动性，反映特定企业自身的风险 如何计算Beta?首先要分析可比公司的beta,由于可比公司的资本机构可能与被估值企业差异很大，所以要将可比公司的beta折算成无杠杆的beta可比公司无杠杆beta的平均值/中间值用作被估值企业的风险系数，为反映财务风险，需要重新考虑被估值企业的资本结构，计算有杠杆beta39Excel在财务管理中的28个应用 袁志刚1
21、2.WACC计算因子 无杠杆beta和有杠杆beta的关系： D=债务，E=股权，t=税率，Pref./E=优先股 无杠杆beta=有杠杆beta/(1+(D/E)(1-t)+(Pref./E)40Excel在财务管理中的28个应用 袁志刚12.IPO定价与发行公司估值的关系 IPO概念 IPO定价(Initial Public Offering)是指首次获准公开发行股票上市的公司与其承销商共同确定的将股票公开发售给特定或非特定投资者的价格。 两者关系 公司估值是IPO定价的基础 IPO定价与公司估值不能等同。IPO还通过选择合适的发售机制来确定影响价格的因素(如市场需求)以最终敲定价格41E
22、xcel在财务管理中的28个应用 袁志刚12.自由现金流量 自由现金流是一种财务方法，用来衡量企业实际持有的能够回报股东的现金。指在不危及公司生存与发展的前提下可供分配给股东(和债权人)的最大现金额。 自由现金流量等于企业的税后净营业利润(即将公司不包括利息收支的营业利润扣除实付所得税税金之后的数额)加上折旧及摊销等非现金支出，再减去营运资本的追加和物业厂房设备及其他资产方面的投资。它是公司所产生的税后现金流量总额，可以提供给公司资本的所有供应者，包括债权人和股东。”42Excel在财务管理中的28个应用 袁志刚12.自由现金流量 自由现金流量=(税后净利润+利息+折旧及摊销等非现金支出)一(
23、资本支出+营运资本增加) 中国计算公式 = 经营活动产生的现金流量净额资本性支出 = 经营活动产生的现金流量净额 （购建固定、无形和其他长期资产所支付的现金 处置固定、无形和其他长期资产而收回的现金净额）43Excel在财务管理中的28个应用 袁志刚12.公司估值财务模型 建立财务模型的重要步骤： 在excel里建立历史的现金流量表，并把资产负债表，损益表和现金流量表链接在一起 现金流计算 营运资本=存货+应收账款+其他流动资产-应付账款-其他流动负债 现金=净利润+折旧-营运资本-资本支出+新增股本-股利+债务44Excel在财务管理中的28个应用 袁志刚12.建模-财务模型架构假设 资产负
24、债表损益表现金流量表资本支出与折旧表融资预算表运营资本计算表WACC计算表企业价值计算表 重要指标概览DCF估值45Excel在财务管理中的28个应用 袁志刚12.财务模型建模 经营预测 收入预测 成本费用预测可以使用占收入的百分比来表述 运营资本预测根据有关资产，负债相对于收入或成本的周转天数来计算存货周转天数应收账款周转天数应付账款周转天数 资本支出预测46Excel在财务管理中的28个应用 袁志刚12.财务模型建模 融资政策 债务政策以还债为首要任务，还是保持适当的债务比例？何时是偿还债务的最佳时机？如何达到最佳资本结构？ 融资政策计划新增多少股本或贷款 运营资本政策需要保持的最低现金4
25、7Excel在财务管理中的28个应用 袁志刚12.财务模型建模 格式 手工输入，公式，链接分别使用不同色彩标识 逻辑清晰 适当使用名称定义 注释和帮助系统 灵活性 便于对变量进行调整48Excel在财务管理中的28个应用 袁志刚13.偿还贷款 Excel中几个与贷款有关的函数 Pmt(): 等额分期偿还贷款=pmt(rate,nper,pv,fv,type)Rate:贷款利率Nper:贷款期限Pv: 贷款金额Fv,type: 可省略 Ppmt(): 偿还本金的部分 Ipmt(): 偿还利息的部分49Excel在财务管理中的28个应用 袁志刚14.订单分布状况 Frequency：频率函数 分段
26、点与统计区间 与数组公式一起使用了解数组公式Crtl+shift+enter不可单独删除其中的部分公式如需算出多个值，需要首先选中数量相等的目标区域50Excel在财务管理中的28个应用 袁志刚15.高级商务图表制作51Excel在财务管理中的28个应用 袁志刚15.高级商务图表制作52Excel在财务管理中的28个应用 袁志刚15.高级商务图表制作53Excel在财务管理中的28个应用 袁志刚15.高级商务图表制作54Excel在财务管理中的28个应用 袁志刚15.高级商务图表制作55Excel在财务管理中的28个应用 袁志刚15.高级商务图表制作56Excel在财务管理中的28个应用 袁志刚15.高级商务图表制作 几种配色方案57Excel在财务管理中的28个应用 袁志刚15.高级商务图表制作 利用colorpix软件获取商周图表配色RGB值58Excel在财务管理中的28个应用 袁志刚15.高级商务图表制作 改变excel图表默认布局为商周布局59Excel在财务管理中的28个应用 袁志刚15.高级商务图表制作 中文字体： 黑体或微软雅黑 数字字体： Arial 去除纵坐标，绘图区边框 淡化网格线 在脚注区添加数据来源60Excel在财务管理中的28个应用 袁志刚
