武潇2015级第八讲学生讲的资料武潇

1、Excel 高级财会应用课程期末作业课程：Excel 高级财会应用 授课老师：陈 士 成 老师 学生： 武 潇 学号：220150910900承接课堂所学的知识，根据基础资料，通过数据透视表整理基础数据，在数据透视的的基础上设计资产负债表、利润表，并在此基础上做了财务部率分析和杜邦分析。1. 基础表选取课堂练习资料中的财务记账凭证，整理数据。2. 数据透视表具体步骤：1) 全选财务记账凭证数据2) 插入“数据透视表”3) 在“选择要添加到报表的字段”选取“日期”、“科目编号”、“借方”、“贷方”4) 将光标定于日期栏下具体日期处，选择“数据透视表”选项下“将字段分组”。在分组列表中，选择年和月

2、。5）添加月份字段： （1）将2010年12月记为数字的“0”月；（2）2011年的各月都按原月份记。6）设置数组变量（1）月份列设为“年月”；（2）科目编号（C5：C335）列设为“科目编号”；（3）借方汇总额（D5：D335）设置为“借”；（4）贷方汇总额（E5：E335）设置为“贷”；3插入工作表，创建空白“资产负债表”。添加报表抬头、添加和设置微调控件。“大图标”-“Excel选项”-“自定义”-在“从下列位置选择命令”框中选“不能在功能区的命令”-选“数值调节钮（窗体控件）”-“添加”-“确定”。此时在第一行的附加工具栏中就会显示控件 ，随后就可以调用。1）根据数据透视表核算资产负债

3、表数据透视表中的数据是记账凭证库中每月各科目的发生额和对应余额（借、贷相减）。而记账凭证库中只记录了13个月（2010年12月-2011年12月）的数据，而每月在报表中各科（利润表中的项目）都对应有年初数、月初数、月余数（当月余额）和期末数。所以编制报表时，每个行数据都要进行核算：对于2010年12月，是初始建账，所以年初数和月初数都是空（没有数据）；而月余数和期末数都是当月的科目余额，因此每行都按“资产负债表关系”中所述的关系进行核算。另外，该月的利润表全为空（因为是建账月没有经营）；对于2011年每月的报表，每行的年初数，都是2010年12月的月余数，月初数是该月前一个月的月余数（前一个月

4、以前的所有月份的总余额），月余数是按“资产负债表关系”中所述的关系进行核的余额值，期末数是该月以前所有月份的总余额。如：“货币资金”，“资产负债表关系”中描述的关系是，“货币资金=101现金 +102银行存款（借-贷）”，也就是说一行对应了两个科目（101和102）的数据，并且都是要核算其借贷方余额。所以就有以下核算方法。（1）货币资金年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,101)-SUMIFS(贷,年月,0,科目编号,101)+SUMIFS(借,年月,0,科目编号,102)-SUMIFS(贷,年月,0,科目编号,102)；月初数：=IF($E$2=10,SUM

5、IFS(借,年月, &$G$2,科目编号,101)-SUMIFS(贷,年月, &$G$2,科目编号,101)+SUMIFS(借,年月, &$G$2,科目编号,102)-SUMIFS(贷,年月, &$G$2,科目编号,102)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,101)-SUMIFS(贷,年月,0,科目编号,101)+SUMIFS(借,年月,0,科目编号,102)-SUMIFS(贷,年月,0,科目编号,102),SUMIFS(借,年月,=&$G$2,科目编号,101)-SUMIFS(贷,年月,=&$G$2,科目编号,101)+SUMIFS(借,年月,= &$G

6、$2,科目编号,102)-SUMIFS(贷,年月,=& $G$2,科目编号,102)；期末数=IF($E$2=10,SUMIFS(借,年月,0,科目编号,101)-SUMIFS(贷,年月,0,科目编号,101)+SUMIFS(借,年月,0,科目编号,102)-SUMIFS(贷,年月,0,科目编号,102),SUMIFS(借,年月,= &$G$2,科目编号,101)-SUMIFS(贷,年月,= &$G$2,科目编号,101)+SUMIFS(借,年月,= &$G$2,科目编号,102)-SUMIFS(贷,年月,= &$G$2,科目编号,102)。（2）应收账款年初数：=IF($E$2=10,SUM

7、IFS(借,年月,0,科目编号,113)-SUMIFS(贷,年月,0,科目编号,113)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科目编号,113)-SUMIFS(贷,年月,&$G$2,科目编号,113)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,113)-SUMIFS(贷,年月,0,科目编号,113),SUMIFS(借,年月,=&$G$2,科目编号,113)-SUMIFS(贷,年月,=&$G$2,科目编号,113)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,113)-SUMIFS(贷,年月,0,科目编号,11

8、3),SUMIFS(借,年月,=&$G$2,科目编号,113)-SUMIFS(贷,年月,=&$G$2,科目编号,113)。（3）其它应收款年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,119)-SUMIFS(贷,年月,0,科目编号,119)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科目编号,119)-SUMIFS(贷,年月,&$G$2,科目编号,119)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,119)-SUMIFS(贷,年月,0,科目编号,119),SUMIFS(借,年月,=&$G$2,科目编号,119)-SU

