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

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