Excel在高级财务中的应用 2013级Mpacc曹艳蓉

1、 Excel高级财会应用Excel 高级财会应用课程期末作业课程：Excel 高级财会应用授课老师：陈士成老师学生：曹艳蓉学号：220130928950目录1.总体思路32.数据透视表33.资产负债表设计43.1.设计资产负债表模板43.2.设计公式53.3.调整格式64.利润表设计64.1.设计利润表模板64.2.公式设计74.3.格式调整75.固定资产折旧分析75.1 直线法85.3 年数合计法95.4 固定余额递减法95.5 倍率余额递减法96EXCLE工作表中线性财务预测模型建立106.1 线性财务预测模型建立106.2 线性财务预测模型建立121. 总体思路承接课堂所学的知识，根据基

2、础资料，通过数据透视表整理基础数据，在数据透视的的基础上设计资产负债表、利润表。对固定资产折旧进行五种方法的计提分析，以及对某公司excle工作表中线性财务预测模型进行创建和应用。2. 数据透视表根据所选取的*公司从2008年到2012年的财务记账凭证，整理数据。具体步骤：1) 全选财务记账凭证数据2) 插入“数据透视表”3) 在“选择要添加到报表的字段”选取“日期”、“科目编号”、“借方”、“贷方”4) 将光标定于日期栏下具体日期处，选择“数据透视表”选项下“将字段分组”。在分组列表中，选择年和月。5) 将光标定于日期栏下具体日期处，选择“数据透视表”选项下“将字段分组”。在分组列表中，选择

3、年和月。6) 设置数组变量：为“C5C226”设置数组变量“科目编号”为“D5D226”设置数组变量“借”为“E5E226”设置数组变量“贷”为了方便后续工作，在“G5G226”，对应“A5A226”分别输入2008、2009、2010、2011，并设置数组变量“年月”3. 资产负债表设计3.1. 设计资产负债表模板此资产负债表编制日期为2008-2012年的12月31日。2008年12月为建账期。按照我国一般财务报告中资产负债表标准格式编制，包括期初数和期末数，期初数为每年的1月1日，期末数为每年的12月31日。滚动条按照年度改变编制时间。3.2. 设计公式期初：说明，在2008年12月公司

4、建账，2008年12月31日编制的资产负债表中，报表项目期初数为零，以后期编制的资产负债表中，资产类项目为相关科目借方减去贷方余额，负债类项目为相关科目贷方减去借方余额。具体公式如下：1) 货币资金=IF($E$2=2008,0,SUMIFS(借,年月,=&($E$2-1),科目编号,101)-SUMIFS(贷,年月,=&($E$2-1),科目编号,101)+SUMIFS(借,年月,=&($E$2-1),科目编号,102)-SUMIFS(贷,年月,=&($E$2-1),科目编号,102)2) 应收账款=IF($E$2=2008,0,SUMIFS(借,年月,=&($E$2-1),科目编号,113

5、)-SUMIFS(贷,年月,=&($E$2-1),科目编号,113)3) 其他报表项目依次类推。期末：1) 货币资金：=SUMIFS(借,年月,=&$E$2,科目编号,101)-SUMIFS(贷,年月,=&$E$2,科目编号,101)+SUMIFS(借,年月,=&$E$2,科目编号,102)-SUMIFS(贷,年月,=&$E$2,科目编号,102)2) 应收账款=SUMIFS(借,年月,=&$E$2,科目编号,113)-SUMIFS(贷,年月,=&$E$2,科目编号,113)3) 其他应收账款=SUMIFS(借,年月,=&$E$2,科目编号,119)-SUMIFS(贷,年月,=&$E$2,科目

6、编号,119)4) 预付账款=SUMIFS(借,年月,=&$E$2,科目编号,115)-SUMIFS(贷,年月,=&$E$2,科目编号,115)5) 存货=SUMIFS(借,年月,=&$E$2,科目编号,123)-SUMIFS(贷,年月,=&$E$2,科目编号,123)+SUMIFS(借,年月,=&$E$2,科目编号,129)-SUMIFS(贷,年月,=&$E$2,科目编号,129)+SUMIFS(借,年月,=&$E$2,科目编号,137)-SUMIFS(贷,年月,=&$E$2,科目编号,137)+SUMIFS(借,年月,=&$E$2,科目编号,401)-SUMIFS(贷,年月,=&$E$2,

7、科目编号,401)6) 待摊费用=IF($E$2=2008,SUMIFS(借,年月,=&($E$2-1),科目编号,139)-SUMIFS(贷,年月,=&($E$2-1),科目编号,139),SUMIFS(借,年月,=&$E$2,科目编号,139)-SUMIFS(贷,年月,=&$E$2,科目编号,139)7) 流动资产合计=货币资金+应收账款+其他应收账款+预付账款+存货+待摊费用8) 其他报表项目依次类推。3.3. 调整格式调整字体、列宽、行高、字号等。4. 利润表设计4.1. 设计利润表模板此利润表编制日期为2008-2012年的12月31日。2008年12月为建账期，本期利润表项目全为0

