第二章EXCEL在財(cái)務(wù)管理與分析中的應(yīng)用解析

1、Excel會(huì)自動(dòng)在公式的兩邊加上大括號(hào)訂 。 但也可以直接鍵入數(shù)組數(shù)值，這樣鍵入的數(shù)組數(shù)值被在=10, 20, 30, 40A11023456789101112131415161 7BcDEF120|130.401第二章Excel在財(cái)務(wù)管理分析中的應(yīng)用第一節(jié)公式及函數(shù)的高級(jí)應(yīng)用一、數(shù)組公式及其應(yīng)用數(shù)組公式就是可以同時(shí)進(jìn)行多重計(jì)算并返回一種或多種結(jié)果的公式。在數(shù)組公式中使用兩組或多 組數(shù)據(jù)成為數(shù)組參數(shù)，數(shù)組參數(shù)可以是一個(gè)數(shù)據(jù)區(qū)域，也可以是數(shù)組常量。數(shù)組公式中的每個(gè)數(shù)組必 須有相同數(shù)量的行和列。數(shù)組公式的輸入、編輯及刪除1、數(shù)組公式的輸入步驟選定單元格或單元格區(qū)域。如果數(shù)組公式將返回一個(gè)結(jié)果，單擊

2、輸入數(shù)組的單元格；如果數(shù)組 公式將返回多個(gè)結(jié)果，則要選定需要輸入數(shù)組公式的單元格區(qū)域。輸入數(shù)組公式同時(shí)按Ctrl+Shift+Enter ”組合鍵，貝U 在數(shù)組公式中，通常都是用單元格區(qū)域引用，成為數(shù)組常量。生成數(shù)組常量，必須按如下操作： 直接在公式中輸入數(shù)值，并用大括號(hào)不同列的數(shù)值用逗號(hào)：”分開。 不同行的數(shù)值用分號(hào) ；”分開 例2 1 :輸入數(shù)組常量。見圖 2-1、2-2C1在單元格Al： D1中分別輸 入10, 20, 30和40這4個(gè)數(shù)值。操作步驟：選取單元格區(qū)域A1:D1 口 在公式編輯欄中輸入公式- "= 10如30彳0;50屜70/0 M 同時(shí)按 Ctrl+Sliift

3、+Entei 組合鍵:晝快譯運(yùn)英中旦曰中空中英匡設(shè)置三C1十點(diǎn) 1= 10,20, 30, 40; 50,60, 70, 80 C4ABCDE1233536837941Lll8L 10 15|561,7數(shù)組公貳及結(jié)呆8910111213145 (=B2:E2+B3:E3數(shù)據(jù)區(qū)域HIT在E4:E4中均有相同的計(jì)算公 式爭它們分別為單元格B2:E2與單 元格B3:E3中的數(shù)據(jù)和'即B4單冗 格中的公式為,r = B2+B3r羊元格 C4中的公式為JC2+C3J， 則可采用數(shù)組公式的方法輸入公 式。立f半（SJ緬輯 逗了 觀圖 世） 犒入（X） 梏式CQ） 工具 QJ數(shù)據(jù)）宙口I二wJ 1當(dāng)

4、丄1苓遞1晶芻達(dá)十/1 勺 h 1+1 曲 空 7 收潢夾前往尋E:"十算機(jī)財(cái)勞首理I" ji y2、編輯數(shù)組公式數(shù)組公式的特征之一就是不能單獨(dú)編輯、清除或移動(dòng)數(shù)組公式所涉及的單元格區(qū)域中的某一個(gè)單元 格。若在數(shù)組公式輸入后發(fā)現(xiàn)錯(cuò)誤需要修改，則需要按以下步驟進(jìn)行：在數(shù)組區(qū)域中單擊任一單元格。 單擊公式編輯欄，當(dāng)編輯欄被激活時(shí)，大括號(hào)“訂”在數(shù)組公式中消失。 編輯數(shù)組公式內(nèi)容。修改完畢后，按 Ctrl+Shift+Enter "組合鍵。3、刪除數(shù)組公式刪除數(shù)組公式的步驟是：首先選定存放數(shù)組公式的所有單元格，然后按Delete鍵。4、數(shù)組運(yùn)算規(guī)則以數(shù)字的數(shù)學(xué)運(yùn)算為例數(shù)

5、字?jǐn)?shù)組5,2,8,4與另一個(gè)數(shù)字?jǐn)?shù)組1,2,3,4的加法運(yùn)算如下：5,2,8,4+1,2,3,4=5+1,2+2,8+3,4+4=6,4,11,8其余的乘、除、減的運(yùn)算與加法雷同。都是對應(yīng)數(shù)字與對應(yīng)數(shù)字運(yùn)算后返回。數(shù)組公式的應(yīng)用1、用數(shù)組公式計(jì)算兩個(gè)數(shù)據(jù)區(qū)域的乘積例2- 3 :已知A企業(yè)1- 12月份的銷售量與價(jià)格，用數(shù)組公式計(jì)算1- 12月份的銷售額和月平均銷售額。L20TPABCDEFGHI；KLL2A企業(yè)112月份的銷售量與價(jià)格y月份1234567e91011廠5銷售量323300350420380400356430450S90500510&單價(jià)20202S28282828282

