EXCEL财务教程-采购成本分析表(共5页).doc

精选优质文档-倾情为你奉上EXCEL财务教程-采购成本分析表材料的成本是生产成本的重要组成部分，而材料的成本除了价格因素外，还有一项很重要的因素，就是采购成本。采购成本通常由两项组成，一是采购环节发生的费用；二是材料存储时的发生的费用。这是两个互相制约的因素，每批采购量大，采购次数少，可以减少年采购成本，但是存储费用的增加；反之亦然。 因此确定采购量和存储量之间的关系也是一项很重要的工作，通过“采购成本分析”可以帮助企业设置科学合理的采购量和采购次数，从而为降低企业采购成本提供可靠依据。 我们先创建采购成本和存储成本在不同批次下的数据表，再利用公式计算最小成本、采购批次和采购量。然后添加年采购量、年采购成本和单位存储成本滚动条，最后制作存储成本和采购成本的散点图。首先，我们还是要来看一下本节要使用的公式： 采购当量年采购量/年采购批次平均存量采购数量/2存储成本平均存量×单位存储成本采购成本年采购批次×采购成本总成本存储成本采购成本 步骤01 新建工作表 新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的字段标题、数据，并设置格式等。 步骤02 输入公式 在B2到F2依次输入下列公式： =$B$19/A2 =B2/2 =C2*$I$19 =A2*$E$19 =D2+E2 步骤03 复制公式 选中B2:F2，移动光标到F2右下角，双击即可复制上述公式至B3:F13区域。 步骤04 最低采购成本公式 在B16单元格输入公式：=MIN(F2:F13)。 步骤05 采购批次公式 在E16单元格输入公式：=INDEX(A2:A13,MATCH(B16,F2:F13,0)。 步骤06 采购量公式 在I16单元格输入公式：=INDEX(B2:B13,MATCH(B16,F2:F13,0)。关于INDEX函数的使用我们在前面的相关章节已经介绍过。 知识点： 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中使用通配符、问号(?)和星号(*)。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符()。 步骤07 添加年采购量滚动条 我们前面已经在自定义快速访问工具栏上添加了滚动条按钮，现在我们又要用到它。点击这个按钮，然后在A21单元格拖出一个滚动条。 步骤08 设置年采购量滚动条 在刚刚添加的滚动条上单击鼠标右键，在弹出的对话框中对滚动条进行如图中所示的设置。与这个滚动条相关联的单元格是B19，设置方法是单击文本框后面的编辑按钮，再用鼠标选中B19单元格。单击“确定”完成设置。 步骤09 添加并设置采购成本滚动条 使用与上两步相机的方法在D21单元格添加滚动条，并对其进行设置，设置内容如图所示。 步骤10 添加并设置单位存储成本滚动条 同理，在H21单元格添加滚动条，并对其进行设置，设置内容如图所示。 步骤11 完成设置 通过以上几个步骤的设置，我们已经得到一个具有数据调节功能的采购成本分析表。专心-专注-专业