水晶报表位置公式_利用PBI制作集团合并财务报表模型

集团合并财务报表模型

财务人员在做集团合并财务报表时，一般用传统Excel工具，这种方法，效率较低，容易出错；部分企业用到财务软件合并报表。本章介绍利用Excel BI工具建立集团合并财务报表模型，案例数据来自2019年cpa教材，第 571页例27-17，为非同一控制下取得的子公司购买日后合并财务报表的编制，其中，甲公司为集团公司，A公司为子公司。

8.1 原始表格设计

本模型，我们需要用到甲公司、A公司的财务报表、基础表及调整抵消分录表。甲公司、A公司财务报表为两个工作簿，放在同一个文件夹内，资产负债表和利润表在一个表格中，分为“报表科目”、“金额”两列；基础表及调整抵消分录表的数据需要我们在模型所在的工作簿中录入。

8.1.1 基础表格式

基础表包括大类、分类、报表科目、借/贷、报表科目序号、大类序号6列。报表科目是教材例题中财务报表用到的科目。“借/贷”列值依据报表科目的性质：资产类、损益类的费用成本科目为“借”，负债、所有者权益、损益类的收益科目为“贷”。“大类序号”按“大类”的性质定：资产为1，负债为2，权益为3，权益分配为4，损益为5。“报表科目序号”的首位与该报表科目的大类序号一致，后面位数按次序依次增加，如下图所示。

8.1.2 抵消调整分录表格式及填写

抵消调整分录表包括报表科目、借/贷、抵调金额3列，是编制合并财务报表时需要写的抵消分录、调整分录。该表的抵消分录、调整分录均是课本例题中的分录。本模型最终目的是要自动生成合并资产负债表、合并损益表，该表中某一笔调整分录（或抵消分录）可能既涉及资产负债表科目，也涉及损益表科目，但损益类科目无法体现资产负债表的增减变动情况，我们需要把损益类的科目全部按“未分配利润”重述一遍（类似于财务每月的记账凭证，最后一笔要将本月损益类科目结转到资产负债科目-本年利润，才可以损益类的增减变动体现在资产负债表上）。

例如2号调整分录： 营业成本 借 1100

管理费用 借 150

应收账款 借 100

存货 贷 1100

固定资产 贷 150

资产减值损失 贷 100

该分录涉及“营业成本”、“管理费用”、“资产减值损失”三个损益类科目，无法体现资产负债表的变动情况，为此，我们以资产负债类科目“未分配利润”替代以上三个损益类科目，新增加以下三行内容，以体现资产负债表的变动。

未分配利润 借 1100

未分配利润 借 150

未分配利润 贷 100

特殊一点，如下图所示，5号抵消分录比较特殊，除“投资收益”科目外，其他5个科目既未涉及到资产负债表科目，也未涉及到损益表科目。此笔分录损益类科目“投资收益”不必调整为资产负债表科目“未分配利润”。

8.2 数据获取与清洗加工

我们以多工作簿合并的方式把处于同一个文件夹内的甲公司、A公司的财务报表加载到PQ中；鼠标放于基础表及调整抵消分录表上，单击“数据”-“从表格”，将两表格加载到PQ中。

在PQ中，基础表与调整抵消分录表合并后复制，原查询与复制后查询分别形成抵调分录-资产负债、抵调分录-损益两个查询；甲公司、A公司的财务报表合并后，再与基础表合，然后分别与抵调分录-资产负债、抵调分录-损益两个查询合并，最后形成非同一控制合并-资产负债、非同一控制合并-损益两个查询。

8.2.1抵调分录-资产负债、抵调分录-损益

在PQ右侧的“查询设置”-“属性”-“名称”（若页面不显示，单击“视图”-“查询设置”）处，修改“调整抵消分录”查询的名称为“抵调分录-资产负债”，并按照下图所示操作。

1. 单击“开始”-“分组依据”，以“报表科目”、“借/贷”为依据，合并“抵调金额”，如下图所示。

1. 单击“开始”-“合并查询”-“合并查询”， 出现“合并”对话框。在上面的复选框中，选择“抵调分录-资产负债”，并选中“报表科目”列，在下面的复选框中，选择“基础”，并选中“报表科目”，在下面“联接种类”对话框，选择“左外部（第一个中的所有行，第二个中的匹配行）”，单击“确定”，如下图所示。

1. 单击“基础”列左侧双箭头，展开“基础”表，仅勾选“借/贷”、“大类序号”两项，如下图所示。

1. 筛选“基础.大类序号”列，仅保留“1”、“2”、“3”。
2. 单击“添加列”-“自定义列”，添加“参数”自定义列，如下图所示。

1. 单击“添加列”-“自定义列”，添加“金额调整”自定义列，如下图所示。

小贴士：以上两步的意思是，抵消调整分录的借贷方与基础表的借贷方一致时，调整抵消的金额为正数，否则，为负数。

1. 单击“开始”-“分组依据”，以“报表科目”为依据，合并“金额调整”，如下图所示。

1. 鼠标放于左侧导航窗口的“抵调分录-资产负债”上，单击“管理”-“复制”，复制“抵调分录-资产负债”，并将复制后的查询名称改为“抵调分录-损益”，如下图所示。

