动漫插图手绘图片:财务人员实战Excel之11--------成本分析

财务人员实战Excel之11--------成本分析 (未完，接下章)

第1节、采购成本分析表

第2节、采购成本变动分析

第3节、材料成本汇总表

第4节、企业成本|分析表|分解表

第11章　Excel 成本分析

第1节、采购成本分析表

　　 材料的成本是生产成本的重要组成部分，而材料的成本除了价格因素外，还有一项很重要的因素，就是采购成本。采购成本通常由两项组成，一是采购环节发生的费用；二是材料存储时的发生的费用。这是两个互相制约的因素，每批采购量大，采购次数少，可以减少年采购成本，但是存储费用的增加；反之亦然。
　　因此确定采购量和存储量之间的关系也是一项很重要的工作，通过“采购成本分析”可以帮助企业设置科学合理的采购量和采购次数，从而为降低企业采购成本提供可靠依据。

　　采购成本分析表效果图

我们先创建采购成本和存储成本在不同批次下的数据表，再利用公式计算最小成本、采购批次和采购量。然后添加年采购量、年采购成本和单位存储成本滚动条，最后制作存储成本和采购成本的散点图。首先，我们还是要来看一下本节要使用的公式：
　　采购当量＝年采购量/年采购批次平均存量＝采购数量/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 完成设置
　　 通过以上几个步骤的设置，我们已经得到一个具有数据调节功能的采购成本分析表。 

第2节、采购成本变动分析

步骤01 采购量的变动影响分析
    拖动滚动条，增大年采购量，存储成本增大而采购成本未变，因此折线图上的存储成本线发生变动而采购成本线保持不变。
    存储成本增大，造成总成本增大，所以最低采购成本、采购批次、采购量也相应增大。

步骤02 采购成本的变动影响分析
    拖动滚动条，增大采购成本，采购成本增大而存储成本未变，因此折线图上的采购成本线发生变动而存储成本线保持不变。采购成本增大，造成总成本增大，所以最低采购成本、采购量也相应增大，采购批次则减少。

步骤03 单位存储成本的变动影响分析

    拖动滚动条，增大单位存储成本，采购成本未变，因此折线图上的存储成本线发生变动而采购成本线保持不变。采购成本增大，造成总成本增大，所以最低采购成本、采购批次也相应增大，采购量则减少。

第3节、材料成本汇总表

知识点：

数据透视表的数据源使用数据透视表可以汇总、分析、浏览和提供摘要数］使用数据透视图可以在数据透视表中可视化此摘要数据，并且可以方便地查看比较、模式和趋势。数据透视表和数据透视图都能使您做出有关企业中关键数据的决策。在创建数据透视表时，可使用多种源数据类型。
　　使用工作表数据可以将Excel工作表中的数据作为报表的数据来源。数据应为列表格式，第一行包含列标签，其余行包含相同列中的类似项，并且数据区域中没有空白的行或列。Excel将列标签作为报表的字段名称。
　　使用外部数据源要汇总和分析Excel的外部数据，则可从包括数据库、OLAP多维数据集和文本文件的外部数据源上检索数据。
　　使用其他数据透视表每次在新建数据透视表或数据透视图时，Excel均将报表数据的副本存储在内存中，并将其保存为工作簿文件的一部分。这样每张新的报表均需要额外的内存和磁盘空间。
　　但是，如果将现有数据透视表作为同一个工作簿中的新报表的源数据，则两张报表就可以共享同一个数据副本。因为可以重新使用存储区，所以就会缩小工作簿文件，减少内存中的数据。
　　如果要将某个数据透视表用作其他报表的源数据，则两个报表必须位于同一工作簿中。如果源数据透视表位于另一工作簿中，则需要将源报表复制到要新建报表的工作簿位置。不同工作簿中的数据透视表和数据透视图是独立的，它们在内存和工作簿文件中都有各自的数据副本。
　　在刷新新报表中的数据时，Excel也会更新源报表中的数据，反之亦然。如果对某个报表中的项进行分组或取消分组，那么也将同时影响两个报表。如果在某个报表中创建了计算字段或计算项，则也将同时影响两个报表。
　　更改现有报表的源数据更改源数据将导致用于分析的数据也发生变化。例如，您可能希望方便地从测试数据库切换到生产数据库。可以通过刷新报表，使用与原始数据连接信息类似的新数据来更新数据透视表。

　　在材料消耗环节既要统计同一产品不同材料的消耗量，又要计算不同产品对同一材料的消耗量。如何利用材料消耗明细账快速准确地制作出两个统计口径下的材料消耗汇总表是很重要的工作。

材料成本汇总表效果图

步骤01 新建工作表
　　新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的字段标题、数据，并设置格式等。 

步骤02 生成材料金额
　　在D3输入公式：=INT(RAND()*1000)，并复制公式到D4:D32区域。 

知识点：RAND函数
　　D函数返回大于等于0及小于1的均匀分布随机实数，每次计算工作表时都将返回一个新的随机实数。
函数语法
RAND()
函数说明
　　如果要使用函数RAND生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按F9，将公式永久性地改为随机数。

