Excel会计与财务管理应用大全 – 第4页

Excel 创建指数法预测模型

我们先来了解一下案例，然后根据案例创建指数法预测模型。

已知某企业生产的某种产品，产量与生产期间以及生产成本与产量均呈指数关系，现需要根据已有的12个期间的产量和成本数据，预测之后的4个期间的生产产量与生产成本。

具体操作步骤如下：

新建一个工作簿，将工作表Sheet1更改为“生产成本预测”，然后在该工作表中创建两个表格。一个表格用来输入已知的数据，包括已知的生产期间以及各期间对应的产量和生产成本；另一个表格用来输入需要预测的生产期间、目标产量以及目标成本，如图16-25所示。

图16-25　创建指数预测模型

Excel 设置公式预测营业费用

求出参数m和b以后，接下来就需要根据线性预测公式y=mx+b计算下半年各月的营业费用，这一步用户只需要在单元格J6中设置正确的公式即可。

在单元格J6中输入公式“=$E$4*I4+$F$4”，按下Enter键后，向下复制公式至单元格J12，得到6~12月各月的预计营业费用，如图16-23所示。

高手支招：编辑和修改数组公式的技巧

数组公式通常分为单个单元格数组公式和多单元格数组公式，单个单元格数组公式返回一个结果，而多个单元格数组公式返回一个数组。创建和编辑数组公式的基本原则是：每当需要输入或编辑数组公式时都要按Ctrl+Shift+Enter组合建。使用多单元格数组公式时，还需要注意以下方面：

图16-23　输入公式计算预计营业费用

1）必须在输入公式之前选择用于保存结果的单元格区域。

2）不能更改数组公式中单个单元格的内容，否则屏幕上会弹出如图16-24所示的提示对话框。

图16-24　提示对话框

3）可以移动或删除整个数组公式，但无法移动或删除其中某部分内容。换而言之，如果要缩减数组公式，需要先删除现有公式再重新输入。

4）要删除数组公式，先选择整个数组，然后再按下Delete键。

5）不能向多单元格数组公式中插入空白单元格或删除其中某个单元格。

6）数组公式中的大括号“｛｝”不能手动输入，是按下Ctrl+Shift+Enter组合建后自动生成的。

已知某函数的若干离散函数值{f1,f2,…,fn}，通过调整该函数中若干待定系数f（λ1,λ2,…,λm），使得该函数与已知点集的差别（最小二乘意义）最小。如果待定函数是线性，就叫线性拟合或者线性回归（主要在统计中）。线性拟合作为数学计算中一种常用的数学方法，在建筑、物理、化学、甚至天体物理、航天中都得到基本的应用。一般情况下，线性拟合需要根据实际需要，取用不同的拟合度，即R2。线性拟合函数LINEST的功能是使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函数返回数组数据，所以必须以数组公式的形式输入。该函数语法格式为：LINEST（known_y’s,known_x’s,const,stats）。Known_y’s是关系表达式y=mx+b中已知的y值集合；Known_x’s是关系表达式y=mx+b中已知的可选x值集合；const为一逻辑值，用于指定是否将常量b强制设为0，如果const为TRUE或省略，b将按正常计算，如果const为FALSE，b将被设为0，并同时调整m值使y=mx；stats为一逻辑值，指定是否返回附加回归统计值，为TRUE时返回附归统计值，为FALSE或省略，只返回系数m和常量b。

LINEST函数返回的附加回归统计值（根据数据的位置）说明如表16-1所示。

表16-1　LINEST函数回归统计值说明

接下来我们利用上面所述营业费用预测案例来介绍LINEST函数的具体使用方法。

步骤01：选择单元格区域E4:F8，如图16-15所示。

图16-15　选择单元格区域

步骤02：在“公式”选项卡中的“函数库”组中单击“插入函数”按钮，如图16-16所示。

图16-16　单击“插入函数”按钮

步骤03：在“插入函数”对话框中的“搜索函数”框中输入函数名称LINEST，然后单击“转到”按钮，如图16-17所示。

步骤04：此时在“选择函数”列表框中会显示与搜索函数相关的一系列函数，选择需要的函数LINEST，然后单击“确定”按钮，如图16-18所示。

步骤05：随后屏幕上弹出“函数参数”对话框，如图16-19所示。

