建立企業(yè)盈虧平衡分析模型(量本利分析模型)-EXCEL動態(tài)圖表-折線散點圖.xls

銷售單價100銷售量邊際貢獻固定成本利潤 單位可變成本 350052000 52000 直接人工101500975005200045500 直接材料15 可變制造費10 盈虧點銷量垂直參考線 單位邊際貢獻65800150000 固定成本 5200080052000 管理人員工資200008000 資產(chǎn)折舊10000800 50000 固定銷售費用10000 當前銷量垂直參考線 銷量10001000150000 邊際貢獻65000100065000 銷售收入100000100052000 總成本87000100013000 利潤130001000 50000 售價 100元 盈虧平衡銷量 800 盈虧平衡銷量800 銷量 1000時 盈利 EXCELEXCEL夢夢想想之之家家 轉(zhuǎn)轉(zhuǎn)載載請請保保留留出出處處 52000 13000 盈盈 虧虧 平平 衡衡 分分 析析 1400120010008006004002000 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 邊際貢獻固定成本利潤 Series4Series5 售價 100元 盈虧平衡銷量 800銷量 1000時 盈利 52000 13000 盈盈 虧虧 平平 衡衡 分分 析析 1400120010008006004002000 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 邊際貢獻固定成本利潤 Series4Series5 售價 100元 盈虧平衡銷量 800銷量 1000時 盈利 EXCELEXCEL夢夢想想之之家家 轉(zhuǎn)轉(zhuǎn)載載請請保保留留出出處處 假設(shè)有一個制衣廠 生產(chǎn)的褲子單位售價100元 其中生產(chǎn)一件產(chǎn)品的人工費10元 材料費15元 制造過程中所耗費的水電費10元 當然可能會有其他隨產(chǎn)量變動的制造費用 此處暫且不計 可做表如下 其中 單位可變成本 即為 直接人工 直接材料 可變制造費 相加之和 上表B2 B3 B4 B5 請自己加公式 B7解釋一下 B7等于銷售單介B1減單位可變成本B2 這個值在會計上專用名詞叫單元邊際貢獻 意思說賣出去一件產(chǎn)品對利潤所做的貢 獻 注意 單位 兩個字 是銷售一件產(chǎn)品貢獻 在實際企業(yè)生產(chǎn)過程中 還有部分費用是不隨產(chǎn)量變動而變動的 我們稱之為固定費用 象機器設(shè)備的折舊 管理層人員的工資 銷售過程中的廣告費等等 它們是不隨銷量 變動 意思是說賣不賣東西 這部分費用依然產(chǎn)生 我們暫且估計數(shù)據(jù)如下 本故事純屬虛構(gòu) 如有雷同 純屬巧合 1400 上表B8 B9 B10 B11 假設(shè)銷量1000條 繼續(xù)做表 上表 B14 B13 B7 B15 B13 B1 B16 B8 B13 B2 解釋一下B19盈虧平衡銷量 當企業(yè)有銷量時 單位邊際貢獻隨之增加 只有所有售出產(chǎn)品的邊際貢獻總值超過企業(yè)的固定成本時 企業(yè)才會實現(xiàn)利潤 在利潤為零那一時刻 盈虧實現(xiàn)平衡 邊際貢獻總值等于固定成本 因為邊際貢獻總值 單位邊際貢獻乘 X 銷量 所以盈虧平衡時的銷量 固定成本 單位邊際貢獻 即B19 B8 B7 以下內(nèi)容為EXCEL盈虧損模型所需要的建的輔助區(qū)域 至于為什么要建這個區(qū)域 等做好之后您再慢慢體會 繼續(xù)做表 其中 C1到F1標題手工填入 C2銷量輸入0 C2為0 D2肯定也為0 E2輸入公式 B8 F2輸入公式 D2 E2 C3輸入1500 假設(shè)這是企業(yè)最大銷量 閑少 那您填多大我都不攔著 D3輸入公式 C3 B7 E3輸入公式 B8 F3輸入公式 D3 E3 再加兩條參考線 其中 C7到C10的公式都是 B19 D8公式 B8 C13到C17的公式都是 B13 D14公式 B14 D15公式 B8 D16公式 B17 其他位置的數(shù)字直接手工輸入 至于為什么輸入這些數(shù)字 下邊解釋 至于為什么要做參考線也下邊解釋 現(xiàn)在只需要明白一點 拿 盈虧點銷量垂直參考線 來說 如果把C7到C10的值放圖表上的X軸 D7到D10為Y軸的話 因為X值相同 這些實際上是垂直于X軸方向上的一條直線上的四個點 當前銷量垂直參考線 中的數(shù)據(jù)就是垂直于X軸的直線上的五個點 明白這些就足夠了 下面開始做圖表 1 選擇C1到F3 然后按工具欄上的 圖表向?qū)?選擇XY散點圖 子圖表類型選擇 無數(shù)據(jù)點折線散點圖 然后按一步 2 系列產(chǎn)生在處選擇 列 然后按完成 3 完成之后 如下圖所示 藍色文字指出圖表上各部分名字 下文會用到這些地方 別找不到 4 簡單修飾一下 a 選中圖例 鼠標右鍵 圖例格式 位置 底部 確定 b 選中繪圖區(qū) 鼠標右鍵 繪圖區(qū)格式 區(qū)域 處選擇 白色 確定 c 選中繪圖區(qū) 再選中 y軸主格線 鼠標右鍵 網(wǎng)格線格式 顏色處 選擇 淺灰色 確定 d 選中 圖表上的 黃色 利潤 系列線型 鼠標右鍵 數(shù)據(jù)系列格式 左邊 線型 下的顏色選擇 黃色 粗細處選擇倒數(shù)第二粗的線粗 同理按上述方法 將 邊際貢獻 系列改成藍色 加粗線 將 固定成本 系列改成桔紅色 加粗線型 e 選中x坐標軸 鼠標右鍵 坐標軸格式 刻度 最小值輸入0 最大值輸入1500 確定 同樣方法 將y軸刻度最小值設(shè)成 50000 最大值設(shè)成1500000 f 再分別選擇中 圖例 x軸 y軸 在它們鼠標右鍵的格式設(shè)定中的字體選項里邊 將它們的字體全設(shè)成11號字體 請看下圖是完成后樣子 如果您的不是這樣 請按上邊步驟重新改過 60000 40000 20000 0 20000 40000 60000 80000 100000 120000 0500100015002000 邊際貢獻 固定成本 利潤 圖表區(qū) 繪圖區(qū) 圖例 y x x軸軸 y y軸軸主主格格線線 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 邊際貢獻固定成本利潤 5 選中繪圖區(qū) 鼠標右鍵 數(shù)據(jù)源 在 系列 頁上 按 添加 按鈕 出現(xiàn) 系列4 如下圖 6 在x值處 去選擇表格上的C7到C10這個區(qū)域 在y值處 先刪除里邊內(nèi)容中 再去選擇表格上的D7到D10這個區(qū)域 確定之后圖表應(yīng)該是下同這個樣子 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 邊際貢獻固定成本利潤系列4 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 邊際貢獻固定成本利潤 7 按上述方法再添加一個系列 系列5 x值選擇C13到C17 y值處先刪掉里邊內(nèi)容 再選擇 D13到D17 8 完成后 如下圖所示 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 邊際貢獻固定成本利潤 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 邊際貢獻固定成本利潤系列4 9 在繪圖區(qū)選中 系列4 鼠標右鍵 數(shù)據(jù)系列格式 左側(cè) 線形 處顏色 選紫色 粗細選倒數(shù)第二粗 右側(cè) 數(shù)據(jù)標記 處 前景色選紅色 背景色選白色 大小設(shè)成 5磅 確定 10 在繪圖區(qū)選中 系列5 鼠標右鍵 數(shù)據(jù)系列格式 左側(cè) 線形 處顏色 選天藍色 粗細選倒數(shù)第二粗 右側(cè) 數(shù)據(jù)標記 處 前景色選紅色 背景色選白色 大小設(shè)成 5磅 確定 11 完成效果如下圖 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 邊際貢獻固定成本利潤 12 分別清除系列4和系列5的最上邊和最上邊的數(shù)據(jù)點格式 操作如下 a 先鼠標左鍵選中系列4 再鼠標左鍵單擊系列4最上邊的數(shù)據(jù)點 選中這個數(shù)據(jù)點 切記不是雙擊那個點 是兩次單擊才可選中 選中之后 按鼠標右鍵會出現(xiàn) 數(shù)據(jù)點格式 b 在右側(cè)的 數(shù)據(jù)標記 處選 無 確定 重復以上操作 將系列4最下邊數(shù)據(jù)點 以及系列5最上和最下數(shù)據(jù)點格式去除掉 完成后效果如下 13 關(guān)鍵數(shù)據(jù)點加 數(shù)據(jù)標志 選中系列4 再選中系列4與固定費用和邊際貢獻交叉的那個點 鼠標右鍵 數(shù)據(jù)點格式 數(shù)據(jù)標志 選中Y值 確定 同樣操作 選中系列5 再選中系列5與利潤交叉的那個點 加數(shù)據(jù)標志 Y值 確定后 效果如下圖所示 啥 跟我的不一樣 字體大 沒邊框 您自己在這個數(shù)據(jù)標志的右鍵格式設(shè)定里改改嘛 到此為一個靜態(tài)的盈虧模型就建好了 雖然叫靜態(tài)的 但如果您去修改表格上的數(shù)值 如價格 銷量 固定成本 這個模型就隨之變化了 有沒有更好的辦法 不直接去修改表值 能不能在圖表這里直接變動價格 變動銷量來實現(xiàn)動態(tài)的盈虧分析呢 答案是能的 下面我們就在此圖的基礎(chǔ)之上 來做一個動態(tài)圖表 這里我們需要用到 窗體 工具欄上的微調(diào)按鈕 如圖 如果你的工具欄上沒有窗體工具欄 請去視圖菜單下的工具欄里鉤選窗體工具欄 14 鼠標單擊窗體工具欄上的微調(diào)按鈕 然后在表格上拖拽出這個按鈕 然后在其上按鼠標右鍵 選擇 設(shè)置控件格式 當前值輸入100 最小值輸入50 最大值300 步長5 單元格鏈接處 選擇B1 確定 我的目的是用這個按鈕控制售價變化 15 再次鼠標單擊窗體工具欄上的微調(diào)按鈕 然后在表格上拖拽出第二個按鈕 然后在其上按鼠標右鍵 選擇 設(shè)置控件格式 當前值輸入1000 最小值輸入0 最大值1500 步長50 單元格鏈接處 選擇B13 確定 我的目的是用這個按鈕控制銷量變化 完成之后 當單擊這兩個按鈕的向上或向下鍵頭的時候 你會發(fā)現(xiàn)圖表是動態(tài)的了 注 如果你的按鈕上沒有 控制 這個選項 請你注意一下我用的窗體工具欄上的微調(diào)按鈕 不是控制工具欄的 這兩個有區(qū)別的 16 給這兩個按鈕添加一些文字說明 a 在單元格C18中輸入公式 售價 B1 元 盈虧平衡銷量 ROUND B19 1 b 在單元格C19中輸入公式 銷量 B13 時 IF B17 0 盈利 IF B17 0 保本 虧損 c 在繪圖工具欄上 選擇矩形框 在圖表上邊拉出一個條形矩形框 然后選中這個矩形框 上邊公式欄中輸入 C18 回車 完成之后矩形框上就會出現(xiàn)C18里的文字 如圖所示 注 繪圖工具欄默認在EXCEL窗口的下邊 如果你找不到請去視圖菜單下的工具欄里將其鉤選 c 再建一個矩形框 選中這個矩形框 上邊公式欄中輸入 C19 回車 完成之后矩形框上就會出現(xiàn)C19里的文字 如圖所示 17 最后一步 給圖表區(qū)右鍵的表選項里給表加上標題 點編輯菜單下的 定位 定位條件 選 對象 確定 在選中圖形上按鼠標右鍵 組合 組合 使EXCEL圖表 兩個按鈕 兩個矩形框組合到一起 看一下成果吧 分析 由以上圖形可以看出 1 當銷量增加 產(chǎn)品的邊際貢獻逐漸增加 利潤的負值 虧損 逐漸減少 2 當邊際貢獻數(shù)值增長到與固定費用持平時 這時利潤剛好為零 此時即所謂的盈虧平衡 3 當邊際貢獻再繼續(xù)增加時 這時企業(yè)已開始盈利了 假設(shè)有一個制衣廠 生產(chǎn)的褲子單位售價100元 其中生產(chǎn)一件產(chǎn)品的人工費10元 材料費15元 制造過程中所耗費的水電費10元 當然可能會有其他隨產(chǎn)量變動的制造費用 此處暫且不計 上表B2 B3 B4 B5 請自己加公式 B7解釋一下 B7等于銷售單介B1減單位可變成本B2 這個值在會計上專用名詞叫單元邊際貢獻 意思說賣出去一件產(chǎn)品對利潤所做的貢 獻 注意 單位 兩個字 是銷售一件產(chǎn)品貢獻 在實際企業(yè)生產(chǎn)過程中 還有部分費用是不隨產(chǎn)量變動而變動的 我們稱之為固定費用 象機器設(shè)備的折舊 管理層人員的工資 銷售過程中的廣告費等等 它們是不隨銷量 變動 意思是說賣不賣東西 這部分費用依然產(chǎn)生 當企業(yè)有銷量時 單位邊際貢獻隨之增加 只有所有售出產(chǎn)品的邊際貢獻總值超過企業(yè)的固定成本時 企業(yè)才會實現(xiàn)利潤 在利潤為零那一時刻 盈虧實現(xiàn)平衡 邊際貢獻總值等于固定成本 因為邊際貢獻總值 單位邊際貢獻乘 X 銷量 所以盈虧平衡時的銷量 固定成本 單位邊際貢獻 即B19 B8 B7 以下內(nèi)容為EXCEL盈虧損模型所需要的建的輔助區(qū)域 至于為什么要建這個區(qū)域 等做好之后您再慢慢體會 繼續(xù)做表 其中 C1到F1標題手工填入 C2銷量輸入0 C2為0 D2肯定也為0 E2輸入公式 B8 F2輸入公式 D2 E2 C3輸入1500 假設(shè)這是企業(yè)最大銷量 閑少 那您填多大我都不攔著 D3輸入公式 C3 B7 E3輸入公式 B8 F3輸入公式 D3 E3 其中 C7到C10的公式都是 B19 D8公式 B8 C13到C17的公式都是 B13 D14公式 B14 D15公式 B8 D16公式 B17 其他位置的數(shù)字直接手工輸入 至于為什么輸入這些數(shù)字 下邊解釋 至于為什么要做參考線也下邊解釋 現(xiàn)在只需要明白一點 拿 盈虧點銷量垂直參考線 來說 如果把C7到C10的值放圖表上的X軸 D7到D10為Y軸的話 因為X值相同 這些實際上是垂直于X軸方向上的一條直線上的四個點 當前銷量垂直參考線 中的數(shù)據(jù)就是垂直于X軸的直線上的五個點 明白這些就足夠了 下面開始做圖表 1 選擇C1到F3 然后按工具欄上的 圖表向?qū)?選擇XY散點圖 子圖表類型選擇 無數(shù)據(jù)點折線散點圖 然后按一步 c 選中繪圖區(qū) 再選中 y軸主格線 鼠標右鍵 網(wǎng)格線格式 顏色處 選擇 淺灰色 確定 d 選中 圖表上的 黃色 利潤 系列線型 鼠標右鍵 數(shù)據(jù)系列格式 左邊 線型 下的顏色選擇 黃色 粗細處選擇倒數(shù)第二粗的線粗 同理按上述方法 將 邊際貢獻 系列改成藍色 加粗線 將 固定成本 系列改成桔紅色 加粗線型 e 選中x坐標軸 鼠標右鍵 坐標軸格式 刻度 最小值輸入0 最大值輸入1500 確定 f 再分別選擇中 圖例 x軸 y軸 在它們鼠標右鍵的格式設(shè)定中的字體選項里邊 將它們的字體全設(shè)成11號字體 圖表區(qū) 繪圖區(qū) 圖例 y x x軸軸 y y軸軸主主格格線線 5 選中繪圖區(qū) 鼠標右鍵 數(shù)據(jù)源 在 系列 頁上 按 添加 按鈕 出現(xiàn) 系列4 如下圖 6 在x值處 去選擇表格上的C7到C10這個區(qū)域 在y值處 先刪除里邊內(nèi)容中 再去選擇表格上的D7到D10這個區(qū)域 確定之后圖表應(yīng)該是下同這個樣子 7 按上述方法再添加一個系列 系列5 x值選擇C13到C17 y值處先刪掉里邊內(nèi)容 再選擇 D13到D17 9 在繪圖區(qū)選中 系列4 鼠標右鍵 數(shù)據(jù)系列格式 左側(cè) 線形 處顏色 選紫色 粗細選倒數(shù)第二粗 右側(cè) 數(shù)據(jù)標記 處 前景色選紅色 背景色選白色 大小設(shè)成 5磅 確定 10 在繪圖區(qū)選中 系列5 鼠標右鍵 數(shù)據(jù)系列格式 左側(cè) 線形 處顏色 選天藍色 粗細選倒數(shù)第二粗 右側(cè) 數(shù)據(jù)標記 處 前景色選紅色 背景色選白色 大小設(shè)成 5磅 確定 a 先鼠標左鍵選中系列4 再鼠標左鍵單擊系列4最上邊的數(shù)據(jù)點 選中這個數(shù)據(jù)點 切記不是雙擊那個點 是兩次單擊才可選中 選中之后 按鼠標右鍵會出現(xiàn) 數(shù)據(jù)點格式 重復以上操作 將系列4最下邊數(shù)據(jù)點 以及系列5最上和最下數(shù)據(jù)點格式去除掉 完成后效果如下 13 關(guān)鍵數(shù)據(jù)點加 數(shù)據(jù)標志 選中系列4 再選中系列4與固定費用和邊際貢獻交叉的那個點 鼠標右鍵 數(shù)據(jù)點格式 數(shù)據(jù)標志 選中Y值 確定 同樣操作 選中系列5 再選中系列5與利潤交叉的