第四章EXCEL在会计中的应用.

Presentation on theme: "第四章EXCEL在会计中的应用."— Presentation transcript:

1 第四章EXCEL在会计中的应用

2 【本章的学习目的与要求】 通过本章的学习，使学生了解并掌握EXCEL系统的基本操作，能够利用EXCEL进行固定资产折旧的计算，进行货币时间价值的分析以及利用EXCEL进行投资决策。 【课时分配】 10学时（含上机6学时）

3 第一节固定资产折旧的计算 按照现行制度规定，企业计提折旧的方法有：直线法、双倍余额递减法和年数总和法。按照传统的手工计算方法计算起来比较烦琐，我们利用Excel计算就方便多了

4 1.直线法。SLN 用途：在ExceI中使用直线折旧法提取的折旧， 函数：SLN（cost，salvage，1ife）
参数: Cost为资产原值，Salvage为资产在折旧期末的价值(也称为资产残值)，Life为折旧期限(有时也可称作资产的生命周期)

5 例如，某企业有一台机器原始成本为20万元，预计可使用10年，预计残值为1万元
每年的折旧额为：SLN（200000，10000，10）＝19000（元） 每月的折旧额为：SLN（200000，10000，5×12）＝ （元）

6 2.双倍余额递减法。DDB 双倍余额递减法是加速折旧计算法。用此法提取折旧第一期折旧额较大，以后各期依次减少。
用途：使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。 语法：DDB(cost，salvage，life，period，factor) 参数：Cost为资产原值，Salvage为资产在折旧期末的价值(也称为资产残值)，Life为折旧期限(有时也称作资产的使用寿命)，Period为需要计算折旧值的期间。Period必须使用与life相同的单位，Factor为余额递减速率(如果factor省略，则假设为2)。

7 例如，某企业进口一条生产线，安装完毕后，固定资产原值为20万元，预计使用年限5年，预计净残值收入8000元，计算各年的折旧额。
第一年应提折旧：DDB（200000，8000，5，1）＝80000 第二年应提折旧：DDB（200000，8000，5，2）＝48000 第三年应提折旧：DDB（200000，8000，5，3）＝28800 第四年应提沂旧：SLN（43200，8000，2，4）＝17600 第五年应提折旧：SLN（43200，800Q，2，5）＝17600

8 3. 固定余额递减法.DB 用途：使用固定余额递减法，计算一笔资产在给定期间内的折旧值。
函数：DB(cost,salvage,life,period,month)。 参数：Cost为资产原值，Salvage为资产在折旧期末的价值(也称为资产残值)，Life为折旧期限(有时也可称作资产的生命周期)，Period为需要计算折旧值的期间。Period必须使用与life相同的单位，Month为第一年的月份数，如省略，则假设为12。

9 例如：假定某工厂购买了一台新机器。价值为￥1,000,000，使用期限为8年。残值为￥100,000。
下面的例子给出机器在使用期限内的历年折旧值，结果保留整数。 DB( ,100000,8,1)等于￥250000 DB( ,100000,8,2)等于￥187500 DB( ,100000,8,3)等于￥140625 DB( ,100000,8,4)等于￥ DB( ,100000,8,5)等于￥ DB( ,100000,8,6)等于￥ DB( ,100000,8,7)等于￥ DB( ,100000,8,8)等于￥

10 4.年数总和法。SYD 用途：使用年数总和法，计算一笔资产在给定期间内的折旧值。
函数：SYD（cost，salvage，1ife，period）。 参数：Cost为资产原值，Salvage为资产在折旧期末的价值(也称为资产残值)，Life为折旧期限(有时也可称作资产的生命周期)， period为期间，其单位与life相同。

11 例如，某项固定资产原值为100000元，预计使用年限为5年，预计净残值为1000元。采用年数总和法计算各年折旧额。
第一年应提折旧：SYD（100000，1000，5，1）＝33000 　　第二年应提折旧：SYD（100000、1000、5、2）＝26400 　　第三年应提折旧：SYD（100000，1000，5，3）＝19800 　　第四年应提折旧：SYD（100000，1000，5，4）＝13200 　　第五年应提折旧：SYD（100000，1000，5，5）＝6600

