1、Excel 在财务管理上的应用实验报告1学生学号0120903921107实验课成绩学 生 实 验 报 告 书实验课程名称 专业综合实验开 课 学 院 管理学院指导教师姓名 周国强学 生 姓 名 李操龙学生专业班级 财务 0902 班2012 2013 学年 第 1 学期 Excel 在财务管理上的应用实验报告2Excel 在财务管理上的应用实验报告3实验报告填写规范1、 实验是培养学生动手能力、分析解决问题能力的重要环节；实验报告是反映实验教学水平与质量的重要依据。为加强实验过程管理，改革实验成绩考核方法，改善实验教学效果，提高学生质量，特制定本实验报告书写规范。2、 本规范适用于管理学院实
2、验课程。3、 每门实验课程一般会包括许多实验项目，除非常简单的验证演示性实验项目可以不写实验报告外，其他实验项目均应按本格式完成实验报告。在课程全部实验项目完成后，应按学生姓名将各实验项目实验报告装订成册，构成该实验课程总报告，并给出实验课程成绩。4、 学生必须依据实验指导书或老师的指导，提前预习实验目的、实验基本原理及方法，了解实验内容及方法，在完成以上实验预习的前提下进行实验。教师将在实验过程中抽查学生预习情况。5、 学生应在做完实验后三天内完成实验报告，交指导教师评阅。6、 教师应及时评阅学生的实验报告并给出各实验项目成绩，同时要认真完整保存实验报告。在完成所有实验项目后，教师应将批改好
3、的各项目实验报告汇总、装订，交课程承担单位（实验中心或实验室）保管存档。附表：实验成绩考核建议观测点 考核目标 成绩组成实验预习1 对验证型实验，考察对实验原理与方法的预习情况2 对于综合型、设计型实验，着重考查设计方案的科学性、可行性和创新性对实验目的和基本原理的认识程度，对实验方案的设计能力20%实验过程1 是否按时参加实验2 对实验过程的熟悉程度3 对基本操作的规范程度4 对突发事件的应急处理能力5 实验原始记录的完整程度6 同学之间的团结协作精神着重考查学生的实验态度、基本操作技能；严谨的治学态度、团结协作精神30%结果分析1 所分析结果是否用原始记录数据2 计算结果是否正确3 实验结
4、果分析是否合理4 对于综合实验，各项内容之间是否有分析、比较与判断等考查学生对实验数据处理和现象分析的能力；对专业知识的综合应用能力；事实求实的精神50%Excel 在财务管理上的应用实验报告4实验项目名称 专业综合实验（EXCEL 操作在财务领域的应用）实验者 李操龙 专业班级 财务 0902 班同组者 1 实验日期 2013.122013.1.18一、 实验目的、意义通过本课程的学习操作，加强学生对财务管理基本理论的理解、对财务管理基本技能、基本方法的掌握和应用，使学生能够系统全面地掌握现代财务管理的基本原理和具体方法；将财务管理理论与实践及 Excel 应用有机地结合在一起，培养和提高实
5、际工作能力、创新能力、使用计算机的能力；在实验操作完成后，根据实验内容详细地写一份实验报告，锻炼和提高学生的分析和写作能力。二、 实验基本原理与方法1、资金时间价值、负债与权益资金决策、项目投资决策、证券投资管理、流动资金管理、资金成本和资本结构、收益分配管理和决策、财务预算、财务控制和财务预警、企业价值评估等财务管理的基本理论和方法。2、EXCEL 模型及财务函数应用。三、 实验内容及要求序号 实验项目名称 内容提要实验学时1 资金时间价值 复利终值和现值计算、年金计算、贷款实际利率计算、抵押贷款的分期支付42 负债与权益资金决策 银行借款长期筹资决策、融资租赁筹资决策、债券筹资决策、股票筹
6、资决策、 43 项目投资决策 投资决策评价方法、固定资产折旧、固定资产更新决策、 44 证券投资管理 股票收益率计算、股票投资组合收益与风险分析、债券估价分析 45 流动资金管理 现金最佳持有量管理、信用方案比较选优、存货决策 46 资金成本和资本结构个别资金成本和综合资金成本计算、边际资金成本计算、杠杆系数计算与分析、最优基本结构分析47 收益分配管理和决策上市公司股利分配方案与股利政策分析 28 财务预算 销售收入预算、生产预算、直接材料预算、现金预算、财务报表预算29 财务控制和财务预警责任中心业务考核、财务预警模型 410企业价值评估 企业价值模型及敏感性分析 4具体内容及要求参见教材
7、财务管理及 Excel 应用指导 （陈玉菁、沙一心、何俊骏等编著，清华大学出版社，2012 年 5Excel 与月第一版）有关章节Excel 在财务管理上的应用实验报告5四、实验方案或技术路线（只针对综合型和设计型实验）1. 登陆 office2003 或 office2007；2. 学生本人进入系统，完成有关作业；3. 实验过程中截图；4. 实验完毕，点击数据保存，并点击提交；5. 将自己所做实验环节进行下载并打印，同时按照要求进行实验分析。Excel 在财务管理上的应用实验报告6五、实验原始记录（可附加页）（程序设计类实验：包括原程序、输入数据、运行结果、实验过程发现的问题及解决方法等；分
8、析与设计、软件工程类实验：编制分析与设计报告，要求用标准的绘图工具绘制文档中的图表。系统实施部分要求记录核心处理的方法、技巧或程序段；其它实验：记录实验输入数据、处理模型、输出数据及结果分析）（一）资金时间价值1.第 17 页第 1 题知识预备：按公式计算年金现值，P=A（1-（1+i ）（-n）/i 试验中将用到 PV 函数。年金计算： 年金现值 PV(rate,nper,pmt,0,type) Rate：贷款利率，nper：贷款付款期数，pmt：每期付款额，pv：现值Fv：未来值，type：数字 0 为期末，数字 1 为期初。操作步骤：创建一工作簿，在工作表中输入有关数据，在 pv 对应的
9、表格里面计算出年金为 5 万元，期限为 8 年的，折现率为 6%的年金现值。如图 1-1 所示。然后与市场价格进行对比选择。在 B5 中输入图 1-1 中函数。同时选中数据插入图表。Excel 在财务管理上的应用实验报告7图 1-1实验结果：如图 1-2 所示：rate 6%nper 8pmt 50000type 0pv 310,489.69首 付 600000方 案 1 910,489.69市 场 单 价 7000面 积 100方 案 2 7000000.00200,000.00400,000.00600,000.00800,000.001,000,000.00方 案 1 方 案 2系 列
10、1图 1-2通过比较得出应该分 8 年支付。2.第 17 页第 2 题：知识预备：复利终值：可按 F=P（1+i）n 的公式计算，也可以利用 Excel 中的 FV函数来求解。FV 函数：FV（rate，nper，pmt，pv，type）在计算本题时，可以采用单变量求解。实验步骤：建立 excel 工作簿，输入本题相关数据，在 J4 对应单元格里面输入图 1-3 所示的函数 . 图 1-3建立单变量求解（数据-假设分析-单变量求解）。如图 1-4 所示Excel 在财务管理上的应用实验报告8图 1-4实验结果：如图 1-5 所示rate 7%nper 6pv 20000fv 30,000.00
11、type 0图 1-5所以年利率为 7%3、 第 17 页第 3 题 知识预备与操作步骤：建立工作簿输入题中相关数据，计算总投资额， Rate：贷款利率，nper：贷款付款期数，pmt：每期付款额，pv：现值，Fv：未来值，type：数字 0 为期末，数字 1 为期初。为年金终值计算 FV（rate ，nper，pmt， type）在B17 中输入图 1-6 所示函数Excel 在财务管理上的应用实验报告9图 1-6实验结果：如图 1-7 所示：rate 10%nper 3pmt 1500fv 4,965.00type 0图 1-7知识预备与操作步骤：建立工作簿输入题中相关数据，计算总投资额，
12、 Rate：贷款利率，nper：贷款付款期数，pmt：每期付款额，pv：现值，Fv：未来值，type：数字 0 为期末，数字 1 为期初。计算年支付额，为年金的计算PMT（ rate， nper，pv，type ）在 J17 中输入图 1-8 中所示函数图 1-8实验结果：如图 1-9 所示Excel 在财务管理上的应用实验报告10rate 10%nper 5pv 4,965.00pmt 1,309.75type 0图 1-9知识预备与操作步骤：建立工作簿输入题中相关数据，计算总投资额， Rate：贷款利率，nper：贷款付款期数，pmt：每期付款额，pv：现值，Fv：未来值，type：数字
13、0 为期末，数字 1 为期初。计算偿还期，为期限的计算NPER（rate，pmt ，pv，type ）在 T18 中输入图 1-10 中所示函数图 1-10实验结果：如图 1-11 所示rate 10%pmt 1000pv 4,965.00type 0nper 4.2296536图 1-114、第 17 页第 4 题知识预备：复利终值的计算；实际年利率的计算Excel 在财务管理上的应用实验报告11操作步骤：计算实际年利率公式如图 1-12 所示；计算终值 B25=B21*(1+B24)图 1-12实验结果如图 1-13 所示贷 款 额 500000年 利 率 12%年 复 利 次 数 4实
14、际 年 利 率 12.55%年 末 归 还 数 额 562754.41图 1-13（二）负债与权益资金决策1.第 40 页第 1 题：预备知识：PMT（rate ， nper，pv）PPMT（rate，per，nper ，pv ）IPMT（rate ， per，nper ， pv）操作步骤： 创建工作簿，输入数据至工作表 计算还款总期数和每期还款数。如图 2-1 所示Excel 在财务管理上的应用实验报告12 建立双变量模型工作表 进行双变量模拟运算。如图 2-2 所示 建立筹资现值计算表格 计算等额还款金额如图 2-3 所示 计算偿还本金。如图 2-4 所示 计算期初尚欠本金，如图 2-5
15、所示，D20=B2 计算偿还利息、避税额、净现金流量、现值和合计值。如图 2-6 所示F20=E20*$B$17 ,G20=B20-F20, H20=G20/(1+$B$18)A20拖动求出所有结果图 2-1图 2-2Excel 在财务管理上的应用实验报告13图 2-3图 2-4图 2-5Excel 在财务管理上的应用实验报告14图 2-6实验结果：如图 2-7 所示借 款 金 额 2000000借 款 年 利 率 8%借 款 期 限 5每 年 还 款 期 数 1还 款 总 期 数 5分 期 等 额 还 款 款 金 额 -500,912.91长 期 借 款 基 本 模 型图 2-7-1借 款
16、年 利 率-500,912.91 5 6 78% -500,912.91 -432630.7725 -384144.80299% -514184.9139 -445839.5666 -397381.033710% -527594.9616 -459214.7607 -410810.9994还 款 总 期 数图 2-7-2所 得 税 税 率贴 现 率期 限 等 额 还 款 金 额 偿 还 本 金 期 初 尚 欠 金 额 偿 还 利 息 利 息 避 税 净 现 金 流 量 现 值1 500,912.91 340,912.91 2,000,000.00 160,000.00 40,000.00 46
17、0,912.91 438,964.682 500,912.91 368,185.94 1,659,087.09 132,726.97 33,181.74 467,731.17 424,245.963 500,912.91 397,640.82 1,290,901.15 103,272.09 25,818.02 475,094.89 410,404.834 500,912.91 429,452.08 893,260.33 71,460.83 17,865.21 483,047.70 397,404.545 500,912.91 463,808.25 463,808.25 37,104.66 9,
18、276.16 491,636.74 385,210.25合 计 2,504,564.55 2,000,000.00 6,307,056.82 504,564.55 126,141.14 2,378,423.41 2,056,230.25长 期 借 款 筹 资 现 值 计 算25%5%图 2-7-3Excel 在财务管理上的应用实验报告152.第 40 页第 2 题：实验预备知识：第一问：PV(rate，nper，pmt，fv)；第二问：单变量求解 （数据-假设分析-单变量求解） 实验步骤：建立工作簿，输入相关数据，在 B34 中录入图 2-8 中函数图 2-8实验结果：如图 2-9 所示发 行
19、 面 值 2000期 限 （ 年 ） 6票 面 年 利 率 8%市 场 利 率 10%发 行 价 格 1,825.79债 券 估 值 分 析图 2-9 实验步骤：输入相关数据，先在发行价格中输入图 2-10 中公式，然后进行单变量求解Excel 在财务管理上的应用实验报告16图 2-10实验结果：如图 2-11发 行 面 值 2000期 限 （ 年 ） 6票 面 年 利 率 8%市 场 利 率 9%发 行 价 格 1,910.00债 券 估 值 分 析图 2-113 第 40 页第 3 题知识预备：每期支付额计算函数：PMT(rate，nper，pv，1)操作步骤：建立模型，计算每期支付金额如
20、图 2-12 所示 建立筹资模型，计算流量现值。如图 2-13 所示图 2-12Excel 在财务管理上的应用实验报告17图 2-13实验结果：如图 2-14租 赁 资 产 设 备租 金 3000000租 期 5租 赁 年 利 率 8%每 年 支 付 次 数 1总 支 付 次 数 5支 付 方 式 期 初每 期 支 付 金 额 751369.53租 赁 筹 资 模 型图 2-14-1租 赁 筹 资 现 值 计 算所 得 税 税 率 25%贴 现 率 6%年 末 租 金 支 付 额 避 税 额 税 后 现 金 流 量 现 值0 751369.53 187842.38 563527.15 5635
21、27.151 751369.53 187842.38 563527.15 531629.392 751369.53 187842.38 563527.15 501537.163 751369.53 187842.38 563527.15 473148.264 751369.53 187842.38 563527.15 446366.29合 计 3756847.67 939211.92 2817635.75 2516208.24图 2-14-24 第 40 页第 4 题知识预备：Excel 在财务管理上的应用实验报告18每股收益=发行当年预计净利润/（发行前总股本+拟增发股本（12-发行时月份）
22、/12），股票发行价 =每股收益市盈率操作步骤：建立表格计算每股收益，根据市盈率计算发行价。如图 2-15 所示图 2-15实验结果：如图 2-16 所示当 前 总 股 本 3000拟 增 发 股 本 1500发 行 市 盈 率 25发 行 月 份 6当 年 预 计 净 利 润 2000每 股 收 益 0.533333333股 票 发 行 价 13.33333333市 盈 率 定 价 法图 2-16（三）项目投资决策1.第 52 页第 1 题：实验预备知识：NPV(rate，value1，value2，)IRR(values,guess)Excel 在财务管理上的应用实验报告19实验步骤：建立
23、表格计算净现值如图 3-1；计算内含报酬率如图 3-2,；计算现值指数如图 3-3；计算回收期如图 3-4.函数如各图中所示。图 3-1图 3-2Excel 在财务管理上的应用实验报告20图 3-3图 3-4实验结果：如图 3-5期 间 A方 案 现 金 流 B方 案 现 金 流 资 金 成 本0 -30000 -40000 10%1 7000 110002 10000 150003 12000 180004 15000 16000净 现 值 3,889.08 6,848.58内 含 报 酬 率 15% 17%现 值 指 数 1.129635954 1.1712144回 收 期 3.06666
24、6667 2.7777778投 资 决 策 评 价 方 法图 3-52.第 52 页第 2 题：实验预备知识：双倍余额递减法 DDB（cost，salvage ，life，period，factor）年数总和法 SYD(cost，salvage，life，period)实验步骤：建立表格输入相关数据，双倍余额法计提折旧，函数如图 3-6 中所示；年数总和法计提折旧，函数如图 3-7 中所示。Excel 在财务管理上的应用实验报告21图 3-6图 3-7实验结果：如图 3-8 所示设 备 折 旧 值 计 算原 值 残 值 年 限5000000 250000 5年 限 1 2 3 4 5双 倍 余
25、 额 递 减 法 2,000,000.00 1,200,000.00 720,000.00 415,000.00 415,000.00年 数 总 和 法 1,583,333.33 1,266,666.67 950,000.00 633,333.33 316,666.67图 3-8第 52 页第 3 题预备知识：税前利润=收入 -付现成本-折旧；Excel 在财务管理上的应用实验报告22所得税=税前利润 *所得税；营业现金流=税后利润加折旧操作步骤：建立现金流量计算表格，输入数据；计算税前利润；计算所得税和税后利润；计算现金流量。如图 3-9 所示其中几个单元格函数为：C38=(SLN($B$2
26、5,$D$24,4)-SLN($F$25,$D$23,4)*25%C39=(DDB($B$25,$D$24,4,C27)-DDB($F$25,$D$23,4,C27)*25%C40=(SYD($B$25,$D$24,4,C27)-SYD($F$25,$D$23,4,C27)*25%E39=(B25-D24-DDB(B25,D24,4,1)-DDB(B25,D24,4,2)/2-(F25-D23-DDB(F25,D23,4,1)-DDB(F25,D23,4,2)/2)*25% ，G44=G41/(1+$B$23)($G$27-1)此处公式太多不便一一列举。项目决策也要考虑机会成本和税率的影响。E
27、xcel 在财务管理上的应用实验报告23图 3-9实验结果：如 3-10 所示Excel 在财务管理上的应用实验报告24资 金 成 本 10%旧 设 备 残 值 5000旧 设 备 残 值 收 入 纳 税 1250税 率 25%新 设 备 残 值 18000新 设 备 残 值 收 入 纳 税 4500新 设 备 购 买 价 80000.00 旧 设 备 出 售 价 43000旧 设 备 账 面 净 值 45000年 期 0 1 2 3 4 5新 设 备 现 金 流 出 -60000.00出 售 设 备 现 金 流 入 32250.00出 售 设 备 损 失 抵 税 660.00更 新 设 备
28、导 致 的 残 值 收 入 减 少 -3750.00新 设 备 残 值 收 入 13500.00新 设 备 增 加 销 售 收 入 8000.00 8000.00 8000.00 8000.00新 设 备 减 少 的 经 营 成 本 3000.00 3000.00 3000.00 3000.00增 加 的 税 前 利 润 11000.00 11000.00 11000.00 11000.00所 得 税 2750.00 2750.00 2750.00 2750.00增 加 的 税 后 利 润 8250.00 8250.00 8250.00 8250.00折 旧 抵 税 差 额 （ 直 线 法 ）
29、 1375.00 1375.00 1375.00 1375.00折 旧 抵 税 差 额 （ 双 倍 余 额 递 减 法 ） 4375.00 2187.50 -531.25 -531.25折 旧 抵 税 差 额 （ 年 数 总 和 法 ） 2200.00 1650.00 1100.00 550.00每 年 现 金 流 （ 直 线 法 ） -27090.00 9625.00 9625.00 9625.00 9625.00 9750.00每 年 现 金 流 （ 双 倍 余 额 递 减 法 ） -27090.00 12625.00 10437.50 7718.75 7718.75 9750.00每 年
30、 现 金 流 （ 年 数 总 和 法 ） -27090.00 10450.00 9900.00 9350.00 8800.00 9750.00现 值 （ 直 线 法 ） 37169.34 8750.00 7954.55 7231.40 6574.00 6659.38现 值 （ 双 倍 余 额 递 减 法 ） 37833.91 11477.27 8626.03 5799.21 5272.01 6659.38现 值 （ 年 数 总 和 法 ） 37376.51 9500.00 8181.82 7024.79 6010.52 6659.38NPV直 线 法 10079.34NPV双 倍 余 额 递
31、减 法 10743.91NPV年 数 总 和 法 10286.51新 设 备 现 金 流 量 计 算 （ 符 号 表 示 现 金 流 动 方 向 ）图 3-10由此可知，无论采取何种折旧方法。更新设备总是会有正的净现值。故更新设备！（四）证券投资管理1.第 69 页第 2 题：实验预备知识：方差、标准差可以股票收益率的波动性，通过协方差、相关系数可以判定股票间的相关性相关系数函数 CORREL（array1 ，array2）协方差函数 COVAR（array1，array2）方差函数 VARP(number1，number2，)标准差函数 STDEVP(number1，number2 ，)Ex
32、cel 在财务管理上的应用实验报告25E(r)=xE(rA)+(1-x)E(rB)VARP（r）=x2*VARP(rA)+（1-x）2*VARP(rB)+2*x*(1-x)*COVAR(rA,rB)实验步骤：建立表格，输入相关数据，计算组合收益如图 4-1 所示图 4-1实验结果：如图 4-2股 票 投 资 组 合 收 益 与 风 险 分 析A股 票 B股 票 A股 票 B股 票 C股 票投 资 额 50 250 50 200 150平 均 收 益 10% 20% 10% 20% 15%贝 塔 系 数 1.2 0.35 1.2 0.35 1.57组 合 收 益组 合 风 险购 买 C股 票 后
33、0.183333333 0.16875图 4-22.第 69 页第 3 题知识预备：运用 PV 函数返回投资的现值。PV(rate,nper,pmt,fv,type)操作步骤：建立表格，输入相关数据，计算函数如图 4-3 所示Excel 在财务管理上的应用实验报告26图 4-3实验结果：如图 4-4 所示债 券 购 买 价 计 算 分 析面 值 1000票 面 利 率 6%年 付 息 次 数 2市 场 利 率 10%期 限 5市 场 价 569.88图 4-4而市场价为 1100 元，故不应该购买。3、 第 69 页第 4 题知识预备：布莱克-舒尔斯模型期权定价公式为：C=SN(d1)-Xe(
34、-rT)N(d2)d1=(ln(S/X）+(r+2/2)*T)/( *SQRT(T)d2=d1-*SQRT(T)NORMSDIST 函数：返回标准正态分布的数值操作步骤创建表格，输入相关数据；计算股票收益率如图 4-5 所示；计算股票波动率如图 4-6 所示；计算 d1 如图 4-7 所示；计算 d2 如图 4-8 所示；计算认股权证理论价格如图 4-9 所示，其中 B30=NORMSDIST(B28) Excel 在财务管理上的应用实验报告27B31=NORMSDIST(B29)图 4-5图 4-6图 4-7图 4-8Excel 在财务管理上的应用实验报告28图 4-9实验结果：如图 4-1
35、0 所示B-S模 型 认 股 权 证 定 价 分 析 日 期 股 价期 限 1 2 3 0 100当 前 股 价 115 105 125 1 115 0.139762行 权 价 格 110 115.5 121.275 2 105 -0.09097无 风 险 利 率 3.25% 3.25% 3.25% 3 125 0.174353到 期 时 间 3 2 1股 票 波 动 率 0.117772 0.117772 0.117772d1 2.39364 -0.19741 0.591721d2 2.189653 -0.36397 0.473949N(d1） 0.991659 0.421753 0.722
36、981N(d2） 0.985725 0.357941 0.682232认 股 权 证 理 论 价 格 15.68387 5.543554 10.28074图 4-10由于 3 万份认股权证每年只能执行 1/3，未执行的可以累积到以后年度执行。故最优方案是第一年执行 10000 份，第二年不执行，第三年执行 20000 份。认股权证的获利情况为：如图 4-11 所示认 股 权 证 理 论 价 格 15.68387 5.543554 10.28074执 行 情 况 10000 0 20000获 利 362453.4279图 4-11（五 ）流动资金管理1.第 84 页第一题：Excel 在财务管理
37、上的应用实验报告29预备知识：Q=SQRT(2*T*F/K)TC=SQRT(2*T*F*K)T 为现金总需求量；F 为每次转换固定成本；Q 为最佳现金持有量； K 为机会成本（有价证券利息率）；TC 现金管理总成本操作步骤：创建工作簿，建立表格输入相关数据；计算最低现金持有量如图 5-1 所示；计算最低现金管理成本如图 5-2 所示；计算持有机会成本如图 5-3 所示；交易次数和间隔期如图 5-4 所示，其中 B9=365/B8.图 5-1图 5-2Excel 在财务管理上的应用实验报告30图 5-3图 5-4实验结果：如图 5-5 所示预 计 需 求 量 400000转 换 成 本 200有 价 证 券 利 息 率 10%最 佳 现 金 持 有 量 28284.27最 低 现 金 管 理 成 本 4000持 有 机 会 成 本 1414.214有 价 证 券 交 易 次 数 14.14214有 价 证 券 交 易 间 隔 期 25.8094现 金 管 理 计 算 表图 5-52.第 85 页第 2 题：预备知识：应收账款款平均余额=年赊销额/应收账款周转率
