混合成本Excel详解

1.分解混合成本的方法

财务会计报告是分析企业财务状况、经营成果与现金流量等重要经济信息的载体，但由于其信息的局限性，有时需对其进行加工分析或数据挖掘等，满足财务管理的需要。为了进行成本预测、风险分析、杠杆分析、本量利分析等，需对利润表中的成本费用，如主营业务成本、税金及附加、管理费用、销售费用、资产减值损失等成本费用项目，按成本习性分类为变动成本、固定成本和混合成本；再将混合成本进行成本习性分析，分解为变动成本和固定成本，从而计算出企业的固定成本总额和单位变动成本（或变动成本率）。对混合成本可使用高低点法、回归直线法等进行分析。

2.高低点法

高低点法用直线方程式Y＝a＋bX来表达混合成本，其中固定成本总额a、单位变动成本（或变动成本率）b两个参数的计算公式如下：

3.相对位置查找MATCH函数

Excel提供了查找指定数值在数组中的相对位置的查找MATCH函数，如查找某单元格在选定的区域中的相对行数值、相对列数值等。相对位置查找MATCH函数的公式如下：

＝MATCH(lookup＿value，lookup＿array，match＿type)

式中，lookup＿value表示待查数据，即需要在数据表中查找的数值，可以为数字、文本或逻辑值，或对数字、文本或逻辑值的单元格引用。lookup＿array表示查找范围，即包含所要查找的数值的连续单元区域，它应为数组或数组引用。在查找范围中待查找数据必须唯一，即不能有重复项；若有重复项，应合并同类项或删除重复值。match＿type表示查找方式，若为1，则查找小于或等于待查数的最大数值（待查范围必须按升序排列）；若为0，则查找等于待查数的第一个数值（待查范围可按任何顺序排列）；若为-1，则查找大于或等于待查数的最小数值（查找范围必须按降序排列）；若省略，则假设为1。

4.相对位置引用INDEX函数

Excel提供了引用相对位置INDEX函数，有数组和引用两种形式。

（1）数组INDEX函数的作用是从一个指定的区域中查找某行与某列交叉的单元格，并取该单元格的数值，即单一区域的单元取值函数INDEX。其函数公式如下：式中，array表示要引用的单元区域或数组常量，即查找范围；row＿num表示在查找范围内要查找的相对行数；column＿num表示要查找的相对列数。

＝INDEX(array，row＿num，column＿num)

例如：“＝Index（A2：D6，2，3）”是指从A2至D6中查找相对位置为第2行和第3列交叉的单元格，并取该单元格的值，其结果是取C3单元格的值。

（2）引用INDEX函数的作用是从多个指定的区域中，查找其中一个区域内某行与某列交叉的单元格，并取该单元格的数值，即多区域的单元取值函数INDEX。其函数公式如下：

＝INDEX(reference，row＿num，column＿num，area＿num)

式中，row＿num表示在查找范围内要引用的相对行数；column＿num表示要查找的相对列数；reference表示要引用的全部单元区域（即查找范围），如果是不连续的区域，必须用括号括起来；area＿num表示要取值的区域在引用区域（即查找范围）中的相对序号。

例如：reference为“（A1：B4，D1：E4，G1：H4）”时，若area＿num取值为1，即是A1至B4区域；取值为2，即是D1至E4区域；取值为3，即是G1至H4区域。

再如：“＝INDEX（（C3：H5，C7：F9），1，3，2）”是指从C3至H5、C7至F9这两个区域的第2个（即C7至F9）区域中，查找处于该区域中第1行（即工作表的第7行）、第3列（即工作表的E列）的单元格，并取该单元格的值，其结果是取E7单元格的值。

某公司近5年利润表中的营业收入、成本费用总额，以及2019年收入预测值见表4-3。分解该公司的混合成本，并预测2019年成本费用总额。

表4-3　某公司近年收入与成本费用总额表　　万元

在Excel中混合成本分解结果，并进行预测公式提示，如图4-19所示。

图4-19　高低点法结果(www.xing528.com)

（a）及MATCH查找函数（b）对话框

（1）在“Excel财务会计”工作簿中新建“分解成本”工作表。在该表录入文字，录入C3至D7、C13单元格中的已知数据，合并单元区域，并设置字体、字号，调整行高、列宽等。

