excel在財(cái)務(wù)中的應(yīng)用

1、Excel在財(cái)務(wù)中的高級應(yīng)用,主講內(nèi)容：,Excel在財(cái)務(wù)中的應(yīng)用基礎(chǔ)；,1、Excel在財(cái)務(wù)中的應(yīng)用基礎(chǔ)；,1.1、代碼化（數(shù)據(jù)標(biāo)準(zhǔn)化）1.2、日期、分?jǐn)?shù)的輸入1.3、引用的應(yīng)用1.4、數(shù)據(jù)清單1.5、工作表的保護(hù)及安全打開,1.1、代碼化（數(shù)據(jù)標(biāo)準(zhǔn)化）,以電子計(jì)算機(jī)為計(jì)算工具處理財(cái)務(wù)數(shù)據(jù)的基本概念。代碼化——對系統(tǒng)原始數(shù)據(jù)按一定的規(guī)律進(jìn)行編碼的處理。數(shù)據(jù)處理代碼化，縮短了數(shù)據(jù)項(xiàng)的長度，減少數(shù)據(jù)占用的存儲(chǔ)空間，提高會(huì)計(jì)數(shù)據(jù)處

2、理的速度和精度。方便計(jì)算機(jī)進(jìn)行排序、分類、匯總等操作。輸入方式：’+代碼；如：現(xiàn)金科目代碼輸入方式：’1001,1.2、日期、分?jǐn)?shù)的輸入,日期在EXCEL中其實(shí)質(zhì)為數(shù)字1900-1-1代表1分?jǐn)?shù)與日期輸入5/8在EXCEL中被識(shí)別為5月8日分?jǐn)?shù)5/8的輸入方式：0+空格+ 5/8,,1.3、引用的使用；,引用的作用在于標(biāo)識(shí)工作表上的單元格或單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的位置。通過引用，可以在公式中使用工作表不同部分的數(shù)

3、據(jù)，或者在多個(gè)公式中使用同一個(gè)單元格的數(shù)值。還可以引用同一個(gè)工作簿中不同工作表上的單元格和其他工作簿中的數(shù)據(jù)。引用不同工作簿中的單元格稱為鏈接。 相對引用絕對引用（混合引用）鏈接名稱,1.3、引用的使用；,相對引用--在創(chuàng)建公式時(shí)，單元格或單元格區(qū)域的引用通常是相對于包含公式的單元格的相對位置。隨公式位置的改變，其引用的單元格也會(huì)相應(yīng)發(fā)生變化。絕對引用--復(fù)制公式時(shí)Excel不調(diào)整引用，如$C$1。包括絕對引用單元格的公式，無

4、論將其復(fù)制到什么位置，總是引用特定的單元格。鏈接--引用不同工作簿中的單元格。每次按F4鍵時(shí)，Excel會(huì)在以下組合間切換：絕對列與絕對行（例如，$A$1）；相對列與絕對行(A$1)；絕對列與相對行($A1)以及相對列與相對行(A1)，當(dāng)切換到用戶所需的引用時(shí)，按回車鍵確認(rèn)即可。,1.4動(dòng)態(tài)引用,OFFSET函數(shù)可以對動(dòng)態(tài)數(shù)據(jù)進(jìn)行查詢，它以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個(gè)單元格或單元格區(qū)域，并可以

5、指定返回的行數(shù)或列數(shù)，其語法格式如下：OFFSET(reference,rows,cols,height,width)可以看出，該函數(shù)最多包含五個(gè)參數(shù)，后兩個(gè)參數(shù)為可選項(xiàng)。其中：Reference作為偏移量參照系的引用區(qū)域，它必須為對單元格或相連單元格區(qū)域的引用；否則，函數(shù) OFFSET 返回錯(cuò)誤值 #VALUE!。Rows  為相對于偏移量參照系左上角單元格，上（下）偏移的行數(shù)。Cols 為相對于偏移量參

6、照系左上角單元格，左（右）偏移的列數(shù)。Heigh 為高度，即所要返回的引用區(qū)域的行數(shù)。Width 為 寬度，即所要返回的引用區(qū)域的列數(shù)。,1.4、數(shù)據(jù)清單,在Excel 中，數(shù)據(jù)庫是作為一個(gè)數(shù)據(jù)清單來看待。我們可以理解數(shù)據(jù)清單就是數(shù)據(jù)庫。在一個(gè)數(shù)據(jù)庫中，信息按記錄存儲(chǔ)。每個(gè)記錄中包含信息內(nèi)容的各項(xiàng)，稱為字段。Excel 提供了一整套功能強(qiáng)大的命令集，使得管理數(shù)據(jù)清單（數(shù)據(jù)庫）變得非常容易。我們可以完成下列工作：&

7、#160;    排序——在數(shù)據(jù)清單中，針對某些列的數(shù)據(jù)，我們可以用數(shù)據(jù)菜單中的排序命令來重新組織行的順序?？梢赃x擇數(shù)據(jù)和選擇排序次序，或建立和使用一個(gè)自定義排序次序。     篩選——可以利用“數(shù)據(jù)”菜單中的“篩選”命令來對清單中的指定數(shù)據(jù)進(jìn)行查找和其它工作。一個(gè)經(jīng)篩選的清單僅顯示那些包含了某一特定值或符合一組條件的行，暫時(shí)隱藏其它行。 

8、60;   數(shù)據(jù)記錄單——一個(gè)數(shù)據(jù)記錄單提供了一個(gè)簡單的方法，讓我們從清單或數(shù)據(jù)庫中查看、更改、增加和刪除記錄，或用你指定的條件來查找特定的記錄。     自動(dòng)分類匯總——利用“數(shù)據(jù)”菜單的“分類匯總”命令，在清單中插入分類匯總行，匯總你所選的任意數(shù)據(jù)。當(dāng)你插入了分類匯總后，Microsoft Excel自動(dòng)為你在清單底部插入一個(gè)“總計(jì)”行。,Excel數(shù)據(jù)庫表格遵循