6、82828287售額90448400980011760106401120099681204012600109201400014280B月平均銷 售額1122191011121311221136521-12月份的銷售額建立過程：第一步：建立如圖2-4所示的模型結(jié)構(gòu)，并輸入數(shù)據(jù)；第二步：選定單元格區(qū)域C7:N7，并輸入公式 =C5:N5*C6:N6 ”第三步：同時(shí)按“ CTRL+SHIFT+ENTER"11221C licrosoft EkcbI - 例23?理文件限)歸匹|視圉遡插入格式工貝戳描辺)奮口血幫肋Qp14；直快譯 昌英中 旦日中 出中英1.設(shè)苴-ACDFGHIJKLM12

7、-3-A企業(yè)1一12月份的銷售量與價(jià)格4月份1234567891011125銷售量323300350420380400356430450390&005106單價(jià)P 2S28P 282828282828282828287售額90448400980011760106401120099681204012600109201400014280S4平銷額 月坷售-11221A l=C5：N5+C6：N5H7月平均銷售的計(jì)算過程：第一步：選定I10單元格第二步：輸入公式=AVERAGE(C5:N5*C6:N6) 第三步：同時(shí)按 CTRL+SHIFT+ENTER"2、用數(shù)組公式計(jì)算多個(gè)數(shù)據(jù)區(qū)

8、域的和例2- 4:某企業(yè)2004年銷售3種產(chǎn)品，用數(shù)組公式計(jì)算該公司2004年的總銷售額。計(jì)算月銷售總額過程：第一步：選取單元格區(qū)域C10:N10，見圖2-5HIDV5力:【饑5:吁:總盼:側(cè)巨礙貶AE-PUDEFKIJ1112產(chǎn)品ew隼表3134567 /8QID11-i n 占5產(chǎn)甌澤量323洶550必23D35643D45D3905D0552828282828282828282828286澤量匸55D54D細(xì)5?0應(yīng)52D46D頸55u31u?輕f32323232323232323232n u產(chǎn)靈膛量匚83D73D71D59D閆?9082D83D67D?7D；8u925252525252

9、525 U U u下 u L-1：月銷售般4：丄4652DW3D465304936D45558Bbd52?7D115672621；第二步：輸入公式=C4:N4*C5:N5+C6:N6*C7:N7+C8:N8*C9:N9"第三步：按數(shù)組生成組合鍵。計(jì)算三年銷售總額：第一步：選取單元格 C11;第二步：輸入公式"=SUM(C4:N4*C5:N5+C6:N6*C7:N7+C8:N8*C9:N9)"；第三步：選取單元格區(qū)域C11:N11 ；第四步：單擊合并居中按鈕。、常用函數(shù)及其應(yīng)用第二節(jié)圖表處理動(dòng)態(tài)圖標(biāo)的建立例2-5 :某企業(yè)的8月各銷售部門一年內(nèi)個(gè)月銷售數(shù)據(jù)如下表所示

10、：建立各部門的動(dòng)態(tài)圖表。icDEFGHJKLS1何冷售峻表戦:臺(tái)7份 n1月2月3月4月5月6月胡8月9月10月11月12月504864515?業(yè)687254645570前2657880卞6558744274704967部卍7744251404860445376465E前4697644774466724457657852前5504653467054化5641494345前6544145774063705568445473部叩406059518054415660505373前8587952431247745E5E80501516171819.20E122份 部門、1月2月3月4月5月6月7月9月

11、2月11月12月部Ha777442514D436044S37646552E262829)3L51+部門萌舗fl fl o o 勺 QQ9 0 7 6 54321如】麗出is13i.y動(dòng)態(tài)圖表數(shù)據(jù)區(qū)域第一步：在單元格 B16中輸入公式"=INDEX(B4:B11,B17)&"銷售圖"，顯示選中某部門的銷售圖，如 選中的部門2,則顯示的結(jié)果為“部門 2銷售圖”。第二步：單擊視圖t窗體t組合框，在適當(dāng)位置畫組合框，并進(jìn)行設(shè)置，見設(shè)置控件格式圖。第三步：在單元格 C16中輸入公式"=INDEX(C4:C11,$B$17)" 第四步：將 C16中

