本量利分析模型

1、項(xiàng)目 15 制作量本利分析模型量本利分析法， 全稱為產(chǎn)量成本利潤(rùn)分析， 也叫保本分析或盈虧平衡分析， 是通過分析生產(chǎn)成本、 銷售利潤(rùn)和產(chǎn)品數(shù)量這三者的關(guān)系， 掌握盈虧變化的規(guī)律， 指導(dǎo)出企業(yè)選擇能夠以最小的成本生產(chǎn)最 多產(chǎn)品并可使企業(yè)獲得最大利潤(rùn)的經(jīng)營(yíng)方案。15.1 學(xué)習(xí)目標(biāo)通過本項(xiàng)目的學(xué)習(xí)，可以學(xué)會(huì)使用 Excel 建立量本利分析模型的方法，學(xué)會(huì)建立盈虧平衡圖表的 方法，在表格制作的過程中學(xué)習(xí)并掌握以下Excel 功能的綜合運(yùn)用。創(chuàng)建 Excel 工作簿 在單元格中使用公式 SUM 函數(shù)的使用 建立量本利分析模型 使用規(guī)劃求解工具求解盈虧臨界點(diǎn) 制作量本利分析圖表 在圖表中添加盈虧臨界點(diǎn)垂直

2、參考線 圖表的修飾15.2 項(xiàng)目實(shí)施效果圖本項(xiàng)目完成后的效果圖如下：圖 15-1 盈虧臨界點(diǎn)銷量圖 15-2 量本利分析效果圖15.3 項(xiàng)目實(shí)施方案任務(wù) 1 ：理解量本利分析的概念量本利分析是產(chǎn)量 （或銷售量） 、成本、 利潤(rùn)之間依存關(guān)系分析的簡(jiǎn)稱， 也稱 VCP分析（ Volume -Cost -Profit Analysis）。它著重研究銷售數(shù)量、價(jià)格、成本和利潤(rùn)之間的數(shù)量關(guān)系。它所提供的原理、 方法在經(jīng)濟(jì)管理中有著廣泛的用途，同時(shí)它又是企業(yè)進(jìn)行決策、計(jì)劃和控制的重要工具。 在量本利分析中，確定盈虧臨界點(diǎn)是進(jìn)行量本利分析的關(guān)鍵。所謂盈虧臨界點(diǎn)，是指企業(yè)收入和 成本相等的經(jīng)營(yíng)狀態(tài)， 即邊際貢

3、獻(xiàn)等于固定成本時(shí)企業(yè)所處的既不盈利又不虧損的狀態(tài)， 通常用一定 的業(yè)務(wù)量來表示這種狀態(tài)。盈虧平衡臨界點(diǎn)越低，說明企業(yè)獲得贏利的可能性越大，出現(xiàn)虧損的可能 性越小，企業(yè)的抗風(fēng)險(xiǎn)能力也就越強(qiáng)。量本利分析所考慮的相關(guān)因素主要包括銷售量、單價(jià)、銷售收入、單位變動(dòng)成本、固定成本、營(yíng) 業(yè)利潤(rùn)等。任務(wù) 2 ：準(zhǔn)備量本利分析基礎(chǔ)數(shù)據(jù)某企業(yè)生產(chǎn)某種產(chǎn)品，該產(chǎn)品的市場(chǎng)銷售價(jià)格為 170 元。生產(chǎn)每一件產(chǎn)品的直接人工成本為 24 元，材料費(fèi) 11 元，其它制造費(fèi) 10元。管理人員工資 5.6萬元，資產(chǎn)折舊費(fèi) 3萬元， 固定銷售費(fèi)用 2.1 萬元。試建立模型，計(jì)算盈虧臨界點(diǎn)銷量。步驟 1：?jiǎn)?dòng) Excel 2010應(yīng)

