如何用電子表格進(jìn)行本量利分析

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

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

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

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

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

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

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

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

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

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

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

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

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

　　同理我們還可以對保本點、保本額、保利點和保利額等數(shù)據(jù)修正之后，用單變量求解的功能可以分別計算出單價，或單位變動成本，或固定成本總額，或目標(biāo)利潤發(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＊�矪8”計算出銷售收入，在B10單元格輸入“=B8＊B3”計算出變動成本總額，在B11單元格輸入“=B5”計算出固定成本，在B12單元格輸入“=B9－B10－B11”，就計算出了產(chǎn)量為“8000”件時的“營業(yè)利潤”指標(biāo)。

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

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

　　從以上的介紹中我們不難意識到“Excel電子表格”是進(jìn)行本量利分析的強(qiáng)有力的工具，它不僅具有簡單易用的特點，而且可以使得本量利分析變得直觀、準(zhǔn)確、經(jīng)濟(jì)和迅速，極大地提高了本量利分析的工作效率，從而為管理會計更加廣泛的應(yīng)用開辟了廣闊的前景。