實驗指導書-《財務實驗操作》

本學期考試時間為最后一周，到時候考場內(nèi)同樣有監(jiān)考老師希望大家能夠?qū)⒚看握n堂在EXCEL中做的練習保存到自己或同學的優(yōu)盤中，以備將來考試復習使用考試時不允許攜帶任何書面和電子資料本課程需要使用的公式分為兩類：一類是需要背誦的，考試時不會函數(shù)表達式，另外一類公式考試時會在EXCEL中列示，課程結(jié)束時會和大家指明《財務實驗操作》實驗指導書安徽科技學院財務會計系財務管理課程組編2008年2月目錄：前言Excel基本操作實驗一貨幣時間價值實驗二企業(yè)籌資分析實驗三籌資預測與決策實驗四內(nèi)部長期投資決策實驗五投資決策中的風險分析實驗六流動資產(chǎn)管理實驗七利潤管理參考教材：1、韓良智編著，《EXCEL在財務管理中的應用》，人民郵電出版社，2004年第一版2、杜茂康編著，EXCEL與數(shù)據(jù)處理，北京電子工業(yè)出版社，2002年第一版3、魏亞平尹均惠編著，財務管理實驗教程，經(jīng)濟科學出版社，2005年6月第一版秒”。要輸入邏輯型的數(shù)據(jù)，輸入“true”(真)或“false”(假)即可。若數(shù)據(jù)由數(shù)字與小數(shù)點構(gòu)成，Excel自動將其識別為數(shù)字型，Excel允許在數(shù)值型數(shù)據(jù)前加入貨幣符號，Excel將其視為貨幣數(shù)值型，Excel也允許數(shù)值型數(shù)據(jù)用科學記數(shù)法表示，如2╳109在Excel中可表示為2E+9。除了以上三種格式以外的輸入數(shù)據(jù)，Excel將其視為字符型處理。（二）公式生成數(shù)據(jù)Excel的數(shù)據(jù)中也可由公式直接生成。例如：在當前工作表中A1和B1單元格中已輸入了數(shù)值數(shù)據(jù)，欲將A1與B1單元格的數(shù)據(jù)相加的結(jié)果放入C1單元格中，可按如下步驟操作：用鼠標選定C1單元格，然后輸入公式“=A1+B1”或輸入“=SUM(a1:b1)”,回車之后即可完成操作。C1單元格此時存放實際上是一個數(shù)學公式“A1+B1”，因此C1單元格的數(shù)值將隨著A1、B1單元格的數(shù)值的改變而變化。Excel提供了完整的算術運算符,如＋(加)－(減)*(乘)／(除)％(百分比)^(指數(shù))和豐富的函數(shù)，如SUM(求和)、CORREL(求相關系數(shù))、STDEV(求標準差)等，供用戶對數(shù)據(jù)執(zhí)行各種形式的計算操作，在（三）復制生成數(shù)據(jù)Excel中的數(shù)據(jù)也可由復制生成。實際上，在生成的數(shù)據(jù)具有相同的規(guī)律性的時候，大部分的數(shù)據(jù)可以由復制生成?？梢栽诓煌瑔卧裰g復制數(shù)據(jù)，也可以在不同工作表或不同工作簿之間復制數(shù)據(jù)，可以一次復制一個數(shù)據(jù)，也可同時復制一批數(shù)據(jù)，為數(shù)據(jù)輸入帶來了極大的方便。普通單元格的復制結(jié)果與公式單元格的復制結(jié)果相差較大，下面分別予以說明。1．普通單元格指的是非公式的單元格。普通單元格的復制，一般可以按如下步驟進行：（1）拖動鼠標選定待復制的區(qū)域，選定之后該區(qū)域變?yōu)楹谏xcel可以進行整行、整列或整個表格的選定操作，例如，如果要選定表格的第一列，可直接用鼠標單擊列標“A”，如果要選定表格的第一行，可直接用鼠標單擊行標“1”，如果要選定整個表格，可直接點擊全選按扭，如附圖-2所示：附圖-2（2）選定完區(qū)域之后，用鼠標右擊該區(qū)域，選擇“復制”，將區(qū)域內(nèi)容復制到粘貼版之中?？梢园l(fā)現(xiàn)該區(qū)域已被虛線包圍。（3）用鼠標右擊目標區(qū)域，選擇“粘貼”，則單元格區(qū)域的復制即告完成。2．公式單元格的復制，一般可分為兩種，一種是值復制，一種是公式復制。值復制指的是只復制公式的計算結(jié)果到目標區(qū)域，公式復制指的是僅復制公式本身到目標區(qū)域。下面對它們的操作步驟分別予以說明。（1）值復制：①拖動鼠標選定待復制區(qū)域。②用鼠標右擊選定區(qū)域，選擇“復制”選項。③用鼠標右擊目標區(qū)域，再單擊“選擇性粘貼”子菜單。出現(xiàn)復制選項，選定“數(shù)值”選項，然后用鼠標單擊“確定”按扭，則公式的值復制即告完成。（2）公式復制：公式復制是Excel數(shù)據(jù)成批計算的重要操作方法，要熟練公式復制的操作首先要區(qū)分好兩個概念：單元格的相對引用與絕對引用。Excel中的公式中一般都會引用到別的單元格的數(shù)值，如果你希望當公式復制到別的區(qū)域之時，公式引用單元格不會隨之相對變動，那么你必須在公式中使用單元格的絕對引用。如果你希望當公式復制到別的區(qū)域之時，公式引用單元格也會隨之相對變動，那么你必須在公式中使用單元格的相對引用。在公式中如果直接輸入單元格的地址，那么默認的是相對引用單元格，如果在單元格的地址之前加入“$”符號那么意味著絕對引用單元格。例如，在當前工作表中A1和B1單元格中已輸入了數(shù)值數(shù)據(jù)，用鼠標選定C1單元格，然后輸入公式“=A1+B1”,此公式引用的便是兩個相對的單元格A1、B1,也就是說如果將該公式復制到C2的單元格，公式所引用的單元格的地址將隨著發(fā)生變化，公式將變?yōu)椤?A2+B2”，如果將該公式復制到F100的單元格,那么公式將變?yōu)椤?D100+E100”，這就是相對引用的結(jié)果，公式的內(nèi)容隨著公式的位置變化而相對變化。如果在C1單元格輸入的是“=$A$1+$B$1”那么此公式引用的便是絕對的單元格，不論將公式復制到何處，公式的內(nèi)容都不會發(fā)生變化。當然，絕對引用和相對引用亦可在同一公式之中混合交叉使用，例如，如果在C1單元中輸入的是公式“=A$1+B$1”,那么意味著，公式的內(nèi)容不會隨著公式的垂直移動而變動，而是隨著公式的水平移動而變動，如果將該公式復制到F100單元格，那么公式將變?yōu)?，?D$1+E$1”?？梢宰鬟@樣的歸納：公式中“$在實際的使用中，如果能把單元格的相對引用與絕對引用靈活應用到Excel的公式之中，能為數(shù)據(jù)成批準確運算帶來極大的方便。四、數(shù)據(jù)的移動操作數(shù)據(jù)的移動操作可按如下步驟進行：（一）拖動鼠標選定待移動區(qū)域。（二）用鼠標右擊選定區(qū)域，選擇“剪切”選項。（三）用鼠標右擊目標區(qū)域，選擇“粘貼”，則單元格區(qū)域的移動即告完成。與數(shù)據(jù)的復制操作不同，公式單元格的移動操作不存在值移動或公式移動的區(qū)別，也不存在絕對引用已相對引用的區(qū)別，移動操作將把公式單元格的公式內(nèi)容原原本本移動到目標區(qū)域，不作任何改動。五、數(shù)據(jù)的刪除操作數(shù)據(jù)的刪除操作可按如下步驟進行：（一）拖動鼠標選定待刪除區(qū)域。（二）用鼠標右擊選定區(qū)域，選擇“刪除”，即可刪除單元格區(qū)域的內(nèi)容。如果不小心刪除了不該刪除的區(qū)域，可以通過“編輯”菜單的“撤消”命令來恢復被刪除的內(nèi)容?！俺废辈僮魇荅xcel中較常用到的操作，如果不小心實施了錯誤的操作，那么可以通過“撤消”操作使工作表恢復原樣。六、與其它軟件交換數(shù)據(jù)的方法在Excel中可以打開其它類型的數(shù)據(jù)文件，如FOXPRO系列的DBF數(shù)據(jù)庫文件，文本文件，lotus1-2-3的數(shù)據(jù)文件等。具體操作方法如下：（一）在“文件”菜單中選擇“打開”子菜單。（二）在“打開文件”對話框中選擇所要打開的文件的類型及其所在的目錄。（三）用鼠標雙擊該文件名，并按Excel提示步驟操作即可打開該文件。Excel文件同樣也可存為其它類型的數(shù)據(jù)文件，具體操作方法如下：（一）編輯好文件后，在“文件”菜單中選擇“另存為”子菜單。（二）在“另存為”對話框中選擇所要打開文件的類型及其所在的目錄。（三）輸入文件名之后，用鼠標單擊“保存”按扭即可。實驗一貨幣時間價值一、實驗目的通過實驗使學生理解貨幣時間價值；利用EXCEL函數(shù)FV、PV及電子表格軟件，熟悉并掌握貨幣時間價值，包括復利終值、復利現(xiàn)值、年金終值、年金現(xiàn)值、永續(xù)年金、遞延年金、期數(shù)和貼現(xiàn)率的計算。二、實驗原理運用籌資、投資管理中的終值、現(xiàn)值的概念，結(jié)合EXCEL函數(shù)公式，計算復利終值、復利現(xiàn)值、年金終值、年金現(xiàn)值、永續(xù)年金、遞延年金、期數(shù)和貼現(xiàn)率。復利現(xiàn)值=pv(rate,nper,0,-fv)復利終值=fv(rate,nper,0,-pv)年金現(xiàn)值=pv(rate,nper,-pmt,0)年金終值=FV(rate,nper,-pmt,0)期數(shù)=NPER(RATE,,-PV,FV)復利終值=NPER(RATE,PMT,-PV)年金現(xiàn)值練習6=-NPER(RATE,PMT,FV)年金終值貼現(xiàn)率=RATE(NPER,,-PV,FV)復利終值=RATE(NPER,PMT,-PV)年金現(xiàn)值三、實驗資料1.終值和現(xiàn)值的計算某人5年后要想從銀行取出10000元，年利率6%，按復利計算，則現(xiàn)在應該存入銀行的現(xiàn)金是多少？若某人現(xiàn)在存入銀行10000元，年利率6%，按復利計算，則5年后復利終值是多少？若有一筆年金，分期支付，每期支付1000元，年利率6%，則這筆年金的現(xiàn)值是多少？若有一筆年金，分期支付，每期支付1000元，年利率6%，則這筆年金在第5期時的終值是多少？2.計算期數(shù)A．某企業(yè)現(xiàn)有資金50000元，擬投資于甲方案，甲方案的年投資報酬率為12%，問該企業(yè)應投資與甲方案幾年后才能得到100000元的終值？B．某人現(xiàn)在投入資金20000元購買一個理財產(chǎn)品，每年的回報資金均等為1600元，貼現(xiàn)率為6％，問這個理財產(chǎn)品至少要保證多少年才能夠開始取得凈收益？3.計算貼現(xiàn)率A．某人現(xiàn)在存款20000元，希望5年后得到50000元，假設每年末復利計息一次，那么復利年利率應該達到多少？B．某人現(xiàn)在投入資金20000元購買一個理財產(chǎn)品，每年的回報資金均等為1600元，收益的持續(xù)時間為25年，求該投資的實際收益率。后面的習題6還有復利終值下的貼現(xiàn)率的計算。四、實驗步驟1.終值和現(xiàn)值的計算（1）創(chuàng)建工作表，如下表1ABCDE2終值、現(xiàn)值計算表3年限利率終值（元）現(xiàn)值（元）年金（元）456%100001000010005現(xiàn)值------------------------------6終值------------------------------7年金現(xiàn)值------------------------------8年金終值------------------------------（2）在上表的A4：E4中輸入原始數(shù)據(jù)。（3）參考下表所示，利用FV、PV函數(shù)在上表中進行計算。單元格函數(shù)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）計算結(jié)果，如下表所示。1ABCDE2終值、現(xiàn)值計算表3年限利率終值（元）現(xiàn)值（元）年金（元）456%100001000010005現(xiàn)值--------------------7472.58----------6終值----------13382.26--------------------7年金現(xiàn)值------------------------------4212.368年金終值------------------------------5637.092.計算期數(shù)A．在單元格中插入NPER函數(shù)，輸入內(nèi)容為：=NPER(12%,,-50000,100000)運算結(jié)果為6.12B．在單元格中插入NPER函數(shù)，輸入內(nèi)容為：=NPER(0.06,1600,-20000)運算結(jié)果為23.793.計算貼現(xiàn)率A．在單元格中插入RATE函數(shù)，輸入內(nèi)容為：=RATE（5，，-20000，50000）運算結(jié)果為20.11%B．在單元格中插入RATE函數(shù)，輸入內(nèi)容為：=RATE(25,-1600,20000)運算結(jié)果為6.24%實驗一習題（資金時間價值）1.在15年前由于A企業(yè)的發(fā)展遇到一些問題，急需大筆資金，王先生借給A企業(yè)150萬元現(xiàn)金用于發(fā)展生產(chǎn)，考慮到風險水平，雙方約定的利率為14％，A企業(yè)可以選擇在15年內(nèi)的任意時間點還錢，但是該企業(yè)一直沒有足夠的資金償還，本年度是A企業(yè)還款的最后一年，A企業(yè)終于準備還款，請計算還款額。=FV（14%，15，0，-1500000）=10,706,906.972.如果你突然收到一張事先不知道的1255億美元的賬單，你一定會大吃一驚。而這件事卻發(fā)生在瑞士的田納西鎮(zhèn)的居民身上。該問題源于1966年的一筆存款。斯蘭黑不動產(chǎn)公司在田納西鎮(zhèn)的一個銀行存入6億美元的存款。存款協(xié)議要求銀行按1%的周利率復利付息（難怪該銀行第二年破產(chǎn)?。?994年，紐約布魯克林法院做出判決：從存款日到田納西鎮(zhèn)對該銀行進行清算的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.王先生準備與朋友投資一個新項目，按照規(guī)定，王先生提供項目所需的150萬元資金，但是不享受公司股份，只享受未來公司給其分配的固定收益，收益額為每年30萬元，期限為8年，已知王先生的如果不做這個項目，可以獲得的最低投資報酬率為18％，問王先生是否該投資此項目。=RATE(8,300000,-1500000)=12%5.基金中有一種購買方式為定投型基金，是約定銀行每月按期定額從制定帳戶扣除現(xiàn)金用于購買基金的一種產(chǎn)品。如果A家庭現(xiàn)在開始使用定投方式購買基金，月投入額為1500元，問15年后可以取出多少錢。（基金長期的年投資報酬率假設為24％）=FV(2%,180,-1500,0)=2,574,062.35（要換算成每月的年金來算，不可化成每年來算，15年共有15*12=180個月，月報酬率為24%/12=2%）6.小王準備儲存40萬元用于子女將來的出國費用，現(xiàn)在開始向銀行存款，已知存款的利率為7.85％，如果每年存款額為3萬元，問幾年小王可以達到40萬的存款額？=-NPER(7.85%,30000,400000)=9.487.如果你手中擁有100萬元的現(xiàn)金，有人愿意向你借錢，其承諾未來10年每年還款17萬元，而同期你可以獲得的年平均收益率為12％（一分利），不考慮借款人的風險差異，問是否應該借錢給他？=RATE(10,170000,-1000000)=11%8再參考年金終值系數(shù)表編制一個先付年金終值系數(shù)表(1)橫軸為利率1％——10％,縱軸為年限1年——30年(2)再參考年金現(xiàn)值系數(shù)表編制一個先付年金現(xiàn)值系數(shù)表橫軸為利率1％——10％,縱軸為年限1年——30年實驗二股票籌資分析、債券籌資分析、長期借款籌資分析和租賃籌資分析一、實驗目的掌握各種籌資方式的優(yōu)缺點，利用EXCEL函數(shù)PMT、IPMT、PPMT及電子表格軟件計算貸款的每年償還額、償還的利息和本金；利用EXCEL函數(shù)及電子表格處理軟件進行籌資方式?jīng)Q策分析。二、實驗原理1．股票籌資時，股票價格=每年股利/（投資報酬率-股利固定增長率）或者=未來形成的現(xiàn)金股利現(xiàn)值之和，或者=每股利潤*市盈率2.債券籌資時，要注意分清不同利息支付時間下債券價值的計算，債券的價值=未來每年支付的利息現(xiàn)值之和+票面金額現(xiàn)值3.長期借款籌資的還款方式有：一次償還法，等額本金法，等額分期付款等（1）一次性償付————較簡單，自學（2）等額本金法————每期償還等額本金，并按照借款余額計算利息（3）等額分期付款————每期償還相等數(shù)額款項（本金和利息之和），則每期的付款額現(xiàn)值之和應該等于借款數(shù)額基于固定利率和等額分期付款方式的每期相等還款額：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)在長期借款分析時，要考慮到借款支付的利息具有減稅效應，應該根據(jù)實際每期支付額計算企業(yè)借款所發(fā)生的所有成本（考慮貨幣時間價值）4．租賃籌資每期應付租金的計算方法：（1）平均分攤法：以事先約定的利率和手續(xù)費率計算出租賃期間的利息和手續(xù)費，然后連同租賃資產(chǎn)的成本按支付租金的次數(shù)平均分攤每期的租金。每期應付租金R=[(C-S)+I+F]/NC——租賃資產(chǎn)的購置成本S——出租人回收的租賃資產(chǎn)的殘值I——租賃期間的利息F——租賃的手續(xù)費N——租期（2）等額年金法：按期數(shù)每期等額支付租金三、實驗內(nèi)容（一）股票籌資分析1．實驗資料：甲乙丙三家公司的有關資料如下：甲公司準備發(fā)行6000萬股普通股，預計第一年發(fā)放股利2元/股，以后股利將以5%的比率穩(wěn)定增長，股東要求的投資報酬率為15%。乙公司準備發(fā)行5000萬股普通股，預計第一年發(fā)放股利1元/股，第2至5年中股利將以8%的比率增長，5年以后股利將以3%的比率穩(wěn)定增長，股東要求的投資報酬率為12%。丙公司原有普通股5000萬股，現(xiàn)擬于當年4月1日增發(fā)3000萬股新普通股，預計發(fā)行股票當年公司的盈利為4500萬元，根據(jù)行業(yè)平均市盈率水平測算，發(fā)行市盈率確定為20倍。問：不考慮承銷費用時三家公司股票的發(fā)行價格應為多少？如果證券承銷商對承銷股票業(yè)務按股票發(fā)行總額收取5%的承銷費用，三家公司股票的零售發(fā)行價格應為多少？2．分析步驟：ABCD1甲股票乙股票丙股票2現(xiàn)有普通股50003增發(fā)新股（萬股）6000500030004第2至5年股利增長率8%5第1年預計股利（元/股）216第2年預計股利（元/股）7第3年預計股利（元/股）8第4年預計股利（元/股）9第5年預計股利（元/股）10股利穩(wěn)定增長率5%3%11股東要求的投資報酬率15%12%12發(fā)行股票當年預計盈利（萬元）450013發(fā)行市盈率2014發(fā)行股票承銷費率5%5%5%15不考慮承銷費時股票的發(fā)行價格（元/股）20.0012.9912.4116考慮承銷費時股票的發(fā)行價格（元/股）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．實驗資料：這里以每年付息一次,到期一次還本的債券為例子某公司擬發(fā)行兩種期限不同的債券，M債券的期限5年，N債券的期限10年。兩種債券面值均為1000元，票面利率均為10%，每年末付息一次，到期一次還本。當市場利率分別為6%、8%、10%、12%和14%的情況下，兩種債券的價值分別是多少？試根據(jù)計算結(jié)果對債券發(fā)行價格進行分析。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的年金現(xiàn)值之和為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）結(jié)果分析：選取單元格區(qū)域B8：F10，單擊工具欄上的[插入]按鈕，在[圖表]中選取[XY散點圖]，然后按照圖標向?qū)υ捒蛱崾镜?個步驟依次操作，并對圖表進行格式化，即可得到M和N兩種債券的價值與市場利率之間的關系圖，如圖所示。由圖的計算結(jié)果以及債券價值與市場利率之間的關系圖可以看出，隨著市場利率的升高，M和N兩種債券的價值都會逐漸降低，即債券的價值與市場利率之間成反方向變化的關系。但是期限長的N債券價值的變動比期限短的M債券價值的變動受市場利率的影響更大，即當市場利率升高時，N債券價值下降的幅度大于M債券；反之，當市場利率降低時，N債券價值增加的幅度也大于M債券。當市場利率等于債券的票面利率時，兩種債券的價值都等于其面值，此時債券應等價發(fā)行；當市場利率低于債券的票面利率時，兩種債券的價值都高于其面值，此時債券應溢價發(fā)行；當市場利率高于債券的票面利率時，兩種債券的價值都低于其面值，此時債券應折價發(fā)行。（3）習題：每半年付息一次、到期一次還本的債券a．實驗資料：某公司正在考慮發(fā)行X債券或Y債券。兩種債券的面值均為1000元，期限均為5年，均為每半年付息一次，到期一次還本。X債券的票面利率為8％，Y債券的票面利率為12％。市場利率10％。這兩種債券目前的發(fā)行價格應是多少？ABCDEFG1債券資料2X債券Y債券3債券面值（元）100010004票面利率8%12%5期限（年）556每年付息次數(shù)（次）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）解釋：上述公式實質(zhì)為＝PV(5%,0*2,-1000*8%/2,-1000)其中，5%為實際折現(xiàn)率，0*2表示折現(xiàn)期數(shù)，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）選擇單元格區(qū)域B10:G12,單擊工具欄上的[插入]按鈕，在[圖表]中選取[XY散點圖]，然后按照圖標向?qū)υ捒蛱崾镜?個步驟依次操作，并對圖表進行格式化，即可得到M和N兩種債券的價值與市場利率之間的關系圖如圖所示。（三）長期借款籌資分析1．實驗資料：某企業(yè)向銀行借款500萬元，年利率為8％，期限6年，借款籌資的手續(xù)費為2萬元。所得稅稅率33％，現(xiàn)有兩種還本付息方式：（1）方案A，每年末等額還本付息；（2）方案B，前2年每年末償還本金50萬元，并每年末支付按每年初借款余額計算的相應利息，其余的款項在后4年中每年末等額還本付息。問：企業(yè)應選擇哪種還本付息方式？分析：這個題目應該比較兩個方案的稅后成本代價現(xiàn)值來判斷。2．分析步驟ABCDEFGHI1借款額借款利率期限金額單位：萬元25008％62345各年還本付息（萬元）成本代價現(xiàn)值6年份1234567方案A還本8付息9利息抵稅10成本代價11方案B還本12付息13利息抵稅14成本代價1516結(jié)論：（1）方案A的計算：選取單元格C7:H7,點擊最上方的單元格函數(shù)輸入?yún)^(qū)域，輸入數(shù)組公式＝PPMT(B2,C6:H6,C2,-A2)，然后同時按Ctrl+Shift+Enter，就可以得到單元格C7:H7區(qū)域內(nèi)的數(shù)據(jù)。下面也是一樣。選取單元格C8:H8,輸入數(shù)組公式＝IPMT(B2,C6:H6,C2,-A2)選取單元格C9:H9,輸入數(shù)組公式＝C8:H8*33%選取單元格C10:H10,輸入數(shù)組公式＝C7:H7+C8:H8-C9:H9在單元格I7中輸入公式＝NPV(B2,C10:H10)+D2（2）方案B的計算：在單元格C11和D11中分別輸入50選取單元格E11:H11,輸入數(shù)組公式=PPMT(B2,E6:H6-2,H6-D6,-(A2-SUM(C11:D11)))在單元格C12中輸入公式＝A2*B2在單元格D12中輸入公式＝（A2-C11）*B2選取單元格E12:H12,輸入數(shù)組公式=IPMT(B2,E6:H6-2,H6-D6,-(A2-SUM(C11:D11)))選取單元格C13:H13,輸入數(shù)組公式＝C12:H12*33%選取單元格C14:H14,輸入數(shù)組公式＝C11:H11+C12:H12-C13:H13在單元格I11中輸入公式＝NPV(B2,C14:H14)+D2（3）在單元格B16中輸入公式=IF(I7<I11,"方案A","方案B")結(jié)果顯示方案B成本更低，應選擇方案BABCDEFGHI1借款額借款利率期限籌資費用金額單位均為：萬元25008％62345各年還本付息（萬元）成本代價現(xiàn)值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結(jié)論：方案B請同學們自行計算所得稅稅率改為20%，B方案的前兩年本金付款額為80萬元，看下這個時候應該選擇哪個方案？經(jīng)計算A方案現(xiàn)值為477.73萬元，B方案現(xiàn)值為478.45萬元。選擇A方案。結(jié)果如下：各年還本付息（萬元）成本代價現(xiàn)值年份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兩個方案的現(xiàn)值是相等的。答案應該為70.40萬元，當然也可以用“單變量求解”來做，但是計算量比較大。單變量設置的函數(shù)為：=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函數(shù)在使用的時候，還可以考慮到兩者相等的情況，表達為=IF(I7<I11,"方案A",IF(I7=I11,＂AB均可＂,"方案B"))，這里的"方案B"也可以用前面的單元格A11代替。（四）.租賃籌資分析1．實驗資料某公司2004年1月1日從租賃公司租入一套設備，價值200萬元，租期6年，租金每年末支付一次。①若預計租賃期滿的殘值為3萬元，殘值歸租賃公司所有，年利率為8％，租賃手續(xù)費率為設備價值的2％，按平均分攤法計算每期租金。②若設備殘值歸承租公司，綜合租費率確定為10％，按等額年金法確定每年末支付的租金。2．分析步驟在單元格B6中輸入公式=((B2－B3）+(B2*(1+B4)^D2-B2)+B2*B5)/D2在單元格D8中輸入公式=PMT(D4,D2,-B2)ABCD1年租金的計算2租賃資產(chǎn)價值200租賃期限63殘值3每年支付租金時點年末4年利率8％綜合租費率10％5手續(xù)費率2％6平均分攤法年租金53.06等額年金法年租金45.92這里面的等額年金不僅考慮了設備款，還考慮了殘值給企業(yè)成本帶來的減值效應。如果這里面要求計算支付的現(xiàn)金多少，應在單元格D8中輸入公式=PMT(D4,D2,-B2)，結(jié)果為45.92（五）用日線數(shù)據(jù)繪制個股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，點擊插入圖表，選擇“股價圖”，選擇“子圖表類型”，根據(jù)已知資料選擇第四個圖表格式，點下一步，直到出現(xiàn)標題，輸入“股價圖”，x軸為“日期”，y軸為“成交額”，次x軸不輸入，次y軸為股價。然后完成，完成后可以對圖表做適當修改，比如縱軸刻度的最小值和最大值設置，陰線和陽線的顏色。結(jié)果如下：如果沒有成交量得到的結(jié)果為（選股價第二種圖格式）：實驗三籌資預測與決策一、實驗目的通過實驗，使學生了解資金預測時需要考慮的因素和主要方法，能夠使用EXCEL軟件中的SLOPE函數(shù),INTERCEPT函數(shù)進行回歸分析，能利用EXCEL進行本量利分析，計算資本成本率、財務杠桿系數(shù)和營業(yè)杠桿系數(shù)。二、實驗原理1.資本需求量的預測：在利用回歸分析法進行預測時，是按照歷史數(shù)據(jù)，按照數(shù)學上最小平方法的原理，確定能夠正確反映自變量和因變量之間具有最小誤差平方和的回歸方程作為預測模型進行預測的方法。主要使用的函數(shù)有：SLOPE函數(shù),INTERCEPT函數(shù)。（LINEST為另外一種計算斜率、截距和相關系數(shù)的函數(shù)）SLOPE函數(shù)：=SLOPE(known_y’s,known_x’s)計算線性回歸直線的斜率INTERCEPT函數(shù)：=INTERCEPT(known_y’s,known_x’s)計算直線與y軸的截距PEARSON函數(shù)：=PEARSON(array1,array2)計算y和x的相關系數(shù)r，也可以使用另外一個函數(shù)=INDEX(LINEST)計算計算y和x的相關系數(shù)r2，相對復雜，具體使用方法參考后面的實驗。LINEST函數(shù)和后面一元二次回歸方法一樣，可以一次計算a和b的數(shù)值，INDEX是在所計算的結(jié)果中去定位具體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）總成本＝變動成本＋固定成本＝單位變動成本×產(chǎn)銷量＋固定成本（2）邊際貢獻＝銷售收入－變動成本＝（單價－單位變動成本）×產(chǎn)銷量（3）邊際貢獻率＝邊際貢獻/銷售收入（4）變動成本率＝變動成本/銷售收入（5）息稅前利潤＝銷售收入－變動成本－固定成本＝邊際貢獻－固定成本（6）保本點銷售量＝固定成本/（單價－單位變動成本）（7）保本點銷售額＝保本點銷售量×單價＝固定成本/邊際貢獻率4.經(jīng)營杠桿公式：DOL=(S-C)/(S-C-F)5.財務杠桿公式：DFL=EBIT/[EBIT-I-DP/(1-T)]，若無優(yōu)先股，DFL=EBIT/（EBIT-I）DP——優(yōu)先股股利6.籌資決策：（1）比較資本成本法：選擇資本成本率最低的籌資方式（2）每股利潤分析法：通常計算每股利潤無差別點，當息稅前利潤=每股利潤無差別點時兩種籌資方式均可；當息稅前利潤>每股利潤無差別點時，選擇發(fā)行債券或者長期借款籌資方式；當息稅前利潤<每股利潤無差別點，選擇發(fā)行普通股籌資方式（3）最佳資本結(jié)構(gòu)分析三、實驗資料1.資金需要量預測案例一、一元回歸分析某企業(yè)在過去5年中銷售收入與資金占用量的有關數(shù)據(jù)如圖所示，則利用回歸分析法預測資金需要量的具體步驟如下（假設銷售收入與資金占用量之間為線性關系）：（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)”,計算系數(shù)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)”,計算相關系數(shù)r2，相關系數(shù)達0.9503，故資金占用量與銷售收入之間的線性關系較為顯著，可以利用直線回歸方程進行預測；（由于本題已經(jīng)假設y和x存在線性關系，本步驟可以省略）也可以使用PEARSON函數(shù)“=PEARSON(C3:C7,B3:B7)”，得到的結(jié)果為r，有別于上面計算的結(jié)果r2。（4）在單元格B15中輸入公式“=B10+B11*B14”ABC1已知條件（金額單位：萬元）2年度銷售收入(x)資金總額(y)31999256126420002711395200126413262002295148720033161548回歸方程有關參數(shù)的計算9回歸方程y=a+bx10a=11b=12r=132004年資金需要量預測（萬元）14預計銷售收入32715預計資金需要量通常在相關系數(shù)在0.8以上時，就可以認為自變量和因變量的相關關系成立，否則相關關系不成立，可以采用線性回歸分析法，否則只能采用其他方法。實驗結(jié)果：ABC1已知條件（金額單位：萬元）2年度銷售收入(x)資金總額(y)31999256126420002711395200126413262002295148720033161548回歸方程有關參數(shù)的計算9回歸方程y=a+bx10a=13.3411b=0.4512r=97.49%132004年資金需要量預測（萬元）14預計銷售收入32715預計資金需要量160.82當然還有很多其他的函數(shù)可以進行預測分析，例如forecast函數(shù)，trend函數(shù)如上題使用forecast函數(shù)可以不需要計算斜率和截距，直接計算當銷售收入等于327的時候?qū)馁Y金需要量，B15單元格輸入內(nèi)容：=forecast(B14,C3:C7,B3:B7)Trend函數(shù)和forecast函數(shù)類似，也不需要計算斜率和截距，直接計算當銷售收入等于327的時候?qū)馁Y金需要量，B15單元格輸入內(nèi)容：=trend(C3:C7,B3:B7,B14)下面解釋下INDEX和LINEST函數(shù)的意思。首先要在A11：C11或者A11：C14計算系列結(jié)果。可以看到結(jié)果其實只需要一行即可，下面都是重復數(shù)據(jù)，因此系列結(jié)果默認為一行。如果求斜率，其應該位于數(shù)列的第1行，第1個數(shù)，用index的時候后面寫的定位為1，1，如果是截距定位為1，2，即第一行第二個數(shù)，當然第一行的1可以省略，因為有效數(shù)據(jù)只有一行。ABC1已知條件（金額單位：萬元）2年度銷售收入(x)資金總額(y)31999256126420002711395200126413262002295148720033161548回歸方程有關參數(shù)的計算9回歸方程y=a+bx10b=A11a=B11110.45101113.33651159#N/A120.45101113.33651159#N/A130.45101113.33651159#N/A140.45101113.33651159#N/A如果希望求相關系數(shù)r2，就得在計算數(shù)組的時候使用LINEST的標準格式公式。要在A11：C15輸入數(shù)組公式=LINEST(C3:C7,B3:B7,TRUE,TRUE)，r2位于第3行第1個數(shù)。因此上面題目是在B12中輸入公式“=INDEX(LINEST(C3:C7,B3:B7,,TRUE),3,1)”ABC1已知條件（金額單位：萬元）2年度銷售收入(x)資金總額(y)31999256126420002711395200126413262002295148720033161548回歸方程有關參數(shù)的計算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案例二、二元回歸分析某企業(yè)在本年度1-9月份總成本與機器工時、人工小時的有關數(shù)據(jù)如圖所示，則利用回歸分析法預測線性函數(shù)（y=a+bx1+cx2）的具體步驟如下（假設銷售收入與機器工時、人工小時之間為線性關系）：ABCD1月份總成本y機器工時x1人工小時x221100050100321120551284312306214554135070180651450752007615807823587166080250981800922801091900100300111012035012y=a+bx1+cx213c=A14b=B14a=C1414XXXXXX15XXXXXX16XXXXXX17XXXXXX1819r2=A16標準差=B162010月總成本由于是二元回歸分析，在13行以下預留不少于三行，三列空白單元格選擇多個預留空白單元格，輸入數(shù)組公式“=LINEST(B2:B10,C2:D10,TRUE,TRUE)”或者輸入“=LINEST(B2:B10,C2:D10,,1)”然后按CTRL+SHIFT+ENTER，生成如下數(shù)據(jù)，相應位置的數(shù)據(jù)會表示確定的變量值。需要注意x1和x2的位置在已知條件中和結(jié)果中處于不同的順序。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時的總成本，直接代入即可計算?；蛘呤褂肨REND函數(shù)：B20單元格輸入=TREND（B2：B10，C2：D10，C11：D11）當然二元回歸模型也可以用于一元回歸模型的計算，同學們可以自己嘗試。案例三、指數(shù)曲線模型分析指數(shù)曲線的數(shù)學模型為：在指數(shù)模型兩邊各取對數(shù)：設，則上式可改寫為：根據(jù)案例一的一元回歸分析，可以求得A、B。例：某企業(yè)產(chǎn)品銷售收入1999～2006年的實際市場銷售額如表所示，試對2007年的市場銷售額進行預測。（銷售額與年數(shù)存在指數(shù)關系）ABCDEF1年份年（x）銷售額（y）Y=lny2199914476610.713200025431310.904200136339711.065200247497111.226200359114211.4272004611871111.6882005714290611.8792006817660012.0810合計3676680690.951120079209780.2712A=10.48367B=0.196682相關系數(shù)R=0.997813a=eA35727.41b=eB1.22在這里lny可以首先計算出來，輸入公式“=ln（數(shù)值）”，看成Y，此時年數(shù)x仍然不變，據(jù)此可以對兩列數(shù)據(jù)一元回歸分析，結(jié)果如下：A，B，R的數(shù)值。ln函數(shù)---取自然對數(shù)，例如輸入“=ln（100）”會得到4.61，即e^4.61=100，e=2.71828182845log函數(shù)--給定底數(shù)返回數(shù)字的對數(shù)，例如輸入“=log（1000，10）”得到結(jié)果3exp函數(shù)--e的n次方，例如輸入“=EXP(10.48367)”結(jié)果為35727.29因此a=e10.48367=35727.29b=e0.196682=1.22因此指數(shù)曲線數(shù)學模型為y=35727.29(e0.196682)x=35727.29*1.22x因此2007年銷售=35727.29*1.229=209780.272.資本成本的計算實驗資料：某公司擬籌資4000萬元，其中按面值發(fā)行長期債券1000萬元，票面利率10%，期限4年，籌資費率1%，每年末付息一次，到期一次還本；長期借款600萬元，3年期，年利率11%；發(fā)行普通股2000萬元，籌資費率4%，預計第一年股利率12%，以后每年按照4%的增長率遞增。此外，公司保留盈余400萬元，所得稅稅率33%。要求：計算該公司的綜合資本成本。實驗步驟（1）創(chuàng)建工作表，如下表所示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單元格區(qū)域內(nèi)輸入原始數(shù)據(jù)。（3）參考下表所示，利用相應公式在上表中進行計算。單元格公式或函數(shù)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）計算結(jié)果，如下表所示。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萬不變，直到第十年，公司運營結(jié)束，求資本成本率。（不考慮公司到期后公司剩余資產(chǎn)）創(chuàng)建表格如下：ABCD1籌資額1000前五年股利固定增長率10%2籌資費率3%后五年股利額2003第一年股利率10%4期數(shù)股利假設資本成本率凈現(xiàn)值517%628%739%8410%9511%10612%117128139141015假設法下目標資本成本率16單變量求解：17凈現(xiàn)值18可變單元格根據(jù)題目意思可得到第1年到第10年每年的股利額，分別填入B5：B14假設資本成本率分別為7%，8%，9%，10%，11%，12%可以得到對應的凈現(xiàn)值，例如D5單元格輸入：=NPV(C5,B5:B14)-B1*（1-B2）分析結(jié)果可知資本成本率應該在9%和10%之間，此時凈現(xiàn)值分別為2.81和-44.70采用差值法可的目標資本成本率，在單元格B15輸入：=C7+D7/(D7-D8)*1%如果采用單變量求解，在單元格B17輸入：=NPV(B18,B5:B14)-B1*（1-B2）可變單元格B18即是所求的資本成本率，采用單變量求解令B17單元格等于0，可變單元格為B18可得資本成本率為9.06%。實驗結(jié)果：ABCD1籌資額1000前五年股利固定增長率10%2籌資費率3%后五年股利額2003第一年股利率10%4期數(shù)股利假設資本成本率凈現(xiàn)值511007%108.91621108%53.90731219%2.8184133.110%-44.7095146.4111%-88.9310620012%-130.14117200128200139200141020015假設法下目標資本成本率9.06%此處是差值法得到的結(jié)果16單變量求解：此處是直接求解得到結(jié)果更準確17凈現(xiàn)值0.0018可變單元格9.06%3.杠桿作用分析1）本量利之間的關系例1：某公司僅生產(chǎn)單一的甲產(chǎn)品，單位變動成本為200元/件，單價為360元/件，年固定經(jīng)營成本為80000元，要求計算公司保本點的銷售量和銷售額；如果下一年預計的銷售量分別是100、200、300、400、500、600、700、800件，公司的息稅前利潤分別是多少，并根據(jù)該數(shù)據(jù)繪制公司的保本分析圖。計算過程：a．在單元格B6中輸入公式“＝B4/(B2-B3)”;在單元格B7中輸入公式“＝B6*B2b．選取單元格區(qū)域B10:I10,輸入公式“＝B9:I9*B2”;選取單元格區(qū)域B11:I11,輸入公式“＝B9:I9*B3+B4”，得到總成本；選取單元格區(qū)域B12:I12,輸入公式“＝B10:I10-B11:Ic．選取單元格區(qū)域B9:I12，單擊工具欄上的[插入]按鈕，在[圖表]中選取[XY散點圖]，然后按照圖標向?qū)υ捒蛱崾镜?個步驟依次操作，并對圖表進行格式化和調(diào)整，即可得到保本分析圖。ABCDEFGHI1已知條件2單價（元/件）3603單位變動成本（元/件）2004年固定成本（元）800005保本點的計算6保本點銷售量7保本點銷售額（元）8息稅前利潤的計算9產(chǎn)銷量10020030040050060070080010銷售收入（元）11總成本（元）12息稅前利潤（元）結(jié)果如下：ABCDEFGHI1已知條件2單價（元/件）3603單位變動成本（元/件）2004年固定成本（元）800005保本點的計算6保本點銷售量5007保本點銷售額（元）1800008息稅前利潤的計算9產(chǎn)銷量10020030040050060070080010銷售收入（元）360007200010800014400018000021600025200028800011總成本（元）10000012000014000016000018000020000022000024000012息稅前利潤（元）-64000-48000-32000-160000160003200048000保本分析圖如下：例2：某公司只產(chǎn)銷一種產(chǎn)品，根據(jù)下列已知條件求相應的未知數(shù)：a.當銷售收入為250000元，邊際貢獻率35％，息稅前利潤18000元，求固定成本和變動總成本。b.銷售收入460000元，變動總成本320000元，固定成本125000元，求邊際貢獻率和息稅前利潤。c.邊際貢獻率20％，固定成本50000元，息稅前利潤-1000元，求銷售收入和變動總成本。d.銷售收入500000元，變動總成本360000元，息稅前利潤45000元，求邊際貢獻率和固定成本。請同學自己設計表格，并在表格中運用函數(shù)計算結(jié)果。2）經(jīng)營杠桿例：某公司只生產(chǎn)和銷售一種產(chǎn)品，單價500元/件，單位變動成本300元/件，該公司沒有負債，每月銷售額75萬元，稅后凈利潤4.8萬元，公司的所得稅稅率40％，計算：公司每月的固定經(jīng)營成本，每月的保本點銷售量和銷售額；在每月的銷售量分別為300、600、900、1200、1500、1800、2100、2400件時的經(jīng)營杠桿系數(shù)（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，得到經(jīng)營杠桿系數(shù)。ABCDE1已知條件經(jīng)營杠桿系數(shù)的計算2單價500月銷售量經(jīng)營杠桿系數(shù)3單位變動成本3003004月銷售額7500006005凈利潤480009006所得稅稅率40％12007保本點計算15008月固定成本18009保本點銷售量210010保本點銷售額2400結(jié)果如下：ABCDE1已知條件經(jīng)營杠桿系數(shù)的計算2單價500月銷售量經(jīng)營杠桿系數(shù)3單位變動成本300300-0.384月銷售額750000600-1.205凈利潤48000900-4.506所得稅稅率40％120012.007保本點計算15003.758月固定成本22000018002.579保本點銷售量110021002.1010保本點銷售額55000024001.853）財務杠桿例題：某公司現(xiàn)有普通股200萬股，資金總額500萬元，負債比率40％，負債利率8％，優(yōu)先股年股息4萬元，當年息稅前利潤60萬元，所得稅稅率33％，計算：當年的普通股每股利潤，當年的財務杠桿系數(shù)；預計下一年度息稅前利潤將增長20％，則普通股每股利潤的增長率是多少？預計的普通股每股利潤是多少？ABCD1已知條件2普通股股數(shù)2000000優(yōu)先股年股息400003資金總額5000000息稅前利潤6000004負債比率40％所得稅稅率33％5負債利率8％預計息稅前利潤增長率20％67每股利潤和財務杠桿系數(shù)的計算8普通股每股利潤普通股每股利潤增長率9財務杠桿系數(shù)普通股每股利潤（增長）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結(jié)果如下：ABCD1已知條件2普通股股數(shù)2000000優(yōu)先股年股息400003資金總額5000000息稅前利潤6000004負債比率40％所得稅稅率33％5負債利率8％預計息稅前利潤增長率20％67每股利潤和財務杠桿系數(shù)的計算8普通股每股利潤0．13普通股每股利潤增長率31．55%9財務杠桿系數(shù)1．58普通股每股利潤（增長）0．174.籌資決策方法1）比較資本成本法：以初始籌資決策為例例題：某企業(yè)初創(chuàng)時需要資金1000萬元，有三個備選籌資方案，有關資料如下，請選擇最優(yōu)籌資方案。ABCDEFGHIJ1初始籌資決策（金額單位：萬元）2籌資方案方案A方案B方案C3籌資額比重資本成本籌資額比重資本成本籌資額比重資本成本4長期借款2006%3006.5%4007%5長期債券2007%1007%00%6優(yōu)先股20015%10015%20015%7普通股40012%50012%40012%8合計910最優(yōu)方案步驟：分別計算方案A、B、C的籌資額所占比重，并據(jù)此計算綜合資本成本率，根據(jù)綜合資本成本率選擇最佳籌資方案。單元格輸入函數(shù)：單元格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優(yōu)先股20020%15%10010%15%20020%15%7普通股40040%12%50050%12%40040%12%8合計1000100%10.40%1000100%10.15%1000100%10.60%910最優(yōu)方案方案B附加：sumif函數(shù)介紹某公司有三個業(yè)務員小張、小王和小劉，某年的單次業(yè)務量如下表，要求計算每人的匯總后年銷量。ABCDE1已知資料統(tǒng)計結(jié)果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函數(shù)介紹運用舉例1、返回包含值12的單元格數(shù)量=COUNTIF（DATA,12）2、返回包含負值的單元格數(shù)量=COUNTIF（DATA，"<0"）3、返回不等于0的單元格數(shù)量（只要單元格內(nèi)容不是0就要統(tǒng)計）=COUNTIF（DATA，"<>0"）4、返回大于5的單元格數(shù)量=COUNTIF（DATA，">5"）5、返回等于單元格A1中內(nèi)容的單元格數(shù)量=COUNTIF（DATA，A1）6、返回大于單元格A1中內(nèi)容的單元格數(shù)量=COUNTIF（DATA，">''&A1）7、返回包含文本內(nèi)容的單元格數(shù)量=COUNTIF（DATA,”*”）8、返回包含三個字符內(nèi)容的單元格數(shù)量=COUNITF（DATA，''???''）9、返回包含單詞"GOOD"(不分大小寫)內(nèi)容的單元格數(shù)量=COUNTIF（DATA,''GOOD''）10、返回在文本中任何位置包含單詞"GOOD"字符內(nèi)容的單元格數(shù)量=COUNTIF（DATA,"*GOOD*"）11(1)、返回包含以單詞"AB"(不分大小寫)開頭內(nèi)容的單元格數(shù)量=COUNTIF（DATA,"AB*"）11(2)、返回包含以單詞"AB"(不分大小寫)結(jié)尾內(nèi)容的單元格數(shù)量=COUNTIF（DATA,"*AB"）12、返回包含當前日期的單元格數(shù)量=COUNTIF（DATA，TODAY()）13、返回大于平均值的單元格數(shù)量=COUNTIF（DATA，">"&AVERAGE(DATA))14、返回平均值上面超過三個標準誤差的值的單元格數(shù)量=COUNTIF（DATA,">"&AVERAGE(DATA)+STDEV(DATA)*3)15、返回包含值為3或-3的單元格數(shù)量=COUNTIF（DATA，3）+COUNIF（DATA，-3）16、返回包含值;邏輯值為TRUE的單元格數(shù)量=COUNTIF（DATA，TRUE）17、統(tǒng)計區(qū)域中不為空的單元格個數(shù)（數(shù)值、文本、空格都算）——（上述第3條：文本也算不等于0，空格不算）只要單元格有內(nèi)容，即使是空格都要統(tǒng)計=Countif(DATA,"<>")18、只統(tǒng)計文本單元格數(shù)量，不統(tǒng)計數(shù)值和空格——（上述第7條統(tǒng)計含空格）=COUNTIF(DATA,"><")具體舉例AB1資料資料2蘋果323橘子544水蜜桃755蘋果86公式敘述結(jié)果=COUNTIF(A2:A5,"蘋果")上述第一欄是蘋果的儲存格數(shù)目2=COUNTIF(B2:B5,">55")以上第二欄中的數(shù)值大于55的儲存格數(shù)目22）每股利潤分析法例題：某企業(yè)現(xiàn)有資本800萬元，其中債務200萬元，股東權益600萬元，該公司準備實施一項投資計劃，為此需要追加籌資200萬元，現(xiàn)有A、B兩個備選的籌資方案，有關資料如圖，公司所得稅稅率為33％，計算：若投資計劃實施后預計的息稅前利潤分別為40、60、80、100、120、140、160、180萬元時，兩個籌資方案的普通股每股利潤各是多少？兩個籌資方案的普通股每股利潤無差別點是多少？根據(jù)第一問繪制普通股每股利潤與息稅前利潤之間的關系圖，并作出決策。ABCDEFGHI1已知條件2現(xiàn)有資本結(jié)構(gòu)追加籌資方案3股東權益6000000普通股股數(shù)600000方案A4債務2000000債務利率8％股票籌資額2000000新增股數(shù)2000005現(xiàn)有資本8000000方案B6所得稅稅率33％債務籌資額2000000新增利率10％789計算過程10預計息稅前利潤4000006000008000001000000120000014000001600000180000011方案A每股利潤12方案B每股利潤13最優(yōu)籌資方案1415目標函數(shù)：方案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)”結(jié)果如下：ABCDEFGHI1已知條件2現(xiàn)有資本結(jié)構(gòu)追加籌資方案3股東權益6000000普通股股數(shù)600000方案A4債務2000000債務利率8％股票籌資額2000000新增股數(shù)2000005現(xiàn)有資本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最優(yōu)籌資方案A方案A方案A方案B方案B方案B方案B方案B方案1415目標函數(shù)：方案A-方案B016無差別點的息稅前利潤96000017無差別點的每股利潤0．67每股利潤與息稅前利潤分析圖如下：3）最佳資本結(jié)構(gòu)分析略實驗四內(nèi)部長期投資決策一、實驗目的掌握各種折舊方法的特點，掌握投資決策指標的計算方法，能利用EXCEL函數(shù)SLN、DB、DDB、SYD、NPV、IRR等函數(shù)計算每期的折舊額、每期的現(xiàn)金流量和投資決策相關指標，并根據(jù)結(jié)果進行投資決策分析。二、實驗原理1.固定資產(chǎn)折舊直線折舊法公式：SLN（cost,salvage,life）加速折舊法：余額遞減法DB(cost,salvage,life,period,month)month為第一年的月份數(shù)，若省略則為12雙倍余額遞減法DDB(cost,salvage,life,period,factor)年數(shù)總和法SYD(cost,salvage,life,per)2.現(xiàn)金流量的計算初始現(xiàn)金流量＝-（固定資產(chǎn)投資+流動資產(chǎn)投資+其他投資）+舊固定資產(chǎn)變價凈收入舊固定資產(chǎn)變價凈收入主要指更新固定資產(chǎn)時變賣原有資產(chǎn)所得凈收入營業(yè)現(xiàn)金流量＝凈利潤+折舊終結(jié)現(xiàn)金流量＝殘值+流動資產(chǎn)回收額+該年其他收入建設期內(nèi)為購建固定資產(chǎn)發(fā)生的借款利息按會計制度應該資本化處理，但從企業(yè)全部投資者角度對項目進行評價，資本化的利息費用不能作為現(xiàn)金流量計算。但是資本化利息應該計入固定資產(chǎn)原值，進而可以求出折舊額。3.投資決策指標的計算投資回收期（PP）每年現(xiàn)金流量相等的時候：投資回收期＝原始投資額/每年現(xiàn)金流量當每年現(xiàn)金流量不等的時候：投資回收期＝（累計現(xiàn)金流量開始出現(xiàn)正值的年份－1）+︱上年尚未回收的投資額︱/該年凈現(xiàn)金流量平均投資報酬率（ARR）ARR=∑NCFt/N/I凈現(xiàn)值（NPV）=未來現(xiàn)金流入量現(xiàn)值之和-未來現(xiàn)金流出量現(xiàn)值之和或者NPV=∑NCFt/（1+i）t-I內(nèi)部報酬率（IRR）NPV=∑NCFt/（1+ARR）t＝0獲利指數(shù)PI＝(NPV+I)/I=∑NCFt/（1+i）t/I4.最小公倍壽命法是使投資項目的壽命周期相等的方法，也就是要求初兩個項目使用年限的最小公倍數(shù)。年均凈現(xiàn)值法是把項目的凈現(xiàn)值轉(zhuǎn)化為項目每年的平均凈現(xiàn)值。年均凈現(xiàn)值(ANPV)計算公式：ANPV=NPV/(P/A,i,n)三、實驗內(nèi)容1.固定資產(chǎn)折舊的計算1）直線折舊法：包括平均年限法和工作量法平均年限法：年折舊額＝（固定資產(chǎn)原值－預計凈殘值）/預計使用年限工作量法：每單位工作量折舊額＝（固定資產(chǎn)原值－預計凈殘值）/預計總工作量某期折舊額＝該期實際工作量*每單位工作量折舊額例題：某企業(yè)購置了一臺設備，價格10萬元，預計使用5年，預計凈殘值率5％。a．按平均年限法計提折舊時，每年的折舊額是多少？b．如果該設備在預計使用年限內(nèi)可以運轉(zhuǎn)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)”，然后向右復制內(nèi)容單元格B10：F10輸入“=SLN(B2,B2*B4,D4)*B9:F9”，結(jié)果如下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請同學們按照傳統(tǒng)的方法手工在EXCEL中驗算結(jié)果的正確性。2）加速折舊法：包括余額遞減法、雙倍余額遞減法和年數(shù)總和法余額遞減法：年固定折舊率＝1－