EXCLE動態模型在本量利中的應用分析

楊忠 夏利華

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

關鍵詞：EXCLE;動態模型;本量利分析

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

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

1 實訓案例基礎數據

ABC公司2018年營業周期內的預測相關數據如表1所示。

2 實訓案例內容設計

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

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

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

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

3 實訓案例具體實施步驟

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

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

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

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

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

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

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

（5）將靜態本量利分析圖調整為動態本量利分析圖。

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

打開文件-選項-自定義功能區-主選項卡-勾選“開發工具”-確定;點擊“開發工具”-插入-表單控件-數值調節鈕（窗體控件）。

②利用上下按鈕，根據相關參數的上下限范圍和步長數據，建立動態數據（注意：上下限超范圍和特殊步長的實現）。

“本期銷量（x）”：右擊“數值調節鈕”-設置控件格式-控制-依次輸入相應的“當前值、最大值、最小值、步長、單元格鏈接$B$4”-確定。

“單價（p）”：右擊“數值調節鈕”-設置控件格式-控制-依次輸入相應的“當前值1400、最大值1800、最小值0、步長100、單元格鏈接”-確定。

“單位變動成本（b）”：右擊“數值調節鈕”-設置控件格式-控制-依次輸入相應的“當前值、最大值、最小值、步長、單元格鏈接”-確定。

“固定成本（a）”：右擊“數值調節鈕”-設置控件格式-控制-依次輸入相應的“當前值、最大值、最小值、步長、單元格鏈接”-確定。

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

③點擊數值調節鈕，即可看到折線圖變動的過程。

（6）設置本量利分析報告區。

按照主要分析信息設置報告區內容，具體設計內容如表3所示。

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

4 實訓小結及注意事項

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

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

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

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

參考文獻

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

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