基于Excel多品种条件下本量利分析模型设计.docVIP

基于Excel多品种条件下本量利分析模型设计 　　摘要：本量利分析以数量化的会计模型或图式揭示企业的固定成本、变动成本、单价、销售量、销售额、利润等变量之间的关系。文章运用Excel设计多品种本量利分析模型，通过建立基础资料区、设置微调器、制作面积折线图求解，使管理层直观地了解企业的盈利情况，为预测、决策、规划、控制提供必要的财务信息，帮助管理层在产销规模、成本控制和产品结构等方面做出决策。 　　关键词：Excel；本量利分析；保本 　　本量利分析，是指研究成本―业务量―利润关系的一种数学分析方法。在现实的生产经营活动中，大部分企业的生产活动是复杂的，一般都是进行多品种生产经营，这就涉及多品种本量利分析。企业在生产、销售多种产品的情况下，运用Excel建立分析模型，借助微调器及面积折线图等功能，将数据变得动态可调，有利于提高企业管理层决策的效率。 　　一、基本分析思路 　　在多品种的条件下，只能用销售额来表示企业的盈亏临界点。通常计算多品种企业盈亏临界点的方法有综合边际贡献率法、联合单位法、分算法等几种方法。本文主要介绍综合边际贡献率法。综合边际贡献率法是假设产品结构不变的情况下，通过计算多品种的综合边际贡献率，从而确定企业综合保本点和每种产品的保本点的方法。 　　保本点也称盈亏临界点，是指企业达到收支平衡实现保本的销售水平。盈亏临界点分析是确定企业经营安全程度和进行保利分析的基础。多品种的盈亏临界点分析关键是企业综合保本销售额的确定。具体来说， 　　企业综合保本销售额= 　　综合边际贡献率法下保本点的具体计算步骤如下： 　　第一步，计算综合边际贡献率 　　综合边际贡献率=∑（各?N产品的边际贡献率×该种产品的销售额比重） 　　第二步，计算企业综合保本销售额 　　企业综合保本销售额= 　　第三步，计算各种产品保本销售额 　　某种产品保本销售额=企业综合保本销售额×该种产品的销售额比重 　　二、模型设计与案例分析 　　（一）案例背景 　　以某公司2016年生产资料为例，构建本量利模型进行分析，主要分析该公司三种主要产品在盈亏平衡点时的销量和销售收入。建立多品种分析模型需要了解产品的销量、单价、单位变动成本、固定成本、边际贡献率、销售比重、综合保本销售额等。三种产品的基础资料如表1所示。 　　（二）模型设计 　　1. 建立基础资料区 　　第一步：打开Excel2010工作表，将案例中的基础资料输入。 　　第二步：设置计算公式。 　　（1）单价：在B5单元格中输入“=6100*H5”；C5单元格输入“=5500*H5”；D5单元格输入“=6050*H5”。 　　（2）单位变动成本：在B6单元格中输入“=4180.95*H7”；C6单元格输入“=4278.43*H7”；D6单元格输入“=2383.61*H7。 　　（3）固定成本：在E7单元格中输入“H9”。 　　（以上公式中的H5、H7、H9涉及下文微调器的使用）。 　　（4）单位边际贡献=单价―单位变动成本，在B8单元格中输入“=B5-B6”，单击公式栏中的“√”确定，即可得出计算结果。C8、D8单元格可以通过公式填充工具复制B8单元格来计算。 　　（5）边际贡献率= ，在B9单元格中输入“=B8/B5”，单击公式栏中的“√”确定，即可得出计算结果。C9、D9单元格可以通过公式填充工具复制B9单元格来计算。 　　（6）边际贡献总额=销量×单位边际贡献，在B10单元格中输入“=B4*B8”，单击公式栏中的“√”确定，即可得出计算结果。C10、D10单元格可以通过公式填充工具复制B10单元格来计算。 　　（7）销售收入=销量×单价，在B11单元格中输入“=B4*B5”，单击公式栏中的“√”确定，即可得出计算结果。C11、D11单元格可以通过公式填充工具复制B11单元格来计算。E11单元格可以选定E11，然后单击选项卡中的“公式”，再单击“自动求和”，即可得出计算结果。 　　（8）销售比重= ，在B12单元格中输入“=B11/$E$11”，单击公式栏中的“√”确定，即可得出计算结果。C12、D12单元格可以通过公式填充工具复制B12单元格来计算。 　　（9）加权边际贡献率=边际贡献率×销售比重，在B13单元格中输入“=B9*B12”，单击公式栏中的“√”确定，即可得出计算结果。C13、D13单元格可以通过公式填充工具复制B13单元格来计算。E13单元格可以选定E13，然后单击选项卡中的“公式”，再单击“自动求和”，即可得出计算结果。 　　（10）保本销量= ，在B14单元格中输入“=B15/B5”，单击公式栏中的“√”确定，即可得出计算结果。C14、D14单元格可以通过公式填充工具复制B14单元格来计算