計(jì)算機(jī)財(cái)務(wù)建模教學(xué)案例
——本量利分析

呂志明

（天津財(cái)經(jīng)大學(xué) 會(huì)計(jì)學(xué)院，天津 300222）

0 引言

本量利分析是管理會(huì)計(jì)課程當(dāng)中非常重要的一項(xiàng)內(nèi)容，傳統(tǒng)教學(xué)方法無法直觀揭示本、量、利之間的動(dòng)態(tài)變化過程，也無法進(jìn)行動(dòng)態(tài)盈虧平衡點(diǎn)的分析。本文利用Excel 構(gòu)建動(dòng)態(tài)本量利分析相關(guān)模型，能夠動(dòng)態(tài)揭示本、量、利之間的變化關(guān)系以及盈虧平衡點(diǎn)的動(dòng)態(tài)分析，能夠彌補(bǔ)傳統(tǒng)本量利分析教學(xué)手段的不足，豐富本量利分析教學(xué)內(nèi)容，提高教學(xué)效率、改善教學(xué)效果。企業(yè)可參照本文模型，結(jié)合企業(yè)實(shí)際資料和數(shù)據(jù)，設(shè)計(jì)出適合企業(yè)自身情況的決策分析模型，從而為改善企業(yè)決策手段、提高決策質(zhì)量提供保障。具體包括動(dòng)態(tài)本量利分析模型和動(dòng)態(tài)盈虧平衡點(diǎn)模型。

1 動(dòng)態(tài)本量利分析模型

1.1 模型功能

動(dòng)態(tài)本量利分析基本模型如圖1 所示。利用滾動(dòng)條調(diào)節(jié)單價(jià)、單位變動(dòng)成本、當(dāng)前銷量、固定成本等數(shù)值時(shí)，本量利分析圖會(huì)相應(yīng)地進(jìn)行動(dòng)態(tài)調(diào)整，其中當(dāng)前銷量以紅色垂線顯示，并標(biāo)注其與成本線、收入線和利潤線的交點(diǎn)，即當(dāng)前銷量對應(yīng)的總成本、銷售收入和利潤總額。

圖1 動(dòng)態(tài)本量利分析模型

1.2 模型構(gòu)建方法與步驟

1.2.1 設(shè)置基本數(shù)據(jù)

利用滾動(dòng)條設(shè)置單價(jià)、單位變動(dòng)成本、當(dāng)前銷量和固定成本。

1.2.2 準(zhǔn)備圖表數(shù)據(jù)

第一，本量利數(shù)據(jù)。按照圖2 輸入數(shù)據(jù)和公式，然后利用數(shù)據(jù)表功能生成數(shù)據(jù)。

圖2 本量利數(shù)據(jù)公式設(shè)置

第二，當(dāng)前銷量數(shù)據(jù)。因?yàn)橐趫D表中顯示當(dāng)前銷量直線及其對應(yīng)的總成本、銷售收入和利潤，所以需要按照圖3 準(zhǔn)備數(shù)據(jù)。

圖3 當(dāng)前銷量數(shù)據(jù)

1.2.3 制作圖表

第一，同時(shí)選中區(qū)域“N1：Q1”和“N3：Q4”，制作“帶平滑線的散點(diǎn)圖”，并對圖表進(jìn)行必要的格式設(shè)置。

第二，添加當(dāng)前銷量直線。選中本量利圖表，添加數(shù)據(jù)系列，將“X 軸系列值”設(shè)置為“N6：N7”，將“Y 軸系列值”設(shè)置為“O6：O7”，并對圖表進(jìn)行必要的格式設(shè)置。

第三，添加當(dāng)前銷量直線與總成本線、銷售收入線和利潤總額線的交點(diǎn)系列。參照以上步驟，依次添加3 個(gè)系列，第1 個(gè)系列的X 軸系列值為“N9”，Y 軸系列值為“O9”，第2 個(gè)系列的X 軸系列值為“N10”，Y 軸系列值為“O10”，第3 個(gè)系列的X 軸系列值為“N11”，Y 軸系列值為“O11”。根據(jù)需要設(shè)置這三個(gè)系列點(diǎn)的格式。

1.2.4 保護(hù)工作表