12、的公式復(fù)制到 D16:N16。第五步：繪圖第三節(jié)數(shù)據(jù)分析處理第四節(jié)數(shù)據(jù)分析工具的應(yīng)用Excel提供了非常使用的數(shù)據(jù)分析工具，禾U用這些工具，可以解決財(cái)務(wù)管理中的許多問題，例 如財(cái)務(wù)分析工具、統(tǒng)計(jì)分析工具、工程分析工具、規(guī)劃求解工具、方案管理器等。下面介紹財(cái)務(wù)管理與分 析中常用的一些數(shù)據(jù)分析工具。、模擬運(yùn)算表模擬運(yùn)算表就是將工作表中的一個(gè)單元格區(qū)域的數(shù)據(jù)進(jìn)行模擬計(jì)算，測試使用一個(gè)或兩個(gè)以上變量 對運(yùn)算結(jié)果的影響。在 Excel中，可以構(gòu)造兩種模擬運(yùn)算表：單變量模擬運(yùn)算表和多變量模擬運(yùn)算表。模擬運(yùn)算表可以運(yùn)用在敏感性分析領(lǐng)域。所謂敏感性分析是指通過多次使用不同的參數(shù)，對某個(gè)或 某一組公式的運(yùn)算結(jié)果

13、進(jìn)行綜合對比，從而了解不同參數(shù)在其可能的取值范圍內(nèi)，對運(yùn)算結(jié)果的影響程 度。單變量模擬運(yùn)算表單變量模擬運(yùn)算表就是基于一個(gè)輸入變量，用它來測試對公式計(jì)算結(jié)果的影響。例2- 6:企業(yè)向銀行貸款 10000元，期限5年，測試不同利率對月還款額的影響。貸款總額10000貸款利率12%貸款期限5單變量模擬計(jì)算表第一步：設(shè)計(jì)模擬運(yùn)算表結(jié)構(gòu)，如圖2-8。戶 Mieros of-t2 G21文件世J褊輯 d）現(xiàn)圉世）插入 廷）格式©工具數(shù)據(jù)窗口迪j幫肋電） q 1 3“心丄鈔盤44十丨召也w ”iij塾也步驟:訂一決譯l述I英中.日_曰中l(wèi)中中英設(shè)負(fù) jgC7=ACPMT貸款利率/IZ 貸款期限*貸

14、款總額R利率月還款額日期月還款額¥ 222.44¥ 222.444.00%184.171888.494.50%186.432470.735.00%188.713332.145.50%191.014263.346.00%193.335222.446.50%195.666195.507.00%198.017176.537.50%200.388162.538.00%202.769151.84AC1DEF12貸款總額10000貸款利率12%3貸欹期限545單變量模擬計(jì)算表6利率月還款額1日期月還款額7¥222. 44|Y222. 4484. 009S184. 17'

15、; 1888. 4994, 50%186, 432470. 73105, 00961S8, 713332. 14115. 509&191. 014263. 341200%193 335222. 4413S 5 09&195. 666195. 50147, 009G198, 017176, 531B7, 5096200. 3S8162. 53168. 009&202. 769151. 84第二步：在單元格C7中輸入公式=ABS（PMT（貸款利率/12,貸款期限*12,貸款總額）”，計(jì)算月?lián)Q款額。第三步：選取單元格區(qū)域B7:C16。模擬運(yùn)算表輸入引用行的單元格 輸入引用列的

16、單元格C)：取消第四步：單擊數(shù)據(jù)，選擇模擬運(yùn)算表，彈出模擬運(yùn)算表對話窗，如圖2-4圖確定第五步：在本例中引用的是列數(shù)據(jù)，所以在輸入引用列的單元格中輸入$E$2。單擊確定按鈕，既單變量模擬運(yùn)算表，見圖2-9。雙變量模擬運(yùn)算表例2-7:某投資項(xiàng)目，投資 10000,項(xiàng)目期為5年。每年的凈現(xiàn)金流量為2000，貼現(xiàn)率為8%。對項(xiàng)目凈現(xiàn)值進(jìn)行敏感性分析。設(shè)計(jì)過程：第一步：設(shè)計(jì)模擬運(yùn)算表結(jié)構(gòu)，如圖2-10。；旦懾譯匡英中己日中王中英工雕日 D15 E &面ABCDEFGHIj12NPV計(jì)算3投資8000. 004項(xiàng)目年限55年現(xiàn)金流量2000. 00-418. 436貼現(xiàn)率10%7NPV-

