EXCEL盈虧平衡分析模型

1、盈虧平衡分析模型一、模型描述1 基本模式Excel 電子表格建立盈虧平衡分析模型的方法，可采用公式計(jì)算、單變量求解、規(guī)劃求 解等尋找盈虧平衡點(diǎn)的多種方法，分析各種管理參數(shù)的變化對(duì)盈虧平衡點(diǎn)的影響。盈虧平衡分析問(wèn)題描述：銷售量Q,銷售收益 R,總成本 C 以及利潤(rùn)沢之間的關(guān)系的模型：銷售收益 R=銷售量 Q*銷售單價(jià) p總成本。=固定成本+變動(dòng)成本 V變動(dòng)成本 V=單位變動(dòng)成本 v*銷售量 Q總成本。=固定成本+單位變動(dòng)成本 v*銷售量 Q利潤(rùn)JI=銷售收益 R-總成本 C單位邊際貢獻(xiàn)=銷售單價(jià) p -單位變動(dòng)成本 v邊際貢獻(xiàn)=銷售收益 R -變動(dòng)成本 V邊際貢獻(xiàn)率 k=單位邊際貢獻(xiàn)/銷售單價(jià)R

2、 = pQC = F vOv 7T = U、RF二kRF7T - R-Cp2、盈虧平衡銷量 Q0 和盈虧平衡銷售收益 R0FPy二、 EXCEL 中建立盈虧平衡分析模型的步驟(1 )在 Excel 中建立盈虧平衡分析的框架，輸入產(chǎn)品的單價(jià)、單位變動(dòng)成本、固定成本(2) 給定銷售量的情況下，計(jì)算總成本、銷售收益、利潤(rùn)等。(3)可繪制利潤(rùn)隨著銷售量改變的XY 散點(diǎn)圖形(利用模型運(yùn)算表)(4)計(jì)算盈虧平衡點(diǎn)：可以使用A :單變量求解B :規(guī)劃求解C:公式計(jì)算(5) 可繪制參數(shù)(例如單價(jià))對(duì)盈虧平衡點(diǎn)的影響(6 )根據(jù)預(yù)期的利潤(rùn)確定實(shí)現(xiàn)該利潤(rùn)的產(chǎn)品銷量三、 案例分析富勒公司制造一種高質(zhì)量運(yùn)動(dòng)鞋，公司管

3、理層邀請(qǐng)你幫助公司整理用于管理決策的信息，公司最高生產(chǎn)能力為 1500。一項(xiàng)銷售調(diào)查顯示明年的平均每雙銷售價(jià)格定為90 元；公司的成本數(shù)據(jù)為：固定成本為 37800 元，每雙可變成本為 36 元。若當(dāng)前的銷量為 900,要 求：1、 計(jì)算單位邊際貢獻(xiàn)及邊際貢獻(xiàn)率；2、 計(jì)算銷售收益、總成本及利潤(rùn)；3、 盈虧平衡(保本點(diǎn))銷量及盈虧平衡銷售收益；4、 假若公司預(yù)算利潤(rùn)為 24000 元，計(jì)算為達(dá)到利潤(rùn)目標(biāo)所需要的銷量及銷售收益；5、 根據(jù)公司的銷售收益、總成本、利潤(rùn)等數(shù)據(jù)，繪制本-量-利圖形；通過(guò)圖形動(dòng)態(tài)反映 出銷量從 100 按增量 10 變化到 1500 時(shí)利潤(rùn)的情況及“盈利”、“虧損”、“

4、保本”的決策信息。6、假定銷售單價(jià)從 80 元按增量變化到 100 元時(shí)，計(jì)算出盈虧平衡銷量和盈虧平衡銷售 收益的相應(yīng)變化值？并且以圖形方式動(dòng)態(tài)反映。操作步驟：（1 ）在 Excel 中建立模型，計(jì)算單位邊際貢獻(xiàn)、單位邊際貢獻(xiàn)率、銷售收益、總成本、利潤(rùn)、盈虧平衡銷量、盈虧平衡銷售收益BC1D12銷量_90031_ !4單位：元5平均每雙售價(jià)906每雙可變成本367固定成本3780089單位邊際貢獻(xiàn)5410邊際貢獻(xiàn)率60%11銷售收益8100012總成本7020013利潤(rùn)108001415盈虧 2 衡銷量70016盈虧平衡收益6300017各個(gè)單位格公式如下:C9: =C5 C6C10： =C9

