《财务管理必会excel之成本分析.pdf》由会员分享,可在线阅读,更多相关《财务管理必会excel之成本分析.pdf(18页珍藏版)》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、156财务管理必会Excel应用100例办公室之第11章 成本分析成本分析中有很多常用的表,本章将会看到其中一些。上一章我们讲了本量利分析,本量利分析要求将企业成本划分为变动和固定成本,在成本项目中很多项目既有固定成分,又有变动成分,所以需要将其分解。本章要点:1.趋势线的使用 2.RAND函数、MIN函数、MATCH函数如何用汉字名称代替单元格地址?材料成本汇总表效果图采购成本分析表效果图157成本分析CHAPTER 1 111.1成本分解表如何准确将成本中的固定成分和翻去成分分离是做好本量利分析的关键,一些成本项目的性质比较明确,可以直接划分为固定成本或变动成本,但是有的则比较模糊。比如电
2、费,虽然电费与产量有关,但是产量为零时电费却并不为零,这说明电费中既有变动成分,又有固定成分。成本分解表效果图步骤01 新建工作表新建一个工作簿,并在工作簿中重命名其中一个工作表,删除其他的。输入表格的标题、数据,并设置格式等。这个工作表中包括了产量和电费的数据。步骤02 生成散点图选中C2:D14区域,使用上一章介绍的方法插入一个散点图,这次我们要选择的是一个没有线的默认散点图。定义名字的方法有两种:一种是选定单元格区域后在“名字框”直接输入名字,另一种是选定想要命名的单元格区域,再选择“插入”“名字”“定义”,在“当前工作簿中名字”对话框内键人名字即可。158财务管理必会Excel应用10
3、0例办公室之步骤03 对散点图进行调整对于生成的散点图我们进行一些小的调整,以便最终效果更加便于查看。首先我们可以删除图例,因为现在只有一个系列。另外,我们还可以修改一下散点图的标题。步骤04 设置轴标题如图所示,从“布局”选项卡中选择“坐标轴标题”下的横坐标及纵坐标项目可以添加轴标题,并设置相应的格式等。添加后可以直接对轴标题进行编辑。如何在公式中快速输入不连续的单元格地址?159成本分析CHAPTER 1 1步骤05 给散点添加趋势线右键单击散点图中的任意一个散点,从弹出的快捷菜单中选择“添加趋势线”。步骤06 趋势线的设置在弹出的对话框中,选择“线性”,并勾选“显示公式”。步骤07 分解
4、成本确定后生成最终的散点图。可以看到,在散点图上出现了线性趋势线和一个二元一次方程。方程中的截距代表固定成本,即505.4;斜率代表单位变动成本,即1.9942。知识点:趋势线Excel的图表中,散点图、折线图、面积图、条形图、柱形图、股份图和气泡图中都可以添加趋势线,通常适合使用趋势线的的图表有两类,一是成对的数字数据,即典型的散点图中使用的数据;二是基于时间的数据,比如折线图、散点图、面积图等使用的数据。可按住Ctrl键,进行不连续区域的选取。区域选定后选择“插入”“名字”“定义”,将此区域命名,如Group1,然后在公式中使用这个区域名,如“SUM(Group1)”。160财务管理必会E
5、xcel应用100例办公室之另外,除了线性趋势线,Excel还提供了非线性趋势线:*对数:当数据增加或减少的变化速率非常大,然后很快变得平缓时使用。*多项式:数据规则波动时使用,可以根据数据的波动规律制定多项式的阶数。*乘幂:数据按照固定的速率增加时使用,此时数据不能为零值或负数。*指数:数据以递增或者递减的趋势变化时使用,数据同样不能为零或负数。*移动平均:不是真正的趋势线,它是原数据按照指定的项数不高平均值。使用移动平均时要设定移动平均的项数。在勾选“显示公式”后,图形上会显示线性或非线性的趋势线对应的公式。在勾选“显示R平方值”后,图形上会显示模型的拟合系数。一般情况下,拟合系数越大,趋
6、势线和原数据的拟合程度越好;反之则越差。11.2材料成本汇总表在材料消耗环节既要统计同一产品不同材料的消耗量,又要计算不同产品对同一材料的消耗量。如何利用材料消耗明细账快速准确地制作出两个统计口径下的材料消耗汇总表是很重要的工作。材料成本汇总表效果图如何定义局部名字?161成本分析CHAPTER 1 1步骤01 新建工作表新建一个工作簿,并在工作簿中重命名其中一个工作表,删除其他的。输入表格的字段标题、数据,并设置格式等。步骤02 生成材料金额在D3输入公式:=INT(RAND()*1000),并复制公式到D4:D32区域。知识点:RAND函数RAND函数返回大于等于 0 及小于 1 的均匀分
7、布随机实数,每次计算工作表时都将返回一个新的随机实数。函数语法RAND()函数说明如果要使用函数 RAND 生成一随机数,并且使之不随单元格计算而改变,可以在编辑栏中输入“=RAND()”,保持编辑状态,然后按 F9,将公式永久性地改为随机数。在默认情况下,工作薄中的所有名字都是全局的。其实,可以定义局部名字,使之只对某个工作表有效,方法是将名字命名为“工作表名!名字”的形式即可。162财务管理必会Excel应用100例办公室之在本例中,使用公式:=INT(RAND()*1000),即是生成一个大于0及小于1000的整数。如果要生成一个规定范围内的随机数,可以使用公式:=RAND()*(上限-
8、下限)+下限步骤03 开始生成数据透视表选择B2:D32中的任意单元格,然后在“插入”选项卡中选择“数据透视表”里的“数据透视表”,开始生成数据透视表。步骤04 使用向导进行设置这时会弹出一个对话框,本例中不必改动任何选项。在本例中数据来源就是我们的工作表,而数据透视表生成于新的工作表中。步骤05 设置布局在这一步我们要在新生成的工作表中设置数据透视表的布局形式,比较简单的方式就是直接在窗口中拖动字段名称到相应的区域。步骤06 生成数据透视表通过上面的一系列设置,我们得到一张数据透视表,表中显示了材料和产品的汇总数据。如何命名常数?163成本分析CHAPTER 1 1步骤07 增加数据透视图有
9、了数据透视表,但我们希望更加直观地查看数据,所以我们可以增加一张数据透视表,方法跟之前生成柱形图的方法是一样的。步骤08 生成数据透视图通过上面一个简单的步骤,我们就得到了一张数据透视图,不过我们还需要对图形做一些细节的调整。在某个工作表中经常需用利率4.9来计算利息,可以在“当前工作薄的名字”框内输入“利率”,在“引用位置”框中输入“0.049”,按“确定”按钮。164财务管理必会Excel应用100例办公室之步骤09 设置数据标签现在我们要对柱形图的数据标签进行设置,以方便查看。方法前面已经讲过,在“布局”选项卡中选择“数据标签”下的“其他数据标签选项”。步骤10 显示系列名称在弹出的对话
10、框中,我们选择显示系列名称,这样柱形图上就会直观地显示材料的类型。步骤11 完成设置做完以上的步骤,我们就得到了最终的结果。实际上,本节的例子除了使用数据透视表,我们也可以使用公式来得到相同的结果,这里我们就不再赘述了,有兴趣的朋友可以自己研究一下。如何给工作簿扩容?165成本分析CHAPTER 1 1知识点:数据透视表的数据源使用数据透视表可以汇总、分析、浏览和提供摘要数据。使用数据透视图可以在数据透视表中可视化此摘要数据,并且可以方便地查看比较、模式和趋势。数据透视表和数据透视图都能使您做出有关企业中关键数据的决策。在创建数据透视表时,可使用多种源数据类型。使用工作表数据可以将Excel工
11、作表中的数据作为报表的数据来源。数据应为列表格式,第一行包含列标签,其余行包含相同列中的类似项,并且数据区域中没有空白的行或列。Excel将列标签作为报表的字段名称。使用外部数据源要汇总和分析 Excel 的外部数据,则可从包括数据库、OLAP 多维数据集和文本文件的外部数据源上检索数据。使用其他数据透视表每次在新建数据透视表或数据透视图时,Excel均将报表数据的副本存储在内存中,并将其保存为工作簿文件的一部分。这样每张新的报表均需要额外的内存和磁盘空间。但是,如果将现有数据透视表作为同一个工作簿中的新报表的源数据,则两张报表就可以共享同一个数据副本。因为可以重新使用存储区,所以就会缩小工作
12、簿文件,减少内存中的数据。如果要将某个数据透视表用作其他报表的源数据,则两个报表必须位于同一工作簿中。如果源数据透视表位于另一工作簿中,则需要将源报表复制到要新建报表的工作簿位置。不同工作簿中的数据透视表和数据透视图是独立的,它们在内存和工作簿文件中都有各自的数据副本。在刷新新报表中的数据时,Excel也会更新源报表中的数据,反之亦然。如果对某个报表中的项进行分组或取消分组,那么也将同时影响两个报表。如果在某个报表中创建了计算字段或计算项,则也将同时影响两个报表。更改现有报表的源数据更改源数据将导致用于分析的数据也发生变化。例如,您可能希望方便地从测试数据库切换到生产数据库。可以通过刷新报表,
13、使用与原始数据连接信息类似的新数据来更新数据透视表。选取“工具”“选项”命令,选择“常规”项,在“新工作薄内的工作表数”对话栏用上下箭头改变打开新工作表数。一个工作薄最多可以有255张工作表,系统默认值为6。166财务管理必会Excel应用100例办公室之11.3采购成本分析表材料的成本是生产成本的重要组成部分,而材料的成本除了价格因素外,还有一项很重要的因素,就是采购成本。采购成本通常由两项组成,一是采购环节发生的费用;二是材料存储时的发生的费用。这是两个互相制约的因素,每批采购量大,采购次数少,可以减少年采购成本,但是存储费用的增加;反之亦然。因此确定采购量和存储量之间的关系也是一项很重要
14、的工作,通过“采购成本分析”可以帮助企业设置科学合理的采购量和采购次数,从而为降低企业采购成本提供可靠依据。采购成本分析表效果图我们先创建采购成本和存储成本在不同批次下的数据表,再利用公式计算最小成本、采购批次和采购量。然后添加年采购量、年采购成本和单位存储成本滚动条,最后制作存储成本和采购成本的散点图。首先,我们还是要来看一下本节要使用的公式:采购当量年采购量/年采购批次平均存量采购数量/2存储成本平均存量单位存储成本采购成本年采购批次采购成本总成本存储成本采购成本步骤01 新建工作表新建一个工作簿,并在工作簿中重命名其中一个工作表,删除其他的。输入表格的字段标题、数据,并设置格式等。步骤0
15、2 输入公式在B2到F2依次输入下列公式:=$B$19/A2如何减少重复劳动?167成本分析CHAPTER 1 1=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)。为了减少重复劳动,我们可以把一些常用到的操作定义成宏。其方法是:选取“工具”菜单中的“宏”命令,执行“记录新宏”,记录好后按“停止”按钮即可。
16、也可以用VBA编程定义宏。168财务管理必会Excel应用100例办公室之步骤06 采购量公式在I16单元格输入公式:=INDEX(B2:B13,MATCH(B16,F2:F13,0)。关于INDEX函数的使用我们在前面的相关章节已经介绍过。知识点:MIN函数返回一组值中的最小值。函数语法MIN(number1,number2,.)Number1,number2,.是要从中查找最小值的 1 到 255 个数字。函数说明参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为数组或引用,则只使用该数组或引用中的数字。数组或引用中的空白单元格
17、、逻辑值或文本将被忽略。如果参数中不含数字,则函数 MIN 返回 0。如果参数为错误值或为不能转换为数字的文本,将会导致错误。怎样直接打开文本文件获得所需数据?169成本分析CHAPTER 1 1MATCH函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身,则应该使用 MATCH 函数而不是 LOOKUP 函数。函数语法MATCH(lookup_value,lookup_array,match_type)Lookup_value为需要在Lookup_array中查找的数值。例如,如果要在电话簿中查找某人的电话号码,则应该将姓名作为查找值,但实
18、际上需要的是电话号码。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
19、查找等于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查找不成功,则返回错误
20、值#N/A。如果match_type为0且lookup_value为文本,可以在lookup_value中使用通配符、问号(?)和星号(*)。问号匹配任意单个字符;星号匹配任意一串字符。如果要查找实际的问号或星号,请在该字符前键入波形符()。步骤07 添加年采购量滚动条选择“文件”“打开”,再单击“文件类型”下拉列表中,选择“文本文件”,这样就可以从打开得文本文件中获得所需数据了。170财务管理必会Excel应用100例办公室之我们前面已经在自定义快速访问工具栏上添加了滚动条按钮,现在我们又要用到它。点击这个按钮,然后在A21单元格拖出一个滚动条。步骤08 设置年采购量滚动条在刚刚添加的滚动条
21、上单击鼠标右键,在弹出的对话框中对滚动条进行如图中所示的设置。与这个滚动条相关联的单元格是B19,设置方法是单击文本框后面的编辑按钮,再用鼠标选中B19单元格。单击“确定”完成设置。步骤09 添加并设置采购成本滚动条使用与上两步相机的方法在D21单元格添加滚动条,并对其进行设置,设置内容如图所示。步骤10 添加并设置单位存储成本滚动条同理,在H21单元格添加滚动条,并对其进行设置,设置内容如图所示。步骤11 完成设置通过以上几个步骤的设置,我们已经得到一个具有数据调节功能的采购成本分析表。怎样将数字格式化为文本类型?171成本分析CHAPTER 1 1步骤12 添加折线图选中D1:E13单元格
22、,在“插入”选项卡中选择“折线图”的“带数据标记的折线图”,向工作中添加折线图。步骤13 设置X轴看一下X轴,显然我们希望它表示年采购批次,但是显示的内容并不正确,准确地是说是正好相反,所以我们需要进行设置。现在我们双击折线图,并在工具栏上单击“选择数据”按钮,在弹出的对话框中,单击“水平轴标签”下的“编辑”按钮,选择水平轴标签的数据源,选择A2:A13作为X轴的数据源,正是我们所需要的。步骤14 改变图例位置在图例上单击鼠标右键,选择“设置图例格式”,在弹出的对话框中,选择靠上。这样图例就从右边跑到了顶部。在Excel中,通过选择含有数值的单元格,并且在“格式”菜单中选择“单元格”项,在数字
23、标签中选定文本格式,并单击“确定”按钮。利用此技巧,还可以将数值格式化为邮政编码、电话号码、社会保险号等等。172财务管理必会Excel应用100例办公室之步骤15 最终结果关闭设置对话框,结束设置,我们终于得到了最后的结果。如果需要,你还可以设置一下字体,颜色等等。11.4采购成本变动分析在上一节我们在完成了采购成本变动分析表,现在我们就可以利用这个工作表来分析“年采购量”、“采购成本”、“单位存储成本”和“最低采购成本”、“采购批次”、“采购量”以及折线图之间的动态变化关系。步骤01 采购量的变动影响分析拖动滚动条,增大年采购量,存储成本增大而采购成本未变,因此折线图上的存储成本线发生变动
24、而采购成本线保持不变。存储成本增大,造成总成本增大,所以最低采购成本、采购批次、采购量也相应增大。怎么样快速输入含小数点的数字?173成本分析CHAPTER 1 1步骤02 采购成本的变动影响分析拖动滚动条,增大采购成本,采购成本增大而存储成本未变,因此折线图上的采购成本线发生变动而存储成本线保持不变。采购成本增大,造成总成本增大,所以最低采购成本、采购量也相应增大,采购批次则减少。步骤03 单位存储成本的变动影响分析拖动滚动条,增大单位存储成本,采购成本未变,因此折线图上的存储成本线发生变动而采购成本线保持不变。采购成本增大,造成总成本增大,所以最低采购成本、采购批次也相应增大,采购量则减少。打开“工具选项编辑”标签页,首先勾选“自动设置小数点”复选框,再在“位数”编辑框中设置小数点右边的位数就行了。这样就只需要直接输入数字,而可以省略小数点的输入,它会在回车键后自动定位。