EXCLE動態(tài)模型在本量利中的應(yīng)用分析

楊忠 夏利華

摘 要：通過一個實訓案例的設(shè)計，運用EXCLE工具建立動態(tài)模型，使讀者和學生通過繪制標準式本量利分析圖，設(shè)置基本指標計算區(qū)、輔助計算區(qū)和本量利分析報告區(qū)，熟悉控件工具箱的運用，掌握調(diào)節(jié)按鈕的使用、數(shù)值上下限超范圍情況的處理，以及特殊步長要求的實現(xiàn)技巧，提升本量利分析的質(zhì)量和效率。

關(guān)鍵詞：EXCLE;動態(tài)模型;本量利分析

中圖分類號：F23 文獻標識碼：A doi：10.19311/j.cnki.1672-3198.2019.35.062

本量利分析是成本-業(yè)務(wù)量-利潤關(guān)系分析的簡稱，是指在變動成本計算模式的基礎(chǔ)上，以數(shù)學化的會計模型與圖式來揭示固定成本、變動成本、銷售量、單價、銷售量等變量之間的內(nèi)在規(guī)律聯(lián)系。本量利分析基本公式是（單價-單位變動成本）*銷售量-固定成本=（p-b）*x-a。企業(yè)營業(yè)利潤與產(chǎn)品的邊際貢獻有著密切的聯(lián)系，可以將本量利分析法應(yīng)用于全面預(yù)算、成本控制和責任會計中。本量利分析與經(jīng)營風險分析相關(guān)聯(lián)，可促使企業(yè)努力降低風險。運用EXCLE工具建立動態(tài)模型，掌握分析規(guī)則與技巧，對提升本量利分析的質(zhì)量和效率有著重要的意義，下面通過實訓案例的設(shè)計做以詳細分析。

1 實訓案例基礎(chǔ)數(shù)據(jù)

ABC公司2018年營業(yè)周期內(nèi)的預(yù)測相關(guān)數(shù)據(jù)如表1所示。

2 實訓案例內(nèi)容設(shè)計

（1）建立一個工作表，將其命名為“標準式本量利分析圖”，在“標準式本量利分析圖”工作表上，根據(jù)初始數(shù)據(jù)設(shè)置利潤計算區(qū)，計算營業(yè)收入、變動成本、貢獻邊際和利潤指標。

（2）在同一工作表上，設(shè)置輔助計算區(qū)，計算總成本、保本點、安全邊際等指標，確定繪制標準式本量利分析圖相關(guān)變量的各項參數(shù);確定繪制傳統(tǒng)式本量利分析圖。

（3）利用視圖下拉菜單中的工具欄，調(diào)出控件工具箱，利用上下按鈕，根據(jù)相關(guān)參數(shù)的上下限范圍和步長數(shù)據(jù)，建立動態(tài)數(shù)據(jù)（注意：上下限超范圍和特殊步長的實現(xiàn)），將靜態(tài)本量利分析圖調(diào)整為動態(tài)本量利分析圖。

（4）設(shè)置本量利分析報告區(qū)，反映在特定業(yè)務(wù)量條件下的保本點、利潤水平和安全邊際指標。

3 實訓案例具體實施步驟

（1）建立一個工作表，將其命名為“標準式本量利分析圖”。

（2）在A10：B14區(qū)域計算利潤表。貢獻式利潤表計算步驟及公式如表2所示。

（3）在A16：B26區(qū)域輸入輔助計算數(shù)據(jù)。其中單位貢獻邊際=單價-單位變動成本，即=B5-B6;貢獻邊際率=單位貢獻邊際/單價，即=B17/B5;變動成本率=單位變動成本/單價，即=B6/B5;總成本=固定成本+單位變動成本*銷量，即=B7+B4*B6;保本量=固定成本/單價-單位變動成本，且保留兩位小數(shù)，即ROUND（B7/（B5-B6），2）;保本額=固定成本/貢獻邊際率，且保留兩位小數(shù)，即=ROUND（B7/B18，2）;安全邊際量=保本量-銷量，即=B4-B21;安全邊際額=營業(yè)收入-保本額，即=B10-B22;安全邊際率=安全邊際量/本期銷量，即=B23/B4;保本作業(yè)率=保本量/本期銷量，即=B21/B4，如圖1所示。

（4）繪制標準式本量利分析圖.采用“高低點法”繪制本量利分析圖，畫出收入線。

①選中最低點（0，0），高點（1800，504000）。

②單擊工具欄中“插入”選項，選中散點圖-僅帶數(shù)據(jù)標記的散點圖，右擊選擇數(shù)據(jù)，彈出“設(shè)置數(shù)據(jù)區(qū)域”對話框，單擊“添加”，輸入“收入線”，選中x軸，y軸數(shù)據(jù)區(qū)域，單擊“確定”，右擊圖中的點，添加趨勢線，勾選“線性”和“顯示公式”。

