集團公司核對關聯方交易的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.