5、/C5C11 ： =C2*C5C12: =C6*C2+C7C13 : =C11 C12C15 : =C7/C9C16: =C15*C5（2）利用單變量求解方法計(jì)算目標(biāo)利潤(rùn)對(duì)應(yīng)的目標(biāo)銷售量和目標(biāo)銷售收益（3）繪制公司的銷售收益、總成本、利潤(rùn)等數(shù)據(jù)，繪制本一量一利。A :禾 U 用模擬運(yùn)算表準(zhǔn)備作圖數(shù)據(jù)。以銷售量為變量，銷售收益、總成本、禾 U 潤(rùn)進(jìn)行單 變量模擬。公式如下：G3: =B11H3: =B1213: =B13模擬運(yùn)算參數(shù)：選 F3: 15模擬運(yùn)算丟區(qū)I輸入引用行的單元格:I5J輸入引用列的單元格：m2vII取消ABcD EFGHI12銷量900銷量銷售收益總成本利澗381000702

6、00108004單札元0037800-378005平均每雙善價(jià)90150013500091800432006每雙可變成本3&7固定咸本378009單位迦樂(lè)貢獻(xiàn)5410費(fèi)際貢齡60%11 銷售收益810QQ12 總?cè)?020013 和涓10800【1415F衡梢7001G盈竽 任衡收益63000B :選擇 F2: 12F5: 16，繪制 XY 散點(diǎn)圖，注意：數(shù)據(jù)系列選擇列。調(diào)整圖形為銷售收益總成本禾I潤(rùn)C(jī):構(gòu)造垂直參考線的數(shù)據(jù)增加銷售量垂直線參考：公式：F8: =B15F9: =B15 G8: -40000 G8: 140000選擇 F8: G9，選擇“編輯”的“選擇性粘貼”選擇性黏貼區(qū)

7、添加單元格為一1數(shù)值軸在一_金新系列廠行1確定1新徽據(jù)點(diǎn)辺a 列Q取清廠首行為樂(lè)列名稱通1*胃劌衙分類了直!S B i i i m-iI-Mlai ha i i 7 *= BaiM1 1MJ Im lai an i wr 替換現(xiàn)有分類辺kBCDEPGHIi2銷量110銷量網(wǎng)售收益總成本利涓3990041760-31S60q單位:元0037800-37E005平均每雙售價(jià)901.50013500091800432006 每雙可變成本367 固定成本3780 0盈虧平衡銷量垂直線參考S700-400009單位邊際貢獻(xiàn)5470014000010 邊際貢獻(xiàn)牽60%11 銷莒收益9900銷星乘百參者線1

8、2 總成本4176011.0-4000013-318601101400001415 盈虧平働帝量70016 盈丐平衡收益6300017ISIS銷售量為：110 萬(wàn)折20 售價(jià)二沁元， 盈虧平慟銷量二 W021增加銷售量垂直參考線：公式：F12: =B2F13: =B2G12: -40000G13: 140000選擇 F12: G13，選擇圖形，選擇“編輯”的“選擇性粘貼”D :在表格的 A19 和 A20 建立公式，反映建模型的結(jié)果：A19 :=銷售量為:&B2&IF(B130,贏利,IF(B13=0,平衡,虧損)A20 :=售價(jià)=&B5&元，盈虧平衡銷量 =&ROUND(B15,0)E:在圖表中創(chuàng)建微調(diào)器控件，反映銷售量和單價(jià)與利潤(rùn)之間的關(guān)系。設(shè)蓋控井格式? rx大小1燥護(hù)1