9、的準(zhǔn)則,建立和使用Excel數(shù)據(jù)庫表格時(shí)，用戶必須遵循以下的基本準(zhǔn)則： （1） 一個(gè)數(shù)據(jù)庫最好單獨(dú)占據(jù)一個(gè)工作表，避免將多個(gè)數(shù)據(jù)庫放到一個(gè)工作表上。（2）數(shù)據(jù)記錄緊接在字段名下面，不要使用空白行將字段名和第一條記錄數(shù)據(jù)分開。（3）避免在數(shù)據(jù)庫中間放置空白行或空白列，任意兩行的內(nèi)容不能完全相同。（4）避免將關(guān)鍵數(shù)據(jù)放到數(shù)據(jù)庫左右兩側(cè)，防止數(shù)據(jù)篩選時(shí)這些數(shù)據(jù)被隱藏。（5）字段名的字體、對齊方式、格式、邊框等樣式，應(yīng)當(dāng)與其他數(shù)據(jù)的格

10、式相區(qū)別。（6）條件區(qū)域不要放在數(shù)據(jù)庫的數(shù)據(jù)區(qū)域下方。因?yàn)橛糜涗泦翁砑訑?shù)據(jù)時(shí)，Excel會(huì)在原數(shù)據(jù)庫的下邊添加數(shù)據(jù)記錄，如果數(shù)據(jù)庫的下邊非空，就不能利用記錄單添加數(shù)據(jù)。,2、公式的編輯；,公式的組成：運(yùn)算單元、運(yùn)算符、函數(shù)及參數(shù)、引用、常數(shù)、文本、時(shí)間等公式中元素的結(jié)構(gòu)或次序決定了最終的計(jì)算結(jié)果。Excel中的公式遵循一個(gè)特定的語法或次序：最前面是等號（=），后面是參與計(jì)算的元素（運(yùn)算數(shù)），這些參與計(jì)算的元素又是通過運(yùn)算符隔開的。每

11、個(gè)運(yùn)算數(shù)可以是不改變的數(shù)值（常量數(shù)值）、單元格或引用單元格區(qū)域、標(biāo)志、名稱、或工作表函數(shù)。,2.1公式中的運(yùn)算符,（1）算術(shù)運(yùn)算符：完成基本的數(shù)學(xué)運(yùn)算。（2）比較操作符：比較運(yùn)算符用于比較兩個(gè)值。當(dāng)用操作符比較兩個(gè)值時(shí)，結(jié)果是一個(gè)邏輯值，不是TRUE就是FALSE。（3）文本運(yùn)算符：使用和號（&）可以將文本連接起來。（4）引用操作符：引用運(yùn)算符的作用是確定在公式中需要參與運(yùn)算的數(shù)據(jù)在工作表中所處的位置，可以使用三個(gè)運(yùn)算符：

12、冒號、逗號和空格。,2.1公式中的運(yùn)算符,①：（冒號）：區(qū)域運(yùn)算符，對兩個(gè)單元格之間，包括兩個(gè)單元格在內(nèi)的所有單元格參與運(yùn)算。 ②，（逗號）：聯(lián)合運(yùn)算符，可將多個(gè)引用合并為一個(gè)引用。 ③空格：交叉運(yùn)算符，它是將同時(shí)屬于兩個(gè)引用的單元格區(qū)域進(jìn)行引用，即兩個(gè)單元格引用相重疊的區(qū)域。,2.2運(yùn)算符及其優(yōu)先級,2.3公式審核及出錯(cuò)檢查,1．追蹤引用單元格【工具】 【公式審核】 【追蹤引

13、用單元格】 【工具】 【公式審核】 【取消所有追蹤箭頭】 2．追蹤從屬單元格【工具】 【公式審核】 【追蹤從屬單元格】 【工具】 【公式審核】 【取消所有追蹤箭頭】 3．公式審核工具欄【工具】 【公式審核】 【顯示“公式審核”工具欄】,2.4常見的公式錯(cuò)誤信息,3、函數(shù)的運(yùn)用,3.1函數(shù)的語法格式3.2常用函數(shù) 1．?dāng)?shù)學(xué)函數(shù) 2．文本函數(shù) 3．子字符串函數(shù)

14、 4．邏輯函數(shù) 5．查找和引用函數(shù) 6．財(cái)務(wù)函數(shù),3.1函數(shù)的語法格式,函數(shù)名（參數(shù)1，參數(shù)2，…，參數(shù)n）。在使用函數(shù)時(shí)，應(yīng)注意以下幾個(gè)問題：（1）函數(shù)名與其后的括號“（”之間不能有空格。（2）當(dāng)有多個(gè)參數(shù)時(shí)，參數(shù)之間要用逗號“，”分隔。（3）參數(shù)部分總長度不能超過1024個(gè)字符。（4）參數(shù)可以是數(shù)值、文本、邏輯值、單元格地址或單元格區(qū)

15、域地址，也可以是各種表達(dá)式或函數(shù)。（5）函數(shù)中的逗號“，”、引號“"”等都是半角字符，而非全角的中文字符。,3.2常用函數(shù)介紹,1．求和函數(shù)（1）無條件求和SUM函數(shù)該函數(shù)的功能是計(jì)算所選取的單元格區(qū)域中所有數(shù)值的和。（2）條件求和SUMIF函數(shù)SUMIF函數(shù)的功能是根據(jù)指定條件對若干單元格求和。 （3）SUMPRODUCT函數(shù)功能是在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。（3）DSUM函數(shù)

16、功能是對數(shù)據(jù)庫表格進(jìn)行多條件匯總。,3.2常用函數(shù)介紹,2．平均值函數(shù)—— AVERGAE函數(shù)功能是計(jì)算給定參數(shù)的算術(shù)平均值。 3．最小值與最大值函數(shù)——MIN/ MAX函數(shù)MIN函數(shù)的功能是給定參數(shù)表中的最小值MAX函數(shù)的功能是給定參數(shù)表中的最大值。,3.2常用函數(shù)介紹,4．?dāng)?shù)學(xué)函數(shù)（1）ABS函數(shù)：返回?cái)?shù)字的絕對值。（2）SQRT函數(shù)：計(jì)算一個(gè)正數(shù)的正平方根。（3）ROUND函數(shù)：返回某個(gè)數(shù)字按指定位數(shù)舍入后的數(shù)字。

