Excel 高级财会应用word版 马晓晨

1、 Excel高级财会应用Excel 高级财会应用课程期末作业 课 程：Excel 高级财会应用 授课老师：陈 士 成 老师学 生： 马晓晨 学 号：220130929021 目录一、总体思路3二、数据透视表32.1具体步骤32.2字段分组3三、设置数组变量：4四、资产负债表设计44.1设计资产负债表模板44.2设计公式5五、利润表设计75.1设计利润表模板75.2公式设计8六、财务比率分析表86.1创建财务比率分析表框架96.2在财务比率分析表中创建公式和跨表引用数据。96.3滚动条设计106.4输入公式11七、杜邦分析117.1设计杜邦分析模型117.2 输入公式127.3 杜邦分析图12八

2、、净利润影响因素分析表设计138.1创建净利润影响因素分析表138.2微调控件设计：138.3公式输入148.4插入图表14一、总体思路根据陈老师给的凭证资料（全部选用），通过数据透视表整理基础数据，在数据透视的的基础上设计资产负债表、利润表。同时对财务报告进行分析，依靠表内勾稽关系进行了每月的财务比率分析和杜邦财务分析，并对公司净利润影响因素进行了分析。二、数据透视表根据所选取的老师所给的2010年到2011年的全部财务记账凭证资料，整理数据。2.1具体步骤全选财务记账凭证数据插入“数据透视表”在“选择要添加到报表的字段”选取“日期”、“科目编号”、“借方”、“贷方”2.2字段分组将光标定于

3、日期栏下具体日期处，选择“数据透视表”选项下“将字段分组”。在分组列表中，选择年和月。三、设置数组变量：为“C5C226”设置数组变量“科目编号”为“D5D226”设置数组变量“借”为“E5E226”设置数组变量“贷”为了方便后续工作，在“G5G226”，对应“A5A226”分别输入月份，2010年12月记为0,2011年月份记为1,2，并设置数组变量“月份”四、资产负债表设计4.1设计资产负债表模板此资产负债表编制日期为2010-2011年的12月31日。2010年12月为建账期。按照我国一般财务报告中资产负债表标准格式编制，包括期初数和期末数，期初数为每年的1月1日，期末数为每年的12月3

4、1日。滚动条按照年度改变编制时间。4.2设计公式期初：说明，在2010年12月公司建账，2010年12月31日编制的资产负债表中，报表项目期初数为零，以后期编制的资产负债表中，资产类项目为相关科目借方减去贷方余额，负债类项目为相关科目贷方减去借方余额。具体公式如下：一、（01）货币资金（1）10年12月的年初数为0，11年各月的年初数就是10年12月的当月余额。年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,101)-SUMIFS(贷,年月,0,科目编号,101)+SUMIFS(借,年月,0,科目编号,102)-SUMIFS(贷,年月,0,科目编号,102)；（2）10年

5、12月的月初数为0，11个各月的年初数就是10年12月至当月前的月余额之和。月初数：=IF($E$2=10,SUMIFS(借,年月, &$G$2,科目编号,101)-SUMIFS(贷,年月, &$G$2,科目编号,101)+SUMIFS(借,年月, &$G$2,科目编号,102)-SUMIFS(贷,年月, &$G$2,科目编号,102)；（3）所有月份的月余数都取当月的月余数。月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,101)-SUMIFS(贷,年月,0,科目编号,101)+SUMIFS(借,年月,0,科目编号,102)-SUMIFS(贷,年月,0,科目编号,102

6、),SUMIFS(借,年月,=&$G$2,科目编号,101)-SUMIFS(贷,年月,=&$G$2,科目编号,101)+SUMIFS(借,年月,= &$G$2,科目编号,102)-SUMIFS(贷,年月,=& $G$2,科目编号,102)；（4）10年12月的期末数为该月的月余数，11年各月的期末数就是10年12月至当月的月余额之和。期末数=IF($E$2=10,SUMIFS(借,年月,0,科目编号,101)-SUMIFS(贷,年月,0,科目编号,101)+SUMIFS(借,年月,0,科目编号,102)-SUMIFS(贷,年月,0,科目编号,102),SUMIFS(借,年月,= &$G$2,科

7、目编号,101)-SUMIFS(贷,年月,= &$G$2,科目编号,101)+SUMIFS(借,年月,= &$G$2,科目编号,102)-SUMIFS(贷,年月,= &$G$2,科目编号,102)。二、（06）应收入账款年初数：=IF($E$2=10,SUMIFS(借,年月,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,

8、科目编号,113),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)。三、（07）其它应收款年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,119)-SUMIFS(贷,年月,0,科目编号,119)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科

