在D24格输“0”；在D25格输“-100”
设置盈亏平衡量：在B30格输“盈亏平衡量”； 在C30格输入公式：=C23；在B31格输“盈亏平衡收入”；在其后单元格中输公式：=D23。为方便查看，可设置相关格的单位和格式，同上。
使用“窗体”工具栏，添加“滚动条”控件，在G28格拖出一个滚动条，右击选“设置控件格式”命令，设当前值(C): 0 最小值(M):60 最大值(X):100 步长(I):1 步长(P):10 单元格链接√三维阴影 (L):$H$26 □
在J28格拖出一个滚动条，右击选“设置控件格式”命令，设当前值(C): 35 最小值(M):35 最大值(X):100 步长(I):1 步长(P):10 单元格链接(L):$K$26 √□三维阴影
生成散点图
“插入/XY散点图/平滑线散点图/数据区域(D): ='量本利分析(2)'!$B$2:$E$18 ·○列(L) /系列/点击添加(A)钮（把盈亏平衡线辅助数据添加到图表中），名称(N): 盈亏平衡线 X值(X):选C22:C25 ='量本利分析 (2)'!$C$22:$C$25 Y值(Y): 选D22:D25 ='量本利分析 (2)'!$D$22:$D$25 ”
设置刻度：设Y轴刻度为固定值去掉勾，最小值(N):-100 最大值(X):2000 主刻单位:100 次刻单位:100完成散点图。
盈亏平衡量的变动分析
通过上一节的工作表建立、数据输入，以及一系列的设置，我们已经有了一张量本利分析表，此时就可以通过这张表来分析售价变动和单位成本变动对盈亏平衡量、盈亏平衡收入以及散点图上盈亏平衡线的影响。
售价降低对盈亏平衡量的影响：拖动第一个滚动条，降低产品售价。此时散点图中
的盈亏平衡线向右侧移动，表明盈亏平衡量增大，相应的单元格数据也会增大。
售价提高对盈亏平衡量的影响：拖动第一个滚动条，提高产品售价。此时散点图中的盈亏平衡线向左侧移动，表明盈亏平衡量减小，相应的单元格数据也会减小。
单位成本降低对盈亏平衡量的影响：拖动第二个滚动条，降低单位成本。此时散点图中的盈亏平衡线向左侧移动，表明盈亏平衡量减小，相应的单元格数据也会减小。
单位成本提高对盈亏平衡量的影响：拖动第二个滚动条，提高单位成本。此时散点图中的盈亏平衡线向右侧移动，表明盈亏平衡量增大，相应的单元格数据也会增大。
第6章 成本分析
在量本利分析中要求将企业成本划分为变动和固定成本，在成本项目中很多项目既有固定成分，又有变动成分，所以需要将其分解。
1. 趋势线的使用 2. RAND函数、MlN函数、MATCH函数
成分分解表
11
如何准确地将成本中的固定成分和变动成分分离是做好量本利分析的关键，一此成本项目的性质比较叫确，可以直接划分为固定成本或变动成本，但是有的则比较模糊。比如电费，虽然电费与产量有关，但是产量为零时电费却并不为零，这说叫电费中既有变动成分，又有固定成分。
新建“”工作表，输入数据，并选中C2:D14区域（产量、电费）生成没有线的默认散点图，可进行一些小的调整，以便于查看。首先可删除图例，因为现在只有一个系列。
给散点添加趋势线：右击某个散点，选“添加趋势线”选“线性/勾选显示公式”，此时，生成最终的散点图。在散点图上出现了线性趋势线和一个二元一次方程。方程中的截距代表固定成本，即505.4；斜率代表单位变动成本，即1.9942。
知识点：趋势线
Excel图表中，散点图、折线图、面积图、条形图、柱形图、股份图和气泡图中都可以添加趋势线，通常适合使用趋势线的图表有两类，一是成对的数字数据，即典型的散点图中使用的数据；二是基于时间的数据，比如折线图、散点图、面积图等使用的数据。 另外，除了线性趋势线，Excel还提供了非线性趋势线：
※对数：当数据增加或减少的变化速率非常大，然后很快变得平缓时使用。 ※多项式：数据规则波动时使用，可以根据数据的波动规律制定多项式的阶数。 ※乘幂：数据按照固定的速率增加时使用，此时数据不能为零值或负数。 ※指数：数据以递增或者递减的趋势变化时使用，数据同样不能为零或负数。
※移动平均：不是真正的趋势线，它是原数据按照指定的项数不高平均值。使用移动平均时要设定移动平均的项数。
在勾选“显示公式”后，图形上会显示线性或非线性的趋势线对应的公式。
在勾选“显示R平方值”后，图形上会显示模型的拟合系数。一般情况下，拟合系数越大，趋势线和原数据的拟合程度越好；反之则越差。
采购成本分析表
材料的成本是生产成本的重要组成部分，而材料的成本除了价格因素外，还有一项很重要的因素，就是采购成本。采购成本通常由两项组成，一是采购环节发生的费用；二是材料存储时的发生的费用。这是两个互相制约的因素，每批采购量大，采购次数少，可以减少年采购成本，但是存储费用会增加；反之亦然。
因此确定采购量和存储量之例的关系也是一项很重要的工作，通过“采购成本分析”可以帮助企业设置科学合理的采购量和采购次数，从而为降低企业采购成本提供可靠依据。
我们先创建采购成本和存储成本在不同批次下的数据表，再利用公式计算最小成本、采购批次和采购量。然后添加年采购量、年采购成本和单位存储成本滚动条，最后制作存储成本和采购成本的散点图。首先，我们还是要来看一下本节要使用的公式：
采购数量=年采购量/年采购批次 B2 =$B$19/A2
12
平均存量=采购数量/2 C2 =B2/2 存储成本=平均存量×单位存储成本 D2 =C2*$I$19 采购成本=年采购批次×采购成本 E2 =A2*$E$19 总成本=存储成本+采购成本 F2=D2+E2
新建“采购成本分析”工作表，录入标题、年采购批次（A2:A13）等数据。用上边的公式计算后，再智能填充B2:F2区域的公式到B3:F13区域。
在A16格输【最低采购成本】，B16格输公式：=MIN(F2:F13)；D16格输【采购批次】，E16格输公式：=INDEX(A2:A13,MATCH(B16,F2:F13,0))；H16格输【采购量】，I16格输公式：=INDEX(B2:B13,MATCH(B16,F2:F13,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。如果为1，则查找小于或等于Lookup_value的最大数值，Lookup_array必须按升序排列：??、-2、-l、0、l、2??、A–Z、FALSE、TRUE；如果为0，则查找等于Lookup_value的第一个数值，Lookup_array可以按任何顺序排列；如果为-l，将查找大于或等于Lookup_value的最小数值，Lookup_array必须按降序排列：TRUE、FALSE、Z–A、??、2、1、0、-1、-2??，等等。如果省略Match_type，则假设为l。 如果Match_type为0且Lookup_value为文本，可以在Lookup_value中函数说明 使用通配符、问号(？)和星号(*)。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符(～)。 添加年采购量滚动条：在A21、D21、H21格拖出一个滚动条，右击选“设置控件格式”命令 设A21格当前值(C): 0 最小值(M):1000 最大值(X):3000 步长(I):200 步长(P):10 单元格√三维阴影 链接(L):$B$19 □
设D21格当前值(C): 0 最小值(M):200 最大值(X):600 步长(I):100 步长(P):10 单元格链√三维阴影 接(L):$E$19 □
设H21格当前值(C): 0 最小值(M):4 最大值(X):12 步长(I):1 步长(P):10 单元格链接√三维阴影 (L):$I$19 □
13
添加拆线图，选中D1:E13，插入数据点拆线图，在设置X轴，显然我们希望它表示年采购批次，“图表菜单/源数据/系列，分类(X)轴标志(T): =采购成本分析!$A$2:$A$13 ”（选A2:A13区域作为X轴的数据源），正是我们所需要的，在改变图例位置到上边。
采购量的变动影响分析
拖动滚动条，增大年采购量，存储成本增大而采购成本未变，因此折线图上的存储成本线发生变动而采购成本线保持不变。
存储成本增大，造成总成本增大，所以最低采购成本、采购批次、采购量也相应增大。
采购成本的变动影响分析
拖动滚动条，增大采购成本，采购成本增大而存储成本未变，因此折线图上的采购成本线发生变动而存储成本线保持不变。
采购成本增大，造成总成本增大，所以最低采购成本、采购量也相应增大，采购批次则减小。
单位存储成本的变动影响分析
拖动滚动条，增大单位存储成本，采购成本未变，因此折线图上的存储成本线发生变动而采购成本线保持不变。
采购成本增大，造成总成本增大，所以最低采购成本、采购批次也相应增大，采购量则减小。
第7章 销售分析
作为企业经营的重要环节，销售的重要性不言而喻，销售利润是企业追求的目标。对销售利润及其影响因素的分析有着非常重要的作用。同样，对于销售情况的预测也是一项十分重要的工作。
销售利润分析
销售利润受到许多因素的影响，比如产品的售价变动、成本的增减、销售数量的变化等。本节将展示如何将利润的变化额以定量的形式分解到各个因素中。
新建一个“销售数据表”工作表，输入产品名称 成本等内容。
销售利润=销售收入-销售成本-销售税金 F3=C3-D3-E3，并复制公式到F4:F9区域。
K3=H3-I3-J3，并复制公式到K4:K9区域。
销售数量
销售收入
销售税金
销售
14
上年同期单位成本售价 L3=IF($B3=0,0,ROUND(C3/$B3,4)) 复制公式到L3:O3区域。 上年总利润 P3=ROUND(O3*B3,2) 复制公式到P4:P9区域。
本年同期单位成本售价 Q3=IF(G3=0,0,ROUND(H3/$G3,4)) 复制公式到Q3:T9区域。 本年总利润U3=ROUND(T3*G3,2) 复制公式到U4:U9区域。
创建增减变化数据表
接下来我们要制作单位成本增减变化数据表。这个部分将要使用到的公式： 利润变化 =本年实际总利润—上年同期总利润 V3=U3-P3 复制公式到V4:V9 销售影响 =（本年实际销量—上年同期销量）×上年同期单位利润
W3 =IF(OR(B3=0,G3=0),0,ROUND((G3-B3)*O3,2)) 复制公式到W4:W9 售价影响 =（本年实际售价—上年同期售价）×本年实际销量
X3 =IF(OR(B3=0,G3=0),0,ROUND((Q3-L3)*G3,2)) 复制公式到X4:X9 税金影响 =（上年同期单位税金—本年实际单位税金）×本年实际销量
Y3 =IF(OR(B3=0,G3=0),0,ROUND((M3-R3)*G3,2)) 复制公式到Y4:Y9
成本影响 =（上年同期单位成本—本年实际单位成本）×本年实际销量
Z3 =IF(OR(B3=0,G3=0),0,ROUND((N3-S3)*G3,2)) 复制公式到Z4:Z9
品种影响，若上年同期销售数量为0，等于本年实际利润；若本年实际销售量为0，等于上年同期利润的负值。AA3 =IF(B3=0,U3,IF(G3=0,-P3,0)) 复制公式到AA4:AA9
添加销售利润分析图，选择W2:AA2和W13:AA13两个区域，插入簇状柱形图，删除图例，在图中把正负按不同颜色区分开较容易分辨，双击负值数据点设颜色。
销售预测分析
预测工作在财务管理中也是很重要的，除了销售预测，对于费用、成本、利润等都要进行科学地预测，才能为管理者提供决策的依据。
新建“销售预测分析”工作表，输入月份、销量两列数据，选中B2:C13单元格，插入数据点折线图，删除图表中的“月份”系列，先择“B3:B13”为X轴数据源。并给X轴和Y轴加上标题。
调整Y轴设置刻度：设Y轴刻度为固定值去掉勾，最小值(N):5200 最大值(X):82000 主刻单位:500 次刻单位:100完成折线图。
添加趋势线：右击折线，选“添加趋势线”，选择“线性/√□显示公式”。
15