12 试验案例 试验例题1：某工厂购买一台新设备，价值1,000000元，使用年限为10年，残值为10,000元，用四种折旧函数计算每年的折旧额(结果保留整数) 用EXCEL实例介绍

13 （1）使用SLN(平均年限法)函数 第一步：光标移在B5上，选择“插入/函数”命令，出现相应对话框。在“函数分类”中选“财务”， “函数名”中选“SLN"，单击“确定”按钮，出现“SLN函数”对话框. 第二步:按要求输入SLN函数的参数－固定资产原值Cost“$B$1”,固定资产残值Salvage“$B$2”，预计使用年限Life“$B$3”，(注意使用绝对引用)，如图所示

14 第三步：单击“确定”按钮，在B5单元格上显示结果为“￥98，000.00”。
第四步：拖曳B5单元格右下角的自动填充柄到B14，使B5单元格中已有函数自动按序列复制到B6、B7、B8、B9、B10、Bll、B12、B13、B14单元格中。 第五步：利用SUM函数求累计折旧。

15 第六步：计算结果如下：

16 （2）使用DDB（双倍余额递减法)函数 第一步：光标移在C5上，选择“插入/函数”命令，出现相应对话框。在“函数分类”中选“财务”， “函数名”中选“DDB"，单击“确定”按钮，出现“DDB函数”对话框. 第二步:按要求输入DDB函数的参数－固定资产原值Cost“$B$1”,固定资产残值Salvage“$B$2”，预计使用年限Life“$B$3”，（注意使用绝对引用）

17 第三步：在Period文本框中输入计算年期“ A5” (注意使用相对引用)，在Factor文本框中输入“2”，如图所示

18 第四步：单击“确定”按钮，在B5单元格上显示结果为“￥200，000.00”。
第五步：拖曳C5单元格右下角的自动填充柄到C12，使C5单元格中已有函数自动按序列复制到C6、C7、C8、C9、C10、Cll、B12、单元格中 第六步：单击C13单元格，输入公式“ (B1-B2-SUM(C5：C12))／2”，按回车键 第七步：在C14单元格中输入公式“=C13”，按回车键 第八步：利用SUM函数求累计折旧。

19 第九步：计算结果如下：

20 (3)使用DB(固定余额递减法)函数 第一步：光标移在D5上，选择“插入/函数”命令，出现相应对话框。在“函数分类”中选“财务”， “函数名”中选“DB"，单击“确定”按钮，出现“DB函数”对话框. 第二步:按要求输入DB函数的参数－固定资产原值Cost“$B$1”,固定资产残值Salvage“$B$2”，预计使用年限Life“$B$3”，(注意使用绝对引用)

21 第三步：在Period文本框中输入计算年期“ A5” (注意使用相对引用)，在Month文本框中输入“12”，（ 参数Month为第一年的月份数，一般选12，可以省略）如图所示

22 第四步：单击“确定”按钮，在D5单元格上显示结果为“￥324，000.00”。
第五步：拖曳D5单元格右下角的自动填充柄到D13，使D5单元格中已有函数自动按序列复制到D6、D7、D8、D9、D10、Dll、D12、D13单元格中 第六步：单击D13单元格，输入公式“ B1-B2-SUM(D5：D13)”，按回车键 第七步：利用SUM函数求累计折旧。

23 第八步：计算结果如下：

24 (4)使用SYD(年限总和法)函数 第一步：光标移在E5上，选择“插入/函数”命令，出现相应对话框。在“函数分类”中选“财务”， “函数名”中选“SYD"，单击“确定”按钮，出现“SYD函数”对话框. 第二步:按要求输入SYD函数的参数－固定资产原值Cost“$B$1”,固定资产残值Salvage“$B$2”，预计使用年限Life“$B$3”，(注意使用绝对引用)