17、,3.2常用函數(shù)介紹,5．條件函數(shù)IF函數(shù)也稱條件函數(shù)，它根據(jù)參數(shù)條件的真假，返回不同的結(jié)果。 6．計(jì)數(shù)函數(shù)COUNT函數(shù)：計(jì)算給定區(qū)域內(nèi)數(shù)值型參數(shù)的數(shù)目 COUNTIF函數(shù)：計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。,3.2常用函數(shù)介紹,7．邏輯函數(shù)AND函數(shù)：表示邏輯與OR函數(shù)：表示邏輯或NOT函數(shù)：功能是對參數(shù)的邏輯值求反這三個(gè)函數(shù)一般與IF函數(shù)結(jié)合使用,3.2常用函數(shù)介紹,8．查找函數(shù) （1）LOOKUP函數(shù)

18、：返回向量（單行區(qū)域或單列區(qū)域）或數(shù)組中的數(shù)值。LOOKUP(lookup_value,lookup_vector,result_vector)（2）VLOOKUP函數(shù)：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)（3）HLOOKUP函數(shù)：從表格或數(shù)值數(shù)組的首行查找指定的數(shù)

19、值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。 HLOOKUP(lookup_value,table_array,row_index_num,range_lookup),3.2常用函數(shù)介紹,9．引用函數(shù)INDEX函數(shù)：返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用。 INDEX(array,row_num,column_num),4、數(shù)組公式和數(shù)據(jù)透析；,數(shù)組公式數(shù)據(jù)透析；,4.1數(shù)組公式,1.數(shù)組公式

20、的輸入數(shù)組公式與一般公式不同之處在于它被括在大括號（{}）中，其輸入步驟如下：（1）選中一個(gè)單元格或者單元格區(qū)域。說明：如果數(shù)組公式只是返回一個(gè)結(jié)果，需要選擇保存用來保存結(jié)果的那一個(gè)單元格；如果數(shù)組公式返回多個(gè)結(jié)果，則需要選中需要保留數(shù)組公式計(jì)算結(jié)果的單元格區(qū)域。（2）按照前面介紹的公式輸入規(guī)則，輸入公式的內(nèi)容。（3）公式輸完后，按【Ctrl+Shift+Enter】組合鍵結(jié)束操作。,4.2數(shù)據(jù)透析,數(shù)據(jù)透視分析就是從數(shù)據(jù)庫的

21、特定字段中概括信息，從而方便從各個(gè)角度查看、分析數(shù)據(jù)，并可對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)，它在Excel中的實(shí)現(xiàn)工具是數(shù)據(jù)透視表。數(shù)據(jù)透視表是一種對大量數(shù)據(jù)快速匯總和建立交叉列表的動(dòng)態(tài)工作表，而數(shù)據(jù)透視圖是一種能夠根據(jù)數(shù)據(jù)處理需要，查看部分?jǐn)?shù)據(jù)的圖表對比效果，有些類似前面介紹的動(dòng)態(tài)圖表功能，另外，Excel中還可以根據(jù)數(shù)據(jù)透視表制作不同格式的數(shù)據(jù)透視報(bào)告。,數(shù)據(jù)透析,用2007,發(fā)現(xiàn)數(shù)據(jù)透視表里面沒有多重區(qū)域合并的選項(xiàng)了,那有什么辦法用

22、2007實(shí)現(xiàn)多表合并?快捷鍵ALT+D+P，調(diào)用數(shù)據(jù)透視表向?qū)А?5、應(yīng)用EXCEL完成財(cái)務(wù)會(huì)計(jì)工作；,應(yīng)用EXCEL完成：期初余額，憑證清單——期末余額表——明細(xì)賬，總賬——報(bào)表——報(bào)表分析等工作,6、財(cái)務(wù)函數(shù)的應(yīng)用；,Excel提供了許多有關(guān)財(cái)務(wù)、投資、償還、利息及折舊方面的函數(shù)，在工作表中運(yùn)用這些函數(shù)可以較松地完成相關(guān)的財(cái)務(wù)運(yùn)算，或者對其他財(cái)務(wù)管理軟件的運(yùn)算輸出數(shù)據(jù)進(jìn)行驗(yàn)證。,6、財(cái)務(wù)函數(shù)的應(yīng)用；,6.1投資計(jì)算函數(shù) 6.2折

23、舊計(jì)算函數(shù) 6.3償還率計(jì)算函數(shù),6.1投資計(jì)算函數(shù),投資計(jì)算函數(shù)常見參數(shù)說明貸款定額還款期數(shù)計(jì)算函數(shù)NPER 貸款分期償還金額計(jì)算函數(shù)PMT貸款利息、本金金額計(jì)算函數(shù)IPMT、PPMT投資未來值計(jì)算函數(shù)FV投資償還額現(xiàn)值計(jì)算函數(shù) PV投資定期現(xiàn)金流凈現(xiàn)值計(jì)算函數(shù)NPV投資不定期現(xiàn)金流凈現(xiàn)值計(jì)算函數(shù)XNPV,投資計(jì)算函數(shù)常見參數(shù)說明,Excel投資分析方面的函數(shù)，這些函數(shù)使用的參數(shù)大致相同，意義相近 ，如下表所示。,一、

24、貸款定額還款期數(shù)計(jì)算函數(shù)NPER,1. NPER函數(shù)知識(shí)介紹1）NPER函數(shù)功能：基于固定利率及等額分期付款方式，返回一項(xiàng)投資或貸款的期數(shù)。 2）語法形式：NPER（rate，pmt，pv[，fv，type]） 3）參數(shù)說明 4）注意點(diǎn)：第一所有期數(shù)的單位要統(tǒng)一 第二所有的入帳用正數(shù)表示，支付額用 負(fù)數(shù)表示,一、貸款定額還款期數(shù)計(jì)算函數(shù)NPER,2.實(shí)例１ 超市貸款還款期數(shù)預(yù)

