图7.5.2-3设置控件格式
步骤三：在B6:B13 单元格区域输入内插值的计算公式，公式如下，计算结果如图7.5.2-4。
?
? ? ? ? ? ? B7单元格公式：“=C7/100”；
B8单元格公式：“=MATCH(B7,B4:I4,-1)+1”； B10单元格公式：“=INDEX($A$3:$I$3,,$B$8)”； B11单元格公式：“=INDEX($A$3:$I$3,,$B$8+1)”； B12单元格公式：“=INDEX($A$4:$I$4,,$B$8)”； B13单元格公式：“=INDEX($A$4:$I$4,,$B$8+1)”； B6单元格公式：“=TREND(B10:B11,B12:B13,B7)”。
图7.5.2-4计算结果图
步骤四：选择图表右键单击，在关联菜单中点击“选择数据”，打开“选择数据源”对话框，在系列中单击“添加”按钮，打开“编辑数据系列”对话框，设置X值为“=艾宾浩斯记忆曲线!$B$6”，Y值为“=艾宾浩斯记忆曲线!$B$7”，如图7.5.2-5，单击“确定“按钮，在图表中添加了一个数据点，如图7.5.2-6。
图7.5.2-5编辑数据系列
46
图7.5.2-6曲线标记点
步骤五：选择刚刚自动添加的点，右键单击，在关联菜单中点击“添加数据标签”下的“添加数据标注”，如图7.5.2-7所示。完成曲线坐标值的动态显示，单击微调按钮，点沿着艾宾浩斯记忆曲线移动，并动态显示点的坐标值，如图7.5.2-8所示。
图7.5.2-7添加数据标注
图7.5.2-8艾宾浩斯记忆曲线
7.5.3本量利分析模型的创建
本量利分析是管理会计的主要内容，是企业在预测、决策、规划和控制工作中常用的也是最有用和最有效的方法之一。本节着重讨论基于Excel工作表的动态图表本量利分析模型的创建与应用。即应用Excel工作表的图表功能将本量利之间的数量关系以动态图表的形式展现出来，并在动态图表本量利分析模型中进行因素变动的假设分析，使各因素变动对结果（利润）的影响程度直观地、动态地再现出来。
例：假定江海电器有限公司生产一种新产品A，产品单位售价为98元，单位变动成本为60元，全年固定成本为80000元，企业正常的产品销售量为2000件。
如果上述案例中的A产品的销售数量可能在1000件到2600件之间变动（变化率为100件），那么在不同销售量水平下，该企业的利润总额各是多少？或者说如果A产品的销售单价可能
47
在95元到110元之间变动（变化率为1元），那么在不同单位售价条件下，其利润总额各是多少？
根据以上资料首先在Excel工作表中创建本量利分析模型。
步骤一：将案例中的相关资料输入Excel工作表，在Excel工作表中输入创建本量利分析基本模型的相关资料，如图7.5.3-1所示。
图7.5.3-1 基本数据模型
步骤二：计算销售总额、成本总额、利润总额和保本点。
用鼠标单击要输入公式的单元格C8然后输入公式“=C4*C7”，然后回车确定，在C8单元格就可以得到算的结果，本例计算出来的销售总额为196000件\。同理，我们可以通过在C9单元格输入公式“=C5+C4*C6”计算出成本总额，在C10单元格输入公式“=C8-C9”计算出利润总额，在C12单元格输
入公式“=C5/(C7-C6)”计算出保本点等指标，如图7.5.3-2。
图7.5.3-2保本点计算
步骤三：在B14单元格输入IF语句“=IF(C10>0,\利润总额\亏损额\”，在C14单元格输入“=IF(C10>0,ROUND(C10,0),-ROUND(C10,0))”，如图7.5.3-3。
图7.5.3-3盈亏额计算
其次根据以上数据模型来创建直观、形象便于操作和理解的动态本量利分析模型图表。 步骤一：在F4、G4和H14单元格中，分别引用C8、C9和C10单元格中的销售总额、成本总额、利润总额，然后在E5单元格输入0，在E6单元格输入1000，在E7单元格输入1200，然后选中两个单元格，将鼠标指向选定E6:E7两个单元格的右下角，待鼠标指针变成黑色“+”指针时，按下鼠标左键向下拖动至E14单元格，如图7.5.3-4。
48
图7.5.3-4 销售量设计
步骤二：利用Excel工作表中的模拟运算表的数据处理功能，一次计算出在不同销售量情况下的销售总额、成本总额、利润总额三项指标。其方法是选中E4:H14单元格，点击菜单栏的“数据”，选择“数据工具”中“模拟分析”下的“模拟运算表”，弹出“模拟运算表”对话框，点击“输入引用列的单元格”，然后点击C4单元格，如图7.5.3-5。按下“模拟运算表”对话框上的“确定”按钮，不同销售量情况下的销售总额、成本总额、利润总额就一次全部计算出来了，如图7.5.3-6。
图7.5.3-5模拟运算表图7.5.3-6模拟运算结果
步骤三：为了使动态图表本量利分析模型出效果，还需要在动态图表本量利分析模型做出保本点指示线和利润指示线两条辅助线。
在B16单元格引用C12单元格的保本点的数据，按下F4键将“C12”转换为“$C$12”,将鼠标指向B16单元格的右下角,待鼠标指针变成黑色“+”指针时!按下鼠标左键向下拖动至B18单元格。然后选中C17单元格，输入公式“=C7*C17”，计算出保本点的销售额。然后在C16单元格输入指示线的下标0，在C18单元格输入指示线的上标260000，这样绘制保本点指示线的数据资料就已经准备好了。
同理，在E16单元格引用C4单元格的销售量的数据，按下“F4”键将“C4”转换为“$C$4”，将鼠标指向E16单元格的右下角待鼠标指针变成黑色“+”指针时,按下鼠标左键向下拖动至E19单元格，然后选中F16单元格引用C16单元格地址，选中F17单元格引用C8单元格地址，选中F18单元格引用C9单元格地址，选中F19单元格引用C18单元格地址，这样绘制利润指示线的数据资料就已经准备好了，如图7.5.3-7。
图7.5.3-7辅助线设计
49
步骤四：绘制动态图表本量利分析图表。
在工作表中插入“带平滑线和数据标记的散点图”，右击图表区，在关联菜单中“选择选择数据”，打开“选择选择数据源”对话框，在“图表数据区域”文本框中选择区域E5:H14，这样在系列中就有三个系列值，分别为“系列1”、“系列2”、“系列2”，编辑更改相对应的名称为“成本总额”、“销售总额”、“利润中总额”。 点击系列中的“添加”，弹出的“编辑数据”对话框，在“系列名称”中输入“保本点指示线”，在“X轴系列值”中选择区域“B16:B18”，在“Y轴系列值”中选择区域“C16:C18”，单击“确定”，同理添加“利润指示线系列”，在“X轴系列值”中选择区域“E16:E19”，在“Y轴系列值”中选择区域“F16:F19”，单击“确定”，如图7.5.3-8。再单击“选择选择数据源”对话框中的“确定”。回到图表，添加图表元素中的“坐标轴标题”和“图例”，如图7.5.3-9。
图7.5.3-8添加系列值
图7.5.3-9本量利分析散点图 最后添加窗体控件完善动态图表本量利分析模型。 步骤一：在J24:J26单元格中分别输入“销售额”、“利润额”、“单价”，将K24单元格链接到“=$C$4”，K25单元格链接到“=$C$10”，K26单元格链接到“=$C$7”。
步骤二：单击“开发工具”下的“插入选项”框，选择“数值调节钮（窗体控件）”，在L24单元格附近画一个微调按钮，右键单击微调项按钮，在弹出的关联菜单中选择“设置控件格式”命令，打开“设置控件格式”对话框，输入设置最小值为1000，最大值为2600，步长为100，单元格链接为“$C$4”，单击“确定”按钮，完成微调控件的添加。
同理在L26单元格附近再绘制一个微调按钮，设置最小值为90，最大值为110，步长为1，单元格链接为“$C$7”，单击“确定”按钮，完成微调控件的添加，如图7.5.3-10。
50