1. “抵调分录-损益”查询与“抵调分录-资产负债”查询基本一致，唯一不同之处在筛选“基础.大类序号”时，仅保留“5”，如下图所示。

8.2.2非同一控制合并-资产负债、非同一控制合并-损益

甲公司、A公司的财务报表在PQ中合并，再分别与基础表、抵消调整分录表合并，最终形成非同一控制合并-资产负债、非同一控制合并-损益两个查询。进入PQ界面时，首先在 PQ右侧的“查询设置”-“属性”-“名称”（若页面不显示，单击“视图”-“查询设置”）处，将查询改为“非同一控制合并-资产负债”，如下图所示。

1. 在Excel界面，单击“数据”-“新建查询”-“从文件”-“从文件夹”，在“文件夹”对话框中，单击“浏览”，寻找甲公司、A公司的财务报表共同存放的文件夹“非同一控制合并”，然后，单击“确定”，如下图所示。

2. 在出现的以该文件夹路径名称为主题的对话框中，单击“编辑”，如下图所示。

3. 在PQ编辑器界面，单击“添加列”-“自定义列”，在“自定义列”对话框公式栏，输入（或粘贴）公式“Excel.Workbook([Content])”，如下图所示。

4. 依次进行以下操作：删除“自定义列”外的其他列、展开“自定义列”、删除除“自定义.Data”列外的其他列、展开“自定义.Data”、将表格字段名提升为标题行、仅保留一行标题行。

5. 合并同一“报表科目”的金额，如下图所示。

6. 单击“开始”-“合并查询”-“合并查询”， 出现“合并”对话框。在上面的复选框中，选择“非同一控制合并-资产负债”，并选中“报表科目”列，在下面的复选框中，选择“基础”，并选中“报表科目”，在下面“联接种类”对话框，选择“左外部（第一个中的所有行，第二个中的匹配行）”，单击“确定”，如下图所示。

7. 单击“基础”列左侧双箭头，展开“基础”表，勾选“大类”、“分类”、“借/贷”、“报表科目序号”、“大类序号”五项，“默认的列名前缀”为空白，如下图所示。

8. 筛选“大类序号”，仅保留“1”、“2”、“3”。

9. 单击“开始”-“合并查询”-“合并查询”， 出现“合并”对话框。在上面的复选框中，选择“非同一控制合并-资产负债”，并选中“报表科目”列，在下面的复选框中，选择“抵调分录-资产负债”，并选中“报表科目”，在下面“联接种类”对话框，选择“左外部（第一个中的所有行，第二个中的匹配行）”，单击“确定”，如下图所示。

1. 展开新增的“抵调分录”，仅保留“抵调金额”列；该列中“null”替换为“0”。
2. 单击“添加列”-“自定义列”，添加自定义列“合并金额”，公式如下图所示。

小贴士：此步与8.2.1的6步对应，当抵消金额为负数（抵消分录中该报表科目的借贷方与基础表中同类报表科目不一致）时，报表金额与抵调金额相减；当抵消金额为正数时，报表金额与抵调金额相加。

1. 合并相同五列的“合并金额”，即对“合并金额”合并同类项，如下图所示。

1. 鼠标放于左侧导航窗口的“非同一控制合并-资产负债”上，单击“管理”-“复制”，复制“非同一控制合并-资产负债”，并将复制后的查询名称改为“非同一控制合并-损益”，
2. “非同一控制合并-损益”查询与“非同一控制合并-资产负债”查询操作步骤基本一致，唯一不同之处，在筛选“大类序号”时，仅保留“5”，如下图所示。

8.3 建模与模型展现

本章案例只需要自动生成集团合并资产负债表、合并损益表，不需要做同比、环比分析，未建日期表；未建立度量值，只有增加两列简单的计算列。

8.3.1 建模

1. 在PP中“基础”表，选中“报表科目”列，单击“主页”-“排列顺序”-“按列排序…”，在“排序依据列”对话框，排序列选择“报表科目”，依据列选择“报表科目序号”，单击“确定”，如下图所示。

本操作的目的，是为了在数据透视表展现报表科目时按照既定的顺序排列。

2. 在PP中“非同一控制合并-损益”表，添加“分类参数”、“损益表金额”两列计算列，如下图所示。目的是损益类科目的成本费用等损失类以负值表示，以便“总计”栏直接得出最后的净利润数值。

3.“基础”表、“非同一控制合并-资产负债”表、“非同一控制合并-损益”表三个表中，“报表科目”列的值都是唯一值，我们建关系时，默认“基础”表是“一”方，其他表是“多”方， “关系图视图”建立表与表之间的关系时，我们从“非同一控制合并-资产负债”表、“非同一控制合并-损益”表向“基础”表的方向拉线建立“多对一”关系，如下图所示。

4. 在“关系图视图”中，单击基础表上方右侧第一个小方框，出现“层次结构1”，将“大类”、“分类”、“报表科目”依次拉到“层次结构1”，建立父子层级关系，如下图所示。

8.3.2 模型展现

我们把“报表科目”放于“行”位置，“合并金额”或“损益表金额”放于“值”位置，分别得出合并的资产负债表与合并的损益表，如下图所示。

（层次结构1

放于行位置，合并金额放于值位置，父子层级结构的数据可汇总可明细）

自动生成的报表与教材例题的结果完全一致，如下图所示。