!!!财务预测中构建Excel回归分析模型

2011年3月第14卷第6期

ChinaManagementInformationization

中国管理信息化

Mar.，2011Vol．14，No．6

财务预测中构建Excel回归分析模型

王顺金，陈立波

（重庆电子工程职业学院会计与金融系，重庆401331）

［摘

要］本文研究在销售收入、资金占用、成本费用等财务预测中，利用Excel内置的公式与函数、窗体工具交互选择等功

能，构建Excel回归分析模型的技巧。

［关键词］回归分析；模型构建；Excel函数；窗体工具

doi:10.3969/j.issn.1673-0194.2011.06.006［中图分类号］F232［文献标识码］A［文章编号］1673-0194（2011）06-0011-04

一、导

言

以建立交互选择模型，简化复杂的数据计算。回归分析的运用非常广泛，在单位理财中主要用于销售预测、资金预测和成本费用预测等方面。

回归分析一般用直线方程Y＝a＋bX预测未来值。其中，截距参数a、斜率参数b的确定方法有：（1）最小二乘法公式计算；（2）

预测是对未来状况的分析、判断和规划，Excel中可使用窗体工具的滚动条、微调项、列表框等，对未来状况值进行范围设定，

［收稿日期］2010-3-14

中国交通职业教育教学指导委员会科研项目（2009B43）。［基金项目］

王顺金（1964-），男，重庆电子工程职业学院会计与金［作者简介］

融系教授、高级会计师、高级审计师、注册会计师，主要研究方向：会计电

算化、财务管理、财务会计、审计；陈立波（1963-），男，重庆电子工程职业学院会计与金融系主任，副教授。

Excel截距函数Intercept、斜率函数Slope确定；（3）用直线拟合函数Linest计算，再将其作为数值引用函数Index的嵌套函数，以

返回参数值等。

财务预测时，一般都要用到计数函数COUNT、求和函数失，价格高使卖方收益而买方受损，价格低会使卖方受损而买方受益，但从总体上双方收益之和等于零，即零和博弈。由于国家以税收征收者的身份出现，使每一笔赊销业务出现第三方，改变了零和博弈的格局。从对赊销业务和赊购业务的分析中我们看到，国家在赊销业务中是比较大的受益者，主要原因在于赊销方在没有收到现金的前提下，国家就预收了各种税款包括增值税、消费税、资源税、城市建设维护税、教育费附加以及企业所得税等，在企业现金流入不确定的情况下，就确定了国家的现金流入，这在某种程度上是不公平的。新会计准则实施以来，无收无论怎样降低企业税负，国家税收都以两位数的增长率增长，这是我国改变收付实现制为权责发生制以来，给国家带来收益的实证。但税收政策是国家单方面做出的，虽然税收政策的制定经过咨询、制定、试点、实施等阶段，但会计制度的颁布与实施一般落后于税收政策，如权责发生制的实施就严重侵蚀了企业利益，长此以往，企业的现金流和资金链就会断裂，对国家税收的稳定增长反而是不利的，因此国家应采取一些保护企业现金流的措施，以维持企业的健康有序持续发展。

总之，通过对赊销业务各利益主体的分析我们看到，每一笔赊销业务的发生对赊销单位、赊购单位及国家的影响都是很大的，利益分配存在严重的不均衡，如何采取对策改变这种不均衡分配态势，对于企业的持续发展具有重要现实意义。

主要参考文献

［1］李仰陶．企业应收账款管理之浅析［J］．山西财经大学学报，2010（z2）．［2］李瑞兰．刍议坏账准备计提的赊销百分比法［J］．财会月刊，2010（22）.［3］李成章．基于赊销风险度企业赊销风险评估模型及操作［J］．天津财经

大学学报，2009（4）．

［4］张舰．企业应收账款的风险与管理策略［J］．企业活力，2008（7）．［5］梁洁．企业应收账款管理方略［J］．煤炭经济研究，2007（8）．

［6］孙雪莲．刍议应收账款机会成本的计算［J］．财会月刊：综合版，2006（27）．

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

的特征，在一年中可以看成一个常数，因此企业赊购是对企业极为有利的无息筹资方式。同时赊购单位可以将节约的1340000元存入银行取得存款利息即1340000×2.5%=33500（元）,如果企业筹集这笔资金可以免付银行贷款利息1340000×

5.6%=75040（元），还应该看到在对赊销业务及赊购业务分析时，