25、測 【實(shí)例內(nèi)容】：由于拓展業(yè)務(wù)需要，欲向銀行貸款1,000,000元。根據(jù)新風(fēng)超市目前的經(jīng)濟(jì)與經(jīng)營狀況進(jìn)行判斷，再綜合其他各種因素考慮，新風(fēng)超市每月有能力且最適合的還款額為12,000元，若以7.50%的平均年利率作為參考，選擇期（月）末付款方式，至少需要多久能還清貸款？,一、貸款定額還款期數(shù)計(jì)算函數(shù)NPER,【實(shí)例操作步驟】： 步驟1：構(gòu)造數(shù)據(jù)模型 。如圖6-1所示。 步驟2：選定B4單元格，在輯欄中輸入“=NPER(

26、B2/12,-B3,B1)”。步驟3：單擊“輸入”按鈕，此時(shí)可以這筆貸款方案需要償還的時(shí)間（月）。,,,圖6-1 例6.1數(shù)據(jù)模型,二、貸款分期償還金額計(jì)算函數(shù)PMT,1. PMT函數(shù)知識(shí)介紹1）PMT函數(shù)功能：基于固定利率及等額分期付款方式，返回投資或貸款的每期付款額。 2）語法形式：PMT(rate，nper，pv[，fv，type]) 3）參數(shù)說明,二、貸款分期償還金額計(jì)算函數(shù)PMT,2.實(shí)例2 超市貸款分期償還金額計(jì)

27、算 【實(shí)例內(nèi)容】：君華銀行向新風(fēng)超市提供了四種基于固定利率及等額分期期末付款的貸款方案，還款期數(shù)及年利率如表6-2所示，新風(fēng)超市應(yīng)該選擇哪一種貸款方案比較合適？ 表6-2 君華銀行貸款方案表,二、貸款分期償還金額計(jì)算函數(shù)PMT,【實(shí)例操作步驟】： 步驟1：構(gòu)造數(shù)據(jù)模型 。如圖6-3所示。 步驟2：選定D8單元格，在編輯欄中輸入“=PMT(C8/12,B8*12,$B$1)”。 步驟3：單擊“輸入”按鈕，此時(shí)可以看到

28、第一種貸款方案的每月還款額。,,,圖6-3例6.2數(shù)據(jù)模型,,三、貸款利息、本金金額計(jì)算函數(shù)IPMT、PPMT,1. IPMT函數(shù)知識(shí)介紹1）IPMT函數(shù)功能：基于固定利率及等額分期付款方式，返回投資或貸款在某一給定期次內(nèi)的利息償還額。 2）語法形式：IPMT(rate，per，nper，pv[，fv，type]) 3）參數(shù)說明,三、貸款利息、本金金額計(jì)算函數(shù)IPMT、PPMT,2. PPMT函數(shù)知識(shí)介紹1）PPMT函數(shù)功能

29、：基于固定利率及等額分期付款方式，返回投資或貸款在某一給定期次內(nèi)的本金償還額。 2）語法形式：PPMT(rate，per，nper，pv[，fv，type]) 3）參數(shù)說明,三、貸款利息、本金金額計(jì)算函數(shù)IPMT、PPMT,3.實(shí)例3 超市貸款分期償還利息與本金金額計(jì)算 【實(shí)例內(nèi)容】：在實(shí)例2中新風(fēng)超市確定貸款方案后，第一年每月償還的利息和本金分別是多少？,三、貸款利息、本金金額計(jì)算函數(shù)IPMT、PPMT,【實(shí)例操作步驟】：

30、步驟1：構(gòu)造數(shù)據(jù)模型 。如圖6-5所示。 步驟2：選定B14單元格，在編輯欄中輸入“=IPMT($C$9/12,A14,$B$9*12,$B$1)”。 步驟3：選定C14單元格，在編輯欄中輸入“=PPMT($C$9/12,A14,$B$9*12,$B$1)”。 步驟4：用填充柄填充B和C兩列，可以看到結(jié)果。,,圖6-5 例6.3數(shù)據(jù)模型,,,四、投資未來值計(jì)算函數(shù)FV,1. FV函數(shù)知識(shí)介紹 1）FV函數(shù)功能

31、：基于固定利率及等額分期付款方式，返回某項(xiàng)投資的未來值。 2）語法形式：FV(rate，nper，pmt[，pv，type]) 3）參數(shù)說明,四、投資未來值計(jì)算函數(shù)FV,2.實(shí)例4 超市存款投資未來值計(jì)算 【實(shí)例內(nèi)容】：新風(fēng)超市計(jì)劃在5年后創(chuàng)辦一所新風(fēng)希望小學(xué)，需要資金200,000元。從本年開始籌備資金，打算在每月月初存入5,000元，在整個(gè)投資期間內(nèi)，平均年投資回報(bào)率為7.6%，3年后這筆存款是否足夠創(chuàng)辦新風(fēng)希望小學(xué)？,四

32、、投資未來值計(jì)算函數(shù)FV,【實(shí)例操作步驟】： 步驟1：構(gòu)造數(shù)據(jù)模型 。如圖6-7所示。 步驟2：選定B30單元格，在編輯欄中輸入“=FV(B27/12,B28*12,-B29,,1)”。 步驟3：單擊“輸入”按鈕，此時(shí)可以看到這筆存款3年后的價(jià)值。,,圖6-7 例6.4數(shù)據(jù)模型,,,五、投資償還額現(xiàn)值計(jì)算函數(shù) PV,1. PV函數(shù)知識(shí)介紹1）PV函數(shù)功能：返回投資的現(xiàn)值?，F(xiàn)值為一系列未來付款當(dāng)前值的累積和，可以作為一

33、種權(quán)衡長期投資的方法。 2）語法形式：PV(rate，nper，pmt[，fv，type]) 3）參數(shù)說明,五、投資償還額現(xiàn)值計(jì)算函數(shù) PV,2.實(shí)例5 超市保險(xiǎn)投資決策 【實(shí)例內(nèi)容】：新風(fēng)超市計(jì)劃為高級員工提高福利，購買一筆醫(yī)療保險(xiǎn)，購買的成本為28,000元，該保險(xiǎn)可以在今后30年中每月末回報(bào)150元，投資年回報(bào)率為5.6%。新風(fēng)超市購買這份保險(xiǎn)是否劃算？,五、投資償還額現(xiàn)值計(jì)算函數(shù) PV,【實(shí)例操作步驟】： 步驟1：構(gòu)

