第5篇excel财务篇.ppt

第第5 5篇篇 财财 务务 篇篇 案例案例20-20-公怀贷款及预算公怀贷款及预算- -摸拟运算表摸拟运算表 5.3案例案例1919财务报表的制作财务报表的制作- -资产负债资产负债表表 5.2案例案例1818员工工次管理员工工次管理- -工资表的制工资表的制作作 5.1  学习目标学习目标 1．学会．学会Excel中导入中导入/导出外部数导出外部数据的方法据的方法 2．学会利用公式自动计算数据．学会利用公式自动计算数据 3．掌握．掌握Excel中函数的用法，如中函数的用法，如SUM、、IF函数等 4．以．以IF函数为例，理解函数嵌套函数为例，理解函数嵌套的意义和用法的意义和用法 5．．Excel表格打印之前的页面设表格打印之前的页面设置 6．利用公式完成财务报表相关项．利用公式完成财务报表相关项目的计算目的计算 7．利用向导完成不同类型企业的．利用向导完成不同类型企业的一组财务报表的制作一组财务报表的制作 8．财务函数的应用，如．财务函数的应用，如PMT 9．理解并学会单变量和双变量模．理解并学会单变量和双变量模拟运算表的构造。

拟运算表的构造5.15.1案例案例18 18 员工工资管理员工工资管理 ————工资表的制作工资表的制作 1.案例分析案例分析 员工工资管理是每个企业财务部门必员工工资管理是每个企业财务部门必然的工作，财务人员要清晰明了地计算出然的工作，财务人员要清晰明了地计算出各个项目，并且完成一定的统计汇总工作各个项目，并且完成一定的统计汇总工作 在人力资源部的案例中，我们已经学在人力资源部的案例中，我们已经学习了在习了在Excel中手工输入数据的方法这里中手工输入数据的方法这里直接使用已经创建好的直接使用已经创建好的“员工人事档案、员工人事档案、工资管理表工资管理表.xls”中的数据，以导入外部中的数据，以导入外部数据的方式来实现数据内容的填入数据的方式来实现数据内容的填入 在本案例中，需要利用已有工资项来在本案例中，需要利用已有工资项来计算其他工资项，最终核算出每个员工的计算其他工资项，最终核算出每个员工的“实发工资实发工资”，并设置好打印前的版面。

并设置好打印前的版面效果如图：效果如图：  2.解决方案解决方案（（1）导出数据为文本格式导出数据为文本格式2）导入外部数据导入外部数据3）引用其他工作表的数据引用其他工作表的数据 （（4）在后面的列中增加）在后面的列中增加“初算应税初算应税工资工资”、、“实际应税工资实际应税工资”、、“个人个人所得税所得税”、、“应扣工资应扣工资”、、“实发工实发工资资”列5）计算）计算“初算应税工资初算应税工资”6）利用函数计算）利用函数计算“实际应税工资实际应税工资” （（7）计算）计算“个人所得税个人所得税”8）利用函数计算）利用函数计算“应扣工资应扣工资”9）利用公式计算）利用公式计算“实发工资实发工资” （（10）使用自动填充功能完成所有人）使用自动填充功能完成所有人的的“初算应税工资初算应税工资”的填充计算的填充计算11）完成上述操作后，数据处理就）完成上述操作后，数据处理就完成了如果需要打印，则要使用完成了如果需要打印，则要使用“打印预览打印预览”功能来查看打印的效果功能来查看打印的效果 3.案例小结案例小结 本案例中，我们通过核算出每个员工本案例中，我们通过核算出每个员工的的“实发工资实发工资”，并设置好打印前的版面，，并设置好打印前的版面，介绍了在介绍了在Excel中可以以多种方式（复制、中可以以多种方式（复制、引用、导入）使用本工作簿或其他工作簿引用、导入）使用本工作簿或其他工作簿的工作表中的数据。

