用一張動(dòng)態(tài)圖表,教你學(xué)會(huì)“量本利分析”
?
作者:E圖表述來源:部落窩教育發(fā)布時(shí)間:2022-03-24 15:33:09點(diǎn)擊:8311
編按:
“量本利分析”也叫本量利分析、盈虧平衡分析、保本分析,簡(jiǎn)稱為CVP分析。通過它的一系列叫法,我們就可以看出,這個(gè)分析法是對(duì)于控制成本的一種數(shù)學(xué)分析方法。使用Excel進(jìn)行量本利分析,最好的方式就是圖表。
量本利分析是根據(jù)業(yè)務(wù)量(指產(chǎn)量、銷售量、銷售額等)、成本和利潤(rùn)三者之間的互相依存關(guān)系,進(jìn)行綜合分析的。用以預(yù)測(cè)利潤(rùn),在企業(yè)經(jīng)營(yíng)決策、利潤(rùn)規(guī)劃、成本目標(biāo)的確定與控制等方面應(yīng)用很廣,其中心內(nèi)容是盈虧臨界點(diǎn)分析。
如下圖所示:
從圖示中,我們可以根據(jù)調(diào)整“單價(jià)”的滾動(dòng)條表單控件,得到一個(gè)動(dòng)態(tài)圖表,從中可以直觀的看出“盈虧平衡點(diǎn)”以及“平衡點(diǎn)上的參數(shù)”。這樣交給領(lǐng)導(dǎo)做定價(jià)決策就很方便了。
下面一起來看看它是如何通過Excel實(shí)現(xiàn)的吧。
STEP1:建立數(shù)據(jù)區(qū)域
單元格地址 |
內(nèi)容摘要 |
函數(shù) |
B1 |
單價(jià) |
=C1/4 |
B2 |
銷量 |
=C2*1000 |
B3 |
銷售收入 |
=B2*B1 |
B4 |
固定成本 |
固定值 |
B5 |
單位變動(dòng)成本 |
固定值 |
B6 |
總變動(dòng)成本 |
=B5*B2 |
B7 |
總成本 |
=B4+B6 |
B8 |
銷售收入 |
=B2*B1 |
B9 |
利潤(rùn) |
=B8-B7 |
B10 |
盈虧平衡銷量 |
=IFERROR(ROUNDUP(B4/(B1-B5),0),0) |
B11 |
盈虧平衡銷售收入 |
=B10*B1 |
B12 |
盈虧平衡制表參數(shù) |
=A1&":"&B1&CHAR(10)&A10&":"&B10&CHAR(10)&A11&":"&B11 |
按上方的內(nèi)容,建立測(cè)算數(shù)據(jù)區(qū)域如下圖所示:
STEP2:建立滾動(dòng)條,控制單價(jià)和銷量
在工具欄——開發(fā)工具——插入——表單控件:滾動(dòng)條,插入控件。鼠標(biāo)選中控件,點(diǎn)擊鼠標(biāo)右鍵,彈出命令菜單,選擇“設(shè)置控件格式”;在彈出的窗口中,按下圖配置參數(shù),如下:
按“確定”按鈕,此時(shí),我們使用滾動(dòng)條的時(shí)候,就會(huì)發(fā)現(xiàn)【單價(jià)】字段會(huì)隨著滾動(dòng)條的調(diào)整而變化;同理制作【銷量】的滾動(dòng)條,如下設(shè)置:
STEP3:建立圖表數(shù)據(jù)區(qū)域
單元格地址 |
摘要 |
函數(shù) |
B16 |
最小銷量 |
銷量滾動(dòng)條最小值*1000 |
B17 |
最小銷量-銷售收入 |
=B16*B1 |
B18 |
最小銷量-總成本 |
=B4+B5*B16 |
B19 |
最小銷量-利潤(rùn) |
=B17-B18 |
C16 |
最大銷量 |
銷量滾動(dòng)條最大值*1000 |
C17 |
最大銷量-銷售收入 |
=C16*B1 |
C18 |
最大銷量-總成本 |
=B4+B5*C16 |
C19 |
最大銷量-利潤(rùn) |
=C17-C18 |
制作完后,如下圖所示:
STEP4:制作圖表
選中A16:C19單元格區(qū)域,插入圖表——帶直線的散點(diǎn)圖;
得到下面的圖表:
鼠標(biāo)對(duì)圖表,點(diǎn)擊右鍵,在彈出的命令菜單中選擇“選擇數(shù)據(jù)”,按下圖箭頭的地方,點(diǎn)擊“切換行/列”,如下所示:
STEP5:插入“盈虧平衡點(diǎn)”
依然對(duì)圖表,點(diǎn)擊鼠標(biāo)右鍵——選擇“選擇數(shù)據(jù)”,點(diǎn)擊“添加”新的系列;
在彈出的窗口中,按下圖設(shè)置參數(shù)引用,如下:
及此,圖表部分制作完畢。具體的美工部分這里就不展開講了。
寫在最后:
制作并使用一次“量本利分析”后,我們應(yīng)該從中學(xué)到一些關(guān)于數(shù)據(jù)應(yīng)用的知識(shí),這也是個(gè)人成長(zhǎng)的一個(gè)很重要階段,思考后才會(huì)有所得。
1、盈虧平衡和什么有關(guān)系?
通過圖表滾動(dòng)條的變化,我們發(fā)現(xiàn),圖表內(nèi)容和“單價(jià)”有關(guān),和“銷量”無關(guān)!這是因?yàn)椋N量的增加,會(huì)導(dǎo)致“總變動(dòng)成本”的增加,而且是成正比增加,這個(gè)比例是定值;而真正影響盈虧平衡的因素,是“單價(jià)”和“單位變動(dòng)成本”。
2、銷量在經(jīng)營(yíng)中的意義何在?
如果說盈虧平衡與銷量無關(guān),那么銷量在經(jīng)營(yíng)數(shù)據(jù)中的意義到底是什么,筆者認(rèn)為是“體量”。一個(gè)公司的銷售渠道不可能無限擴(kuò)展的,一定是在市場(chǎng)上有著自己的比例,即:市場(chǎng)份額。我們通過上面的圖表工具,測(cè)算出“盈虧平衡銷量”,那么經(jīng)營(yíng)者就要考慮:
a、產(chǎn)能允許生產(chǎn)出這個(gè)數(shù)量的產(chǎn)品嗎?
b、我們是否可以賣掉這個(gè)數(shù)量的產(chǎn)品?
如果不能滿足,那么如何調(diào)整策略來滿足這個(gè)條件,那么銷量的作用就是這個(gè)了,它不是決定因素,而是決策因素。
編后語
以上就是對(duì)“量本利分析”的一個(gè)基本總結(jié)和應(yīng)用分享,感興趣的同學(xué)們可以自己試著做一做,分析一下當(dāng)前自己公司的銷售單價(jià),再結(jié)合公司的歷史數(shù)據(jù)、市場(chǎng)行情、原材料成本等等一些列數(shù)據(jù)做一份盈虧平衡分析報(bào)告,相信那時(shí)你就是領(lǐng)導(dǎo)眼中——那個(gè)最靚的仔。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
將excel中的數(shù)據(jù)按照指定數(shù)量進(jìn)行重復(fù),非得用VBA才能實(shí)現(xiàn)嗎?
Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能!
版權(quán)申明:
本文作者E圖表述;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!