利息是所有业务中最低的，也是最起码能得到的机会成本或收益，如果企业用这笔资金购买股票或进行其他投资，其收益远远高于此。由于赊购业务给企业带来巨大收益，企业都在努力拓展赊购业务，即使赊购价格较高也乐于接受。通过分析可以看出，在某种程度上，赊购单位得到的已经超出了赊销单位给予的收益。赊购给企业带来的有利之处在于：一是能够缓解企业的资金压力，赊购行为的直接后果是引起企业现金净增加，这种依赖商业信用的筹资行为，筹资成本较低甚至没有成本，而且会成为企业稳定的资金来源。但这种筹资方式风险较大，企业必须在年内或几个月内给予支付，如果发生的金额过大，反而会加重企业资金困难的局面。二是降低企业的偿债能力，特别是短期偿债能力。一般而言。在所有的短期债务项目中，应付账款是最常见、最稳定的短期负债项目，如果没有计划地扩大规模，会严重影响企业的短期偿债能力，增加企业的财务风险。企业在赊购商品时要遵守信用，及时归还货款，否则企业将无法取得这种商业信用。同时要充分注意到原材料采购十分复杂，企业在处理赊购业务时要做好预算，对采购的商品做好验收，避免压货或供应次品的问题发生，更不能无限制进行赊购，应做好现金支付预算，避免到期货款无力支付，以防财务风险和信用风险。

三、国家在赊销业务中的得失分析

税收是国家为了实现其管理职能，以法律的形式提前规定征收标准和方式，凭借国家权利强制无偿征收货币或实物的一种经济行为，是国家参与国民收入分配和再分配的重要手段，也是国家实行宏观调控的重要措施。税收具有强制性、无偿性及固定性的显著特征。依据双方交易原则，一方获利正是另一方的损

CHINAMANAGEMENTINFORMATIONIZATION

/11

财务管理

SUM、四舍五入函数ROUND、条件函数IF，以及字符运算、显示公式或计算结果等技术方法，以充分利用Excel强大的计算、分

析、提示等功能。

二、销售回归分析的模型构建

Excel回归分析时，可用最小二乘法公式确定a、b的参数值，再用Y＝a＋bX预测未来销售收入。最小二乘法参数公式如下：

-∑x∑ya=∑y-b∑xb=n∑xy

（）式中，y表示销售量或销售额；X表示时间序列；a表示固定不变的销售；b表示随时间序列变动的单位销售；n表示历史数据的期间项数；∑表示求和。

Excel提供了计算期间项数n的计数函数COUNT，其函数公

式如下：

4．输入参数公式。在B9单元格键入“＝ROUND（（D8－B10*C8）／B8，1）”。其中ROUND是四舍五入函数，参数“1”表示保留一位小数；“（D8－B10*C8）／B8”是参数a的计算公式。

在B10单元格中键入对参数b的计算结果四舍五入并保留一位小数的函数公式“＝Round（（B8*E8－C8*D8）／（B8*F8－C8＾2），1）”。

5．字符运算提示信息。在C11单元格键入“＝＂Y＝＂＆B9＆＂＋＂＆B10＆＂X＂”。

6．设计组合框，方法如下：

（1）选择菜单“视图／工具栏／窗体”命令，调出窗体工具栏，如图1上部所示。

（2）在窗体工具栏上单击组

合框

按钮，此时鼠标变“十”

字状，在B12单元格拖动出组合框控件，该控件处于编辑状态（按钮上有6个或8个圆圈的控制柄）时，如图2所示，调整其大小和位置。

（3）在G1：G13单元区域中，按时间顺序键入需要预测的年度文字。

（4）右击B12单元的控件选择“设置控件格式”命令进入如图3所示的界面。在数据源区域键入“SGS1：SGS13”（绝对引用

＝COUNT（value1，value2，…）

式中，value表示包含或引用的数字型数据的参数，可以设置1至30个参数。

【案例1】某公司甲产品近5年销量如表1所示。要求用回归直线法预测该产品未来13年的销售情况。

表1甲产品近5年销量

年销

度量

单位：万台

2004176

2005180

2006

189

2007197

2008203

G1：G13单元区域），单元格链接中键入“SBS12”（绝对引用B12单元格），下拉显示项数中键入“5”，并勾选“三维阴影”选项。

