集团公司核对关联方交易的Excel模型设计

刘刚

【摘 要】 如今，许多企业实施纵向一体化战略，形成集团化全产业链的经营模式，并发生大量的关联交易，这些关联交易抵消是否充分和准确直接对集团财务状况、经营成果和现金流量产生重大影响。针对A集团公司财务软件自动收集核对关联方交易数据的不足以致影响集团合并报表质量和时效性，创新运用Excel及其函数，以总账中引出的往来、主营业务收入、库存商品、现金流量等明细数据为基础，按照有关会计科目共享“客商”辅助核算以及记账凭证借贷对应关系，创建查找条件、数据源和提取关联交易数据，构建起关联方交易合并抵消数核对模型，低成本高效率地解决了A集团公司收集核对合并抵消数的问题，确保集团合并报表保质按时出具。

【关键词】 集团公司； 关联方； 数据源

【中图分类号】 F275.2 【文献标识码】 A 【文章编号】 1004-5937（2018）21-0111-08

随着信息化进程的加快，许多集团公司持续推进会计信息化，使用财务软件处理集团财会业务，甚至组建财务共享服务中心，将基础性会计工作交由计算机自动完成，一方面能够节约人力成本，另一方面提高工作效率。但是，购买用友或金蝶等国内知名财务软件中所有功能模块或者组建财务共享服务中心所需成本，对于有些集团公司来说难以承受。因此，这些集团公司财务软件往往只有总账、固定资产、报表等常用功能模块，如A集团公司。A集团公司使用的用友NC财务软件功能模块基本上能够满足业务需求，但也存在不能收集并核对关联方交易数据的缺陷，使得原合并报表编制人员花费大量时间和精力核对相关数据，影响出具合并报表的时效性和质量。为此，结合实际工作，以A集团公司为例，用其总账中引出的往来、主营业务收入、库存商品、现金流量等明细数据，运用Excel及其函数创建关联方交易合并抵消数核对模型，高效快捷地收集并核对内部交易数据，提高工作效率，确保保质按时出具合并报表。

一、A公司情况

（一）合并报表范围

A公司是一家从事能源行业的集团公司，旗下拥有两家控股子公司B、C公司，B公司旗下有两家控股子公司D、E公司，C公司旗下也有三家控股子公司F、G、H公司。A、B、C公司都能够对各自子公司形成控制，按照《企业会计准则第33号——合并财务报表》相关规定[1]，A公司应将B、C公司纳入其合并報表范围，同时，B、C公司也应将其子公司纳入合并报表范围。A集团公司经营模式为D、E公司从上游采购商品后销售给F、G、H公司，再由F、G、H公司在购进当月全部销售给下游用户。由此，D、E公司与F、G、H公司之间发生的交易构成关联方交易，A公司在编制合并报表时应将该部分收入和成本予以抵消。

（二）会计核算与报表编制

A集团公司内统一使用用友NC财务软件，启用了总账、固定资产和报表三个功能模块。总账模块中“银行存款”会计科目设置了“客商”和“现金流量项目”辅助核算，“应收账款”“应付账款”“预付账款”“预收账款”“其他应收款”“其他应付款”六大往来会计科目均设置了“客商”辅助核算。按惯例方法编制凭证，每份原始凭证（如购销发票、银行入账单等）依次编制记账凭证。如：

1.D公司确认销售给F公司收入的记账凭证

借：应收账款——F公司

贷：主营业务收入

应交税费——应交增值税（销项税额）

2.F公司编制采购D公司商品的记账凭证

借：库存商品

应交税费——应交增值税（进项税额）

贷：应付账款——D公司

3.D公司编制收到F公司货款的记账凭证

借：银行存款——F公司——销售商品、提供劳务收到的现金

贷：应收账款——F公司

4.F公司编制支付D公司货款的记账凭证

借：应付账款——D公司

贷：银行存款——D公司——购买商品、接受劳务支付的现金

每月A集团公司合并报表编制人员要等子公司B、C公司以及孙公司D、E、F、G、H公司报表编制好后，在NC报表模块中编制集团合并报表。虽然NC报表模块中设置了内部交易取数和抵消功能，大大减轻报表编制人员工作量，但也存在一些缺陷，如内部交易取数表不能收集并核对孙公司D、E公司与F、G、H公司之间发生的购销数据，也不能收集并核对所有公司与关联方之间发生的内部交易数据等。