在該模型中，只需用滾動(dòng)條設(shè)置單價(jià)、單位變動(dòng)成本、銷量和固定成本數(shù)據(jù)，為此，選中“C2：C4”和“D5”，取消其單元格鎖定狀態(tài)，然后保護(hù)工作表。

2 動(dòng)態(tài)盈虧平衡點(diǎn)模型

2.1 模型功能

模型樣式如圖4 所示。在給定的單價(jià)、單位變動(dòng)成本和固定成本下，根據(jù)一系列銷量制作成本線、收入線和利潤線，并以紅色垂直線顯示當(dāng)前條件下的盈虧平衡銷量及其與以上三條曲線的交點(diǎn)。當(dāng)通過微調(diào)鈕調(diào)整單價(jià)、單位變動(dòng)成本和固定成本時(shí)，各曲線將隨之動(dòng)態(tài)調(diào)整。此外，還可通過微調(diào)鈕設(shè)置當(dāng)前銷量，并在圖中顯示當(dāng)前銷量垂線及其與利潤線的交點(diǎn)，隨著銷量的變化，該垂線也將動(dòng)態(tài)調(diào)整。顯然，當(dāng)前銷量垂線與盈虧平衡垂線間的水平距離即為安全邊際。該模型可以直觀地動(dòng)態(tài)反映當(dāng)前給定條件下的盈虧平衡點(diǎn)以及安全邊際。

圖4 動(dòng)態(tài)盈虧平衡點(diǎn)模型

2.2 模型設(shè)計(jì)方法與步驟

2.2.1 設(shè)置基本數(shù)據(jù)

在該模型中，通過微調(diào)鈕設(shè)置變動(dòng)成本、單價(jià)、固定成本和當(dāng)前銷量。為此，需要進(jìn)行如下設(shè)置：

第一，變動(dòng)成本。利用文本框與微調(diào)鈕，將變動(dòng)成本設(shè)置為5～10 的整數(shù)，對以上各對象進(jìn)行必要的格式設(shè)置，并組合為一個(gè)對象。

第二，單價(jià)。利用文本框與微調(diào)鈕，將單價(jià)設(shè)置為15～20 的整數(shù)，對以上各對象進(jìn)行必要的格式設(shè)置，并組合為一個(gè)對象。

第三，固定成本。利用文本框與微調(diào)鈕，將固定成本設(shè)置為10 萬～15 萬的整數(shù)，對以上各對象進(jìn)行必要的格式設(shè)置，并組合為一個(gè)對象。

第四，當(dāng)前銷量。利用文本框與微調(diào)鈕，將當(dāng)前銷量設(shè)置為0～4 萬的整數(shù)，對以上各對象進(jìn)行必要的格式設(shè)置，并組合為一個(gè)對象。

2.2.2 準(zhǔn)備圖表數(shù)據(jù)

第一，成本線、收入線和利潤線數(shù)據(jù)。按照圖5 所示，準(zhǔn)備計(jì)算成本、收入和利潤的計(jì)算公式，并利用數(shù)據(jù)表功能求解各銷量對應(yīng)的成本、收入和利潤。

圖5 成本、收入和利潤公式

第二，盈虧平衡銷量直線數(shù)據(jù)。按照圖6 所示，準(zhǔn)備制作盈虧平衡銷量垂線數(shù)據(jù)和公式。

圖6 盈虧平衡銷量垂線數(shù)據(jù)

第三，盈虧平衡線與收入線交點(diǎn)坐標(biāo)。按照圖7 所示，準(zhǔn)備制作盈虧平衡線與收入線交點(diǎn)坐標(biāo)數(shù)據(jù)。

圖7 盈虧平衡線與收入線交點(diǎn)坐標(biāo)

第四，當(dāng)前銷量直線數(shù)據(jù)。按照圖8 所示，準(zhǔn)備制作當(dāng)前銷量直線的數(shù)據(jù)。

圖8 當(dāng)前銷量直線數(shù)據(jù)

第五，當(dāng)前銷量直線與利潤線交點(diǎn)坐標(biāo)。按照圖9 所示，準(zhǔn)備當(dāng)前銷量直線與利潤線交點(diǎn)坐標(biāo)。

圖9 當(dāng)前銷量直線與利潤線交點(diǎn)

2.2.3 制作圖表