25 第三步：在Period文本框中输入计算年期“ A5” (注意使用相对引用)，如图所示

26 第四步：单击“确定”按钮，在E5单元格上显示结果为“￥178,181.82”。
第五步：拖曳E5单元格右下角的自动填充柄到E14，使D5单元格中已有函数自动按序列复制到E6、E7、E8、E9、E10、Ell、E12、E13、E14单元格中 第六步：利用SUM函数求累计折旧。

27 第七步：计算结果如下：

28 第二节货币时间价值的计算 货币时间价值的计算，在财务管理中有着广泛的用途，如存货管理、养老金决策、租赁决策、资产和负债估价、长期投资决策等方面。随着财务问题的日益复杂化，时间价值观念的应用也将日益广泛。

29 1.复利终值的计算。 复利终值有普通复利终值、普通年金终值和预付年终值等形式。
其计算函数为：FV（rate、nper，pmt，pv，type）。其中：rate代表各期利率，是一固定值；nper代表总投资期（或贷款期），即该项投资（或贷款）总的付款期数；pmt代表各期支出金额，在整个投资期内不变，若该参数为0或省略，则函数值为普通复利终值；pv代表现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称本金。如果省略pv，则假设其值为0。type只有数值0或1，0或忽略表示收付款时间是期末，1表示是期初。

30 注意：1．若pmt为0或省略，则函数值为普通复利终值。
2．在pmt不为0，pv=0，type＝0时，函数值为普通年金终值。 3.在pmt不为0，pv=0，type＝1时，函数值为预付年金终值。 4. 应确认所指定的rate和nper单位的一致性。 5.在Excel中，对函数涉及金额的参数，是有特别规定的，即：支出的款项（如向银行存入款项）、用负数表示：收入的款项（如股息收入），用正数表示。

31 例：1、（1）普通复利终值的计算。 （2）普通年金终值的计算。 例如，某人将10000元投资于一顶事业，年报酬率为6％，3年后的复利终值为：
FV（6％，3，0，-10000，0）＝11910（元） （2）普通年金终值的计算。 例如，某人每年存入银行20000元、年利率为4％，计算第3年年末可以从银行取得的本利和（每年年末存入银行）为： FV（4％，3，-20000，0，0）＝62432（元）

32 （3）预付年金终值的计算。 仍以上例为例，计算预付年金终值（每年年初存入银行），则：
FV（4％，3，-20000，0，1）＝ （元）

33 例2、 某企业设立新产品开发基金，现账户上已有资金100，000元，计划每年末存人10，000元，假设没有支出的情况，年利率为6％，10后这笔基金将有多少？
FV(6％，10，—10000， ，0)等于￥

34 例3、假设需要为一年后的某个项目预筹资金，现在将￥10000以年利10％，按月计息存人储蓄存款帐户中，并在以后十二个月的每个月初存人￥1000则一年后该帐户的存款额等于多少?
FV(10％/12，12，—1000，-10000，1)等于￥23，717.41

35 2.复利现值的计算 返回投资的现值。现值为一系列未来付款当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。

36 语法：PV(rate,nper,pmt,fv,type)。
Pmt为各期所应付(或得到)的金额，其数值在整个年金期间(或投资期内)保持不变。通常pmt包括本金和利息。 Fv为未来值，或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零(一笔贷款的未来值即为零)。 Type：数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。

37 （1）普通复利现值的计算。 例如，某人拟在6年后获得本利和50000元，投资报酬率为10％，他现在应投入的金额为：
PV（10％，6，0，50000，0） ＝-￥ （元）

38 （2）普通年金现值的计算。 例如，某人要购买一项养老保险，购买成本为60000元，该保险可以在20年内于每月末回报500元、投资报酬率为8％，计算这笔投资是否值得。 PV（0.08／12，12×20.500，0，0） ＝—59777（元）  由于养老保险的现值（59777元）小于实际支付的现值（60000元），因此，这项投资不合算：