二、核对关联方交易合并抵消数的模型设计

针对NC报表模块中内部交易取数存在的缺陷，运用财会人员使用率最高的数据处理软件Excel，对从D、E、F、G、H公司总账中引出的六大往来会计科目、主营业务收入、库存商品、现金流量等明细账数据进行整理，并结合函数创建核对模型，实现自动收集并核对关联方之间发生的购销业务收入成本抵消数据和其他内部交易数据。

（一）建立Excel工作簿

新建一个Excel工作簿，并在工作簿中新建五张工作表，分别命名为“核对模型”“往来余额和现金流量”“主营业务收入明细”“库存商品明细”“往来明细”。“往来余额和现金流量”工作表用来整理需抵消的D、E公司与F、G、H公司往来余额和现金流量金额；“主营业务收入明细”工作表用来整理需抵消的D、E公司销售金额；“库存商品明细”工作表用来整理需抵消的F、G、H公司购进金额；“往来明细”工作表作为“主营业务收入明细”和“库存商品明细”工作表的查找条件。

（二）Excel函数介绍

模型创建过程中需用到的函数有IF（）、VLOOKUP（）、IFERROR（）、SUMIFS（）、OR（）等函数以及这些函数间相互嵌套。如：

1.运用IF（）函数对往来科目进行重分类，IF（B3="应收账款"，IF（D3="借"，B3，"预收账款"））即应收账款科目余额方向为借方，不需重分类返回值“应收账款”，如科目余额方向不是借方，需重分类返回值“预收账款”。

2.运用VLOOKUP（）函数在“往来明细”工作表中查找出“主营业务收入明细”工作表中销售方对应的采购方，VLOOKUP（B5，往来明细！$A$4：$F$168，6，FALSE）即以B5单元格的值为查找条件，在“往来明细”工作表A4：F168区域第6列中查找对应的采购方，如无对应返回#N/A。

3.运用IFERROR（）函数在“核对模型”中提取往来余额和现金流量数据时返回具体金额或空值，IFERROR（VLOOKUP（$B4&$B$2&C;$3，往来余额和现金流量！$G$3：$K$35，5，FALSE），""）即关联方之间实际发生了内部交易数据，返回公式中VLOOKUP（）函数运算的内部交易具体金额，否则返回空值。

4.运用SUMIFS（）函数在“核对模型”中提取主营业务收入和库存商品数据，SUMIFS（主营业务收入明细！$J：$J，主营业务收入明细！$A：$A，核对模型！$B$14&核对模型！$B$2&核对模型！C$3）即从“主营业务收入明细”工作表中筛选加总关联方之间的主营业务收入数据，并将此运算结果返回到“核对模型”中相应的单元格。

5.运用OR（）函数并嵌套IF（）函数对NC总账引出的现金流量借贷方两列金额转换成一列金额，IF（OR（C22="收到的其他与经营活动有关的现金"，C22="销售商品、提供劳务收到的现金"），D22-E22，E22-D22）即现金流入项目的用借方金额减贷方金额，现金流出项目的用贷方金额减借方金额。

（三）创建取数数据源

第1步，创建往来余额数据源。

在“往来余额和现金流量”工作表中，将NC总账引出的往来余额明细粘贴到单元格A2：E18区域，并在单元格G2：K18区域新建往来余额数据源表（见表1），同时，在表头5个字段的计算单元格内输入有关函数公式。如：

G3单元格输入“=I3&H3;&J3;”；

H3单元格输入“=A3”；

I3单元格输入“=IF（B3="应收账款"，IF（D3="借"，B3，"预收账款"），IF（B3="预付账款"，IF（D3="借"，B3，"应付账款"），IF（B3="其他应收款"，IF（D3="借"，B3，"其他应付款"），IF（B3="预收账款"，IF（D3="贷"，B3，"应收账款"），IF（B3="应付账款"，IF（D3="贷"，B3，"预付账款"），IF（B3=

"其他应付款"，IF（D3="贷"，B3，"其他应收款"）））））））”；