9、目编号,119)-SUMIFS(贷,年月,&$G$2,科目编号,119)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,119)-SUMIFS(贷,年月,0,科目编号,119),SUMIFS(借,年月,=&$G$2,科目编号,119)-SUMIFS(贷,年月,=&$G$2,科目编号,119)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,119)-SUMIFS(贷,年月,0,科目编号,119),SUMIFS(借,年月,=&$G$2,科目编号,119)-SUMIFS(贷,年月,=&$G$2,科目编号,119)。四、（08）预付账款年初数：=IF($

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

11、,0,科目编号,115),SUMIFS(借,年月,=&$G$2,科目编号,115)-SUMIFS(贷,年月,=&$G$2,科目编号,115)。五、（12）存货年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,123)-SUMIFS(贷,年月,0,科目编号,123)+SUMIFS(借,年月,0,科目编号,129)-SUMIFS(贷,年月,0,科目编号,129)+SUMIFS(借,年月,0,科目编号,137)-SUMIFS(贷,年月,0,科目编号,137)+SUMIFS(借,年月,0,科目编号,401)-SUMIFS(贷,年月,0,科目编号,401)；月初数：=IF($E$2=

12、10,SUMIFS(借,年月,&$G$2,科目编号,123)-SUMIFS(贷,年月,&$G$2,科目编号,123)+SUMIFS(借,年月,&$G$2,科目编号,129)-SUMIFS(贷,年月,&$G$2,科目编号,129)+SUMIFS(借,年月,&$G$2,科目编号,137)-SUMIFS(贷,年月,&$G$2,科目编号,137)+SUMIFS(借,年月,&$G$2,科目编号,401)-SUMIFS(贷,年月,&$G$2,科目编号,401)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,123)-SUMIFS(贷,年月,0,科目编号,123)+SUMIFS(借,

13、年月,0,科目编号,129)-SUMIFS(贷,年月,0,科目编号,129)+SUMIFS(借,年月,0,科目编号,137)-SUMIFS(贷,年月,0,科目编号,137)+SUMIFS(借,年月,0,科目编号,401)-SUMIFS(贷,年月,0,科目编号,401),SUMIFS(借,年月,=&$G$2,科目编号,123)-SUMIFS(贷,年月,=&$G$2,科目编号,123)+SUMIFS(借,年月,=&$G$2,科目编号,129)-SUMIFS(贷,年月,=&$G$2,科目编号,129)+SUMIFS(借,年月,=&$G$2,科目编号,137)-SUMIFS(贷,年月,=&$G$2,科

14、目编号,137)+SUMIFS(借,年月,=&$G$2,科目编号,401)-SUMIFS(贷,年月,=&$G$2,科目编号,401)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,123)-SUMIFS(贷,年月,0,科目编号,123)+SUMIFS(借,年月,0,科目编号,129)-SUMIFS(贷,年月,0,科目编号,129)+SUMIFS(借,年月,0,科目编号,137)-SUMIFS(贷,年月,0,科目编号,137)+SUMIFS(借,年月,0,科目编号,401)-SUMIFS(贷,年月,0,科目编号,401),SUMIFS(借,年月,=&$G$2,科目编号,1

15、23)-SUMIFS(贷,年月,=&$G$2,科目编号,123)+SUMIFS(借,年月,=&$G$2,科目编号,129)-SUMIFS(贷,年月,=&$G$2,科目编号,129)+SUMIFS(借,年月,=&$G$2,科目编号,137)-SUMIFS(贷,年月,=&$G$2,科目编号,137)+SUMIFS(借,年月,=&$G$2,科目编号,401)-SUMIFS(贷,年月,=&$G$2,科目编号,401)。六、（13）其中：原材料年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,123)-SUMIFS(贷,年月,0,科目编号,123)；月初数：=IF($E$2=10,S

16、UMIFS(借,年月,&$G$2,科目编号,123)-SUMIFS(贷,年月,&$G$2,科目编号,123)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,123)-SUMIFS(贷,年月,0,科目编号,123),SUMIFS(借,年月,=&$G$2,科目编号,123)-SUMIFS(贷,年月,=&$G$2,科目编号,123)；期末数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,123)-SUMIFS(贷,年月,0,科目编号,123),SUMIFS(借,年月,=&$G$2,科目编号,123)-SUMIFS(贷,年月,=&$G$2,科目编号,123)。

17、七、（14）产成品（存货商品）年初数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,137)-SUMIFS(贷,年月,0,科目编号,137)；月初数：=IF($E$2=10,SUMIFS(借,年月,&$G$2,科目编号,137)-SUMIFS(贷,年月,&$G$2,科目编号,137)；月余数：=IF($E$2=10,SUMIFS(借,年月,0,科目编号,137)-SUMIFS(贷,年月,0,科目编号,137),SUMIFS(借,年月,=&$G$2,科目编号,137)-SUMIFS(贷,年月,=&$G$2,科目编号,137)；期末数：=IF($E$2=10,SUMIFS(借,年月

18、,0,科目编号,137)-SUMIFS(贷,年月,0,科目编号,137),SUMIFS(借,年月,=&$G$2,科目编号,137)-SUMIFS(贷,年月,=&$G$2,科目编号,137)。 其他报表项目依次类推。调整格式：调整字体、列宽、行高、字号等。五、利润表设计5.1设计利润表模板此利润表编制日期为2010-2012年的12月31日。2010年12月为建账期，本期利润表项目全为0。按照我国一般财务报告中利润表标准格式编制，包括上期数和本期数，利润表涉及的期间为每年的1月1日12月31日。滚动条按照年度改变编制时间。5.2公式设计年初数：主营业务收入=IF($E$2=10,SUMIFS(借,年月,0,科目编号,501)-SUMIFS(贷,年月,0,科目编号,501)其他项目依次类推。月初数：=IF($E$2=10,SUMIFS(贷,年月,0,科目编号,501),SUM