③固定成本線、總成本線等同理可得，如圖2所示。

（5）將靜態(tài)本量利分析圖調(diào)整為動態(tài)本量利分析圖。

①利用視圖下拉菜單中的工具欄，調(diào)出控件工具箱。

打開文件-選項-自定義功能區(qū)-主選項卡-勾選“開發(fā)工具”-確定;點擊“開發(fā)工具”-插入-表單控件-數(shù)值調(diào)節(jié)鈕（窗體控件）。

②利用上下按鈕，根據(jù)相關(guān)參數(shù)的上下限范圍和步長數(shù)據(jù)，建立動態(tài)數(shù)據(jù)（注意：上下限超范圍和特殊步長的實現(xiàn)）。

“本期銷量（x）”：右擊“數(shù)值調(diào)節(jié)鈕”-設(shè)置控件格式-控制-依次輸入相應(yīng)的“當前值、最大值、最小值、步長、單元格鏈接$B$4”-確定。

“單價（p）”：右擊“數(shù)值調(diào)節(jié)鈕”-設(shè)置控件格式-控制-依次輸入相應(yīng)的“當前值1400、最大值1800、最小值0、步長100、單元格鏈接”-確定。

“單位變動成本（b）”：右擊“數(shù)值調(diào)節(jié)鈕”-設(shè)置控件格式-控制-依次輸入相應(yīng)的“當前值、最大值、最小值、步長、單元格鏈接”-確定。

“固定成本（a）”：右擊“數(shù)值調(diào)節(jié)鈕”-設(shè)置控件格式-控制-依次輸入相應(yīng)的“當前值、最大值、最小值、步長、單元格鏈接”-確定。

注意：在設(shè)置“固定成本”的數(shù)值時，由于最大值限制為“30000”，故另選一個單元格，將這個單元格內(nèi)的數(shù)值按比例縮小10倍，“單元格鏈接”鏈接到其他單元格，當前單元格=鏈接的其他單元格*10，如圖3所示。

③點擊數(shù)值調(diào)節(jié)鈕，即可看到折線圖變動的過程。

（6）設(shè)置本量利分析報告區(qū)。

按照主要分析信息設(shè)置報告區(qū)內(nèi)容，具體設(shè)計內(nèi)容如表3所示。

當本期銷售量為1400件時，營業(yè)利潤為178000元，此時保本點為（352.94，98823.5）;安全邊際絕對量為（1047.06，293176.47）;安全邊際率為74.79%;保本作業(yè)率為25.21%。

4 實訓小結(jié)及注意事項

本次實訓涉及的計算量較多，從營業(yè)收入到安全邊際率，這些指標之間是可以聯(lián)系起來的，要掌握這些指標的計算;在計算完指標的基礎(chǔ)上，選取各特殊點，通過插入散點圖，繪制傳統(tǒng)的本量利分析圖;依據(jù)“數(shù)值調(diào)節(jié)鈕”功能，可以將傳統(tǒng)的本量利分析圖變成動態(tài)的本量利分析圖，在實際運用中更具有價值，具體注意事項如下：

（1）控件的取值介于0-30000，當遇到需要調(diào)節(jié)的范圍大于數(shù)值調(diào)節(jié)鈕規(guī)定的數(shù)值范圍時，可以利用乘數(shù)，設(shè)置中間單元格，使控件鏈接于其他表格，同時使取值是該表格的倍數(shù)（例如10倍），可以通過控件調(diào)整該表格來調(diào)整取值，幫助實現(xiàn)自動化。

（2）無論是“利潤表”，還是“輔助計算區(qū)”中的數(shù)值，要使用原始數(shù)據(jù)中的單元格利用鏈接和公式帶入計算，為方便初始數(shù)據(jù)變化的情況下，兩表數(shù)值，以及本量利分析圖和報告區(qū)中的數(shù)值隨當前數(shù)據(jù)的變化而變化，有利于建立動態(tài)本量利分析模型。

（3）設(shè)置本量利分析報告區(qū)，反映在特定業(yè)務(wù)量條件下的保本點、利潤水平和安全邊際指標。在表述報告區(qū)時也要用特殊的表達方式，從而使當前數(shù)值發(fā)生改變時，報告區(qū)的結(jié)果也能隨之變動。

參考文獻

[1]周麗媛，付艷.EXCLE在財務(wù)管理中的應(yīng)用[M].哈爾濱：東北財經(jīng)大學出版社，2014.

[2]韓良智.EXCLE在財務(wù)管理中的應(yīng)用[M].北京：清華大學出版社，2015.