EXCEL与财务分析课件

EXCEL财务分析

基础知识：如何使用控件例1：某人准备购买一套住房，现有若干面积、楼层和位置的商品房可供选择。购车时允许分期付款，但必须支付不少于一定比例（如3成）的首付，其余部分可在15年的时间内分月付清，年利率为9.5%，但有可能在8-11%范围内变化。试构建一个EXCEL模型来计算采用各种购房方案时的月付款额以便可以综合考虑各种因素从而根据自己的承受能力决定一种最合适的购房方案。基础知识：如何使用控件一个简单的例子建立方案列表建立手工输入模型使用控件EXCEL中的财务函数基础知识：如何使用控件一个简单的例子建立方案列表建立手工输入模型使用控件EXCEL中的财务函数(例子)9.1EXCEL与财务预测一、财务预测的目的预测企业未来的融资需求预测不准的损失二、财务预测的步骤销售预测资产总量预测收入、费用与盈余预测融资预测9.1EXCEL与财务预测三、财务预测的方法*销售百分比法：总额预测，增加额预测回归分析法现金预算法四、EXCEL财务预测公司下年预计销售收入3000万元，上年销售额2500万元，净利150万元，股利支付率为30%，根据公司本年资产负债表进行财务预测，确定融资需求EXCEL财务预测1、建立融资需求表2、确定销售百分比3、计算预计销售额下的预计资产负债额4、预计留存收益增加额和预计留存收益额预计留存收益=上年留存收益+预计新增收益新增留存收益=预计销售额*计划销售净利（本年）*（1-股利支付率）融资需求=预计总资产-预计总负债-预计权益5、计算外部融资需求EXCEL财务预测EXCEL财务预测类似的问题销售预测日常现金需求量预测成本费用预测利润预测……9.2EXCEL与债券估价一、财务估价与债券估价估价是财务管理的核心用适当的折现率计算的资产预期未来现金流量的现值债券估价：债券到期日所支付的款项的现值，取决于当前利率和现金流量的风险水平二、债券估价的基本模型债券价值:PV利息:I到期本金:M贴现率:i到期前年数：n9.2EXCEL与债券估价三、债券估价的方法计算债券利息现值和调整相关数据动态计算四、EXCEL财务预测公司拟于2008年2月1日发行面额为1000元的债券，票面利率8%，每年2月1日计算并支付一次利息，并于5年后的1月31日到期。同等风险投资的必要报酬率为10%，试计算债券的价值。9.2EXCEL与债券估价1、建立债券估价表2、计算债券利息现值3、调整相关数据4、债券价值动态计算债券估价EXCEL与估价类似的问题购房的分期付款问题存款的复利问题年金的现值问题贷款利率中途调整问题国库券的投资计算……9.3EXCEL与存货管理一、存货管理的目标保证生产和销售和正常供应总成本最低：取得成本与储存成本最低二、存货成本取得成本=订货成本+购置成本订货成本=固定成本F1(次数无关)+变动成本K（次数相关）购置成本=年需要量D*单价U若每次订货量为Q，则取得成本为：F1+D/Q*K+D*U9.3EXCEL与存货管理二、存货成本储存成本：为保存存货而发生的费用，也可分为固定成本和变动成本。固定成本与数量无关，用F2表示变动成本与数量有关，单位成本用Kc表示，则存货成本为：F2+Kc*Q/2其中Q/2表示本年的平均存货9.3EXCEL与存货管理三、经济订货批量存货总成本：

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.3EXCEL与存货管理三、经济订货批量经济订货批量：Q=sqrt(2*K*D/Kc)年最佳订货次数N=D/Q=sqrt(D*Kc/2*K)最佳订货周期：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)*UEXCEL经济订货批量模型1、建立经济订货批量模型表2、计算最佳订货批量3、计算其它参数4、模型的进一步扩展EXCEL经济订货批量模型类似的问题固定成本、可变成本总成本最小问题量本利固定需求情况下2种商品的最优订货策略固定订货周期库存管理策略固定订货数量库存管理策略经济订货批量9.4EXCEL与短期筹资一、短期筹资的特点资金可使用时间较短，一般不超过一年筹资速度快，容易取得，成本低，风险高二、短期筹资的主要形式商业信用：应付账款、应付票据、预收账款应付账款做为企业最常用的短期筹资工具，如何建立模型进行管理其余的方法类似9.4EXCEL与短期筹资三、应付账款的决策模型免费信用：规定折扣期内享受折扣有代价信用：放弃折扣获得的信用展期信用：推迟付款获得的信用决策依据：权衡放弃现金折扣的成本现金折扣成本=折扣百分比/(1-折扣百分比）*一年的天数/（信用期-折扣期）9.4EXCEL与短期筹资四、EXCEL模型的构建公司按2/10,N/30的条件购入货物500万元，如果企业在10天内付账，则享受10天的免费信用期，并获得2%的折扣，若企业放弃折扣，在10天后（30天以内）付款，企业便要承担因放弃折扣而造成的隐含利息成本，试运用EXCEL进行付账决策分析。9.4EXCEL与短期筹资五、EXCEL模型的步骤1、创建应付账款成本决策分析已知数据表2、根据应付账款成本决策模型计算放弃折扣成本现金折扣成本=折扣百分比/(1-折扣百分比*一年的天数/（信用期-折扣期）短期筹资决策类似的问题债券购买信用条件与企业利润信用条件与销售收入9.5EXCEL与最佳资本结构一、最佳资本结构企业各种长期资金筹资来源的构成和比例关系。最佳资本结构应当是可使公司的总价值最大，资本成本最低的结构。二、最佳资本结构模型公司市场总价值等于其股票的总价值与债券的总价值之和，V=B+S假设债券价值等于其面值，则股票的市场价格为：S=（EBIT-I）*(1-T)/Ks9.5EXCEL与最佳资本结构二、最佳资本结构模型S=（EBIT-I）*(1-T)/KsKs=Rs=Rf+b(Rm-Rf)EBIT：息税前利润I：年利息额T：所得税率Ks:权益资本成本Rf:无风险报酬b:股票贝塔系数Rm:必要报酬率公司资本成本用加权平均资本Kw表示：Kw=Kb(B/S)(1-T)+Ks(S/V)9.5EXCEL与最佳资本结构三、EXCEL模型的构建公司年息税前利润500万元，资金全部由普通股资本构成，股票账面价值200万元，所得税率33%。该公司认为目前的资本结构不太合理，拟发行债券回购部分股票，经调查，目前的债券等量资本成本和权益成本情况如下表：9.5EXCEL与最佳资本结构三、EXCEL模型的构建1、建立已知条件表2、计算股票资本成本Ks3、建立公司价值与资本成本表4、计算发行不同债券B下的市场价值S与公司价值V，依据Kb与Ks，计算Kw5、从Kw值中选择最低值，对应的B为最佳短期筹资决策类似的问题股票发行价格的计算财务杠杆资本成本经营杠杆每股盈余……9.6EXCEL与量本利分析一、量本利分析概述成本有不同的形态：固定成本、变动成本、总成本。加入收入和利润，就可以构建量本利数学模型总成本=变动成本+固定成本=单位变动成本*销量+固定成本销售收入=单价*销量利润=销售收入-总成本=（单价-单位变动成本）*销量-固定成本保本量=固定成本/(单价-单位变动成本)保利量=（固定成本+利润）/(单价-单位变动成本)单位变动成本=单价-（固定成本+利润）/销量固定成本=（单价-单位变动成本）*销量-利润9.6EXCEL与量本利分析二、EXCEL模型的构建公司月固定成本10000元，单价50元，单位变动成本30元，试用EXCEL构建量本利模型。1、建立已知数据表格2、计算保本量、保利量3、单变量求解保本量、保利量下的单价、变动成本与固定成本4、制作图形EXCEL与量本利分析类似的问题产量确定情况下的定价变动成本与利润固定成本与利润销售单价确定情况下的保利量计算成本性态分析……9.7EXCEL与设备投资决策一、设备投资决策概述设备购置项目评价多个购置项目的比较设备的租买比较9.7EXCEL与设备投资决策二、EXCEL模型的构建公司拟增加一台机器，用以生产一种新的产品，有三种设备可供选择，每种设备原始资料如表9-3，用EXCEL建模分析在只考虑净现值时如何决策。1、建立已知数据表格2、计算三种设备的市场收益3、计算三种设备投资的净现值4、动态决策4、制作图形9.7EXCEL与设备投资决策类似的问题项目投资评价设备租、购比较不同设备购买比较……9.8EXCEL与人力资源管理和优化一、人力资源管理概述为完成即定生产计划，将劳动力合理分配到生产的各个环节上。使用EXCEL的线性规划功能，解决约束条件下的最大化问题。二、一个实例公司生产2种产品：产品1和产品2，每件产品分别可获利润100元和200元，生产2种产品均需要3道工序，分别由部门1、2、3完成，其中每道工序所需人数和每个部门可提供的人数如下表。问该厂应生产多少产品1和产品2才能在满足各部门人力资源的约束条件下获得最大利润？9.8EXCEL与人力资源管理和优化1、人力资源模型构建设定决策变量：2种产品的产量，x1,x2构建目标利润函数：获得最大利润：MAXY=100x1+200x2构建约束条件：各部门人力资源约束0.6x1+1x2<=1500.5x1+0.7x2<=750.6x1+0.7x2<=78X1,x2>=0,int9.8EXCEL与人力资源管理和优化2、EXCEL建模产品与工序人力资源对照表三部门人力资源约束表人力资源优化配置表3、规划求解规划求解参数设置4、模型求解结果分析获利最大的X1与X2提高效率的瓶颈工种转换教育与培训9.8EXCEL与人力资源管理和优化5、人力资源优化模型添加决策变量：Xij—从i部门转到j部门的职工数,建立部门间人员调配表构建优化目标函数：考虑培训费的利润Maxy=100x1+200x2-50*(x12+x13+x21+x23+x31+x32)构建优化约束条件：考虑人工转移0.6x1+1x2<=150+(x31+x21)-(x12+x13)0.5x1+0.8X2<=75+(x12+x32)-(x21+x23)0.9x1+0.7x2<=78+(x23+x13)-(x31+x32)X12+x13<=30X21+x23<=5X31+x32<=15X1,x2>=0,x13,x21,x23,x31,x32>=0,intEXCEL中的线性规划求解EXCEL中的线性规划求解EXCEL中的线性规划求解

各部门人力资源优化结果

产品1(x1)产品2(x2)

生产量3979

利润19700

类似的问题约束条件下的极大极小值……第三次作业要求1、选择一个会计相关的项目，在EXCEL中建立模型；如：量本利分析、投资项目评价、杜邦分析、购房的分期付款问题存款的复利问题、年金的现值问题、贷款利率中途调整问题、国库券的投资计算、债券购买、。。。。。。要求能根据已知数据，按该方法的计算要求自动计算本关的指标2、界面美观，自动化程度高；3、使用下拉列表框、微调框等控件4、如果需要请使用可调图形5、制作完成后发到我的邮箱：westone@,文件命名和邮箱主题按