【模型构建】构建的销售预测模型如图1所示。单击B12单元格中的组合框下拉箭头，在列表中选定某年度，该年度的销量预测即可显示于D12单元格中，并在B13单元格中进行提示。该模型的设计方法如下：

1．设计表格。键入文字、已知数据，合并单元格区域等。

2．求和、乘积与幂运算。在E3单元格中键入“＝C3*D3”，F3单元格中键入“＝C3＾2”，C8单元格中键入“＝SUM（C3：C7）”；再自动填充E4：E7、F4：F7和D8：F8单元区域的公式。

图3设置组合框控件格式

（5）单击设置控件格式中的“确定”按钮回到工作表界面，

B12单元格左部显示“1”，这是组合框的选项代码；它依“数据源区域”中单元格的相对位置顺序而定，如单击组合框并选“2021年”，将显示代码“13”。

B12和G1：G13区域的数据是组合框的设计代码，属于干扰数据，可将其隐藏。方法是选定B12单元格，单击格式工具栏的

居

中

按钮，使该代码置于组合框的后部而隐藏；右击G列，

在快捷菜单中选择“隐藏”命令。

图1销售预测回归模型

3．确定观察值项数。选定B8单元格，键入“＝COUNT（B3：B7）”，如图2所示。由于引用区域有5年，所以计算结果为“5”。

7．在D12单元格中键入Y＝a＋bX的预测公式“＝B9＋B10*（C7＋B12）”；在B13单元格中键入信息的动态提示公式“＝

8．选择“工具／选项”菜单命令，在“视图”选项卡中勾选“公式”复选框，则单元格中显示公式，如图2所示；取消该选项，则单元格中显示计算结果，如图1所示。

三、资金回归分析的模型构建

Excel回归分析时，可采用线性方程截距函数Intercept确定参数a的值，斜率函数Slope确定参数b的值，然后用Y＝a＋bX

预测资金占用额。参数公式如下：

＝Intercept（known＿y’s，known＿x’s）＝Slope（known＿y’s，known＿x’s）

式中，Known＿y’s表示因变量数据组或单元区域；Known＿x’s

表示自变量数据点集合。

【案例2】某公司近6年销售量与资金占用额如表2所示。

图2销售预测回归模型（显示公式）

该公司未来的销售量可能为279万～379万台，请用回归直线法

12/

CHINAMANAGEMENTINFORMATIONIZATION

财务管理

预测其资金占用额。

表2产销量与资金占用额

年

度

将其作为Index的嵌套函数，以返回这些参数值；然后用Y＝a＋

2004241198

[***********]09249

210

259206

263210

269213

278220

产销量（万台）资金额

（万元）

【模型构建】构建的资金占用预测模型如图4所示。单击C8单元格的微调按钮，D8单元格将显示不同的产销量，G8单元格将预测出该产量下的资金占用额，B9单元格进行信息的动态提示。该模型设计方法如下：

1．设计表格、键入已知数据等。2．计算参数a、b值，方法如下：

（1）计算参数a的方法是：在C5单元格键入公式“＝Intercept（C4：H4，C3：H3）”。

（2）计算参数b的方法是：在C6单元格键入公式“＝Slope（C4：H4，C3：H3）”。

bX预测成本费用。参数的函数公式如下：

b＝Index（Linest（known＿y’s，known＿x’s，true，true），1，1）a＝Index（Linest（known＿y’s，known＿x’s，true，true），1，2）R2＝Index（Linest（known＿y’s，known＿x’s，true，true），3，1）直线拟合函数Linest参数：known＿y’s表示因变量；known＿x’s表示自变量；第三个参数设为TRUE或空置表示按正常的a值计算，若设为FALSE则将a设为0；第四个参数设为TRUE表示要计算相关系数R2、a和b的值，若设为FALSE则只计算a和b的值。

数值引用函数Index参数：“1，1”返回Linest中的b值；“1，2”返回Linest中的a值；“3，1”返回Linest中的R2值。

相关系数R2的值为1则完全正相关，为－1则完全负相关，为0则不相关，大于0．8则显著相关，在0．5～0．8之间则相关，0～0．5则弱相关。只有相关系数大于0．5时，回归直线法的预测结果

才具有参考价值。

【案例3】某公司近6年销售收入与成本费用总额如表3所示。要求判断两者之间的相关程度，并预测未来10年不同销售额（最高4000万元）对应的成本费用。

表3某公司近收入与成本费用

年

度

单位：万元

[**************]6

[1**********]9

