Excel在会计与财务中的应用课件：Excel在会计凭证中的应用

5.1编制资产负债表5.2编制利润表5.3专家设置

Excel在会计凭证中的应用5.1编制资产负债表5.1.1设置资产负债表格式5.1.2填制资产负债表2024/4/182资产负债表一般采用账户式结构，分为左右两方，左方为资产，右方为负债和所有者权益。资产负债表左右双方平衡，资产总计等于负债和所有者权益总计，即“资产=负债+所有者权益”。具体操作步骤如下。步骤1：在“科目余额表”后新建工作表，重命名为“资产负债表”。步骤2：选中A1:F1单元格区域，单击“合并后居中”按钮。在A1单元格输入“资产负债表”，并单击加粗按钮。5.1.1设置资产负债表格式步骤3：使用相同的方法，并参照资产负债表基本格式，在每个单元格输入指定的内容。步骤4：设置边框，并调整字体，使得各类项目能够区分清楚；将B4:C38和E4:F38单元格设置为“会计专用”格式。编制好的资产负债表如图所示。5.1.1设置资产负债表格式由于B传媒公司刚成立，此处以填制月度报表为例，8月份为期末，7月份为期初。实务中若制作年度报表，方法相同，只是时间不同，本年度为期末，上一年度为期初。资产负债表各项目的数据来源方式有以下五种：方式一：根据科目余额直接填列。如交易性金融资产、应收票据、短期借款等。方式二：根据科目余额表计算填列。如货币资金=库存现金+银行存款+其他货币资金。方式三：根据科目余额表计算填列。如应收账款、预收账款、应付账款、预付款项等。方式四：根据科目余额表计算填列，如长期借款、应付债券等。方式五：根据科目余额表减去其备抵项目后的净额填列，如应收账款、持有至到期投资和无形资产等。5.1.2填制资产负债表依照项目的数据来源方式，可以采用数据链接直接引用的方式，引用科目余额表等工作表的相关数据进行资产负债表的编制，也可以采用SUMIF函数和VLOOKUP函数等函数间接调用科目余额表等工作表的相关数据来填制资产负债表。1.货币资金步骤1：B5单元格输入公式：=科目余额表!H3+科目余额表!H4+科目余额表!H5，如图所示，即：货币资金=库存现金+银行存款+其他货币资金。5.1.2填制资产负债表1.货币资金步骤2：C5单元格输入公式：=科目余额表!D3+科目余额表!D4+科目余额表!D5，如图所示。由于本案例相当于计算月度报表，故7月份数据作为期初余额。5.1.2填制资产负债表2.应收账款“应收账款”项目的“期初余额”与“期末余额”应根据科目余额表计算填列。“应收账款”项目，应根据“应收账款”和“预收账款”科目所属各明细科目的期末借方余额合计数，减去“坏账准备”科目中有关应收账款计提的坏账准备期末余额后的金额填列步骤1：打开“资产负债表”，选中B8单元格,输入公式：=科目余额表!H8+科目余额表!H51-科目余额表!I13，按“Enter”键,即可求出“应收账款”项目的“期末余额”,如图所示。5.1.2填制资产负债表2.应收账款步骤2：C8单元格输入公式：=科目余额表!D8+科目余额表!D51-科目余额表!E13，如图5.5所示。5.1.2填制资产负债表3.预付账款“预付款项”项目，应根据“预付账款”和“应付账款”科目所属各明细科目的期末借方余额合计数，减去“坏账准备”科目中有关预付款项计提的坏账准备期末余额后的金额填列。步骤1：假设B企业预付账款不计提坏账准备，选择B9单元格录入公式：B9=科目余额表!H9+科目余额表!H50，即可求出“预付账款”项目的“期末余额”,如图所示。5.1.2填制资产负债表3.预付账款步骤2：C9单元格输入公式：=科目余额表!D9+科目余额表!D50，如图5.7所示。5.1.2填制资产负债表4.存货注：本案例假定该公司不做存货跌价准备。步骤1：B13单元格输入公式：=SUM(科目余额表!H15:H23)，按“Enter”键，即可求出“存货”项目的“期末余额”，如图所示。5.1.2填制资产负债表4.存货步骤2：选中C13单元格，输入公式：=SUM(科目余额表!D15:D23)，即可求出“存货”项目的“期初余额”，如图所示。5.1.2填制资产负债表注意，其他流动资产类项目的填列可直接选择科目余额表中的对应项目，如图所示。5.1.2填制资产负债表5.流动资产合计步骤1：B16单元格，输入公式“=SUM(B5:B15)”，按“Enter”键，即可求出“流动资产合计”项目的“期末余额”，如图所示。5.1.2填制资产负债表5.流动资产合计步骤2：C16单元格，输入公式“=SUM(C5:C15)”，按“Enter”键，即可求出“流动资产合计”项目的“期初余额”，如图所示。5.1.2填制资产负债表6.固定资产（1）固定资产原值步骤1：B23单元格输入公式：=科目余额表!H34，如图所示。5.1.2填制资产负债表6.固定资产（1）固定资产原值步骤2：C23单元格输入公式：=科目余额表!D34，如图所示。5.1.2填制资产负债表6.固定资产（2）累计折旧步骤1：B24单元格输入公式：=科目余额表!H35，如图所示。5.1.2填制资产负债表6.固定资产（2）累计折旧步骤2：C24单元格输入公式：=科目余额表!E35，如图所示。5.1.2填制资产负债表6.固定资产（3）固定资产净值步骤1：B25单元格输入公式：=B23-B24，如图所示。5.1.2填制资产负债表6.固定资产（3）固定资产净值步骤2：C25单元格输入公式：=C23-C24，如图所示。5.1.2填制资产负债表6.固定资产（4）固定资产减值准备步骤1：B26单元格输入公式：=科目余额表!I36，如图所示。5.1.2填制资产负债表6.固定资产（4）固定资产减值准备步骤2：C26单元格输入公式：=科目余额表!E36，如图所示。5.1.2填制资产负债表6.固定资产（5）固定资产净额步骤1：B27单元格输入公式：=B25-B26，如图所示。5.1.2填制资产负债表6.固定资产（5）固定资产净额步骤2：C27单元格输入公式：=C25-C26，如图所示。5.1.2填制资产负债表5.1.2填制资产负债表7.非流动资产合计步骤1：B37单元格，输入公式“=SUM(B18:B22,B27:B36)”，按“Enter”键，即可求出“非流动资产合计”项目的“期末余额”，如图所示。5.1.2填制资产负债表7.非流动资产合计步骤2：C37单元格，输入公式“=SUM(C18:C22,C27:C36)”，按“Enter”键，即可求出“非流动资产合计”项目的“期初余额”，如图所示。5.1.2填制资产负债表8.资产总计步骤1：B38单元格输入公式：=B16+B37，如图所示。步骤2：C38单元格输入公式：=C16+C37，如图所示。5.1.2填制资产负债表9.应付账款、预收账款“应付账款”项目，应根据“应付账款”和“预付账款”科目所属的相关明细科目的期末贷方余额合计数填列；“预收账款”项目，应根据“应收账款”和“预收账款”科目所属的相关明细科目的期末贷方余额合计数填列。步骤1：选择E8单元格，录入公式：=科目余额表!I50+科目余额表!I9，如图所示。选择F8单元格，录入公式：=科目余额表!E50+科目余额表!E9，如图所示。9.应付账款、预收账款步骤2：选择E9单元格，录入公式：=科目余额表!I51+科目余额表!I8，如图所示。选择F9单元格，录入公式：=科目余额表!E51+科目余额表!E8，如图所示。5.1.2填制资产负债表10.应付职工薪酬步骤1：E10单元格输入公式：=科目余额表!I52，如图所示。5.1.2填制资产负债表10.应付职工薪酬步骤2：F10单元格输入公式：=科目余额表!E52，如图所示。5.1.2填制资产负债表11.流动负债合计步骤1：E19单元格，输入公式“=SUM(E5:E18)”，按“Enter”键，即可求出“流动负债合计”项目的“期末余额”，如图所示。步骤2：F19单元格，输入公式“=SUM(F5:F18)”，按“Enter”键，即可求出“流动负债合计”项目的“期初余额”，如图所示。5.1.2填制资产负债表12.负债合计步骤1：E28单元格输入公式：=E19+E27，如图所示。步骤2：F28单元格输入公式：=F19+F27，如图所示。5.1.2填制资产负债表13.实收资本步骤1：E31单元格输入公式：=科目余额表!I66，如图5.37所示。