第一，根據(jù)圖5 所示數(shù)據(jù)，制作散點(diǎn)圖，以顯示不同銷量下的收入線、成本線和利潤線。生成圖表后，可對圖表刻度、格式等進(jìn)行必要的設(shè)置。

第二，為圖表添加一個(gè)系列，用以顯示盈虧平衡線，X 軸系列值為“D16：D17”，Y 軸系列值為“E16：E17”。對系列格式進(jìn)行必要的設(shè)置。

第三，為圖表添加一個(gè)系列，用以顯示盈虧平衡線與收入線的交點(diǎn)，X 軸系列值為“D19”，Y 軸系列值為“E19”。對數(shù)據(jù)點(diǎn)格式進(jìn)行必要的設(shè)置。

第四，為圖表添加一個(gè)系列，用以顯示當(dāng)前銷量線，X 軸系列值為“D13：D14”，Y 軸系列值為“E13：E14”。對系列格式進(jìn)行必要的設(shè)置。

第五，為圖表添加一個(gè)系列，用以顯示當(dāng)前銷量線與利潤線的交點(diǎn)，系列X 值坐標(biāo)為“D21”，Y 值坐標(biāo)為“E21”。對數(shù)據(jù)點(diǎn)格式進(jìn)行必要的設(shè)置。

第六，在圖表中添加兩個(gè)文本框，分別動(dòng)態(tài)顯示盈虧平衡銷量和當(dāng)前利潤。在“D23”單元格輸入公式”＝IF（A1＞A3，＂盈虧平衡銷量＂＆＂＝＂＆ROUND（D19，0），＂＂）”，然后將某個(gè)文本框與“D23”進(jìn)行鏈接。在“D24”單元格輸入公式“＝＂當(dāng)前利潤＂＆＂＝＂＆ROUND（E21，0）”，然后將另一文本框與“D24”進(jìn)行鏈接。其中round（）為四舍五入函數(shù)，利用該函數(shù)將結(jié)果保留為整數(shù)?？蓪卧襁M(jìn)行必要的格式設(shè)置，并放置于圖表之上。

可以設(shè)置圖表的疊放次序，將其置于最底層，以顯示其上的文本框和各組合對象。

2.2.4 保護(hù)工作表

各微調(diào)鈕鏈接單元格均位于A 列，可將A 列隱藏，然后保護(hù)工作表。

3 模型的使用方法及效果

以上分別構(gòu)建了動(dòng)態(tài)本量利分析模型和動(dòng)態(tài)盈虧平衡點(diǎn)模型。在動(dòng)態(tài)本量利分析模型中，用戶可通過滾動(dòng)條分別設(shè)置單價(jià)、銷量、單位變動(dòng)成本和固定成本的數(shù)值，圖形將根據(jù)設(shè)置結(jié)果動(dòng)態(tài)顯示本量利分析圖，并以醒目的紅色垂線顯示當(dāng)前銷量，以及銷量垂線與收入線、成本線和利潤線的交點(diǎn)，并動(dòng)態(tài)標(biāo)注顯示當(dāng)前的收入、成本和利潤金額，從而更加直觀地揭示出本、量、利三者之間的關(guān)系。在動(dòng)態(tài)盈虧平衡點(diǎn)模型中，用戶可通過滾動(dòng)條設(shè)置單價(jià)、單位變動(dòng)成本和固定成本三個(gè)外生變量，以及當(dāng)前銷量這一內(nèi)生變量，圖形中將以不同顏色垂線分別表示當(dāng)前銷量和盈虧平衡點(diǎn)銷量，及其與收入線、成本線和利潤線的交點(diǎn)，從而可以動(dòng)態(tài)顯示盈虧平衡點(diǎn)，并與當(dāng)前實(shí)際本量利情況進(jìn)行直觀對比。以上兩個(gè)模型，極大地彌補(bǔ)了傳統(tǒng)本量利教學(xué)的不足，豐富了本量利教學(xué)的內(nèi)容，能夠有效提高教學(xué)質(zhì)量、改善教學(xué)效果。企業(yè)可參照該模型，結(jié)合企業(yè)實(shí)際資料和數(shù)據(jù)，設(shè)計(jì)出適合企業(yè)自身情況的決策分析模型，從而為改善企業(yè)決策手段、提高決策質(zhì)量提供保障。