execl财经数据处理实验手册.docx
《execl财经数据处理实验手册.docx》由会员分享，可在线阅读，更多相关《execl财经数据处理实验手册.docx（18页珍藏版）》请在冰点文库上搜索。
execl财经数据处理实验手册
《EXCEL财经数据处理》
课程实验教学手册
上海立信会计学院
2009年9月5日
一、实验教学大纲
课程编号
14063020
课程名称
EXCEL财经数据处理
开课系部
数学与信息学院
课程总课时
34
实验课时
11
执行时间
每次课尾1/3时间
面向专业
管理类经济类各专业
教材资料
刘兰娟主编：
《经济管理中计算机应用》清华大学出版社，2006年10月第1版
本课程实验目的和基本要求
本课程是一门涉及数据库和表处理软件等计算机信息技术，运筹学、统计学等定量分析方法，管理会计、财务管理等经济管理原理等多门学科知识的课程。
它遵循经济管理的原理，采用运筹统计等现代数学方法，以数据库和Execl表处理软件为工具完成对企事业组织的大量财经信息进行处理的应用技术性课程。
本课程内容的掌握和消化主要是通过实验动手环节实现的。
通过学生实验操作，可以逐步领悟和消化该课程内容和较高的应用技能要求，掌握本课程的知识内涵，在今后工作实践中，达到使用EXCEL进行财经数据处理，能融会贯通，应用自如的境地。
实验项
目内容
序号
项目名称
时数
项目类型
必开
选开
内容简述
1
建立ODBC数据源、使用Query进行复杂查询
1
操作性
√
见下文
2
Query和数据透视表的数据分类汇总（时间序列和频率发布等）
1
操作性
√
见下文
3
D函数和模拟运算表、带控件分类汇总
2
操作性
√
见下文
4
盈亏平衡分析模型和安全边际分析
1.5
操作性
√
见下文
5
成本决策模型（带阈值）
2
操作性
√
见下文
6
经济订货量模型
2
操作性
√
见下文
7
投资决策函数，投资决策模型、设备更新模型
1.5
操作性
√
见下文
合计
11
备注
说明
制定人：
朱伟民
审定人：
实验教学负责人
注：
类型指：
操作性、设计性、综合性。
二、实验教学计划安排
日期
实验内容
实验课时
实验形式
备注
每次课尾1/3时间
建立ODBC数据源、使用Query进行复杂查询
1
机房上机
Query和数据透视表的数据分类汇总（时间序列和频率发布等）
1
机房上机
D函数和模拟运算表、带控件分类汇总
2
机房上机
盈亏平衡分析模型和内插法、安全边际分析
1.5
机房上机
成本决策模型（带阈值）
2
机房上机
经济订货量模型
2
机房上机
投资决策函数，投资决策模型、设备更新模型
1.5
机房上机
合计
11
实验形式：
指实验室集中、课外分散等实验形式。
三、实验项目
实验一建立ODBC数据源、使用Query进行复杂查询
[实验目的]
1.理解建立外源数据库的意义，建立ODBC数据源方法
2.理解Query的作用，掌握使用Query进行单表和多表复杂查询、构建计算字段的方法
[实验内容]
1．建立ODBC数据源
A.使用控制面板对示例数据库Northwind.mdb，定义一个名为“nw”的ODBC数据源。
B．使用Msqry32.exe完成上述操作。
C．在MsExcel中调用Msqry32.exe完成下列操作：
对示例数据库Salse.dbf，定义一个名为“ABCSalse”的ODBC数据源
2．使用Query进行单表及多表的复杂查询。
A．查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话等信息。
其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津。
B.“Northwind”数据库中存放了三年的订单数据，现有关人员想查询该公司的客户在1996年下半年订购的所有订单的订购日期、订单ID、客户ID和雇员ID等信息。
C．Northwind数据库中有三年的数据，现在需要查询最后一个月中每份订单的的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等信息。
并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单ID”的降序排列。
D．Northwind数据库中存放了其所有订单的信息，现要求查询其中的“10248”和“10254”号订单的订单ID、运货商的公司名称以及订单上所订购的产品的名称。
E．查询Northwind公司所有雇员的ID、姓氏、名字、职务以及其上级的姓氏、名字和职务。
F．（构造计算字段）Northwind数据库中有三年的数据，现在需要查询最后一个月中每份订单的订单ID、订单上所订购的产品的名称及其销售金额。
[实验步骤]
1．在Excel中运行Query程序。
2．按实验内容，根据上课所学知识和操作技术，逐题完成上机习题操作，完成实验。
3．总结操作要点，撰写实验报告。
[实验方法]
根据上课教师的演示范例，参照教材相应提示，逐题完成上机习题要求。
[实验条件]
一人一机，机房机器上装有多媒体教学演示软件（凌波系统）和完整的MsExcel软件模块。
[实验指导]
共性问题机房大屏幕演示解答，个别问题教师及时耐心解答，必要时进行手把手示范。
[问题思考]
1．总结建立ODBC数据源的三种方法。
2．一个ODBC数据源包括哪三大要素？
3．比较直接调用Query和Excel中调用的方法差异。
4．说明表的多条件查询过程中建立并交筛选条件和条件窗口行列的对应关系。
5．说明如何在查询中添加删除表、筛选条件或列。
6．说明多表查询时建立表间连线的意义。
当需要时，如何观察和建立起人工链接。
实验二Query和数据透视表的数据分类汇总
[实验目的]
1.学习Query进行数据分类汇总
2.学习数据透视表进行数据分类汇总，掌握分类字段的组合编辑技术：
数据透视表生成时间序列，统计频率分布等。
[实验内容]
1．学习Query进行数据分类汇总：
A）利用Sales.dbf的数据源，在ABC公司销售数据库中，汇总1995年不同省份不同类别产品的净销售额总计值。
B）ABC公司销售数据.dbf中保存着公司从1994年到1997年三年的销售数据，利用Query软件的统计值功能汇总该公司的销售数据，并按照图3-7的形式，汇总出1995年不同省份、不同类别商品的净销售额。
将数据汇总后返回Excel。
。
2．学习数据透视表进行数据分类汇总，掌握分类字段的组合编辑技术：
A．直接利用Excel数据透视表功能从数据库中查询数据，并根据查询结果汇总，制作出如图3-7所示的分类汇总表。
B．利用数据透视表生成时间序列：
对NorthwindTrader公司的销售数据按月汇总种各产品销售额。
C．利用数据透视表，对Northwind公司的销售数据按照不同产品、不同时间段，统计不同规模销售数量的发生次数，并计算其频率分布。
D．NorthwindTrader公司根据客户总销售额将客户划分为不同级别，20000元以上为黄金客户，10000元～20000元为重要客户，10000以下为普通客户。
按照客户的不同级别汇总各级客户的总销售额、销售额占总销售额的百分比，以及该级客户销售次数占总销售次数的比重，如图3-69所示，并绘制数据透视图
[实验步骤]
1．Excel中运行相应模块。
2．实验内容，根据上课所学知识和操作技术，逐题完成上机习题操作，完成实验。
3．总结操作要点，撰写实验报告。
[实验方法]
根据上课教师的演示范例，参照教材相应提示，逐题完成上机习题要求。
[实验条件]
一人一机，机房机器上装有多媒体教学演示软件（凌波系统）和完整的MsExcel软件模块。
[实验指导]
共性问题机房大屏幕演示解答，个别问题教师及时耐心解答，必要时进行手把手示范。
[问题思考]
1。
总结利用数据透视表生成时间序列的一般规律。
页字段能否直接进行组合操作？
如何解决？
2．总结利用数据透视表进行频率分布统计的方法和操作步骤。
3．数据透视表如何对行统计字段再次分组？
如何进行两次以上不同量的统计分析？
实验三D函数和模拟运算表、带控件分类汇总
[实验目的]
1．学习D函数和模拟运算表的使用
3.学习带控件的动态统计图的制作。
[实验内容]
1．使用D函数和模拟运算表进行数据分类汇总：
A）利用ABC公司的销售数据，应用DSUM（）函数，生成一个与图3-7相同的数据分类汇总表，列出ABC公司不同省份不同类别净销售额总计值。
并在此基础上利用DSUM（）函数和模拟运算表计算ABC公司不同省份不同类别净销售额总计值。
2．学习带控件的动态统计图的制作技术：
A）在上题的基础上，利用DSUM（）函数和模拟运算表，计算ABC公司不同省份、不同类别净销售额总计值，使用窗体中的组合框对省份进行控制，当选择组合框中的不同省份时，模拟运算表计算出该省份净销售额总计值，并以柱形图显示该结果。
B）在上题的基础上，使用组合框控制汇总的年份，使用列表框控制汇总字段为“净销售额”或“毛销售额”，并以柱形图形式显示结果，如图3-92所示。
售次数的比重，如图3-69所示，并绘制数据透视图
[实验步骤]
1．Excel中运行相应模块进行D函数和模拟运算表操作。
2．制作统计图，使用窗体工具栏，制作控制按钮。
进行所需设置等操作。
3．根据上课所学知识和操作技术，逐题完成上机习题操作，完成实验。
4．总结操作要点，撰写实验报告。
[实验方法]
根据上课教师的演示范例，参照教材相应提示，逐题完成上机习题要求。
[实验条件]
一人一机，机房机器上装有多媒体教学演示软件（凌波系统）和完整的MsExcel软件模块。
[实验指导]
共性问题机房大屏幕演示解答，个别问题教师及时耐心解答，必要时进行手把手示范。
[问题思考]
1．总结D函数参数的含义和函数使用方法。
理解模拟运算表的含义总结其使用方法
2．总结动态统计图中控件设置和使用的方法和操作步骤。
3．总结日期控件建立的技巧，INDEX（）函数的作用
4．总结使用D函数和模拟运算表制作动态统计图的一般步骤的方法。
实验四盈亏平衡分析模型和安全边际分析
[实验目的]
1．学习盈亏平衡分析模型的相关概念、计算公式和模型制作
2．学习盈亏平衡分析模型的动态统计图制作。
3．学习安全边际分析模型
4．学习内插值法的使用和如何建立统计图的动态参考线
[实验内容]
1．盈亏平衡分析模型：
某公司制造一种高质量运动鞋，公司管理层邀请你帮助公司整理用于管理决策的信息，公司最高生产能力为1500。
一项销售调查显示明年的平均每双销售价格定为90元；公司的成本数据为：
固定成本为37800元，每双可变成本为36元。
若当前的销量为900，要求：
A、计算单位边际贡献及边际贡献率；
B、计算销售收益、总成本及利润；
C、盈亏平衡（保本点）销量及盈亏平衡销售收益；
D、假若公司预算利润为24000元，计算为达到利润目标所需要的销量及销售收益；
E、根据公司的销售收益、总成本、利润等数据，绘制本-量-利图形；通过图形动态反映出销量从100按增量10变化到1500时利润的情况及“盈利”、“亏损”、“保本”的决策信息。
F、假定销售单价从80元按增量变化到100元时，计算出盈亏平衡销量和盈亏平衡销售收益的相应变化值？
并且以图形方式动态反映。
2．安全边际分析：
在上题的基础上，公司的财务主管已使用上题的分析模型计算得到了销量为900时的单位边际贡献等许多决策用的数据。
他想知道当目标利润为24000时，对应的销量、安全边际、安全边际率为多少？
并且他要绘制销量-利润图形，通过图形反映出当目标利润从10000按增量1000变化到40000时安全边际的数据变化情况，以便于他作出决策。
[实验步骤]
1．运行Excel制作相应模型。
2．制作动态统计图。
3．根据上课所学知识和操作技术，逐题完成上机习题操作，完成实验。
4．总结操作要点，撰写实验报告。
[实验方法]
根据上课教师的演示范例，参照教材相应提示，逐题完成上机习题要求。
[实验条件]
一人一机，机房机器上装有多媒体教学演示软件（凌波系统）和完整的MsExcel软件模块。
[实验指导]
共性问题机房大屏幕演示解答，个别问题教师及时耐心解答，必要时进行手把手示范。
[问题思考]
1．总结盈亏平衡分析模型的概念和相应计算公式
2．本实验如何使用插值法？
3．总结动态垂直参考线建立的一般步骤和主要注意事项。
实验五成本决策模型（带阈值）
[实验目的]
1．学习成本决策模型的相关概念、计算公式和模型制作
2．学习带阈值的成本决策模型制作要点。
3．学习Match（）函数在动态模型中的使用方法
[实验内容]
1．成本决策模型：
公司制造产品时需要某种零件，此零件如果自制，单位变动成本为4.8元，固定成本共计30000元。
如果向外公司采购，单件买价为7元，但可以减少固定成本20000元。
目前公司此零件的需求量为10000件，公司将作出“自制”还是“外购”的方案选择。
你作为管理人员向公司提出你所采用方案的建议，使用分析模型和图形来论证你的观点。
2．带阈值的成本决策模型：
在上例分析模型中，你已经计算出当公司的零件的需求量超过9091时，“自制”方案是一个好的选择。
富勒公司得知在其他条件不变的情况下若采购批量达到18000件时外购单件买价可以降低到4.1元。
目前公司零件的需求量为25000，公司应选择方案是什么？
使用分析模型和图形来论证你的方案选择。
。
[实验步骤]
1．运行Excel制作相应模型。
2．根据上课所学知识和操作技术，逐题完成上机习题操作，完成实验。
3．总结操作要点，撰写实验报告。
[实验方法]
根据上课教师的演示范例，参照教材相应提示，逐题完成上机习题要求。
[实验条件]
一人一机，机房机器上装有多媒体教学演示软件（凌波系统）和完整的MsExcel软件模块。
[实验指导]
共性问题机房大屏幕演示解答，个别问题教师及时耐心解答，必要时进行手把手示范。
[问题思考]
1．思考相对盈亏平衡点的意义
2．带阈值的成本决策模型构建时有哪些注意点和主要技术？
3．总结Match（）函数使用方法和参数值的含义。
实验六经济订货量模型
[实验目的]
1．学习经济订货量模型的相关概念、计算公式和模型制作方法
2．学习动态统计图中曲线族的制作。
[实验内容]
1．经济订货量模型：
某公司需采购某零件，全年需求量为15000件，每次订货成本为500元，单件零件的年储存成本为30元，当订货量为900。
要求：
A、计算年订货成本、年储存成本、年总成本；
B、计算经济订货量及经济订货量时的年总成本；
C、绘制反映该零件的年订货成本、年储存成本、年总成本随订货量变化的图形；当该零件年需量从10000按增量1000变化到20000时，经济订货量及经济订货量时的年总成本的值；
D、在图形中反映出当订货量从400按增量50变化到1000时年订货成本、年储存成本、年总成本的值。
2．模型中的曲线族制作：
采用上例数据，绘制一个不同年需求量对应的一簇年总成本随订货量变化的图形。
其中的8条灰色背景曲线分别与年需求量11000，12000，13000，14000，15000，16000，17000，18000等数值对应，制作一个受年需求量控件控制的反映年需求量的红色曲线，使得在年需求量控件的操纵下红色曲线会在8条灰色曲线族中由一个位置移动到另一个位置，在这个过程中红色曲线的最低点应会沿绿色轨迹曲线移动，而经过该最低点的垂直参考线则应会相应地左右移动。
3．带阈值经济订货量模型：
采用上例数据，假定供货单位提供给公司的折扣优惠政策条件为：
每次订货量大于等于1500件，则每件的采购单价在原价4.5元/每件的基础上可以享受5%的优惠折扣。
要求你作选择，采用经济订货量（未采用折扣）还是采用折扣阈限值作订货量？
[实验步骤]
1．运行Excel制作相应模型。
2．根据上课所学知识和操作技术，逐题完成上机习题操作，完成实验。
3．总结操作要点，撰写实验报告。
[实验方法]
根据上课教师的演示范例，参照教材相应提示，逐题完成上机习题要求。
[实验条件]
一人一机，机房机器上装有多媒体教学演示软件（凌波系统）和完整的MsExcel软件模块。
[实验指导]
共性问题机房大屏幕演示解答，个别问题教师及时耐心解答，必要时进行手把手示范。
[问题思考]
1．总结经济订货量模型的相应概念和主要计算公式
2．带阈值的经济订货量模型模型构建时有哪些注意点和主要技术？
3．总结曲线族制作的基本方法和技术要点。
实验七投资决策模型
[实验目的]
1．学习与投资决策相关的函数使用方法
2．学习基于净现值函数的投资决策模型的制作。
3．设备更新改造模型制作
[实验内容]
1．常用财务函数：
A、假如要购买一家鞋店，投资成本为40000，并且希望前五年的营业收入如下：
8000，9200，10000，12000和14500，每年的贴现率为8%。
计算鞋店投资的净现值
B、假设要购买一项保险投资，该保险可以在今后二十年内每月未回报500。
此项保险的购买成本为60000，假定投资回报率为8%，通过函数PV计算一下这笔保险投资是否值得?
C、假设需要为一年后的某个项目预筹资金，现在将1000以年利6%，按月计息（月利6%/12或0.5%）存入储蓄存款帐户中，并在以后十二个月内每个月末存入100，则一年后该帐户的存款额等于多少？
D、某客户欲购买一套价值50万元的公寓，首付20万元，余下金额向银行申请商业贷款30万元，贷款年限10年，年利率为5%，试制作按月还款表及查询月还款数据的模型。
E、一贷款投资项目的现金流数据如表8-1所示（见教材），求该项目的内部报酬率
F、金额为8000元的贷款，每月偿还200元，月利率为1%，该笔贷款多少年还清?
G、某公司准备投资1000万元建设一座桥梁，当年投资，当年建成，该桥今后15年内预计每年收益100万元。
请问：
若年贴现率为8%，今后15年内预计收益相当于现值多少?
若银行贷款年利率为5%，贷款900万元，按计划15年等额还清，每年还款额是多少？
考虑15年的回报期，则该项目的内部报酬率是多少?
2．基于净现值函数的投资决策模型：
某公司有甲乙丙3个投资项目可供选择，这些项目初始（第0年）投资额与第1年继续投入的资金额以及它们在第1年和第2年两年的现金收入分别如表8－3所示（见教材）。
3个项目第2年以后将不再获得收入（它们的有效期都是2年）。
试在公司成本率等于15％条件下确定3个投资项目的最优者。
此外，如果公司贴现率有可能在5％－40％范围变化时，研究此变化过程中最优投资项目的可能变化。
3．设备更新改造模型：
某企业拟进行扩建，面临着三种选择：
A、一次较大扩建，使未来10年产量增加一倍；
B、先进行较小扩建，产量增加40％，5年后第二次扩建，使产量达到现在的一倍；
C、进行小扩建后不再扩建。
具体数据见教材表8－4。
3种扩建方案有效期为10年，10年后项目扩建投资均有残值，公司使用贴现率为9％，残值率为10％，试确定哪一种方案最优。
另外绘制图形说明当贴现率在2％－10％，残值率在5％－15％之间变化时最优方案选择的变化
[实验步骤]
1．运行Excel财务函数在工作表中制作相应模型。
2．根据上课所学知识和操作技术，逐题完成上机习题操作，完成实验。
3．总结操作要点，撰写实验报告。
[实验方法]
根据上课教师的演示范例，参照教材相应提示，逐题完成上机习题要求。
[实验条件]
一人一机，机房机器上装有多媒体教学演示软件（凌波系统）和完整的MsExcel软件模块。
[实验指导]
共性问题机房大屏幕演示解答，个别问题教师及时耐心解答，必要时进行手把手示范。
[问题思考]
1．总结基于净现值的投资决策模型建模的一般步骤
2．如何应用内插法求不同项目净现值相等的曲线交点？
3．如何应用函数法求不同项目净现值相等的曲线交点？
试推导证明该方法的公式正确性。
四、实验教学组织（以下内容供参考）
（一）对教师的要求
为达到实验教学效果，保证实验教学质量，实验指导教师应做到以下几点：
1．实验前充分准备
实验前指导教师应熟悉实验内容与重点、难点，熟悉实验教学软件的操作与应用。
学生实际操作前，对实验内容、方法、步骤及要求要给学生做简要说明，对应注意或可能出现的问题进行必要的讲解和提醒。
协助实验室做好实验资料、用品等教学条件的准备。
2．实验中加强指导
充分发挥学生在实验中的积极性和主动性，同进要求指导教师必须加强指导、检查和监督，指导学生按规定的方法、步骤进行实验操作，对实验中出现的疑难问题，指导教师要耐心及时解答，必要时还应进行示范。
3．实验后认真总结
实验后教师应对学生的实验报告进行审阅批改，以百分制给出成绩，并于下一次实验前进行点评。
学期结束时，对整个实验教学活动进行小结，并对实验课建设与改提出意见与建议。
（二）对学生的要求
1．课前认真预习准备
实验前学生应根据实验内容与要求，对相关知识进行复习和回顾，认真研读实验手册内容，熟悉实验内容，了解实验步骤，以便在实验时能够较快地进入实验状态。
2．积极参与实验过程
学生必须按教学计划规定要求完成实验教学内容，实验中要亲自动手，勇于实践，独立思考，充分发挥主观能动性，实验中遇到问题及时向指导教师请教，或与同学之间进行讨论，但对实验任务必须独立完成。
实验操作应做到规范、准确、清楚、及时。
3．编写实验报告
实验结束后，要对每项实验分别编写实验报告，总结实验体会与收获。
实验报告写作要求文字叙述精炼，通顺、层次分明，对相关专业问题进行有益探讨。
实验报告格式附后。
（三）实验考核与成绩评定
课程实验环节考核主要依据学生的实验报告与平时实验表现进行，按每项实验成绩计算平均分数确定实验考核成绩，在课程总绩中实验环节占20%。
（不同课程灵活确定）
（四）实验成果保管
学生的《上机实验报告》批阅后，交系部归入教学档案。
升级会员 