《Excel在会计中的应用》实验指导书(学生版).doc

Excel在会计中的应用实验指导书(学生版) 1实验一Excel基础知识 一、实验目的1掌握Excel的基本操作。 熟练掌握各种启动退出Excel的有关操作；熟悉Excel的窗口及初始屏幕各部分的功能；掌握Excel联机帮助功能的使用方法。 2掌握工作表的建立方法。 熟练掌握工作表的建立方法和操作技巧，如建立一个新的工作薄向工作表中输入各种类型的数据保存工作薄工作薄密码的设置。 3掌握工作表的方法。 熟练掌握工作表的各种操作，包括移动复制删除查找和替换单元格中的数据以及各种引用的操作；熟练掌握工作表的管理方法，包括打开查看多个工作薄，插入删除重命名移动复制隐藏和显示隐藏工作表；熟练掌握单元格的行高和列宽的调整方法。 4学会用图表的形式表示数据间的复杂关系，直观地分析统计数据。 二、实验内容1启动退出Excel；熟悉Excel的窗口中菜单栏工具栏中各项命令按纽的含义；Excel联机帮助功能的使用方法。 请完成以下操作删除“常用”工具栏中“超链接”工具按纽在“格式”工具栏增加“竖排文字”工具按纽把“格式”工具栏与“常用”工具栏恢复原状新建一个工具栏，命名为“我的工具栏”，任意添加按钮删除自定义工具栏“我的工具栏”。 2在磁盘上建立一个文件名为“成绩表”的工作薄，在工作表sheet1中创建一个如表1-1所示的工作表。 表1-1xx级考试成绩表姓名性别高数线代英语物理语文会计学财务管理总成绩平均分张三男92807372809086梁齐男86767576828367王文女75617671868481吕梁男68806980797986余青女59738069657672岳韩男71697167746890戴卫女82816585816274杜平男73787064836592 （1）练习添加记录，并用公式和函数求出每位同学的总成绩和平均分。 （2）将工作表重新命名为“学生成绩表”。 （3）将“xx级考试成绩表”这一标题设置格式如下标题为黑体14号字，居中显示，颜色为红色。 表格中的文字型数据居中显示。 （4）将“学生成绩表”调整到适当的列宽和行高。 （5）将“学生成绩表”复制到同一工作簿的另一工作表中，命名为“学生成绩表2”。 （6）显示“学生成绩表”平均分大于等于60切小于70的记录，然后恢复原状。 2 （7）在“学生成绩表”中选中一个单元格区域，然后在其上使用自动套用格式。 （8）将“学生成绩表2”按总分从高到低排序。 （9）在“学生成绩表2”的最后添加一行，利用公式算出各科成绩和全班同学总成绩的平均分。 （10）将“学生成绩表2”的标题设为楷体，22号字，水平居中显示，并且将标题单元格所在的行高设为24。 （11）在“学生成绩表2”中只显示姓王的成绩记录。 （12）为“成绩表”工作簿设置文件打开密码123456。 （13）练习工作表与工作簿的保护 （14）练习窗口的拆分与冻结 （15）利用“学生成绩表”中的数据建立图表，并对该图表进行操作。 （16）为“学生成绩表”设置页边距页眉和页脚，并练习设置打印区域设置打印标题及其他打印选项，最后进行打印预览。 3计算表1-3中每人的数学总分、各科的平均分(保留两位小数)，并计算出所有同学数学（3科）总平均分，并置于F18单元格中。 将成绩表加粗外边框和细内实线，表标题在表上部居中显示，用18号仿宋体加粗字。 表中数据按班级顺序排列数据，总分高者在本班的前面，表中文字用14号宋体字。 各行高度随字形而定，各列宽度取自动选择方式，以放下本列数据为准。 王佩奇线性代数成绩所在单元格用6.25%灰色图案填充，并在本页中间位置处打印全表。 表的左边距为2.5厘米，右边距为2厘米，上下边距为3厘米，页眉中写“山东工商学院会计学院”，用仿宋体加粗12号，居中。 姓名，用仿宋体12号字，倾斜，放在页眉右侧。 页眉距上边距2厘米，页脚距下边距2厘米。 页脚居中放置作业完成日期。 表1-3A BC DE F1自动化专业学生成绩单2班级姓名高等数学概率论线性代数数学总分34班刘凯64848943班刘明亮64827251班吴国东68768961班钱志刚73827272班王佩奇73758182班刘怀正75647694班王世华848372104班任思化847689113班刘晓友847577122班黄卫东919278134班陈林918373141班任晓兰917784153班李小梅917684161班杨雪林958991174班张俊芒95756418全专业平均分34练习电子表格的制作（见练习1-7） 三、实验步骤略 四、需要注意的问题1理解绝对引用和相对引用的含义，并掌握他们分别在何种情况下使用。 2自定义序列的填充问题。 3数组的输入按ctrl+shift+enter组合键。 实验二Excel在财会中应用基础 一、实验目的熟练掌握Excel常用函数及财务函数的功能和操作方法。 二、实验内容1SUM函数表2-1如表2-1所示，在往来单位余额对比表中，根据具体问题来求和。 A、对B列xx年金额求和B、对“单位-1”“单位-2”“单位-5”和“单位-7”等4个单位的xx年数据求和2SUMIF函数如表2-2所示，在商品销售明细表中，根据要求求和。 表2-2A B1商品销售名细表2商品名称销售金额3A11,200.004000A202,500.005B11,400.00A BC1往来单位余额对比表2往来单位xx年xx年3单位-120000120004单位-230000250005单位-325000140006单位-496000360007单位-578000850008单位-61000001200009单位-7960008590010合计46B110,000.007000A202,000.008000A34E1,000.009A13,000.0010000A1100.00A、根据B列销售金额求和，要求对销售金额大于2000的数值进行求和。 B、根据商品名称求和，要求对商品名称为A1的商品相对应的销售金额进行求和。 C、求商品名称包含“A”的销售金额之和D、商品名称第四五个字符为“A2”且字符总长度为6个字符的的销售金额之和3了解COUNT函数和COUNTA函数的区别。 并根据表2- 4、2-5中A10:A13区域中的公式在B10B13区域中写出相应的结果。 表2-3A BC DE F1COUNT和COUNTA的区别2内容类别COUNT结论COUNTA结论312数值型数字1统计1统计4TRUE逻辑值0不统计1统计5FALSE逻辑值0不统计1统计6小说文本0不统计1统计715数值型数字1统计1统计8空白单元格0不统计0不统计9含有字符0不统计1统计1014文本型数值1统计1统计11#NAME?错误符号0不统计1统计12合计384COUNTIF函数如表2-6所示的员工工资表，根据要求计数。 表2-6部门姓名基本工资提成工资实发工资财务部张景2-45财务部杨会来20005002500销售部李琴3000xx200人事部原林1xx001600销售部吴凤莲23006002900财务部张飞21001002200A、统计实发工资大于2500的人数B、统计财务部的人数5平均函数AVERAGE函数某公司的工资表如表2-7所示，要求A、请求出所有员工的平均总工资；B、请求出职务是“财务”的员工的平均总工资。 表2-7编号姓名性别职务基本工资效绩工资总工资xx李碧华女行政1xx001600xx林淑芬女行政1200xx400xx王喜育女财x吕利萍女财务24008003200xx姚启明男业务10002xxxxDAVERAGE函数资料如上题（表2-7），请求出职务是“财务”的员工的平均工资7某人每月存1000元，连续存了12个月，银行的年利率是12%，请问一年以后该帐户的余额是多少？8假设期初余额为5500元，每月支付500（每月月末支付），如果获得月息为0.75%，三年时间用户可以积累多少钱？9贴现率为14.720269%，8年后要获得3000元，请问现在要投资多少？10如果某人的帐户上最终有1000000元的存款，现在已经存入12个月，银行的月息是1%，请问最初的存款额是多少？11年金函数PMT、PPMT、IPMT函数某人贷款1万元，年利率6%，计划分10个月还清，为了延长还款时间，每个月的还款本金和利息准备在月末还，请计算每个月应还多少钱？请计算第一个月应还多少本金？请计算第一个月应还多少利息？12净现值函数NPV函数现假设要购买一个商铺，投资成本为400000元，并且购买后第一年没有收益，以后5年的收益分别为85000元、97000元、120000元、125000元和140000元。 现假设当年的利率为5%，请判断该项投资值不值得。 13利率函数RATE函数某公司进行一项短期投资，期初投入现金1000元，期末回报3000元，这个项目一共用了8个月，请计算此项目的年收益率是多少？14内部收益率函数IRR函数假设某用户开一家公司投资额为1500000元，预计在今后5年内的收益分别为400000元、6450000元、580000元、660000元与710000元，现计算投资1年后3年后和5年后的内部收益率是多少？15修正内部收益率函数MIRR函数某公司5年前以年利12%贷款2000000元购买了一批卡车，这批卡车在5年内带来的收入分别为300000元、410000元、590000元、680000元和760000元。 其间又将这些收入用于重新投资，每年的收益率为15%，请计算购买卡车3年以及5年以后的修正内部收益率是多少？16投资回收期函数NPER函数一项投资的期初投资是30000元，该项投资的月回报率是25%，现希望该项投资的最后收益达到400000元，请问该投资的期限是多少个月？17直线法折旧函数SLN函数假设某用户花250000元买了一辆车，其使用年限为8年，残值为10000元，现用直线折旧法进行折旧，请求出每月的折旧额是多少？18单倍余额递减法DB函数假设某工厂在今年的8月，以2000000元买了一部生产用的机器，使用年限为6年，估计残值是300000元，用余额递减法计算该机器的折旧值。 19双倍余额递减法DDB函数某公司花2000000元购入一台机器，运输费及安装费共500000元，这台机器的使用年限为6年，估计残值为250000元，用双倍余额递减法计算该机器每年计提的折旧金额。 20可变余额递减法VDB函数某公司购入一台机器，资产原值为1500万元，预计残值为100万元，这台机器的使用年限为10年，请用VDB函数分别求出这台机器第一天的折旧值第一年的折旧值以及第6个月与18个月之间的折旧值是多少？21年数总和法SYD函数某公司花2000000元购入一台机器，运输费及安装费共500000元，这台机器的使用年限为6年，估计残值为250000元，用年数总和法计算该机器每年计提的折旧金额。 22某公司生产一种产品，其成本由固定成本和变动成本组成，现有一组产量数据和对应的总成本数据如表2-8所示，试用Intercept函数求出固定成本，Slope函数求出单位变动成本，并用Linest函数求出固定成本和单位变动成本来验证正确性。 表2-8总成本（万元）产量（万吨）1001100.5959.598.7567.456.5896.585.6705.769.223资料如上题所示，用FORECAST函数预测产量是120万吨时的总成本。 24对于TREND函数、LOGEST函数、GROWTH函数，请练习书上的例题。 25IF函数如表2-9所示，在销售提成计算表中，C列显示的数据是根据B列的销售额进行判断的结果，如果“销售额”大于30000，“提成额”按“销售额*0.015”计算，否则按“销售额*0.001”计算。 表2-97姓名销售额提成额张三25000250李四36000540王五30000300孙六45000675李七2100021026VLOOKUP函数某公司个人所得税计算表如表2-10所示，请求出每位员工应税所得对应的税率、速算扣除数、及交纳的税金。 表2-10姓名应税所得对应税率对应速算扣除数税金级数应税所得且不超过税率速算扣除数张雨37000.15125430105005%0刘平25000.151252502500200010%25李正5000.1252532000500015%125王华00.0500450002000020%3755200004000025%13756400006000030%33757600008000035%637588000010000040%10375910000045%1537527MINVERSE函数求矩阵?322214312的逆矩阵。 28MMULT函数求矩阵?322214312和?7942125863的乘积。 三、实验步骤略 四、需要注意的问题1应用NPV函数时，如果第一笔现金流发生在第一期的期初，则第一笔现金加到NPV的结果中，否则包含在Values参数中。 2如果函数返回的是一组数值，则输入完函数的参数后，同时按Ctrl+Shift+Enter组合键。 3使用MMULT函数时，注意Array1的列数必须与Array2的行数相同。 8实验三账务处理和会计报表专题 一、实验目的掌握建立账务处理模型的方法并学会运用该模型进行进行账务处理。 二、实验内容1建立账务处理模型 （1）建立账务处理工作薄 （2）建立科目编码表 （3）建立会计分录表 （4）建立科目汇总表 （5）建立科目余额表2编制资产负责表3编制利润表4编制现金流量表5假设东方股份公司1月份发生以下10笔业务，利用上面建立的模型请做出相应的会计分录，并编制资产负责表利润表现金流量表。 A公司1月1日，各科目的余额如表3-1所示表3-1东方股份公司1月1日科目余额表编码科目名称期初余额借方贷方1001库存现金4000.001002银行存款48000.001011存放同业15000.001111买入返售金融资产3000.001201应收代位追偿款62000.001301贴现资产12500.001403原材料50000.001407商品进销差价723000.001408委托加工物资55000.001431贵金属120000.001631油气资产850000.001632累计折耗120000.001701无形资产47000.001702累计摊销1703无形资产减值准备12000.002211应付职工薪酬7500.004001实收资本公积550000合计1989500.001989500.00 （1）1月3日从工商银行提取现金1000元。 （2）1月10日采购100台电风扇单价100元，已入库，用工商银行存款支付。 假设无增值税。 （3）1月13日向长安商场销售电风扇50台，售价为120元，款项未付。 假设无增值税。 （4）1月15日向某客户销售电风扇30台，售价为120元，客户用工商银行支票支付款项。 假9设无增值税。 （5）1月27日结转上两笔销售的产品销售成本，电风扇的进价为100元。 （6）1月28日为各部门报销办公费用3000元，全部用工商银行的支票支付。 （7）1月28日发放工资12000元，款项用工商银行支票支付。 （8）1月28日向某客户销售电风扇100台售价为200元，款项未付。 （9）1月28日结转产品销售成本，电风扇进价为100元。 （10）1月30日收到客户欠款10000元，全部存入工行。 三、实验步骤14略5在会计分录表中输入以下会计分录 （1）借库存现金 （1001）1000贷银行存款工行 （100201）1000 （2）借库存商品 （1405）10000贷银行存款工行 （100201）10000 （3）借应收账款 （1122）6000贷主营业务收入 （6001）6000 （4）借银行存款工行 （100201）3600贷主营业务收入 （6001）3600 （5）借主营业务成本 （6401）8000贷库存商品 （1405）8000 （6）借管理费用 （6602）3000贷银行存款工行 （100201）3000 （7）借应付职工薪酬 （2211）12000贷银行存款工行 （100201）12000 （8）借应收账款 （1122）20000贷主营业务收入 （6001）20000 （9）借主营业务成本 （6401）10000贷库存商品 （1405）10000 （10）借银行存款工行 （100201）10000贷应收账款 （1122）10000 四、需要注意的问题1.SUMIF()函数的应用。 2几个函数的相互嵌套时，注意输入正确的参数。 3数据有效性的设置方法。 实验四财务分析专题 一、实验目的使学生掌握从外部获取数据、建立动态链接的方法，掌握比率分析、杜邦分析模型的建立步骤10及方法。 二、实验内容1练习从外部网页获取数据。 2根据第三章建立的资产负债表和利润表，利用第4章所讲内容，设计模型，对财务报表进行分析（包括比率分析和杜邦分析）。 并在第三章建立的账务处理模型中更改会计分录中的部分数据，观察财务比率分析模型各个指标的变化。 3资料某企业的账务系统采用的是VFP语言编写的，其主要的一张余额表（表名YEB.DBF）是一自由表，其结构为KMBM，C，14；JD，C，2；QCJF、QCDF、BQJF、BQDF、QMJF、QMDF字段均为N，12，2。 科目编码结构为4-2-2-2-2-2型，一级编码采用财政部统一规定（请参照企业会计制度），明细科目企业自定。 该余额表中已有相应的数据（一级及其明细科目均有数据），请完成下列要求 （1）建立数据库查询，将余额表表中数据引入到Excel中来。 （2）利用该数据编制资产负债表和利润表。 （3）利用第4章所讲内容，设计模型，对财务报表进行分析（包括比率分析和杜邦分析）。 （4）在VFP中更改YEB的部分数据，观察要求3的各项变化。 三、实验步骤1操作参见教材75页。 2操作结果见第四章附件。 3 （1）打开Excel工作表，单击菜单栏“工具”/“导入外部数据”/“新建数据库查询”，在弹出的“选择数据源”对话框中，选择“Visual FoxproTables”，单击“确定”按钮，在弹出的“Confogure Connection”对话框中选中“Free TableDirectory”选项，并选中“YEB.DBF”文件所在的路径，单击“OK”按钮，在弹出的查询向导中根据实际需要选择导入到EXCEL中的字段，直至最后完成数据导入的操作。 （2）、 （3）略 （4）在VFP中更改YEB的部分数据后，把鼠标放到Excel中余额表的任一有数据单元格，选择“数据”/“刷新数据”便完成了对Excel中数据的更新，资产负债表利润表和财务报表分析模型中的数据会随着相应变化。 四、需要注意的问题1如何在Excel中导入外部文件，如.TXT文件等。 2如何从数据源获取数据，建立数据动态连接，从而完成财务报表数据的采集。 3掌握财务报表分析中比率分析方法的使用实验五成本预测、决策和成本计算专题 一、实验目的掌握成本预测决策和计算模型的建立步骤及方法 二、实验内容111假设有一家生产家电设备的大型企业，其中该公司的甲设备尤为畅销，1998年销售量为100000件，单位售价为750元/件，单位变动成本为300元/件，固定成本为25000000元。 现假设单位变动成本在270元/件至330元/件间（增减幅度为10%）变动，其他因素不变。 要求请用滚动条设计单位变动成本变动时相对应的盈亏分界点，列出滚动条的设计步骤，并指出哪几个单元格需要设置公式，并写出相应的公式。 2某企业生产需要耗用甲、乙、丙、丁四种材料，相关的数据如表5-1所示表5-1存货名称甲乙丙丁年需要量R18000200003000025000一次订货成本S25252525单位储存成本CK2343每日到货量100xx00250每日耗用量20304025要求 （1）建立模型，计算甲、乙、丙、丁四种材料的最优订货批量。 （2）确定最优订货次数（R/Q）和最佳订货周期(1年*Q/R)。 3某企业有甲乙丙三个辅助生产车间，他们相互提供劳务并为A、B、C三个基本生产车间、分厂和公司管理部门提供劳务，相关劳务量和成本数据如下表5-2表5-2供应部门受益部门受益量甲乙丙甲xx00乙5000300丙20000800A20000400B80000600C20000400分厂5000010001000管理部门50001600600劳务量合计20000050002000本月发生的辅助生产费用8000050004000要求建立Excel模型分配辅助生产费用。 三、实验步骤略 四、需要注意的问题1设置滚动条时，应从“视图”/“工具栏”中选择“窗体”，而不是选择“控件工具箱”。 2使用MMULT函数分配辅助生产费用时，注意两个矩阵的顺序。 12实验六投资决策专题 一、实验目的掌握投资决策模型建立的步骤及方法。 二、实验内容1已知贴现率为10%，有三种投资机会，有关数据如表6-1所示。 建立Excel模型，应用NPV、IRR函数分别计算三种方案的净现值、内含报酬率和现值指数。 表6-1期间A方案现金净流量B方案现金净流量C方案现金净流量0 (20000) (10000) (12000)1120001xx60021400060004600360004600合计60003xx8002某公司原有一台机床在四年前购入，原设备成本为200000元，估计尚可使用6年。 假定期满无残值，已提折旧80000元（采用的是直线法），账面折余价值为120000元。 使用该设备每年可获得销售收入298000元，每年支付的直接材料和直接人工为226000元。 该公司为提高产品的产量和质量，准备另行购买一台全自动的机床，约需要价款300000元，估计可使用6年，期满有残值15000元。 购入新机床时，旧机床可作价70000元。 使用新设备可增加销售收入50000元，同时每年节约直接材料和直接人工20000元。 建立固定资产更新决策模型，在该公司资金成本为12%新设备采用不同的折旧方法（直线法年数总和法）的条件下，根据净现值法和内含报酬率法对该公司的设备更新方案作出决策。 （假设所得税率为33%）。 三、实验步骤 四、需要注意的问题1现金流量的确定根据现金流量的定义计算营业现金流量=营业收入-付现成本-所得税根据年末营业结果来计算。 企业每年现金增减两个主要方面一是当年增减的净利；二是机器折旧，以现金的形式从销售收入中扣回，留在企业里。 营业现金流量=税后净利+折旧根据所得税对收入和折旧的影响计算。 营业现金流量=税后收入-税后成本+税负减少=营业收入*（1-所得税率）-付现成本*（1-所得税率）+折旧*所得税率2对于独立方案的决策分析，可以运用NPVPIIRR等任何一个合理的决策指标进行分析；13对于互斥项目的决策分析可以使用下列方法排列顺序法按照NPV、IRR、PI分别排序，以NPV为准增量收益分析法差量分析法总费用现值法实验七Excel高级应用 一、实验目的掌握数据透视表动态数据分析线性规划宏的使用和建立应用系统的方法。 二、实验内容1在“产品销售统计表”工作表中建立一个关于销售员和产品分类的销售额统计数据透视表，并对该数据透视表进行相应的操作 （1）改变字段的位置； （2）行列字段互换； （3）添加字段； （4）改变汇总方式； （5）数据的格式化； （6）显示隐藏汇总行； （7）删除数据透视表； （8）在该数据透视表中增加一个“增值税”计算字段（增值税等于销售金额*0.17）；2利用模拟运算表计算在利率为5%、5.5%、6%15%，期限为10年，投资额分别为10万、10.5万20万元的资金未来值。 3某企业需要同时经过 一、二两个车间生产甲、乙两种产品，拟进行生产决策，以便获取最大数额的贡献毛益，有关限制资料如下表7-1表7-1项目一车间二车间耗用材料数量单位贡献毛益最大