9、MIFS(贷,年月,=&$G$2,科目编号,119)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,119)-SUMIFS(贷,年月,0,科目编号,119),SUMIFS(借,年月,=&$G$2,科目编号,119)-SUMIFS(贷,年月,=&$G$2,科目编号,119)。其余略。4.编制利润表前期工作与编制资产负债表相同，插入工作表，创建空白“利润表”。添加报表抬头、添加和设置微调控件。（1）“主营业务收入”年初数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,501)；月初数：=IF($E$2=10,SUMIFS(贷,年月,&$G$2,科目编号,50

10、1)；月余数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,501),SUMIFS(贷,年月,=&$G$2,科目编号,501)；期末数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,501),SUMIFS(贷,年月,=&$G$2,科目编号,501)。（2）“主营业务收入净额”年初数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,501)；月初数：=IF($E$2=10,SUMIFS(贷,年月,&$G$2,科目编号,501)；月余数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,501),SUMIFS(贷,年月,=&$G$2,科目编

11、号,501)；期末数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,501),SUMIFS(贷,年月,=&$G$2,科目编号,501)。（3）“减：（一）主营业务成本”年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,502)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科目编号,502)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,502),SUMIFS(借,年月,=&$G$2,科目编号,502)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,502),SUMIFS(借,年月,=&$

12、G$2,科目编号,502)。（4）“（二）主营业务税金及附加”年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,504)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科目编号,504)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,504),SUMIFS(借,年月,=&$G$2,科目编号,504)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,504),SUMIFS(借,年月,=&$G$2,科目编号,504)。（5）“（三）经营费用”年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,

13、503)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科目编号,503)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,503),SUMIFS(借,年月,=&$G$2,科目编号,503)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,503),SUMIFS(借,年月,=&$G$2,科目编号,503)。（6）“三、主营业务利润（亏损以“”号填列）”年初数：=IF($E$2=10,C5-C6-C7-C8)；月初数：=IF($E$2=10,D5-D6-D7-D8)；月余数：=E5-E6-E7-E8；期末数：=F5-F6-F7-F

14、8。 （7）“加：其他业务利润（亏损以“”号填列）”年初数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,511)；月初数：=IF($E$2=10,SUMIFS(贷,年月,&$G$2,科目编号,511)；月余数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,511),SUMIFS(贷,年月,=&$G$2,科目编号,511)；期末数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,511),SUMIFS(贷,年月,=&$G$2,科目编号,511)。 （8）“减：（一）营业费用”年初数：=IF($E$2=10,0)；月初数：=IF($E$2=10,0)

15、；月余数：=0；期末数：=0。 （9）“（二）管理费用”年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,521)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科目编号,521)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,521),SUMIFS(借,年月,=&$G$2,科目编号,521)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,521),SUMIFS(借,年月,=&$G$2,科目编号,521)。（10）“（三）财务费用”年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,52

16、2)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科目编号,522)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,522),SUMIFS(借,年月,=&$G$2,科目编号,522)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,522),SUMIFS(借,年月,=&$G$2,科目编号,522)。 （11）“四、营业利润（亏损以“”号填列）”年初数：=IF($E$2=10,C9+C10-C11-C12-C13)；月初数：=IF($E$2=10,D9+D10-D11-D12-D13)；月余数：=E9+E10-E11-E12-

17、E13；期末数：=F9+F10-F11-F12-F13。（12）“（一）加：以前年度损益调整”年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,560)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科目编号,560)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,560),SUMIFS(借,年月,=&$G$2,科目编号,560)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,560),SUMIFS(借,年月,=&$G$2,科目编号,560)。（13）“（二）补贴收入”年初数：=IF($E$2=10,SU

18、MIFS(贷,年月,0,科目编号,532)；月初数：=IF($E$2=10,SUMIFS(贷,年月,&$G$2,科目编号,532)；月余数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,532),SUMIFS(贷,年月,=&$G$2,科目编号,532)；期末数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,532),SUMIFS(贷,年月,=&$G$2,科目编号,532)。（14）“（三）营业外收入”年初数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,541)；月初数：=IF($E$2=10,SUMIFS(贷,年月,&$G$2,科目编号,541

19、)；月余数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,541),SUMIFS(贷,年月,=&$G$2,科目编号,541)；期末数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,541),SUMIFS(贷,年月,=&$G$2,科目编号,541)。（15）“减：营业外支出”年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,542)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科目编号,542)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,542),SUMIFS(借,年月,=&$G$2,科目编号,542)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,542),SUMIFS(借,年月,=&$G$2,科目编号,542)。（16）“五、利润总额（亏损总额以“”号填列）”年初数：=IF($E$2=10,C14+C15+C16+C17-C18)；月初数：=