[1**********]8

[1**********]1

[1**********]7

销售收入2216成本费用1590

【模型构建】构建的成本费用预测模型如图6所示，单击

图4资金占用回归预测模型（显示公式）

3．设计微调按钮，方法如下：（1）单击窗体工具栏的微调

项

按钮，见图1上部，此时

鼠标变“十”字状；在C8单元格拖动一个控件。

（2）右击控件选“设置控件格式”命令进入设置控件格式界面，如图5所示；在最小值中键入“1”，最大值中键入“100”，步长中键入“1”，单元格链接中键入“SCS8”（绝对引用C8），勾选“三维阴影”选项。

B10单元格的微调按钮以确定预测年度并显示于C10单元格；单击F10单元格的滚动条左右箭头将增减1，单击滚动条空白处将增减10，以确定该年的销售额并显示于H10单元格；第11行自

动提示该年成本费用的预测值。设计方法如下：

图6成本费用回归预测模型

图5微调项控件格式设置

（3）单击“确定”按钮回到工作表界面，C8单元格显示代码“1”（当前值或最小值），单击微调按钮的上下箭头，代码值在1～

1．设计表格。录入文字，键入前4行的已知数据，合并单元格

区域等。

100之间变动；选定C8单元格将其格式设置为居中对齐，使代码

值隐藏于微调按钮后部。

4．预测资金占用额。在D8单元格键入“＝H3＋C8”；在G8单元格键入“＝C5＋C6*D8”，这是回归资金占用预测公式。

5．字符运算提示信息。在C7单元格键入“＝＂Y＝＂＆C5＆＂＋＂＆C6＆＂X＂”；在B9单元格键入“＝B8＆D8＆＂万台时，＂＆E8＆G8＆＂万元＂”，以提示不同产销量下的资金占用额。

四、成本费用回归分析的模型构建

2．计算相关参数。在B5单元格中键入“＝Index（Linest（C4：H4，C3：H3，TRUE，TRUE），1，2）”，这是相关系数R2的函数公式；在B7单元格键入预测直线的截距参数a的函数公式“＝Index（Linest（C4：H4，C3：H3，TRUE，TRUE），1，2）”；B8单元格键入斜率参数b的函数公式“＝Index（Linest（C4：H4，C3：H3，TRUE，TRUE），1，1）”。

3．参数信息提示。在B6单元格中键入“＝IF（C5＞0．8，＂显著＂，IF（C5＞0．5，＂相关＂，＂弱＂））”，以提示相关程度；B9单元格中键入“＝＂Y＝＂＆Round（C7，2）＆＂＋＂＆Round（C8，2）＆＂X＂”，以提示带参数

的预测公式。

CHINAMANAGEMENTINFORMATIONIZATION

Excel回归分析时，可用直线拟合函数Linest计算参数值，再

/13

2011年3月第14卷第6期

ChinaManagementInformationization

中国管理信息化

Mar.，2011Vol．14，No．6

我国上市公司内部控制信息披露存在的问题及对策分析

崔淑芬

（武汉大学东湖分校，武汉430070）

［摘

要］目前，上市公司会计信息披露的研究已具有一定的广度，但在深度上还存在一定的不完备性和局限性，没有把与

内部控制相关的问题结合起来进行全面的分析研究。本文针对上市公司内部控制会计信息披露问题进行研究，立足于我国上市公司会计信息披露理论、基本框架和实践的现状，分析了上市公司内控信息披露中存在的诸多问题，并在此基础上对上市公司内控信息披露不规范的成因进行论述，最终提出切实可行的解决对策。

［关键词］上市公司；会计信息；信息披露；内部控制

doi:10.3969/j.issn.1673-0194.2011.06.007［中图分类号］F276.6［文献标识码］A［文章编号］1673-0194（2011）06-0014-03

会计信息具有信息所拥有的基本属性，它把企业财务状况、经营成果和现金流量等信息，传递给同该企业有着利益关系的其他相关者，让更多的人能够参与企业的规划与发展。同时，会计信息披露制度是随着合伙制企业的盛行而出现的，会计信息披露制度首先是整个会计行业的自律性规范。在我国，随着企业所有权与经营权逐渐分离，公司制企业出现。在契约关系约束下的会计信息，包括内部控制信息，都由企业经营者生产并交付企业所有者使用，因此会计信息披露的对象也非常有限。上市公司作为公开公司，必须定期向社会公布其经营情况、财务状况和资产变动情况，使企业处于社会监督和压力之中。

