实验指导书-《财务实验操作》

本学期考试时间为最后一周，到时候考场内同样有监考老师希望大家能够将每次课堂在EXCEL中做的练习保存到自己或同学的优盘中，以备将来考试复习使用考试时不允许携带任何书面和电子资料本课程需要使用的公式分为两类：一类是需要背诵的，考试时不会函数表达式，另外一类公式考试时会在EXCEL中列示，课程结束时会和大家指明《财务实验操作》实验指导书安徽科技学院财务会计系财务管理课程组编2008年2月目录：前言Excel基本操作实验一货币时间价值实验二企业筹资分析实验三筹资预测与决策实验四内部长期投资决策实验五投资决策中的风险分析实验六流动资产管理实验七利润管理参考教材：1、韩良智编著，《EXCEL在财务管理中的应用》，人民邮电出版社，2004年第一版2、杜茂康编著，EXCEL与数据处理，北京电子工业出版社，2002年第一版3、魏亚平尹均惠编著，财务管理实验教程，经济科学出版社，2005年6月第一版秒”。要输入逻辑型的数据，输入“true”(真)或“false”(假)即可。若数据由数字与小数点构成，Excel自动将其识别为数字型，Excel允许在数值型数据前加入货币符号，Excel将其视为货币数值型，Excel也允许数值型数据用科学记数法表示，如2╳109在Excel中可表示为2E+9。除了以上三种格式以外的输入数据，Excel将其视为字符型处理。（二）公式生成数据Excel的数据中也可由公式直接生成。例如：在当前工作表中A1和B1单元格中已输入了数值数据，欲将A1与B1单元格的数据相加的结果放入C1单元格中，可按如下步骤操作：用鼠标选定C1单元格，然后输入公式“=A1+B1”或输入“=SUM(a1:b1)”,回车之后即可完成操作。C1单元格此时存放实际上是一个数学公式“A1+B1”，因此C1单元格的数值将随着A1、B1单元格的数值的改变而变化。Excel提供了完整的算术运算符,如＋(加)－(减)*(乘)／(除)％(百分比)^(指数)和丰富的函数，如SUM(求和)、CORREL(求相关系数)、STDEV(求标准差)等，供用户对数据执行各种形式的计算操作，在（三）复制生成数据Excel中的数据也可由复制生成。实际上，在生成的数据具有相同的规律性的时候，大部分的数据可以由复制生成。可以在不同单元格之间复制数据，也可以在不同工作表或不同工作簿之间复制数据，可以一次复制一个数据，也可同时复制一批数据，为数据输入带来了极大的方便。普通单元格的复制结果与公式单元格的复制结果相差较大，下面分别予以说明。1．普通单元格指的是非公式的单元格。普通单元格的复制，一般可以按如下步骤进行：（1）拖动鼠标选定待复制的区域，选定之后该区域变为黑色。Excel可以进行整行、整列或整个表格的选定操作，例如，如果要选定表格的第一列，可直接用鼠标单击列标“A”，如果要选定表格的第一行，可直接用鼠标单击行标“1”，如果要选定整个表格，可直接点击全选按扭，如附图-2所示：附图-2（2）选定完区域之后，用鼠标右击该区域，选择“复制”，将区域内容复制到粘贴版之中。可以发现该区域已被虚线包围。（3）用鼠标右击目标区域，选择“粘贴”，则单元格区域的复制即告完成。2．公式单元格的复制，一般可分为两种，一种是值复制，一种是公式复制。值复制指的是只复制公式的计算结果到目标区域，公式复制指的是仅复制公式本身到目标区域。下面对它们的操作步骤分别予以说明。（1）值复制：①拖动鼠标选定待复制区域。②用鼠标右击选定区域，选择“复制”选项。③用鼠标右击目标区域，再单击“选择性粘贴”子菜单。出现复制选项，选定“数值”选项，然后用鼠标单击“确定”按扭，则公式的值复制即告完成。（2）公式复制：公式复制是Excel数据成批计算的重要操作方法，要熟练公式复制的操作首先要区分好两个概念：单元格的相对引用与绝对引用。Excel中的公式中一般都会引用到别的单元格的数值，如果你希望当公式复制到别的区域之时，公式引用单元格不会随之相对变动，那么你必须在公式中使用单元格的绝对引用。如果你希望当公式复制到别的区域之时，公式引用单元格也会随之相对变动，那么你必须在公式中使用单元格的相对引用。在公式中如果直接输入单元格的地址，那么默认的是相对引用单元格，如果在单元格的地址之前加入“$”符号那么意味着绝对引用单元格。例如，在当前工作表中A1和B1单元格中已输入了数值数据，用鼠标选定C1单元格，然后输入公式“=A1+B1”,此公式引用的便是两个相对的单元格A1、B1,也就是说如果将该公式复制到C2的单元格，公式所引用的单元格的地址将随着发生变化，公式将变为“=A2+B2”，如果将该公式复制到F100的单元格,那么公式将变为“=D100+E100”，这就是相对引用的结果，公式的内容随着公式的位置变化而相对变化。如果在C1单元格输入的是“=$A$1+$B$1”那么此公式引用的便是绝对的单元格，不论将公式复制到何处，公式的内容都不会发生变化。当然，绝对引用和相对引用亦可在同一公式之中混合交叉使用，例如，如果在C1单元中输入的是公式“=A$1+B$1”,那么意味着，公式的内容不会随着公式的垂直移动而变动，而是随着公式的水平移动而变动，如果将该公式复制到F100单元格，那么公式将变为，“=D$1+E$1”。可以作这样的归纳：公式中“$在实际的使用中，如果能把单元格的相对引用与绝对引用灵活应用到Excel的公式之中，能为数据成批准确运算带来极大的方便。四、数据的移动操作数据的移动操作可按如下步骤进行：（一）拖动鼠标选定待移动区域。（二）用鼠标右击选定区域，选择“剪切”选项。（三）用鼠标右击目标区域，选择“粘贴”，则单元格区域的移动即告完成。与数据的复制操作不同，公式单元格的移动操作不存在值移动或公式移动的区别，也不存在绝对引用已相对引用的区别，移动操作将把公式单元格的公式内容原原本本移动到目标区域，不作任何改动。五、数据的删除操作数据的删除操作可按如下步骤进行：（一）拖动鼠标选定待删除区域。（二）用鼠标右击选定区域，选择“删除”，即可删除单元格区域的内容。如果不小心删除了不该删除的区域，可以通过“编辑”菜单的“撤消”命令来恢复被删除的内容。“撤消”操作是Excel中较常用到的操作，如果不小心实施了错误的操作，那么可以通过“撤消”操作使工作表恢复原样。六、与其它软件交换数据的方法在Excel中可以打开其它类型的数据文件，如FOXPRO系列的DBF数据库文件，文本文件，lotus1-2-3的数据文件等。具体操作方法如下：（一）在“文件”菜单中选择“打开”子菜单。（二）在“打开文件”对话框中选择所要打开的文件的类型及其所在的目录。（三）用鼠标双击该文件名，并按Excel提示步骤操作即可打开该文件。Excel文件同样也可存为其它类型的数据文件，具体操作方法如下：（一）编辑好文件后，在“文件”菜单中选择“另存为”子菜单。（二）在“另存为”对话框中选择所要打开文件的类型及其所在的目录。（三）输入文件名之后，用鼠标单击“保存”按扭即可。实验一货币时间价值一、实验目的通过实验使学生理解货币时间价值；利用EXCEL函数FV、PV及电子表格软件，熟悉并掌握货币时间价值，包括复利终值、复利现值、年金终值、年金现值、永续年金、递延年金、期数和贴现率的计算。二、实验原理运用筹资、投资管理中的终值、现值的概念，结合EXCEL函数公式，计算复利终值、复利现值、年金终值、年金现值、永续年金、递延年金、期数和贴现率。复利现值=pv(rate,nper,0,-fv)复利终值=fv(rate,nper,0,-pv)年金现值=pv(rate,nper,-pmt,0)年金终值=FV(rate,nper,-pmt,0)期数=NPER(RATE,,-PV,FV)复利终值=NPER(RATE,PMT,-PV)年金现值练习6=-NPER(RATE,PMT,FV)年金终值贴现率=RATE(NPER,,-PV,FV)复利终值=RATE(NPER,PMT,-PV)年金现值三、实验资料1.终值和现值的计算某人5年后要想从银行取出10000元，年利率6%，按复利计算，则现在应该存入银行的现金是多少？若某人现在存入银行10000元，年利率6%，按复利计算，则5年后复利终值是多少？若有一笔年金，分期支付，每期支付1000元，年利率6%，则这笔年金的现值是多少？若有一笔年金，分期支付，每期支付1000元，年利率6%，则这笔年金在第5期时的终值是多少？2.计算期数A．某企业现有资金50000元，拟投资于甲方案，甲方案的年投资报酬率为12%，问该企业应投资与甲方案几年后才能得到100000元的终值？B．某人现在投入资金20000元购买一个理财产品，每年的回报资金均等为1600元，贴现率为6％，问这个理财产品至少要保证多少年才能够开始取得净收益？3.计算贴现率A．某人现在存款20000元，希望5年后得到50000元，假设每年末复利计息一次，那么复利年利率应该达到多少？B．某人现在投入资金20000元购买一个理财产品，每年的回报资金均等为1600元，收益的持续时间为25年，求该投资的实际收益率。后面的习题6还有复利终值下的贴现率的计算。四、实验步骤1.终值和现值的计算（1）创建工作表，如下表1ABCDE2终值、现值计算表3年限利率终值（元）现值（元）年金（元）456%100001000010005现值------------------------------6终值------------------------------7年金现值------------------------------8年金终值------------------------------（2）在上表的A4：E4中输入原始数据。（3）参考下表所示，利用FV、PV函数在上表中进行计算。单元格函数D5=PV（B4，A4，0，-C4）C6=FV（B4，A4，0，-D4）E7=PV（B4，A4，-E4，0）E8=FV（B4，A4，-E4，0）（4）计算结果，如下表所示。1ABCDE2终值、现值计算表3年限利率终值（元）现值（元）年金（元）456%100001000010005现值--------------------7472.58----------6终值----------13382.26--------------------7年金现值------------------------------4212.368年金终值------------------------------5637.092.计算期数A．在单元格中插入NPER函数，输入内容为：=NPER(12%,,-50000,100000)运算结果为6.12B．在单元格中插入NPER函数，输入内容为：=NPER(0.06,1600,-20000)运算结果为23.793.计算贴现率A．在单元格中插入RATE函数，输入内容为：=RATE（5，，-20000，50000）运算结果为20.11%B．在单元格中插入RATE函数，输入内容为：=RATE(25,-1600,20000)运算结果为6.24%实验一习题（资金时间价值）1.在15年前由于A企业的发展遇到一些问题，急需大笔资金，王先生借给A企业150万元现金用于发展生产，考虑到风险水平，双方约定的利率为14％，A企业可以选择在15年内的任意时间点还钱，但是该企业一直没有足够的资金偿还，本年度是A企业还款的最后一年，A企业终于准备还款，请计算还款额。=FV（14%，15，0，-1500000）=10,706,906.972.如果你突然收到一张事先不知道的1255亿美元的账单，你一定会大吃一惊。而这件事却发生在瑞士的田纳西镇的居民身上。该问题源于1966年的一笔存款。斯兰黑不动产公司在田纳西镇的一个银行存入6亿美元的存款。存款协议要求银行按1%的周利率复利付息（难怪该银行第二年破产！）。1994年，纽约布鲁克林法院做出判决：从存款日到田纳西镇对该银行进行清算的7年中，这笔存款应按每周1%的复利计息，而在银行清算后的21年中，每年按8.54%的复利计息。请说明1255亿美元如何计算出来的并思考该案例的启示。=FV（8.54%,21,0,-FV（1%，365，0，-6））=1,267.18或FV1=FV（1%，365，0，-6）则FV=FV（8.54%,21,0,-FV1）(这里也是用周利率来算，365的由来是7年*（365天/7天），即一共365周)3.某人持有一份10年公司债券，在取得的时候忘记了支付的金额，但是已知本年度截至日可以取得的本金和利息之和为124800元，票面利率为9.2％，求该公司债券的票面金额。=PV(9.2%,10,0,-124800)=51,759.284.王先生准备与朋友投资一个新项目，按照规定，王先生提供项目所需的150万元资金，但是不享受公司股份，只享受未来公司给其分配的固定收益，收益额为每年30万元，期限为8年，已知王先生的如果不做这个项目，可以获得的最低投资报酬率为18％，问王先生是否该投资此项目。=RATE(8,300000,-1500000)=12%5.基金中有一种购买方式为定投型基金，是约定银行每月按期定额从制定帐户扣除现金用于购买基金的一种产品。如果A家庭现在开始使用定投方式购买基金，月投入额为1500元，问15年后可以取出多少钱。（基金长期的年投资报酬率假设为24％）=FV(2%,180,-1500,0)=2,574,062.35（要换算成每月的年金来算，不可化成每年来算，15年共有15*12=180个月，月报酬率为24%/12=2%）6.小王准备储存40万元用于子女将来的出国费用，现在开始向银行存款，已知存款的利率为7.85％，如果每年存款额为3万元，问几年小王可以达到40万的存款额？=-NPER(7.85%,30000,400000)=9.487.如果你手中拥有100万元的现金，有人愿意向你借钱，其承诺未来10年每年还款17万元，而同期你可以获得的年平均收益率为12％（一分利），不考虑借款人的风险差异，问是否应该借钱给他？=RATE(10,170000,-1000000)=11%8再参考年金终值系数表编制一个先付年金终值系数表(1)横轴为利率1％——10％,纵轴为年限1年——30年(2)再参考年金现值系数表编制一个先付年金现值系数表横轴为利率1％——10％,纵轴为年限1年——30年实验二股票筹资分析、债券筹资分析、长期借款筹资分析和租赁筹资分析一、实验目的掌握各种筹资方式的优缺点，利用EXCEL函数PMT、IPMT、PPMT及电子表格软件计算贷款的每年偿还额、偿还的利息和本金；利用EXCEL函数及电子表格处理软件进行筹资方式决策分析。二、实验原理1．股票筹资时，股票价格=每年股利/（投资报酬率-股利固定增长率）或者=未来形成的现金股利现值之和，或者=每股利润*市盈率2.债券筹资时，要注意分清不同利息支付时间下债券价值的计算，债券的价值=未来每年支付的利息现值之和+票面金额现值3.长期借款筹资的还款方式有：一次偿还法，等额本金法，等额分期付款等（1）一次性偿付————较简单，自学（2）等额本金法————每期偿还等额本金，并按照借款余额计算利息（3）等额分期付款————每期偿还相等数额款项（本金和利息之和），则每期的付款额现值之和应该等于借款数额基于固定利率和等额分期付款方式的每期相等还款额：A＝P/(P/A，i，n)也可以用PMT公式来计算A=PMT(rate,nper,-pv)表示借款额为P，要求未来N年每年等额偿还等额付款额A其计算公式分别为：利息偿还额＝IPMT(rate,per,nper,-pv)本金偿还额＝PPMT(rate,per,nper,-pv)在长期借款分析时，要考虑到借款支付的利息具有减税效应，应该根据实际每期支付额计算企业借款所发生的所有成本（考虑货币时间价值）4．租赁筹资每期应付租金的计算方法：（1）平均分摊法：以事先约定的利率和手续费率计算出租赁期间的利息和手续费，然后连同租赁资产的成本按支付租金的次数平均分摊每期的租金。每期应付租金R=[(C-S)+I+F]/NC——租赁资产的购置成本S——出租人回收的租赁资产的残值I——租赁期间的利息F——租赁的手续费N——租期（2）等额年金法：按期数每期等额支付租金三、实验内容（一）股票筹资分析1．实验资料：甲乙丙三家公司的有关资料如下：甲公司准备发行6000万股普通股，预计第一年发放股利2元/股，以后股利将以5%的比率稳定增长，股东要求的投资报酬率为15%。乙公司准备发行5000万股普通股，预计第一年发放股利1元/股，第2至5年中股利将以8%的比率增长，5年以后股利将以3%的比率稳定增长，股东要求的投资报酬率为12%。丙公司原有普通股5000万股，现拟于当年4月1日增发3000万股新普通股，预计发行股票当年公司的盈利为4500万元，根据行业平均市盈率水平测算，发行市盈率确定为20倍。问：不考虑承销费用时三家公司股票的发行价格应为多少？如果证券承销商对承销股票业务按股票发行总额收取5%的承销费用，三家公司股票的零售发行价格应为多少？2．分析步骤：ABCD1甲股票乙股票丙股票2现有普通股50003增发新股（万股）6000500030004第2至5年股利增长率8%5第1年预计股利（元/股）216第2年预计股利（元/股）7第3年预计股利（元/股）8第4年预计股利（元/股）9第5年预计股利（元/股）10股利稳定增长率5%3%11股东要求的投资报酬率15%12%12发行股票当年预计盈利（万元）450013发行市盈率2014发行股票承销费率5%5%5%15不考虑承销费时股票的发行价格（元/股）20.0012.9912.4116考虑承销费时股票的发行价格（元/股）21.0513.6813.07填充公式单元格公式单元格公式C6=C5*（1+$C$4）C15=NPV(C11,C5:C9)+(C9*(1+C10)/(C11-C10))/(1+C11)^5C7=C6*（1+$C$4）D15=D13*D12/(D2+D3*9/12)C8=C7*（1+$C$4）B16=B15/(1-B14)C9=C8*（1+$C$4）C16=C15/(1-B14)B15=B5/(B11-B10)D16=D15/(1-B14)（二）债券筹资分析1．实验资料：这里以每年付息一次,到期一次还本的债券为例子某公司拟发行两种期限不同的债券，M债券的期限5年，N债券的期限10年。两种债券面值均为1000元，票面利率均为10%，每年末付息一次，到期一次还本。当市场利率分别为6%、8%、10%、12%和14%的情况下，两种债券的价值分别是多少？试根据计算结果对债券发行价格进行分析。2．分析步骤：ABCDEF1债券资料2M债券N债券3债券面值（元）100010004票面利率10%10%5期限（年）51067债券价值计算8市场利率6%8%10%12%14%9M债券的价值（元）1168.491079.851000.00927.90862.6810N债券的价值（元）1294.401134.201000.00887.00791.36填充公式单元格PV和NPV公式的使用方法=PV(10%，2，－121)=210PV和NPV公式的使用方法=PV(10%，2，－121)=210表示未来两年每年121的年金现值之和为210，公式中有负号“－”PV公式括号最后面还可以加一个终值，如下＝PV(10%，2，－121，－121)=310=NPV(10%，121，121)=210与公式一两者的意思一致，但是这里没有负号“－”B9=PV（B8，$B$5,－$B$3*$B$4,-$B$3）C9=PV（C8，$B$5,－$B$3*$B$4,-$B$3）D9=PV（D8，$B$5,－$B$3*$B$4,-$B$3）E9=PV（E8，$B$5,－$B$3*$B$4,-$B$3）F9=PV（F8，$B$5,－$B$3*$B$4,-$B$3）B10=PV（B8，$C$5,－$C$3*$C$4,-$C$3）C10=PV（C8，$C$5,－$C$3*$C$4,-$C$3）D10=PV（D8，$C$5,－$C$3*$C$4,-$C$3）E10=PV（E8，$C$5,－$C$3*$C$4,-$C$3）F10=PV（F8，$C$5,－$C$3*$C$4,-$C$3）结果分析：选取单元格区域B8：F10，单击工具栏上的[插入]按钮，在[图表]中选取[XY散点图]，然后按照图标向导对话框提示的4个步骤依次操作，并对图表进行格式化，即可得到M和N两种债券的价值与市场利率之间的关系图，如图所示。由图的计算结果以及债券价值与市场利率之间的关系图可以看出，随着市场利率的升高，M和N两种债券的价值都会逐渐降低，即债券的价值与市场利率之间成反方向变化的关系。但是期限长的N债券价值的变动比期限短的M债券价值的变动受市场利率的影响更大，即当市场利率升高时，N债券价值下降的幅度大于M债券；反之，当市场利率降低时，N债券价值增加的幅度也大于M债券。当市场利率等于债券的票面利率时，两种债券的价值都等于其面值，此时债券应等价发行；当市场利率低于债券的票面利率时，两种债券的价值都高于其面值，此时债券应溢价发行；当市场利率高于债券的票面利率时，两种债券的价值都低于其面值，此时债券应折价发行。（3）习题：每半年付息一次、到期一次还本的债券a．实验资料：某公司正在考虑发行X债券或Y债券。两种债券的面值均为1000元，期限均为5年，均为每半年付息一次，到期一次还本。X债券的票面利率为8％，Y债券的票面利率为12％。市场利率10％。这两种债券目前的发行价格应是多少？ABCDEFG1债券资料2X债券Y债券3债券面值（元）100010004票面利率8%12%5期限（年）556每年付息次数（次）227市场利率10%10%89债券价值计算10距离债券到期时间（年）01234511X债券的价值（元）12Y债券的价值（元）b．分析步骤（1）在单元格B11中输入计算公式＝PV（$B$7/$B$6,B10*$B$6,-$B$3*$B$4/$B$6,-$B$3）解释：上述公式实质为＝PV(5%,0*2,-1000*8%/2,-1000)其中，5%为实际折现率，0*2表示折现期数，1000*8％/2表示每半年利息，1000表示终值然后向右复制公式直到G11，计算X债券在不同时期的价值（2）在单元格B12输入计算公式＝PV（$C$7/$C$6,B10*$C$6,-$C$3*$C$4/$C$6,-$C$3）然后向右复制公式直到G12，计算X债券在不同时期的价值（3）选择单元格区域B10:G12,单击工具栏上的[插入]按钮，在[图表]中选取[XY散点图]，然后按照图标向导对话框提示的4个步骤依次操作，并对图表进行格式化，即可得到M和N两种债券的价值与市场利率之间的关系图如图所示。（三）长期借款筹资分析1．实验资料：某企业向银行借款500万元，年利率为8％，期限6年，借款筹资的手续费为2万元。所得税税率33％，现有两种还本付息方式：（1）方案A，每年末等额还本付息；（2）方案B，前2年每年末偿还本金50万元，并每年末支付按每年初借款余额计算的相应利息，其余的款项在后4年中每年末等额还本付息。问：企业应选择哪种还本付息方式？分析：这个题目应该比较两个方案的税后成本代价现值来判断。2．分析步骤ABCDEFGHI1借款额借款利率期限金额单位：万元25008％62345各年还本付息（万元）成本代价现值6年份1234567方案A还本8付息9利息抵税10成本代价11方案B还本12付息13利息抵税14成本代价1516结论：（1）方案A的计算：选取单元格C7:H7,点击最上方的单元格函数输入区域，输入数组公式＝PPMT(B2,C6:H6,C2,-A2)，然后同时按Ctrl+Shift+Enter，就可以得到单元格C7:H7区域内的数据。下面也是一样。选取单元格C8:H8,输入数组公式＝IPMT(B2,C6:H6,C2,-A2)选取单元格C9:H9,输入数组公式＝C8:H8*33%选取单元格C10:H10,输入数组公式＝C7:H7+C8:H8-C9:H9在单元格I7中输入公式＝NPV(B2,C10:H10)+D2（2）方案B的计算：在单元格C11和D11中分别输入50选取单元格E11:H11,输入数组公式=PPMT(B2,E6:H6-2,H6-D6,-(A2-SUM(C11:D11)))在单元格C12中输入公式＝A2*B2在单元格D12中输入公式＝（A2-C11）*B2选取单元格E12:H12,输入数组公式=IPMT(B2,E6:H6-2,H6-D6,-(A2-SUM(C11:D11)))选取单元格C13:H13,输入数组公式＝C12:H12*33%选取单元格C14:H14,输入数组公式＝C11:H11+C12:H12-C13:H13在单元格I11中输入公式＝NPV(B2,C14:H14)+D2（3）在单元格B16中输入公式=IF(I7<I11,"方案A","方案B")结果显示方案B成本更低，应选择方案BABCDEFGHI1借款额借款利率期限筹资费用金额单位均为：万元25008％62345各年还本付息（万元）成本代价现值6年份1234567方案A还本68.1673.6179.5085.8692.73100.15461.968付息40.0034.5528.6622.3015.438.019利息抵税13.2011.409.467.365.092.6410成本代价94.9696.7698.70100.80103.07105.5111方案B还本50.0050.0088.7795.87103.54111.82459.4412付息40.0036.0032.0024.9017.238.9513利息抵税13.2011.8810.568.225.692.9514成本代价76.8074.12110.21112.55115.08117.821516结论：方案B请同学们自行计算所得税税率改为20%，B方案的前两年本金付款额为80万元，看下这个时候应该选择哪个方案？经计算A方案现值为477.73万元，B方案现值为478.45万元。选择A方案。结果如下：各年还本付息（万元）成本代价现值年份123456方案A还本68.1673.6179.5085.8692.73100.15付息40.0034.5528.6622.3015.438.01利息抵税8.006.915.734.463.091.60成本代价100.16101.25102.43103.70105.07106.56477.73方案B还本80.0080.0075.4581.4988.0195.05付息40.0033.6027.2021.1614.647.60利息抵税8.006.725.444.232.931.52成本代价112.00106.8897.2198.4299.72101.13478.45请大家再次计算B方案的前两年的本金付款额等于多少的时候，AB两个方案的现值是相等的。答案应该为70.40万元，当然也可以用“单变量求解”来做，但是计算量比较大。单变量设置的函数为：=NPV(B2,C11+C12-C13,C11+D12-D13,PMT(0.08,4,-500+$C11*2)-E13,PMT(0.08,4,-500+$C11*2)-F13,PMT(0.08,4,-500+$C11*2)-G13,PMT(0.08,4,-500+$C11*2)-H13)+2-I10同时，IF函数在使用的时候，还可以考虑到两者相等的情况，表达为=IF(I7<I11,"方案A",IF(I7=I11,＂AB均可＂,"方案B"))，这里的"方案B"也可以用前面的单元格A11代替。（四）.租赁筹资分析1．实验资料某公司2004年1月1日从租赁公司租入一套设备，价值200万元，租期6年，租金每年末支付一次。①若预计租赁期满的残值为3万元，残值归租赁公司所有，年利率为8％，租赁手续费率为设备价值的2％，按平均分摊法计算每期租金。②若设备残值归承租公司，综合租费率确定为10％，按等额年金法确定每年末支付的租金。2．分析步骤在单元格B6中输入公式=((B2－B3）+(B2*(1+B4)^D2-B2)+B2*B5)/D2在单元格D8中输入公式=PMT(D4,D2,-B2)ABCD1年租金的计算2租赁资产价值200租赁期限63残值3每年支付租金时点年末4年利率8％综合租费率10％5手续费率2％6平均分摊法年租金53.06等额年金法年租金45.92这里面的等额年金不仅考虑了设备款，还考虑了残值给企业成本带来的减值效应。如果这里面要求计算支付的现金多少，应在单元格D8中输入公式=PMT(D4,D2,-B2)，结果为45.92（五）用日线数据绘制个股K线图已知资料如下：ABCDEF1000000中华股份2日期成交额开盘价最高价最低价收盘价304-6-1100.001.0011.0450.9920.999404-6-2101.000.9981.0850.9840.937504-6-3102.000.9771.0020.9711.001604-6-499.001.0041.0850.9681.044704-6-598.001.0501.1050.9701.088804-6-899.001.0951.1240.9981.075904-6-9100.001.0671.1170.9841.0551004-6-10102.001.0281.0181.0051.0281104-6-11101.001.0281.1191.0221.0681204-6-1298.001.0601.1200.9801.1001304-6-1497.001.0811.1511.0811.1511404-6-1597.001.1501.2221.1111.1551504-6-16101.001.1581.2231.1281.1661604-6-17103.001.1141.2241.1101.1511704-6-18101.001.1551.2251.1121.131选择单元格A3：F17，点击插入图表，选择“股价图”，选择“子图表类型”，根据已知资料选择第四个图表格式，点下一步，直到出现标题，输入“股价图”，x轴为“日期”，y轴为“成交额”，次x轴不输入，次y轴为股价。然后完成，完成后可以对图表做适当修改，比如纵轴刻度的最小值和最大值设置，阴线和阳线的颜色。结果如下：如果没有成交量得到的结果为（选股价第二种图格式）：实验三筹资预测与决策一、实验目的通过实验，使学生了解资金预测时需要考虑的因素和主要方法，能够使用EXCEL软件中的SLOPE函数,INTERCEPT函数进行回归分析，能利用EXCEL进行本量利分析，计算资本成本率、财务杠杆系数和营业杠杆系数。二、实验原理1.资本需求量的预测：在利用回归分析法进行预测时，是按照历史数据，按照数学上最小平方法的原理，确定能够正确反映自变量和因变量之间具有最小误差平方和的回归方程作为预测模型进行预测的方法。主要使用的函数有：SLOPE函数,INTERCEPT函数。（LINEST为另外一种计算斜率、截距和相关系数的函数）SLOPE函数：=SLOPE(known_y’s,known_x’s)计算线性回归直线的斜率INTERCEPT函数：=INTERCEPT(known_y’s,known_x’s)计算直线与y轴的截距PEARSON函数：=PEARSON(array1,array2)计算y和x的相关系数r，也可以使用另外一个函数=INDEX(LINEST)计算计算y和x的相关系数r2，相对复杂，具体使用方法参考后面的实验。LINEST函数和后面一元二次回归方法一样，可以一次计算a和b的数值，INDEX是在所计算的结果中去定位具体a和b的位置。2.资本成本率的计算：（1）长期债券成本。计算公式为：Kb=Ib（1-T）/[B（1-Fb）]（2）长期借款成本。计算公式为：Kl=Il（1-T）/[L（1-Fl）]（3）普通股成本。计算公式为：Knc=D/[P（1-Fc）]+G（4）留存收益成本。计算公式为：Ks=D/P+G（5）综合资本成本。计算公式为：Kw=∑KjWj3.本量利分析（1）总成本＝变动成本＋固定成本＝单位变动成本×产销量＋固定成本（2）边际贡献＝销售收入－变动成本＝（单价－单位变动成本）×产销量（3）边际贡献率＝边际贡献/销售收入（4）变动成本率＝变动成本/销售收入（5）息税前利润＝销售收入－变动成本－固定成本＝边际贡献－固定成本（6）保本点销售量＝固定成本/（单价－单位变动成本）（7）保本点销售额＝保本点销售量×单价＝固定成本/边际贡献率4.经营杠杆公式：DOL=(S-C)/(S-C-F)5.财务杠杆公式：DFL=EBIT/[EBIT-I-DP/(1-T)]，若无优先股，DFL=EBIT/（EBIT-I）DP——优先股股利6.筹资决策：（1）比较资本成本法：选择资本成本率最低的筹资方式（2）每股利润分析法：通常计算每股利润无差别点，当息税前利润=每股利润无差别点时两种筹资方式均可；当息税前利润>每股利润无差别点时，选择发行债券或者长期借款筹资方式；当息税前利润<每股利润无差别点，选择发行普通股筹资方式（3）最佳资本结构分析三、实验资料1.资金需要量预测案例一、一元回归分析某企业在过去5年中销售收入与资金占用量的有关数据如图所示，则利用回归分析法预测资金需要量的具体步骤如下（假设销售收入与资金占用量之间为线性关系）：（1）在单元格B10中输入公式“=INTERCEPT(C3:C7,B3:B7)”，计算a截距或者输入公式“=INDEX(LINEST(C3:C7,B3:B7,,TRUE),1,2)”或者“=INDEX(LINEST(C3:C7,B3:B7,TRUE,TRUE),1,2)”或者“=INDEX(LINEST(C3:C7,B3:B7),1,2)”或者“=INDEX(LINEST(C3:C7,B3:B7),2)”（2）在单元格B11中输入公式“=SLOPE(C3:C7,B3:B7)”,计算系数b（斜率）；或者输入公式“=INDEX(LINEST(C3:C7,B3:B7,,TRUE),1,1)”或者“=INDEX(LINEST(C3:C7,B3:B7),1,1)”或者“=INDEX(LINEST(C3:C7,B3:B7),1)”（3）在单元格B12中输入公式“=INDEX(LINEST(C3:C7,B3:B7,,TRUE),3,1)”,计算相关系数r2，相关系数达0.9503，故资金占用量与销售收入之间的线性关系较为显著，可以利用直线回归方程进行预测；（由于本题已经假设y和x存在线性关系，本步骤可以省略）也可以使用PEARSON函数“=PEARSON(C3:C7,B3:B7)”，得到的结果为r，有别于上面计算的结果r2。（4）在单元格B15中输入公式“=B10+B11*B14”ABC1已知条件（金额单位：万元）2年度销售收入(x)资金总额(y)31999256126420002711395200126413262002295148720033161548回归方程有关参数的计算9回归方程y=a+bx10a=11b=12r=132004年资金需要量预测（万元）14预计销售收入32715预计资金需要量通常在相关系数在0.8以上时，就可以认为自变量和因变量的相关关系成立，否则相关关系不成立，可以采用线性回归分析法，否则只能采用其他方法。实验结果：ABC1已知条件（金额单位：万元）2年度销售收入(x)资金总额(y)31999256126420002711395200126413262002295148720033161548回归方程有关参数的计算9回归方程y=a+bx10a=13.3411b=0.4512r=97.49%132004年资金需要量预测（万元）14预计销售收入32715预计资金需要量160.82当然还有很多其他的函数可以进行预测分析，例如forecast函数，trend函数如上题使用forecast函数可以不需要计算斜率和截距，直接计算当销售收入等于327的时候对应的资金需要量，B15单元格输入内容：=forecast(B14,C3:C7,B3:B7)Trend函数和forecast函数类似，也不需要计算斜率和截距，直接计算当销售收入等于327的时候对应的资金需要量，B15单元格输入内容：=trend(C3:C7,B3:B7,B14)下面解释下INDEX和LINEST函数的意思。首先要在A11：C11或者A11：C14计算系列结果。可以看到结果其实只需要一行即可，下面都是重复数据，因此系列结果默认为一行。如果求斜率，其应该位于数列的第1行，第1个数，用index的时候后面写的定位为1，1，如果是截距定位为1，2，即第一行第二个数，当然第一行的1可以省略，因为有效数据只有一行。ABC1已知条件（金额单位：万元）2年度销售收入(x)资金总额(y)31999256126420002711395200126413262002295148720033161548回归方程有关参数的计算9回归方程y=a+bx10b=A11a=B11110.45101113.33651159#N/A120.45101113.33651159#N/A130.45101113.33651159#N/A140.45101113.33651159#N/A如果希望求相关系数r2，就得在计算数组的时候使用LINEST的标准格式公式。要在A11：C15输入数组公式=LINEST(C3:C7,B3:B7,TRUE,TRUE)，r2位于第3行第1个数。因此上面题目是在B12中输入公式“=INDEX(LINEST(C3:C7,B3:B7,,TRUE),3,1)”ABC1已知条件（金额单位：万元）2年度销售收入(x)资金总额(y)31999256126420002711395200126413262002295148720033161548回归方程有关参数的计算9回归方程y=a+bx10b=A11a=B11r2=A13110.45101113.33651#N/A120.05952116.74121#N/A130.9503452.936013#N/A1457.416443#N/A15494.939525.86051#N/A案例二、二元回归分析某企业在本年度1-9月份总成本与机器工时、人工小时的有关数据如图所示，则利用回归分析法预测线性函数（y=a+bx1+cx2）的具体步骤如下（假设销售收入与机器工时、人工小时之间为线性关系）：ABCD1月份总成本y机器工时x1人工小时x221100050100321120551284312306214554135070180651450752007615807823587166080250981800922801091900100300111012035012y=a+bx1+cx213c=A14b=B14a=C1414XXXXXX15XXXXXX16XXXXXX17XXXXXX1819r2=A16标准差=B162010月总成本由于是二元回归分析，在13行以下预留不少于三行，三列空白单元格选择多个预留空白单元格，输入数组公式“=LINEST(B2:B10,C2:D10,TRUE,TRUE)”或者输入“=LINEST(B2:B10,C2:D10,,1)”然后按CTRL+SHIFT+ENTER，生成如下数据，相应位置的数据会表示确定的变量值。需要注意x1和x2的位置在已知条件中和结果中处于不同的顺序。ABCD1月份总成本y机器工时x1人工小时x221100050100321120551284312306214554135070180651450752007615807823587166080250981800922801091900100300111012035012y=a+bx1+cx213c=A14b=B14a=C14143.616533.4323459471.43655#N/A150.367761.56086243.41869856#N/A160.998911.779178#N/A#N/A172719.986#N/A#N/49418#N/A#N/A19r2=A16标准差=B162010月总成本2149.11因此二元回归模型为y=471.44+3.43x1+3.62x2如果题目要求计算当十月份机器工时和人工小时分别是120和350时的总成本，直接代入即可计算。或者使用TREND函数：B20单元格输入=TREND（B2：B10，C2：D10，C11：D11）当然二元回归模型也可以用于一元回归模型的计算，同学们可以自己尝试。案例三、指数曲线模型分析指数曲线的数学模型为：在指数模型两边各取对数：设，则上式可改写为：根据案例一的一元回归分析，可以求得A、B。例：某企业产品销售收入1999～2006年的实际市场销售额如表所示，试对2007年的市场销售额进行预测。（销售额与年数存在指数关系）ABCDEF1年份年（x）销售额（y）Y=lny2199914476610.713200025431310.904200136339711.065200247497111.226200359114211.4272004611871111.6882005714290611.8792006817660012.0810合计3676680690.951120079209780.2712A=10.48367B=0.196682相关系数R=0.997813a=eA35727.41b=eB1.22在这里lny可以首先计算出来，输入公式“=ln（数值）”，看成Y，此时年数x仍然不变，据此可以对两列数据一元回归分析，结果如下：A，B，R的数值。ln函数---取自然对数，例如输入“=ln（100）”会得到4.61，即e^4.61=100，e=2.71828182845log函数--给定底数返回数字的对数，例如输入“=log（1000，10）”得到结果3exp函数--e的n次方，例如输入“=EXP(10.48367)”结果为35727.29因此a=e10.48367=35727.29b=e0.196682=1.22因此指数曲线数学模型为y=35727.29(e0.196682)x=35727.29*1.22x因此2007年销售=35727.29*1.229=209780.272.资本成本的计算实验资料：某公司拟筹资4000万元，其中按面值发行长期债券1000万元，票面利率10%，期限4年，筹资费率1%，每年末付息一次，到期一次还本；长期借款600万元，3年期，年利率11%；发行普通股2000万元，筹资费率4%，预计第一年股利率12%，以后每年按照4%的增长率递增。此外，公司保留盈余400万元，所得税税率33%。要求：计算该公司的综合资本成本。实验步骤（1）创建工作表，如下表所示ABCDE1个别资本成本及综合资本成本2筹资种类长期债券长期借款普通股留存收益3筹资金额（万元）100060020004004票面利率10%11%————5期限43————6股利率————12%12%7年增长率————4%4%8筹资费率1%04%——9所得税税率33%33%————10个别资本成本11筹资总额（万元）——————12个别资本成本所占比重13综合资本成本——————（2）在上表的B3：E9单元格区域内输入原始数据。（3）参考下表所示，利用相应公式在上表中进行计算。单元格公式或函数B10=B4*（1-B9）/（1-B8）C10=C4*（1-C9）/（1-C8）D10=D6/（1-D8）+D7E10=E6+E7B11=SUM（B3：E3）B12：E12=B3：E3/$B$11B13=B10*B12+C10*C12+D10*D12+E10*E12或者=SUMPRODUCT(B10:E10,B12:E12)（4）计算结果，如下表所示。ABCDE1个别资本成本及综合资本成本2筹资种类长期债券长期借款普通股留存收益3筹资金额（万元）100060020004004票面利率10%11%————5期限43————6股利率————12%12%7年增长率————4%4%8筹资费率1%04%——9所得税税率33%33%————10个别资本成本6.77%7.37%16.50%16.00%11筹资总额（万元）4000——————12个别资本成本所占比重25%15%50%10%13综合资本成本12.65%——————思考题：非固定增长股利模型某股票筹资总额为1000万元，筹资费率3%，第一年股利率（相对筹资总额）10%，第二年---五年都是按照10%递增，从第六年开始保持200万不变，直到第十年，公司运营结束，求资本成本率。（不考虑公司到期后公司剩余资产）创建表格如下：ABCD1筹资额1000前五年股利固定增长率10%2筹资费率3%后五年股利额2003第一年股利率10%4期数股利假设资本成本率净现值517%628%739%8410%9511%10612%117128139141015假设法下目标资本成本率16单变量求解：17净现值18可变单元格根据题目意思可得到第1年到第10年每年的股利额，分别填入B5：B14假设资本成本率分别为7%，8%，9%，10%，11%，12%可以得到对应的净现值，例如D5单元格输入：=NPV(C5,B5:B14)-B1*（1-B2）分析结果可知资本成本率应该在9%和10%之间，此时净现值分别为2.81和-44.70采用差值法可的目标资本成本率，在单元格B15输入：=C7+D7/(D7-D8)*1%如果采用单变量求解，在单元格B17输入：=NPV(B18,B5:B14)-B1*（1-B2）可变单元格B18即是所求的资本成本率，采用单变量求解令B17单元格等于0，可变单元格为B18可得资本成本率为9.06%。实验结果：ABCD1筹资额1000前五年股利固定增长率10%2筹资费率3%后五年股利额2003第一年股利率10%4期数股利假设资本成本率净现值511007%108.91621108%53.90731219%2.8184133.110%-44.7095146.4111%-88.9310620012%-130.14117200128200139200141020015假设法下目标资本成本率9.06%此处是差值法得到的结果16单变量求解：此处是直接求解得到结果更准确17净现值0.0018可变单元格9.06%3.杠杆作用分析1）本量利之间的关系例1：某公司仅生产单一的甲产品，单位变动成本为200元/件，单价为360元/件，年固定经营成本为80000元，要求计算公司保本点的销售量和销售额；如果下一年预计的销售量分别是100、200、300、400、500、600、700、800件，公司的息税前利润分别是多少，并根据该数据绘制公司的保本分析图。计算过程：a．在单元格B6中输入公式“＝B4/(B2-B3)”;在单元格B7中输入公式“＝B6*B2b．选取单元格区域B10:I10,输入公式“＝B9:I9*B2”;选取单元格区域B11:I11,输入公式“＝B9:I9*B3+B4”，得到总成本；选取单元格区域B12:I12,输入公式“＝B10:I10-B11:Ic．选取单元格区域B9:I12，单击工具栏上的[插入]按钮，在[图表]中选取[XY散点图]，然后按照图标向导对话框提示的4个步骤依次操作，并对图表进行格式化和调整，即可得到保本分析图。ABCDEFGHI1已知条件2单价（元/件）3603单位变动成本（元/件）2004年固定成本（元）800005保本点的计算6保本点销售量7保本点销售额（元）8息税前利润的计算9产销量10020030040050060070080010销售收入（元）11总成本（元）12息税前利润（元）结果如下：ABCDEFGHI1已知条件2单价（元/件）3603单位变动成本（元/件）2004年固定成本（元）800005保本点的计算6保本点销售量5007保本点销售额（元）1800008息税前利润的计算9产销量10020030040050060070080010销售收入（元）360007200010800014400018000021600025200028800011总成本（元）10000012000014000016000018000020000022000024000012息税前利润（元）-64000-48000-32000-160000160003200048000保本分析图如下：例2：某公司只产销一种产品，根据下列已知条件求相应的未知数：a.当销售收入为250000元，边际贡献率35％，息税前利润18000元，求固定成本和变动总成本。b.销售收入460000元，变动总成本320000元，固定成本125000元，求边际贡献率和息税前利润。c.边际贡献率20％，固定成本50000元，息税前利润-1000元，求销售收入和变动总成本。d.销售收入500000元，变动总成本360000元，息税前利润45000元，求边际贡献率和固定成本。请同学自己设计表格，并在表格中运用函数计算结果。2）经营杠杆例：某公司只生产和销售一种产品，单价500元/件，单位变动成本300元/件，该公司没有负债，每月销售额75万元，税后净利润4.8万元，公司的所得税税率40％，计算：公司每月的固定经营成本，每月的保本点销售量和销售额；在每月的销售量分别为300、600、900、1200、1500、1800、2100、2400件时的经营杠杆系数（1）在单元格B8中输入公式“=B4-(B4/B2)*B3-B5/(1-B6)”，得到月固定成本。（2）在单元格B9中输入公式“=B8/(B2-B3)”，在单元格B10中输入公式“=B9*B2（3）在单元格E3中输入公式“=D3*($B$2-$B$3)/(D3*($B$2-$B$3)-$B$8)”，并将其向下复制到单元格E4:E10，得到经营杠杆系数。ABCDE1已知条件经营杠杆系数的计算2单价500月销售量经营杠杆系数3单位变动成本3003004月销售额7500006005净利润480009006所得税税率40％12007保本点计算15008月固定成本18009保本点销售量210010保本点销售额2400结果如下：ABCDE1已知条件经营杠杆系数的计算2单价500月销售量经营杠杆系数3单位变动成本300300-0.384月销售额750000600-1.205净利润48000900-4.506所得税税率40％120012.007保本点计算15003.758月固定成本22000018002.579保本点销售量110021002.1010保本点销售额55000024001.853）财务杠杆例题：某公司现有普通股200万股，资金总额500万元，负债比率40％，负债利率8％，优先股年股息4万元，当年息税前利润60万元，所得税税率33％，计算：当年的普通股每股利润，当年的财务杠杆系数；预计下一年度息税前利润将增长20％，则普通股每股利润的增长率是多少？预计的普通股每股利润是多少？ABCD1已知条件2普通股股数2000000优先股年股息400003资金总额5000000息税前利润6000004负债比率40％所得税税率33％5负债利率8％预计息税前利润增长率20％67每股利润和财务杠杆系数的计算8普通股每股利润普通股每股利润增长率9财务杠杆系数普通股每股利润（增长）B8输入“=((D3-B3*B4*B5)*(1-D4)-D2)/B2B9输入“=D3/(D3-B3*B4*B5-D2/(1-D4))”D8输入“=B9*D5D9输入“=B8*(1+D8)”也可以先计算D9，然后计算D8结果如下：ABCD1已知条件2普通股股数2000000优先股年股息400003资金总额5000000息税前利润6000004负债比率40％所得税税率33％5负债利率8％预计息税前利润增长率20％67每股利润和财务杠杆系数的计算8普通股每股利润0．13普通股每股利润增长率31．55%9财务杠杆系数1．58普通股每股利润（增长）0．174.筹资决策方法1）比较资本成本法：以初始筹资决策为例例题：某企业初创时需要资金1000万元，有三个备选筹资方案，有关资料如下，请选择最优筹资方案。ABCDEFGHIJ1初始筹资决策（金额单位：万元）2筹资方案方案A方案B方案C3筹资额比重资本成本筹资额比重资本成本筹资额比重资本成本4长期借款2006%3006.5%4007%5长期债券2007%1007%00%6优先股20015%10015%20015%7普通股40012%50012%40012%8合计910最优方案步骤：分别计算方案A、B、C的筹资额所占比重，并据此计算综合资本成本率，根据综合资本成本率选择最佳筹资方案。单元格输入函数：单元格D8输入：=SUMPRODUCT(C4:C7,D4:D7)单元格G8输入：=SUMPRODUCT(F4:F7,G4:G7)单元格J8输入：=SUMPRODUCT(I4:I7,J4:J7)单元格B10输入：=IF(D8=MIN(D8,G8,J8),"方案A",IF(G8=MIN(D8,G8,J8),"方案B","方案C"))ABCDEFGHIJ1初始筹资决策（金额单位：万元）2筹资方案方案A方案B方案C3筹资额比重资本成本筹资额比重资本成本筹资额比重资本成本4长期借款20020%6%30030%7%40040%7%5长期债券20020%7%10010%7%00%0%6优先股20020%15%10010%15%20020%15%7普通股40040%12%50050%12%40040%12%8合计1000100%10.40%1000100%10.15%1000100%10.60%910最优方案方案B附加：sumif函数介绍某公司有三个业务员小张、小王和小刘，某年的单次业务量如下表，要求计算每人的汇总后年销量。ABCDE1已知资料统计结果2姓名单次销量姓名总销量3小王120小张=SUMIF(A3:A10,D3,B3:B10)4小张150小王=SUMIF(A3:A10,D4,B3:B10)5小张180小刘=SUMIF(A3:A10,D5,B3:B10)6小张200上面公式中D3也可以用”小张”代替7小王1808小刘1909小王145小张180汇总年销量E列输入的公式在上方。Countif函数介绍运用举例1、返回包含值12的单元格数量=COUNTIF（DATA,12）2、返回包含负值的单元格数量=COUNTIF（DATA，"<0"）3、返回不等于0的单元格数量（只要单元格内容不是0就要统计）=COUNTIF（DATA，"<>0"）4、返回大于5的单元格数量=COUNTIF（DATA，">5"）5、返回等于单元格A1中内容的单元格数量=COUNTIF（DATA，A1）6、返回大于单元格A1中内容的单元格数量=COUNTIF（DATA，">''&A1）7、返回包含文本内容的单元格数量=COUNTIF（DATA,”*”）8、返回包含三个字符内容的单元格数量=COUNITF（DATA，''???''）9、返回包含单词"GOOD"(不分大小写)内容的单元格数量=COUNTIF（DATA,''GOOD''）10、返回在文本中任何位置包含单词"GOOD"字符内容的单元格数量=COUNTIF（DATA,"*GOOD*"）11(1)、返回包含以单词"AB"(不分大小写)开头内容的单元格数量=COUNTIF（DATA,"AB*"）11(2)、返回包含以单词"AB"(不分大小写)结尾内容的单元格数量=COUNTIF（DATA,"*AB"）12、返回包含当前日期的单元格数量=COUNTIF（DATA，TODAY()）13、返回大于平均值的单元格数量=COUNTIF（DATA，">"&AVERAGE(DATA))14、返回平均值上面超过三个标准误差的值的单元格数量=COUNTIF（DATA,">"&AVERAGE(DATA)+STDEV(DATA)*3)15、返回包含值为3或-3的单元格数量=COUNTIF（DATA，3）+COUNIF（DATA，-3）16、返回包含值;逻辑值为TRUE的单元格数量=COUNTIF（DATA，TRUE）17、统计区域中不为空的单元格个数（数值、文本、空格都算）——（上述第3条：文本也算不等于0，空格不算）只要单元格有内容，即使是空格都要统计=Countif(DATA,"<>")18、只统计文本单元格数量，不统计数值和空格——（上述第7条统计含空格）=COUNTIF(DATA,"><")具体举例AB1资料资料2苹果323橘子544水蜜桃755苹果86公式叙述结果=COUNTIF(A2:A5,"苹果")上述第一栏是苹果的储存格数目2=COUNTIF(B2:B5,">55")以上第二栏中的数值大于55的储存格数目22）每股利润分析法例题：某企业现有资本800万元，其中债务200万元，股东权益600万元，该公司准备实施一项投资计划，为此需要追加筹资200万元，现有A、B两个备选的筹资方案，有关资料如图，公司所得税税率为33％，计算：若投资计划实施后预计的息税前利润分别为40、60、80、100、120、140、160、180万元时，两个筹资方案的普通股每股利润各是多少？两个筹资方案的普通股每股利润无差别点是多少？根据第一问绘制普通股每股利润与息税前利润之间的关系图，并作出决策。ABCDEFGHI1已知条件2现有资本结构追加筹资方案3股东权益6000000普通股股数600000方案A4债务2000000债务利率8％股票筹资额2000000新增股数2000005现有资本8000000方案B6所得税税率33％债务筹资额2000000新增利率10％789计算过程10预计息税前利润4000006000008000001000000120000014000001600000180000011方案A每股利润12方案B每股利润13最优筹资方案1415目标函数：方案A-方案B16无差别点的息税前利润17无差别点的每股利润步骤：分别计算AB方案的每股利润单元格B13中输入“＝if（B11>B12,＂方案A＂，＂方案B＂）”然后向右复制单元格D15中输入“=(D16-B4*D4)*(1-B6)/(D3+H4)-(D16-B4*D4-F6*H6)*(1-B6)/D3然后选择“单变量求解”，在对话框中，目标单元格输入D15，在目标值中输入“0”，在可变单元格中输入D16，最后单击确定在单元格D17中输入“=(D16-B4*D4)*(1-B6)/(D3+H4)”结果如下：ABCDEFGHI1已知条件2现有资本结构追加筹资方案3股东权益6000000普通股股数600000方案A4债务2000000债务利率8％股票筹资额2000000新增股数2000005现有资本8000000方案B6所得税税率33％债务筹资额2000000新增利率10％789计算过程10预计息税前利润4000006000008000001000000120000014000001600000180000011方案A每股利润0.200.370.540.700.871.041.211.3712方案B每股利润0.040.270.490.710.941.161.381.6113最优筹资方案A方案A方案A方案B方案B方案B方案B方案B方案1415目标函数：方案A-方案B016无差别点的息税前利润96000017无差别点的每股利润0．67每股利润与息税前利润分析图如下：3）最佳资本结构分析略实验四内部长期投资决策一、实验目的掌握各种折旧方法的特点，掌握投资决策指标的计算方法，能利用EXCEL函数SLN、DB、DDB、SYD、NPV、IRR等函数计算每期的折旧额、每期的现金流量和投资决策相关指标，并根据结果进行投资决策分析。二、实验原理1.固定资产折旧直线折旧法公式：SLN（cost,salvage,life）加速折旧法：余额递减法DB(cost,salvage,life,period,month)month为第一年的月份数，若省略则为12双倍余额递减法DDB(cost,salvage,life,period,factor)年数总和法SYD(cost,salvage,life,per)2.现金流量的计算初始现金流量＝-（固定资产投资+流动资产投资+其他投资）+旧固定资产变价净收入旧固定资产变价净收入主要指更新固定资产时变卖原有资产所得净收入营业现金流量＝净利润+折旧终结现金流量＝残值+流动资产回收额+该年其他收入建设期内为购建固定资产发生的借款利息按会计制度应该资本化处理，但从企业全部投资者角度对项目进行评价，资本化的利息费用不能作为现金流量计算。但是资本化利息应该计入固定资产原值，进而可以求出折旧额。3.投资决策指标的计算投资回收期（PP）每年现金流量相等的时候：投资回收期＝原始投资额/每年现金流量当每年现金流量不等的时候：投资回收期＝（累计现金流量开始出现正值的年份－1）+︱上年尚未回收的投资额︱/该年净现金流量平均投资报酬率（ARR）ARR=∑NCFt/N/I净现值（NPV）=未来现金流入量现值之和-未来现金流出量现值之和或者NPV=∑NCFt/（1+i）t-I内部报酬率（IRR）NPV=∑NCFt/（1+ARR）t＝0获利指数PI＝(NPV+I)/I=∑NCFt/（1+i）t/I4.最小公倍寿命法是使投资项目的寿命周期相等的方法，也就是要求初两个项目使用年限的最小公倍数。年均净现值法是把项目的净现值转化为项目每年的平均净现值。年均净现值(ANPV)计算公式：ANPV=NPV/(P/A,i,n)三、实验内容1.固定资产折旧的计算1）直线折旧法：包括平均年限法和工作量法平均年限法：年折旧额＝（固定资产原值－预计净残值）/预计使用年限工作量法：每单位工作量折旧额＝（固定资产原值－预计净残值）/预计总工作量某期折旧额＝该期实际工作量*每单位工作量折旧额例题：某企业购置了一台设备，价格10万元，预计使用5年，预计净残值率5％。a．按平均年限法计提折旧时，每年的折旧额是多少？b．如果该设备在预计使用年限内可以运转12000机器工时，1～5年预计使用的机器工时分别为4000、2000、2500、2500、1000小时，则每年的折旧额是多少？ABCDEF1已知条件2原值（元）1000003使用年限54净残值率5%总工作量120005平均年限法：6年份123457折旧额8工作量法9工作量4000200025002500100010折旧额过程：单元格B77输入“=SLN(B2,B2*B4,B3)”，然后向右复制内容单元格B10：F10输入“=SLN(B2,B2*B4,D4)*B9:F9”，结果如下ABCDEF1已知条件2原值（元）1000003使用年限54净残值率5%总工作量120005平均年限法：6年份123457折旧额19,000.0019,000.0019,000.0019,000.0019,000.008工作量法9工作量4000200025002500100010折旧额31666.6715833.3319791.6719791.677916.67请同学们按照传统的方法手工在EXCEL中验算结果的正确性。2）加速折旧法：包括余额递减法、双倍余额递减法和年数总和法余额递减法：年固定折旧率＝1－