谭藤2015级第八讲学生讲的资料谭藤

1、Excel 高级财会应用课程期末作业课 程：Excel 高级财会应用授课老师： 陈士成 老师 学 生： 谭 藤 学 号： 220150910870 1 总体思路由于无法找到合适的详细财务数据，故只能使用老师上课时的数据资料进行资产负债表、利润表模板的编制，并根据财务报表进行财务数据分析，分别使用杜邦分析模型分析和财务比率趋势分析，并进一步编制固定资产折旧模板和投资收益模板，可供今后学习工作借鉴使用，要说明的是，作业中使用的数据均是课堂学习资料，但是我尽自己所能，根据自己所学所悟，进行了编制完善。2 财务数据报表2.1数据透视表根据某公司2010年12月2011年12月的记账凭证，整理制作成数据

2、透视表，以便财务报表的编制。具体步骤如下：（1）选中数据区域，插入数据透视表，将日期、科目编号依次拖入行标签，借方贷方拖入数值。 （2）将光标定于日期栏下具体日期处，选择“数据透视表”选项下“将字段分组”。在分组列表中，选择年和月。 （3）设置数组变量 为“C5C335”设置数组变量“科目编号”为“D5D335”设置数组变量“借”为“E5E335”设置数组变量“贷”为了方便后续工作，在“G5G335”，对应“A5A335”分别输入对应着的月份，为编制方便，将2010年12月月份设置为0，并设置数组变量“年月”。2.2资产负债表2.2.1设计资产负债表模板此资产负债表编制日期为2010年12月3

3、12011年的12月31日。2010年12月为建账期。按照课堂资料中的资产负债表模板，包括年初数、月初数、月余数和期末数，期初数为每年的1月1日，月初数为每月的1日，月末数为每月的最后一天，期末数为每年的12月31日。两个滚动条分别按照年度和月份改变编制时间。2.2.2 编制公式货币资金（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

4、年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,科目编号,10

5、2),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,

6、科目编号,101)-SUMIFS(贷,年月,= &$G$2,科目编号,101)+SUMIFS(借,年月,= &$G$2,科目编号,102)-SUMIFS(贷,年月,= &$G$2,科目编号,102)。（其余详见附件）最后，编制结果如下： 2.3 利润表利润表和资产负债表模板的制作方法一样，用记账凭证的数据透视表和“利润及利润分配表关系”，制作利润表模板 。(公式详见附件) 3 财务分析3.1 财务分析杜邦分析3.1.1 设计杜邦分析模型 3.1.2 编制公式1）权益净利率=总资产净利率*权益乘数2）总资产周转率=销售净利率*资产周转率3）销售净利率=净利润 /销售收入4）权益乘数=总资产/所有

7、者权益在编制公式时，由于涉及到财务数据为负的情况，有两种情况，一种情况例如：销售净利率=净利润 /销售收入，净利润和销售收入为负时，则不能直接将两个单元格直接相除，否则运算出来的比率虽然为正，但是，当净利润和销售收入为负时说明分析销售净利率已然没有意义，所以我使用IF函数，排除掉当两者均为负仍运算的情况，使得财务比率具有意义。另一种情况：当总资产净利率和权益乘数均为负时，使用IF函数使其不进行运算。 5）其他项目就引用综合表中的数据。引用的是综合表中的数据，是由于数据会涉及到资产负债表和利润表，而且微调框需要链接单元格，不论链接资产负债表还是利润表都不正确，基于两点，所以引用综合表数据。3.2

8、 财务分析财务比率分析3.2.1 设计财务比率分析框架 3.2.2 输入公式（1） 流动比率 （其他比率同理，公式不再罗列） 在编制公式时，由于涉及到财务数据为负的情况，例如：流动比率=流动资产/流动负债，流动资产和流动负债为负时，则不能直接将两个单元格直接相除，否则运算出来的比率虽然为正，但是，当流动资产和流动负债为负时说明分析流动比率已然没有意义，所以我使用IF函数，排除掉当两者均为负仍运算的情况，使得财务比率具有意义。（2）应收账款 出现这样的原因是因为应收账款为0。（2） 插入微调框，年份的单元格设置公式，月份同理。3.3 财务分析趋势分析3.3.1 建立分析框架 3.3.2 趋势分析

9、由于IF函数的设置，许多比率在分析时是无意义，所以显示FALSE。故只选取了两个从1月至12月均可分析的比率：存货周转率和总资产净利率进行趋势分析。（1）存货周转率 （2）总资产净利率 4 其他分析4.1 固定资产折旧4.1.1 设计模板 4.2.2 输入公式（1）计算当期计提累计折旧 1）设置数组变量将C2、C3、C4分别定义为固定资产原值、耐用年限、残值；2）输入公式 分别在D5H5 单元格输入1、2、3、4、5，代表年份，方便输入公式直线法： 第一年 “=SLN(固定资产原值,残值,耐用年限)”固定余额递减法：第一年 “=DB(固定资产原值,残值,耐用年限,D5)”双倍余额递减法：第一年

10、 “=DDB(固定资产原值,残值,耐用年限,D5)”年数合计法： 第一年 “=SYD(固定资产原值,残值,耐用年限,D5)”倍率余额递减法 第一年 “=VDB(固定资产原值,残值,耐用年限,0,1,1.5)”，并且随着年度的递增，期限要改变。其他年份同理，不再罗列。（2）计算当期计提累计折旧 、输入公式 例：直线法第一年 D16=C16-D8账面价值=固定资产原值-当期累计折旧同理，公式不再罗列4.2 投资收益分析4.2.1 设计模板 4.2.2 输入公式以 2006年10月 为例（1）归还利息 “=IPMT(G3,1,E3,C2,0,0)”（2）归还本金 “=PPMT(G3,1,E3,C2,0)”（3）归还本利额 “=PMT($G$3,$E$3,$C$2,0,0)”（4）累计利息 “=CUMIPMT(G3,E3,C3,1,1,0)”（5）累计本金 “=CUMPRINC(G3,10,C3,1,1,0)”（6）未还贷款 “=H6-G7” 我在计算的时候遇到了两个没有解决的问题：1、计算累计利息和累计本金的时候，运用CUMPUT和CUMPRINC