管理科学盈亏平分析.docVIP

实　　验　　报　　告 学　院：　　 工商管理 　 　 系　别：　　　市场营销 专　业：　　　市场营销 班　级：　　　 姓　名：　　　 学 号：　　　 实验题目：　　　　　　盈亏平衡分析 　　　　　　 实验类型（演示□验证□设计□其它） 实验日期：2013年 9 月 5 日 实验目的： 以Microsofe Excel为背景，能够运用Spreadsheet描述和解决盈亏平衡问题。 实验原理： 利用Excel解决盈亏平衡问题。 实验仪器： 每人1台计算机，并安装Microsofe Excel中的数据分析、规划求解。 实验方法与步骤： 第一步：在Spreadsheet上进行问题的描述。 用地址为B2、B3、B4的单元格分别表示固定成本、边际成本和单价，在这些表格中分别输入已知数据。见表1.1。 第二步：在Spreadsheet中建立模型。 可在单元格A6处键入“模型”两个字，以表示以下为模型。用单元格B8表示产品产量，它是一个由待遇确定的决策变量。由于总成本、总收益与总利润与决策变量有关，所以可讲单元格B8用黄色底纹以表示该决策变量的重要性。单元格B9、B10、B11分别表示总成本、总收益与总利润。总成本（单元格B9）等于年固定成本与年可变成本之和，其中年边际成本等于单位产品成本与产品的产量之积，所以在单元格B9中输入下述公式： =B2+B3*B8 总收益（单元格B10）等于产品单价与产品产量之积，在单元格B10中输入下述公式： =B4*B8 总利润（B11）等于总收益与总成本之差，在单元格B11中输入公式： =B10-B9 运用上述模型即可计算出不同产品产量下的盈亏情况。例如：当产品的产量为1000个时，可在单元格B8中输入1000，即可得到此时的总成本、总收益与总利润分别为50000元，20000元与-30000元，如表1.1所示。 第三步：确定盈亏平衡点。 盈亏平衡点是总成本等于总收益的点，或总利润等于零的点。前面已经算出，当产量为1000个时，总利润为-30000元，所以该点不是盈亏平衡点。可在单元格B8中继续输入其他产量值进行试算，直到总利润为零。 表1.1 盈亏平衡分析模型 下面介绍两种使用Excel中的命令迅速求出盈亏平衡点产量的方法。 方法一：数据表命令方法。 第一步：确定输入的决策变量值得范围与计算步长。 前面已计算出，当产量为1000个时，总利润为负值，即亏损；在表1.1的模型中，若在单元格B8中试输入16000，得到总利润为正值，即盈利。因此，在产量为1000与16000之间，必有一个值使得总利润为零，这个值即为盈亏平衡点的产量。因此，可将输入范围定位[1400，2400]，假设计算步长为1000。 第二步：在单元格F7：F23中分别输入从1000至1600、步长为1000的产量值。 第三步：在单元格G7中输入计算总利润的公式，即：=B11。如表1.1所示。 第四步：用Excel中的数据表命令计算不同产量下的利润值。 ①用鼠标选择单元格F7：给G22的区域； ②在Excel工作表的菜单栏中，选择“数据（data）”，如图1.1所示； 图1.1 创建模拟运算表 ③选择“模拟运算表（Table）”，如图1.1所示； ④出现模拟运算表对话框，在“输入引用列的单元格”一栏中输入“B8”，B8是表示产量的单元格，这表示模拟运算表要计算的是不同产量下的利润，见表1.1中的模拟运算对话框； ⑤选择“确定”。 这时，表内将出现不同产量所对应的利润值，见表1.1。从表中数据可见，当产量为4000个时，总利润为零，即盈亏平衡点的产量为4000个。 方法二：单变量求解命令方法 第一步：在Excel的菜单栏中选择“工具（Tools）”，如图1.2所示； 图1.2 创建单变量求解表 第二步：选择“单变量求解（Goal Seek）”，如图1.2所示； 表1.2 使用单变量求解命令确定盈亏平衡点的产量 第三步：这时，出现“单变量求解”对话框。在“目标单元格”一栏中输入地址“B11”（总利润值），在“目标值”一栏中输入“0”（表示总利润为零），在“可变单元格”一栏中输入地址“B8”（表示产量）。见表1.2中的单变量求解对话框。该对话框的输入表明，下面要寻找的是当总利润为零时对应的产量值。选择“确定”。 这时，出现“单变量求解状态”表，见表1.2中单变量求解状态表。它表示已经求得了一个解。选择“确定”。这时，