Office-2010办公软件案例教程-第5篇-财务篇

第5篇财务篇——Office2010办公软件案例教程学习目标1．学会Excel中导入/导出外部数据的方法。2．学会利用公式自动计算数据。3．掌握Excel中函数的用法，如SUM、IF函数等。4．以IF函数为例，理解函数嵌套的意义和用法。5．学会Excel表格打印之前的页面设置。6．会利用公式完成财务报表相关项目的计算。7．会利用向导完成不同类型企业的一组财务报表的制作。8．理解财务函数的应用，如PMT。9．理解并学会单变量和双变量模拟运算表的构造。主要案例案例18制作员工工资表案例19公司投资决策分析案例20往来账务管理案例18制作员工工资表案例分析知识与技能解决方案拓展案例拓展训练案例小结案例分析员工工资管理是每个企业财务部门必然的工作，财务人员要清晰明了地制定员工的工资明细，统计员工的扣款项目、核算员工的工资收入等。制作工资表通常需要综合大量的数据，如基本工资、绩效工资、补贴、扣款项等。本案例通过制作“员工工资管理表”和“工资查询表”来介绍Excel软件在工资管理方面的应用，案例效果如图5.1和图5.2所示。知识与技能●工作簿的创建●工作表重命名●导入外部数据●函数DATEDIF、YEAR、ROUND、VLOOKUP、IF的使用●公式的使用●数据透视表●数据透视图解决方案Step01创建工作簿，重命名工作表（1）启动Excel2010，新建一份空白工作簿。（2）将创建的工作簿以“员工工资管理表”为名保存在“E:\公司文档\财务部”文件夹中。（3）将工作簿中的Sheet1工作表重命名为“工资基础信息”。Step02导入“员工信息”将前面人力资源部制作“员工人事档案表”时导出的“员工信息”数据导入到当前工作表中，作为员工“工资基础信息”的数据。（1）选中“工资基础信息”工作表。解决方案（2）单击【数据】→【获取外部数据】→【自文本】按钮，打开“导入文本文件”对话框，在“查找范围”中找到位于“E:\公司文档\人力资源部”文件夹中的“员工信息”文件。（3）单击【导入】按钮，弹出“文件导入向导”第1步，在“原始数据类型”处选择“固定宽度”作为最合适的文件类型；在“导入起始行”文本框中保持默认值“1”不变；在“文件原始格式”中选择“936：简体中文（GB2312）。（4）单击【下一步】按钮，设置字段宽度（列间隔），在图5.6中可见，部分列间缺少分隔线，如“部分”和“身份证号码”、“入职时间”和“学历”、“职称”和“性别”两列数据缺少列间隔，需要单击鼠标建立分列线。拖曳水平和垂直滚动条，将所有需要导入的数据检查一遍，使数据分别处于对应的分列线之间。解决方案（5）单击【下一步】按钮，设置每列的数据类型，如图5.8所示。默认时，一般为“常规”。这里，我们将“身份证号码”设置为“文本”、“入职时间”和“出生日期”设置为“日期”，其余列使用默认类型“常规”。（6）单击【完成】按钮，打开如图5.9所示的“导入数据”对话框。设置数据的放置位置为“现有工作表”的“$A$1”单元格。（7）单击【确定】按钮，返回“工资基础信息”工作表，将文本文件“员工信息”的数据导入到工作表中，如图5.84所示。解决方案Step03编辑“工资基础信息”表（1）选中“工资基础信息”工作表。（2）删除“身份证号码”、“学历”、“职称”、“性别”和“出生日期”列的数据。（3）分别在E1、F1、G1单元格中输入标题字段名称“基本工资”、“绩效工资”和“工龄工资”。（4）参照图5.13输入“基本工资数据”。（5）计算“绩效工资”。（6）计算“工龄工资”。解决方案Step04创建“加班费结算表”（1）复制“工资基础信息”工作表，将复制后的工作表重命名为“加班费结算表”。（2）删除“入职时间”、“绩效工资”和“工龄工资”列。（3）在E1、F1单元格中分别输入标题“加班时间”和“加班费”。（4）输入加班时间。按图5.16所示，输入员工加班时间。（5）计算加班费。解决方案Step05创建“考勤扣款结算表”（1）复制”工资基础信息”工作表，将复制后的工作表重命名为“考勤扣款结算表”。（2）删除“入职时间”、“绩效工资”和“工龄工资”列。（3）在E1:K1单元格中分别输入标题“迟到”、“迟到扣款”、“病假”、“病假扣款”、“事假”、“事假扣款”和“扣款合计”。（4）参照图5.18输入“迟到”、“病假”、“事假”的数据。（5）计算“迟到扣款”。（6）计算“病假扣款”。（7）计算“事假扣款”。（8）计算“扣款合计”。解决方案Step06创建“员工工资明细表”（1）插入一张新工作表，将新工作表重命名为“员工工资明细表”。（2）参见图5.20创建员工工资明细表的框架。（3）填充“编号”、“姓名”和“部门”数据。（4）导入“基本工资”、“绩效工资”、“工龄工资”和“加班费”数据。（5）同样的方式，分别导入“绩效工资”、“工龄工资”数据。（6）导入“加班费”数据。（7）计算“应发工资”。（8）计算“养老保险”数据。（9）计算“医疗保险”数据。解决方案（10）计算“失业保险”数据。（11）导入“考勤扣款”数据。（12）计算“应税工资”。（13）计算“个人所得税”。（14）计算“实发工资”。Step07格式化“员工工资明细表”（1）将工作表的标题设置为“合并后居中”格式，标题字体为“黑体”、“22磅”，标题行行高为“50”。（2）将列标题的字体设置为“加粗”、“居中”，行高设置为“30”。解决方案（3）将表中所有的数据项格式设置为“会计专用”格式，保留2位小数，货币符号为“无”。（4）为表格A2:O27数据区域添加内细外粗的蓝色边框。（5）为“应发工资”、“应税工资”和“实发工资”三列数据区域添加“蓝色，强调文字颜色1，淡色80%”的底纹。Step08制作“工资查询表”（1）插入一张新工作表，将新工作表重命名为“工资查询表”。（2）创建如图5.32所示的“工资查询表”。解决方案（3）显示员工“姓名”。①选中D2单元格。②插入VLOOKUP函数，设置如图5.33所示的参数。③按【Enter】键确认。（4）类似的方法，使用VLOOKUP函数构建查询其他数据项的公式。（5）取消网格线显示。单击【视图】选项卡，在“显示”组中，取消勾选“网格线”复选框选项。拓展案例1．制作各部门工资汇总表，如图5.34所示。2．制作各部门平均工资收入数据透视表和数据透视图，如图5.35所示。拓展训练设计和制作公司“差旅结算表”，效果如图5.36所示。案例小结本案例通过制作“员工工资管理表”，主要介绍了工作簿的创建、工作表重命名、外部数据的导入，使用函数Year、Datedif、Today、Round、Sum等构建了”工资基础信息”工作表、“加班费结算表”和“考勤扣款结算表”。在此基础上，使用公式和VLOOKUP函数，以及IF函数的嵌套创建出“员工工资明细表”。此外使用VLOOKUP函数制作出“工资查询表”，实现了对员工工资的轻松、高效管理。案例19公司投资决策分析案例分析知识与技能解决方案拓展案例拓展训练案例小结案例分析企业在项目投资过程中，通常需要贷款来加大资金的周转量。进行投资项目的贷款分析，可使项目的决策者们更直观地了解贷款和经营情况，以分析项目的可行性。利用长期贷款基本模型，财务部门在对投资项目的贷款分析时，可以根据不同的贷款金额、贷款年利率、贷款年限、每年还款期数中任意一个或几个因素的变化，来分析每期偿还金额的变化，从而为公司管理层做决策提供相应依据。本案例通过制作“投资决策分析”来介绍Excel财务函数及模拟运算表在财务预算和分析方面的应用。本案例中针对公司需要购进一批设备，需要资金120万元，现需向银行贷款部分资金，年利率假设为4.9%，采取每月等额还款的方式。现需要分析不同贷款数额（100万、90万、80万、70万、60万以及50万），不同还款期限（5年、8年、10年和15年）下对应的每月应还贷款金额。效果如图5.42所示。知识与技能●工作簿的创建●工作表重命名●公式的使用●函数PMT的使用●模拟运算表●单元格名称的使用●方案管理器应用●工作表格式设置解决方案Step01创建工作簿、重命名工作表（1）启动Excel2010，新建一个空白工作簿。（2）将创建的工作簿以“投资决策分析”为名保存在“E:\公司文档\财务部”文件夹中。（3）将“投资决策分析表”工作簿中的Sheet1工作表重命名为“贷款分析表”。Step02创建“投资贷款分析表”结构（1）如图5.43所示，输入贷款分析的基本数据。（2）计算“总还款期数”。解决方案Step03计算“每月偿还金额”（1）选中C7单元格。（2）单击编辑栏上的“插入函数”按钮，打开“插入函数”对话框。（3）在【插入函数】对话框中选择“PMT”函数，打开“函数参数”对话框。（4）在“函数参数”对话框中输入如图5.44所示的PMT函数参数。（5）单击【确定】按钮，计算出给定条件下的“每月偿还金额”，如图5.45所示。解决方案Step04计算不同“贷款金额”的“每月偿还金额”这里，设定贷款数额分别为100万、90万、80万、70万、60万以及50万，还款期限分别为5年，贷款利率为4.9%，可以使用单变量模拟运算表来分析适合公司的每月偿还金额。（1）创建贷款分析的单变量模拟运算数据模型。（2）计算“每月偿还金额”。解决方案Step05计算不同“贷款金额”和不同“总还款期数”的“每月偿还金额”这里，设定贷款数额分别为100万、90万、80万、70万、60万以及50万，还款期限分别为5年、8年、10年及15年，即设计双变量决策模型。（1）创建贷款分析的双变量模拟运算数据模型。（2）计算“每月偿还金额”。解决方案Step06格式化“投资贷款分析表”（1）按住【Ctrl】键，同时选中E3:E8、C11:F11及B12:B17单元格区域，将对齐方式设置为居中。（2）分别为B2:C7、E2:F8及A11:F17单元格区域设置内细外粗的表格边框。（3）单击【视图】→【显示/隐藏】选项，取消【网格线】选项，隐藏工作表网格线。拓展案例1．制作不同贷款利率下每月偿还金额贷款分析表（单模拟变量），如图5.55所示。2．制作不同贷款利率、不同还款期限下每月偿还金额贷款分析表（双模拟变量），如图5.56所示。拓展训练制作“本量利分析”效果如图5.57所示。案例小结本案例通过制作“投资决策分析”，介绍了在Excel中的财务函数PMT、模拟运算表、单变量模拟运算表、双变量模拟运算表等内容。这些函数和运算都可以用来解决当变量不是唯一的一个值而是一组值时所得到的一组结果，或变量为多个，即多组值甚至多个变化因素时对结果产生的影响。我们可以直接利用Excel中的这些函数和方法实现数据分析，为企业管理提供准确详细的数据依据。案例20往来账务管理案例分析知识与技能解决方案拓展案例拓展训练案例小结案例分析往来账是企业在生产经营过程中发生业务往来而产生的应收和应付款项。在公司的财务管理中，往来账务管理是一项很重要的工作。往来款项作为单位总资产的一个重要组成部分，直接影响到企业的资金使用、财务状况结构、财务指标分析等诸多方面。本案例通过制作“往来账务管理表”介绍Excel在往来账务管理方面的应用，效果如图5.71和图5.72所示。知识与技能●工作簿的创建●工作表重命名●使用公式和函数计算●单元格名称的使用●TODAY、IF、SUM函数的应用●数组公式应用●图表应用解决方案Step01创建工作簿，重命名工作表（1）启动Excel2010，新建一个空白工作簿。（2）将创建的工作簿以“往来账务管理”为名保存在“E:\公司文档\财务部”文件夹中。（3）将Sheet1工作表重命名为“应收账款明细表”。Step02创建“应收账款明细表”（1）选中“应收账款明细表”。（2）设置A1:G1合并后居中，输入表格标题“应收账款明细表”，字体为“华文中宋”、字号“18”。（3）按照图5.73所示输入表格字段标题和基础数据。解决方案Step03显示应收账款期限这里，设定收款期为90天。（1）选中E3单元格。（2）输入公式“=A3+90”，按【E