4、用程序，創(chuàng)建空白工作簿。步驟 2：將空白工作簿另存為“量本利分析模型” 。步驟 3：將 Sheet1 工作表標(biāo)簽重命名為“量本利分析” ，并刪除 Sheet2、 Sheet3 空白工作表。 步驟 4：制作量本利分析表框架，如圖 15-3 所示。如圖 15-3 量本利分析表框架 表中，“邊際貢獻(xiàn)”是指銷售收入減去變動(dòng)成本后的余額，邊際貢獻(xiàn)又稱為“邊際利潤(rùn)”或“貢 獻(xiàn)毛益” 等 。單位邊際貢獻(xiàn)是指單件產(chǎn)品銷售收入減去單位變動(dòng)成本后的余額。上表中沒有數(shù)據(jù)的單元格為需要運(yùn)算的數(shù)據(jù)。步驟 5：計(jì)算“單位變動(dòng)成本” 。點(diǎn)擊 B2 單元格，選擇【公式】菜單中的【自動(dòng)求和】按鈕，在 參數(shù)輸入狀態(tài)下用鼠標(biāo)選擇

5、B3:B5 單元格區(qū)域，然后按回車。步驟 6：計(jì)算“固定成本” 。點(diǎn)擊 B8 單元格，用與步驟 2 相同的方法將 B9:B11 單元格區(qū)域求和。 步驟 7：計(jì)算“變動(dòng)成本” ，變動(dòng)成本 =銷量 *單位變動(dòng)成本。 點(diǎn)擊 B14 單元格，輸入公式“ =B13*B2” 并回車，步驟 8：計(jì)算“總成本” ，總成本 =固定成本 +變動(dòng)成本。點(diǎn)擊 B15 單元格，輸入公式“ =B8+B14” 并回車。步驟 9：計(jì)算“銷售收入” ，銷售收入 =銷量*銷售單價(jià)。點(diǎn)擊 B16 單元格，輸入公式“ =B13*B1”并回車。步驟 10：計(jì)算“邊際貢獻(xiàn)”，邊際貢獻(xiàn) =銷售收入 =變動(dòng)成本。點(diǎn)擊 B17 單元格，輸入公

6、式“=B16-B14” 并回車。步驟 11：計(jì)算“利潤(rùn)” ，利潤(rùn) =銷售收入 -總成本，點(diǎn)擊 B18 單元格，輸入公式“ =B16-B15”并回 車。表中“銷量”數(shù)據(jù) B13 單元格不用輸入數(shù)據(jù)，計(jì)算公式輸入完成后的量本利分析表如圖 15-4 所 示。圖 15-4 輸入計(jì)算參數(shù)任務(wù) 3：使用【規(guī)劃求解】工具確定盈虧臨界點(diǎn)銷量步驟 1：加載【規(guī)劃求解】 工具。由于 Excel 2010應(yīng)用程序安裝后默認(rèn)不加載 “規(guī)劃求解” 工具， 所以，如果要使用【規(guī)劃求解】功能時(shí)，需要手動(dòng)加載【規(guī)劃求解】工具，加載“規(guī)劃求解”工具的 方法如下。 點(diǎn)擊【文件】菜單下的【選項(xiàng)】命令，彈出【Excel選項(xiàng)】對(duì)話框，

7、如圖 15-5 所示。圖 15-5 【Excel 選項(xiàng)】對(duì)話框 在【 Excel選項(xiàng)】對(duì)話框窗口中選擇【加載項(xiàng)】按鈕，然后點(diǎn)擊【轉(zhuǎn)到】按鈕，彈出【加載宏】 對(duì)話框，如圖 15-6 所示。圖 15-6 【加載宏】對(duì)話框 在【加載宏】對(duì)話框窗口中，選中【規(guī)劃求解加載項(xiàng)】 ，如圖 15-7 所示。圖 15-7 選中【規(guī)劃求解加載項(xiàng)】 點(diǎn)擊【確定】 按鈕，系統(tǒng)會(huì)自動(dòng)安裝相關(guān)軟件， 軟件安裝完成后會(huì)在 【數(shù)據(jù)】 選項(xiàng)卡中出現(xiàn) 【規(guī) 劃求解】按鈕，如圖 15-8 所示。圖 15-8 加載【規(guī)劃求解】功能后的【數(shù)據(jù)】數(shù)據(jù)選項(xiàng)卡步驟 2：使用【規(guī)劃求解】工具求解出盈虧臨界點(diǎn)銷量值。點(diǎn)擊B18 單元格，選擇【數(shù)