17、5;4188說明:C14:C19中是雙變量模擬運(yùn)算表的計(jì)算結(jié)果.1JT11雙變量模擬運(yùn)算表12年現(xiàn)金流量貼現(xiàn)率13-¥4182%4%6%8%10%141000. 00-3286. 54-3548. 18-3787. 64-4007. 29-4209. 21151200. 00-2343. 85-2657. 81-2945. 16-3208. 75-3451. 06161400. 00-1401. 16-1767. 45-2102. 69-2410.21-2692. 90171600. 00-458. 46-877. 08-1260. 22-1611. 66-1934. 7418180

18、0. 00484. 2313. 28-417. 75-613. 12-1176+58192000. 001426. 92903. 64424. 73-11 58-418.43時(shí)fl第二步：在單元格 C7中輸入公式“ =PV(C6,C4,-C5)-C3 ”計(jì)算凈現(xiàn)值。第三步：在單元格 B13中輸入公式“ =C7”第四步：選中單元格區(qū)域B13:G19。第五步：單擊數(shù)據(jù)，選擇模擬運(yùn)算表，彈出模擬運(yùn)算表對話窗，圖2-10。第六步：在本例中引用的是雙變量，所以在輸入引用行的單元格和輸入引用列的單元格中輸入變量。單擊確定按鈕，既的導(dǎo)彈變量模擬運(yùn)算表，見圖2-11。確定取消匸丿 單變量求解單變量求解就是求解

19、只有一個(gè)變量的方程的根，方程可以是線性的，也可以是非線性的。單變量求 解工具可以解決財(cái)務(wù)管理中涉及到一個(gè)變量的求解問題。例2- &某投資項(xiàng)目，投資 10000，項(xiàng)目期為5年，貼現(xiàn)率為8%。若要求該項(xiàng)的凈現(xiàn)值達(dá)到5000,則每年的凈現(xiàn)金流量應(yīng)為多少？第一步：設(shè)計(jì)如圖 2-12所示的計(jì)算表格。C licrosoft Excel -例2一8C7 ” 衣屮曲6«£哽)£3CD E F G H I第二步：在單元格 C7中輸入公式=PV(C6,C4,-C5)-C3 ”計(jì)算凈現(xiàn)值。第三步：單擊工具菜單，選擇彈變量求解項(xiàng)，彈出彈變量求解對話框，如圖2-13。第四步：在目標(biāo)

20、單元格中輸入“ C7”，在目標(biāo)值中輸入“ 50000”，在可變單元格中輸入“ $C$5 ” , 單擊確定按鈕，則系統(tǒng)計(jì)算出結(jié)果，如圖2-12所示，即實(shí)現(xiàn)凈現(xiàn)值 50000元，年凈現(xiàn)金流量應(yīng)達(dá)到3255.93元。(四)方案管理器所謂方案，是保存在工作表當(dāng)中并可以相互之間進(jìn)行切換的數(shù)據(jù)。例如，對于一組現(xiàn)金流量分別使用不同的貼現(xiàn)率計(jì)算凈現(xiàn)值，就可以把不同條件下的結(jié)果保存在若干方案中，通過 Excel的方案管理器在不同的結(jié)果間進(jìn)行切換，以對比和評(píng)價(jià)各方案之間的差異。例2 9:某投資項(xiàng)目，投資 10000,項(xiàng)目期為5年。項(xiàng)目的現(xiàn)金流量和貼現(xiàn)率受經(jīng)濟(jì)形勢的影響如果經(jīng)濟(jì) 不景氣，預(yù)計(jì)每年的現(xiàn)金流量為1800

21、,貼現(xiàn)率為6%;如果經(jīng)濟(jì)繁榮，預(yù)計(jì)每年現(xiàn)金流量為2800 ,貼現(xiàn)率為10% ;如果經(jīng)濟(jì)形勢一般，則現(xiàn)金流量為2200,貼現(xiàn)率為8%。要求對項(xiàng)目進(jìn)行評(píng)價(jià)？第一步：建立如圖 2-13所示的方案管理器數(shù)據(jù)結(jié)果。并在C4、C5、C6、C7單元格輸入數(shù)據(jù)。在單元格C8中輸入公式“ =PV(C7,C5,-C6)-C4 ”。單擊工具菜單，選擇方案項(xiàng)，彈出方案管理器對話框。3方案管理器應(yīng)用4投資8000. 00£項(xiàng)目年限56年現(xiàn)金流量2800. 007貼現(xiàn)率10%8NPV¥2,6149101112131415-ai 點(diǎn)ABCDEG1第二步：單擊添加按鈕，打開添加方案對話框。根據(jù)題意添加3次，形成三個(gè)方案，分別是繁榮、般、不景氣。如圖 2-14。AECD123方案管理器應(yīng)用4投資8000. 00匕項(xiàng)目年限5年現(xiàn)金流量2800. 007貼現(xiàn)率1