投资盈亏平衡分析模型

1、变量净现值120201202项目寿命5产品单价4022单位变动成本2725付现固定成本2600002600利率12%12所得税税率33%33期末残值4826004826初始投资300000030000计算年销量883净流量￥759,597.72项目净现值要达到12020，年销量应在883.38以上20000150001000050000 879.50 880.50 881.50 882.50 883.50 884.50 885.50 886.50 净现值年销量净现值年销量净流量6010881.46757,930.497212881.85758,263.948414882.23758,597.3

2、89616882.61758,930.8310818883.00759,264.2812020883.38759,597.7213222883.76759,931.1714424884.15760,264.6215626884.53760,598.0616828884.92760,931.5118030885.30761,264.9620000150001000050000 879.50 880.50 881.50 882.50 883.50 884.50 885.50 886.50 净现值年销量投投资资盈盈亏亏平平衡衡分分析析模模型型应应用用场场景景在投资净现值预测模型中，根据项目寿命、销量

3、、单价、单位变动成本、固定成本、所得税率、利率、期末残值、初始投资等变量，我们可计算出投资的净现值。类似于本量利分析模型的盈亏平衡分析，反过来，我们也希望知道，为了达到既定的净现值目标，各变量应该达到或控制在什么水平。以零为净现值目标，就是投资净现值盈亏的临界点，实际只是平衡分析的特例而已。为了进行投资盈亏平衡分析，我们需要首先根据净现值反算净流量，然后再根据净流量反算销量、单价、单位变动成本、付现固定成本等变量。根据净现值反算净流量，计算过程比较复杂。好在EXCEL将复杂的计算过程编写成了函数（PMT），可供我们直接调用。根据净流量反算销量、单价、单位变动成本、付现固定成本等变量，计算过程也

4、比较复杂。好在EXCEL将复杂的计算过程编写成了函数（SLN），可供我们直接调用。现在，我们假定已经通过PMT函数计算出了每期净流量，SLN函数计算出了每期折旧额。我们在“销量平衡分析”工作表，进行销量平衡分析，计算其他因素已知时，对销量应采取的措施，以取得目标净现值。销量=(每期净流量-每期折旧额*所得税税率)/(1-所得税税率)+付现固定成本)/(单价-单位变动成本)我们在“单价平衡分析”工作表，进行单价平衡分析，计算其他因素已知时，对单价应采取的措施，以取得目标净现值。单价=(每期净流量-每期折旧额*所得税税率)/(1-所得税税率)+付现固定成本)/年销量+单位变动成本我们在“单位变动成

5、本平衡分析”工作表，进行单位变动成本平衡分析，计算其他因素已知时，对单位变动成本应采取的措施，以取得目标净现值。单位变动成本=单价-(每期净流量-每期折旧额*所得税税率)/(1-所得税税率)+付现固定成本)/年销量我们在“付现固定成本平衡分析”工作表，进行付现固定成本平衡分析，计算其他因素已知时，对付现固定成本应采取的措施，以取得目标净现值。付现固定成本=销量*(单价-单价变动成本)-( 每期净流量-每期折旧额*所得税税率)/(1-所得税税率)模模型型建建立立输输入入1、新建工作簿。包含以下工作表：销量平衡分析、单价平衡分析、单位变动成本平衡分析、付现固定成本平衡分析。2、“销量平衡分析”工作

6、表1）在工作表录入数据，如图所示：2）添加滚动条。点“视图-工具栏-窗体”，选择“滚动条”，录入单元格链接、当前值、最小值、最大值等。例如，D2单元格的滚动条设置如下3）单元格公式C2：=F2*10C6：=F6*100C7：=F7/100C8：=F8/100C9：=F9*100C10：=F10*1004）合并单元格A16：E17区域合并5）格式化。如调整列宽、选择填充色、设置字体、字号等。3、“单价平衡分析”工作表1）在工作表录入数据，如图所示：2）添加滚动条。略3）单元格公式略4）合并单元格略5）格式化。略4、“单位变动成本平衡分析”工作表1）在工作表录入数据，如图所示：2）添加滚动条。略3

