投資項目盈虧平衡分析及EXCEL的應(yīng)用.doc

1、投資項目盈虧平衡分析及excel的應(yīng)用摘要：在項目投資決策中，動態(tài)盈虧平衡分析通常被用來判斷項目的經(jīng)營風(fēng)險，從而提高決策的科學(xué)性。本文通過案例分析，運用excel。構(gòu)建投資項目的動態(tài)盈虧平衡分析模型，其結(jié)論有助于項目執(zhí)行者在執(zhí)行決策認識到充分利用excel的重要性，從而預(yù)先考慮措施與對策，實現(xiàn)科學(xué)決策。關(guān)鍵詞：excel；投資項目；動態(tài)盈虧平衡分析盈虧平衡分析是在成本形態(tài)分析和變動成本法的基礎(chǔ)上進 一步分析研究銷量、價格、成本及利潤直接的內(nèi)在規(guī)律性聯(lián)系， 為企業(yè)進行預(yù)測、決策、控制和計劃提供必要的財務(wù)信息的一種 定量分析方法。西方管理學(xué)家通常認為企業(yè)管理的重心在于經(jīng)營，經(jīng)營的重心在于決策，即充

2、分利用所掌握的信息，為實現(xiàn)某一目標(biāo)，從 而達到資源（人、財、物等）的優(yōu)化配置。盈虧平衡分析法是企業(yè) 經(jīng)營決策中常用的工具之一，它是根據(jù)產(chǎn)品的業(yè)務(wù)量（產(chǎn)量或銷 量）、成本、利潤之間的相互制約關(guān)系的綜合分析，用來預(yù)測利 潤，控制成本，判斷經(jīng)營狀況的一種數(shù)學(xué)分析方法，亦即通過成 本、銷售量、利潤三者之間的關(guān)系分析，確定項目的最低生產(chǎn)和 銷售水平，即盈虧平衡點，也稱為保本點。在上海財經(jīng)大學(xué)出版社出版的由劉蘭娟主編的財經(jīng)管理中的計算機應(yīng)用一書中，介紹了用excel建立盈虧平衡分析模型的方法。在該書中作者運用公式計算邊際貢獻、銷售收益、總成本和利潤，運用模擬運算表作出制圖所需的數(shù)據(jù)，用散點圖來展示邊際貢獻

3、、固定成本與利潤的關(guān)系，并用模擬運算表的方法在圖形中加入盈虧平衡銷量垂直參考線和當(dāng)前銷量垂直參考線，用微調(diào)項控件來對銷量和售價進行調(diào)節(jié)，將調(diào)節(jié)對利潤和盈虧平衡點的影響結(jié)果顯示在文本框中。利用excel做的將業(yè)務(wù)、公式和圖表緊密結(jié)合后的一個盈虧平衡分析的圖形 ，交互式的功能讓管理人員體會到數(shù)據(jù)在“說話”，他只需調(diào)整銷量和售價兩個參數(shù)，馬上在圖表上展示出邊際貢獻、固定成本與利潤的關(guān)系圖，同時還能給出盈虧平衡的分析結(jié)果，是盈利還是虧損，以及該售價所對應(yīng)的盈虧平衡銷量。excel制作的盈虧平衡分析模型，在此模型中可以調(diào)節(jié)銷量和售價兩個變量，求得相應(yīng)銷量下的利潤情況和相應(yīng)單價對應(yīng)的平衡銷量并形象地將結(jié)果

4、展示出來。模型采用結(jié)構(gòu)圖的方式能夠形象地展現(xiàn)出影響利潤的各個因素之間的關(guān)系，比如可以通過調(diào)整可變成本的滑塊，變化各種相關(guān)的因素，圖中的直接人工等因素就會發(fā)生變化進而會影響總的可變成本，并帶動總成本，最終影響利潤。而影響固定成本的主要因素，包括管理層薪酬、保險費用、折舊、廣告等，這里采用了直接在進度條上拖動的方式，根據(jù)業(yè)務(wù)狀況靈活調(diào)整，來影響固定成本，并帶動總成本，最終影響利潤。一、傳統(tǒng)盈虧平衡分析模型描述1、基本模式excel電子表格建立盈虧平衡分析模型的方法，可采用公式計算、單變量求解、規(guī)劃求解等尋找盈虧平衡點的多種方法，分析各種管理參數(shù)的變化對盈虧平衡點的影響。盈虧平衡分析問題描述：銷售量