39 （3）预付年金现值的计算。 例如，用6年时间分期付款购物，每年预付566元。设银行利率为10％，该项分期付款相当于一次现金交付的购价是多少？ 　　PV（10％，6，200，0，1）＝-958

40 试验案例 例1、 某企业现投资200万，在第5年年末能收入400万，假设年利率为10％，问企业是否可接受这个投资？
PV（10％，5，0， ，0） ＝-￥ （元）

41 计算后的现值为248多万元（因为是流出，所以是负数），比期初投资200万要高，说明可以接受这个投资。

42 3．PMT 基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。 语法：PMT(rate,npe~pv,fv,type)。
Nper为总投资(或贷 款)期，即该项投资(或贷款)的付款期总数。 pv为现值，即从该项投资(或贷款)开始计算时已经人账的款项，或一系列未来付款当前值的累积和，也称为本金。fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fV，则假设其值为零(例如，一笔贷款的未来值即为零)。 Type：数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。

43 例：下面的公式将返回需要10个月付清的年利率为8％的￥10,000贷款的月支付额：
PMT(8％／12，10，10000) 等于-￥1,037．03 对于同一笔贷款，如果支付期限在每期的期初，支付额应为： PMT(8％／12，10，10000，0，1) 等于-￥1,030．16 如果以12％的利率贷出￥5,000，并希望对方在5个月内还清，下列公式将返回每月所得款数： PMT(12％／12，5，－5000) 等于￥1,030．20

44 除了用于贷款之外，函数PMT还可以计算出别的以年金方式付款的支付额。
即向6％的存款帐户中每月存人￥129．08，18年后可获得￥50，000。

45 试验案例 某企业抵押贷款2000万元，偿还期20年，年利率10％，计算每年末应偿还多少才能在贷款期内还清贷款。

46 PMT(10％，20， ，0，0) 等于-￥

47 4.投资期数的计算。 函数NPER（rate，pmt，pv，fv，type）
例如，A公司准备从甲公司购买一台设备，甲公司有两种销货方式供A公司选择：一是现在一次全额付款90万元；二是分若干年每年初付款16万元。假设资金成本为10％，如果A公司选择第二种付款方式，甲公司在签定合同时可接受的收款次数至少为多少次，其收入才不低于一次性全额收款？ 　　由于A公司和甲公司一个属于付款，另一个属于收款，所以pmt和pv必需有一个用负数表示，则根据题意！ 　　NPER（10％， ，900000，0，1）＝7.51 因为收款次数应为正整数，并且不能小于7.51，所以收款次数至少为8次。

48 第三节利用EXCEL进行投资决策 投资决策中、作为评价方案优劣尺度的指标主要有净现值、现值指数和内含报酬率。这些计算通常用手工较为麻烦，Exeel直接提供了计算净现值和内含报酬率的函数，现值指数可以间接地计算得到。

49 1.净现值的计算。 基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。投资的净现值是指未来各期支出(负值)和收人(正值)的当前值的总和。

50 语法：NPV(rate,valuel,value2……)
Valuel，value2，…表1到29笔支出及收入的参数值。Valuel，value2…所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。

51 NPV按次序使用Valuel,Value2，…来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。如果参数是数值、空白单元格、逻辑值或表示数值的文字表达式，则都会计算在内；如果参数是错误值或不能转化为数值的文字，则被忽略。如果参数是一个数组或引用， 只有其中的数值部分计算在内。忽略数组或引用中的空白单元格、逻辑值、文字及错误值。

52 函数NPV假定投资开始于valuel现金流所在日期的前一期，并结束于最后一笔现金流的当期。函数NPV依据未来的现金流计算。如果第一笔现金流发生在第一个周期的期初，则第一笔现金必须加入到函数NPV的结果中，而不应包含在values参数中。

53 例1、假设第一年投资￥10,000，而未来三年中各年的收入分别为￥3,000、￥4,200和￥6,800。 假定每年的贴现率是10％，
则投资的净现值是：NPV(10％，－10000，3000，4200，6800)等于￥1，188．44。