步骤06：设置known_y’s参数为单元格区域C4:C9，设置known_x’s参数为单元格区域B4:B9，const参数省略，为空，stats参数设置为1，然后单击“确定”按钮，如图16-20所示。

图16-17　搜索函数

图16-18　选择函数

图16-19　弹出“函数参数”对话框

图16-20　设置函数参数

步骤07：此时函数计算结果如图16-21所示。只返回了一个值，并没有返回回归附加值，这是因为此时函数并没有作为数组公式输入。

图16-21　函数计算结果

步骤08：在编辑栏中单击，使公式处于编辑状态，然后按下键盘上的组合键Ctrl+Shift+Enter，数组公式返回结果如图16-22所示，单元格E4和F4中的值分别对应参数m和b。

图16-22　更改为数组公式

Excel 创建营业费用预测模型

首先，我们需要根据已知条件来创建企业营业费用线性预测模型，具体操作方法如下。

步骤01：新建一个工作簿，输入已知的上半年各月的营业额和营业费用，如图16-13所示。

图16-13　输入已知数据

步骤02：在工作表中创建一个“参数值”和下半年营业费用预测表格，如图16-14所示。

图16-14　创建预算表格

Excel 回归分析法资金需要量预测

回归法的计算原理与上一节中的高低点法类似，唯一的区别是在计算常量和变量时使用了Excel中的函数来替代数学公式。下面我们就来讲解一下回归法的具体操作步骤，体会一下回归法与高低法的不同。

步骤01：输入函数计算变量a。在单元格B17中输入公式“=SLOPE（C3：C8,B3：B8）”，按下Enter键后，计算结果如图16-9所示。

步骤02：输入函数计算常量b。在单元格B18中输入公式“=INTERCEPT（C3：C8,B3：B8）”，按下Enter键后，计算结果如图16-10所示。

图16-9　输入函数计算变量a

图16-10　输入函数计算常量b

步骤03：在单元格B19中输入预计产销量1500，如图16-11所示。

图16-11　输入预计产销量

步骤04：计算预计资金需要量。在单元格C19中输入公式“=B19*B17+B18”，按下Enter键后，计算结果如图16-12所示。

图16-12　计算预计资金需要量

Excel 高低点法资金需要量预测

高低点法是一种典型的资金性态法，它主要是利用函数关系y=ax+b来进行资金的预算。高低点法预测模型表格中包括的项目有：产销量高点、产销量低点、预测方程变量a、预测方程常量b及2016年预测值。接下来利用上面做出的预测模型开始进行具体的预算操作。

步骤01：计算产销量最高点。在单元格B11中输入公式“=MAX（B3：B8）”，按下Enter键后，计算结果如图16-3所示。

图16-3　计算产销量最高点

步骤02：计算产销量最低点。在单元格B12中输入公式“=MIN（B3：B8）”，按下Enter键后，计算结果如图16-4所示。

步骤03：设置公式引用资金占用量。在单元格C11中输入公式“=INDEX（$C$3：$C$8,MATCH（B11,$B$3：$B$8））”，按下Enter键后，拖动单元格C11右下角的填充柄，复制公式至单元格C12，得到如图16-5所示的结果。

图16-4　计算产销量最低点

图16-5　引用资金占用量

步骤04：计算变量a。在单元格B13中输入公式“=（C11-C12）/（B11-B12）”，计算结果如图16-6所示。

图16-6　计算变量a

步骤05：计算常量b。在单元格B14中输入公式“=C11-B11*B13”，计算结果如图16-7所示。

图16-7　计算常量b

步骤06：计算预计资金。在单元格B15中输入预计产销量“1500”，在单元格C15中输入公式“=B14+B15*B13”，计算结果如图16-8所示。即在2016年产销量为1500万的情况下，资金需要量为950万元。

图16-8　计算预计资金

Excel 创建资金需要量预测模型

下面我们以实例来说明怎么创建资金需要量预测模型。

已知某企业在2010年至2015年的产销量以及每年资金的占用量，假设2016年预计产销量为1500万，现需要预测2016年可能占用的资金量。假设资金需要量与销售量的关系为如下的一元线性关系：资金需要量（y）=a+b*销售量（x），这里a、b为待估计参数。

步骤01：新建一个工作簿，在工作表Sheet1中的输入如图16-1所示的已知数据。

图16-1　输入已知数据

步骤02：创建如图16-2所示的高低点资金预测模型和回归分析法资金预测模型。

