12.1+模型-本量利.xls

固定成本F￥270,000销量成本收入利润单位可变成本 v￥1.550270,0000-270,000单位售价p￥3.80#50,000347,500190,000-157,500100,000425,000380,000-45,000150,000502,500570,00067,500Q=F/(p-v)200,000580,000760,000180,000盈亏平衡销量=120000件250,000657,500950,000292,500300,000735,000 1,140,000405,000350,000812,500 1,330,000517,500400,000890,000 1,520,000630,000450,000967,500 1,710,000742,500500,0001,045,000 1,900,000855,000盈亏平衡线12000016000001200004560001200000120000-400000盈盈亏亏平平衡衡图图5000004000003000002000001000000-5000000500000100000015000002000000销量成本收入利润盈亏平衡线￥3.80单位售价=盈亏平衡销量=120000分析单价变动对盈亏平衡销量的影响盈盈亏亏平平衡衡图图5000004000003000002000001000000-5000000500000100000015000002000000销量成本收入利润盈亏平衡线固定成本F￥270,000销量成本收入利润亏损额单位可变成本v￥1.30#100000400000250000-150000单位售价p￥2.50#0270,0000-270,000270,00050,000335,000125,000-210,000210,000100,000400,000250,000-150,000150,000150,000465,000375,000-90,00090,000预计销量Q100,000#200,000530,000500,000-30,00030,000销售收入R￥250,000250,000595,000625,00030,0000总成本C￥400,000300,000660,000750,00090,0000利润P(￥150,000)350,000725,000875,000150,0000400,000790,0001,000,000210,0000盈亏平衡点Qe225,000450,000855,0001,125,000270,0000500,000920,0001,250,000330,0000Qe=F/(p-v)盈亏平衡销量=225000件盈亏平衡线2250001600000亏损额=1500002250005625002250000225000-400000比较线100,000-200000100,000250000100,000400000100,0001600000￥2.50 单位售价=当前销量=100,000 亏损额=150000单价=￥100,000 当前销量=￥1.30 单位可变成本=盈利额000125,0000250,0000375,0000500,00030,000595,00090,000660,000150,000725,000210,000790,000270,000855,000330,000920,000亏损区盈利区可可调调盈盈亏亏平平衡衡图图(面面积积折折线线图图）0100,000200,000300,000400,000500,0000200,000400,000600,000800,0001,000,0001,200,0001,400,0001,600,000销量金额亏损区盈利区成本收入盈亏平衡销量=225000件分析单位售价、可变成本对利润额的影响￥2.50单位售价=￥1.30 单位可变成本=250000400000可可调调盈盈亏亏平平衡衡图图500,000400,000300,000200,000100,0000-200,0000200,000400,000600,000800,0001,000,0001,200,0001,400,0001,600,000销量金额成本收入利润盈亏平衡线比较线当前销量=100,000 亏损额=150000单价=￥盈亏平衡销量=225000件分析销量、单价对利润额的影响￥1.30 单位可变成本=固定成本F￥270,000固定成本￥270,000单位可变成本v￥1.55#单位可变成本￥1.55单位售价p￥3.00#单位售价￥2.70预计销量Q300,000#盈亏平衡销量234,783总成本C￥735,000单位成本￥3销售收入R￥900,000单位收入￥3利润P￥165,000单位利润(￥0.00)￥2.70234,783固定成本F￥270,000单位售价 盈亏平衡销量单位可变成本v￥1.55#￥2.70234,783单位售价p￥2.70#2.0600000单位边际贡献p-v￥1.152.14909092.2415385盈亏平衡销量Qe234,7832.3360000盈亏平衡销售额Se633,9132.43176472.52842112.62571432.72347832.82160002.92000003.0186207使使用用规规划划求求解解，设设定定利利润润=0(或或单单位位利利润润=0)，找找到到盈盈亏亏平平衡衡的的销销量量。234,783盈盈亏亏平平衡衡销销量量&单单位位售售价价3.02.82.62.42.22.00100000200000300000400000500000600000700000单位售价盈亏平衡销量￥2.70 单位售价=固定成本F￥270,000预计销量 单位成本单位收入单位可变成本 v￥1.70#100,0004.402.70单位售价p￥2.70#50,0007.102.70预计销量Q100,000#100,0004.402.70150,0003.502.70单位成本uc4.4200,0003.052.70单位收入ur2.7250,0002.782.70单位利润p-1.7300,0002.602.70350,0002.472.70400,0002.382.70450,0002.302.70500,0002.242.70550,0002.192.70600,0002.152.70比较线10000001000004.41000002.710000010盈亏线27000002700002.7270000104.40 2.70 单单位位成成本本收收入入分分析析图图700,000600,000500,000400,000300,000200,000100,00000.001.002.003.004.005.006.007.008.009.0010.00销量单位成本收入单位成本单位收入比较线盈亏线单位售可变成本=￥1.70 100,000 当前销量=￥2.70 当前售价=固定成本#单位可变成本￥1.55in D8:=$E$3+$E$4*C8in C9:=E9/$E$5单位售价￥2.70#in E9:=D8in D9:=$E$3+$E$4*C9QCR0270,0000100,000425,000270,000157,407513,981425,000190,364565,063513,981209,283594,388565,063220,144611,223594,388226,379620,887611,223229,958626,435620,887232,013629,620626,435233,193631,449629,620233,870632,498631,449234,259633,101632,498234,482633,447633,101234,610633,645633,447234,683633,759633,645234,726633,825633,759234,750633,862633,825234,764633,884633,862234,772633,896633,884234,776633,903633,896￥2.70 单位售价=确确定定QeQe方方法法图图600,000500,000400,000300,000200,000100,00000200,000400,000600,000800,0001,000,0001,200,000销量CRCR250,000200,000150,000100,00050,00000100,000200,000300,000400,000500,000600,000700,000CRQCR500,000 1,045,000387,037869,907 1,045,000322,188769,391869,907284,960711,688769,391263,588678,561711,688251,319659,545678,561244,276648,627659,545240,232642,360648,627￥2.70 单位售价=确确定定QeQe方方法法图图600,000500,000400,000300,000200,000100,00000200,000400,000600,000800,0001,000,0001,200,000销量CRCRQ1Q2Q3Q4Q0RCQ1Q2Q3Q4Q0RC