Excel在财务中的应用 第3版 课件 项目三 Excel在财务报表中的应用

Excel在财务报表中的应用项目三任务一编制资产负债表任务二编制利润表和损益表任务三搭建财务报表分析框架编制资产负债表o1任务一编制资产负债表（1）表头，包括报表的标题、编制单位、编制日期及计量单位等；（2）表体，一般为账户式，即左边为资产类项目，右边为负债类及所有者权益类项目；（3）表尾，一般为编制报表的相关责任人。1.设置资产负债表的基本格式(1)表头选中E3:G3区域，合并单元格后输入日期公式“=IF(OR(封面!F6=4,封面!F6=6,封面!F6=9,封面!F6=11),

DATE(封面!D6,封面!F6,30),IF(封面!F6=2,DATE(封面!D6,2,28),DATE(封面!D6,封面!F6,31)))”公式可以跨工作簿引用，其引用格式为“=[SUMIF(S).XLSX]工作表名!单元格地址”，即工作簿名称需要放在方括号中间。

如果被引用的工作簿已关闭，还需要在工作簿前增加路径，且需在半角模式下的单引号将文件全名及路径、工作表名引起来。例如该文件存在于D盘，则引用公式为“=‘D:\[工作簿名]工作表名’!单元格地址”。知识技能3-1：跨表引用技巧(2)表体由于字体较小，具体请参考教材(3)表尾选中B46：D46，合并单元格，输入“="单位负责人："&封面!D8”，选中E46：F46，合并单元格，输入“="财务负责人："&封面!D10”，选中G46：I46，合并单元格，输入“="编表人："&封面!D12”2.设置资产类科目的数据

资产负债表数据包括资产类、负债类和所有者权益类三大项数据，每项又包括年初数和期末数。

一般来说，年初数就是上一期的期末数，可以直接从上一期的资产负债表中复制过来即可，也可以从税务系统中将自动生成的年初数复制过来。对于期末数，则需要通过SUMIF函数来实现。（1）输入货币资金的期末数=SUMIF(总账汇总表!J:J,"<1100",总账汇总表!G:G)其中，第二参数"<1100"是一个逻辑关系式，意思是从科目编码中找到"<1100"这个条件的所有科目，将它们的期末借方余额相加。2.设置资产类科目的数据知识技能3-2：快速将文本格式转换为数值格式在“总账汇总表”工作表中，复制A列文本格式的科目编码，鼠标右键在J列上点击“选择性粘贴”。（2）输入交易性金融资产的期末数=SUMIF(总账汇总表!B:B,B7,总账汇总表!G:G)

找到和该资产负债表B7单元格的名称相符的科目后，计算出这个科目的借方余额也可写成：=SUMIF(总账汇总表!B:B,"交易性金融资产",总账汇总表!G:G)2.设置资产类科目的数据（3）输入应收票据的期末数暂不考虑坏账准备，公式简化为:=SUMIF(总账汇总表!B:B,"应收票据",总账汇总表!G:G)同上题理，也可直接取单元格内容：=SUMIF(总账汇总表!B:B,B9,总账汇总表!G:G)2.设置资产类科目的数据（4）输入应收账款的期末数=SUMIF(总账汇总表!B:B,"应收账款",总账汇总表!G:G)+SUMIF(总账汇总表!B:B,"预收账款",总账汇总表!G:G)-SUMIF(总账汇总表!B:B,"坏账准备",总账汇总表!H:H)其中，坏账准备是备抵科目，所以要从H列取数扣除。2.设置资产类科目的数据（5）输入预付款项的期末数=SUMIF(总账汇总表!B:B,"预付账款",总账汇总表!G:G)+SUMIF(总账汇总表!B:B,"应付账款",总账汇总表!G:G)2.设置资产类科目的数据（6）输入其他应收款的期末数暂不考虑坏账准备，公式简化为:=SUMIF(总账汇总表!B:B,"应收利息",总账汇总表!G:G)+SUMIF(总账汇总表!B:B,"应收股利",总账汇总表!G:G)+SUMIF(总账汇总表!B:B,B13,总账汇总表!G:G)

注：B13为“其他应收款”2.设置资产类科目的数据（7）输入存货项目的期末数存货项目是由多个科目汇总而成的，当需要同时满足多个条件时，需要用到SUMIFS函数。=SUMIFS(总账汇总表!G:G,总账汇总表!J:J,"<1500",总账汇总表!J:J,">1400")+SUMIF(总账汇总表!B:B,"生产成本",总账汇总表!G:G)