34、造數(shù)據(jù)模型 。如圖6-9所示。 步驟2：選定B35單元格，在編輯欄中輸入“=PV(B32/12,B33*12,B34)”。 步驟3：單擊“輸入”按鈕，此時(shí)可以看到這份保險(xiǎn)的現(xiàn)值。,,,圖6-9 例6.5數(shù)據(jù)模型,,,六、投資定期現(xiàn)金流凈現(xiàn)值計(jì)算函數(shù)NPV,1. NPV函數(shù)知識(shí)介紹1）NPV函數(shù)功能：計(jì)算一組定期現(xiàn)金流的凈現(xiàn)值。通過使用貼現(xiàn)率以及一系列未來支出（負(fù)值）和收入（正值），返回一項(xiàng)投資的凈現(xiàn)值（當(dāng)前純利潤）。

35、 2）語法形式：NPV(rate，value1，value2， ...) 3）參數(shù)說明 ：Rate是應(yīng)用于現(xiàn)金流的貼現(xiàn)率，為一固定值。value1，value2，...代表1到29筆支出及收入的參數(shù)值，value1，value2，...所屬各期間的長度必須相等，而且支付及收入的時(shí)間都發(fā)生在期末。如果支付的時(shí)間發(fā)生在期初，不能作為value參數(shù)，而是將收入轉(zhuǎn)換成凈現(xiàn)值后再扣除支付金額。,六、投資定期現(xiàn)金流凈現(xiàn)值計(jì)算函數(shù)NPV,2.實(shí)例

36、6 超市收購?fù)顿Y決策 【實(shí)例內(nèi)容】：新風(fēng)超市計(jì)劃收購一小超市，收購成本為500,000元，必須預(yù)先付清，經(jīng)過市場調(diào)查，預(yù)計(jì)今后6年的營業(yè)收入可達(dá)到110,000元，130,000元，160,000元，180,000元，190,000元和210,000元，每年的貼現(xiàn)率為8.7%。這項(xiàng)收購計(jì)劃是否可行？,六、投資定期現(xiàn)金流凈現(xiàn)值計(jì)算函數(shù)NPV,【實(shí)例操作步驟】： 步驟1：構(gòu)造數(shù)據(jù)模型 。如圖6-11所示。 步驟2：選定B45

37、單元格，在編輯欄中輸入“=NPV(B38,B39:B44)-B37”。 步驟3：單擊“輸入”按鈕，此時(shí)可以看到收購小超市投資計(jì)劃的當(dāng)前純利潤。,,圖6-11 例6.6數(shù)據(jù)模型,,,七、投資不定期現(xiàn)金流凈現(xiàn)值計(jì)算函數(shù)XNPV,1. XNPV函數(shù)知識(shí)介紹1）XNPV函數(shù)功能：返回一組現(xiàn)金流的凈現(xiàn)值，這些現(xiàn)金流不一定定期發(fā)生。 2）語法形式：XNPV(rate，values，dates) 3）參數(shù)說明 ： rate是應(yīng)用于

38、現(xiàn)金流的貼現(xiàn)率，為一固定值。 dates是與現(xiàn)金流支付相對應(yīng)的支付日期表。 values是與dates中的支付時(shí)間相對應(yīng)的一系列現(xiàn)金流轉(zhuǎn)。,七、投資不定期現(xiàn)金流凈現(xiàn)值計(jì)算函數(shù)XNPV,2.實(shí)例7 超市項(xiàng)目投資決策 【實(shí)例內(nèi)容】：新風(fēng)超市計(jì)劃在2008年5月3日拿出500,000元資金用于某項(xiàng)投資，預(yù)計(jì)可于如下時(shí)間內(nèi)分4次獲取返回資金：2008年12月3日返回120,000元，2009年2月3日返回160,000元，2

39、009年5月3日返回160,000元，2009年12月3日返回90,000元，資金流轉(zhuǎn)折扣為8.2%，這項(xiàng)投資是否劃算？,七、投資不定期現(xiàn)金流凈現(xiàn)值計(jì)算函數(shù)XNPV,【實(shí)例操作步驟】： 步驟1：構(gòu)造數(shù)據(jù)模型 。如圖6-13所示。,,,圖6-13 例6.7數(shù)據(jù)模型,,,步驟2：將A50：A54單元格區(qū)域格式設(shè)置為“日期”。結(jié)果如圖6-14所示。 步驟3：選定B55單元格，在編輯欄中輸入“=XNPV(B47,B50:B54,A

40、50:A54)”。 步驟4：單擊“輸入”按鈕，此時(shí)可以看到這項(xiàng)投資計(jì)劃的當(dāng)前純利潤。,圖6-14 轉(zhuǎn)換日期序列,6.2 折舊計(jì)算函數(shù),折舊計(jì)算函數(shù)常見參數(shù)說明直線折舊函數(shù)SLN固定余額遞減折舊函數(shù)DB雙倍余額遞減折舊函數(shù)DDB年限總和折舊函數(shù)SYD可變余額遞減折舊函數(shù)VDB,折舊計(jì)算函數(shù)常見參數(shù)說明,Cost：固定資產(chǎn)原值Salvage：固定資產(chǎn)使用年限終了時(shí)的估計(jì)殘值Life：固定資產(chǎn)進(jìn)行折舊計(jì)算的周期總數(shù)Perio

41、d：進(jìn)行折舊計(jì)算的期次，必須與life使用的單位相同,上一頁,一、五種折舊計(jì)算函數(shù),１.實(shí)例8 計(jì)算超市貨架的折舊金額 【實(shí)例內(nèi)容】：新風(fēng)超市計(jì)劃將現(xiàn)有一批已使用3年的貨架轉(zhuǎn)手賣出，3年前的購買價(jià)值為50,000元，使用年限為8年，預(yù)計(jì)報(bào)廢價(jià)值為15,000元，使用直線折舊，固定余額遞減折舊，雙倍余額遞減折舊，年限總和折舊和可變余額遞減折舊5種不同的折舊方法計(jì)算出這批貨架這3年的折舊金額。,一、五種折舊計(jì)算函數(shù),【實(shí)例操作步驟】：