8、。按照我国一般财务报告中利润表标准格式编制，包括上期数和本期数，利润表涉及的期间为每年的1月1日12月31日。滚动条按照年度改变编制时间。4.2. 公式设计上期数：主营业务收入=IF($E$2=2008,0,SUMIFS(贷,年月,=&($E$2-1),科目编号,501)其他项目依次类推。本期数：主营业务收入=SUMIFS(贷,年月,=&$E$2,科目编号,501)其他项目依次类推。4.3. 格式调整调整字体、列宽、行距、字号等5. 固定资产折旧分析固定资产原值：2569437.80 元耐用年限：5年残值：1663421.635.1 直线法公式： SLN(2569437.8,1663421.6

9、3,5)第一年至第五年：181203.23图形表示法：1、在固定资产计提折旧表旁选定一单元格录入“年份”，“年份”行向依次输入2008-2012五个年份，“年份竖向第一个单元格录入“直线法”，“直线法”行向依次录入2008-2012五年的折旧额。2、选中所录入的数据后选择工具栏的“插入”选项，在下拉菜单里选“图表”选项，再选折线图选项，在右边的供选图形中选中所需要的图形，然后点击“完成”。5.2 双倍余额递减法第一年公式：DDB(2569437.8,1663421.63,5,1)计提折旧：906016.17第二年公式： DDB(2569437.8,166341.63,5,2)计提折旧：-第三年

10、公式： DDB(2569437.8,166341.63,5,3)计提折旧：-第四年公式： DDB(2569437.8,166341.63,5,4)计提折旧：-第五年公式： DDB(2569437.8,166341.63,5,5)计提折旧：-图形表示法：1、在固定资产计提折旧表旁选定一单元格录入“年份”，“年份”行向依次输入2008-2012五个年份，“年份竖向第一个单元格录入“直线法”，“直线法”行向依次录入2008-2012五年的折旧额。2、选中所录入的数据后选择工具栏的“插入”选项，在下拉菜单里选“图表”选项，再选条形图选项，在右边的供选图形中选中所需要的图形，然后点击“完成”。5.3 年

11、数合计法第一年公式：SYD(2569437.8,1663421.63,5,1)计提折旧：302005.39第二年公式：SYD(2569437.8,1663421.63,5,2)计提折旧：241604.31第三年公式：SYD(2569437.8,1663421.63,5,3)计提折旧：181203.23第四年公式：SYD(2569437.8,1663421.63,5,4)计提折旧：120802.16第五年公式：SYD(2569437.8,1663421.63,5,5)计提折旧：60401.08图形表示法：1、在固定资产计提折旧表旁选定一单元格录入“年份”，“年份”行向依次输入2008-2012五

12、个年份，“年份竖向第一个单元格录入“直线法”，“直线法”行向依次录入2008-2012五年的折旧额。2、选中所录入的数据后选择工具栏的“插入”选项，在下拉菜单里选“图表”选项，再选条形图选项，在右边的供选图形中选中所需要的图形，然后点击“完成”。5.4 固定余额递减法第一年公式：DB(2569437.8,1663421.63,5,1,12)计提折旧：213263.34第二年公式：DB(2569437.8,1663421.63,5,2,12)计提折旧：195562.48第三年公式：DB(2569437.8,1663421.63,5,3,12)计提折旧：179330.79第四年公式：DB(2569

13、437.8,1663421.63,5,4,12)计提折旧：164446.34第五年公式：DB(2569437.8,1663421.63,5,5,12)计提折旧：150797.29图形表示法：1、在固定资产计提折旧表旁选定一单元格录入“年份”，“年份”行向依次输入2008-2012五个年份，“年份竖向第一个单元格录入“直线法”，“直线法”行向依次录入2008-2012五年的折旧额。2、选中所录入的数据后选择工具栏的“插入”选项，在下拉菜单里选“图表”选项，再选柱形图选项，在右边的供选图形中选中所需要的图形，然后点击“完成”。5.5 倍率余额递减法第一年至第五年公式：VDB(2569437.8,1

14、663421.63,5,0,1)计提折旧：906016.17图形表示法：1、在固定资产计提折旧表旁选定一单元格录入“年份”，“年份”行向依次输入2008-2012五个年份，“年份竖向第一个单元格录入“直线法”，“直线法”行向依次录入2008-2012五年的折旧额。2、选中所录入的数据后选择工具栏的“插入”选项，在下拉菜单里选“图表”选项，再选环形图选项，在右边的供选图形中选中所需要的图形，然后点击“完成”。6EXCLE工作表中线性财务预测模型建立6.1线性财务预测模型建立财务预测是指根据销售预测结果，运用一定的方法确定：企业未来的外部融资需求，其算式为：外部融资需求=资产增加一负债自然增加一留

15、存收益增加。因此，在销售预测基础上进行财务预测的关键在于确定资产、负债与销售收入的关系，进而确定预测销售下的资产、负债需求。通常假设销售额与资产、负债等存在线性关系，这种关系可根据历史资料和Excel中回归分析函数Linest加以确定。以某公司资料为例，已知前五年历史资料，并已知该公司销售净利率为-76%，股利支付率为10。财务预测模型的创建步骤如下：1运行Excel，打开工作簿并更名为线性财务预测模型。2在A1：G7区域中输入有关资料(G7中列示的是销售收入与年份的线性关系)。3定义历史数据区域的名字。将B2：F2命名为 nf ，将B3：F3命名为1dzc，将B4：F4命名为gdzc，将B5

16、：F5 命名为1dfz，将B7：F7命名为xssr。4. 求解流动资产、固定资产及流动负债与销售收入的关系模型。在下页表4中的A8：C24区域中设计关系模型。该区域中CIO：C12、C14：Cl6、C18：C20、C22：c24各单元格中的数值为求解的结果，其中输入的参数公式见下边表。5进行财务预测。在表4中的E8：G17区域中设计财务预测的样式，其中除F9、G17、G18单元格为直接输入的数值外，其他单元格中的数值为输入公式(见下表)后的计算结果。6. 至此，模型创建完毕，保存该工作簿并退出Excel。最终创建的财务预测模型结果如表46.2线性财务预测模型建立当进行年度财务预测时，打开工作簿

17、，在B2：F7中分别输入年份、流动资产、固定资产、流动负债、长期负债、销售收入的历史数据；在G17：G18中分别输入销售净利率和股利支付率的值，则财务预测结果便会自动生成。利用财务预测模型快速准确地计算出企业的外部融资需求，是创建财务预测模型的主要目的，然而财务预测模型的应用不至于此。下面将说明如何应用基于Excel工作表的财务预测模型进行“假设分析”和“究因分析”。(一)假设分析财务预测模型的假设分析是指从因素到结果的逻辑分析过程，即根据预测收入的变动来确定外部融资需求的范围。前述财务预测模型假设销售收入与年份存在线性关系，并据此假设求得预测年份的预测收入。但是，企业产品销售市场的波动决定了

18、预测年份的收入存在不确定性。因此，不妨假设预测年份的收入是包含上述预测收入在内的某一范围，如以本文所示公司为例，预测年份的收入范围不妨假设为 -300000000，-200000000。根据收入范围，利用财务预测模型可以求出外部融资需求的范围。用-300000000数值代替单元格F10 中的原有内容，回车后，返回到单元格F19中，数值则成为 -190600976.9；（二)究因分析财务预测模型的究因分析是指从结果到因素的逻辑分析过程，即根据外部融资需求来确定预测年份的收入。当外部融资需求等于零时，企业销售收入的增长主要依靠内部积累资金的支持，此时的预测收入可称为内含增长下的销售收入。在不增发新股并保持目