　　在本例中，使用公式：=INT(RAND()*1000)，即是生成一个大于0及小于1000的整数。
　　如果要生成一个规定范围内的随机数，可以使用公式：=RAND()*(上限-下限)+下限

 步骤03 开始生成数据透视表
     选择B2:D32中的任意单元格，然后在“插入”选项卡中选择“数据透视表”里的“数据透视表”，开始生成数据透视表。

步骤04 使用向导进行设置
    这时会弹出一个对话框，本例中不必改动任何选项。在本例中数据来源就是我们的工作表，而数据透视表生成于新的工作表中。 

步骤05 设置布局
　　在这一步我们要在新生成的工作表中设置数据透视表的布局形式，比较简单的方式就是直接在窗口中拖动字段名称到相应的区域。 

步骤06 生成数据透视表
　　通过上面的一系列设置，我们得到一张数据透视表，表中显示了材料和产品的汇总数据。 

步骤07 增加数据透视图
　　有了数据透视表，但我们希望更加直观地查看数据，所以我们可以增加一张数据透视表，方法跟之前生成柱形图的方法是一样的。 

步骤08 生成数据透视图
　　通过上面一个简单的步骤，我们就得到了一张数据透视图，不过我们还需要对图形做一些细节的调整。 

步骤09 设置数据标签
　　现在我们要对柱形图的数据标签进行设置，以方便查看。方法前面已经讲过，在“布局”选项卡中选择“数据标签”下的“其他数据标签选项”。 

步骤10 显示系列名称
　　在弹出的对话框中，我们选择显示系列名称，这样柱形图上就会直观地显示材料的类型。

步骤11 完成设置
　　做完以上的步骤，我们就得到了最终的结果。实际上，本节的例子除了使用数据透视表，我们也可以使用公式来得到相同的结果，这里我们就不再赘述了，有兴趣的朋友可以自己研究一下。 

第4节、企业成本|分析表|分解表

　　成本分析中有很多常用的表，本章将会看到其中一些。上一章我们讲了本量利分析，本量利分析要求将企业成本划分为变动和固定成本，在成本项目中很多项目既有固定成分，又有变动成分，所以需要将其分解。

材料成本汇总表效果图

 
采购成本分析表效果图

如何准确将成本中的固定成分和翻去成分分离是做好本量利分析的关键，一些成本项目的性质比较明确，可以直接划分为固定成本或变动成本，但是有的则比较模糊。比如电费，虽然电费与产量有关，但是产量为零时电费却并不为零，这说明电费中既有变动成分，又有固定成分。

成本分解表效果图

步骤01   新建工作表
新建一个工作簿，并在工作簿中重命名其中一个工作表，删除其他的。输入表格的标题、数据，并设置格式等。这个工作表中包括了产量和电费的数据。

步骤02   生成散点图
　　选中C2:D14区域，使用上一章介绍的方法插入一个散点图，这次我们要选择的是一个没有线的默认散点图。 

步骤03   对散点图进行调整
　　对于生成的散点图我们进行一些小的调整，以便最终效果更加便于查看。首先我们可以删除图例，因为现在只有一个系列。另外，我们还可以修改一下散点图的标题

步骤04   设置轴标题
　　如图所示，从“布局”选项卡中选择“坐标轴标题”下的横坐标及纵坐标项目可以添加轴标题，并设置相应的格式等。添加后可以直接对轴标题进行编辑。 

步骤05   给散点添加趋势线
　　右键单击散点图中的任意一个散点，从弹出的快捷菜单中选择“添加趋势线” 。

步骤06   趋势线的设置
　　在弹出的对话框中，选择“线性”，并勾选“显示公式”。

步骤07   分解成本
　　确定后生成最终的散点图。可以看到，在散点图上出现了线性趋势线和一个二元一次方程。方程中的截距代表固定成本，即505.4；斜率代表单位变动成本，即1.9942。 

知识点：趋势线
　　Excel的图表中，散点图、折线图、面积图、条形图、柱形图、股份图和气泡图中都可以添加趋势线，通常适合使用趋势线的的图表有两类，一是成对的数字数据，即典型的散点图中使用的数据；二是基于时间的数据，比如折线图、散点图、面积图等使用的数据。另外，除了线性趋势线，Excel还提供了非线性趋势线：
　　* 对数：当数据增加或减少的变化速率非常大，然后很快变得平缓时使用。
　　* 多项式：数据规则波动时使用，可以根据数据的波动规律制定多项式的阶数。
　　* 乘幂：数据按照固定的速率增加时使用，此时数据不能为零值或负数。
　　* 指数：数据以递增或者递减的趋势变化时使用，数据同样不能为零或负数。
　　* 移动平均：不是真正的趋势线，它是原数据按照指定的项数不高平均值。使用移动平均时要设定移动平均的项数。
　　在勾选“显示公式”后，图形上会显示线性或非线性的趋势线对应的公式。
　　在勾选“显示R平方值”后，图形上会显示模型的拟合系数。一般情况下，拟合系数越大，趋势线和原数据的拟合程度越好；反之则越差。