楊忠 夏利華

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

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

中圖分類號(hào)：F23 文獻(xiàn)標(biāo)識(shí)碼：A doi：10.19311/j.cnki.1672-3198.2019.35.062

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

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

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

2 實(shí)訓(xùn)案例內(nèi)容設(shè)計(jì)

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

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

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

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

3 實(shí)訓(xùn)案例具體實(shí)施步驟

（1）建立一個(gè)工作表，將其命名為“標(biāo)準(zhǔn)式本量利分析圖”。

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

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

（4）繪制標(biāo)準(zhǔn)式本量利分析圖.采用“高低點(diǎn)法”繪制本量利分析圖，畫出收入線。

①選中最低點(diǎn)（0，0），高點(diǎn)（1800，504000）。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

當(dāng)本期銷售量為1400件時(shí)，營(yíng)業(yè)利潤(rùn)為178000元，此時(shí)保本點(diǎn)為（352.94，98823.5）;安全邊際絕對(duì)量為（1047.06，293176.47）;安全邊際率為74.79%;保本作業(yè)率為25.21%。

4 實(shí)訓(xùn)小結(jié)及注意事項(xiàng)

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

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

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

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

參考文獻(xiàn)

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

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