財務(wù)人員實(shí)戰(zhàn)Excel之10--------本量利分析(未完，接下章)

第1節(jié)、盈虧平衡量的變動分析

第2節(jié)、本量利分析表,本量利分析樣圖

第10章　Excel 本量利分析

第1節(jié)、盈虧平衡量的變動分析

　　通過上一節(jié)的工作表建立、數(shù)據(jù)輸入，以及一系列的設(shè)置，我們已經(jīng)有了一張本量利分析表，現(xiàn)在我們就可以通過這個表來分析售價變動和單位成本變動對盈虧平衡量、盈虧平衡收入以及散點(diǎn)圖上盈虧平衡線的影響了。

步驟01   售價降低對盈虧平衡量的影響
　　拖動第一個滾動條，降低產(chǎn)品售價。此時散點(diǎn)圖中的盈虧平衡線向右側(cè)移動，表明盈虧平衡量增大，相應(yīng)的單元格數(shù)據(jù)也會增大。 

步驟02   售價提高對盈虧平衡量的影響
　　拖動第一個滾動條，提高產(chǎn)品售價。此時散點(diǎn)圖中的盈虧平衡線向左側(cè)移動，表明盈虧平衡量減小，相應(yīng)的單元格數(shù)據(jù)也會減小。 

步驟03   單位成本降低對盈虧平衡量的影響
　　拖動第二個滾動條，降低單位成本。此時散點(diǎn)圖中的盈虧平衡線向左側(cè)移動，表明盈虧平衡量減小，相應(yīng)的單元格數(shù)據(jù)也會減小。 

步驟04   單位成本提高對盈虧平衡量的影響
　　拖動第二個滾動條，提高單位成本。此時散點(diǎn)圖中的盈虧平衡線向右側(cè)移動，表明盈虧平衡量增大，相應(yīng)的單元格數(shù)據(jù)也會增大。

第2節(jié)、本量利分析表,本量利分析樣圖

本量利在財務(wù)分析中占有重要作用，通過設(shè)定的銷量、變動成本、固定成本和售價可以推算出盈虧平衡銷量及收入。本章以數(shù)據(jù)表和分析圖的形式展示某一數(shù)據(jù)發(fā)生變化時盈虧線的變化情況。

　　本量利就是對成本、銷量和利潤的分析。成本、銷量和利潤三者之間的變化關(guān)系是決定企業(yè)是否盈利的關(guān)鍵，本量利分析是定量分析出企業(yè)成本、銷量和利潤三者之間的變化關(guān)系。盈虧平衡點(diǎn)指標(biāo)是企業(yè)盈虧分界線，它也是由本量利分析引出。

 本量利分析效果圖

　　這里我們需要用到一些公式：
　　成本＝單位成本×產(chǎn)量＋固定費(fèi)用收入＝售價×產(chǎn)量在過到盈利平衡點(diǎn)時，成本＝收入。此時的盈虧平衡量＝固定費(fèi)用/（售價－單位成本）。固定費(fèi)用保持不變，售價和單位成本可以變動，因此盈虧平衡量是變動的。根據(jù)盈虧平衡量，可以求得，盈虧平衡收入＝盈虧平衡量×售價。 

步驟01   新建工作表并輸入字段名
　　新建一個工作簿，并在工作簿中重命名其中一個工作表，刪除其他的。輸入表格的標(biāo)題、數(shù)據(jù)，并設(shè)置格式等。

步驟02   輸入數(shù)據(jù)并設(shè)置格式
　　在B3:B18區(qū)域輸入產(chǎn)量數(shù)據(jù)，并新建立“固定費(fèi)用”、“售價”、“單位成本”等新的數(shù)據(jù)區(qū)域以備后用。 

步驟03   創(chuàng)建成本公式
　　在C3單元格輸入公式：=($K$26*B3+$C$28)/10000，并將公式復(fù)制到C4:C18區(qū)域。 

步驟04   創(chuàng)建收入公式
　　在D3單元格輸入公式：=($H$26*B3)/10000，并將公式復(fù)制到D4:D18區(qū)域。

步驟05   創(chuàng)建利潤公式
　　在E3單元格輸入公式：=D3-C3，并將公式復(fù)制到E4:E18區(qū)域。 

步驟06   設(shè)置盈虧平衡線橫坐標(biāo)數(shù)據(jù)
　　在C2 0單元格輸入標(biāo)題“盈虧平衡線輔助數(shù)據(jù)”。在C2 2單元格輸入公式：=ROUND(C28/(H26-K26),2)。并在C23:C25輸入公式：=C22。 

步驟07   設(shè)置盈虧平衡線縱坐標(biāo)數(shù)據(jù)
　　在D22單元格輸入“1800”；在D23單元格輸入公式：=(C23*H26)/10000；在D24單元格輸入“0”；在D25單元格輸入“-100”。 

步驟08   設(shè)置盈利平衡量和盈虧平衡收入
　　在B30單元格輸入“盈虧平衡量”，在C30單元格輸入公式：=C23。在B31單元格輸入“盈虧平衡收入”，在單元格輸入公式：=D23。同時，為了方便查看，可以設(shè)置一下相關(guān)單元格的單位和格式。 

步驟09   添加新命令
　　在“自定義快速訪問工具欄”中選擇“其他命令

步驟10   添加“滾動條”
　　彈出一個對話框，在下拉列表中選擇“不在功能區(qū)中的命令”，添加“滾動條（窗體控件）”。 

步驟11   插入滾動條
　　通過上面的設(shè)置，在自定義快速訪問工具欄中有了“滾動條”按鈕。單擊該按鈕，在G28單元格拖出一個滾動條。使用同樣的方法，在J28也拖出一個滾動條。 

步驟12   準(zhǔn)備設(shè)置滾動條格式
　　在剛剛插入的滾動條上點(diǎn)擊鼠標(biāo)右鍵，在彈出的菜單中選擇“設(shè)置控件格式”。

步驟13   設(shè)置滾動條格式
　　如圖所示，在彈出的對話框中設(shè)置滾動條相關(guān)的值。包括最小和最大值，更重要的是與之相關(guān)的單元格鏈接設(shè)置。

步驟14   設(shè)置另一個滾動條格式
　　設(shè)置方法與上一步相同，不同的是設(shè)置的值。

步驟15   生成散點(diǎn)圖
　　在“插入”選項卡中選擇“散點(diǎn)圖”，在彈出的菜單中選擇“帶平滑線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖”。 

步驟16   添加數(shù)據(jù)系列
　　雙擊散點(diǎn)圖，在工具欄上選擇“選擇數(shù)據(jù)”，在彈出的對話框中單擊“添加”，這一步的目的是向散點(diǎn)圖中添加新的數(shù)據(jù)系列。

步驟17   設(shè)置“盈虧平衡線”
　　在彈出的對話框中，系列名稱設(shè)置為“盈虧平衡線”。X軸的數(shù)據(jù)通過點(diǎn)擊文本框后面的編輯按鈕進(jìn)行選擇。本例中應(yīng)該選擇C22:C25。Y軸使用相同的方法，選擇D22:D25。最后，點(diǎn)擊 “確定”完成設(shè)置。

步驟18   調(diào)整坐標(biāo)刻度
　　我們看到，得到的散點(diǎn)圖Y軸的刻度不夠細(xì)，數(shù)據(jù)讀取不便，我們可以對它進(jìn)行一些設(shè)置。先選中散點(diǎn)圖，在“布局”選項卡中選擇如圖的項目。