需注意的是，A2、C2、D2等单元格需使用自动换行，或下标字体等格式进行设置。

（2）用MIN、MAX极值函数，计算销售收入的低点与高点，方法如下：

a.选定C8单元格，单击插入函数按钮并选择“统计”函数类别中的最小值MIN函数，进入“函数参数”对话框，键入需要求取最小值的一组单元区域“C3：C7”，单击“确定”按钮后，其公式为“＝MIN（C3：C7）”。

b.选定C9单元格，单击插入函数按钮并选择“统计”函数类别中的MAX函数，进入最大值MAX的“函数参数”对话框，键入查找最大值的单元区域范围“C3：C7”，单击“确定”按钮后，其公式为“＝MAX（C3：C7）”。

（3）用相对位置查找MATCH函数，确定高点、低点收入的相对位置，方法如下：

a.选定C10单元格，单击插入函数按钮并选择“查找与引用”函数类别中的MATCH函数，进入“函数参数”对话框，如图4-19（b）所示。

b.在待查参数value中键入最小值所在的单元格“C8”；在范围参数array中键入查找的单元区域“C3：C7”；在查找方式参数type中键入“0”（查找相等的值）。

c.单击“确定”按钮回到工作表，编辑框中显示其公式“＝MATCH（C8，C3：C7）”；在C10单元格显示“3”。这是因为要查找的低点收入值在C5单元格中，而C5单元格在C3至C7单元区域中行数的相对位置为3。

d.用以上方法，在D10单元格查找高点收入在C3至C7区域中的相对位置，也可直接在D10单元格键入公式“＝MATCH（C9，C3：C7，0）”，其显示的相对位置为“5”。

（4）用数组INDEX函数引用高点与低点收入对应的成本费用总额，其方法如下：

a.选定D8单元格，单击插入函数按钮并选择“查找与引用”类别中的INDEX函数；由于该函数有数组和引用两种形式，所以进入“选定参数”对话框，如图4-20（a）所示。

b.选定数组INDEX函数（第1项）进入“函数参数”对话框，如图4-20（b）所示。

图4-20　选定INDEX的组合方式（a）与函数参数（b）

c.在查找范围Array中键入“D3：D7”（成本费用总额历史数据区域）；在待查找相对行数Row＿num中键入“C10”（低点收入所在的相对行数）；由于查找范围只有一列，所以待查找相对列数Column＿num可以忽略，也可以输入0或1。

注意：若有多列，则应输入该数据在该区域中的相对列数。例如，在查找范围参数Array中键入了“C3：D7”两列，则该参数应键入“2”，即还要限定在该范围第2列中查找。

d.单击“确定”按钮，工作表编辑框中显示公式“＝INDEX（D3：D7，C10，0）”，在D8单元格中将显示“1530”。

因为C10单元格值为3，处于D3至D7单元格区域中第3行的为D5单元格，D5单元格的值为1 530。

e.用以上方法，在D9单元格键入公式“＝INDEX（D3：D7，D10，0）”，D9单元格显示“1911”。即在D3至D7范围内查找相对行数为5的单元格，并取该单元格的值。

注意：D9单元格也可设置为两列的区域查找公式“＝INDEX（C3：D7，D10，2）”。

f.隐藏第10行。第10行中的相对行数是为了使用INDEX函数而产生的设计代码，可将此干扰数据隐藏。方法是右击第10行并选择“隐藏”命令（隐藏后工作表显示的行号不连续）。

（5）计算固定成本、变动成本率。在D11单元格键入高低点法固定成本公式“＝D9-D12∗C9”；在D12单元格键入变动成本率公式“＝（D9-D8）/（C9-C8）”。

（6）直线公式预测。在D13单元格键入2019年成本费用预测公式“＝D11＋D12∗C13”。

（7）提示信息。在B14单元格键入数值与文本混合运算公式“＝"预测公式：Y＝"＆ROUND（D11，2）＆"＋"＆ROUND（D12，2）＆"∗X"”。

公式含义：对D11、D12单元格中的a、b两个参数值，四舍五入并保留两位小数，再以Y＝a＋bX的方式进行显示。