42、步驟1：構(gòu)造數(shù)據(jù)模型 。如圖6-16所示。,,,圖6-16 例6.8數(shù)據(jù)模型,,一、五種折舊計(jì)算函數(shù),步驟2：選定B7單元格，在編輯欄中輸入“=SLN($B$1,$B$2,$B$3)”。 步驟3：選定C7單元格，在編輯欄中輸入“=DB($B$1,$B$2,$B$3,A7)”。 步驟4：選定D7單元格，在編輯欄中輸入“=DDB($B$1,$B$2,$B$3,A7)”。 步驟5：選定E7單元格，在編輯欄中輸入“=SYD($B$1,$

43、B$2,$B$3,A7)”。 步驟6：選定F7單元格，在編輯欄中輸入“=VDB($B$1,$B$2,$B$3,0,A7,2)”。 步驟7：用填充柄工具填充B、C、D、E、F列，可以看到結(jié)果。,一、五種折舊計(jì)算函數(shù),２. SLN函數(shù)知識(shí)介紹1）SLN函數(shù)功能：用直線折舊的計(jì)算方法，返回一項(xiàng)資產(chǎn)每期的直線折舊費(fèi)。 2）語法形式：SLN(cost，salvage，life) 3）參數(shù)說明,一、五種折舊計(jì)算函數(shù),3. DB函數(shù)知識(shí)

44、介紹1）DB函數(shù)功能：用固定余額遞減的計(jì)算方法，返回一項(xiàng)資產(chǎn)在給定期間內(nèi)的折舊值。用于計(jì)算固定利率下的資產(chǎn)折舊金額。 2）語法形式：DB(cost，salvage，life，period[，month]) 3）參數(shù)說明 ：month為第一年的月份數(shù)，如省略，則假設(shè)為12。,一、五種折舊計(jì)算函數(shù),4. DDB函數(shù)知識(shí)介紹1）DDB函數(shù)功能：用雙倍余額遞減法或其他指定計(jì)算方法，返回一筆資產(chǎn)在給定期間內(nèi)的折舊值。 2）語法形式

45、：DDB(cost，salvage，life，period[，factor]) 3）參數(shù)說明 ：factor參數(shù)為余額遞減速率，也稱為折舊因子。如果factor被省略，則假設(shè)為2，即雙倍余額遞減法。這5個(gè)參數(shù)都必須為正數(shù)。,一、五種折舊計(jì)算函數(shù),5. SYD函數(shù)知識(shí)介紹1）SYD函數(shù)功能：用年限總和折舊計(jì)算方法，返回一筆資產(chǎn)在給定期間內(nèi)的折舊值。 2）語法形式：SYD(cost，salvage，life，per) 3）參數(shù)

46、說明 ：per參數(shù)為期間，單位與life相同。,一、五種折舊計(jì)算函數(shù),6. VDB函數(shù)知識(shí)介紹1）VDB函數(shù)功能：用可變余額遞減計(jì)算方法，返回一筆資產(chǎn)在給定期間內(nèi)的折舊值。 2）語法形式：VDB(cost，salvage，life，star_period，end_period，factor，no_switch) 3）參數(shù)說明 ：star_period參數(shù)為進(jìn)行折舊計(jì)算的起始期間，單位與life相同。End_period參數(shù)為進(jìn)

47、行折舊計(jì)算的截止期間，單位與life相同。factor參數(shù)為余額遞減速率，也稱為折舊因子。如果factor被省略，則假設(shè)為2，即雙倍余額遞減法。No_switch參數(shù)指定當(dāng)折舊值大于余額遞減計(jì)算值時(shí)，是否轉(zhuǎn)用直線折舊法，為一邏輯值。,6.3償還率計(jì)算函數(shù),投資償還率計(jì)算函數(shù)RATE投資定期現(xiàn)金流內(nèi)部收益率計(jì)算函數(shù)IRR投資修正收益率計(jì)算函數(shù)MIRR,一、投資償還率計(jì)算函數(shù)RATE,1.RATE函數(shù)知識(shí)介紹 1）RATE函數(shù)功能：返

48、回年金的各期利率。 2）語法形式：RATE(nper，pmt，pv，fv，type，guess) 3）參數(shù)說明 :guess參數(shù)為預(yù)期利率。如果省略，則假設(shè)該值為10%。,一、投資償還率計(jì)算函數(shù)RATE,2.實(shí)例9 銀行的貸款償還率計(jì)算 【實(shí)例內(nèi)容】：新風(fēng)超市于5年前創(chuàng)建，創(chuàng)建初期向銀行貸款200,000元，期限5年，每個(gè)月償還4,000元，這筆貸款銀行的年收益率是多少？,一、投資償還率計(jì)算函數(shù)RATE,【實(shí)例操作步驟】：

49、步驟1：構(gòu)造數(shù)據(jù)模型 。如圖6-18所示。 步驟2：選定B4單元格，在編輯欄中輸入“=RATE(B2*12,-B3,B1)”,單擊“輸入”按鈕 。步驟3：選定B5單元格，在編輯欄中輸入“=B4*12”，單擊“輸入”按鈕，此時(shí)可以看到銀行的年收益率。,,,圖6-18例6.9數(shù)據(jù)模型,,二、投資定期現(xiàn)金流內(nèi)部收益率計(jì)算函數(shù)IRR,按照傳統(tǒng)的內(nèi)部收益率（IRR）法，項(xiàng)目的凈現(xiàn)值（NPV）為零時(shí)的貼現(xiàn)率，就是IRR。通常，我們以此I

50、RR與市場利率或預(yù)定的收益率相比較，對該項(xiàng)目的可行性進(jìn)行評價(jià)。 比如，一個(gè)簡單的兩年到期項(xiàng)目，初始投入1000元；第一年凈流入500元；第二年600元。市場利率為10%。采用傳統(tǒng)的IRR法： NPV=-1000+500/（1+IRR）+600/（1+IRR）*（1+IRR）=0。由此模型可求出IRR。,二、投資定期現(xiàn)金流內(nèi)部收益率計(jì)算函數(shù)IRR,1.IRR函數(shù)知識(shí)介紹 1）IRR函數(shù)功能：返回由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。