步骤2：F31单元格输入公式：=科目余额表!E66，如图所示。5.1.2填制资产负债表14.未分配利润由于未到年末，在凭证表中只对7、8月份进行了损益结转，结转至“本年利润”，并没有将“本年利润”余额结转至“未分配利润”账户中。因此“未分配利润”不能仅根据本科目余额填列，其填写金额可以利用科目余额表中“本年利润”的余额与“未分配利润”之和进行填列。选择E34单元格，录入公式：=科目余额表!I69+科目余额表!I70，如图所示。选择F34单元格，录入公式：科目余额表!E69+科目余额表!E70，如图所示。

5.1.2填制资产负债表15.所有者权益合计步骤1：E36单元格，输入公式“=SUM(E31:E35)”，按“Enter”键，即可求出“所有者权益合计”项目的“期末余额”，如图所示。

5.1.2填制资产负债表15.所有者权益合计步骤2：F36单元格，输入公式“=SUM(F31:F35)”，按“Enter”键，即可求出“所有者权益合计”项目的“期初余额”，如图所示。

5.1.2填制资产负债表16.负债和所有者权益合计步骤1：E38单元格输入公式：=E36+E28，如图所示。

步骤2：F38单元格输入公式：=F36+F28，如图所示。5.1.2填制资产负债表16.负债和所有者权益合计负债和所有者权益中未说明的各项目均可直接从“科目余额表”中进行匹配，完整的B公司8月份资产负债表如图所示。5.1.2填制资产负债表5.2编制利润表5.2.1设置利润表的格式5.2.2填制利润表2024/4/1842步骤1：“资产负债表”后面的“新工作表”按钮，增加一张新的工作表，将其重命名为“利润表”。步骤2：打开“利润表”工作表，输入表格标题、表头信息，并根据当前实际情况输入利润表的各个项目。步骤3：对建立的表格进行边框设置、文字字体设置、对齐方式设置等，得到设置好格式的利润表，如图所示。5.2.1设置利润表的格式利润表的数据来自科目汇总表，在建立好的科目汇总表基础上，可以根据公式自动计算填制利润表。1.本月数本月数就是本期发生额，故而需要引用“科目汇总表”中的数据。步骤1：计算“营业收入”项目的“本月数”，“营业收入”等于“主营业务收入”与“其他业务收入”之和，由于报表将作为B公司以后各月的模板，故无论当月“主营业务收入”和“其他业务收入”是否有发生额，都应当将公式设置完整，便于后期使用。选中B4单元格，输入公式：=科目汇总表!D75+科目汇总表!D76，如图所示。5.2.2填制利润表步骤2：计算“营业成本”项目的“本月数”，“营业成本”等于“主营业务成本”与“其他业务成本”之和。选中B5单元格，输入公式：=科目汇总表!C80+科目汇总表!C81，如图所示。5.2.2填制利润表利润表中其他各项可根据科目汇总表对应项目直接填列，假设该小型微利企业暂无“信用减值损失”，其中：“投资收益”、“公允价值变动收益”以及“资产减值损失”均以贷方发生额与借方发生额的差额填列，达到损失以负号填列的目的。如图所示。5.2.2填制利润表步骤3：计算“营业利润”项目的“本月数”，选中B15单元格，输入公式：=B4-SUM(B5:B10)+SUM(B11:B14)，如图所示。5.2.2填制利润表步骤4：计算“利润总额”项目的“本月数”，选中B18单元格，输入公式：=B15+B16-B17，如图所示。5.2.2填制利润表步骤5：计算“所得税费用”项目的“本月数”。选中B19单元格，输入公式：=B18*12.5%*20%，如图所示。5.2.2填制利润表步骤6：计算“净利润”本月数。选中B20单元格，输入公式：=B18-B19，如图所示。5.2.2填制利润表2.本年累计数利润表中的“本年累计数”是指从本年1月份起至本月份止若干月份累计实现的利润数，即“本年累计数”应该等于上月利润表“本年累计数”加上本月利润表本月数。这样需要建立起上月利润表与本月利润表的链接，进行数据的调用。本案例的启用日期是2020年7月，业务以8月份为主，故没有7月份利润表数据，因此，通过制作7月份科目汇总表汇总7月份发生额数据，用于填制利润表的本年累计数。5.2.2填制利润表2.本年累计数步骤1：设置7月份科目汇总表。点击“科目汇总表”标签，同时按“Ctrl”键向右拖动，完成科目汇总表的复制，生成“科目汇总表（2）”，将其名称修改为“科目汇总表7月”，将原有科目汇总表的表页名称修改为“科目汇总表8月”，如图所示。5.2.2填制利润表2.本年累计数步骤2：根据本章已知7月份经济业务，填写7月份科目汇总表。如图所示。5.2.2填制利润表2.本年累计数步骤3：录入“本年累计数”。“本年累计数”等于当期8月份发生额对应加上7月份发生额。公式、利润表数据如图所示。5.2.2填制利润表5.3专家设置5.3.1将工作簿设置为“始终以只读方式打开”5.3.2在“信息”菜单中设置工作簿打开密码2024/4/1855工作簿编辑完成后，可以通过下述操