的工作表中的数据 IF函数的使用和嵌套可以实现二选一函数的使用和嵌套可以实现二选一或多选一结果的构造，对已有的表进行打或多选一结果的构造，对已有的表进行打印相关的设置（页面的设置、页眉印相关的设置（页面的设置、页眉/页脚的页脚的加入、打印方向等）能够让最后打印的表加入、打印方向等）能够让最后打印的表格更加美观格更加美观 IF函数的使用是本案例的学习重点，函数的使用是本案例的学习重点，它的含义、构造、结果等都应该在使用者它的含义、构造、结果等都应该在使用者清醒地控制下完成，这里需要一些逻辑思清醒地控制下完成，这里需要一些逻辑思维的能力维的能力 而除了有单纯的而除了有单纯的IF函数之外，函数之外，Excel还还提供了如提供了如AND、、NOT等逻辑函数，如等逻辑函数，如COUNTIF统计函数，使用者需要仔细理解统计函数，使用者需要仔细理解它们的含义后灵活地使用，以更好地实现它们的含义后灵活地使用，以更好地实现Excel强大的功能强大的功能5.2 5.2 案例案例19 19 财务报表的制作财务报表的制作 ————资产负债表资产负债表 1.案例分析案例分析 在试算平衡表和损益表的基础上，制在试算平衡表和损益表的基础上，制作企业的资产负债表。

效果如图：作企业的资产负债表效果如图：  2.解决方案解决方案（（1）启动）启动Excel，输入试算平衡表的，输入试算平衡表的表头2）输入各个会计科目的数据输入各个会计科目的数据3）计算出借方合计和贷方合计计算出借方合计和贷方合计4）建立损益表表头建立损益表表头 （（5）计算损益表的）计算损益表的“收入收入”6）计算）计算“营业费用营业费用”7）计算）计算“管理费用管理费用”8）计算）计算“本期净利本期净利” （（9）建立资产负债表的表头建立资产负债表的表头10）引用数据引用数据 （（11）计算借方和贷方的合计，如果）计算借方和贷方的合计，如果平衡，则数据正确平衡，则数据正确12）各类企业的报表，也可以利用）各类企业的报表，也可以利用“模板模板”中已有的中已有的“电子方案表格电子方案表格”来创建 3.案例小结案例小结 通过制作公司的通过制作公司的“资产负债表资产负债表”，学，学会了利用试算平衡等方法，来协助制作资会了利用试算平衡等方法，来协助制作资产负债表在产负债表在Excel中，除了直接输入之外，中，除了直接输入之外，还可以利用模板来生成所在行业企业的各还可以利用模板来生成所在行业企业的各类标准报表，再根据各个企业的自身特点类标准报表，再根据各个企业的自身特点进行修改和完善。

进行修改和完善5.3 5.3 案例案例20 20 公司贷款及预算公司贷款及预算 ————模拟运算表模拟运算表 1.案例分析案例分析 Excel中的模拟运算表主要用来考查一中的模拟运算表主要用来考查一个或两个重要决策变量的变动对于分析结个或两个重要决策变量的变动对于分析结果的影响，对于一些更复杂的计划安排，果的影响，对于一些更复杂的计划安排，常常需要考查更多的因素常常需要考查更多的因素 例如，为了达到公司的预算目标，可例如，为了达到公司的预算目标，可以从多种途径入手，如增加广告促销、提以从多种途径入手，如增加广告促销、提高价格增收、降低包装和材料等费用、减高价格增收、降低包装和材料等费用、减少非生产开支等来达到目的少非生产开支等来达到目的 利用利用Excel提供的多种财务函数，可以提供的多种财务函数，可以有效地计算财务相关数据有效地计算财务相关数据 利用利用Excel提供的方案管理器，可以进提供的方案管理器，可以进行更复杂的分析，模拟为达到预算目标选行更复杂的分析，模拟为达到预算目标选择不同方式的大致结果。

