EXCEL实训.doc

安徽机电职业技术学院Excel在财务中的应用实训报告 学院班 级姓名学号日 期Excel在财务中的应用实训报告目 录实训一 Excel在货币时间价值计算中的应用1一实训目的和要求1二、实训过程和实训步骤2三、实训过程中遇到哪些问题？你是如何解决的？还存在哪些问题？10四、本次实训有何感想、体会、意见和建议？10实训二 Excel在账务处理中应用11一实训目的和要求11二实训过程和实训步骤11三实训过程中遇到哪些问题？你是如何解决的？还存在哪些问题？15四本次实训有何感想、体会、意见和建议？15实训三 编制会计报表并进行财务分析16一实训目的和要求16二实训过程和实训步骤16三实训过程中遇到哪些问题？你是如何解决的？还存在哪些问题？19四本次实训有何感想、体会、意见和建议？20实训四 Excel在固定资产管理中的应用20一实训目的和要求20二实训过程和实训步骤20三实训过程中遇到哪些问题？你是如何解决的？还存在哪些问题？24四本次实训有何感想、体会、意见和建议？24实训五 Excel在流动资金管理中的应用25一实训目的和要求25二实训过程和实训步骤25三实训过程中遇到哪些问题？你是如何解决的？还存在哪些问题？27四本次实训有何感想、体会、意见和建议？27实训一 Excel在货币时间价值计算中的应用一 实训目的和要求 实训目的1. 熟练掌握Excel在终值计算中的应用。2. 熟练掌握Excel在现值计算中的应用。实训要求第一题：某人在银行存入100 000元，存款期限为5年，银行按5%的年利率单利计息，要求建立一个单利终值的计算分析模型，通过该模型完成以下任务。1）计算这笔存款在第5年末的单利终值2）分析本金、利息和单利终值对计息期限的敏感性3）绘制本金、利息和单利终值与计息期限之间的关系图第二题： 某人在银行存入100 000元，存款期限为5年，银行按5%的年利率复利计息。要求建立一个复利终值的计算分析模型，并完成以下任务。1） 计算这笔存款在5年末的复利终值。2） 分析本金、利息和复利终值对计息期限的敏感性。3） 绘制本金、利息和复利终值与计息期限之间的关系图。4） 分析复利终值对利率变化的影响。5） 分析复利终值对利率和计息期限变化的敏感性。6） 绘制不同利率水平下复利终值与计息期限之间的关系图。 第三题：已知一笔款项的现值为100 000元，年利率为5%，计息期限为6年，要求完成以下任务。1）设计一个可以选择按单利方法或复利方法计算这笔款项终值的模型。2）对比分析未来10年的单利终值和复利终值。3）绘制单利终值与复利终值的比较分析图。第四题：假定某银行5年期存款的年利率为5%，工人小王希望在第5年末从银行取出100 000元。要求建立一个单利现值计算模型，并使该模型具有以下功能。 1）计算为了在5年末获得100 000元现在应存入银行多少钱。 2）分析不同利率水平下单利现值与计息期限之间的关系。 3）绘制不同利率水平下单利现值与计息期限之间的关系图。 第五题：假定某银行存款的复利年利率为5%，某人希望在第5年末从银行取出80 000元。要求建立一个复利现值计算模型，并使该模型具备以下功能。 1）计算为在5年末获得80 000元现在应存入银行多少钱。 2）分析不同利率水平下复利现值与计息期限之间的关系。 3）绘制不同利率水平下复利现值与计息期限之间的关系图。 第六题：已知一笔款项在第8年末的终值为100 000元，年利率为5%。要求根据以上资料完成以下任务。 1）设计一个可以选择按单利方法或复利方法计算这笔款项现值的模型。 2）对比分析未来8年的单利现值和复利现值。1） 绘制单利现值与复利现值的比较分析图。 二、实训过程和实训步骤第一题：（1）计算存款在第5年末的单利终值。打开一个新的Excel工作簿，在工作表的单元格区域A1：B4中输入已知条件，同时在单元格区域D1：E2中设计计算结果输出区域的格式，如图：选取单元格E2，输入公式“=B2*(1+B3*B4)”，按下回车键，单元格E2中会得到公式计算结果，即单利终值为125000元。如图：(2)分析本金、利息和单利终值对计息期限的敏感性。在单元格区域中设计分析敏感性表格的格式，如图：选取单元格B8，输入公式“=$B$2”；选取单元格B9，输入公式“=$B$2*$B$3*B7”；选取单元格B10，输入公式“=B8+B9”。如图：选取单元格区域B8：F10，将鼠标指针对准填充柄，按住左键向右拖到F列，则在单元格区域C8：F10中自动填充了公式。结果如图：（3）绘制本金、利息和单利终值与计息期限之间的关系图选取单元格区域A8：F10，插入-图表-柱形图-二维柱形图选择“图表工具”菜单下“设计”选项卡的“图表布局”组中的布局7，为横、纵坐标轴添加标题。完成图的表如下：第二题：（1）计算该笔存款在第5年末的复利终值。打开新的工作薄，在表中A1：B4区域输入已知条件，在区域D1：E3中设计计算结果输出区域的格式。选取E2，输入公式“=B2*(1+B3)B4”后按下回车键，即可得到复利终值结果。完成的图表如下：（2）分析本金、利息和复利终值对计息期限的敏感性。在区域A6：F10中设计敏感性分析表格的格式。在单元格B8中输入公式“=B2”；在单元格B9中输入公式“=B8*$B$3”；在单元格B10中输入公式“=B8+B9”；在单元格C中输入公式“B10”。将单元格区域B9：B10向右填充到C9:C10，再将C8向右填充到D8；依次交叉填充，直到完成。完成的表格如下：（3）绘制本金、利息和复利与计息期限之间的关系图。选取单元格区域A8：F10，插入-图表-柱形图-二维柱形图-确定选择“图表工具”菜单下“设计”选项卡的“图表布局”组中的布局7，为横、纵坐标轴添加标题。右键图表中的复利终值系列，在菜单中执行“更改系列图表类型”命令，打开“更改图表类型”对话框；然后左侧选择“折线图”，右侧选择“带数据标记的折线图”确定。完成的图表如下：（4）分析复利终值对利率变化的敏感性。设计分析用表格的格式。在单元格A14中输入公式“=FV(B3,B4,-B2)”，按下回车键。选择区域A13：A14，数据-数据工具-假设分析-数据表；在“数据表”对话框中，在“输入引用行的单元格”框中输入“$B$3”。单击“确定”按钮，即可完成单变量模拟运算，得到复利终值对计算期限的敏感性分析表。完成的图表如下：（5）分析复利终值对利率和计息期限变化的敏感性。设计分析用表格的格式，然后输入公式“=FV(B3,B4,-B2)”。选取区域A20：F25，数据-数据工具-假设分析-数据表；在“数据表”对话框中，在“输入引用行的单元格”框中输入“$B$4”，在“ 输入引用列的单元格”中输入“$B$3”。单击“确定”按钮，即可完成单变量模拟运算，得到复利终值对利率计算期限的敏感性分析表。完成的图表如下：（6）绘制不同利率水平下复利终值与计息期限之间关系图选取表格区域B21：F25，插入-图表-折线图-二维折线图-确定。选择“图表工具”菜单下“设计”选项卡的“图表布局”组中的布局7，为横、纵坐标轴添加标题。完成的图表如下图：第三题：（1）设计可选择按单利方法或复利方法计算终值的模型。输入单利终值和复利终值增加组合框到快速访问工具栏利用组合框从中选择单利终值或复利终值输入公式“=IF($A$7=1,B2*(1+B3*B4),FV(B3,B4,-B2)”，设计出按单利方法或复利方法计算单利终值和复利终值。完成的图表，如下图：（2）对比分析未来10年的单利终值和复利终值完成的图表，如下图：（3）绘制单利终值与复利终值的比较分析图插入-选择-折线图为折线图增加横、纵坐标完成的图表，如下图：第四题：（1）计算单利现值打开新的工作薄，输入数据，设计计算表。在单元格输入公式“=B2/(1+B3*B4)”，即可得到单利现值。完成的图表如下：（2）分析不同利率水平下单利现值与计息期限之间的关系。设计分析表格格式输入公式“=B2/(1+B3*B4)”输入年利率具体数值，数据-数据工具-假设分析-数据表；在“数据表”对话框中，在“输入引用行的单元格”框中输入“$B$4”，在“ 输入引用列的单元格”中输入“$B$3”。完成的图表如下图：（3）绘制不同利率水平下单利现值与计息期限之间的关系。选取单元格区域，插入-选择-折线图为折线图增加横、纵坐标完成的图表如下图：第五题：（1）计算复利现值打开新的工作薄，输入数据，设计计算表。输入公式“=B2/(1+B3)B4”完成的图表如下：（2）分析不同利率水平下复利现值与计息期限之间的关系设计分析表格格式输入公式“=B2/(1+B3)B4”输入计息期限具体数值，数据-数据工具-假设分析-数据表；在“数表”对话框中，在“输入引用行的单元格”框中输入“$B$4”，在“ 输入引用列的单元格”中输入“$B$3”，确定。完成的图表如下：（3）绘制不同水平下复利现值与计息期限之间的关系图选取单元格区域，插入-选择-折线图为折线图增加横、纵坐标完成的图表如下：第六题：（1）设计可选择按单利方法或复利方法计算这笔款项现值的模型。输入单利现值和复利现值增加组合框到快速访问工具栏利用组合框从中选择单利现值或复利现值输入公式“=IF(D2=1,B2/1+B3*B4),B2/(1+B3)B4)”，设计出按单利方或方法计算单利终值和复利终值。完成的图表如下：（2）对比分析未来8年的单利现值和复利现值完成的图表如下：（3）绘制单利现值与复利现值的比较分析图。选取单元格区域，插入-选择-折线图为折线图增加横、纵坐标完成的图表如下：三、实训过程中遇到哪些问题？你是如何解决的？还存在哪些问题？问题：建立柱形图，并在该图中添加数据标志时显示的图表不符合要求，总是有遗漏。解决方法：通过看课本，看资料的实训过程和步骤，已经可以很熟练的独立完成相应的图表。问题：建立折线图，不会修改图例。解决方法：通过老师的讲解，在“选择数据”中找到了答案，现在已经不是问题了。问题：对于函数的应用不是很好，记不住公式和相应的参数应该选什么。解决方法：多看课本，多操作练习，所谓“熟能生巧”，做的多了就可以记住了。通过多次练习，对于实验一已经差不多了，可以熟练的掌握。四、本次实训有何感想、体会、意见和建议？感想和体会：通过实验一的整个过程，已经可以熟练的掌握关系Excel的基本操作，熟练的掌握Excel在货币时间价值计算中的应用，熟练的掌握Excel在终值计算和现值计算中的应用。通过一系列的学习和操作，简直受益匪浅。发现利用Excel真的可以很快的得到我们想要的数据，简单，方便，省时间。意见和建议：老师讲课方式很好，很灵活。还教了很多她在教学中领悟的小技巧，让我们学到了不少课外的宝贵知识。如果我们这门课将理论课的时间缩短一下，上机操作的时间多点，我们可以对Excel的学习将会得到更好的提高。实训二 Excel在账务处理中应用一 实训目的和要求掌握建立账务处理模型的方法并学会运用该模型进行账务处理。建立会计凭证表自动显示会计科目建立记账凭证平衡检验表建立分类账建立科目汇总表建立科目余额表生成现金日记账和银行存款日记账注意：所用经济业务为P94页1-10二 实训过程和实训步骤会计科目表（1）打开新的工作簿，输入相关的会计科目代码。完成的图表如下： 会计凭证表：（1）打开一张工作表命名为“会计凭证表”。（2）设置表头。（3）理将借方金额和贷方金额设置为数值格式。（4）设置表名。（5）在会计凭证表中选择G3单元格。（6）输入公式“=IF(F2=,VLOOKUP(F2,会计科目表!$A:$B,2,1)”自动显示会计科目，然后填充整个G列。（7）根据提供的资料，编制会计分录，填写会计科目表。完成的图表如下：记账凭证平衡检验表：（1）将一张工作表命名为“记账凭证平衡检验表”。（2）设置借方合计和贷方合计的公式，分别输入的公式是“=SUM(会计凭证表!I:I)”；“ =SUM(会计凭证表!J:J)”。（3）设置借贷差额的公式，输入公式“=C4-B4”。（4）设置显示是否平衡的公式，输入公式“=IF(C5=0,平衡,不平衡)”。完成的图表如下：分类账：（1）将一张工作表命名为“分类账”。（2）插入数据透视表（3）在打开的“创建数据透视表”对话框中选中“会计凭证表”的区域。（4）设置报表字段。（5）将“列标签”改为“科目命称”；“行标签”改为“凭证编码”。（6）对该表进行格式设置，如对齐方式、边框、小数位数等。完成的图表如下：科目汇总表：（1）将一张工作表命名为“分类账”。（2）插入数据透视表（3）在打开的“创建数据透视表”对话框中选中“会计凭证表”的区域。（4）设置报表字段。（5）对该表进行格式设置，如对齐方式、边框、小数位数等。完成的图表如下：科目余额表：（1）将一张工作表命名为“科目余额表”。（2）设置科目余额表的格式。（3）定义本期发生额的公式。本期借方公式“=SUMIF(会计凭证表!$F$2:$F$31,A4,会计凭证表!$I$2:$I$31)”；本期贷方发生额公式“=SUMIF(会计凭证表!$F$2:$F$31,A4,会计凭证表!$J$2:$J$31)”，向下填充整列。（4）定义期末余额公式：期末余额=期初余额+本期增加发生额-本期减少发生额完成的图表如下：日记账：（1）将一张工作表命名为“日记账”。（2）在“会计凭证表”中，点击“科目名称”在下拉菜单中选择“银行存款”，显示的就是“银行存款日记账”，将其引用到“日记账”中即可。完成的图表如下：三 实训过程中遇到哪些问题？你是如何解决的？还存在哪些问题？问题：在建立会计凭证时，输入科目代码不能够自动显示会计科目。解决方法：经过仔细检查，发现公式中没有采用绝对应用，修改后即可。问题：在记账平衡检验表中，输入正确的公式，发现借贷不平衡。解决方法：经过反复的检查，发现在录入会计凭证时，数据输入错误。问题：在建立分类账时，发现显示的都是计数。解决办法：经过询问老师，得到解决办法：在数据区域右击，选择“值字段设置”，进入，把“计数”改为“求和”即可。四 本次实训有何感想、体会、意见和建议？感想和体会：通过这次的学习，感觉收获很多。Excel功能值得很多，以前不会用这些函数，只能手工的找某些要找的东西，现在学会了这些函数，用起来已经很顺利了，感觉很好。这样可以省下很多的时间和计算量，只要函数输入和使用正确，计算就保证绝对的正确性。在工作中平时的一些查找，就会很便捷。发现在会计中的应用也很便利的。以后工作中定可以用得到，我会好好学习的。意见和建议：在这个信息技术时代，计算机的应用被广泛推广，希望能够多学习一些办公软件，更深的了解。实训三 编制会计报表并进行财务分析一 实训目的和要求编制资产负债表和利润表注意：数据来源P110 P129二 实训过程和实训步骤资产负债表：（1）将一张工作表命名为“资产负债表”，并定义该表。（2）设置单元格格式，定义单元格公式，计算数据资料，即可完成资产负债表的编制。最终的图表如下：利润表：（1）将一张工作表命名为“利润表”，按利润表的样式定义该表。（2）根据数据资料，定义各单元格公式，即可完成利润表的编制。最终完成的图表如下：财务比率分析表：（1）根据创建好的资产负债表和利润表。（2）按照财务比率的分类创建一个财务比率分析表的框架。（3）在相应单元格中输入公式，得到计算结果。最终完成的表格如下：财务比率综合评分表：（1）新建一张工作表，命名为“财务比率综合评分表”。（2）选定评价企业财务状况的财务比率，设计表格样式。（3）根据各项财务比率的重要程度，设定权重。（4）设定标准值，确定各项财务比率的标准值。（5）计算某一时期内各项财务财务比率的实际值，设置各财务比率的实际值公式。（6）计算关系比率，输入公式。（7）输入公式，利用关系比率计算财务比率的实际得分。（8）计算总得分，利用SUM函数即可得到结果。完成的图表如下：杜邦分析表：（1）新建一张工作表，命名为“杜邦分析表”，并制作出杜邦分析法的结构图。（2）输入各个指标所对应的公式。（3）计算出结果。完成的图表如下：三实训过程中遇到哪些问题？你是如何解决的？还存在哪些问题？问题：资产负债表最终结果不平。解决方法：在老师的帮助下，发现未分配利润计算错误，未分配利润应该等于本年利润加上未分配利润。问题：在采用杜邦分析法对财务进行综合分析时，不会在表格中画线。解决方法：通过询问老师，原来在“插入-形状”里面有很多作图的工具，现在已经可以熟练的运用了。四本次实训有何感想、体会、意见和建议？现在才发现，我的知识太贫乏，根本连入门都算不上，以前还以为报表只能用会计电算化来，现在想想都可笑。Excel的功能太强大了，学好它，对我这个学会计的来说，有百利而无一害，我会努力探索，希望能够进一步加深学习。实训四 Excel在固定资产管理中的应用一 实训目的和要求 1.某公司固定资产的相关情况如下表所示项目金额/元预计使用年限/年预计净残值率/%机器设备80 00050.3办公设备5 00050.5建筑物30 000 00050.02要求：建立固定资产卡片、利用折旧函数，采用年限平均法、双倍余额递减法、年数总和法计算各项固定资产的年折旧额和月折旧额2. 利用数据透视表对固定资产进行分类汇总（可以用教材中的P143页M公司固定资产清单）二 实训过程和实训步骤年限平均法：（1）建立平均年限法下固定资产的卡片样式。（2）根据资料，输入相关信息。（3）利用函数“=SLN($F$8,$D$9,$F$7)”，计算出-年折旧额。（4）利用IF函数，计算出-本月折旧额和累计折旧额。（5）输入公式，计算出账面净值。最终计算出的折旧表如下：双倍余额递减法：(1)建立双倍余额递减法下固定资产卡片的样式。(2)根据资料,输入相关信息。(3)利用函数“=DDB($F$8,$D$9,$F$7,A13,2)”,计算出-年折旧额。最后两年的折旧额采用平均年限法计算。(4) 利用IF函数，计算出-本月折旧额和累计折旧额。（5）输入公式计算出-月折旧额和每年末的账面净值。完成的图表如下：年数总和法：（1）建立年数总和法下固定资产卡片的样式。（2）根据资料，输入相关信息。（3）利用函数“=SYD($F$8,$D$9,$F$7,A12)”，计算出-年折旧额。（4）利用IF函数，计算出-本月折旧额和累计折旧额。（5）输入公式计算出-本月折旧额和年末账面净值。最终的结果如下：固定资产分类汇总：（1）将一张工作表命名为“科目汇总表”。（2）插入数据透视表（3）在打开的“创建数据透视表”对话框中选中资料中的“固定资产清单”区域。（4）设置报表字段。（5）对该表进行格式设置，如对齐方式、边框、小数位数等。完成的图表如下：三实训过程中遇到哪些问题？你是如何解决的？还存在哪些问题？问题：公式总是输入错误，并且不能够理解。解决方法：反复检查，让同学帮助检查，在通过老师的讲解，最终很好的解决了该问题，还让我学会了要细心。问题：在用双倍余额递减法计提折旧时，发现最后出现了负数。解决方法：经过对该折旧方法的再次理解，发现在用该种方法计提折旧时，最后两年是采用年限平均法计提折旧的，现在可以熟练的掌握该方法了。四本次实训有何感想、体会、意见和建议？通过这次的学习，我发现Excel在财务中的运用之强大，对于一个企业来说，也是很需要可以熟练运用该软件的人才。现在我已经能够很熟练的操作一些功能了，相信在以后的工作中应用到时会得心应手。实训五 Excel在流动资金管理中的应用一 实训目的和要求成本分析模型在Excel中的实现存货模型在Excel中的实现经济订货量基本模型在Excel中的实现ABC分类管理法在Excel中的实现二 实训过程和实训步骤成本分析模型：（1）新建工一张工作表，命名为“成本分析模型”。（2）根据资料，输入相关数据。（3）选择单元格，输入公式“=SUM(B5:B7)”，计算出总成本。完成的图表如下：存货模型：（1）将一张工作表，命名为“存货模型”，并根据资料，输入相关数据。（2）在单元格中，分别输入公式“=SQRT(2*B2*B3/B4)”，“ =B2/B5”。即可算出最佳现持有量和一年内现金的转换次数。完成的图表如下:经济订货量基本模型：（1）新建一张工作表，命名为“经济订货量模型”，将相关数据输入到工作表中。（2）在相关单元格中分别输入相关公式“=$B2/B6*$B3”、“ =B6/2*$B4”、“ =B7+B8”，即可计算出年进货成本、年储存成本、年总成本。（3）采用相同的计算方法，计算其他批量下的相关成本。（4）输入公式“=SQRT(2*B2*B3/B4)”计算经济订货量，输入公式“=SQRT(2*B2*B3*B4)”，计算最低相关总成本，输入公式“=B2/B11”计算订货次数。所有相关成本的计算结果如下：（5）选中单元格区域A6：H9，插入-折线图