8、據(jù)】選項(xiàng)卡，點(diǎn)擊【規(guī)劃求解】按鈕，彈出【規(guī)劃求解參數(shù)】對(duì)話框，如圖 15-9 所示。圖 15-9 【規(guī)劃求解參數(shù)】設(shè)置對(duì)話框，步驟 3：在對(duì)話框窗口中設(shè)置 【規(guī)劃求解參數(shù)】 ，選中【目標(biāo)值】 前的單選按鈕， 設(shè)置目標(biāo)值為 0， 點(diǎn)擊【通過更改可變單元格】下面的文本框，然后點(diǎn)擊 B13 單元格，其他選項(xiàng)保持默認(rèn)值不變，如圖 15-10 所示。圖 15-10 設(shè)置盈虧臨界點(diǎn)銷量時(shí)的【規(guī)劃求解參數(shù)】步驟 4：點(diǎn)擊【求解】按鈕，彈出【規(guī)劃求解結(jié)果】對(duì)話框，如圖15-11 所示。圖 5-11 【規(guī)劃求解結(jié)果】對(duì)話框步驟 5：點(diǎn)擊【確定】按鈕，求解結(jié)果如圖15-11 所示。圖 15-11 【規(guī)劃求解】結(jié)果

9、步驟 6：點(diǎn)擊 B18 單元格，選擇【開始】選項(xiàng)卡，在【數(shù)字格式】菜單中選擇【數(shù)字】選項(xiàng)。 完成盈虧臨界點(diǎn)銷量的最終效果圖見圖 15-1 所示。任務(wù) 4 ：制作量本利分析圖步驟 1：分別在 D1:G1 單元格區(qū)域的單元格中輸入字段名“銷量、邊際貢獻(xiàn)、固定成本、利潤(rùn)” ，并設(shè)置居中，如圖 15-12 所示。圖 15-12 輸入圖表字段名步驟 2：在 D2:D2 單元格區(qū)域輸入數(shù)量“ 0、 200、 400、 2000 ”。選擇 D2 單元格，輸入“ 0”， 選擇 D3 單元格，輸入“ 200”，同時(shí)選擇 D2、D3 兩個(gè)單元格，然后用鼠標(biāo)拖曳選中區(qū)域的填充句柄 向下填充，直至顯示 2000 時(shí)放

10、開鼠標(biāo)。步驟 3：計(jì)算“邊際貢獻(xiàn)” 。在 E2 單元格輸入公式“ =D2*$B$6”，可以直接輸入，或選擇 E2單元 格，輸入“ =”符號(hào)，然后用鼠標(biāo)點(diǎn)擊 D2 單元格，再輸入“ * ”符號(hào)，再用鼠標(biāo)點(diǎn)擊 B6 單元格，按 F4把 B6轉(zhuǎn)換為絕對(duì)地址，向下填充。步驟 4：輸入“固定成本” 。選擇 F2 單元格，輸入“ 107000”，或輸入單元格引用公式“ =$B$8”， 然后向下填充。步驟 5：計(jì)算“利潤(rùn)” 。選擇 G2 單元格，輸入公式“ =D2*$B$1-D2*$B$2-$B$8 ”并向下填充，公 式的含義是“利潤(rùn) =數(shù)量 *銷售單價(jià) -數(shù)量 *單件產(chǎn)品人工成本 -固定成本”。完成輸入的

11、量本利數(shù)據(jù)如圖 15-13 所示。圖 15-13 量本利數(shù)據(jù)步驟 6：選擇 D1:G12 單元格區(qū)域 .步驟 7：選擇【插入】選項(xiàng)卡，點(diǎn)擊【散點(diǎn)圖】按鈕，彈出圖表類型選擇菜單，如圖15-14 所示。圖 15-14 選擇【散點(diǎn)圖】類型步驟 8：在菜單中選擇【帶平滑線的散點(diǎn)圖】命令，彈出量本利分析圖，如圖15-15 所示。圖 15-15 量本利分析圖步驟 9：用鼠標(biāo)右擊圖例，在彈出的菜單中選擇【設(shè)置圖例格式】命令，彈出【設(shè)置圖例格式】 對(duì)話框，如圖 15-16 所示。圖 15-16 【設(shè)置圖例格式】對(duì)話框步驟 10：在【設(shè)置圖例格式】對(duì)話框中選擇【圖例位置】為【底部】，然后點(diǎn)擊【關(guān)閉】按鈕，設(shè)置效

