1、 天津财经大学天津财经大学 商学院会计系商学院会计系 第9章 本量利分析与建模 【本章重点本章重点】 本量利分析指标模型本量利分析指标模型 多因素变动分析模型多因素变动分析模型 动态本量利分析模型动态本量利分析模型 天津财经大学天津财经大学 商学院会计系商学院会计系 9.1 本量利分析基本理论 9.2 本量利分析指标计算模型 9.3 多因素变动分析模型 本章目录 【课后习题】 9.4 动态本量利分析模型 9.5 动态盈亏平衡分析模型 9.6 盈亏平衡销量随单价变化分析模型 9.7实现目标利润所需销量分析模型 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.1 本量利
2、分析基本理论 9.1.19.1.1固定成本和变动成本固定成本和变动成本 根据成本与产量依存关系的不同将成本分成两种：一种是根据成本与产量依存关系的不同将成本分成两种：一种是 不受产量影响的成本，我们称之为固定成本；另一种是随着产不受产量影响的成本，我们称之为固定成本；另一种是随着产 量的变化而正比例变化的成本，我们称之为变动成本。量的变化而正比例变化的成本，我们称之为变动成本。 总成本总成本= =变动成本变动成本+ +固定成本固定成本= =单位变动成本单位变动成本产量产量+ +固定成固定成 本本 9.1.29.1.2损益方程式损益方程式 1.1.利润方程式利润方程式 天津财经大学天津财经大学
3、商学院会计系商学院会计系 目录目录 本节本节 9.1 本量利分析基本理论 税后利润税后利润= =（单价（单价销量单位变动成本销量单位变动成本销量固定成销量固定成 本）本）（1 1- -所得税税率）所得税税率） 2.2.销量方程式销量方程式 单位变动成本单价 所得税税率 税后利润 固定成本 销量 - -1 3.3.单价方程式单价方程式 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.1 本量利分析基本理论 4.4.单位变动成本方程式单位变动成本方程式 5 5固定成本方程式固定成本方程式 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.1 本
4、量利分析基本理论 9.1.39.1.3边际贡献方程式边际贡献方程式 1.1.边际贡献与边际贡献率边际贡献与边际贡献率 边际贡献边际贡献 边际贡献边际贡献= =销售收入变动成本销售收入变动成本 单位边际贡献单位边际贡献= =单价单位变动成本单价单位变动成本 边际贡献率边际贡献率 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.1 本量利分析基本理论 2.2.边际贡献方程式边际贡献方程式 利润方程式利润方程式 税前利润税前利润= =边际贡献固定成本边际贡献固定成本 = =销量销量单位边际贡献固定成本单位边际贡献固定成本 = =销售收入销售收入边际贡献率固定成本边际贡献率
5、固定成本 销量方程式销量方程式 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.1 本量利分析基本理论 变动成本方程式变动成本方程式 变动成本变动成本= =销售收入边际贡献销售收入边际贡献 = =销售收入（税前利润固定成本）销售收入（税前利润固定成本） 单位变动成本单位变动成本= =单价单位边际贡献单价单位边际贡献 固定成本方程式固定成本方程式 固定成本固定成本= =边际贡献税前利润边际贡献税前利润 = =销量销量单位边际贡献税前利润单位边际贡献税前利润 = =销售收入销售收入单位边际贡献率税前利润单位边际贡献率税前利润 天津财经大学天津财经大学 商学院会计系商学院
6、会计系 目录目录 本节本节 9.1 本量利分析基本理论 9.1.49.1.4盈亏临界点盈亏临界点 1.1.盈亏临界点销售量盈亏临界点销售量 2.2.盈亏临界点销售额盈亏临界点销售额 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.1 本量利分析基本理论 3.3.盈亏临界点作业率盈亏临界点作业率 4.4.安全边际和安全边际率安全边际和安全边际率 安全边际安全边际 安全边际安全边际= =正常销售额盈亏临界点销售额正常销售额盈亏临界点销售额 安全边际率安全边际率 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.1 本量利分析基本理论 盈亏临界点
7、作业率与安全边际率的关系盈亏临界点作业率与安全边际率的关系 盈亏临界点作业率盈亏临界点作业率+ +安全边际率安全边际率=1=1 利润与安全边际的关系利润与安全边际的关系 利润利润= =边际贡献固定成本边际贡献固定成本 = =销售收入销售收入边际贡献率盈亏临界点销售收入边际贡献率盈亏临界点销售收入边边 际贡献率际贡献率 = =安全边际安全边际边际贡献率边际贡献率 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.2本量利分析指标计算模型 9.2.19.2.1模型功能模型功能 在该模型中，利用滚动条设置单价、销量、单位变动成本在该模型中，利用滚动条设置单价、销量、单位变动
8、成本 和固定成本后，将自动计算本量利分析相关的一系列相关指标。和固定成本后，将自动计算本量利分析相关的一系列相关指标。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.2本量利分析指标计算模型 9.2.29.2.2模型设计步骤模型设计步骤 1.1.设置基本数据滚动条及公式设置基本数据滚动条及公式 单价。假定单价为介于单价。假定单价为介于3030至至6060之间的整数，对单价滚动之间的整数，对单价滚动 条进行以下设置：最小值条进行以下设置：最小值3030，最大值，最大值6060，步长，步长1 1，页长，页长1010，单，单 元格链接“元格链接“C2”C2”。设置“。设
9、置“C2”C2”单元格为左对齐。单元格为左对齐。 单位变动成本。假定单位变动成本为介于单位变动成本。假定单位变动成本为介于2020至至4040之间的之间的 小数，且保留小数，且保留1 1位小数，对单位变动成本滚动条进行以下设置：位小数，对单位变动成本滚动条进行以下设置： 最小值最小值200200，最大值，最大值400400，步长，步长1 1，页长，页长1010，单元格链接“，单元格链接“F2”F2”。 在“在“E2”E2”单元格输入公式“单元格输入公式“=F2/10”=F2/10”。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.2本量利分析指标计算模型 销量。假
10、定销量为介于销量。假定销量为介于10001000至至20002000之间的整数，且最小之间的整数，且最小 变动幅度为变动幅度为100100，对销量滚动条进行以下设置：最小值，对销量滚动条进行以下设置：最小值10001000， 最大值最大值20002000，步长，步长100100，页长，页长500500，单元格链接“，单元格链接“C3”C3”。设置。设置 “C3”C3”单元格为左对齐。单元格为左对齐。 固定成本。假定固定成本为固定成本。假定固定成本为2000020000至至5000050000之间的整数，之间的整数， 且最小变动幅度为且最小变动幅度为10001000。因为滚动条最大值范围为。因为
11、滚动条最大值范围为3000030000，所，所 以需借助公式完成设置。对固定成本滚动条进行以下设置：最以需借助公式完成设置。对固定成本滚动条进行以下设置：最 小值小值2020，最大值，最大值5050，步长，步长1 1，页长，页长1010，单元格链接“，单元格链接“F3”F3”。在。在 “E3”E3”单元格输入公式“单元格输入公式“=F3*1000”=F3*1000”，并设置为左对齐。，并设置为左对齐。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.2本量利分析指标计算模型 2.2.设置指标计算公式设置指标计算公式 利润总额公式。在“利润总额公式。在“C6”C6”单
12、元格输入利润公式“单元格输入利润公式“=(C2=(C2- - E2)*C3E2)*C3- -E3”E3”。 保本点销量。在“保本点销量。在“E6”E6”单元格输入保本点销量公式单元格输入保本点销量公式 “=E3/(C2=E3/(C2- -E2)”E2)”。 保本点销售额。在“保本点销售额。在“C7”C7”单元格输入保本点销售额公式单元格输入保本点销售额公式 “=E6*C2”=E6*C2”。 保本点作业率。在“保本点作业率。在“E7”E7”单元格输入保本点作业率公式单元格输入保本点作业率公式 “=C7/(C2*C3)”=C7/(C2*C3)”。 天津财经大学天津财经大学 商学院会计系商学院会计系
13、 目录目录 本节本节 9.2本量利分析指标计算模型 安全边际。在“安全边际。在“C8”C8”单元格输入安全边际公式单元格输入安全边际公式 “=C2*C3=C2*C3- -C7”C7”。 安全边际率。在“安全边际率。在“E8”E8”单元格输入公式单元格输入公式 “=C8/(C2*C3)”=C8/(C2*C3)”。 单价敏感系数。在“单价敏感系数。在“C9”C9”单元格输入公式单元格输入公式 “=C3*C2/C6”=C3*C2/C6”。 单位变动成本敏感系数。在“单位变动成本敏感系数。在“E9”E9”单元格输入公式“单元格输入公式“= =- - C3*E2/C6”C3*E2/C6”。 天津财经大学
14、天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.2本量利分析指标计算模型 销量敏感系数。在“销量敏感系数。在“C10”C10”单元格输入公式“单元格输入公式“=(C2=(C2- - E2)*C3/C6”E2)*C3/C6”。 固定成本敏感系数。在“固定成本敏感系数。在“E10”E10”单元格输入公式“单元格输入公式“= =- - E4/C6”E4/C6”。 3.3.保护工作表保护工作表 选中滚动条链接单元格“选中滚动条链接单元格“C2C2：C3”C3”、“、“F2F2：F3”F3”，取消其，取消其 单元格锁定状态，如果不希望看到保留滚动条的中间数据，可单元格锁定状态，如果不希望
15、看到保留滚动条的中间数据，可 将将F F列隐藏，然后保护工作表。列隐藏，然后保护工作表。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.3多因素变动分析模型 9.3.19.3.1模型功能模型功能 输入单价、单位变动成本、销量和固定成本的当前值，然输入单价、单位变动成本、销量和固定成本的当前值，然 后根据可行方案利用滚动条调整以上四因素的变动百分比，即后根据可行方案利用滚动条调整以上四因素的变动百分比，即 可自动计算变动后利润及其变动百分比。同时，以图表的形式可自动计算变动后利润及其变动百分比。同时，以图表的形式 对比当前利润和变动后利润，并可用微调钮选择关注项是当
16、前对比当前利润和变动后利润，并可用微调钮选择关注项是当前 利润还是变动后利润，关注项图表将以红色醒目显示。此外，利润还是变动后利润，关注项图表将以红色醒目显示。此外， 利用该模型也可以进行利润规划分析，例如为了使得明年利润利用该模型也可以进行利润规划分析，例如为了使得明年利润 提高提高10%10%，可采取哪些单一措施或配套措施，在此信息基础上，可采取哪些单一措施或配套措施，在此信息基础上 企业可进行各种措施的可行性分析论证，并进行最终决策。企业可进行各种措施的可行性分析论证，并进行最终决策。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.3多因素变动分析模型 天津
17、财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.3多因素变动分析模型 9.3.29.3.2模型设计步骤模型设计步骤 1.1.输入当前数据输入当前数据 根据企业当前资料，分别输入单价、单位变动成本、销量根据企业当前资料，分别输入单价、单位变动成本、销量 和固定成本的值。可根据需要，分别设置这四个单元格的数据和固定成本的值。可根据需要，分别设置这四个单元格的数据 有效性，以保证输入的数据有效。有效性，以保证输入的数据有效。 2.2.设置变动百分比设置变动百分比 假定单价、单位变动成本、销量和固定成本的变动幅度在假定单价、单位变动成本、销量和固定成本的变动幅度在 3030和和
18、3030之间，可以设置四个窗体滚动条分别用于设置单之间，可以设置四个窗体滚动条分别用于设置单 价、单位变动成本、销量和固定成本的变动幅度，并将滚动条价、单位变动成本、销量和固定成本的变动幅度，并将滚动条 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.3多因素变动分析模型 的当前值、最小值、最大值、步长和页步长分别设置为的当前值、最小值、最大值、步长和页步长分别设置为3030、0 0、 6060、1 1、1010，单元格链接分别指定为“，单元格链接分别指定为“C6”C6”、“、“C7”C7”、“、“C8”C8”、 “C9”C9”。 在“在“B6”B6”单元格定义公式
19、“单元格定义公式“=C6/100=C6/100- -30%”30%”，并将该公式，并将该公式 复制到“复制到“B7:B9”B7:B9”，并将其设置为百分比格式。，并将其设置为百分比格式。 在“在“D6”D6”单元格输入公式“单元格输入公式“=B2*(1+B6)”=B2*(1+B6)”，即变动后单价，即变动后单价 等于原单价加单价变动额。等于原单价加单价变动额。 在“在“D7”D7”单元格输入公式“单元格输入公式“=D2*(1+B7)”=D2*(1+B7)”，即变动后单位，即变动后单位 变动成本等于原单位变动成本加单位变动成本变动额。变动成本等于原单位变动成本加单位变动成本变动额。 天津财经大学
20、天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.3多因素变动分析模型 在“在“D8”D8”单元格输入公式“单元格输入公式“=B3*(1+B8)”=B3*(1+B8)”，即变动后销量，即变动后销量 等于原销量加销量变动额。在“等于原销量加销量变动额。在“D9”D9”单元格输入公式单元格输入公式 “=D3*(1+B9)”=D3*(1+B9)”，即变动后固定成本等于原固定成本加固定成，即变动后固定成本等于原固定成本加固定成 本变动额。本变动额。 3.3.计算利润相关指标计算利润相关指标 在“在“B10”B10”输入当前利润计算公式“输入当前利润计算公式“=(B2=(B2- -D2)*
21、B3D2)*B3- -D3”D3”； 在“在“D10”D10”输入变动后利润计算公式“输入变动后利润计算公式“=(D6=(D6- -D7)*D8D7)*D8- -D9”D9”；在；在 “B11”B11”输入利润变动额计算公式“输入利润变动额计算公式“=D10=D10- -B10”B10”；在“；在“D11”D11” 输入利润变动百分比计算公式“输入利润变动百分比计算公式“=B11/B10”=B11/B10”。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.3多因素变动分析模型 4.4.制作图表制作图表 根据当前利润及变动后利润制作柱形图，同时可以利用微根据当前利润
22、及变动后利润制作柱形图，同时可以利用微 调钮选择关注项为当前利润还是变动后利润，关注项将以红色调钮选择关注项为当前利润还是变动后利润，关注项将以红色 图形突出显示。为了实现以上功能，需进行以下操作：图形突出显示。为了实现以上功能，需进行以下操作： 设置关注项设置关注项 添加一个微调钮，设置其最小值为添加一个微调钮，设置其最小值为1 1，最大值为，最大值为2 2，步长为，步长为 1 1，单元格链接为“，单元格链接为“F3”F3”。添加一个文本框，其内容为提示信。添加一个文本框，其内容为提示信 息“关注项”，并进行必要的格式设置。此外，为了根据微调息“关注项”，并进行必要的格式设置。此外，为了根据
23、微调 钮的值显示关注项，在“钮的值显示关注项，在“F4”F4”单元格输入公式“单元格输入公式“=IF(F3=1,=IF(F3=1, 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.3多因素变动分析模型 “当前利润当前利润”,“”,“变动后利润变动后利润”)”)”，然后添加一个文本框，选，然后添加一个文本框，选 中该文本框，在公式编辑栏内输入“中该文本框，在公式编辑栏内输入“=F4”=F4”，使文本框与“，使文本框与“F4”F4” 单元格建立链接。单元格建立链接。 准备图表数据准备图表数据 在“在“G3”G3”单元格输入“当前利润”，在“单元格输入“当前利润”，在“H
24、3”H3”单元格输入单元格输入 “变动后利润”，在“变动后利润”，在“G4”G4”单元格输入公式“单元格输入公式“=B10”=B10”，在“，在“H4”H4” 单元格输入公式“单元格输入公式“=D10”=D10”，在“，在“G5”G5”单元格输入公式单元格输入公式 “=IF(F3=1,G4,0)”=IF(F3=1,G4,0)”，在“，在“H5”H5”单元格输入公式单元格输入公式 “=IF(F3=2,H4,0)”=IF(F3=2,H4,0)”。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.3多因素变动分析模型 制作柱形图制作柱形图 选中区域“选中区域“G3G3：H
25、5”H5”制作柱形图，并参照图制作柱形图，并参照图9 9- -2 2进行必要进行必要 的格式设置，在图中右键单击某系列图形，出现快捷菜单，选的格式设置，在图中右键单击某系列图形，出现快捷菜单，选 择其中的“设置数据系列格式”，出现对话框，将系列重叠值择其中的“设置数据系列格式”，出现对话框，将系列重叠值 设置为设置为100%100%。两个系列的图形将自动重合，由此便实现了动态。两个系列的图形将自动重合，由此便实现了动态 显示关注项。可参照显示关注项。可参照1111- -2 2，将图表移动至适当位置。，将图表移动至适当位置。 5.5.保护工作表保护工作表 选中“选中“F3”F3”、“、“C6C6
26、：C9”C9”、“、“B2B2：B3”B3”、“、“D2D2：D3”D3”，取，取 消其单元格锁定状态，然后保护工作表。消其单元格锁定状态，然后保护工作表。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.4动态本量利分析模型 9.4.19.4.1模型功能模型功能 利用滚动条调节单价、单位变动成本、当前销量、固定成利用滚动条调节单价、单位变动成本、当前销量、固定成 本等数值时，本量利分析图会相应地进行动态调整，其中当前本等数值时，本量利分析图会相应地进行动态调整，其中当前 销量以红色垂线显示，并标注其与成本线、收入线和利润线的销量以红色垂线显示，并标注其与成本线、收
27、入线和利润线的 交点，即当前销量对应的总成本、销售收入和利润总额。模型交点，即当前销量对应的总成本、销售收入和利润总额。模型 界面如下图所示：界面如下图所示： 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.4动态本量利分析模型 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.4动态本量利分析模型 9.4.29.4.2模型设计步骤模型设计步骤 1.1.设置基本数据设置基本数据 假定单价为假定单价为50005000至至2000020000的整数，且变化幅度为的整数，且变化幅度为500500，设置，设置 其滚动条最小值为其滚动条最小值为500
28、05000，最大值为，最大值为2000020000，步长为，步长为500500，页长为，页长为 10001000，单元格链接为“，单元格链接为“C2”C2”。假定单位变动成本为。假定单位变动成本为30003000至至 1000010000的整数，且变化幅度为的整数，且变化幅度为500500，设置其滚动条最小值为，设置其滚动条最小值为30003000， 最大值为最大值为1000010000，步长为，步长为500500，页长为，页长为10001000，单元格链接为，单元格链接为 “C3”C3”。假定当前销量为。假定当前销量为10001000至至1000010000的整数，且变化幅度为的整数，且变化
29、幅度为 500500，设置其滚动条最小值为，设置其滚动条最小值为10001000，最大值为，最大值为1000010000，步长为，步长为 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.4动态本量利分析模型 500500，页长为，页长为10001000，单元格链接为“，单元格链接为“C4”C4”。假定固定成本为。假定固定成本为1 1 000 000000 000至至2 000 0002 000 000的整数，且变化幅度为的整数，且变化幅度为1000010000，设置其滚，设置其滚 动条最小值为动条最小值为100100，最大值为，最大值为200200，步长为，步长为1
30、 1，页长为，页长为1010，单元，单元 格链接为“格链接为“D5”D5”，在“，在“C5”C5”单元格输入公式“单元格输入公式“=D5*10000”=D5*10000”。 2.2.准备图表数据准备图表数据 本量利数据本量利数据 按照下图输入数据和公式，然后利用数据表功能生成数据。按照下图输入数据和公式，然后利用数据表功能生成数据。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.4动态本量利分析模型 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.4动态本量利分析模型 当前销量数据当前销量数据 因为要在图表中显示当前销量直线及其对应的总
31、成本、销因为要在图表中显示当前销量直线及其对应的总成本、销 售收入和利润，所以需要按下图准备数据：售收入和利润，所以需要按下图准备数据： 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.4动态本量利分析模型 3.3.制作图表制作图表 同时选中区域“同时选中区域“N1N1：Q1”Q1”和“和“N3N3：Q4”Q4”；在功能区“插；在功能区“插 入”选项卡中，单击“散点图带平滑线的散点图”，将自动入”选项卡中，单击“散点图带平滑线的散点图”，将自动 生成图表；接着，在图表被选中的状态下，在功能区“设计”生成图表；接着，在图表被选中的状态下，在功能区“设计” 选项卡中单击
32、“切换行选项卡中单击“切换行/ /列”按钮，即可出现所需的本量利图。列”按钮，即可出现所需的本量利图。 添加当前销量直线。选中本量利图表，在功能区“设计”添加当前销量直线。选中本量利图表，在功能区“设计” 选项卡中单击“选择数据”，出现选择数据源对话框。在该对选项卡中单击“选择数据”，出现选择数据源对话框。在该对 话框中，单击“添加”按钮，出现编辑数据系列对话框，在该话框中，单击“添加”按钮，出现编辑数据系列对话框，在该 对话框中，将光标定位到“对话框中，将光标定位到“X X轴系列值”文本框，然后用鼠标轴系列值”文本框，然后用鼠标 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录
33、本节本节 9.4动态本量利分析模型 选择“选择“N6N6：N7”N7”，再将光标定位到“，再将光标定位到“Y Y轴系列值”文本框，然轴系列值”文本框，然 后用鼠标选择“后用鼠标选择“O6O6：O7”O7”。设置完毕，单击“确定”按钮即可。设置完毕，单击“确定”按钮即可 在本量利图表中添加一条垂线，表示当前销量。可参照图在本量利图表中添加一条垂线，表示当前销量。可参照图9 9- -3 3 设置该系列格式。设置该系列格式。 添加当前销量直线与总成本线、销售收入线和利润总额添加当前销量直线与总成本线、销售收入线和利润总额 线的交点系列。参照以上步骤，依次添加线的交点系列。参照以上步骤，依次添加3 3
34、个系列，第个系列，第1 1个系列个系列 的的X X轴系列值为“轴系列值为“N9”N9”，Y Y轴系列值为“轴系列值为“O9”O9”，第，第2 2个系列的个系列的X X轴轴 系列值为“系列值为“N10”N10”，Y Y轴系列值为“轴系列值为“O10”O10”，第，第3 3个系列的个系列的X X轴系轴系 列值为“列值为“N11”N11”，Y Y轴系列值为“轴系列值为“O11”O11”。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.4动态本量利分析模型 4.4.保护工作表保护工作表 在该模型中，只需用滚动条设置单价、单位变动成本、销在该模型中，只需用滚动条设置单价、单
35、位变动成本、销 量和固定成本数据，为此，选中“量和固定成本数据，为此，选中“C2C2：C4”C4”和“和“D5”D5”，取消其，取消其 单元格锁定状态，然后保护工作表。单元格锁定状态，然后保护工作表。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.5动态盈亏平衡分析模型 9.5.19.5.1模型功能模型功能 在给定的单价、单位变动成本和固定成本下，根据一系列在给定的单价、单位变动成本和固定成本下，根据一系列 销量制作成本线、收入线和利润线，并以红色垂直线显示当前销量制作成本线、收入线和利润线，并以红色垂直线显示当前 条件下的盈亏平衡销量及其与以上三条曲线的交点。当
36、通过微条件下的盈亏平衡销量及其与以上三条曲线的交点。当通过微 调钮调整单价、单位变动成本和固定成本时，各曲线将随之动调钮调整单价、单位变动成本和固定成本时，各曲线将随之动 态调整。此外，还可通过微调钮设置当前销量，并在图中显示态调整。此外，还可通过微调钮设置当前销量，并在图中显示 当前销量垂线及其与利润线的交点，随着销量的变化，该垂线当前销量垂线及其与利润线的交点，随着销量的变化，该垂线 也将动态调整。该模型可以直观地动态反映当前给定条件下的也将动态调整。该模型可以直观地动态反映当前给定条件下的 盈亏平衡点以及安全边际。盈亏平衡点以及安全边际。 天津财经大学天津财经大学 商学院会计系商学院会计
37、系 目录目录 本节本节 9.5动态盈亏平衡分析模型 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.5动态盈亏平衡分析模型 9.5.29.5.2模型设计步骤模型设计步骤 1.1.设置基本数据设置基本数据 变动成本。假定变动成本为变动成本。假定变动成本为5 5- -1010的整数，添加一个微调的整数，添加一个微调 钮，设置其最小值为钮，设置其最小值为5 5，最大值为，最大值为1010，单元格链接为“，单元格链接为“A3”A3”。 在微调钮前添加一个文本框，并将其与“在微调钮前添加一个文本框，并将其与“A3”A3”单元格链接。再单元格链接。再 添加一个文本框并输入“变动
38、成本”提示信息。添加一个文本框并输入“变动成本”提示信息。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.5动态盈亏平衡分析模型 单价。假定单价为单价。假定单价为1515- -2020的整数，添加一个微调钮，设的整数，添加一个微调钮，设 置其最小值为置其最小值为1515，最大值为，最大值为2020，单元格链接为“，单元格链接为“A1”A1”。在微调。在微调 钮前添加一个文本框，并将其与“钮前添加一个文本框，并将其与“A1”A1”单元格链接。再添加一单元格链接。再添加一 个文本框并输入“单价”提示信息。个文本框并输入“单价”提示信息。 固定成本。假定固定成本为固定成
39、本。假定固定成本为1010万万- -1515万的整数，添加一万的整数，添加一 个微调钮，设置其最小值为个微调钮，设置其最小值为100100，最大值为，最大值为150150，单元格链接为，单元格链接为 “A6”A6”。在“。在“A7”A7”单元格输入公式“单元格输入公式“=A6*1000”=A6*1000”。在微调钮前。在微调钮前 添加一个文本框，并将其与“添加一个文本框，并将其与“A7”A7”单元格链接。再添加一个文单元格链接。再添加一个文 本框并输入“固定成本”提示信息。本框并输入“固定成本”提示信息。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.5动态盈亏平
40、衡分析模型 当前销量。假定当前销量为当前销量。假定当前销量为0 0- -4 4万的整数，添加一个微万的整数，添加一个微 调钮，设置其最小值为调钮，设置其最小值为0 0，最大值为，最大值为400400，单元格链接为“，单元格链接为“A4”A4”。 在“在“A5”A5”单元格输入公式“单元格输入公式“=A4*100”=A4*100”。在微调钮前添加一个。在微调钮前添加一个 文本框，并将其与“文本框，并将其与“A5”A5”单元格链接。再添加一个文本框并输单元格链接。再添加一个文本框并输 入“当前销量”提示信息。入“当前销量”提示信息。 2.2.准备图表数据准备图表数据 成本线、收入线和利润线数据成本
41、线、收入线和利润线数据 按照下图所示，准备计算成本、收入和利润的计算公式，按照下图所示，准备计算成本、收入和利润的计算公式， 并利用数据表功能求解各销量对应的成本、收入和利润。并利用数据表功能求解各销量对应的成本、收入和利润。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.5动态盈亏平衡分析模型 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.5动态盈亏平衡分析模型 盈亏平衡销量直线数据盈亏平衡销量直线数据 盈亏平衡线与收入线交点坐标盈亏平衡线与收入线交点坐标 当前销量直线数据当前销量直线数据 天津财经大学天津财经大学 商学院会计系商学
42、院会计系 目录目录 本节本节 9.5动态盈亏平衡分析模型 当前销量直线与利润线交点坐标当前销量直线与利润线交点坐标 3.3.制作图表制作图表 根据图表数据，制作散点图，以显示不同销量下的收入根据图表数据，制作散点图，以显示不同销量下的收入 线、成本线和利润线。生成图表后，可对图表刻度、格式等进线、成本线和利润线。生成图表后，可对图表刻度、格式等进 行必要的设置。行必要的设置。 为图表添加一个系列，用以显示盈亏平衡线，为图表添加一个系列，用以显示盈亏平衡线，X X轴系列轴系列 值为“值为“D16D16：D17”D17”，Y Y轴系列值为“轴系列值为“E16E16：E17”E17”。 天津财经大学
43、天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.5动态盈亏平衡分析模型 为图表添加一个系列，用以显示盈亏平衡线与收入线的为图表添加一个系列，用以显示盈亏平衡线与收入线的 交点，交点，X X轴系列值为“轴系列值为“D19”D19”，Y Y轴系列值为“轴系列值为“E19”E19”。 为图表添加一个系列，用以显示当前销量线，为图表添加一个系列，用以显示当前销量线，X X轴系列轴系列 值为“值为“D13D13：D14”D14”，Y Y轴系列值为“轴系列值为“E13E13：E14”E14”。 为图表添加一个系列，用以显示当前销量线与利润线的为图表添加一个系列，用以显示当前销量线与利润线的
44、 交点，系列交点，系列X X值坐标为“值坐标为“D21”D21”，Y Y值坐标为“值坐标为“E21”E21”。 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.5动态盈亏平衡分析模型 在图表中添加两个文本框，分别动态显示盈亏平衡销量在图表中添加两个文本框，分别动态显示盈亏平衡销量 和当前利润。在“和当前利润。在“D23”D23”单元格输入公式“单元格输入公式“=IF(A1A3,“=IF(A1A3,“盈亏平盈亏平 衡销量衡销量“&“=“&ROUND(D19,0),“)”“&“=“&ROUND(D19,0),“)”，然后将某个文本框与，然后将某个文本框与 “D23”D2
45、3”进行链接。在“进行链接。在“D24”D24”单元格输入公式“单元格输入公式“=“=“当前利润当前利润 “&“=“&ROUND(E21,0)”“&“=“&ROUND(E21,0)”，然后将另一文本框与“，然后将另一文本框与“D24”D24”进行链进行链 接。其中接。其中roundround（）为四舍五入函数，利用该函数将结果保留（）为四舍五入函数，利用该函数将结果保留 为整数。可对单元格进行必要的格式设置，并放置于图表之上。为整数。可对单元格进行必要的格式设置，并放置于图表之上。 4.4.保护工作表保护工作表 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.6盈亏
46、平衡销量随单价变化分析模型 9.6.19.6.1模型功能模型功能 在给定单位变动成本和固定成本下，显示盈亏平衡销量随在给定单位变动成本和固定成本下，显示盈亏平衡销量随 单价的变化趋势图，同时突出显示当前单价线及其与变化趋势单价的变化趋势图，同时突出显示当前单价线及其与变化趋势 线的交点，即当前单价下对应的盈亏平衡点销量。当用微调钮线的交点，即当前单价下对应的盈亏平衡点销量。当用微调钮 调节单位变动成本和固定成本时，盈亏平衡趋势线会自动随之调节单位变动成本和固定成本时，盈亏平衡趋势线会自动随之 移动；当用微调钮调节当前单价时，垂直单价线将随之动态移移动；当用微调钮调节当前单价时，垂直单价线将随之
47、动态移 动。模型界面如下图所示：动。模型界面如下图所示： 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.6盈亏平衡销量随单价变化分析模型 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.6盈亏平衡销量随单价变化分析模型 9.6.29.6.2模型设计步骤模型设计步骤 1.1.设置基本数据设置基本数据 在该模型中，固定成本和单位变动成本属于外部变量，单在该模型中，固定成本和单位变动成本属于外部变量，单 价属于内部的自变量，先参照利用微调钮、文本框等工具设置价属于内部的自变量，先参照利用微调钮、文本框等工具设置 固定成本、单位变动成本和单价数
48、据，假定固定成本介于固定成本、单位变动成本和单价数据，假定固定成本介于1010万万 - -1515万，单位变动成本介于万，单位变动成本介于5 5- -1010，单价介于，单价介于1010- -1515。 2.2.准备图表数据准备图表数据 天津财经大学天津财经大学 商学院会计系商学院会计系 目录目录 本节本节 9.6盈亏平衡销量随单价变化分析模型 制作该图表，需要准备在给定固定成本和单位变动成本下制作该图表，需要准备在给定固定成本和单位变动成本下 的一系列单价所对应的盈亏平衡销量，为此需要准备单价和对的一系列单价所对应的盈亏平衡销量，为此需要准备单价和对 应的销量数据。假定单价介于应的销量数据。假定单价介于1515- -2020，直
