Excel对成本的预测与分析.doc

Excel与统计分析实例分析与设计题 目： Excel对成本的预测与分析 学 期： 学 号： 学生姓名： 指导教师： 专 业： 班 级： 完成时间： 成 绩： 目 录1成本预测21.1 历史成本分析法21.1.1 Excel对历史成本分析方法的应用31.2 因素分析法81.2.1 对因素分析方法的应用81.3比例分析法101.4利润推算法101.4.1对比例分析和利润推算方法的应用112、成本分析122.1建立成本分析表格122.2输入数据并进行审核132.3建立成本比例图143 结论14参考文献16Excel对成本的预测与分析摘 要 随着生产日益社会化和现代化，企业规模不断扩大，工艺过程愈加复杂，生产过程中某一环节或者是某一短暂时期内的生产耗费一旦失去控制，都有可能给企业造成无可挽回的经济损失。鉴于此，为了防止成本费用管理的失控现象，首先必须科学地预见生产耗费的趋势和程度，以便在此基础上采取有效措施，从而搞好成本管理工作。 在现代成本管理中。成本预测采用了一系列科学旗密的程序与方法，基本上是能够把握成本变化的规律性的，因此，成本预测的结果是比较可靠的。但是，由于根据历史资料推测未来，成本预测就 具有不可避免的局限性，这种局限性主要体现在不准确也即近似这一点上。可靠性与近似性的对立统是成本预测的显著特点。本篇论文主要是通过EXCEL 2003 进行成本的分析与预测。在对成本进行预测分析时，会涉及到Excel 2003 中的一些重要的功能以及对数据处理的技巧的应用。论文主要对于【SUM】函数，【MIN】函数，【MATCH】函数，【INDEX】函数，【INTERCEPT】函数，【SLOPE】函数的应用，以及散点图、饼图的制作。关键词：回归直线法；MATCH函数；历史成本分析法；因素分析法；成本分析表格1成本预测 随着生产日益社会化和现代化，企业规模不断扩大，工艺过程愈加复杂，生产过程中某一环节或者是某一短暂时期内的生产耗费一旦失去控制，都有可能给企业造成无可挽回的经济损失。鉴于此，为了防止成本费用管理的失控现象，首先必须科学地预见生产耗费的趋势和程度，以便在此基础上采取有效措施，从而搞好成本管理工作。在现代成本管理中。成本预测采用了一系列科学旗密的程序与方法，基本上是能够把握成本变化的规律性的，因此，成本预测的结果是比较可靠的。但是，由于根据历史资料推测未来，成本预测就 具有不可避免的局限性，这种局限性主要体现在不准确也即近似这一点上。可靠性与近似性的对立统是成本预测的显著特点。常见的成本预测的主要方法有以下几种：历史成本分析法、因素分析法、比例分析法以及利润推算法。1.1 历史成本分析法 历史成本分析法是在采用传统的完全成本计算时，根据一系列的历史成本资料来推算一定期间的固定成本和单位变动成本平均值，解决在传统成本计算下提供决策和计划成本资料的问题。历史成本分析法，包括高低点法、散布图法和回归直线法。（1） 高低点法 高低点法，是根据历史成本资料中产量最高和产量最低期成本以及相应的产量，推算单位产品的增量成本，以此作为单位变动成本，然后，根据总成本和单位变动成本来确定固定成本的一种成本分解方法。高低点法公式：固定成本产量最高期总成本单位变动成本最高产量或固定成本产量最低期总成本单位变动成本最低产量 高低点法的主要优点是简便，其明显的缺点是只利用了历史资料的两组数据，而未考虑其他数据的影响，因而，代表性差。（2） 散布图法散布图法，是根据若干期历史成本资料，绘制各期成本点散布图，按目测所得成本变动趋势画出成本直线，在图上确定直线的截距即固定成本，然后据以计算单位变动成本的一种成本分解方法。 散布图法考虑了全部已经取得的历史成本资料，在这种意义上可以认为是对只考虑两个点的高低点法的一种改进。但是，成本直线是根据目测画出的，固定成本的数额也是在图上目测出来的，因此，不可能十分精确。采用散布图法分解成本，所得结果往往因人而异，这是其主要缺点。人们使用这种方法是因为它比较直观并且容易掌握。（3） 回归直线法回归直线法，是根据一系列历史成本资料，用数学上的最小平方法的原理，计算能代表平均成本水平的直线截距和斜率，以其作为固定成本和单位变动成本的一种成本分解方法。回归直线法在理论上比较健全，计算结果精确，但是，计算过程比较烦琐。如果使用计算机的回归分析程序来计算回归系数，这个缺点则可以较好地克服。1.1.1 Excel对历史成本分析方法的应用 例：某企业2010年每个月的产品产量及生产成本的有关资料如图1.1.1所示。2011年预测产量为5000件，要求分别采用高低点法、散布图法和回归直线法预测2011年得总成本。图1.1.1 2010年得有关资料在上图所示的基础上，输入高低点法和回归直线法中的相关信息，并对它们进行格式化设置，如图1.1.2所示。图1.1.2 输入基本信息具体操作步骤如下：（1） 计算最高产量值。在单元格F2中输入格式“=MAX(B3：B14)，然后按下【Enter】键。结果如图1.1.3所示。 图1.1.3 计算出最高产量值（2） 计算最高成本值。在单元格F3中输入公式“=INDEX(C3：C14，MATCH(MAX(B3：B14)，B3：B14，0)”，按下【Enter】键。结果如图1.1.4所示。表1.1.4 计算出最高成本值（3） 计算最低产量值。在单元格F4中输入公式=MIN(B3:B14)，按下【Enter】键。结果如图1.1.5所示。图1.1.5 计算出最低产量值（4） 计算最低成本值。在单元格F5中输入公式“=INDEX(C3：C14，MATCH(MIN(B3：B14)，B3：B14,0)”，按下【Enter】键。（5） 计算高低点法中的单位变动成本。在单元格F6中输入公式“=(F3-F5)/(F2-F4)”，按下【Enter】键。（6） 计算高低点法中的预测总成本。在单元格F9中输入公式“=F7+F6*F8”，按下【Enter】键。如图1.1.6所示。图1.1.6 计算出所有结果（7） 计算回归直线法中的单位变动成本。在单元格F11中输入公式“=SLOPE(C3：C14，B3：B14)”，按下【Enter】键。（8） 计算回归直线法中的固定成本。在单元格F12中输入公式“=INTERCEPT(C3：C14，B3：B14)”，按下【Enter】键。如图1.1.7所示。图1.1.7 计算出的单位变动成本和固定成本（9） 计算回归直线法中的预测总成本。在单元格F14中输入公式“=F12+F11*F13”，按下【Enter】键。如图1.1.8所示。图1.1.8 计算出预测总成本（10） 选定区域A2:：C14，点击【插入】，【图表】，选择散点图，折线图，单击“下一步”，图表标题填“2010年产品产量和成本散点图”，单击【完成】。如图1.1.9所示。图1.1.9 2010年产品产量和成本散点图在上述的过程中用到了【MAX】【MIN】【INDEX】【MATCH】【INTERCEPT】【SLOPE】函数，对于往年的生产成本进行统计分析，对下一年的生产成本做出一个合理客观的预测。MIN函数 返回一组值中的最小值。 函数语法 MIN(number1,number2,.) Number1, number2, . 是要从中查找最小值的 1 到 255 个数字。 函数说明 参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列 表中代表数字的文本被计算在内。 如果参数为数组或引用，则只使用该数组或引用中的 数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。 如果参数中不含数字，则 函数 MIN 返回 0。如果参数为错误值或为不能转换为数字的文本，将会导致错误。 MATCH函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是 LOOKUP 函数。函数语法MATCH(lookup_value,lookup_array,match_type)Lookup_value为需要在Lookup_array中查找的数值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。Lookup_value可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。Lookup_array可能包含所要查找的数值的连续单元格区域。Lookup_array应为数组或数组引用。Match_type为数字-1、0或1。Match_type指明Excel如何在lookup_array中查找 lookup_value。如果match_type为1，函数MATCH查找小于或等于lookup_value的最大数值，Lookup_array必须按升序排列：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE；如果match_type为0，函数MATCH查找等于lookup_value的第一个数值，Lookup_array可以按任何顺序排列；如果match_type为-1，函数MATCH查找大于或等于lookup_value的最小数值，Lookup_array 必须按降序排列：TRUE、FALSE、Z-A、.、2、1、0、-1、-2、.，等等。如果省略 match_type，则假设为 1。函数说明函数MATCH返回lookup_array中目标值的位置，而不是数值本身。例如，MATCH(b,a,b,c,0) 返回 2，即“b”在数组 a,b,c 中的相应位置。查找文本值时，函数MATCH不区分大小写字母。如果函数MATCH查找不成功，则返回错误值 #N/A。如果match_type为0且lookup_value为文本，可以在lookup_value中使用通配符、问号 (?) 和星号 (*)。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符 ()。1.2 因素分析法因素分析法是在上年成本水平的基础上，分析预测期影响成本的各种因素，在按成本组成项目及各自的影响因素预测成本同上年相比的降低额和降低率的一种分析方法。其中成本项目主要包括直接材料、直接人工和制造费用，是因素分析法中主要的影响成本。（1） 直接材料直接材料是指直接构成产品实体的各种材料，其成本是由原材料的消耗定额和材料单价所决定的。计算公式为：成本降低率=1-（1-原材料消耗定额降低率）（1原材料价格升降率） 原材料成本占产品成本的百分比（2） 直接人工 直接人工是指对产品进行加工的生产工人的工资，其主要受生产工人平均工资和劳动生产率的影响，计算公式为：成本降低率=直接人工成本占产品成本的百分比（3） 制造费用 制造费用是指企业中各个生产部门为组织生产和管理生产而发生的各项间接费用。计算公式为：成本降低率=制造费用占产品成本的百分比以上三种项目的成本降低率汇总后即可得到总成本的降低率。结合预测期的产量和上年单位成本即可计算出成本降低额，即：成本降低率=预测产量上年单位成本总成本降低率1.2.1 对因素分析方法的应用 某企业2010年成本的有关资料和2011年得预测资料如图1.2.1所示，要求用预测分析法预测2011年的总成本。图1.2.1 输入有关成本的预测资料利用因素分析法计算2011年总成本的具体步骤如下。（！） 在图1.2.1所示的表格的基础上输入因素分析法中各个项目相关的数据信息，并对输入的信息进行格式化设置，如图1.2.2所示。图1.2.2 输入相关的项目信息（2） 计算直接材料的成本降低率。在单元格B12中输入公式“=(1-(1-D6)*(1+D7)*B6”，按下【Enter】键。（3） 计算直接人工的成本降低率。在单元格B13中输入公式“=(1-(1+D5)/(1+D4)*B7”，按下【Enter】键。（4） 计算制造费用的成本降低率。在单元格B14中输入公式“=(1-(1+D8)/(1+D3)*B8”，按下【Enter】键。（5） 计算产品成本总降低率。在单元格B15中输入公式“=SUM(B12：B14)”，按下【Enter】键。其结果如图1.2.3所示。图1.2.3计算出上述4种结果（6） 计算预测产量按上年单位成本计算的总成本。在单元格D11中输入公式“=B2*D2，按下【Enter】键。（7） 计算产品成本总降低额。在单元格D13中输入公式“=B15*D11，按下【Enter】键。（8） 计算2011年预测总成本。在单元格D15中输入公式“=D11-D13，按下【Enter】键。其结果如图1.2.4所示。图1.2.4 计算出2011年预测总成本1.3比例分析法比例分析法是根据生产消耗和企业主要经营成果指标之间的关系来预测成本的一种方法，其计算公式为：预测成本=预测产值或者：预测成本=预测销售收入 =预测销售利润1.4利润推算法 利润推算法是指在成品销售数量和价格一定的条件下，根据企业确定的目标利润推算出控制成本的一种方法，起计算公式为：产品总成本=单价产品单位成本=单价1.4.1对比例分析和利润推算方法的应用例如某企业2010年产品的有关资料和2011年得预测资料如图1.4.1所示，要求采用比例分析法和利润推算法预测2011年预测总成本及单位成本。图1.4.1 2010年产品资料及2011年预测资料采用比例分析法和利润推算法计算2011年预测总成本及单位成本的具体步骤如下。（1） 在图1.4.1的基础上输入相关的计算项目，并对它们进行格式化设置，如图1.4.2所示。图1.4.2输入相关的计算项目（2） 计算利润推算法下的预测总成本。在单元格B9中输入公式“=C2*C3*(1-C5)-C6”，按下【Enter】键。（3） 计算比例分析法下的单位成本。在单元格B10中输入公式“=C3*(1-C5)-(C6/C2)”，按下【Enter】键。（4） 计算比例分析法下的预测总成本。在单元格C9中输入公式“=(C2*C3)*(B4*B2)/(B2*B3)”，按下【Enter】键。结果如图1.4.3所示。图1.4.3计算出比例分析法下的预测总成本2、成本分析 成本分析是利用成本核算及其他有关资料，分析成本水平与构成的变动情况，研究影响成本升降的各种因素及其变动原因，寻找降低成本的途径的分析方法。成本分析是成本管理的重要组成部分，其作用是正确评价企业成本计划的执行结果，揭示成本升降变动的原因，为编制成本计划和制定经营决策提供重要依据。 对于生产和销售为主的企业，成本的分析能够帮助企业的决策者适当的调整经营策略，做出相应的经营决策。如果不对成本进行研究分析，可能会由于生产成本的过高而导致企业利润过低，甚至是企业处于亏损状态。如图2.1.1所示的是生产成本分析模型。图2.1.1成本分析模型2.1建立成本分析表格 所有的生产成本基本表格的内容包括标题、编制时间、各成本项目、上年实际、本月实际和本年累计实际等项目。建立成本分析 表格的具体步骤如下;新建一个工作簿并将其命名为“成本分析”,双击工作表Sheet1并重新命名为“生产成本分析”，然后输入各个成本项目并进行格式化，如图2.1.2所示。图2.1.2 建立成本分析表格2.2输入数据并进行审核 当工作表中的数据输入一段时间后，用户在使用时可能会对其中单元格之间的引用关系比较模糊，此时可以用到Excel 2003中的【公式审核】功能找到单元格之间的引用关系。 对生产成本分析表中的数据进行填充的具体步骤如下。（1） 在图2.1.2所示的工作表输入各个成本项目发生的数据，如图2.2.1所示。图2.2.1 输入各个成本项目发生额（2） 计算生产费用合计数。在单元格C9中输入公式“=SUM(C6：C8，C4)”，按下【Enter】键。，然后将此公式填充的单元格区域“D9：E9”中。（3） 计算产品生产成本合计数。在单元格C12中输入公式“=C9+C10-C11”，按下【Enter】键。，然后将此公式填充的单元格区域“D12：E12”中。（4） 计算产品总成本数。在单元格C13中输入公式“=C12”