本量利關系敏感性分析的Excel應用

1、本量利關系敏感性分析的Excel應用珠海城市職業(yè)技術學院經濟管理學院陳希本量利分析（Cost-Volume-ProfitAnalysis,CVP）是指在變動成本法基礎上，以數學化的會計模型與圖文來揭示固定成本、單位變動成本、銷售單價、保本點和保利點等變量之間的內在規(guī)律性聯系及企業(yè)的經營安全性,所提供的原理方法在管理會計中有著廣泛的用途,同時又是企業(yè)進行決策、計劃和控制的重要工具。在進行本量利分析時,主要變量有固定成本、單位變動成本、銷售單價、目標利潤、稅后目標利潤以及企業(yè)所得稅率,供企業(yè)進行財務決策的主要指標包括保本點、保利點、保凈利點和安全邊際率等。一、變動成本法下本-量-利關系（一）本量利

2、基本關系式營業(yè)利潤（P）=（單價-單位變動成本）x銷量-固定成本=（p-b）x-a;邊際貢獻（Tcm）=銷售收入-變動成本=（p-b）x;單位邊際貢獻（cm）=銷售單價-單位變動成本=p-b;邊際貢獻率（cmR）=單位邊際貢獻/銷售單價=cm/p。（二）保本點保本點又稱為盈虧臨界點（Break-evenPoint）,是指企業(yè)的經營規(guī)模（產銷量）剛好使企業(yè)達到不盈不虧的狀態(tài)，即營業(yè)利潤為零時的銷售量或銷售額。保本量=固定成本/（銷售單價-單位變動成本）=a/（p-b）,保本額=固定成本/邊際貢獻率=a/cmR。（三）保利點保利點是指在單價和成本水平確定的情況下，為確保預先確定的目標利潤（TP）能

3、夠實現而應達到的銷售量或銷售額。保利量=（固定成本+目標利潤）/（銷售單價-單位變動成本）=（a+TP）/（p-b）,保利額=（固定成本+目標利潤）/邊際貢獻率=（a+TP）/cmR。（四）保凈利點保凈利點是指在單價和成本水平確定的情況下為確保預先確定的稅后目標利潤能夠實現應達到的銷售量或銷售額。保凈利量=（固定成本+目標利潤）/單位邊際貢獻1-所得稅率保凈利額=（固定成本+目標利潤）/單位邊際貢獻率1-所得稅率（五）經營安全分析安全邊際量=計劃（實際）產銷數量-保本量安全邊際率=安全邊際量/計劃（實際）產銷量安全邊際率越低，說明企業(yè)的計劃產銷量或實際產銷量離保本量越近，隨著企業(yè)經營狀況的不確

