關鍵字:動态圖表;盈虧分析;量本利
欄目:圖表哈喽,小夥伴們,你們好呀!
每天學點Excel,工作效率up,up~
美好的周末已經過去了,又開始了新的一周!
開始之前,先問大家一個問題:你知道什麼是量本利分析嗎?
乍一看,似乎一頭霧水。
量本利分析,簡稱CVP分析。其實就是根據業務量(指産量、銷售量、銷售額等)、成本和利潤三者之間的互相依存關系,進行綜合分析。
這樣子一解釋,似乎好像更加難懂了。
站在老闆的角度,其實就是用以預測利潤,是否保本,盈虧如何的分析。
在企業經營決策、利潤規劃、成本目标的确定等方面應用很廣,其中心内容是盈虧臨界點分析。
如下圖所示:
從圖示中,我們可以根據調整“單價”的滾動條表單控件,得到一個動态圖表,從中可以直觀的看出“盈虧平衡點”以及“平衡點上的參數”。這樣交給領導做定價決策就很方便了。
下面一起來看看它是如何通過Excel實現的吧。
STEP1:建立數據區域
單元格地址 |
内容摘要 |
函數 |
B1 |
單價 |
=C1/4 |
B2 |
銷量 |
=C2*1000 |
B3 |
銷售收入 |
=B2*B1 |
B4 |
固定成本 |
固定值 |
B5 |
單位變動成本 |
固定值 |
B6 |
總變動成本 |
=B5*B2 |
B7 |
總成本 |
=B4+B6 |
B8 |
銷售收入 |
=B2*B1 |
B9 |
利潤 |
=B8-B7 |
B10 |
盈虧平衡銷量 |
=IFERROR(ROUNDUP(B4/(B1-B5),0),0) |
B11 |
盈虧平衡銷售收入 |
=B10*B1 |
B12 |
盈虧平衡制表參數 |
=A1&":"&B1&CHAR(10)&A10&":"&B10&CHAR(10)&A11&":"&B11 |
按上方的内容,建立測算數據區域如下圖所示:
STEP2:建立滾動條,控制單價和銷量
在工具欄——開發工具——插入——表單控件:滾動條,插入控件。鼠标選中控件,點擊鼠标右鍵,彈出命令菜單,選擇“設置控件格式”;在彈出的窗口中,按下圖配置參數,如下:
按“确定”按鈕,此時,我們使用滾動條的時候,就會發現【單價】字段會随着滾動條的調整而變化;同理制作【銷量】的滾動條,如下設置:
STEP3:建立圖表數據區域
單元格地址 |
摘要 |
函數 |
B16 |
最小銷量 |
銷量滾動條最小值*1000 |
B17 |
最小銷量-銷售收入 |
=B16*B1 |
B18 |
最小銷量-總成本 |
=B4+B5*B16 |
B19 |
最小銷量-利潤 |
=B17-B18 |
C16 |
最大銷量 |
銷量滾動條最大值*1000 |
C17 |
最大銷量-銷售收入 |
=C16*B1 |
C18 |
最大銷量-總成本 |
=B4+B5*C16 |
C19 |
最大銷量-利潤 |
=C17-C18 |
制作完後,如下圖所示:
STEP4:制作圖表
選中A16:C19單元格區域,插入圖表——帶直線的散點圖;
得到下面的圖表:
鼠标對圖表,點擊右鍵,在彈出的命令菜單中選擇“選擇數據”,按下圖箭頭的地方,點擊“切換行/列”,如下所示:
STEP5:插入“盈虧平衡點”
依然對圖表,點擊鼠标右鍵——選擇“選擇數據”,點擊“添加”新的系列;
在彈出的窗口中,按下圖設置參數引用,如下:
及此,圖表部分制作完畢。具體的美工部分這裡就不展開講了。
知識拓展:制作并使用一次“量本利分析”後,我們應該從中學到一些關于數據應用的知識。
1、盈虧平衡和什麼有關系?
通過圖表滾動條的變化,我們發現,圖表内容和“單價”有關,和“銷量”無關!這是因為,銷量的增加,會導緻“總變動成本”的增加,而且是成正比增加,這個比例是定值;而真正影響盈虧平衡的因素,是“單價”和“單位變動成本”。
2、銷量在經營中的意義何在?
如果說盈虧平衡與銷量無關,那麼銷量在經營數據中的意義到底是什麼?作者認為是“體量”。一個公司的銷售渠道不可能無限擴展的,一定是在市場上有着自己的比例,即:市場份額。我們通過上面的圖表工具,測算出“盈虧平衡銷量”,那麼經營者就要考慮:
a、産能允許生産出這個數量的産品嗎?
b、我們是否可以賣掉這個數量的産品?
如果不能滿足,那麼如何調整策略來滿足這個條件,那麼銷量的作用就是這個了,它不是決定因素,而是決策因素。
好啦!
以上就是對“量本利分析”的一個基本總結和應用分享,感興趣的同學們可以自己試着做一做。
有話要說...