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

第二章Excel在財(cái)務(wù)管理分析中的應(yīng)用第一節(jié)公式及函數(shù)的高級應(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é)果，單擊輸入數(shù)組的單元格；如果數(shù)組公式將返回多個(gè)結(jié)果，則要選定需要輸入數(shù)組公式的單元格區(qū)域。⑵輸入數(shù)組公式⑶同時(shí)按“Ctrl+Shift+Enter”組合鍵，則Excel會自動在公式的兩邊加上大括號｛｝。在數(shù)組公式中，通常都是用單元格區(qū)域引用，但也可以直接鍵入數(shù)組數(shù)值，這樣鍵入的數(shù)組數(shù)值被成為數(shù)組常量。生成數(shù)組常量，必須按如下操作：⑴直接在公式中輸入數(shù)值，并用大括號“｛｝”括起來。⑵不同列的數(shù)值用逗號“，”分開。⑶不同行的數(shù)值用分號“；”分開例2－1：輸入數(shù)組常量。見圖2-1、2-2例2－2：輸入公式數(shù)組。輸入步驟同輸入常量數(shù)組，見圖2-32、編輯數(shù)組公式數(shù)組公式的特征之一就是不能單獨(dú)編輯、清除或移動數(shù)組公式所涉及的單元格區(qū)域中的某一個(gè)單元格。若在數(shù)組公式輸入后發(fā)現(xiàn)錯(cuò)誤需要修改，則需要按以下步驟進(jìn)行：⑴在數(shù)組區(qū)域中單擊任一單元格。⑵單擊公式編輯欄，當(dāng)編輯欄被激活時(shí)，大括號“｛｝”在數(shù)組公式中消失。⑶編輯數(shù)組公式內(nèi)容。⑷修改完畢后，按“Ctrl+Shift+Enter”組合鍵。3、刪除數(shù)組公式刪除數(shù)組公式的步驟是：首先選定存放數(shù)組公式的所有單元格，然后按Delete鍵。4、數(shù)組運(yùn)算規(guī)則以數(shù)字的數(shù)學(xué)運(yùn)算為例數(shù)字?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月份的銷售額和月平均銷售額。1-12月份的銷售額建立過程:第一步：建立如圖2-4所示的模型結(jié)構(gòu)，并輸入數(shù)據(jù)；第二步：選定單元格區(qū)域C7:N7，并輸入公式“＝C5:N5*C6:N6第三步：同時(shí)按“CTRL+SHIFT+ENTER"月平均銷售的計(jì)算過程：第一步：選定I10單元格第二步：輸入公式“＝AVERAGE(C5:N5*C6:N6)”第三步：同時(shí)按“CTRL+SHIFT+ENTER"2、用數(shù)組公式計(jì)算多個(gè)數(shù)據(jù)區(qū)域的和例2－4：某企業(yè)2004年銷售3種產(chǎn)品，用數(shù)組公式計(jì)算該公司2004年的總銷售額。計(jì)算月銷售總額過程：第一步：選取單元格區(qū)域C10:N10，見圖2-5第二步：輸入公式=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é)圖表處理動態(tài)圖標(biāo)的建立例2-5：某企業(yè)的8月各銷售部門一年內(nèi)個(gè)月銷售數(shù)據(jù)如下表所示：建立各部門的動態(tài)圖表。第一步：在單元格B16中輸入公式"=INDEX(B4:B11,B17)&"銷售圖",顯示選中某部門的銷售圖，如選中的部門2，則顯示的結(jié)果為“部門2銷售圖”。第二步：單擊[視圖]→窗體→組合框，在適當(dāng)位置畫組合框，并進(jìn)行設(shè)置，見設(shè)置控件格式圖。第三步：在單元格C16中輸入公式"=INDEX(C4:C11,$B$17)"。第四步：將C16中的公式復(fù)制到D16:N16。第五步：繪圖第三節(jié)數(shù)據(jù)分析處理第四節(jié)數(shù)據(jù)分析工具的應(yīng)用Excel提供了非常使用的數(shù)據(jù)分析工具，利用這些工具，可以解決財(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é)果進(jìn)行綜合對比，從而了解不同參數(shù)在其可能的取值范圍內(nèi)，對運(yùn)算結(jié)果的影響程度。㈠單變量模擬運(yùn)算表單變量模擬運(yùn)算表就是基于一個(gè)輸入變量，用它來測試對公式計(jì)算結(jié)果的影響。例2－6：企業(yè)向銀行貸款10000元，期限5年，測試不同利率對月還款額的影響。步驟：第一步：設(shè)計(jì)模擬運(yùn)算表結(jié)構(gòu)，如圖2-8。第二步：在單元格C7中輸入公式“=ABS(PMT(貸款利率/12,貸款期限*12,貸款總額))”，計(jì)算月?lián)Q款額。第三步：選取單元格區(qū)域B7:C16。第四步：單擊[數(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。第二步：在單元格C7中輸入公式“=PV(C6,C4,-C5)-C3”第三步：在單元格B13中輸入公式“=C7”第四步：選中單元格區(qū)域B13:G19。第五步：單擊[數(shù)據(jù)]，選擇[模擬運(yùn)算表]，彈出[模擬運(yùn)算表]對話窗，圖2-10。第六步：在本例中引用的是雙變量，所以在[輸入引用行的單元格]和[輸入引用列的單元格]中輸入變量。單擊[確定]按鈕，既的導(dǎo)彈變量模擬運(yùn)算表，見圖2-11。㈢單變量求解單變量求解就是求解只有一個(gè)變量的方程的根，方程可以是線性的，也可以是非線性的。單變量求解工具可以解決財(cái)務(wù)管理中涉及到一個(gè)變量的求解問題。例2－8：某投資項(xiàng)目，投資10000，項(xiàng)目期為5年，貼現(xiàn)率為8％。若要求該項(xiàng)的凈現(xiàn)值達(dá)到5000，則每年的凈現(xiàn)金流量應(yīng)為多少？第一步：設(shè)計(jì)如圖2-12所示的計(jì)算表格。第二步：在單元格C7中輸入公式“=PV(C6,C4,-C5)-C3”第三步：單擊[工具]菜單，選擇[彈變量求解]項(xiàng)，彈出[彈變量求解]對話框，如圖2-13。第四步：在[目標(biāo)單元格]中輸入“C7”，在[目標(biāo)值]中輸入“50000”，在[可變單元格]中輸入“$C$5”（四）方案管理器所謂方案，是保存在工作表當(dāng)中并可以相互之間進(jìn)行切換的數(shù)據(jù)。例如，對于一組現(xiàn)金流量分別使用不同的貼現(xiàn)率計(jì)算凈現(xiàn)值，就可以把不同條件下的結(jié)果保存在若干方案中，通過Excel的方案管理器在不同的結(jié)果間進(jìn)行切換，以對比和評價(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，貼現(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)行評價(jià)？第一步：建立如圖2-13所示的方案管理器數(shù)據(jù)結(jié)果。并在C4、C5、C6、C7單元格輸入數(shù)據(jù)。在單元格C8中輸入公式“=PV(C7,C5,-C6)-C4”第二步：單擊[添加]按鈕，打開添加方案對話框。根據(jù)題意添加3次，形成三個(gè)方案，分別是繁榮、一般、不景氣。如圖2-14。第三步：單擊[方案管理器]的[摘要]按鈕，可生成當(dāng)前保存的全部方案黨的匯總。如圖2-15。（五）規(guī)劃求解通過單變量求解，