用電子表格進行本量利分析的方法

用電子表格進行本量利分析的方法用電子表格進行本量利分析的方法用電子表格進行本量利分析的方法xxx公司用電子表格進行本量利分析的方法文件編號：文件日期：修訂次數(shù)：第1.0次更改批準審核制定方案設(shè)計，管理制度用電子表格進行本量利分析的方法用電子表格進行本量利分析的方法用電子表格進行本量利分析的方法用電子表格進行本量利分析的方法

本量利分析是管理會計的核心內(nèi)容，它是對企業(yè)一定時期和一定業(yè)務量范圍內(nèi)的固定成本、變動成本、銷售量、銷售單價、利潤等變量之間數(shù)量關(guān)系進行的一種定量分析方法，在企業(yè)預測、決策、規(guī)劃和控制中得到廣泛的應用。本量利分析本身并無復雜的理論和方法可言，但是本量利分析的計算工作量相當大，特別在因素分析時，如果變動的因素在一個以上，甚至全部因素都變動，那么其計算工作量就可想而知了。然而應用“MicrosoftExcel97電子表格”進行本量利分析就會使本量利分析的計算工作大大簡化，可以毫不夸張地講，用“Excel電子表格”進行本量利分析無疑是最簡便、最直觀、最經(jīng)濟和最迅速的方法。本文將舉例說明如何使用“Excel電子表格”進行本量利分析的步驟和方法。

1．啟動Excel輸入相關(guān)資料

啟動“Windows95”或“Windows98”之后，用鼠標單擊“開始”，再單擊“程序”及其下拉菜單中的“MicrosoftExcel”程序，即可啟動“Excel電子表格”，出現(xiàn)一張標為“Book1”的空白電子表格。在空白的電子表格中輸入進行本量利分析相關(guān)資料，并另存為“本量利分析”。

2．輸入公式計算保本點和保利點

用鼠標單擊B4輸入公式“=B2－B3”，用鼠標單擊E2單元格，輸入公式“=B5/(B2－B3)”，用鼠標單擊E3單元格，輸入公式“=B5/(B4/B2)”，用鼠標單擊E4單元格，輸入公式“=(B5＋B6)/(B2－B3)”，用鼠標單擊E5單元格，輸入公式“=(B5＋B6)/(B4/B2)”，按下回車鍵就分別計算出保本量、保本額、保利量和保利額指標，即創(chuàng)建完成了一個本量利分析的基本模型。

3.因素變動的本量利分析

以上本量利分析的基本模型創(chuàng)建完成后，就可以大大簡化了因素變動的本量利分析。在本量利分析中我們關(guān)心的是當單價、單位變動成本、固定成本總額、目標利潤等因素中的一項或幾項甚至全部發(fā)生變動時對保本點和保利點的影響。

假定上例中的單價由元下降到100元，該因素的變動對保本點和保利點會產(chǎn)生什么影響？在手工條件下需要重新計算，工作量比較繁重，而利用以上模型問題就簡單多了，只要在B2單元格中輸入100并按下回車即可，模型將自動計算出單價因素變動后的保本量、保本額、保利量和保利額指標，在本例中分別為9194件、919355元、12151件和1215054元。

除單價外，利用本量利分析基本模型對單位變動成本、固定成本總額、目標利潤等其他任意一個因素變動對保本點和保利點影響的分析方法，完全和分析單價因素變動的方法相同，即使幾個甚至全部因素同時發(fā)生變動時也是如此簡單。

4.保本點和保利點變動對各因素影響程度分析

上面我們已經(jīng)掌握了各因素變動對保本量和保利點影響的分析方法，然而在本量利分析中常常也需要了解當保本點或保利點被修正后，影響保本點和保利點的各因素可能發(fā)生變化的幅度。例如我們將上例中的保本量由9194件修正為9000件，那么單價要上升到什么水平或單位變動成本要降到什么水平或固定成本總額要降到什么水平反之亦然。在手工計算的條件下解決這一問題無疑是一件令人發(fā)怵的事情，然而利用Excel電子表格”的“單變量求解”功能，可以事半功倍地解決該問題。

用鼠標單擊E2單元格，然后單擊菜單欄中的“工具”項，在下拉菜單中單擊“單變量求解”命令，彈出“單變量求解”文本框。單擊“目標值(V)：”文本框并輸入“9000”，單擊“可變單元格(C)：”文本框，然后單擊B2單元格。

然后單擊“確定”按鈕，其單價可以變動的幅度就計算出來了，同時由于單價的變化，保本額、保利量和保利額均隨之發(fā)生了變動。在本例中保本量從9194件下降到9000件，單價則需要從100元提高到元。如圖5所示：

同理我們還可以對保本點、保本額、保利點和保利額等數(shù)據(jù)修正之后，用單變量求解的功能可以分別計算出單價，或單位變動成本，或固定成本總額，或目標利潤發(fā)生變動的幅度。

5.計算不同銷售量水平下的營業(yè)利潤

盡管我們可以利用前面已經(jīng)建立的本量利分析模型計算出不同因素變化所產(chǎn)生的各種結(jié)果，但是通常我們也需要掌握不同的銷售量對營業(yè)利潤所產(chǎn)生的影響。例如我們假定單價、單位變動成本、固定成本總額等因素不變，只將銷售量作為變量來觀察銷售量變化對利潤的影響程度。首先在B8：F8單元格區(qū)域內(nèi)分別輸入不同的銷售量“8000”、“9000”、“10000”、“11000”和“12000”，然后在B9：B12單元格區(qū)域內(nèi)輸入公式，即在B9單元格輸入“=B2＊B8”計算出銷售收入，在B10單元格輸入“=B8＊B3”計算出變動成本總額，在B11單元格輸入“=B5”計算出固定成本，在B12單元格輸入“=B9－B10－B11”，就計算出了產(chǎn)量為“8000”件時的“營業(yè)利潤”指標。

我們想要掌握的是不同銷售量條件下的營業(yè)利潤水平，現(xiàn)在還不能滿足我們的需要，使用“Excel電子表格”的“模擬運算表”功能就可以非常方便地完成其他銷售量條件下的營業(yè)利潤水平的計算工作。首先選中B8：F12單元格區(qū)域，然后單擊菜單欄的“數(shù)據(jù)”選項，在下拉菜單中單擊“模擬運算表”命令，彈出“模擬運算表”文本框。單擊“輸入引用行的單元格(R)：”，接著單擊B8單元格。

然后單擊“確定”按鈕，不同銷售量下的利潤就全部計算出來了