例5-4 斯普公司棉籽生產(chǎn)盈虧平衡分析模型-柱形圖與折線圖組合

1、【例5-4】斯普公司每年處理棉籽的生產(chǎn)能力最大為20000噸，這一過程生產(chǎn)幾種可以銷售的商品，包括油、粗粉、棉莢及棉布。一項銷售調(diào)查顯示公司明年的銷售價格可以定為每噸200元；已知公司的成本如下：固定成本（含處理時固定成本和管理費用）640000元/年，單元可變成本（含棉籽成本、處理時可變成本、銷售時可變成本）80元/噸。要求：1、計算單位邊際貢獻；邊際貢獻率；邊際貢獻；2、計算銷售收入、總成本、利潤；3、計算盈虧平衡銷量、盈虧平衡銷售收入；4、繪制銷售收入、總成本、利潤的圖表；其中：銷售收入、總成本采用折線圖，當(dāng)前利潤采用堆積柱形圖且用大紅色柱子表示。5、添加銷量的微調(diào)項，從1000到200

2、00增量1000變化。銷銷量量銷銷售售收收入入總總成成本本銷銷量量160003200000.001920000.0000640000銷銷售售單單價價200.001000200000720000固固定定成成本本640000.002000400000800000單單位位可可變變成成本本80.003000600000880000單單位位邊邊際際貢貢獻獻120.004000800000960000邊邊際際貢貢獻獻1920000.00500010000001040000邊邊際際貢貢獻獻率率60.00%600012000001120000銷銷售售收收入入3200000.007000140000012000

3、00總總成成本本1920000.00800016000001280000利利潤潤1280000.009000180000013600001000020000001440000盈盈虧虧平平衡衡銷銷量量5333.331100022000001520000盈盈虧虧平平衡衡銷銷售售收收入入1066666.67120002400000160000013000260000016800001400028000001760000結(jié)結(jié)論論：1500030000001840000銷銷量量=16000=16000時時，盈盈利利, ,利利潤潤=1280000=12800001600032000001920000170

4、0034000002000000180003600000208000019000380000021600002000040000002240000min(銷銷售售收收入入,總總成成本本)利利潤潤銷銷量量利利潤潤當(dāng)當(dāng)前前利利潤潤1280000.00000-6400000001000-5200000002000-4000000003000-2800000004000-1600000005000-4000000060008000000070002000000008000320000000900044000000010000560000000110006800000001200080000000013

5、00092000000014000104000000015000116000001920000128000016000128000012800000017000140000000018000152000000019000164000000020000176000000 01 10 00 00 02 20 00 00 03 30 00 00 04 40 00 00 05 50 00 00 06 60 00 00 07 70 00 00 08 80 00 00 09 90 00 00 01 10 00 00 00 01 11 10 00 00 01 12 20 00 00 01 13 30 00

6、00 01 14 40 00 00 01 15 50 00 00 01 16 60 00 00 01 17 70 00 00 01 18 80 00 00 01 19 90 00 00 02 20 00 00 00 00 05 50 00 00 00 00 01 10 00 00 00 00 00 01 15 50 00 00 00 00 02 20 00 00 00 00 00 02 25 50 00 00 00 00 03 30 00 00 00 00 00 03 35 50 00 00 00 00 04 40 00 00 00 00 00 04 45 50 00 00 00 00 05

7、50 00 00 00 00 00 0利利潤潤銷銷售售收收入入總總成成本本銷銷量量=16000時時，盈盈利利,利利潤潤=12800000 01 10 00 00 02 20 00 00 03 30 00 00 04 40 00 00 05 50 00 00 06 60 00 00 07 70 00 00 08 80 00 00 09 90 00 00 01 10 00 00 00 01 11 10 00 00 01 12 20 00 00 01 13 30 00 00 01 14 40 00 00 01 15 50 00 00 01 16 60 00 00 01 17 70 00 00 01

8、18 80 00 00 01 19 90 00 00 02 20 00 00 00 00 05 50 00 00 00 00 01 10 00 00 00 00 00 01 15 50 00 00 00 00 02 20 00 00 00 00 00 02 25 50 00 00 00 00 03 30 00 00 00 00 00 03 35 50 00 00 00 00 04 40 00 00 00 00 00 04 45 50 00 00 00 00 05 50 00 00 00 00 00 0利利潤潤銷銷售售收收入入總總成成本本銷銷量量=16000時時，盈盈利利,利利潤潤=128000001000200030004000500060007000800090001000011000120001300014000150001600017000180001900020000-1000000-5000000500000100000015000002000000利潤銷銷量量=16000時時，盈盈利利,利利潤潤=12800000100020003000400050006000700080