《EXCEL与财务分析.ppt》由会员分享，可在线阅读，更多相关《EXCEL与财务分析.ppt（52页珍藏版）》请在三一文库上搜索。
1、EXCEL财务分析,第十章 EXCEL与财务分析,10.1 EXCEL与财务预测 10.2 EXCEL与债券估价 10.3 EXCEL与存货管理 10.4 EXCEL与短期筹资决策 10.5 EXCEL与最佳资本结构 10.6 EXCEL与量本利分析 10.7 EXCEL与设备投资决策 10.8 EXCEL与人力资源管理,基础知识：如何使用控件,例1：某人准备购买一套住房，现有若干面积、楼层和位置的商品房可供选择。购车时允许分期付款，但必须支付不少于一定比例（如3成）的首付，其余部分可在15年的时间内分月付清，年利率为9.5%，但有可能在8-11%范围内变化。 试构建一个EXCEL模型来计算采
2、用各种购房方案时的月付款额以便可以综合考虑各种因素从而根据自己的承受能力决定一种最合适的购房方案。,基础知识：如何使用控件,一个简单的例子 建立方案列表 建立手工输入模型 使用控件 EXCEL中的财务函数,基础知识：如何使用控件,一个简单的例子 建立方案列表 建立手工输入模型 使用控件 EXCEL中的财务函数 (例子),9.1 EXCEL与财务预测,一、财务预测的目的 预测企业未来的融资需求 预测不准的损失 二、财务预测的步骤 销售预测 资产总量预测 收入、费用与盈余预测 融资预测,9.1 EXCEL与财务预测,三、财务预测的方法 *销售百分比法：总额预测，增加额预测 回归分析法 现金预算法
3、四、EXCEL财务预测 公司下年预计销售收入3000万元，上年销售额2500万元，净利150万元，股利支付率为30%，根据公司本年资产负债表进行财务预测，确定融资需求,EXCEL财务预测,1、建立融资需求表 2、确定销售百分比 3、计算预计销售额下的预计资产负债额 4、预计留存收益增加额和预计留存收益额 预计留存收益=上年留存收益+预计新增收益 新增留存收益=预计销售额*计划销售净利（本年）*（1-股利支付率） 融资需求=预计总资产-预计总负债-预计权益 5、计算外部融资需求,EXCEL财务预测,EXCEL财务预测,类似的问题 销售预测 日常现金需求量预测 成本费用预测 利润预测 ,9.2 E
4、XCEL与债券估价,一、财务估价与债券估价 估价是财务管理的核心 用适当的折现率计算的资产预期未来现金流量的现值 债券估价：债券到期日所支付的款项的现值，取决于当前利率和现金流量的风险水平 二、债券估价的基本模型 债券价值:PV 利息:I 到期本金:M 贴现率:i 到期前年数：n,9.2 EXCEL与债券估价,三、债券估价的方法 计算债券利息现值和 调整相关数据 动态计算 四、EXCEL财务预测 公司拟于2008年2月1日发行面额为1000元的债券，票面利率8%，每年2月1日计算并支付一次利息，并于5年后的1月31日到期。同等风险投资的必要报酬率为10%，试计算债券的价值。,9.2 EXCEL
5、与债券估价,1、建立债券估价表 2、计算债券利息现值 3、调整相关数据 4、债券价值动态计算,债券估价,EXCEL与估价,类似的问题 购房的分期付款问题 存款的复利问题 年金的现值问题 贷款利率中途调整问题 国库券的投资计算,9.3 EXCEL与存货管理,一、存货管理的目标 保证生产和销售和正常供应 总成本最低：取得成本与储存成本最低 二、存货成本 取得成本=订货成本+购置成本 订货成本=固定成本F1(次数无关)+变动成本K（次数相关） 购置成本=年需要量D*单价U 若每次订货量为Q，则取得成本为：F1+D/Q*K+D*U,9.3 EXCEL与存货管理,二、存货成本 储存成本：为保存存货而发生
6、的费用，也可分为固定成本和变动成本。 固定成本与数量无关，用F2表示 变动成本与数量有关，单位成本用Kc表示，则存货成本为：F2+Kc*Q/2 其中Q/2表示本年的平均存货,9.3 EXCEL与存货管理,三、经济订货批量 存货总成本： TC= F1+D/Q*K+D*U+F2+Kc*Q/2 当F1、K、D、F2、Kc为常数时，TC的大小取决于Q，求导可得经济订货批量：Q=sqrt(2*K*D/Kc) Sqrt()为开平方函数,9.3 EXCEL与存货管理,三、经济订货批量 经济订货批量： Q=sqrt(2*K*D/Kc) 年最佳订货次数 N=D/Q=sqrt(D*Kc/2*K) 最佳订货周期：
7、T=1/N=1/sqrt(D*Kc/2*K) 批量相关总成本 TC（Q）=SQRT（2*K*D*Kc） 经济订货量占用资金 I=Q/2*U=sqrt(D*K/2*Kc)*U,EXCEL经济订货批量模型,1、建立经济订货批量模型表 2、计算最佳订货批量 3、计算其它参数 4、模型的进一步扩展,EXCEL经济订货批量模型,类似的问题 固定成本、可变成本总成本最小问题 量本利 固定需求情况下2种商品的最优订货策略 固定订货周期库存管理策略 固定订货数量库存管理策略,经济订货批量,9.4 EXCEL与短期筹资,一、短期筹资的特点 资金可使用时间较短，一般不超过一年 筹资速度快，容易取得，成本低，风险高
8、 二、短期筹资的主要形式 商业信用：应付账款、应付票据、预收账款 应付账款做为企业最常用的短期筹资工具，如何建立模型进行管理 其余的方法类似,9.4 EXCEL与短期筹资,三、应付账款的决策模型 免费信用：规定折扣期内享受折扣 有代价信用：放弃折扣获得的信用 展期信用：推迟付款获得的信用 决策依据：权衡放弃现金折扣的成本 现金折扣成本=折扣百分比/(1-折扣百分比）*一年的天数/（信用期-折扣期）,9.4 EXCEL与短期筹资,四、EXCEL模型的构建 公司按2/10,N/30的条件购入货物500万元，如果企业在10天内付账，则享受10天的免费信用期，并获得2%的折扣，若企业放弃折扣，在10天
9、后（30天以内）付款，企业便要承担因放弃折扣而造成的隐含利息成本，试运用EXCEL进行付账决策分析。,9.4 EXCEL与短期筹资,五、EXCEL模型的步骤 1、创建应付账款成本决策分析已知数据表 2、根据应付账款成本决策模型计算放弃折扣成本 现金折扣成本=折扣百分比/(1-折扣百分比*一年的天数/（信用期-折扣期）,短期筹资决策,类似的问题,债券购买 信用条件与企业利润 信用条件与销售收入,9.5 EXCEL与最佳资本结构,一、最佳资本结构 企业各种长期资金筹资来源的构成和比例关系。最佳资本结构应当是可使公司的总价值最大，资本成本最低的结构。 二、最佳资本结构模型 公司市场总价值等于其股票的
10、总价值与债券的总价值之和，V=B+S 假设债券价值等于其面值，则股票的市场价格为： S=（EBIT-I）*(1-T)/Ks,9.5 EXCEL与最佳资本结构,二、最佳资本结构模型 S=（EBIT-I）*(1-T)/Ks Ks=Rs=Rf+b(Rm-Rf) EBIT：息税前利润 I：年利息额 T：所得税率 Ks:权益资本成本 Rf:无风险报酬 b:股票贝塔系数 Rm:必要报酬率,公司资本成本用加权平均资本Kw表示： Kw=Kb(B/S)(1-T)+Ks(S/V),9.5 EXCEL与最佳资本结构,三、EXCEL模型的构建 公司年息税前利润500万元，资金全部由普通股资本构成，股票账面价值200万
11、元，所得税率33%。该公司认为目前的资本结构不太合理，拟发行债券回购部分股票，经调查，目前的债券等量资本成本和权益成本情况如下表：,9.5 EXCEL与最佳资本结构,三、EXCEL模型的构建 1、建立已知条件表 2、计算股票资本成本Ks 3、建立公司价值与资本成本表 4、计算发行不同债券B下的市场价值S与公司价值V，依据Kb与Ks，计算Kw 5、从Kw值中选择最低值，对应的B为最佳,短期筹资决策,类似的问题,股票发行价格的计算 财务杠杆 资本成本 经营杠杆 每股盈余 ,9.6 EXCEL与量本利分析,一、量本利分析概述 成本有不同的形态：固定成本、变动成本、总成本。 加入收入和利润，就可以构建
12、量本利数学模型 总成本=变动成本+固定成本=单位变动成本*销量+固定成本 销售收入=单价*销量 利润=销售收入-总成本=（单价-单位变动成本）*销量-固定成本 保本量=固定成本/(单价-单位变动成本) 保利量=（固定成本+利润） /(单价-单位变动成本) 单位变动成本=单价-（固定成本+利润）/销量 固定成本=（单价-单位变动成本）*销量-利润,9.6 EXCEL与量本利分析,二、EXCEL模型的构建 公司月固定成本10000元，单价50元，单位变动成本30元，试用EXCEL构建量本利模型。 1、建立已知数据表格 2、计算保本量、保利量 3、单变量求解保本量、保利量下的单价、变动成本与固定成本
13、 4、制作图形,EXCEL与量本利分析,类似的问题,产量确定情况下的定价 变动成本与利润 固定成本与利润 销售单价确定情况下的保利量计算 成本性态分析 ,9.7 EXCEL与设备投资决策,一、设备投资决策概述 设备购置项目评价 多个购置项目的比较 设备的租买比较,9.7 EXCEL与设备投资决策,二、EXCEL模型的构建 公司拟增加一台机器，用以生产一种 新的产品，有三种设备可供选择，每种 设备原始资料如表9-3，用EXCEL建模分析在只考虑净现值时如何决策。 1、建立已知数据表格 2、计算三种设备的市场收益 3、计算三种设备投资的净现值 4、动态决策 4、制作图形,9.7 EXCEL与设备投
14、资决策,类似的问题,项目投资评价 设备租、购比较 不同设备购买比较 ,9.8 EXCEL与人力资源管理和优化,一、人力资源管理概述 为完成即定生产计划，将劳动力合理分配到生产的各个环节上。 使用EXCEL的线性规划功能，解决约束条件下的最大化问题。 二、一个实例 公司生产2种产品：产品1和产品2，每件产品分别可获利润100元和200元，生产2种产品均需要3道工序，分别由部门1、2、3完成，其中每道工序所需人数和每个部门可提供的人数如下表。问该厂应生产多少产品1和产品2才能在满足各部门人力资源的约束条件下获得最大利润？,9.8 EXCEL与人力资源管理和优化,1、人力资源模型构建 设定决策变量：
15、2种产品的产量，x1,x2 构建目标利润函数： 获得最大利润：MAX Y=100x1+200x2 构建约束条件：各部门人力资源约束 0.6x1+1x2=0,int,9.8 EXCEL与人力资源管理和优化,2、EXCEL建模 产品与工序人力资源对照表 三部门人力资源约束表 人力资源优化配置表 3、规划求解 规划求解参数设置 4、模型求解结果分析 获利最大的X1与X2 提高效率的瓶颈 工种转换 教育与培训,9.8 EXCEL与人力资源管理和优化,5、人力资源优化模型 添加决策变量：Xij从i部门转到j部门的职工数,建立部门间人员调配表 构建优化目标函数：考虑培训费的利润 Max y=100x1+2
16、00x2-50*(x12+x13+x21+x23+x31+x32) 构建优化约束条件：考虑人工转移 0.6x1+1x2=0,x13,x21,x23,x31,x32=0,int,EXCEL中的线性规划求解,EXCEL中的线性规划求解,EXCEL中的线性规划求解,类似的问题,约束条件下的极大极小值 ,第三次作业要求,1、选择一个会计相关的项目，在EXCEL中建立模型； 如：量本利分析、投资项目评价、杜邦分析、购房的分期付款问题 存款的复利问题、年金的现值问题、贷款利率中途调整问题、 国库券的投资计算、债券购买、 。 要求能根据已知数据，按该方法的计算要求自动计算本关的指标 2、界面美观，自动化程度
17、高； 3、使用下拉列表框、微调框等控件 4、如果需要请使用可调图形 5、制作完成后发到我的邮箱：,文件命名和邮箱主题按发送报表时的方式。 6、21周周三为最后期限。,第三次作业要求,例：用excel的规划求解计算的求最佳方案的题目 一个厂家，在一段时间内生产两种产品，分别是A，B产品。受原材料的限制，产品A的产量不超过4000元，产品B的产量不超过4000元。受机器和人的限制，两种产品之和不超过7000元。产品A的单价利润为166元，每多生产100件，单价利润增加2元，产品B的单价利润为190元，每多生产100件，单价利润增加3元。现在进行规划求解，求出产品A和产品B的产量的最佳方案，使利润最大.,