J3单元格输入“=C3”；

K3单元格输入“=E3”，再将单元格G3：K3区域的公式以“粘贴公式”的方式粘贴到单元格G4：K18区域。

第2步，创建现金流量数据源。

在“往来余额和现金流量”工作表中，将NC总账引出的现金流量明细粘贴到单元格A21：E35区域，并在单元格G21：K35区域新建现金流量数据源表（见表2），同时，在表头5个字段的计算单元格内输入有关函数公式。如：

G22单元格输入“=I22&H22;&J22;”；

H22单元格输入“=A22”；

I22单元格输入“=C22”；

J22单元格输入“=B22”；

K22单元格输入“=IF（OR（C22="收到的其他与经营活动有关的现金"，C22="销售商品、提供劳务收到的现金"），D22-E22，E22-D22）”，再将单元格G22：K22区域的公式以“粘貼公式”的方式粘贴到单元格G23：K35区域。

第3步，创建查找条件。

在“往来明细”工作表中，将NC总账引出的往来明细粘贴到单元格B2：J168区域。根据记账凭证借贷科目对应关系，在往来明细中以“月”“日”“主体账簿”“凭证号”四个字段用文本连接符“&”组成查找条件，分别在主营业务收入明细和库存商品明细工作表中查找出D、E公司与F、G、H公司之间发生的主营业务收入和库存商品数据。因此，在A4单元格输入“=B4&C4;&D4;&G4;”，再将此公式以“粘贴公式”的方式粘贴到单元格A5：A168区域（见表3）。

第4步，创建主营业务收入数据源。

在“主营业务收入明细”工作表中，将NC总账引出的主营业务收入明细粘贴到单元格D2：J77区域，分别合并单元格A2：A3、B2：B3、C2：C3等区域，并输入“模型取数用条件”“查找条件”“采购方”等字段。“模型取数用条件”字段用于核对模型提取D、E公司销售给F、G、H公司的主营业务收入数据；“查找条件”字段用于匹配“往来明细”工作表中的“查找条件”，从而筛选出D、E公司销售给F、G、H公司的主营业务收入数据；“采购方”字段根据“查找条件”提取“往来明细”工作表中对应的采购方。同时，在这3个字段的计算单元格内输入有关函数公式。如：

A4单元格输入“="主营业务收入"&F4;&C4;”；

B4单元格输入“=D4&E4;&F4;&G4;”；

C4单元格输入“=VLOOKUP（B4，往来明细！$A$4：$F$168，6，FALSE）”，再将单元格A4：C4区域的公式以“粘贴公式”的方式粘贴到单元格A5：C77区域，并对C列进行排序把返回#N/A的行数据删除，从而得到D、E公司销售给F、G、H公司的主营业务收入数据源（见表4）。

第5步，创建库存商品数据源。

在“库存商品明细”工作表中，将NC总账引出的库存商品明细粘贴到单元格D2：J74区域，分别合并单元格A2：A3、B2：B3、C2：C3等区域，并输入“模型取数用条件”“查找条件”“销售方”等字段。“模型取数用条件”字段用于核对模型提取F、G、H公司从D、E公司采购的库存商品数据；“查找条件”字段用于匹配“往来明细”工作表中的“查找条件”，从而筛选出F、G、H公司从D、E公司采购的库存商品数据；“销售方”字段根据“查找条件”提取“往来明细”工作表中对应的销售方。同时，在这3个字段的计算单元格内输入有关函数公式。如：

A4单元格输入“="库存商品"&F4;&C4;”；

B4单元格输入“=D4&E4;&F4;&G4;”；

C4单元格输入“=VLOOKUP（B4，往来明细！$A$4：$F$168，6，FALSE）”，再将单元格A4：C4区域的公式以“粘贴公式”的方式粘贴到单元格A5：C74区域，并对C列进行排序把返回#N/A的行数据删除，从而得到F、G、H公司从D、E公司采购的库存商品数据源（见表5）。

（四）创建核对模型

在“核对模型”工作表中，依次合并单元格A1：M1、A4：A9、A10：A13、A14：A15等区域，并输入“A集团公司关联方交易合并抵消数核对模型”“六大往来”“现金流量”“收入成本”等字段，同时，在单元格C3：M3区域输入关联方D、E、F、G、H公司以及单元格B4：B15区域输入合并抵消项目。