51、 2）語法形式：IRR(values，guess) 3）參數(shù)說明 : Values參數(shù)為數(shù)組或單元格的引用，包含用來計(jì)算返回的內(nèi)部收益率的數(shù)字。 Guess參數(shù)為對函數(shù)IRR計(jì)算結(jié)果的估計(jì)值。,二、投資定期現(xiàn)金流內(nèi)部收益率計(jì)算函數(shù)IRR,2.實(shí)例10 超市投資定期現(xiàn)金流內(nèi)部收益率計(jì)算 【實(shí)例內(nèi)容】：5年來新風(fēng)超市每年的收入分別為50,000元，70,000元，100,000元，110,000元，150,000元和180,

52、000元，期初投資的200,000元現(xiàn)在內(nèi)部收益率是多少？,二、投資定期現(xiàn)金流內(nèi)部收益率計(jì)算函數(shù)IRR,【實(shí)例操作步驟】： 步驟1：構(gòu)造數(shù)據(jù)模型 。如圖6-21所示。 步驟2：選定B13單元格，在編輯欄中輸入“=IRR(B7:B12)”。 步驟3：單擊“輸入”按鈕，此時(shí)可以看到新風(fēng)超市近5年的內(nèi)部收益率。,,,圖6-21 例6.10數(shù)據(jù)模型,,,三、投資修正收益率計(jì)算函數(shù)MIRR,傳統(tǒng)的IRR法是建立在以一個(gè)“虛擬的再投資

53、利率”再投資的假設(shè)基礎(chǔ)之上的。隨著現(xiàn)代經(jīng)濟(jì)系統(tǒng)的波動(dòng)性日益頻繁，傳統(tǒng)的IRR法已經(jīng)很難正確地評價(jià)項(xiàng)目的真實(shí)可行性。而MIRR法，就是對IRR法的一個(gè)簡單合理的修正，使之更加有效與真實(shí)。,三、投資修正收益率計(jì)算函數(shù)MIRR,初始投入1000元；第一年凈流入500元；第二年600元。市場利率為10%。采用傳統(tǒng)的IRR法： NPV=-1000+500/（1+IRR）+600/（1+IRR）*（1+IRR）=0。由此模型可求出IRR。我們對此模

54、型進(jìn)行一些“變動(dòng)”，但仍以NPV為零：1000（1+IRR）*（1+IRR）=500*（1+IRR）+600。 該等式經(jīng)濟(jì)含義為，一個(gè)項(xiàng)目（證券）面值為1000元的第二年（末）總價(jià)值，可分拆成面值500元一年期的，與面值600元二年期的項(xiàng)目（價(jià)值）之和。此時(shí)，第一年收到的500元再投資利率應(yīng)當(dāng)是市場利率10%。而對應(yīng)的1000元項(xiàng)目的總收益率（R）為，1000*（1+R）=500*（1+10%）+600；令（1+R）=（1+MIRR）*

55、（1+IRR），由此可求出MIRR。對于多期拓展，就可得到：期末收益/期初投資=（1+MIRR）^n。n是冪次數(shù)，等于投資期數(shù)。,三、投資修正收益率計(jì)算函數(shù)MIRR,1.MIRR函數(shù)知識(shí)介紹 1）MIRR函數(shù)功能：返回某一連續(xù)期間內(nèi)現(xiàn)金流的修正內(nèi)部收益率。函數(shù)MIRR同時(shí)考慮了投資的成本和現(xiàn)金再投資的收益率。 2）語法形式：MIRR(values，finance_rate，reinvest_rate) 3）參數(shù)說明 : V

56、alues參數(shù)為一個(gè)數(shù)組或?qū)Π瑪?shù)字的單元格的引用。 Finance_rate參數(shù)為現(xiàn)金流中使用的資金支付的利率。 Reinvest_rate參數(shù)為將現(xiàn)金流再投資的收益率。,三、投資修正收益率計(jì)算函數(shù)MIRR,2.實(shí)例11 超市投資修正收益率計(jì)算 【實(shí)例內(nèi)容】：5年來，新風(fēng)超市將每年所獲得的收益用于重新投資，每年的收益率為10%，那開業(yè)5年后的修正收益率是多少？,三、投資修正收益率計(jì)算函數(shù)MIRR,【實(shí)例操作步驟】： 步驟

57、1：構(gòu)造數(shù)據(jù)模型 。如圖6-23所示。 步驟2：選定B16單元格，在編輯欄中輸入“=MIRR(B7:B12,B5,B15)”。 步驟3：單擊“輸入”按鈕，此時(shí)可以看到新風(fēng)超市近5年的內(nèi)部修正收益率。,,圖6-23 例6.11數(shù)據(jù)模型,,,,7、應(yīng)用EXCEL完成管理會(huì)計(jì)工作；,7.1本量利分析7.2回歸分析7.3規(guī)劃求解,7.1本量利分析,量本利分析，即成本——數(shù)量——利潤分析，是通過數(shù)學(xué)分析和圖示分析等形式對銷售數(shù)量、

58、銷售單價(jià)、變動(dòng)成本、固定成本等因素與利潤指標(biāo)的內(nèi)在聯(lián)系進(jìn)行研究，以協(xié)助管理者進(jìn)行項(xiàng)目規(guī)劃和期間計(jì)劃的預(yù)測分析方法。（1）量本利分析法的基本假設(shè)① 假設(shè)能夠?qū)⑵髽I(yè)的所有成本都按照成本習(xí)性精確地劃分為變動(dòng)成本和固定成本。② 假設(shè)銷售單價(jià)和銷售結(jié)構(gòu)在相關(guān)范圍內(nèi)保持不變。,7.1本量利分析,③ 假設(shè)企業(yè)的生產(chǎn)能力和生產(chǎn)效率在相關(guān)范圍內(nèi)保持不變。④ 假設(shè)企業(yè)當(dāng)期的產(chǎn)銷量基本平衡，存貨水平保持不變。 （2）量本利的基本關(guān)系式利潤=銷售收