5、q，銷售收益r，總成本c以及利潤之間的關(guān)系的模型：銷售收益r=銷售量q*銷售單價p總成本c=固定成本+變動成本v變動成本v=單位變動成本v*銷售量q總成本c=固定成本+單位變動成本v*銷售量q利潤=銷售收益r總成本c單位邊際貢獻=銷售單價p 單位變動成本v邊際貢獻=銷售收益r 變動成本v邊際貢獻率k=單位邊際貢獻/銷售單價2、盈虧平衡銷量q0和盈虧平衡銷售收益r0二、excel中建立盈虧平衡分析模型的步驟（1）在excel中建立盈虧平衡分析的框架，輸入產(chǎn)品的單價、單位變動成本、固定成本（2）給定銷售量的情況下，計算總成本、銷售收益、利潤等。（3）可繪制利潤隨著銷售量改變的xy散點圖形（利用模型

6、運算表）（4）計算盈虧平衡點：可以使用a：單變量求解 b：規(guī)劃求解 c：公式計算（5）可繪制參數(shù)（例如單價）對盈虧平衡點的影響（6）根據(jù)預(yù)期的利潤確定實現(xiàn)該利潤的產(chǎn)品銷量三、案例分析某公司制造一種高質(zhì)量運動鞋，公司管理層邀請你幫助公司整理用于管理決策的信息，公司最高生產(chǎn)能力為1500。一項銷售調(diào)查顯示明年的平均每雙銷售價格定為90元；公司的成本數(shù)據(jù)為：固定成本為37800元，每雙可變成本為36元。若當(dāng)前的銷量為900，要求：1、 計算單位邊際貢獻及邊際貢獻率；2、 計算銷售收益、總成本及利潤；3、 盈虧平衡（保本點）銷量及盈虧平衡銷售收益；4、 假若公司預(yù)算利潤為24000元，計算為達到利潤目

7、標(biāo)所需要的銷量及銷售收益；5、根據(jù)公司的銷售收益、總成本、利潤等數(shù)據(jù)，繪制本-量-利圖形；通過圖形動態(tài)反映出銷量從100按增量10變化到1500時利潤的情況及“盈利”、“虧損”、“保本”的決策信息。6、假定銷售單價從80元按增量變化到100元時，計算出盈虧平衡銷量和盈虧平衡銷售收益的相應(yīng)變化值？并且以圖形方式動態(tài)反映。操作步驟：（1）在excel中建立模型，計算單位邊際貢獻、單位邊際貢獻率、銷售收益、總成本、利潤、盈虧平衡銷量、盈虧平衡銷售收益各個單位格公式如下：c9：=c5c6 c10：=c9/c5 c11：=c2*c5c12：=c6*c2+c7 c13：=c11c12 c15：=c7/c9

8、c16：=c15*c5（2）利用單變量求解方法計算目標(biāo)利潤對應(yīng)的目標(biāo)銷售量和目標(biāo)銷售收益（3）繪制公司的銷售收益、總成本、利潤等數(shù)據(jù)，繪制本量利。a：利用模擬運算表準(zhǔn)備作圖數(shù)據(jù)。以銷售量為變量，銷售收益、總成本、利潤進行單變量模擬。公式如下：g3：=b11 h3：=b12 i3：=b13模擬運算參數(shù)：選f3：i5b：選擇f2：i2 f5：i6，繪制xy散點圖，注意：數(shù)據(jù)系列選擇列。調(diào)整圖形為c：構(gòu)造垂直參考線的數(shù)據(jù)增加銷售量垂直線參考：公式：f8：=b15 f9：=b15 g8：-40000 g8：140000選擇f8：g9，選擇“編輯”的“選擇性粘貼”增加銷售量垂直參考線：公式：f12：=b