每种方式的结果择不同方式的大致结果每种方式的结果都被称为一个方案，根据多个方案的对比都被称为一个方案，根据多个方案的对比分析，可以考查不同方案的优势，从中选分析，可以考查不同方案的优势，从中选择最适合公司目标的方案择最适合公司目标的方案 本案例针对公司需要购进一批设备，本案例针对公司需要购进一批设备，要向银行贷款￥要向银行贷款￥250000元，分元，分15年还清的年还清的情况，查看公司每月的偿贷款额，并查看情况，查看公司每月的偿贷款额，并查看在不同的利率下每月的应还贷款额在不同的利率下每月的应还贷款额 更进一步查看针对不同贷款利率，不更进一步查看针对不同贷款利率，不同的偿还年限（偿还年限分别为同的偿还年限（偿还年限分别为10年，年，15年，年，20年年30年，年，40年）下所对应的每月应年）下所对应的每月应还的贷款金额效果如图：还的贷款金额效果如图：   2.解决方案解决方案（（1）在）在Excel中输入建立模拟运算表中输入建立模拟运算表的初步结构，保存文件为的初步结构，保存文件为“银行贷款银行贷款.xls”2）在）在B4单元格中，利用财务函数单元格中，利用财务函数PMT来计算来计算“月支付额月支付额”。

 （（3）选择包括公式和需要被替换数）选择包括公式和需要被替换数值的单元格区域值的单元格区域A4：：C11，这部分区，这部分区域就是所谓的模拟运算表域就是所谓的模拟运算表 （（4）使用）使用“数据数据”菜单中的菜单中的“模拟模拟运算表运算表”命令，弹出命令，弹出“模拟运算表模拟运算表”对话框，在对话框，在“输入引用列的单元格输入引用列的单元格”中选择中选择$A$4 （（5）单击）单击“确定确定”按钮后，得到模按钮后，得到模拟运算表的结果，可查看不同利率下拟运算表的结果，可查看不同利率下贷款的月应还贷款额贷款的月应还贷款额 （（6）在另外一张工作表中，输入贷）在另外一张工作表中，输入贷款利息和偿还年限的数据款利息和偿还年限的数据 （（7）在行和列的交叉处）在行和列的交叉处A4单元格中，单元格中，利用利用PMT函数构造等额分期偿还额的函数构造等额分期偿还额的计算公式计算公式 （（8）选中模拟运算表的单元格区域，）选中模拟运算表的单元格区域，弹出弹出“模拟运算表模拟运算表”对话框，填写公对话框，填写公式中引用选中区域的首行、首列的单式中引用选中区域的首行、首列的单元格元格$E$1和和$F$1。

 （（9）利用双变量模拟运算表最后计）利用双变量模拟运算表最后计算的结果，查看针对不同贷款利率，算的结果，查看针对不同贷款利率，不同的偿还年限下所对应的每月应还不同的偿还年限下所对应的每月应还的贷款金额有人的的贷款金额有人的“初算应税工资初算应税工资”的填充计算的填充计算 3.案例小结案例小结 通过制作通过制作“银行贷款银行贷款”和和“分析一种分析一种商品的获利情况商品的获利情况”，介绍了在，介绍了在Excel中的财中的财务函数务函数PMT、模拟运算、单变量（行模拟、模拟运算、单变量（行模拟运算表运算表\列模拟运算表）模拟运算表、双变列模拟运算表）模拟运算表、双变量模拟运算表等内容量模拟运算表等内容 这些函数和运算，都可以用来解决当这些函数和运算，都可以用来解决当变量不是唯一的一个值而是一组值时所得变量不是唯一的一个值而是一组值时所得到的一组结果，或变量为多个，即多组值到的一组结果，或变量为多个，即多组值甚至多个变化因素时对结果产生的影响甚至多个变化因素时对结果产生的影响 我们可以直接利用我们可以直接利用Excel中的这些函数中的这些函数和方法，实现数据分析，为企业管理提供和方法，实现数据分析，为企业管理提供准确详细的数据依据。