54 例2、假如要购买一家鞋店，投资成本为￥40,000，并且希望前五年的营业收入如下：￥8,000、￥9,200、￥10,000、￥12,000和￥14,500。每年的贴现率为8％(相当于通货膨胀率或竞争投资的利率)。如果鞋店的成本及收入分别存储在B1到B6中， 下面的公式可以计算出鞋店投资的净现值：NPV(8％，B2：B6)+B1等于￥1,922．06。

55 例3、某公司准备购置一台新设备，价款为40000元，以扩大生产规模，项目周期为5年，各年的净现金流量分别为15000、12000、13000、18000、8000，若资金成本为16％，计算达一投资项目的净现值并说明是否可行。 　　NPV（ ，15000，12000，13000，18000，8000）＝9620（元） 净现值大于0，所以项目可行。

56 2.内含报酬率的计算 IRR 返回由数值代表的一组现金流的内部收益率。这些现金流不一定必须为均衡的，但作为年金，它们必须按固定的间隔发生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付(负值)和收入(正值)。

57 语法：IRR(values,guess) Values为数组或单元格的引用，包含用来计算内部收益率的数字，Values必须包含至少一个正值和一个负值，以计算内部收益率，函数IRR根据数值的顺序来解释现金流的顺序。故应确定按需要的顺序输入了支付和收入的数值，如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略。 Guess为对函数IRR计算结果的估计值。

58 假设要开办一家饭店。估计需要￥70,000的投资，并预期今后五年的净收益为：￥12,000、 ￥15,000、￥18,000、￥21,000和￥26,000。B1：B6分别包含下面的数值：￥-70,000、￥12,000、 ￥15,000、￥18,000、￥21,000和￥26,000。 计算此项投资四年后的内部收益率：IRR(B1：B5)等于-2．12％ 计算此项投资五年后的内部收益率：IRR(B1：B6)等于8．66％ 计算两年后的内部收益率，必须在函数中包含guess：IRR(B1：B3，-10％)等于-44．35％

59 试验案例： 贴现率为6%,计算分析该投资是否合算？ 按（1）的数据计算该投资净现金流的收益率。

60 (1)通过净现值的计算，分析该项投资是否合算
第一步：光标移在B7上，选择“插入/函数”命令，出现相应对话框。在“函数分类”中选“财务”， “函数名”中选“NPV"，单击“确定”按钮，出现“NPV函数”对话框。

61 第二步:按要求输入NPV函数的参数, 在“Rate”文本框中输入“6％”，在“Valuel”文本框中输入“C6：L6”单元格区域。

62 第三步：在公式“=NPV(6％，C6：16)”后面减去第0年投入的20万。
即公式“=NPV(6％，C6：L6)+(-20)” 或公式“=NPV(6％，C6：L6)+B6"。 第四步：单击“确定”按钮，B7单元格显示结果为￥654．27万元。

63 (2)计算净现金流的收益率 第一步：光标移在E7上，选择“插入/函数”命令，出现相应对话框。在“函数分类”中选“财务”， “函数名”中选“IRR”，单击“确定”按钮，出现“NPV函数”对话框。

64 第二步:按要求输入IRR函数的参数,在“Values’’文本框中输入净现金流的单元格区域B6：L6，如图所示。

65 第四节 利用Excel进行会计报表分析 一、会计报表分析的方法 1．比较分析法 2．比率分析法 3．趋势分析法 4．因素分析法
比较分析法是指通过主要项目或指标数值变化的对比，确定出差异，分析和判断企业经营及财务状况的一种方法。 2．比率分析法 比率分析法是利用指标间的相互关系，通过计算比率来考察、计量和评价企业经济活动效益的一种方法。 3．趋势分析法 趋势分析法是根据企业连续数期的财务报表，以第一期或另外选择某一期为基础，计算每一期各项目对基期同一项目的趋势百分比、计算趋势比率及指数，形成一系列具有可比性的百分数或指数，以揭示企业总体财务状况和经营成果或分项内容的发展趋势。 4．因素分析法 因素分析法是通过顺序变换影响某一经济指标的各个因素的数量，来计算各个因素的变动对该经济指标总的影响程度的一种方法。因素分析法主要用于寻找问题的成因，为一步有针对性地解决问题和企业内部考核提供依据。

