盈虧平衡分析模型一、模型描述1、基本模式Excel電子表格建立盈虧平衡分析模型的方法，可采用公式計算、單變量求解、規劃求解等尋找盈虧平衡點的多種方法，分析各種管理參數的變化對盈虧平衡點的影響。盈虧平衡分析問題描述：銷售量Q，銷售收益R，總成本C以及利潤之間的關系的模型：銷售收益R=銷售量Q*銷售單價p總成本C=固定成本+變動成本V變動成本V=單位變動成本v*銷售量Q總成本C=固定成本+單位變動成本v*銷售量Q利潤=銷售收益R總成本C單位邊際貢獻=銷售單價p 單位變動成本v邊際貢獻=銷售收益R 變動成本V邊際貢獻率k=單位邊際貢獻/銷售單價2、盈虧平衡銷量Q0和盈虧平衡銷售收益R0二、EXCEL中建立盈虧平衡分析模型的步驟（1）在Excel中建立盈虧平衡分析的框架，輸入產品的單價、單位變動成本、固定成本（2）給定銷售量的情況下，計算總成本、銷售收益、利潤等。（3）可繪制利潤隨著銷售量改變的XY散點圖形（利用模型運算表）（4）計算盈虧平衡點：可以使用A：單變量求解 B：規劃求解 C：公式計算（5）可繪制參數（例如單價）對盈虧平衡點的影響（6）根據預期的利潤確定實現該利潤的產品銷量三、案例分析富勒公司制造一種高質量運動鞋，公司管理層邀請你幫助公司整理用于管理決策的信息，公司最高生產能力為1500。一項銷售調查顯示明年的平均每雙銷售價格定為90元；公司的成本數據為：固定成本為37800元，每雙可變成本為36元。若當前的銷量為900，要求：1、 計算單位邊際貢獻及邊際貢獻率；2、 計算銷售收益、總成本及利潤；3、 盈虧平衡（保本點）銷量及盈虧平衡銷售收益；4、 假若公司預算利潤為24000元，計算為達到利潤目標所需要的銷量及銷售收益；5、根據公司的銷售收益、總成本、利潤等數據，繪制本-量-利圖形；通過圖形動態反映出銷量從100按增量10變化到1500時利潤的情況及“盈利”、“虧損”、“保本”的決策信息。6、假定銷售單價從80元按增量變化到100元時，計算出盈虧平衡銷量和盈虧平衡銷售收益的相應變化值？并且以圖形方式動態反映。操作步驟：（1）在Excel中建立模型，計算單位邊際貢獻、單位邊際貢獻率、銷售收益、總成本、利潤、盈虧平衡銷量、盈虧平衡銷售收益各個單位格公式如下：C9：=C5C6 C10：=C9/C5 C11：=C2*C5C12：=C6*C2+C7 C13：=C11C12 C15：=C7/C9C16：=C15*C5（2）（3）繪制公司的銷售收益、總成本、利潤等數據，繪制本量利。A：利用模擬運算表準備作圖數據。以銷售量為變量，銷售收益、總成本、利潤進行單變量模擬。公式如下：G3：=B11 H3：=B12 I3：=B13模擬運算參數：選F3：I5B：選擇F2：I2 F5：I6，繪制XY散點圖，注意：數據系列選擇列。調整圖形為C：構造垂直參考線的數據增加銷售量垂直線參考：公式：F8：=B15 F9：=B15 G8：-40000 G8：140000選擇F8：G9，選擇“編輯”的“選擇性粘貼”增加銷售量垂直參考線：公式：F12：=B2 F13：=B2 G12：-40000 G13：140000選擇F12：G13，選擇圖形，選擇“編輯”的“選擇性粘貼”D：在表格的A19和A20建立公式，反映建模型的結果：A19：=銷售量為: &B2&IF(B130,贏利,IF(B13=0,平衡,虧損)A20：=售價=&B5&元，盈虧平衡銷量=&ROUND