4、定性出現虧損的可能性越大；安全邊際率越高，說明企業(yè)的計劃產銷量或實際產銷量高出保本量越多，經營安全性越好，一般標準如表1表1妥全邊際李救俏10%以下10-20%20%-30*30%-4(n40X以上經營冬全程度危險值得注意較安全安全非常安全二、利用Excel2010建立本-量-利模型（一）案例資料某企業(yè)只生產一種產品，單價5元，單位變動成本2.6元，2010年計劃產銷量60000件，目標利潤240000元，固定成本108000元，企業(yè)所得稅率25%，求保本點、保利點、保凈利點，并進行敏感性分析，以及對該企業(yè)的經營安全性做出判斷。（二）輸入變量打開一個新的Excel2010文檔，劃分好相關變量和

5、分析結果兩個功能區(qū)域，輸入案例資料中的變量，見圖1。|1單位變動咸本向定成專imw相關變量銷皆單價I.所鮒撫電3H標利潤40000稅后目標制潤2+000D計劃銷售亦0000r邊際加童曲際貢射率丄探本沱保本額分桁結果保利貸探利瓠亠探凈刑額左全邊際率翌萱妄金琨度（三）輸入公式輸入公式時必須用英文標點符號才有效。C5=C2-C1；F5=C5/C2；C6=F1/C5；F6=F1/F5；C7=（F1+C3）/C5；F7=（F1+C3）/F5C8=（F1+C3/（1-F2）/C5；F8=（F1+C3/（1-F2）/F5；C9=（C4-C7）/C4；F9單元格顯示的是經營安全程度，在不同的數值下分別對應危險

6、、值得注意、較安全、安全和非常安全等文字描述，要用到Excel中的if函數，在該單元格中輸入=IF（Cll=30%,F（C11=40%“很安全”“安全”），IF（Cll=20%,“較安全”，IF（Cll=10%，“值得注意”，“危險”）。全部輸完后，見圖2。cDBF單位變動咸耳固定康本10000衛(wèi)相共變畳5所得律率25%況后目標利潤240000計劃錯售量WOOO5邊際貢就2,4邊際頁獻卒酣伽A45000保本鎖225000T分析結果145000FOOO二品團178333保凈軻額891&7笛全邊麻率26鯉營負全程J度敦蚩全（四）設置數值調節(jié)鈕（窗體控件）進行各變量的敏感性分析相對于早期版本Exce

7、l2010的窗體控件的使用有較大變化。具體操作是：（1）單擊“文件”菜單在下拉菜單中選擇“選項”。（2）在“選項”對話框中單擊“自定義功能區(qū)”在“主選項卡”中找到“開發(fā)工具”單擊“開發(fā)工具”對應的復選框在中打上，點“確定”回到Excel主界面后，在菜單列表中已經有了“開發(fā)工具”菜單。（3）單擊“開發(fā)工具”菜單，再單擊“插入”打開“表單控件”，單擊數值調節(jié)鈕（窗體控件）圖標后，光標變成+”形狀，在D1單元格中拖動鼠標，松開后即在該單元格中出現窗體控件圖標。（4）右鍵單擊D1單元格中的窗體控件圖標，選擇“設置控件格式”，進入設置控件格式對話框。見圖3。（5）在“單元格鏈接”中輸入$C$1，點確定。

8、同樣操作，在D2單元格建立窗體控件，鏈接到C2，出現如圖4所示的界面。通過單擊D1單元格的數值調節(jié)鈕，可以上下調節(jié)C1單元格數值的大小，在分析結果區(qū)域相關單元格中的計算結果會隨著單位變動成本和銷售單價變量的變化而發(fā)生變化。（6）在現有的Excel版本中，控件設置格式對話框中的“步長”欄中，能輸入的最小數值為1，即限制了數值調節(jié)鈕上下調節(jié)的最小單位為1個單位，無法精確到小數位，而在企業(yè)的實際情況中，單位變動成本和售價可能到小數點后兩位數字（1分錢），因此，對相關的鏈接我們還需要做一些技巧性的處理。（7）在G1中輸入260，在C1中輸入公式：C1=G1/100;在G2中輸入500，C2中輸入公式：

9、C2=G2/100。（8）右鍵單擊D1單元格中的數值調節(jié)鈕，進入設置控件格式對話框，在“單元格鏈接”中輸入$G$1;同樣處理，把D2單元格中窗體控件鏈接到G2單元格。（9）右鍵單擊G列，選擇“隱藏”即可完成Excel2010下的本量利關系敏感性分析模型?，F在，點擊D1或D2單元格中的數值調節(jié)鈕，就可以使C1單元格和C2單元格中數字的變化精確到0.01個單位，也就是說在本量利關系敏感性分析模型中，單位變動成本和售價的敏感性分析精確到了1分錢的變動。見圖5。土小|恨護|慮性|可送站1-；屮BI三蛙陰樹驗cEF1單位變動成本16固定感本1Q60QQZ相關變量留售單樸5J所25KS目折利胸240000稅后目標利河240000缶計劃體管鱉6000002.14a.&4GQ0022EOOO分析結果145000保利頤725(03&保凈利量178333保浄利孫891667經莒童全程度fjCDE71單位變動成本i.iii圭回定成本LOW相關變量5.01259&3目穌利潤240000撫方目標刑潤240000計劃SWXW5迪際貢獻24邊際貢獻率47伽保本訛45W0保本瓠225+50分析結果14&000腺剝額72+50t保睜利婁178333畫34丸p安全邊際率餐營安全程J度較安全用Excel軟件可以