第五章EXCEL在会计中的应用ppt课件.ppt

第四章EXCEL在会计中的应用 本章的学习目的与要求 通过本章的学习 使学生了解并掌握EXCEL系统的基本操作 能够利用EXCEL进行固定资产折旧的计算 进行货币时间价值的分析以及利用EXCEL进行投资决策 课时分配 10学时 含上机6学时 第一节固定资产折旧的计算 按照现行制度规定 企业计提折旧的方法有 直线法 双倍余额递减法和年数总和法 按照传统的手工计算方法计算起来比较烦琐 我们利用Excel计算就方便多了 1 直线法 SLN 用途 在ExceI中使用直线折旧法提取的折旧 函数 SLN cost salvage 1ife 参数 Cost为资产原值 Salvage为资产在折旧期末的价值 也称为资产残值 Life为折旧期限 有时也可称作资产的生命周期 例如 某企业有一台机器原始成本为20万元 预计可使用10年 预计残值为1万元每年的折旧额为 SLN 200000 10000 10 19000 元 每月的折旧额为 SLN 200000 10000 5 12 1583 33 元 2 双倍余额递减法 DDB 双倍余额递减法是加速折旧计算法 用此法提取折旧第一期折旧额较大 以后各期依次减少 用途 使用双倍余额递减法或其他指定方法 计算一笔资产在给定期间内的折旧值 语法 DDB cost salvage life period factor 参数 Cost为资产原值 Salvage为资产在折旧期末的价值 也称为资产残值 Life为折旧期限 有时也称作资产的使用寿命 Period为需要计算折旧值的期间 Period必须使用与life相同的单位 Factor为余额递减速率 如果factor省略 则假设为2 例如 某企业进口一条生产线 安装完毕后 固定资产原值为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 3 固定余额递减法 DB 用途 使用固定余额递减法 计算一笔资产在给定期间内的折旧值 函数 DB cost salvage life period month 参数 Cost为资产原值 Salvage为资产在折旧期末的价值 也称为资产残值 Life为折旧期限 有时也可称作资产的生命周期 Period为需要计算折旧值的期间 Period必须使用与life相同的单位 Month为第一年的月份数 如省略 则假设为12 例如 假定某工厂购买了一台新机器 价值为 1 000 000 使用期限为8年 残值为 100 000 下面的例子给出机器在使用期限内的历年折旧值 结果保留整数 DB 1000000 100000 8 1 等于 250000DB 1000000 100000 8 2 等于 187500DB 1000000 100000 8 3 等于 140625DB 1000000 100000 8 4 等于 105468 75DB 1000000 100000 8 5 等于 79101 5625DB 1000000 100000 8 6 等于 59326 17188DB 1000000 100000 8 7 等于 44494 62891DB 1000000 100000 8 8 等于 33370 97168 4 年数总和法 SYD 用途 使用年数总和法 计算一笔资产在给定期间内的折旧值 函数 SYD cost salvage 1ife period 参数 Cost为资产原值 Salvage为资产在折旧期末的价值 也称为资产残值 Life为折旧期限 有时也可称作资产的生命周期 period为期间 其单位与life相同 例如 某项固定资产原值为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 试验案例 试验例题1 某工厂购买一台新设备 价值1 000000元 使用年限为10年 残值为10 000元 用四种折旧函数计算每年的折旧额 结果保留整数 用EXCEL实例介绍 1 使用SLN 平均年限法 函数第一步 光标移在B5上 选择 插入 函数 命令 出现相应对话框 在 函数分类 中选 财务 函数名 中选 SLN 单击 确定 按钮 出现 SLN函数 对话框 第二步 按要求输入SLN函数的参数 固定资产原值Cost B 1 固定资产残值Salvage B 2 预计使用年限Life B 3 注意使用绝对引用 如图所示 第三步 单击 确定 按钮 在B5单元格上显示结果为 98 000 00 第四步 拖曳B5单元格右下角的自动填充柄到B14 使B5单元格中已有函数自动按序列复制到B6 B7 B8 B9 B10 Bll B12 B13 B14单元格中 第五步 利用SUM函数求累计折旧 第六步 计算结果如下 2 使用DDB 双倍余额递减法 函数 第一步 光标移在C5上 选择 插入 函数 命令 出现相应对话框 在 函数分类 中选 财务 函数名 中选 DDB 单击 确定 按钮 出现 DDB函数 对话框 第二步 按要求输入DDB函数的参数 固定资产原值Cost B 1 固定资产残值Salvage B 2 预计使用年限Life B 3 注意使用绝对引用 第三步 在Period文本框中输入计算年期 A5 注意使用相对引用 在Factor文本框中输入 2 如图所示 第四步 单击 确定 按钮 在B5单元格上显示结果为 200 000 00 第五步 拖曳C5单元格右下角的自动填充柄到C12 使C5单元格中已有函数自动按序列复制到C6 C7 C8 C9 C10 Cll B12 单元格中第六步 单击C13单元格 输入公式 B1 B2 SUM C5 C12 2 按回车键第七步 在C14单元格中输入公式 C13 按回车键第八步 利用SUM函数求累计折旧 第九步 计算结果如下 3 使用DB 固定余额递减法 函数 第一步 光标移在D5上 选择 插入 函数 命令 出现相应对话框 在 函数分类 中选 财务 函数名 中选 DB 单击 确定 按钮 出现 DB函数 对话框 第二步 按要求输入DB函数的参数 固定资产原值Cost B 1 固定资产残值Salvage B 2 预计使用年限Life B 3 注意使用绝对引用 第三步 在Period文本框中输入计算年期 A5 注意使用相对引用 在Month文本框中输入 12 参数Month为第一年的月份数 一般选12 可以省略 如图所示 第四步 单击 确定 按钮 在D5单元格上显示结果为 324 000 00 第五步 拖曳D5单元格右下角的自动填充柄到D13 使D5单元格中已有函数自动按序列复制到D6 D7 D8 D9 D10 Dll D12 D13单元格中第六步 单击D13单元格 输入公式 B1 B2 SUM D5 D13 按回车键第七步 利用SUM函数求累计折旧 第八步 计算结果如下 4 使用SYD 年限总和法 函数 第一步 光标移在E5上 选择 插入 函数 命令 出现相应对话框 在 函数分类 中选 财务 函数名 中选 SYD 单击 确定 按钮 出现 SYD函数 对话框 第二步 按要求输入SYD函数的参数 固定资产原值Cost B 1 固定资产残值Salvage B 2 预计使用年限Life B 3 注意使用绝对引用 第三步 在Period文本框中输入计算年期 A5 注意使用相对引用 如图所示 第四步 单击 确定 按钮 在E5单元格上显示结果为 178 181 82 第五步 拖曳E5单元格右下角的自动填充柄到E14 使D5单元格中已有函数自动按序列复制到E6 E7 E8 E9 E10 Ell E12 E13 E14单元格中第六步 利用SUM函数求累计折旧 第七步 计算结果如下 第二节货币时间价值的计算 货币时间价值的计算 在财务管理中有着广泛的用途 如存货管理 养老金决策 租赁决策 资产和负债估价 长期投资决策等方面 随着财务问题的日益复杂化 时间价值观念的应用也将日益广泛 1 复利终值的计算 复利终值有普通复利终值 普通年金终值和预付年终值等形式 其计算函数为 FV rate nper pmt pv type 其中 rate代表各期利率 是一固定值 nper代表总投资期 或贷款期 即该项投资 或贷款 总的付款期数 pmt代表各期支出金额 在整个投资期内不变 若该参数为0或省略 则函数值为普通复利终值 pv代表现值 即从该项投资 或贷款 开始计算时已经入账的款项 或一系列未来付款当前值的累积和 也称本金 如果省略pv 则假设其值为0 type只有数值0或1 0或忽略表示收付款时间是期末 1表示是期初 注意 1 若pmt为0或省略 则函数值为普通复利终值 2 在pmt不为0 pv 0 type 0时 函数值为普通年金终值 3 在pmt不为0 pv 0 type 1时 函数值为预付年金终值 4 应确认所指定的rate和nper单位的一致性 5 在Excel中 对函数涉及金额的参数 是有特别规定的 即 支出的款项 如向银行存入款项 用负数表示 收入的款项 如股息收入 用正数表示 例 1 1 普通复利终值的计算 例如 某人将10000元投资于一顶事业 年报酬率为6 3年后的复利终值为 FV 6 3 0 10000 0 11910 元 2 普通年金终值的计算 例如 某人每年存入银行20000元 年利率为4 计算第3年年末可以从银行取得的本利和 每年年末存入银行 为 FV 4 3 20000 0 0 62432 元 3 预付年金终值的计算 仍以上例为例 计算预付年金终值 每年年初存入银行 则 FV 4 3 20000 0 1 64929 28 元 例2 某企业设立新产品开发基金 现账户上已有资金100 000元 计划每年末存人10 000元 假设没有支出的情况 年利率为6 10后这笔基金将有多少 FV 6 10 10000 100000 0 等于 310892 7191 例3 假设需要为一年后的某个项目预筹资金 现在将 10000以年利10 按月计息存人储蓄存款帐户中 并在以后十二个月的每个月初存人 1000则一年后该帐户的存款额等于多少 FV 10 12 12 1000 10000 1 等于 23 717 41 2 复利现值的计算 返回投资的现值 现值为一系列未来付款当前值的累积和 例如 借入方的借入款即为贷出方贷款的现值 语法 PV rate nper pmt fv type Rate为各期利率 Nper为总投资 或贷款 期 即该项投资 或贷款 的付款期总数 Pmt为各期所应付 或得到 的金额 其数值在整个年金期间 或投资期内 保持不变 通常pmt包括本金和利息 Fv为未来值 或在最后一次支付后希望得到的现金余额 如果省略fv 则假设其值为零 一笔贷款的未来值即为零 Type 数字0或1 用以指定各期的付款时间是在期初还是期末 如果省略type 则假设其值为零 1 普通复利现值的计算 例如 某人拟在6年后获得本利和50000元 投资报酬率为10 他现在应投入的金额为 PV 10 6 0 50000 0 28223 6965 元 2 普通年金现值的计算 例如 某人要购买一项养老保险 购买成本为60000元 该保险可以在20年内于每月末回报500元 投资报酬率为8 计算这笔投资是否值得 PV 0 08 12 12 20 500 0 0 59777 元 由于养老保险的现值 59777元 小于实际支付的现值 60000元 因此 这项投资不合算 3 预付年金现值的计算 例如 用6年时间分期付款购物 每年预付566元 设银行利率为10 该项分期付款相当于一次现金交付的购价是多少 PV 10 6 200 0 1 958 试验案例 例1 某企业现投资200万 在第5年年末能收入400万 假设年利率为10 问企业是否可接受这个投资 PV 10 5 0 4000000 0 2483685 292 元 计算后的现值为248多万元 因为是流出 所以是负数 比期初投资200万要高 说明可以接受这个投资 3 PMT 基于固定利率及等额分期付款方式 返回投资或贷款的每期付款额 语法 PMT rate npe pv fv type Rate为各期利率 是一固定值 Nper为总投资 或贷款 期 即该项投资 或贷款 的付款期总数 pv为现值 即从该项投资 或贷款 开始计算时已经人账的款项 或一系列未来付款当前值的累积和 也称为本金 fv为未来值 或在最后一次付款后希望得到的现金余额 如果省略fV 则假设其值为零 例如 一笔贷款的未来值即为零 Type 数字0或1 用以指定各期的付款时间是在期初还是期末 如果省略type 则假设其值为零 例 下面的公式将返回需要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 除了用于贷款之外 函数PMT还可以计算出别的以年金方式付款的支付额 例如 如果需要以按月定额存款方式在18年中存款 50 000 假设存款年利率为6 则函数PMT可以用来计算月存款额 PMT 6 12 18 12 0 50000 等于 129 08即向6 的存款帐户中每月存人 129 08 18年后可获得 50 000 试验案例 某企业抵押贷款2000万元 偿还期20年 年利率10 计算每年末应偿还多少才能在贷款期内还清贷款 PMT 10 20 20000000 0 0 等于 2349192 495 4 投资期数的计算 函数NPER rate pmt pv fv type 例如 A公司准备从甲公司购买一台设备 甲公司有两种销货方式供A公司选择 一是现在一次全额付款90万元 二是分若干年每年初付款16万元 假设资金成本为10 如果A公司选择第二种付款方式 甲公司在签定合同时可接受的收款次数至少为多少次 其收入才不低于一次性全额收款 由于A公司和甲公司一个属于付款 另一个属于收款 所以pmt和pv必需有一个用负数表示 则根据题意 NPER 10 160000 900000 0 1 7 51因为收款次数应为正整数 并且不能小于7 51 所以收款次数至少为8次 第三节利用EXCEL进行投资决策 投资决策中 作为评价方案优劣尺度的指标主要有净现值 现值指数和内含报酬率 这些计算通常用手工较为麻烦 Exeel直接提供了计算净现值和内含报酬率的函数 现值指数可以间接地计算得到 1 净现值的计算 基于一系列现金流和固定的各期贴现率 返回一项投资的净现值 投资的净现值是指未来各期支出 负值 和收人 正值 的当前值的总和 语法 NPV rate valuel value2 Rate为各期贴现率 是一固定值 Valuel value2 表1到29笔支出及收入的参数值 Valuel value2 所属各期间的长度必须相等 而且支付及收入的时间都发生在期末 NPV按次序使用Valuel Value2 来注释现金流的次序 所以一定要保证支出和收入的数额按正确的顺序输入 如果参数是数值 空白单元格 逻辑值或表示数值的文字表达式 则都会计算在内 如果参数是错误值或不能转化为数值的文字 则被忽略 如果参数是一个数组或引用 只有其中的数值部分计算在内 忽略数组或引用中的空白单元格 逻辑值 文字及错误值 函数NPV假定投资开始于valuel现金流所在日期的前一期 并结束于最后一笔现金流的当期 函数NPV依据未来的现金流计算 如果第一笔现金流发生在第一个周期的期初 则第一笔现金必须加入到函数NPV的结果中 而不应包含在values参数中 例1 假设第一年投资 10 000 而未来三年中各年的收入分别为 3 000 4 200和 6 800 假定每年的贴现率是10 则投资的净现值是 NPV 10 10000 3000 4200 6800 等于 1 188 44 例2 假如要购买一家鞋店 投资成本为 40 000 并且希望前五年的营业收入如下 8 000 9 200 10 000 12 000和 14 500 每年的贴现率为8 相当于通货膨胀率或竞争投资的利率 如果鞋店的成本及收入分别存储在B1到B6中 下面的公式可以计算出鞋店投资的净现值 NPV 8 B2 B6 B1等于 1 922 06 例3 某公司准备购置一台新设备 价款为40000元 以扩大生产规模 项目周期为5年 各年的净现金流量分别为15000 12000 13000 18000 8000 若资金成本为16 计算达一投资项目的净现值并说明是否可行 NPV 0 1 40000 15000 12000 13000 18000 8000 9620 元 净现值大于0 所以项目可行 2 内含报酬率的计算 IRR返回由数值代表的一组现金流的内部收益率 这些现金流不一定必须为均衡的 但作为年金 它们必须按固定的间隔发生 如按月或按年 内部收益率为投资的回收利率 其中包含定期支付 负值 和收入 正值 语法 IRR values guess Values为数组或单元格的引用 包含用来计算内部收益率的数字 Values必须包含至少一个正值和一个负值 以计算内部收益率 函数IRR根据数值的顺序来解释现金流的顺序 故应确定按需要的顺序输入了支付和收入的数值 如果数组或引用包含文本 逻辑值或空白单元格 这些数值将被忽略 Guess为对函数IRR计算结果的估计值 假设要开办一家饭店 估计需要 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 试验案例 贴现率为6 计算分析该投资是否合算 按 1 的数据计算该投资净现金流的收益率 1 通过净现值的计算 分析该项投资是否合算 第一步 光标移在B7上 选择 插入 函数 命令 出现相应对话框 在 函数分类 中选 财务 函数名 中选 NPV 单击 确定 按钮 出现 NPV函数 对话框 第二步 按要求输入NPV函数的参数 在 Rate 文本框中输入 6 在 Valuel 文本框中输入 C6 L6 单元格区域 第三步 在公式 NPV 6 C6 16 后面减去第0年投入的20万 即公式 NPV 6 C6 L6 20 或公式 NPV 6 C6 L6 B6 第四步 单击 确定 按钮 B7单元格显示结果为 654 27万元 2 计算净现金流的收益率 第一步 光标移在E7上 选择 插入 函数 命令 出现相应对话框 在 函数分类 中选 财务 函数名 中选 IRR 单击 确定 按钮 出现 NPV函数 对话框 第二步 按要求输入IRR函数的参数 在 Values 文本框中输入净现金流的单元格区域B6 L6 如图所示 第三步 单击 确定 按钮 在E7单元格中显示 24 说明该项投资的内部收益率为24 第四节利用Excel进行会计报表分析 一 会计报表分析的方法1 比较分析法比较分析法是指通过主要项目或指标数值变化的对比 确定出差异 分析和判断企业经营及财务状况的一种方法 2 比率分析法比率分析法是利用指标间的相互关系 通过计算比率来考察 计量和评价企业经济活动效益的一种方法 3 趋势分析法趋势分析法是根据企业连续数期的财务报表 以第一期或另外选择某一期为基础 计算每一期各项目对基期同一项目的趋势百分比 计算趋势比率及指数 形成一系列具有可比性的百分数或指数 以揭示企业总体财务状况和经营成果或分项内容的发展趋势 4 因素分析法因素分析法是通过顺序变换影响某一经济指标的各个因素的数量 来计算各个因素的变动对该经济指标总的影响程度的一种方法 因素分析法主要用于寻找问题的成因 为一步有针对性地解决问题和企业内部考核提供依据 二 利用Excel进行会计报表分析的步骤1 确定分析目标和分析范围2 收集 获取分析资料3 选择分析方法并建立相应的分析模型4 确定分析标准并做出分析结论 三 利用Excel进行会计报表分析的数据来源1 会计核算数据源2 外部数据库 四 利用Excel进行会计报表分析 一 结构分析1 资产负债表的结构分析 1 资产负债表的总体结构分析资产负债表的总体结构分析 是指分别以资产总额和负债及所有者权益总额为总量指计算其他各个项目占资产总额和负债及所有者权益总额的百分比 从而反映企业的资产总额和资金总额的总体构成情况的分析方法 2 资产负债表的分类结构分析资产负债表的分类结构分析 是指分别以资产项目和负债及所有者权益项目中的各个分类项目的合计数为总量指标 计算各个分类项目中每个子项目占其所属分类项目合计数的百分比所做的分析 2 利润表的结构分析利