66 二、利用Excel进行会计报表分析的步骤
1．确定分析目标和分析范围 2．收集、获取分析资料 3．选择分析方法并建立相应的分析模型 4．确定分析标准并做出分析结论

67 三、利用Excel进行会计报表分析的数据来源
1．会计核算数据源 2．外部数据库

68 四、利用Excel进行会计报表分析 （一）结构分析 1．资产负债表的结构分析 （1）资产负债表的总体结构分析
资产负债表的总体结构分析，是指分别以资产总额和负债及所有者权益总额为总量指计算其他各个项目占资产总额和负债及所有者权益总额的百分比，从而反映企业的资产总额和资金总额的总体构成情况的分析方法。 （2）资产负债表的分类结构分析 资产负债表的分类结构分析，是指分别以资产项目和负债及所有者权益项目中的各个分类项目的合计数为总量指标，计算各个分类项目中每个子项目占其所属分类项目合计数的百分比所做的分析。

69 2．利润表的结构分析 利润表的结构分析可以从两个方面进行： （1）以主营业务收入为总量指标，计算其他各个项目占主营业务收入的百分比；
（2）以利润总额为总量指标，计算其他各个项目占利润额的百分比。

70 3．现金流量表的结构分析 （1）现金流量表的总体结构分析 （2）现金流量表的分类结构分析
现金流量表的总体结构分析是指对企业一定时期的现金流入、现金流出和现金流量净额的构成情况进行分析，以反映现金流量主要构成情况。 （2）现金流量表的分类结构分析 现金流量表的分类结构分析是指对企业一定时期的经营活动、投资活动和筹资活动的现金流入和现金流出的具体构成情况进行分析，以反映企业的各项经济活动现金流量发生及构成的详细信息。

71 （二）趋势分析 1．资产负债表的趋势分析 2．利润表的趋势分析 3．现金流量表的趋势分析
资产负债表的趋势分析，是指对资产负债表各项目前后两期的变动情况进行分析，有时根据需要也可以对有关项目连续多期的变动趋势进行分析，从而揭示和预测未来发展趋势的一种方法。 2．利润表的趋势分析 利润表的趋势分析是指对利润表的各个项目不同时期的金额进行对比，以反映企业经营成果的变化情况，从而判断变动趋势的分析方法。 3．现金流量表的趋势分析 现金流量表的趋势分析是指对连续数期现金流量有关项目的数额进行比较，以反映其变动趋势的分析方法。

72 （三）比率分析 1．资产负债表的比率分析 2．利润表的比率分析 3．现金流量表的比率分析
比率分析是指把某些彼此有关联的项目加以对比，计算出比率，并据以反映企业财务状况和经营成果等有关情况的一种分析方法。比率以相对数的形式揭示了数据之间的内在联系，同时也克服了绝对值在分析中的缺陷。 2．利润表的比率分析 利润表的比率分析是指利用利润表和资产负债表的数据，来分析企业盈利能力、营运能力、成长能力的一种分析。 3．现金流量表的比率分析 现金流量表的比率分析是指利用现金流量表同资产负债表和利润表的数据联系，以分析企业的利润质量、获取现金能力。

73 本章思考题： 1、FV函数的含义及参数如何？ 2、PV函数的含义及参数如何？ 3、NPV函数的含义及参数如何？
4、IRR函数的含义及参数如何？ 5、在EXCEL中如何实现直线法固定资产折旧的处理？ 6、在EXCEL中如何实现双倍余额递减法固定资产折旧的处理？ 7、在EXCEL中如何实现年数总和法固定资产折旧的处理？ 8、会计报表分析的方法的方法有哪些？ 9、利用Excel进行会计报表分析的步骤？ 10、利用Excel进行会计报表分析的数据来源有哪些？ 11、如何利用Excel进行会计报表分析？