建立企业盈亏平衡分析模型(量本利分析模型)-EXCEL动态图表-折线散点图.xls

销售单价100销售量边际贡献固定成本利润 单位可变成本 350052000 52000 直接人工101500975005200045500 直接材料15 可变制造费10 盈亏点销量垂直参考线 单位边际贡献65800150000 固定成本 5200080052000 管理人员工资200008000 资产折旧10000800 50000 固定销售费用10000 当前销量垂直参考线 销量10001000150000 边际贡献65000100065000 销售收入100000100052000 总成本87000100013000 利润130001000 50000 售价 100元 盈亏平衡销量 800 盈亏平衡销量800 销量 1000时 盈利 EXCELEXCEL梦梦想想之之家家 转转载载请请保保留留出出处处 52000 13000 盈盈 亏亏 平平 衡衡 分分 析析 1400120010008006004002000 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 边际贡献固定成本利润 Series4Series5 售价 100元 盈亏平衡销量 800销量 1000时 盈利 52000 13000 盈盈 亏亏 平平 衡衡 分分 析析 1400120010008006004002000 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 边际贡献固定成本利润 Series4Series5 售价 100元 盈亏平衡销量 800销量 1000时 盈利 EXCELEXCEL梦梦想想之之家家 转转载载请请保保留留出出处处 假设有一个制衣厂 生产的裤子单位售价100元 其中生产一件产品的人工费10元 材料费15元 制造过程中所耗费的水电费10元 当然可能会有其他随产量变动的制造费用 此处暂且不计 可做表如下 其中 单位可变成本 即为 直接人工 直接材料 可变制造费 相加之和 上表B2 B3 B4 B5 请自己加公式 B7解释一下 B7等于销售单介B1减单位可变成本B2 这个值在会计上专用名词叫单元边际贡献 意思说卖出去一件产品对利润所做的贡 献 注意 单位 两个字 是销售一件产品贡献 在实际企业生产过程中 还有部分费用是不随产量变动而变动的 我们称之为固定费用 象机器设备的折旧 管理层人员的工资 销售过程中的广告费等等 它们是不随销量 变动 意思是说卖不卖东西 这部分费用依然产生 我们暂且估计数据如下 本故事纯属虚构 如有雷同 纯属巧合 1400 上表B8 B9 B10 B11 假设销量1000条 继续做表 上表 B14 B13 B7 B15 B13 B1 B16 B8 B13 B2 解释一下B19盈亏平衡销量 当企业有销量时 单位边际贡献随之增加 只有所有售出产品的边际贡献总值超过企业的固定成本时 企业才会实现利润 在利润为零那一时刻 盈亏实现平衡 边际贡献总值等于固定成本 因为边际贡献总值 单位边际贡献乘 X 销量 所以盈亏平衡时的销量 固定成本 单位边际贡献 即B19 B8 B7 以下内容为EXCEL盈亏损模型所需要的建的辅助区域 至于为什么要建这个区域 等做好之后您再慢慢体会 继续做表 其中 C1到F1标题手工填入 C2销量输入0 C2为0 D2肯定也为0 E2输入公式 B8 F2输入公式 D2 E2 C3输入1500 假设这是企业最大销量 闲少 那您填多大我都不拦着 D3输入公式 C3 B7 E3输入公式 B8 F3输入公式 D3 E3 再加两条参考线 其中 C7到C10的公式都是 B19 D8公式 B8 C13到C17的公式都是 B13 D14公式 B14 D15公式 B8 D16公式 B17 其他位置的数字直接手工输入 至于为什么输入这些数字 下边解释 至于为什么要做参考线也下边解释 现在只需要明白一点 拿 盈亏点销量垂直参考线 来说 如果把C7到C10的值放图表上的X轴 D7到D10为Y轴的话 因为X值相同 这些实际上是垂直于X轴方向上的一条直线上的四个点 当前销量垂直参考线 中的数据就是垂直于X轴的直线上的五个点 明白这些就足够了 下面开始做图表 1 选择C1到F3 然后按工具栏上的 图表向导 选择XY散点图 子图表类型选择 无数据点折线散点图 然后按一步 2 系列产生在处选择 列 然后按完成 3 完成之后 如下图所示 蓝色文字指出图表上各部分名字 下文会用到这些地方 别找不到 4 简单修饰一下 a 选中图例 鼠标右键 图例格式 位置 底部 确定 b 选中绘图区 鼠标右键 绘图区格式 区域 处选择 白色 确定 c 选中绘图区 再选中 y轴主格线 鼠标右键 网格线格式 颜色处 选择 浅灰色 确定 d 选中 图表上的 黄色 利润 系列线型 鼠标右键 数据系列格式 左边 线型 下的颜色选择 黄色 粗细处选择倒数第二粗的线粗 同理按上述方法 将 边际贡献 系列改成蓝色 加粗线 将 固定成本 系列改成桔红色 加粗线型 e 选中x坐标轴 鼠标右键 坐标轴格式 刻度 最小值输入0 最大值输入1500 确定 同样方法 将y轴刻度最小值设成 50000 最大值设成1500000 f 再分别选择中 图例 x轴 y轴 在它们鼠标右键的格式设定中的字体选项里边 将它们的字体全设成11号字体 请看下图是完成后样子 如果您的不是这样 请按上边步骤重新改过 60000 40000 20000 0 20000 40000 60000 80000 100000 120000 0500100015002000 边际贡献 固定成本 利润 图表区 绘图区 图例 y x x轴轴 y y轴轴主主格格线线 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 边际贡献固定成本利润 5 选中绘图区 鼠标右键 数据源 在 系列 页上 按 添加 按钮 出现 系列4 如下图 6 在x值处 去选择表格上的C7到C10这个区域 在y值处 先删除里边内容中 再去选择表格上的D7到D10这个区域 确定之后图表应该是下同这个样子 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 边际贡献固定成本利润系列4 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 边际贡献固定成本利润 7 按上述方法再添加一个系列 系列5 x值选择C13到C17 y值处先删掉里边内容 再选择 D13到D17 8 完成后 如下图所示 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 边际贡献固定成本利润 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 边际贡献固定成本利润系列4 9 在绘图区选中 系列4 鼠标右键 数据系列格式 左侧 线形 处颜色 选紫色 粗细选倒数第二粗 右侧 数据标记 处 前景色选红色 背景色选白色 大小设成 5磅 确定 10 在绘图区选中 系列5 鼠标右键 数据系列格式 左侧 线形 处颜色 选天蓝色 粗细选倒数第二粗 右侧 数据标记 处 前景色选红色 背景色选白色 大小设成 5磅 确定 11 完成效果如下图 50000 30000 10000 10000 30000 50000 70000 90000 110000 130000 150000 0200400600800100012001400 边际贡献固定成本利润 12 分别清除系列4和系列5的最上边和最上边的数据点格式 操作如下 a 先鼠标左键选中系列4 再鼠标左键单击系列4最上边的数据点 选中这个数据点 切记不是双击那个点 是两次单击才可选中 选中之后 按鼠标右键会出现 数据点格式 b 在右侧的 数据标记 处选 无 确定 重复以上操作 将系列4最下边数据点 以及系列5最上和最下数据点格式去除掉 完成后效果如下 13 关键数据点加 数据标志 选中系列4 再选中系列4与固定费用和边际贡献交叉的那个点 鼠标右键 数据点格式 数据标志 选中Y值 确定 同样操作 选中系列5 再选中系列5与利润交叉的那个点 加数据标志 Y值 确定后 效果如下图所示 啥 跟我的不一样 字体大 没边框 您自己在这个数据标志的右键格式设定里改改嘛 到此为一个静态的盈亏模型就建好了 虽然叫静态的 但如果您去修改表格上的数值 如价格 销量 固定成本 这个模型就随之变化了 有没有更好的办法 不直接去修改表值 能不能在图表这里直接变动价格 变动销量来实现动态的盈亏分析呢 答案是能的 下面我们就在此图的基础之上 来做一个动态图表 这里我们需要用到 窗体 工具栏上的微调按钮 如图 如果你的工具栏上没有窗体工具栏 请去视图菜单下的工具栏里钩选窗体工具栏 14 鼠标单击窗体工具栏上的微调按钮 然后在表格上拖拽出这个按钮 然后在其上按鼠标右键 选择 设置控件格式 当前值输入100 最小值输入50 最大值300 步长5 单元格链接处 选择B1 确定 我的目的是用这个按钮控制售价变化 15 再次鼠标单击窗体工具栏上的微调按钮 然后在表格上拖拽出第二个按钮 然后在其上按鼠标右键 选择 设置控件格式 当前值输入1000 最小值输入0 最大值1500 步长50 单元格链接处 选择B13 确定 我的目的是用这个按钮控制销量变化 完成之后 当单击这两个按钮的向上或向下键头的时候 你会发现图表是动态的了 注 如果你的按钮上没有 控制 这个选项 请你注意一下我用的窗体工具栏上的微调按钮 不是控制工具栏的 这两个有区别的 16 给这两个按钮添加一些文字说明 a 在单元格C18中输入公式 售价 B1 元 盈亏平衡销量 ROUND B19 1 b 在单元格C19中输入公式 销量 B13 时 IF B17 0 盈利 IF B17 0 保本 亏损 c 在绘图工具栏上 选择矩形框 在图表上边拉出一个条形矩形框 然后选中这个矩形框 上边公式栏中输入 C18 回车 完成之后矩形框上就会出现C18里的文字 如图所示 注 绘图工具栏默认在EXCEL窗口的下边 如果你找不到请去视图菜单下的工具栏里将其钩选 c 再建一个矩形框 选中这个矩形框 上边公式栏中输入 C19 回车 完成之后矩形框上就会出现C19里的文字 如图所示 17 最后一步 给图表区右键的表选项里给表加上标题 点编辑菜单下的 定位 定位条件 选 对象 确定 在选中图形上按鼠标右键 组合 组合 使EXCEL图表 两个按钮 两个矩形框组合到一起 看一下成果吧 分析 由以上图形可以看出 1 当销量增加 产品的边际贡献逐渐增加 利润的负值 亏损 逐渐减少 2 当边际贡献数值增长到与固定费用持平时 这时利润刚好为零 此时即所谓的盈亏平衡 3 当边际贡献再继续增加时 这时企业已开始盈利了 假设有一个制衣厂 生产的裤子单位售价100元 其中生产一件产品的人工费10元 材料费15元 制造过程中所耗费的水电费10元 当然可能会有其他随产量变动的制造费用 此处暂且不计 上表B2 B3 B4 B5 请自己加公式 B7解释一下 B7等于销售单介B1减单位可变成本B2 这个值在会计上专用名词叫单元边际贡献 意思说卖出去一件产品对利润所做的贡 献 注意 单位 两个字 是销售一件产品贡献 在实际企业生产过程中 还有部分费用是不随产量变动而变动的 我们称之为固定费用 象机器设备的折旧 管理层人员的工资 销售过程中的广告费等等 它们是不随销量 变动 意思是说卖不卖东西 这部分费用依然产生 当企业有销量时 单位边际贡献随之增加 只有所有售出产品的边际贡献总值超过企业的固定成本时 企业才会实现利润 在利润为零那一时刻 盈亏实现平衡 边际贡献总值等于固定成本 因为边际贡献总值 单位边际贡献乘 X 销量 所以盈亏平衡时的销量 固定成本 单位边际贡献 即B19 B8 B7 以下内容为EXCEL盈亏损模型所需要的建的辅助区域 至于为什么要建这个区域 等做好之后您再慢慢体会 继续做表 其中 C1到F1标题手工填入 C2销量输入0 C2为0 D2肯定也为0 E2输入公式 B8 F2输入公式 D2 E2 C3输入1500 假设这是企业最大销量 闲少 那您填多大我都不拦着 D3输入公式 C3 B7 E3输入公式 B8 F3输入公式 D3 E3 其中 C7到C10的公式都是 B19 D8公式 B8 C13到C17的公式都是 B13 D14公式 B14 D15公式 B8 D16公式 B17 其他位置的数字直接手工输入 至于为什么输入这些数字 下边解释 至于为什么要做参考线也下边解释 现在只需要明白一点 拿 盈亏点销量垂直参考线 来说 如果把C7到C10的值放图表上的X轴 D7到D10为Y轴的话 因为X值相同 这些实际上是垂直于X轴方向上的一条直线上的四个点 当前销量垂直参考线 中的数据就是垂直于X轴的直线上的五个点 明白这些就足够了 下面开始做图表 1 选择C1到F3 然后按工具栏上的 图表向导 选择XY散点图 子图表类型选择 无数据点折线散点图 然后按一步 c 选中绘图区 再选中 y轴主格线 鼠标右键 网格线格式 颜色处 选择 浅灰色 确定 d 选中 图表上的 黄色 利润 系列线型 鼠标右键 数据系列格式 左边 线型 下的颜色选择 黄色 粗细处选择倒数第二粗的线粗 同理按上述方法 将 边际贡献 系列改成蓝色 加粗线 将 固定成本 系列改成桔红色 加粗线型 e 选中x坐标轴 鼠标右键 坐标轴格式 刻度 最小值输入0 最大值输入1500 确定 f 再分别选择中 图例 x轴 y轴 在它们鼠标右键的格式设定中的字体选项里边 将它们的字体全设成11号字体 图表区 绘图区 图例 y x x轴轴 y y轴轴主主格格线线 5 选中绘图区 鼠标右键 数据源 在 系列 页上 按 添加 按钮 出现 系列4 如下图 6 在x值处 去选择表格上的C7到C10这个区域 在y值处 先删除里边内容中 再去选择表格上的D7到D10这个区域 确定之后图表应该是下同这个样子 7 按上述方法再添加一个系列 系列5 x值选择C13到C17 y值处先删掉里边内容 再选择 D13到D17 9 在绘图区选中 系列4 鼠标右键 数据系列格式 左侧 线形 处颜色 选紫色 粗细选倒数第二粗 右侧 数据标记 处 前景色选红色 背景色选白色 大小设成 5磅 确定 10 在绘图区选中 系列5 鼠标右键 数据系列格式 左侧 线形 处颜色 选天蓝色 粗细选倒数第二粗 右侧 数据标记 处 前景色选红色 背景色选白色 大小设成 5磅 确定 a 先鼠标左键选中系列4 再鼠标左键单击系列4最上边的数据点 选中这个数据点 切记不是双击那个点 是两次单击才可选中 选中之后 按鼠标右键会出现 数据点格式 重复以上操作 将系列4最下边数据点 以及系列5最上和最下数据点格式去除掉 完成后效果如下 13 关键数据点加 数据标志 选中系列4 再选中系列4与固定费用和边际贡献交叉的那个点 鼠标右键 数据点格式 数据标志 选中Y值 确定 同样操作 选中系列5 再选中系列5与利润交叉的