2.设置资产类科目的数据SUMIFS函数表示将J列中所有介于1400和1500之间的科目找出来，并将这些科目对应的G列的金额相加。知识技能3-3：SUMIFS函数含义：SUMIFS函数可以根据多个指定条件对若干单元格进行求和。

语法格式：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,

criteria2],

...)。其中：sum_range为实际求和区域，可以包括数字或包含数字的名称、区域或单元格引用，在计算过程中会忽略空白值和文本值。criteria_range1为计算关联条件的第一个区域。criteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本，可用来定义对criteria_range1参数中的哪些单元格求和。此外，SUMIFS函数和SUMIF函数一样，其条件可包含通配符，即进行模糊查找时可以用“?”代表单个字符，用“*”代表任何文字。知识技能3-3：SUMIFS函数

广东地区3月份共生产了多少A产品？=SUMIFS(D3:D21,B3:B21,3,A3:A21,”A产品”,E3:E21,”广东*”)（8）计算固定资产的期末数“=SUMIF(总账汇总表!B:B,B32,总账汇总表!G:G)-SUMIF(总账汇总表!B:B,"累计折旧",总账汇总表!H:H)-SUMIF(总账汇总表!B:B,"固定资产减值准备",总账汇总表!H:H)”+SUMIF(总账汇总表!B:B,"固定资产清理",总账汇总表!G:G)。2.设置资产类科目的数据（9）计算各个合计数流动资产合计=SUM(D6:D14)非流动资产合计=SUM(D25:D41)资产合计=D23+D422.设置资产类科目的数据（1）一般项目的取数与资产类项目的用意基本一致，只不过负债类和所有者权益类账户是从H列中进行取数。

选择单元格，输入公式“=SUMIF(总账汇总表!B:B,单元格,总账汇总表!H:H)”3.设置负债类及所有者权益类项目的数据（2）应付账款的期末取数选择H10单元格，输入公式“=SUMIF(总账汇总表!B:B

,

"应付账款",总账汇总表!H:H)+SUMIF(总账汇总表!B:B,"预付账款",总账汇总表!H:H)”3.设置负债类及所有者权益类项目的数据（3）预收款项的期末取数选择H11单元格，输入公式“=SUMIF(总账汇总表!B:B

,

"预收账款",总账汇总表!H:H)+SUMIF(总账汇总表!B:B,"应收账款",总账汇总表!H:H)”3.设置负债类及所有者权益类项目的数据（4）其他应付款的期末取数选择H15单元格，输入公式“=SUMIF(总账汇总表!B:B,"应付利息",总账汇总表!H:H)+SUMIF(总账汇总表!B:B,"应付股利",总账汇总表!H:H)+SUMIF(总账汇总表!B:B,F15,总账汇总表!H:H)”3.设置负债类及所有者权益类项目的数据（4）流动负债合计的计算“=SUM(H6:H18)”（5）应付债券（其中）的计算“=H23+H24”（6）非流动负债合计的计算“=SUM(H21:H22,H25:H29)”（7）负债合计的计算（8）其他权益工具（其中）的计算“=SUM(H19,H30)”“=H35+H36”3.设置负债类及所有者权益类项目的数据（9）未分配利润的期末取数“=SUMIF(总账汇总表!B:B,"本年利润",总账汇总表!H:H)+SUMIF(总账汇总表!B:B,

"利润分配",总账汇总表!H:H)-SUMIF(总账汇总表!B:B,"本年利润",总账汇总表!G:G)-SUMIF(总账汇总表!B:B,"利润分配",总账汇总表!G:G)”。3.设置负债类及所有者权益类项目的数据（10）所有者权益（或股东权益）合计的计算“=H33+H34+H37-H38+SUM(H39:H42)”（11）负债和所有者权益（或股东权益）合计的计算“=SUM(H31,H43)”3.设置负债类及所有者权益类项目的数据编制利润表和损益类科目汇总表o2任务二编制利润表和损益类科目汇总表1.设置利润表基本格式（1）创建工作表（2）设置利润表表体（3）设置利润表表体这个利润表是为了公司自用的，所以不要采用“一、二、三”“加、减”这些多余的符号，不利于函数取数。1.设置利润表基本格式2.设置利润表的本期金额（1）营业收入的期末取数

“=SUMIF(总账汇总表!B:B,"主营业务收入",总账汇总表!E:E)+SUMIF(总账汇总表!B:B,"其他业务收入",总账汇总表!E:E)”

