1.【案例7-2】的操作步骤
（1）创建工作簿。
创建一个工作簿，在盈亏临界点计算表中输入相关数据，如图7-23所示。
图7-23 盈亏临界点计算表
（2）填写目标函数。
本案例运用单变量求解工具，计算在基本数据下，当销售量、单价、单位变动成本与固定成本中三个参数不变，目标函数即利润为0时另一因素的值。
“目标函数”栏为利润值，盈亏临界点即为利润值为0时的状态。此时，D10=D11=D12=D13=0。
（3）计算盈亏临界点销售量。
在单元格D10中输入公式“=C10*（C5-C6）-C7”，执行“单变量求解”命令，在系统弹出的“单变量求解”对话框中，将“目标单元格”设置为“$D$10”，将“目标值”设置为“0”，将“可变单元格”设置为“$C$10”，如图7-24所示，然后单击“确定”按钮，则会出现如图7-25所示的“单变量求解状态”对话框，单击“确定”按钮，即可得到盈亏临界点销售量为6 000件。
图7-24 单变量求解的参数设置
图7-25 “单变量求解状态”对话框
（4）计算盈亏临界点单价。
在单元格D11中输入公式“=C4*（C11-C6）-C7”，执行“单变量求解”命令，在系统弹出的“单变量求解”对话框中，将“目标单元格”设置为“$D$11”，将“目标值”设置为“0”，将“可变单元格”设置为“$C$11”，如图7-26所示，然后单击“确定”按钮，则会出现“单变量求解状态”对话框，单击“确定”按钮，即可得到盈亏临界点单价为111.428 571 4元/件（可进行四舍五入保留有效数字）。
图7-26 “单变量求解”对话框
（5）计算盈亏临界点单位变动成本。
在单元格D12中输入公式“=C4*（C5-C12）-C7”，执行“单变量求解”命令，在系统弹出的“单变量求解”对话框中，将“目标单元格”设置为“$D$12”，将“目标值”设置为“0”，将“可变单元格”设置为“$C$12”，然后单击“确定”按钮，则会出现“单变量求解状态”对话框，单击“确定”按钮，即可得到盈亏临界点单位变动成本为68.571 428 57元/件。
（6）计算盈亏临界点固定成本。
在单元格D13中输入公式“=C4*（C5-C6）-C13”，执行“单变量求解”命令，在系统弹出的“单变量求解”对话框中，将“目标单元格”设置为“$D$13”，将“目标值”设置为“0”，将“可变单元格”设置为“$C$13”，然后单击“确定”按钮，则会出现“单变量求解状态”对话框，单击“确定”按钮，即可得到盈亏临界点固定成本为420 000元。最终结果如图7-27所示。
图7-27 盈亏临界点计算结果
2.【案例7-3】的操作步骤
（1）创建工作簿。
创建一个工作簿，在综合盈亏临界点计算表中输入相关数据，如图7-28所示。
图7-28 综合盈亏临界点计算表
（2）计算三种产品的销售收入。
甲产品：在单元格B10中输入公式“=B4*B5”，计算结果为110 000元。
乙产品：在单元格C10中输入公式“=C4*C5”，计算结果为154 000元。
丙产品：在单元格D10中输入公式“=D4*D5”，计算结果为210 000元。
合计：在单元格E10中输入公式“=B10+C10+D10”，计算结果为474 000元。
（3）计算各产品销售收入的比重。
甲产品：在单元格B11中输入公式“=B10/$E$10”，计算结果为23.21%。
乙产品：在单元格C11中输入公式“=C10/$E$10”，计算结果为32.49%。
丙产品：在单元格D11中输入公式“=D10/$E$10”，计算结果为44.30%。
（4）计算三种产品的变动成本。
甲产品：在单元格B12中输入公式“=B4*B6”，计算结果为75 000元。
乙产品：在单元格C12中输入公式“=C4*C6”，计算结果为110 000元。
丙产品：在单元格D12中输入公式“=D4*D6”，计算结果为150 000元。
合计：在单元格E12中输入公式“=B12+C12+D12”，计算结果为335 000元。
（5）计算三种产品的边际贡献。
甲产品：在单元格B13中输入公式“=B10-B12”，计算结果为35 000元。
乙产品：在单元格C13中输入公式“=C10-C12”，计算结果为44 000元。
丙产品：在单元格D13中输入公式“=D10-D12”，计算结果为60 000元。
合计：在单元格E13中输入公式“=B13+C13+D13”，计算结果为139 000元。
（6）计算三种产品的边际贡献率。
甲产品：在单元格B14中输入公式“=B13/B10”，计算结果为31.82%。
乙产品：在单元格C14中输入公式“=C13/C10”，计算结果为28.57%。
丙产品：在单元格D14中输入公式“=D13/D10”，计算结果为28.57%。
（7）计算三种产品的综合边际贡献率。
合并单元格B15、C15、D15、E15，在合并单元格中输入公式“=E13/E10”，计算结果为29.32%。
（8）计算综合盈亏平衡点销售额。
合并单元格B16、C16、D16、E16，在合并单元格中输入公式“=B7/B15”，计算结果为2 046 043.17元。
（9）计算盈亏平衡点各产品的销售额。
甲产品：在单元格B17中输入公式“=$B$16*B11”，计算结果为474 820.14元。
乙产品：在单元格C17中输入公式“=$B$16*C11”，计算结果为664 748.2元。
丙产品：在单元格D17中输入公式“=$B$16*D11”，计算结果为906 474.82元。
（10）计算盈亏平衡点各产品的销售量。
甲产品：在单元格B18中输入公式“=B17/B5”，计算结果为21 582.73件。(www.xing528.com)
乙产品：在单元格C18中输入公式“=C17/C5”，计算结果为23 741.01件。
丙产品：在单元格D18中输入公式“=D17/D5”，计算结果为25 899.28件。
最终计算结果如图7-29所示。
图7-29 综合盈亏临界点计算结果
3.【案例7-4】的操作步骤
（1）创建工作簿。
根据题意创建一个工作簿，在计算表中输入基本数据，如图7-30所示。
图7-30 基本数据
（2）建立各因素预测值变动表。
建立各因素变动情况单元格区域，如图7-31所示。
图7-31 各因素预测值变动表
1）添加滚动条控制按钮。
调用“滚动条（窗体控件）”命令，分别在单元格区域E3：F3，E4：F4，E5：F5，E6：F6，E7：F7，添加“滚动条（窗体控件）”按钮。在单元格区域E3：F3，右键单击“滚动条（窗体控件）”按钮，在出现的快捷菜单中执行“设置控件格式”命令，出现“设置控件格式”对话框，在“当前值”输入50，“最小值”输入0，“最大值”输入100，“步长”输入1，“页步长”输入5，“单元格链接”框输入E3，如图7-32所示，单击“确定”按钮。单元格区域E4：F4，E5：F5，E6：F6，E7：F7中滚动条控制按钮的设置如此类推。
图7-32 “设置控件格式”对话框
2）建立单元格D3与E3、D4与E4、D5与E5、D6与E6的联系。
在单元格D3中输入公式“=E3/100-50%”，并将单元格D3设置为百分比格式。
在单元格D4中输入公式“=E4/100-50%”，并将单元格D4设置为百分比格式。
在单元格D5中输入公式“=E5/100-50%”，并将单元格D5设置为百分比格式。
在单元格D6中输入公式“=E6/100-50%”，并将单元格D6设置为百分比格式。
此时，就建立了变动百分比与滚动条控制按钮之间的联系，且变化范围为[-50%，50%]。当单击该滚动条两端箭头，“因素变动率”单元格中数值以增量或减量1%变化；在滚动条上任意单击，因素变动率以“页步长”5%增减变化；当滚动条的滑块处于滚动条中间位置时，因素变动率为0。
3）建立单元格C3与B3、D3的联系，单元格C4与B4、D4的联系，单元格C5与B5、D5的联系，单元格C6与B6、D6的联系。
在单元格C3中输入公式“=B3*（1+D3）”；在单元格C4中输入公式“=B4*（1+D4）”；在单元格C5中输入公式“=B5*（1+D5）”；在单元格C6中输入公式“=B6*（1+D6）”，运行结果为各因素变化后的数值。
或者由于单元格区域D3：D6的公式原理相同，因此可建立数组公式。具体操作为：选中单元区域D3：D6，并输入数组公式“=E3：E6/100-50%”，即可得到D3：D6单元区域各因素变动率的值。选中E3：E6单元区域，并输入数组公式“=E3：E6*（1+D3：D6）”，即可得到E3：E6单元区域各因素变动后的值。计算结果如图7-33所示。
图7-33 各因素预测值变动计算结果
（3）建立初始预测值的盈亏临界点销售量、预计利润。
在单元格B8中输入公式“=B6/（B4-B5）”，可计算得到初始预测值的盈亏临界点销售量为1 143件。
在单元格F8中输入公式“=B3*（B4-B5）-B6”，可计算得到初始预测值的预计利润为170 000元。计算结果如图7-34所示。
图7-34 初始预测值的盈亏临界点销售量、预计利润计算结果
（4）建立单因素敏感性分析表，如图7-35所示。
选中单元格区域B12：B15，输入数组公式“=D3：D6”，计算结果即为因素变动率数据。
在单元格C12中输入公式“=B6/（B4-B5）”，即可计算得到销售量变动后的盈亏临界点销售量。
在单元格C13中输入公式“=B6/（C4-B5）”，即可计算得到单价变动后的盈亏临界点销售量。
在单元格C14中输入公式“=B6/（B4-C5）”，即可计算得到单位变动成本变动后的盈亏临界点销售量。
在单元格C15中输入公式“=C6/（B4-B5）”，即可计算得到固定成本变动后的盈亏临界点销售量。
在单元格D12中输入公式“=C3*（B4-B5）-B6”，即可计算得到销售量变动后的预计利润。
在单元格D13中输入公式“=B3*（C4-B5）-B6”，即可计算得到单价变动后的预计利润。
在单元格D14中输入公式“=B3*（B4-C5）-B6”，即可计算得到单位变动成本变动后的预计利润。
在单元格D15中输入公式“=B3*（B4-B5）-C6”，即可计算得到固定成本变动后的预计利润。
选中单元格区域E12：E15，输入公式“=D12：D15-F8”，即可计算得到各因素变动后的利润变动额。
选中单元格区域F12：F15，输入公式“=E12：E15/F8”，即可计算得到各因素变动后的利润变动率。
完成了单因素敏感性分析模型后，通过单击滚动条控件改变某一因素，可以自动得到该因素变化对盈亏临界点销售量和利润的影响额，计算结果如图7-36所示，当销售量变动为14.00%、单价变动为-3.00%、单位变动成本变动为-5.00%、固定成本变动为-5.00%时，某一因素变化分别对盈亏临界点销售量和利润的影响额。
图7-35 单因素敏感性分析表
图7-36 单因素敏感分析计算结果
（5）建立多因素敏感性分析表，如图7-37所示。
在单元格B17中输入公式“=C6/（C4-C5）”，可计算得到各因素均变动后的盈亏临界点销售量。
在单元格E17中输入公式“=C3*（C4-C5）-C6”，可计算得到各因素均变动后的利润额。
在单元格B18中输入公式“=E17-F8”，可计算得到各因素均变动后的利润变动额。
在单元格E18中输入公式“=B18/F8”，可计算得到各因素均变动后的利润变动率。
通过单击滚动条控件改变某一因素，可以自动得到该因素变化对盈亏临界点销售量和利润的影响额，如图7-38所示，当销售量变动为14.00%、单价变动为-3.00%、单位变动成本变动为-5.00%、固定成本变动为-5.00%时，四种因素同时变动对盈亏临界点销售量和利润的影响额。
图7-37 多因素敏感分析表
图7-38 多因素敏感分析计算结果
免责声明：以上内容源自网络，版权归原作者所有，如有侵犯您的原创版权请告知，我们将尽快删除相关内容。
