1、第十章 EXCEL财务分析会计电算化课程组2008.08第十章 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与人力资源管理基础知识：如何使用控件n n例例1 1：某人准备购买一套住房，现有若干：某人准备购买一套住房，现有若干面积、楼层和位置的商品房可供选择。面积、楼层和位置的商品房可供选择。购车时允许分期付款，但必须支付不少购车时允许分期付款，但必须支付不少于一定比例（如于一定
2、比例（如3 3成）的首付，其余部分成）的首付，其余部分可在可在1515年的时间内分月付清，年利率为年的时间内分月付清，年利率为9.5%9.5%，但有可能在，但有可能在8-11%8-11%范围内变化。范围内变化。n n试构建一个试构建一个EXCELEXCEL模型来计算采用各种模型来计算采用各种购房方案时的月付款额以便可以综合考购房方案时的月付款额以便可以综合考虑各种因素从而根据自己的承受能力决虑各种因素从而根据自己的承受能力决定一种最合适的购房方案。定一种最合适的购房方案。基础知识：如何使用控件n n一个简单的例子一个简单的例子uu建立方案列表建立方案列表uu建立手工输入模型建立手工输入模型uu
3、使用控件使用控件uuEXCELEXCEL中的财务函数中的财务函数基础知识：如何使用控件n n一个简单的例子一个简单的例子uu建立方案列表建立方案列表uu建立手工输入模型建立手工输入模型uu使用控件使用控件uuEXCELEXCEL中的财务函数中的财务函数t t( (例子例子) )9.1 EXCEL与财务预测n n一、财务预测的目的一、财务预测的目的uu预测企业未来的融资需求预测企业未来的融资需求uu预测不准的损失预测不准的损失n n二、财务预测的步骤二、财务预测的步骤uu销售预测销售预测uu资产总量预测资产总量预测uu收入、费用与盈余预测收入、费用与盈余预测uu融资预测融资预测9.1 EXCEL
4、与财务预测n n三、财务预测的方法三、财务预测的方法uu* *销售百分比法：总额预测，增加额预测销售百分比法：总额预测，增加额预测uu回归分析法回归分析法uu现金预算法现金预算法n n四、四、EXCELEXCEL财务预测财务预测uu公司下年预计销售收入公司下年预计销售收入30003000万元，上年销售额万元，上年销售额25002500万元，净利万元，净利150150万元，股利支付率为万元，股利支付率为30%30%，根据公，根据公司本年资产负债表进行财务预测，确定融资需求司本年资产负债表进行财务预测，确定融资需求EXCEL财务预测n n1 1、建立融资需求表、建立融资需求表n n2 2、确定销售
5、百分比、确定销售百分比n n3 3、计算预计销售额下的预计资产负债额、计算预计销售额下的预计资产负债额n n4 4、预计留存收益增加额和预计留存收益额、预计留存收益增加额和预计留存收益额uu预计留存收益预计留存收益= =上年留存收益上年留存收益+ +预计新增收益预计新增收益uu新增留存收益新增留存收益= =预计销售额预计销售额* *计划销售净利（本年）计划销售净利（本年）* *（1-1-股利支付率）股利支付率）uu融资需求融资需求= =预计总资产预计总资产- -预计总负债预计总负债- -预计权益预计权益n n5 5、计算外部融资需求、计算外部融资需求EXCEL财务预测EXCEL财务预测n n类
6、似的问题类似的问题uu销售预测销售预测uu日常现金需求量预测日常现金需求量预测uu成本费用预测成本费用预测uu利润预测利润预测uu9.2 EXCEL与债券估价n n一、财务估价与债券估价一、财务估价与债券估价uu估价是财务管理的核心估价是财务管理的核心uu用适当的折现率计算的资产预期未来现金流量的现值用适当的折现率计算的资产预期未来现金流量的现值uu债券估价：债券到期日所支付的款项的现值，取决于当前利率和现债券估价：债券到期日所支付的款项的现值，取决于当前利率和现金流量的风险水平金流量的风险水平n n二、债券估价的基本模型二、债券估价的基本模型uu债券价值债券价值:PV:PVuu利息利息:I
7、:Iuu到期本金到期本金:M:Muu贴现率贴现率:i :iuu到期前年数：到期前年数：n n9.2 EXCEL与债券估价n n三、债券估价的方法三、债券估价的方法uu计算债券利息现值和计算债券利息现值和uu调整相关数据调整相关数据uu动态计算动态计算n n四、四、EXCELEXCEL财务预测财务预测uu公司拟于公司拟于20082008年年2 2月月1 1日发行面额为日发行面额为10001000元的债券，元的债券，票面利率票面利率8%8%，每年，每年2 2月月1 1日计算并支付一次利息，日计算并支付一次利息，并于并于5 5年后的年后的1 1月月3131日到期。同等风险投资的必要报日到期。同等风险
8、投资的必要报酬率为酬率为10%10%，试计算债券的价值。，试计算债券的价值。9.2 EXCEL与债券估价n n1 1、建立债券估价表、建立债券估价表n n2 2、计算债券利息现值、计算债券利息现值n n3 3、调整相关数据、调整相关数据n n4 4、债券价值动态计算、债券价值动态计算债券估价EXCEL与估价n n类似的问题类似的问题uu购房的分期付款问题购房的分期付款问题uu存款的复利问题存款的复利问题uu年金的现值问题年金的现值问题uu贷款利率中途调整问题贷款利率中途调整问题uu国库券的投资计算国库券的投资计算9.3 EXCEL与存货管理n n一、存货管理的目标一、存货管理的目标uu保证生产
9、和销售和正常供应保证生产和销售和正常供应uu总成本最低：取得成本与储存成本最低总成本最低：取得成本与储存成本最低n n二、存货成本二、存货成本uu取得成本取得成本= =订货成本订货成本+ +购置成本购置成本t t订货成本订货成本= =固定成本固定成本F1(F1(次数无关次数无关)+)+变动成本变动成本KK（次数相关）次数相关）t t购置成本购置成本= =年需要量年需要量D*D*单价单价U Ut t若每次订货量为若每次订货量为QQ，则取得成本为：，则取得成本为：F1+D/Q*K+D*UF1+D/Q*K+D*U9.3 EXCEL与存货管理n n二、存货成本二、存货成本uu储存成本：为保存存货而发生
10、的费用，也可储存成本：为保存存货而发生的费用，也可分为固定成本和变动成本。分为固定成本和变动成本。t t固定成本与数量无关，用固定成本与数量无关，用F2F2表示表示t t变动成本与数量有关，单位成本用变动成本与数量有关，单位成本用KcKc表表示，则存货成本为：示，则存货成本为：F2+Kc*Q/2F2+Kc*Q/2t t其中其中Q/2Q/2表示表示本年的平均存货本年的平均存货9.3 EXCEL与存货管理n n三、经济订货批量三、经济订货批量uu存货总成本：存货总成本：uu TC=TC= F1+D/Q*K+D*U+F1+D/Q*K+D*U+F2+Kc*Q/2F2+Kc*Q/2uu当当F1F1、KK
11、、D D、F2F2、KcKc为常数时，为常数时，TCTC的大的大小取决于小取决于QQ，求导可得经济订货批量：，求导可得经济订货批量：Q=sqrt(2*K*D/Q=sqrt(2*K*D/KcKc) )uuSqrtSqrt() ()为开平方函数为开平方函数9.3 EXCEL与存货管理n n三、经济订货批量三、经济订货批量uu经济订货批量：经济订货批量：t tQ=sqrt(2*K*D/Q=sqrt(2*K*D/KcKc) )uu年最佳订货次数年最佳订货次数t tN=D/Q=N=D/Q=sqrt(Dsqrt(D*Kc/2*K)*Kc/2*K)uu最佳订货周期：最佳订货周期：t tT=1/N=1/sqr
12、t(D*Kc/2*K)T=1/N=1/sqrt(D*Kc/2*K)uu批量相关总成本批量相关总成本t tTCTC（QQ）=SQRT=SQRT（2*K*D*2*K*D*KcKc）uu经济订货量占用资金经济订货量占用资金t tI=Q/2*U=I=Q/2*U=sqrt(Dsqrt(D*K/2*K/2*KcKc)*U)*UEXCEL经济订货批量模型n n1 1、建立经济订货批量模型表、建立经济订货批量模型表n n2 2、计算最佳订货批量、计算最佳订货批量n n3 3、计算其它参数、计算其它参数n n4 4、模型的进一步扩展、模型的进一步扩展EXCEL经济订货批量模型n n类似的问题类似的问题uu固定成
13、本、可变成本总成本最小问题固定成本、可变成本总成本最小问题uu量本利量本利uu固定需求情况下固定需求情况下2 2种商品的最优订货策略种商品的最优订货策略uu固定订货周期库存管理策略固定订货周期库存管理策略uu固定订货数量库存管理策略固定订货数量库存管理策略经济订货批量9.4 EXCEL与短期筹资n n一、短期筹资的特点一、短期筹资的特点uu资金可使用时间较短，一般不超过一年资金可使用时间较短，一般不超过一年uu筹资速度快，容易取得，成本低，风险高筹资速度快，容易取得，成本低，风险高n n二、短期筹资的主要形式二、短期筹资的主要形式uu商业信用：应付账款、应付票据、预收账款商业信用：应付账款、应
14、付票据、预收账款uu应付账款做为企业最常用的短期筹资工具，如何建应付账款做为企业最常用的短期筹资工具，如何建立模型进行管理立模型进行管理uu其余的方法类似其余的方法类似9.4 EXCEL与短期筹资n n三、应付账款的决策模型三、应付账款的决策模型uu免费信用：规定折扣期内享受折扣免费信用：规定折扣期内享受折扣uu有代价信用：放弃折扣获得的信用有代价信用：放弃折扣获得的信用uu展期信用：推迟付款获得的信用展期信用：推迟付款获得的信用uu决策依据：权衡放弃现金折扣的成本决策依据：权衡放弃现金折扣的成本uu现金折扣成本现金折扣成本= =折扣百分比折扣百分比/(1-/(1-折扣百分比）折扣百分比）*
15、*一年的天数一年的天数/ /（信用期（信用期- -折扣期）折扣期）9.4 EXCEL与短期筹资n n四、四、EXCELEXCEL模型的构建模型的构建uu公司按公司按2/10,N/302/10,N/30的条件购入货物的条件购入货物500500万元，万元，如果企业在如果企业在1010天内付账，则享受天内付账，则享受1010天的免费天的免费信用期，并获得信用期，并获得2%2%的折扣，若企业放弃折的折扣，若企业放弃折扣，在扣，在1010天后（天后（3030天以内）付款，企业便要天以内）付款，企业便要承担因放弃折扣而造成的隐含利息成本，试承担因放弃折扣而造成的隐含利息成本，试运用运用EXCELEXCEL
16、进行付账决策分析。进行付账决策分析。9.4 EXCEL与短期筹资n n五、五、EXCELEXCEL模型的步骤模型的步骤uu1 1、创建应付账款成本决策分析已知数、创建应付账款成本决策分析已知数据表据表uu2 2、根据应付账款成本决策模型计算放、根据应付账款成本决策模型计算放弃折扣成本弃折扣成本uu现金折扣成本现金折扣成本= =折扣百分比折扣百分比/(1-/(1-折扣百折扣百分比分比* *一年的天数一年的天数/ /（信用期（信用期- -折扣期）折扣期）短期筹资决策类似的问题n n债券购买债券购买n n信用条件与企业利润信用条件与企业利润n n信用条件与销售收入信用条件与销售收入9.5 EXCEL
17、与最佳资本结构n n一、最佳资本结构一、最佳资本结构uu企业各种长期资金筹资来源的构成和比例关系。最企业各种长期资金筹资来源的构成和比例关系。最佳资本结构应当是可使公司的总价值最大，资本成佳资本结构应当是可使公司的总价值最大，资本成本最低的结构。本最低的结构。n n二、最佳资本结构模型二、最佳资本结构模型uu公司市场总价值等于其股票的总价值与债券的总价公司市场总价值等于其股票的总价值与债券的总价值之和，值之和，V=B+SV=B+Suu假设债券价值等于其面值，则股票的市场价格为：假设债券价值等于其面值，则股票的市场价格为：uuS=S=（EBIT-IEBIT-I）* *(1-T)/Ks(1-T)/
18、Ks9.5 EXCEL与最佳资本结构n n二、最佳资本结构模型二、最佳资本结构模型uuS=S=（EBIT-IEBIT-I）* *(1-T)/Ks(1-T)/KsuuKs=Ks=RsRs= =Rf+b(Rm-RfRf+b(Rm-Rf) )uuEBITEBIT：息税前利润：息税前利润uuI I：年利息额：年利息额uuT T：所得税率：所得税率uuKs:Ks:权益资本成本权益资本成本uuRfRf: :无风险报酬无风险报酬uub:b:股票贝塔系数股票贝塔系数uuRmRm: :必要报酬率必要报酬率n n公司资本成本用加权平均公司资本成本用加权平均资本资本KwKw表示：表示：n nKwKw=Kb(B/S)
19、(1-=Kb(B/S)(1-T)+Ks(S/V)T)+Ks(S/V)9.5 EXCEL与最佳资本结构n n三、三、EXCELEXCEL模型的构建模型的构建uu公司年息税前利润公司年息税前利润500500万元，资金全部由普万元，资金全部由普通股资本构成，股票账面价值通股资本构成，股票账面价值200200万元，所万元，所得税率得税率33%33%。该公司认为目前的资本结构不。该公司认为目前的资本结构不太合理，拟发行债券回购部分股票，经调查太合理，拟发行债券回购部分股票，经调查，目前的债券等量资本成本和权益成本情况，目前的债券等量资本成本和权益成本情况如下表：如下表：9.5 EXCEL与最佳资本结构n
20、 n三、三、EXCELEXCEL模型的构建模型的构建uu1 1、建立已知条件表、建立已知条件表uu2 2、计算股票资本成本、计算股票资本成本KsKsuu3 3、建立公司价值与资本成本表、建立公司价值与资本成本表uu4 4、计算发行不同债券、计算发行不同债券B B下的市场价值下的市场价值S S与公与公司价值司价值V V，依据，依据KbKb与与KsKs，计算，计算KwKwuu5 5、从、从KwKw值中选择最低值，对应的值中选择最低值，对应的B B为最佳为最佳短期筹资决策类似的问题n n股票发行价格的计算股票发行价格的计算n n财务杠杆财务杠杆n n资本成本资本成本n n经营杠杆经营杠杆n n每股盈
21、余每股盈余n n9.6 EXCEL与量本利分析n n一、量本利分析概述一、量本利分析概述uu成本有不同的形态：固定成本、变动成本、总成本。成本有不同的形态：固定成本、变动成本、总成本。uu加入收入和利润，就可以构建量本利数学模型加入收入和利润，就可以构建量本利数学模型uu总成本总成本= =变动成本变动成本+ +固定成本固定成本= =单位变动成本单位变动成本* *销量销量+ +固定成本固定成本uu销售收入销售收入= =单价单价* *销量销量uu利润利润= =销售收入销售收入- -总成本总成本= =（单价（单价- -单位变动成本）单位变动成本）* *销量销量- -固定成本固定成本uu保本量保本量=
22、 =固定成本固定成本/( /(单价单价- -单位变动成本单位变动成本) )uu保利量保利量= =（固定成本（固定成本+ +利润）利润） /( /(单价单价- -单位变动成本单位变动成本) )uu单位变动成本单位变动成本= =单价单价- -（固定成本（固定成本+ +利润）利润）/ /销量销量uu固定成本固定成本= =（单价（单价- -单位变动成本）单位变动成本）* *销量销量- -利润利润9.6 EXCEL与量本利分析n n二、二、EXCELEXCEL模型的构建模型的构建uu公司月固定成本公司月固定成本1000010000元，单价元，单价5050元，单位变动成元，单位变动成本本3030元，试用元
23、，试用EXCELEXCEL构建量本利模型。构建量本利模型。uu1 1、建立已知数据表格、建立已知数据表格uu2 2、计算保本量、保利量、计算保本量、保利量uu3 3、单变量求解保本量、保利量下的单价、变动成、单变量求解保本量、保利量下的单价、变动成本与固定成本本与固定成本uu4 4、制作图形、制作图形EXCEL与量本利分析类似的问题n n产量确定情况下的定价产量确定情况下的定价n n变动成本与利润变动成本与利润n n固定成本与利润固定成本与利润n n销售单价确定情况下的保利量计算销售单价确定情况下的保利量计算n n成本性态分析成本性态分析n n9.7 EXCEL与设备投资决策n n一、设备投资
24、决策概述一、设备投资决策概述uu设备购置项目评价设备购置项目评价uu多个购置项目的比较多个购置项目的比较uu设备的租买比较设备的租买比较9.7 EXCEL与设备投资决策n n二、二、EXCELEXCEL模型的构建模型的构建uu公司拟增加一台机器，用以生产一种公司拟增加一台机器，用以生产一种 新的产品，有三种设备新的产品，有三种设备可供选择，每种可供选择，每种 设备原始资料如表设备原始资料如表9-39-3，用，用EXCELEXCEL建模分析建模分析在只考虑净现值时如何决策。在只考虑净现值时如何决策。uu1 1、建立已知数据表格、建立已知数据表格uu2 2、计算三种设备的市场收益、计算三种设备的市
25、场收益uu3 3、计算三种设备投资的净现值、计算三种设备投资的净现值uu4 4、动态决策、动态决策uu4 4、制作图形、制作图形9.7 EXCEL与设备投资决策类似的问题n n项目投资评价项目投资评价n n设备租、购比较设备租、购比较n n不同设备购买比较不同设备购买比较n n9.8 EXCEL与人力资源管理和优化n n一、人力资源管理概述一、人力资源管理概述uu为完成即定生产计划，将劳动力合理分配到生产的为完成即定生产计划，将劳动力合理分配到生产的各个环节上。各个环节上。uu使用使用EXCELEXCEL的线性规划功能，解决约束条件下的的线性规划功能，解决约束条件下的最大化问题。最大化问题。n
26、 n二、一个实例二、一个实例uu公司生产公司生产2 2种产品：产品种产品：产品1 1和产品和产品2 2，每件产品分别，每件产品分别可获利润可获利润100100元和元和200200元，生产元，生产2 2种产品均需要种产品均需要3 3道工道工序，分别由部门序，分别由部门1 1、2 2、3 3完成，其中每道工序所需完成，其中每道工序所需人数和每个部门可提供的人数如下表。问该厂应生人数和每个部门可提供的人数如下表。问该厂应生产多少产品产多少产品1 1和产品和产品2 2才能在满足各部门人力资源的才能在满足各部门人力资源的约束条件下获得最大利润？约束条件下获得最大利润？9.8 EXCEL与人力资源管理和优
27、化n n1 1、人力资源模型构建、人力资源模型构建uu设定决策变量：设定决策变量：2 2种产品的产量，种产品的产量，x1,x2x1,x2uu构建目标利润函数：构建目标利润函数：获得最大利润：获得最大利润：MAX Y=100 x1+200 x2MAX Y=100 x1+200 x2uu构建约束条件：各部门人力资源约束构建约束条件：各部门人力资源约束0.6x1+1x2=1500.6x1+1x2=1500.5x1+0.7x2=750.5x1+0.7x2=750.6x1+0.7x2=780.6x1+0.7x2=0,intX1,x2=0,int9.8 EXCEL与人力资源管理和优化n n2 2、EXCE
28、LEXCEL建模建模uu产品与工序人力资源对照表产品与工序人力资源对照表uu三部门人力资源约束表三部门人力资源约束表uu人力资源优化配置表人力资源优化配置表n n3 3、规划求解、规划求解uu规划求解参数设置规划求解参数设置n n4 4、模型求解结果分析、模型求解结果分析uu获利最大的获利最大的X1X1与与X2X2uu提高效率的瓶颈提高效率的瓶颈uu工种转换工种转换uu教育与培训教育与培训9.8 EXCEL与人力资源管理和优化n n5 5、人力资源优化模型、人力资源优化模型uu添加决策变量：添加决策变量：XijXij从从i i部门转到部门转到j j部门的职工数部门的职工数, ,建立部门间建立部
29、门间人员调配表人员调配表uu构建优化目标函数：考虑培训费的利润构建优化目标函数：考虑培训费的利润Max y=100 x1+200 x2-50*(x12+x13+x21+x23+x31+x32)Max y=100 x1+200 x2-50*(x12+x13+x21+x23+x31+x32)uu构建优化约束条件：考虑人工转移构建优化约束条件：考虑人工转移0.6x1+1x2=150+(x31+x21)-(x12+x13)0.6x1+1x2=150+(x31+x21)-(x12+x13)0.5x1+0.8X2=75+(x12+x32)-(x21+x23)0.5x1+0.8X2=75+(x12+x32)
30、-(x21+x23)0.9x1+0.7x2=78+(x23+x13)-(x31+x32)0.9x1+0.7x2=78+(x23+x13)-(x31+x32)X12+x13=30X12+x13=30X21+x23=5X21+x23=5X31+x32=15X31+x32=0,x13,x21,x23,x31,x32=0,intX1,x2=0,x13,x21,x23,x31,x32=0,intEXCEL中的线性规划求解EXCEL中的线性规划求解EXCEL中的线性规划求解各部人力源化果品1(x1)品2(x2)生量3979利19700类似的问题n n约束条件下的极大极小值约束条件下的极大极小值n n第三次
31、作业要求第三次作业要求n n1 1、选择一个会计相关的项目，在、选择一个会计相关的项目，在EXCELEXCEL中建立模型；中建立模型；uu如：量本利分析、投资项目评价、杜邦分析、购房的分期付款问题如：量本利分析、投资项目评价、杜邦分析、购房的分期付款问题uu存款的复利问题、年金的现值问题、贷款利率中途调整问题、存款的复利问题、年金的现值问题、贷款利率中途调整问题、uu国库券的投资计算、债券购买、国库券的投资计算、债券购买、uu。uu要求能根据已知数据，按该方法的计算要求自动计算本关的指标要求能根据已知数据，按该方法的计算要求自动计算本关的指标n n2 2、界面美观，自动化程度高；、界面美观，自
32、动化程度高；n n3 3、使用下拉列表框、微调框等控件、使用下拉列表框、微调框等控件n n4 4、如果需要请使用可调图形、如果需要请使用可调图形n n5 5、制作完成后发到我的邮箱：、制作完成后发到我的邮箱：, ,文件命名和邮箱主文件命名和邮箱主题按发送报表时的方式。题按发送报表时的方式。n n6 6、2121周周三为最后期限。周周三为最后期限。 第三次作业要求第三次作业要求n例：用excel的规划求解计算的求最佳方案的题目n 一个厂家，在一段时间内生产两种产品，分别是A，B产品。受原材料的限制，产品A的产量不超过4000元，产品B的产量不超过4000元。受机器和人的限制，两种产品之和不超过7000元。产品A的单价利润为166元，每多生产100件，单价利润增加2元，产品B的单价利润为190元，每多生产100件，单价利润增加3元。现在进行规划求解，求出产品A和产品B的产量的最佳方案，使利润最大.The End ThanksThe End ThanksKeep in contact.Keep in 2008.122008.12