除应重点提供历史和现在关于内部控制情况的信息之外，还应提供尽可能多的关于内部控制的预测信息，这样会对投资者的投资决策给予更大的帮助。同时，非数量性信息对于投资者和债权人的重要性和相关性取决于决策上的需要程度。比如会计政策的披露，应包括在编报财务报表时，管理当局所采用的原则、基础、惯例、规则和程序，有助于说明特定会计主体的财务报表［2］。随着公司的多种经营和国外业务的发展，使财务内部控制相关信息的聚合问题更为突出。在这种情况下，上市公司除了编制呈报总公司及各子公司的财务报表外，还需要编制分部与内部控制相关的财务报表，并予以披露。分部信息披露的内容、数量、途径等，主要取决于公认会计原则的要求、信息使用者的要求及影响、管理部门的需要。

1上市公司内部控制信息披露的内容及意义1．1上市公司内部控制信息披露的内容

一方面，由于有效的内部控制可以保护公司财产物资的安全、完整，保证公司会计资料的可靠性和正确性，保证公司经济活动的合法性与效益性，关系到广大投资者的利益，关系到资本市场的健康发展；另一方面，我国出现的如银广夏、中航油等舞弊案，都是因为公司的内部控制不健全造成的，对上市公司公开披露内部控制信息的要求也越来越高［1］。上市公司披露会计信息的内容涉及各个方面，而且要求在披露中正确地揭示财务数据和其他有关信息。披露的途径主要通过资产负债表、收益表、财务状况变动表3张财务报表和其他说明材料。财务报表的主要目的是提供投资者和债权人做出决策所需要的数量性数据，公司披露的是具有稳健意义、综合性的信息，公司披露的会计信息

［收稿日期］2011-02-14

1．2上市公司内部控制信息披露的意义

内部控制信息披露是信息披露的一个重要组成部分，是实现组织目标的手段，其根本作用在于保证事态的发展符合计划的要求。由于内部控制的重要性，投资者也越来越关注公司内部控制的状况，客观、真实地披露公司内部控制信息就显得尤为重要。上市公司内部控制信息披露有利于投资者及其他人进行决策。他们需要了解上市公司更多的信息才能做出判断。内部控制是对企业营运的效率和效果、财务报告的可靠性、相关法令的遵循性等目标提供合理保证的过程，可以通过内部控制信息判断其经营管理状况和财务报告的可靠性［3］。同时，内部控制信息披露可以提高企业财务报告的可靠性。内部控制信息披露可以向外部使用者提供财务报告以外的增量信息。内部控制报告提供了额外的对决策有用的信息，用户可以通过它在一定程度上了解企业管理控制是

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

4．在B10单元格设计微调按钮，最小值为“1”，最大值为“10”，步长为“1”，单元格链接为“SBS10”；居中隐藏设计代码。

5．设计滚动条，方法如下：

（1）单击窗体工具栏的滚动

条按钮，在F10单元格中拖

动一个滚动条控件，并调整其大小和位置。

（2）右击该控件选“设置控件格式”命令，在最小值、最大值、步长、页步长中键入1、1280、1、10，单元格链接中键入“SFS10”，勾选“三维阴影”。

（3）单击“确定”按钮回到工作表界面，F10单元显示“1”，这是滚动条的设计代码；将其居中对齐隐藏于滚动条后部。

［1］彭韶兵．管理会计学［M］．成都：西南财经大学出版社，2007．

［2］王顺金，庄小欧．Excel财务与会计应用精粹［M］．北京：北京理工大学

出版社，2009．

［3］王顺金，邹俊霞．财务管理［M］．北京：北京理工大学出版社，2009．［4］徐兴恩．管理会计学［M］．成都：西南财经大学出版社，2002．

F10”，提示该年的销售额是多少；在B11单元格中键入“＝H2＋

B10＆＂年成本费用额＂”，提示要预测哪一年的成本费用；在E11单元格中键入在计算时四舍五入并保留两位小数的直线公式“＝Round（C7＋C8*H10，2）”，显示该年的成本费用总额。

主要参考文献

4．信息提示与预测。在C10单元格中键入“＝H2＋B10＆＂年销售额＂”，提示以哪一年为预测期；在H10单元格中键入“＝H3＋

14/

CHINAMANAGEMENTINFORMATIONIZATION