从借方发生额中取“主营业务收入”和“其他业务收入”的本期发生额。（2）营业成本的期末取数

“=SUMIF(总账汇总表!B:B,”主营业务成本“,总账汇总表!E:E)+SUMIF(总账汇总表!B:B,”其他业务成本",总账汇总表!E:E)”

从借方发生额中取“主营业务成本”和“其他业务成本”的本期发生额。2.设置利润表的本期金额（3）其它损益类项目的期末取数

“=SUMIF(总账汇总表!B:B,单元格,总账汇总表!E:E)”，表示从借方发生额中单元格的本期发生额。

D16、D19和D21单元格是关于相关利润的计算，所以不适用前面的公式2.设置利润表的本期金额（4）设置相关利润的金额营业利润=D6-SUM(D7:D13)+SUM(D14:D15)利润总额=营业利润+营业外收入-营业外支出=D16+D17-D18净利润=利润总额-所得税费用=D19-D202.设置利润表的本期金额3.编制损益类科目汇总表（1）设置损益类科目汇总表的基本格式（2）输入1-11月份金额3.编制损益类科目汇总表（3）对12月份金额进行取数选择O5单元格（营业收入），输入=VLOOKUP("主营业务收入",总账汇总表!$B$1:$H$50,4,0)+VLOOKUP("其他业务收入",总账汇总表!$B$1:$H$50,4,0)3.编制损益类科目汇总表选择O6单元格（营业成本），输入=VLOOKUP("主营业务成本",总账汇总表!$B$1:$H$50,4,0)+VLOOKUP("其他业务成本",总账汇总表!$B$1:$H$50,4,0)其余单元格，输入“=IFERROR(VLOOKUP(单元格,总账汇总表!$B$1:$H$50,4,0),0)”回到C5单元格，输入“=SUM(D5:O5)”。取数后将鼠标拖曳向下填充。3.编制损益类科目汇总表（3）对12月份金额进行取数3.编制损益类科目汇总表（3）对12月份金额进行取数知识技能3-4：IFERROR函数含义：IFERROR函数可以捕获和处理公式中的错误。如果公式的计算结果为错误，则返回您指定的值；否则将返回公式的结果。

语法格式：IFERROR(value,value_if_error)。其中：value就是检查是否错误的参数，如果正确则返回该值的结果；如果错误则返回value_if_error的值。4.编制利润表累计数“利润表”的累计数需要从“损益类科目累计汇总表”中取数，因此只需在“利润表”工作表中利用VLOOKUP函数，查找并返回每项在“损益类科目累计汇总表”工作表中的累计金额即可。

以E6单元格为例，输入公式“=VLOOKUP(B6,损益类科目汇总表!$B$5:$C$17,2,0)”E16、E19、E21这3个单元格会呈现出“#N/A”无法找到的字样，因为这3个项目都是利润的计算公式。简单的处理是直接将D16、D19、D21这3个单元格的公式分别往右拖动。4.编制利润表累计数搭建财务报表分析框架o31.资产负债表分析模型（1）建立分析框架将表格右侧列示负债和所有者权益项目剪切到左边资产项目的下方，确保“行次”项数据顺序排列。1.资产负债表分析模型（2）结构分析

结构分析是以会计报表中的某个总体指标作为100%，再计算出其各组成部分占该总体指标的百分比，通过百分比的增减变动以了解有关财务活动的变化趋势。例如，对资产负债表进行数据结构分析时，以资产为100%设置权重；对利润表进行数据结构分析时，以营业收入作为100%基础。这种比较方法可以用于发现存在显著变化的项目。（2）结构分析（2）结构分析点击“货币资金”所对应的E6单元格，输入“=D6/$D$83”，其中D6就是货币资金的本年数，D83是“负债和所有者权益(或股东权益)总计”数据（当然也可以取D43单元格的“资产合计”数），因为是固定的总体指标，所以需要绝对引用。取数后将鼠标移到E6单元格右下角，双击填充柄，确保该列数据都已计算完毕。选中E整列，修改数据格式为百分比，并调整小数位数为两位。1.资产负债表分析模型（2）结构分析用同样的方法设置G列数据（3）数据比较分析会计报表的比较分析是将连续数期的会计报表金额并列起来，比较其相同指标的增减变动金额和幅度，据此判断企业财务状况和经营成果发展变化的一种方法。继续新增列H“增减变动金额”、列I“横向变动比例”、列J“纵向变动比例”，并在第4行对应位置附上相关计算公式“⑤=①-③”“⑥=⑤/③”“⑦=(②-④)/④”。