7、）单元格公式略4）合并单元格略5）格式化。略5、“付现固定成本平衡分析”工作表1）在工作表录入数据，如图所示：2）添加滚动条。略3）单元格公式略4）合并单元格略5）格式化。略加加工工1、“销量平衡分析”工作表在工作表单元格中录入公式：G13：=-PMT(C7，C3，C2+C10，-C9)C13：=(G13-SLN(C10，C9，C3)*C8)/(1-C8)+C6)/(C4-C5)A16：=项目净现值要达到&(ROUND(C2，2)&，年销量应在&(ROUND(C13，2)&以上2、“单价平衡分析”工作表在工作表单元格中录入公式：G13：=-PMT(C7，C3，C

8、2+C10，-C9)C13：=(G13-SLN(C10，C9，C3)*C8)/(1-C8)+C6)/C4+C5A16：=项目净现值要达到&(ROUND(C2，2)&，产品单价应在&(ROUND(C13，2)&以上3、“单位变动成本平衡分析”工作表在工作表单元格中录入公式：G13：=-PMT(C7，C3，C2+C10，-C9)C13：=C5-(G13-SLN(C10，C9，C3)*C8)/(1-C8)+C6)/C4A16：=项目净现值要达到&(ROUND(C2，2)&，单位变动成本应在&(ROUND(C13，2)&以下4、“付现固定

9、成本平衡分析”工作表在工作表单元格中录入公式：G13：=-PMT(C7，C3，C2+C10，-C9)C13：=C4*(C5-C6)-(G13-SLN(C10，C9，C3)*C8)/(1-C8)A16：=项目净现值要达到&(ROUND(C2，2)&，付现固定成本应在&(ROUND(C13，2)&以下关于PMT函数：功能：基于固定利率及等额分期付款方式，返回贷款的每期付款额。语法：PMT(rate，nper，pv，fv，type)Rate：贷款利率。Nper：该项贷款的付款期数。Pv：现值，或一系列未来付款的当前值的累积和，也称为本金。Fv：为未来值，或在最后一次付

10、款后希望得到的现金余额。Type：数字 0 或 1，用以指定各期的付款时间是在期初还是期末。关于SLN函数功能：返回某项资产在一个期间中的线性折旧值。语法：SLN(cost，salvage，life)Cost：为资产原值。Salvage：为资产在折旧期末的价值（也称为资产残值）。Life：为折旧期限（有时也称作资产的使用寿命）。输输出出1、“销量平衡分析”工作表此时，工作表如图所示：拖动滚动条，为达到目标净现值，年销量计算结果将发生变化，文字描述也随之变化。2、“单价平衡分析”工作表此时，工作表如图所示：拖动滚动条，为达到目标净现值，产品单价计算结果将发生变化，文字描述也随之变化。3、“单位变

11、动成本平衡分析”工作表此时，工作表如图所示：拖动滚动条，为达到目标净现值，单位变动成本计算结果将发生变化，文字描述也随之变化。4、“付现固定成本平衡分析”工作表此时，工作表如图所示：拖动滚动条，为达到目标净现值，付现固定成本计算结果将发生变化，文字描述也随之变化。表表格格制制作作输输入入1、“销量平衡分析”工作表在工作表录入数据，如图所示：2、“单价平衡分析”工作表在工作表录入数据，如图所示：3、“单位变动成本平衡分析”工作表在工作表录入数据，如图所示：4、“付现固定成本平衡分析”工作表在工作表录入数据，如图所示：加加工工1、“销量平衡分析”工作表在单元格录入公式：I2：=0.5*$C$2I3

12、：=0.6*$C$2I11：=1.4*$C$2I12：=1.5*$C$2K2：=-PMT($C$7，$C$3，I2+$C$10，-$C$9选择K2单元格，向下填充至K12J2：=(K2-SLN($C$10，$C$9，$C$3)*$C$8)/(1-$C$8)+$C$6)/($C$4-$C$5)选择J2单元格，向下填充至J122、“单价平衡分析”工作表在单元格录入公式：I2：=0.5*$C$2I3：=0.6*$C$2I11：=1.4*$C$2I12：=1.5*$C$2K2：=-PMT($C$7，$C$3，I2+$C$10，-$C$9)选择K2单元格，向下填充至K12J2：=(K2-SLN($C$1

13、0，$C$9，$C$3)*$C$8)/(1-$C$8)+$C$6)/$C$4+$C$5选择J2单元格，向下填充至J123、“单位变动成本平衡分析”工作表在单元格录入公式：I2：=0.5*$C$2I3：=0.6*$C$2I11：=1.4*$C$2I12：=1.5*$C$2K2：=-PMT($C$7，$C$3，I2+$C$10，-$C$9)选择K2单元格，向下填充至K12J2：=$C$5-(K2-SLN($C$10，$C$9，$C$3)*$C$8)/(1-$C$8)+$C$6)/$C$4选择J2单元格，向下填充至J124、“付现固定成本平衡分析”工作表在单元格录入公式：I2：=0.5*$C$2I3

14、：=0.6*$C$2I11：=1.4*$C$2I12：=1.5*$C$2K2：=-PMT($C$7，$C$3，I2+$C$10，-$C$9)选择K2单元格，向下填充至K12J2：=$C$4*($C$5-$C$6)-(K2-SLN($C$10，$C$9，$C$3)*$C$8)/(1-$C$8)选择J2单元格，向下填充至J12输输出出1、“销量平衡分析”工作表此时，工作表如图所示：拖动滚动条，表格各项数据将发生变化。2、“单价平衡分析”工作表此时，工作表如图所示：拖动滚动条，表格各项数据将发生变化。3、“单位变动成本平衡分析”工作表此时，工作表如图所示：拖动滚动条，表格各项数据将发生变化。4、“付

15、现固定成本平衡分析”工作表此时，工作表如图所示：拖动滚动条，表格各项数据将发生变化。图图形形生生成成1、“销量平衡分析”工作表第第一一步步选择I2：J12区域，点击“插入-图表”， 如下所示：，选择“标准类型-XY散点图-无数据点平滑线散点图”第第二二步步点“下一步”，如下所示：选择“系列”，此时已有系列1。点“添加”，添加如下系列：系列2：X值：=销量平衡分析!$C$2Y值：=销量平衡分析!$C$13系列3：X值：=(销量平衡分析!$C$2，销量平衡分析!$C$2)Y值：=(销量平衡分析!$J$2，销量平衡分析!$J$12)第第三三步步点“下一步”，如下所示：选择“标题”，输入数值X轴：净现

16、值数值Y轴：年销量第第四四步步点“下一步”， 如下所示：选择“作为其中的对象插入”，第第五五步步点“完成”。用户可按自己的意愿修改图表。例如：修改数据系列的格式：在图表中选择某一数据系列，右键选择“数据系列格式”，如下所示：第第六六步步销量平衡分析模型的最终图形，如下所示：拖动滚动条，表格数据将发生变化，图形也随之变化。2、“单价平衡分析”工作表第第一一步步选择I2：J12区域，点击“插入-图表”， 如下所示：，选择“标准类型-XY散点图-无数据点平滑线散点图”第第二二步步点“下一步”，如下所示：选择“系列”，此时已有系列1。点“添加”，添加如下系列：系列2：X值：=单价平衡分析!$C$2Y值

17、：=单价平衡分析!$C$13系列3：X值：=(单价平衡分析!$C$2，单价平衡分析!$C$2)Y值：=(单价平衡分析!$J$2，单价平衡分析!$J$12)第第三三步步点“下一步”，如下所示：选择“标题”，输入数值X轴：净现值数值Y轴：产品单价第第四四步步点“下一步”， 如下所示：选择“作为其中的对象插入”，第第五五步步点“完成”。用户可按自己的意愿修改图表。例如：修改数据系列的格式：在图表中选择某一数据系列，右键选择“数据系列格式”，进行图形格式的设置。第第六六步步单价平衡分析模型的最终图形，如下所示：拖动滚动条，表格数据将发生变化，图形也随之变化。3、“单位变动成本平衡分析”工作表第第一一步

18、步选择I2：J12区域，点击“插入-图表”， 如下所示：，选择“标准类型-XY散点图-无数据点平滑线散点图”第第二二步步点“下一步”，如下所示：选择“系列”，此时已有系列1。点“添加”，添加如下系列：系列2：X值：=单位变动成本平衡分析!$C$2Y值：=单位变动成本平衡分析!$C$13系列3：X值：=(单位变动成本平衡分析!$C$2，单位变动成本平衡分析!$C$2)Y值：=(单位变动成本平衡分析!$J$2，单位变动成本平衡分析!$J$12)第第三三步步点“下一步”，如下所示：选择“标题”，输入数值X轴：净现值数值Y轴：单位变动成本第第四四步步点“下一步”， 如下所示：选择“作为其中的对象插入”

19、，第第五五步步点“完成”。用户可按自己的意愿修改图表。例如：修改数据系列的格式：在图表中选择某一数据系列，右键选择“数据系列格式”，进行图形格式的设置。第第六六步步单位变动成本平衡分析模型的最终图形，如下所示：拖动滚动条，表格数据将发生变化，图形也随之变化。4、“付现固定成本平衡分析”工作表第第一一步步选择I2：J12区域，点击“插入-图表”， 如下所示：，选择“标准类型-XY散点图-无数据点平滑线散点图”第第二二步步点“下一步”，如下所示：选择“系列”，此时已有系列1。点“添加”，添加如下系列：系列2：X值：=付现固定成本平衡分析!$C$2Y值：=付现固定成本平衡分析!$C$13系列3：X值

20、：=(付现固定成本平衡分析!$C$2，付现固定成本平衡分析!$C$2)Y值：=(付现固定成本平衡分析!$J$2，付现固定成本平衡分析!$J$12)第第三三步步点“下一步”，如下所示：选择“标题”，输入数值X轴：净现值数值Y轴：固定成本第第四四步步点“下一步”， 如下所示：选择“作为其中的对象插入”，第第五五步步点“完成”。用户可按自己的意愿修改图表。例如：修改数据系列的格式：在图表中选择某一数据系列，右键选择“数据系列格式”，进行图形格式的设置。第第六六步步付现固定成本平衡分析模型的最终图形，如下所示：拖动滚动条，表格数据将发生变化，图形也随之变化。在投资净现值预测模型中，根据项目寿命、销量、

21、单价、单位变动成本、固定成本、所得税率、利率、期末残值、初始投资等变量，我们可计算出投资的净现值。类似于本量利分析模型的盈亏平衡分析，反过来，我们也希望知道，为了达到既定的净现值目标，各变量应该达到或控制在什么水平。以零为净现值目标，就是投资净现值盈亏的临界点，实际只是平衡分析的特例而已。为了进行投资盈亏平衡分析，我们需要首先根据净现值反算净流量，然后再根据净流量反算销量、单价、单位变动成本、付现固定成本等变量。根据净现值反算净流量，计算过程比较复杂。好在EXCEL将复杂的计算过程编写成了函数（PMT），可供我们直接调用。根据净流量反算销量、单价、单位变动成本、付现固定成本等变量，计算过程也比

22、较复杂。好在EXCEL将复杂的计算过程编写成了函数（SLN），可供我们直接调用。我们在“销量平衡分析”工作表，进行销量平衡分析，计算其他因素已知时，对销量应采取的措施，以取得目标净现值。销量=(每期净流量-每期折旧额*所得税税率)/(1-所得税税率)+付现固定成本)/(单价-单位变动成本)我们在“单价平衡分析”工作表，进行单价平衡分析，计算其他因素已知时，对单价应采取的措施，以取得目标净现值。单价=(每期净流量-每期折旧额*所得税税率)/(1-所得税税率)+付现固定成本)/年销量+单位变动成本我们在“单位变动成本平衡分析”工作表，进行单位变动成本平衡分析，计算其他因素已知时，对单位变动成本应采

23、取的措施，以取得目标净现值。单位变动成本=单价-(每期净流量-每期折旧额*所得税税率)/(1-所得税税率)+付现固定成本)/年销量我们在“付现固定成本平衡分析”工作表，进行付现固定成本平衡分析，计算其他因素已知时，对付现固定成本应采取的措施，以取得目标净现值。付现固定成本=销量*(单价-单价变动成本)-( 每期净流量-每期折旧额*所得税税率)/(1-所得税税率)包含以下工作表：销量平衡分析、单价平衡分析、单位变动成本平衡分析、付现固定成本平衡分析。点“视图-工具栏-窗体”，选择“滚动条”，录入单元格链接、当前值、最小值、最大值等。A16：=项目净现值要达到&(ROUND(C2，2)&a

24、mp;，单位变动成本应在&(ROUND(C13，2)&以下A16：=项目净现值要达到&(ROUND(C2，2)&，付现固定成本应在&(ROUND(C13，2)&以下拖动滚动条，为达到目标净现值，单位变动成本计算结果将发生变化，文字描述也随之变化。拖动滚动条，为达到目标净现值，付现固定成本计算结果将发生变化，文字描述也随之变化。用户可按自己的意愿修改图表。例如：修改数据系列的格式：在图表中选择某一数据系列，右键选择“数据系列格式”，如下所示：用户可按自己的意愿修改图表。例如：修改数据系列的格式：在图表中选择某一数据系列，右键选择“数据系列格式”，进

25、行图形格式的设置。用户可按自己的意愿修改图表。例如：修改数据系列的格式：在图表中选择某一数据系列，右键选择“数据系列格式”，进行图形格式的设置。用户可按自己的意愿修改图表。例如：修改数据系列的格式：在图表中选择某一数据系列，右键选择“数据系列格式”，进行图形格式的设置。在投资净现值预测模型中，根据项目寿命、销量、单价、单位变动成本、固定成本、所得税率、利率、期末残值、初始投资等变量，我们可计算出投资的净现值。类似于本量利分析模型的盈亏平衡分析，反过来，我们也希望知道，为了达到既定的净现值目标，各变量应该达到或控制在什么水平。以零为净现值目标，就是投资净现值盈亏的临界点，实际只是平衡分析的特例而

26、已。在投资净现值预测模型中，根据项目寿命、销量、单价、单位变动成本、固定成本、所得税率、利率、期末残值、初始投资等变量，我们可计算出投资的净现值。类似于本量利分析模型的盈亏平衡分析，反过来，我们也希望知道，为了达到既定的净现值目标，各变量应该达到或控制在什么水平。以零为净现值目标，就是投资净现值盈亏的临界点，实际只是平衡分析的特例而已。在投资净现值预测模型中，根据项目寿命、销量、单价、单位变动成本、固定成本、所得税率、利率、期末残值、初始投资等变量，我们可计算出投资的净现值。类似于本量利分析模型的盈亏平衡分析，反过来，我们也希望知道，为了达到既定的净现值目标，各变量应该达到或控制在什么水平。以

27、零为净现值目标，就是投资净现值盈亏的临界点，实际只是平衡分析的特例而已。变量净现值净现值192000019200960000项目寿命111152000年销量65631344000单位变动成本2281536000付现固定成本21000021001728000利率13%131920000所得税税率41%412112000期末残值72060072062304000初始投资30000003000024960002688000计算2880000产品单价453净流量832107项目净现值要达到1920000，产品单价应在452.95以上40000003000000200000010000000 - 100

28、.00 200.00 300.00 400.00 500.00 600.00 净现值产品单价单价净流量409.36663,298.81418.07697,060.37426.79730,821.93435.51764,583.49444.23798,345.04452.95832,106.60461.67865,868.16470.39899,629.72479.11933,391.28487.83967,152.84496.551,000,914.4040000003000000200000010000000 - 100.00 200.00 300.00 400.00 500.00 600.

29、00 净现值产品单价变量净现值单位变动成本净现值10800001080054000091.94项目寿命864800084.42年销量656175600076.90产品单价42186400069.39付现固定成本760000760097200061.87利率24%24108000054.35所得税税率36%36118800046.83期末残值6000006000129600039.31初始投资300000030000140400031.80151200024.28计算162000016.76单位变动成本54净流量1161180项目净现值要达到1080000，单位变动成本应在54.35以下200000015000001000000