第1步，创建“单位名称”下拉选择菜单。

运用数据工具中的数据有效性为B2单元格设置D、E、F、G、H公司下拉选择菜单，“允许”字段选择“序列”，“来源”字段输入“=$C$3：$G$3”。

第2步，输入表头次标题。

合并单元格C2：G2区域，并输入函数“=B2&"与以下关联方发生的交易数据（单位：元）"”，同时，合并单元格I2：M2区域，并输入函数“="以下关联方与"&B2;&"发生的交易数据（单位：元）"”。

第3步，提取往来余额和现金流量数据。

C4单元格输入“=IF（$B$2=C$3，""，IFERROR（VLOOKUP（$B4&$B$2&C;$3，往来余额和现金流量！$G$3：$K$35，5，FALSE），""））”，再将C4单元格的公式以“粘贴公式”的方式粘贴到单元格D4：G4和C5：G13区域。

I4单元格输入“=IF（$B$2=I$3，""，IFERROR（VLOOKUP（$B4&I;$3&$B$2，往来余额和现金流量！$G$3：$K$35，5，FALSE），""））”，再将I4单元格的公式以“粘贴公式”的方式粘贴到单元格J4：M4和J5：J13区域。

第4步，提取主营业务收入和库存商品数据。

C14单元格输入“=IF（$B$2=C$3，""，IF（SUMIFS（主营业务收入明细！$J：$J，主营业务收入明细！$A：$A，核对模型！$B$14&核对模型！$B$2&核对模型！C$3）=0，""，SUMIFS（主营业务收入明细！$J：$J，主营业务收入明细！$A：$A，核对模型！$B$14&核对模型！$B$2&核对模型！C$3）））”，再将C14单元格的公式以“粘贴公式”的方式粘贴到单元格D14：G14和C15：G15区域。

I14单元格输入“=IF（$B$2=I$3，""，IF（SUMIFS（主营业务收入明细！$J：$J，主营业务收入明细！$A：$A，核对模型！$B$14&核对模型！I$3&核对模型！$B$2）=0，""，SUMIFS（主营业务收入明细！$J：$J，主营业务收入明细！$A：$A，核对模型！$B$14&核对模型！I$3&核对模型！$B$2）））”，再將I14单元格的公式以“粘贴公式”的方式粘贴到单元格J14：M14和I15：M15区域。

至此，A集团公司内D、E公司与F、G、H公司之间发生的关联交易合并抵消数核对模型创建完成，详见表6。

在核对模型中，通过“单位名称”下拉菜单可以选择D、E、F、G、H公司中的任一家公司，即可提取到该公司与其他关联方发生的内部交易数据。如选择H公司，该公司与D、E、F、G公司发生的关联交易数据全部呈现在核对模型中。A集团公司在编制合并报表时，可以据此核对关联交易数据并编制合并抵消分录，如：

1.编制往来项目合并抵消分录

借：应付账款 331 550.51

预收账款 576 576.42

其他应付款 60 000

贷：应收账款 331 550.51

预付账款 576 576.42

其他应收款 60 000

2.编制现金流量项目合并抵消分录

借：购买商品、接受劳务支付的现金 6 897 905.45

支付的其他与经营活动有关的现金 60 000

贷：销售商品、提供劳务收到的现金 6 897 905.45

收到的其他与经营活动有关的现金 60 000

3.编制收入成本项目合并抵消分录

借：主营业务收入

7 116 235.19

贷：主营业务成本

7 116 235.19

三、结语

集团公司编制合并报表时，关联方之间发生内部交易是需抵消的重要组成部分，尚未实现软件自动收集并核对关联方交易合并抵消数的，运用Excel是一种高效快捷的方法。本文运用Excel创建的关联方交易合并抵消数核对模型，很好地解决了集团内部交易数据收集与核对的问题，适用性强。但是，如集团业务模式与本案例不同，则需根据实际情况对模型进行修改完善，以解决自身实务问题。

【参考文献】

[1] 财政部.企业会计准则第33号——合并财务报表[A].财会〔2014〕10号，2014.