图16-2　创建表格分析模型

关于资金需要量预测

资金需要量预测是指企业根据生产经营的需求，对未来所需资金的估计和推测。企业筹集资金，首先要对资金需要量进行预测，即对企业未来组织生产经营活动的资金需要量进行估计、分析和判断，它是企业制定融资计划的基础。资金需要量预测一般按以下几个步骤进行：首先需要进行销售预测，然后估计需要根据预计资产总量，再减去已有的资金来源，接着计算出负债的自发增长和内部提供的留存收益，从而得出应追加的资金需要量，以此为基础进一步确定所需的外部融资数额。

资金需要量预测是财务预测的重要组成部分，科学预测企业的资金需要量是合理筹划和运用资金、提高经济效益的重要保证。资金需要量预测常见的方法之一是资金性态法，是指根据资金的变动同产销量之间的依存关系预测未来资金需要量的一种方法。这种方法要求首先把企业的总资金划分为随产销量成正比例变动的变动资金和不受产销量变动的影响而保持固定不变的固定资金两部分，然后再进行资金需要量预测。

实战：使用 Excel 中的迷你图比较盈利状况

本章以产品单位利润的分析、销售税金、营业费用等在财务核算中与利润相关的实例，着重介绍了Excel中的条件格式和迷你图。使用条件格式相关的知识点包括使用条件格式突出显示数据、使用项目规则选取数据、使用数据条分析数据、使用色阶分析数据、使用图标集分析数据以及自定义规则等；迷你图相关的知识包括迷你图的创建、更改迷你图类型、更改迷你图数据和位置、设置迷你图显示属性以及为迷你图应用样式等。

迷你图是Excel 2016中新增加的功能，接下来将通过一个具体的实例，进一步对迷你图相关知识进行巩固和应用。打开实例文件“公司年度盈利统计.xlsx”工作簿。

步骤01：在“插入”选项卡中的“迷你图”组中单击“柱形图”按钮，如图15-92所示。

步骤02：在“创建迷你图”对话框中的“数据范围”框中设置为单元格区域B3:F3，在“位置范围”框中设置为单元格G3，然后单击“确定”按钮，如图15-93所示。

步骤03：此时单元格G3中会显示创建的默认样式的迷你图，如图15-94所示。

图15-92　单击“柱形图”按钮

图15-93　设置数据范围和位置范围

图15-94　创建的默认的迷你图效果

步骤04：在“迷你图工具–设计”选项卡中的“显示”组中勾选“高点”复选框，如图15-95所示。

图15-95　显示“高点”

步骤05：在“迷你图工具–设计”选项卡中的“样式”组中单击“标记颜色”下三角按钮，从展开的下拉列表中单击“高点”选项，从下级下拉列表中单击“黑色，文字1”，如图15-96所示。

图15-96　设置高点颜色

步骤06：在“插入”选项卡中的“迷你图”组中单击“折线”按钮打开“创建迷你图”对话框。设置“数据范围”为单元格区域B3:F3，设置“位置范围”为单元格G4，然后单击“确定”按钮，如图15-97所示。

图15-97　创建折线迷你图

步骤07：创建好迷你图后，用同样的方法将折线迷你图中的高点设置为“黑色，文字1”，如图15-98所示。

图15-98　迷你图效果

步骤08：用同样的方法，以单元格区域B5:F5为数据区域，分别在单元格G5和G6中创建列迷你图和折线迷你图，并将高点设置为红色，得到的迷你图最终效果如图15-99所示。

图15-99　迷你图最终效果

创建迷你图组合图表分析企业利润

迷你图存在于单元格上，而单元格又是用户平时操作最频繁的对象，经常会在图表、文本框或者图片中引用单元格。但是迷你图并不是真正存在于单元格内的“内容”，它只能被看作覆盖在单元格上方的图层，用户不能通过直接引用的方式来引用它，需要先将它转换为图片。下面以一个具体的实例，来介绍如何在图表中引用迷你图，生成组合图表。

已知某企业各分公司在某年度上半年各月的利润，并且已使用迷你图分析了各分公司利润的趋势。现需要创建一个图表，要求既可以比较各分公司1~6月的利润总额，也能反映各月的利润变化趋势。打开实例文件“各分公司上半年利润统计表.xlsx”工作簿。

步骤01：选择单元格区域A3:A6和单元格区域H3:H6，如图15-82所示。