59、入-總成本 =銷售收入-銷售成本-期間費(fèi)用 =邊際貢獻(xiàn)-固定成本 =銷售量*（銷售單價(jià)-單位變動(dòng)成本）-固定成本,7.2回歸分析,（1）一元線性回歸模型（2）一元非線性回歸模型,7.2回歸分析,（3）多元線性回歸模型（4）多元非線性回歸模型 例如，柯柏—道格拉斯生產(chǎn)函數(shù) 式中：L和K分別為勞動(dòng)力和固定資本,預(yù)測函數(shù),1 LINEST函數(shù) 2 LOGEST函數(shù)

60、 3 TREND函數(shù) 4 GROWTH函數(shù) 5 FORECAST函數(shù) 6 SLOPE函數(shù) 7 INTERCEPT函數(shù),1 LINEST函數(shù),LINEST使用最小二乘法對已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返回描述此直線的數(shù)組。LINEST(known_y's,

61、known_x's,const,stats)直線的公式為：y = mx + b ory = m1x1 + m2x2 + ... + b（如果有多個(gè)區(qū)域的 x 值）,2 LOGEST函數(shù),在回歸分析中，計(jì)算最符合數(shù)據(jù)的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)值數(shù)組。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組，故必須以數(shù)組公式的形式輸入。LOGEST(known_y's,known_x's,const,stats)此曲線的公

62、式為：y = b*m^x 或y = (b*(m1^x1)*(m2^x2)*_)（如果有多個(gè) x 值）,3 TREND函數(shù),返回一條線性回歸擬合線的值。即找到適合已知數(shù)組 known_y‘s 和 known_x’s 的直線（用最小二乘法），并返回指定數(shù)組 new_x‘s 在直線上對應(yīng)的 y 值。TREND(known_y's,known_x's,new_x's,const),4 GROWTH函數(shù),根據(jù)現(xiàn)有

63、的數(shù)據(jù)預(yù)測指數(shù)增長值。根據(jù)現(xiàn)有的 x 值和 y 值，GROWTH 函數(shù)返回一組新的 x 值對應(yīng)的 y 值。可以使用 GROWTH 工作表函數(shù)來擬合滿足現(xiàn)有 x 值和 y 值的指數(shù)曲線。語法GROWTH(known_y's,known_x's,new_x's,const),5 FORECAST函數(shù),根據(jù)已有的數(shù)值計(jì)算或預(yù)測未來值。此預(yù)測值為基于給定的 x 值推導(dǎo)出的 y 值。已知的數(shù)值為已有的 x 值和 y

64、值，再利用線性回歸對新值進(jìn)行預(yù)測。可以使用該函數(shù)對未來銷售額、庫存需求或消費(fèi)趨勢進(jìn)行預(yù)測。語法FORECAST(x,known_y's,known_x's),6 SLOPE函數(shù),返回根據(jù) known_y's 和 known_x's 中的數(shù)據(jù)點(diǎn)擬合的線性回歸直線的斜率。斜率為直線上任意兩點(diǎn)的重直距離與水平距離的比值，也就是回歸直線的變化率。語法SLOPE(known_y's,known_x

65、's),7 INTERCEPT函數(shù),利用現(xiàn)有的 x 值與 y 值計(jì)算直線與 y 軸的截距。截距為穿過已知的 known_x's 和 known_y's 數(shù)據(jù)點(diǎn)的線性回歸線與 y 軸的交點(diǎn)。當(dāng)自變量為 0（零）時(shí)，使用 INTERCEPT 函數(shù)可以決定因變量的值。例如，當(dāng)所有的數(shù)據(jù)點(diǎn)都是在室溫或更高的溫度下取得的，可以用 INTERCEPT 函數(shù)預(yù)測在 0°C 時(shí)金屬的電阻。語法INTERCEPT(

66、known_y's,known_x's),7.3規(guī)劃求解,在計(jì)劃管理中，經(jīng)常會(huì)遇到各種規(guī)劃問題，例如：人力資源的調(diào)度、產(chǎn)品生產(chǎn)的安排、運(yùn)輸線路的規(guī)劃、生產(chǎn)材料的搭配、采購批次的確定等。這類問題有一個(gè)共同要求，那就是：如何合理利用各種約束資源實(shí)現(xiàn)最佳的經(jīng)濟(jì)效益，也就是達(dá)到常量最高、利潤最大、成本最低、費(fèi)用最省等目標(biāo)。這就是本節(jié)要解決的在約束條件下尋求目標(biāo)函數(shù)最優(yōu)的規(guī)劃問題。一般來講，這類規(guī)劃問題都具有如下三個(gè)特點(diǎn)：（1）

67、所求問題都有單一的目標(biāo)，如求生產(chǎn)的最低成本，求運(yùn)輸?shù)淖罴崖肪€，求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時(shí)間以及求其他目標(biāo)函數(shù)的最佳值等。（2）總是有明確的不等式約束條件。比如庫存不能低于一定的數(shù)量，否則造成原料短缺或產(chǎn)品缺貨；生產(chǎn)產(chǎn)品不能超過一定額度，否則會(huì)造成商品積壓等。（3）問題都有直接或間接影響約束條件的一組輸入值。,7.3規(guī)劃求解,規(guī)劃求解工具用于解決復(fù)雜的方程求值及各類線性或非線形有約束優(yōu)化問題。規(guī)劃問題種類繁多。從數(shù)學(xué)角度來看

68、，規(guī)劃問題都有下述共同特征，這些特征也構(gòu)成了Excel規(guī)劃求解工具界面中的主要部分：決策變量：每個(gè)規(guī)劃問題都有一組需要求解的未知數(shù)，稱作決策變量。這組決策變量的一組確定值就代表一個(gè)具體的規(guī)劃方案。在Excel規(guī)劃求解工具中，可變單元格代表的是決策變量。約束條件：對于規(guī)劃問題的決策變量通常都有一定的限制條件，稱作約束條件。約束條件可以用與決策變量有關(guān)的不等式或等式來表示。目標(biāo)：每個(gè)問題都有一個(gè)明確的目標(biāo)，如利潤最大或成本最小。目標(biāo)通