在产品销售本量利分析中应用

Office职场应用书库 Excel 2010函数图表入门与实战体验在 产 品 销 售本量利分析中 应 用 盈 亏 平衡 销 售量 预测 分析 不同 单 位售价 对 利 润 的影响 分析 单 位可 变 成本 对 利 润 的影响 利用 模 拟 运算 计 算不同 单 位售价保本点 单 位 利 润 的 计 算盈 亏 平衡 销 售量 预测 分析 单 一 产 品保本点 预测1本、量、利分析法2 贡 献毛利分析 法 多 产 品保本点 预测1利用加 权 平均法 进 行多 产 品保本点的 预测2利用 贡 献毛利分解法 进 行多 产 品保本点的 预测3利用 贡 献毛利分解法 计 算利 润额4保本点 预测 中 应 注意的 问题贡 献毛利分析法具体的 计 算操作步 骤 如下：步 骤 1：根据所 给资 料在 “销 售公司本量利分析 ”工作簿中 创 建一个 “用 贡 献毛利法 计算保本点 ”工作表，将生 产 成本和 销 售 单 价 记录 到工作表中，如 图 5-5所示。步 骤 2： 计 算 单 位 贡 献毛利（ 单 位 贡 献毛利 =销 售 单 价 单 位 变动 成本）。在 “用 贡献毛利法 计 算保本点 ”工作表中的 C16单 元格中 输 入公式 “=C19-C15”，即可 计 算出 单位 贡 献毛利，如 图 5-6所示。贡 献毛利分析法具体的 计 算操作步 骤 如下：步 骤 3： 计 算 单 位 贡 献毛利率（ 贡 献毛利率 =单 位 贡 献毛利 /销 售 单 价）。在 “用 贡 献毛利法 计 算保本点 ”工作表的 C17单 元格中 输 入公式 “=C16/C19”，即可 计 算出 贡 献毛利率，如 图 5-7所示。步 骤 4： 计 算保本 销 售量（保本 销 售量 =固定成本 总额 /单 位 贡 献毛利）。在 “用 贡 献毛利法 计 算保本点 ”工作表的 C20单 元格中 输 入公式 “=C18/C16”，即可 计 算出保本 销售量，如 图 5-8所示。贡 献毛利分析法具体的 计 算操作步 骤 如下：步 骤 5： 计 算保本 销 售 额 （保本 销 售 额 =固定成本 总额 /单 位 贡 献毛利率）。在 “用 贡献毛利法 计 算保本点 ”工作表的 C21单 元格中 输 入公式 “=C18/C17”，即可 计 算出保本销 售 额 ，如 图 5-9所示。不同 单 位售价 对 利 润 的影响 建立基本 计 算 表1 计 算指定 销 售 单 价 时 盈 亏 平衡点及不同 销 量所 对应 的 利润额2添加 滚动 条控制 单 位 售价 建立本量利分析 图添加 滚动 条控制 单 位售价添加 滚动 条以控制 单 位售价的具体操作步 骤 如下：步 骤 1：添加 “开 发 工具 ”主 选项 卡。在 Excel工作界面中 选择 【文件】主 选项 卡，即可进 入【文件】窗口，如 图 5-47所示。步 骤 2： 单击 【 选项 】按 钮 ，即可打开【 Excel选项 】 对话 框，在其中 选择 【自定 义功能区】 选项 ，再右 侧 自定 义 功能区的 “主 选项 卡 ”设 置窗格中勾 选 “开 发 工具 ”复 选 框，如 图 5-48所示。添加 滚动 条控制 单 位售价添加 滚动 条以控制 单 位售价的具体操作步 骤 如下：步 骤 3： 单击 【确定】按 钮 ，即可将 该 主 选项 卡添加到 Excel工作界面中。 单击 【插入】按 钮 下的三角按 钮 ，即可打开添加的 “控件工具箱 ”，如 图 5-49所示。步 骤 4： 绘 制文本框用于 显 示当前的售价。在 “插入 ”主 选项 卡中 单击 【文本框】下的三角按 钮 ，即可打开 该 下拉菜 单 ，在其中 选择 “横排文本框 ”选项 ，在工作表中 绘 制一个横排文本框，在其中 输 入 “单 位售价 ”文字，如 图 5-50所示。添加 滚动 条控制 单 位售价添加 滚动 条以控制 单 位售价的具体操作步 骤 如下：步 骤 5：再复制一个相同的文本框并 设 置与 B5单 元格 链 接， 这样 “单 位售价 ”就可以 实时变 化了，如 图 5-51所示。步 骤 6： 绘 制 “滚动 条 ”并 设 置 滚动 条的格式。在 “插入控件 ”按 钮 下 单击 “滚动 条 ”按 钮，在 “单 位售价 ”横排文本框下方 绘 制一个 滚动 条，如 图 5-52所示。添加 滚动 条控制 单 位售价添加 滚动 条以控制 单 位售价的具体操作步 骤 如下：步 骤 7：在 绘 制完 毕 后，从右 键弹 出菜 单 中 选择 “设 置控件格式 ”选项 ，即可打开【 设置控件格式】 对话 框，如 图 5-53所示。 选择 “控制 ”选项 卡，在其中 设 置控件的当前值 、最小 值 、最大 值 、步 长 、 页 步 长 等 值 ， 这 些 值 都在 1 30000之 间 的整数，如 图5-54所示。添加 滚动 条控制 单 位售价添加 滚动 条以控制 单 位售价的具体操作步 骤 如下：步 骤 8： 选择 控件 链 接 单 元格。在【 设 置控件格式】 对话 框中 单击 “单 元格 链 接 ”文本框后面的 按 钮 ，即可激活【 设 置控件格式】 对话 框，在工作表中 选择 B5单 元格，如图 5-55所示。 选择 完 毕 后，按下 Enter键 ，即可将 滚动 条的 单 元格 链 接 设 置 为 B5，如 图 5-56所示。添加 滚动 条控制 单 位售价添加 滚动 条以控制 单 位售价的具体操作步 骤 如下：步 骤 9：在 对滚动 条的格式 设 置完 毕 后， 单击 【确定】按 钮 返回到工作表，通 过 拖动滚动 条或 单击 左右 侧 按 钮 ，即可以 0.1为 步 长 更改 单 位售价，如 图 5-57所示。在拖动滚动 条 时 ，不同 单 位售价下， Excel会自 动计 算出盈 亏 平衡 销 量与各个不同 销 量所对应 的收入 额 与利 润额 ，如 图 5-58所示。单 位可 变 成本 对 利 润 的影响 建立基本 计 算 表 建立 盈 亏 平衡 图 表利用模 拟 运算 计 算不同 单 位售价保本点 求解不同售价下的保本 点 建立 图标 直 观显 示盈 亏 平衡 销 售量、盈 亏 平衡 销 售 额 与 单 位售价单 位利 润 的 计 算 求解不同售价下的保本 点 不同 单 位可 变 成本、 单 位售价 对单位收入的影响专家课堂（常见问题与解答）点 拨 1： 在分析不同 单 位售价 对 利 润 的影响 时 ， 为 什么不可以直接 设 置 B5单 元格 为链 接 单 元格，而是在 C5单 元格中 输 入 单 位售价 10倍的数 值 ，并把 该单 元格 设 置 为链 接 单 元格呢？ 解答： 这 主要是因 为 在 绘 制了 滚动 条控件并 设 置控件格式 时 ，其最大 值 、最小值 、当前 值 、步 长 等 值 的取 值 范 围为 0 30000之 间 的整数， 为单 位售价要是 0.1元 为 步 长 增 长 ，必 须 在 C5单 元格中 输 入整数 值 ，且 该 整数 为 B5单 元格 值 的 10倍，在 B5单 元格中 设 置公式 为 “=C5/10”，从而控制 B5单 元格的数 值 。点 拨 2： 在 创 建 图 表的 过 程中，如果需要将默 认图 表中的数据系列更改 为 其他数 值， 该 如何操作？ 解答： 在 图 表