9、2 f13：=b2 g12：-40000 g13：140000選擇f12：g13，選擇圖形，選擇“編輯”的“選擇性粘貼”d：在表格的a19和a20建立公式，反映建模型的結(jié)果：a19：=銷售量為: &b2&if(b130,贏利,if(b13=0,平衡,虧損)a20：=售價=&b5&元，盈虧平衡銷量=&round(b15,0)e：在圖表中創(chuàng)建微調(diào)器控件，反映銷售量和單價與利潤之間的關(guān)系。在圖表中創(chuàng)建標(biāo)簽：公式是：=$b$19同樣：創(chuàng)建單價和銷售利潤之間的關(guān)系：四、傳統(tǒng)盈虧平衡分析的缺陷和改進1、傳統(tǒng)盈虧平衡分析的原理及缺陷 傳統(tǒng)盈虧平衡分析，是通過把成本劃分為固定成本和可變成本。假定產(chǎn)銷量一致，根

10、據(jù)成本、產(chǎn)量、售價和利潤之間的函數(shù)關(guān)系，找出產(chǎn)量、產(chǎn)品價格、單位產(chǎn)品的可變成本、年固定成本、生產(chǎn)能力利用率等因素的盈虧平衡點，再結(jié)合我們預(yù)測的各個因素可能的變動情況，對項目的風(fēng)險情況及項目對各因素不確定性的承受能力進行大致判斷的一種分析方法。其中，某因素的盈虧平衡點就是項目處于盈虧平衡狀態(tài)時該因素的數(shù)值，而在傳統(tǒng)盈虧平衡分析中，盈虧平衡狀態(tài)定義為銷售收入等于總成本即項目盈利為零的狀態(tài)。傳統(tǒng)盈虧平衡分析的主要不足之一是，它是以盈利為零作為盈虧平衡狀態(tài)，沒有考慮資金的時間價值，是一種靜態(tài)分析。而建設(shè)項目生產(chǎn)經(jīng)營期是一個長期的過程，資金在其運動過程中具有潛在的隨時間推移而產(chǎn)生增值的能力，因此，在進行

11、各種投資時，必須考慮投資的實踐機會成本、資金的時間價值，按照投資的具體情況、目標(biāo)以及所在行業(yè)的基準(zhǔn)收益水平確定一個基準(zhǔn)收益率。傳統(tǒng)盈利為零的盈虧平衡實際上意味著項目已經(jīng)損失了基準(zhǔn)收益水平的收益，項目存在著潛在的虧損。為克服傳統(tǒng)靜態(tài)盈虧平衡分析的不足，對其進行改進，進行動態(tài)盈虧平衡分析。2、傳統(tǒng)盈虧平衡分析的改進為克服傳統(tǒng)盈虧平衡分析的不足，可以對其進行改進，方法是進行非線性盈虧平衡分析、動態(tài)盈虧平衡分析和動態(tài)多因素盈虧平衡分析。 2.1 非線性盈虧平衡分析 非線性盈虧平衡分析就是把銷售收入看成產(chǎn)品價格和產(chǎn)量的多元函數(shù)，同時價格又是產(chǎn)量的函數(shù)；把成本看成單位產(chǎn)品可變成本和產(chǎn)量的多元函數(shù)，同時單位

12、產(chǎn)品可變成本又是產(chǎn)量的函數(shù)。據(jù)盈虧平衡等式可求出產(chǎn)量盈虧平衡點q1、q2，并由d(tr-tc)dq0可求出最大盈利點qmax。如圖1，當(dāng)項目的產(chǎn)量介于q1、q2之間時，項目處于盈利狀態(tài)；而當(dāng)產(chǎn)量低于較小的平衡點或高于較大的平衡點時，項目均處于虧損狀態(tài)；qmax為最佳經(jīng)濟規(guī)模點。又根據(jù)價格與產(chǎn)量及單位產(chǎn)品可變成本與產(chǎn)量的函數(shù)關(guān)系可以求出價格盈虧平衡點和單位產(chǎn)品可變成本盈虧平衡點。圖1 盈虧平衡圖2.2 動態(tài)盈虧平衡分析動態(tài)盈虧平衡分析將項目盈虧平衡狀態(tài)定義為凈現(xiàn)值npv等于零的狀態(tài)，便能將資金的時間價值考慮在盈虧平衡分析內(nèi)，變靜態(tài)盈虧平衡分析為動態(tài)盈虧平衡分析。由于凈現(xiàn)值的經(jīng)濟實質(zhì)是項目在整個經(jīng)

13、濟計算期內(nèi)可以獲得的、超過基準(zhǔn)收益水平的、以現(xiàn)值表示的超額凈收益，所以，凈現(xiàn)值等于零意味著項目剛好獲得了基準(zhǔn)收益水平的收益，實現(xiàn)了資金的基本水平的保值和真正意義的“盈虧平衡”。動態(tài)盈虧平衡分析不僅考慮了資金的時間價值，而且可以根據(jù)企業(yè)所要求的不同的基準(zhǔn)收益率確定不同的盈虧平衡點，使企業(yè)的投資決策和經(jīng)營決策更全面、更準(zhǔn)確。其中，ncf為所得稅前年凈現(xiàn)金流量，tr為年銷售收入，oc為年經(jīng)營成本；ocf為年固定經(jīng)營成本，ta為年銷售稅金及附加， fi為年固定資產(chǎn)投資，li為流動資金本年增加額，sv為回收固定資產(chǎn)殘值，sli為回收流動資金， 為基準(zhǔn)收益率，r為銷售稅金的稅率，t為年份。 根據(jù)凈現(xiàn)值等于

14、零的等式可以計算出銷售收入、經(jīng)營成本、固定資產(chǎn)投資、產(chǎn)量、價格、單位產(chǎn)品可變成本等各個因素的動態(tài)盈虧平衡點。 2.3 動態(tài)多因素盈虧平衡分析 實際的應(yīng)用中，多因素盈虧平衡分析和動態(tài)盈虧平衡分析可以很好的結(jié)合，即在項目盈虧平衡分析是要分析多個因素同時變動對項目凈現(xiàn)值的影響，例如，雙因素動態(tài)盈虧平衡分析可以確定坐標(biāo)平面上的一條盈虧平衡線，三因素動態(tài)盈虧平衡分析可以確定坐標(biāo)空間上的一個盈虧平衡面，如果分析的因素超過三個，則所確定的是一個盈虧平衡的多個因素變動率關(guān)系。根據(jù)凈現(xiàn)值等于零的等式可以計算出凈現(xiàn)值等于零時銷售收入、經(jīng)營成本、固定資產(chǎn)投資、產(chǎn)量、價格、單位產(chǎn)品可變成本等多個因素同時變動的多個因素

15、變動率關(guān)系。 動態(tài)多因素盈虧平衡分析例證 例如：有一個石油化工的投資項目，其投資額、年銷售收入、年經(jīng)營成本、年銷售稅金、期末資產(chǎn)殘值如表1所示。采用的數(shù)據(jù)是根據(jù)對未來最可能出現(xiàn)的情況進行預(yù)測估算的。由于對未來影響經(jīng)濟效益的某些因素把握不大，投資額、經(jīng)營成本和產(chǎn)品價格均有可能在20%的范圍內(nèi)變動,設(shè)基準(zhǔn)收益率為10%，試進行動態(tài)多因素盈虧平衡分析。表1 現(xiàn)金流量表 萬元年份投資期第1年第210年第11年投 資 (i)-15 000銷售收入(tr)22 00022 000經(jīng)營成本(oc)15 20015 200銷售稅金(ta) (銷售收入的10%)2 2002 200期末資產(chǎn)殘值（sv）2 000

16、凈現(xiàn)金流量（ncf）-15 00004 6006 600解：方案凈現(xiàn)值計算公式為 npv-i(trtatc)(p/a，10%，10)(p/f，10%，1)sv(p/f，10%，11) -15 0004 6006.1440.909 12 0000.350 511 394(萬元) 如果同時考慮投資額與經(jīng)營成本的變動，設(shè)投資額變動的百分比為x，經(jīng)營成本變動的百分比為y，則兩個因素同時變動對方案凈現(xiàn)值影響的計算公式為： npvi(1x)trtaoc(1y)(p/a，10%，10)(p/f，10%,1)+sv(p/f,10%,11) 將表中的數(shù)據(jù)代入上式，經(jīng)過整理得 npv=11 394-15 000x-84 900y 取npv的臨界值，即令npv0，