12、果如圖 15-17 所示。圖 15-17 設(shè)置【圖例位置】在底部步驟 11：在圖表中添加“盈虧臨界點(diǎn)垂直參考線” 。 準(zhǔn)備垂直參考線數(shù)據(jù)。選擇 A22 單元格，輸入“盈虧臨界點(diǎn)垂直參考線” 。在 A23:A26 單元格區(qū)域中輸入 X 軸坐標(biāo)標(biāo)注點(diǎn)“ 856”，“856 ”為盈虧臨界點(diǎn)銷量。在 B23:B26 單元格區(qū)域中輸入 Y 軸坐標(biāo)標(biāo)點(diǎn) “ 200000（任意的）、107000（盈虧臨界點(diǎn)邊際貢獻(xiàn)） 、 0（與 X 軸的交叉點(diǎn)）、-100000（任意的）”，如圖 15-18 所示。圖 15-18 垂直參考線引用數(shù)據(jù) 選中繪圖區(qū)，選擇【圖表工具】下的【設(shè)計(jì)】選項(xiàng)卡，點(diǎn)擊【選擇數(shù)據(jù)】按鈕，彈出

13、【選擇數(shù) 據(jù)源】對(duì)話框，如圖 15-19 所示。圖 15-19 添加圖例項(xiàng) 在彈出的【選擇數(shù)據(jù)源】對(duì)話框中點(diǎn)擊【圖例項(xiàng)（系列 ）】下的【添加】按鈕，彈出【編輯數(shù)據(jù)系列】對(duì)話框，如圖 15-20 所示。圖 15-20 編輯數(shù)據(jù)系列 設(shè)置垂直參考線參數(shù)。點(diǎn)擊【系列名稱】下的文本框，然后點(diǎn)擊 A22 單元格；點(diǎn)擊【 X軸系列 值】下面的文本框，然后選擇 A23:A26 單元格區(qū)域；點(diǎn)擊【 Y 軸系列值】下面的文本框，刪除其中的 內(nèi)容，然后選擇 B23:B26單元格區(qū)域。設(shè)置結(jié)果如圖 15-21 所示。圖 15-21 垂直參考線參數(shù)設(shè)置結(jié)果 點(diǎn)擊【確定】按鈕，返回到【選擇數(shù)據(jù)源】對(duì)話框窗口，這時(shí)【圖例

14、項(xiàng)】中多了一項(xiàng)【盈虧臨 界點(diǎn)垂直參考線】 ，如圖 15-22 所示。圖 15-22 添加垂直參考線后的數(shù)據(jù)源 點(diǎn)擊【確定】按鈕，圖形中插入了一條垂直參考線，如圖 15-23 所示。圖 15-23 插入垂直參考線后的量本利分析圖步驟 12：調(diào)整垂直參考線線型。 在垂直參考線的任意一個(gè)標(biāo)記點(diǎn)位置點(diǎn)擊左鍵，選中垂直參考線，如圖 15-24 所示。圖 15-24 選擇垂直參考線 在選中的垂直參考線標(biāo)注點(diǎn)上點(diǎn)擊右鍵，在彈出的菜單中選擇【設(shè)置數(shù)據(jù)系列格式】命令，彈 出【設(shè)置數(shù)據(jù)系列格式】對(duì)話框，如圖 15-25 所示。圖 15-25 設(shè)置線型【寬度】 在對(duì)話框中選擇【線型】選項(xiàng)，把線型【線寬】調(diào)為“1 磅”，然后點(diǎn)擊【關(guān)閉】按鈕，步驟 13：結(jié)垂直參考線添加數(shù)據(jù)