资源描述
二 一 三月一日实训一实训一 EXCEL 基本知识基本知识及运用2实训目标：熟悉 EXCEL 常用工具的使用，掌握公式和函数的引用方法。实训要求：掌握 EXCEL 工作环境的调整，常用工具的使用，工作表的操作，函数、公式的基本使用。操作完毕，关闭所有文件，把操作结果发到班级文件夹。(一 )基本函数建立操作1、 求绝对值函数 ABS2、 求和函数 SUM3、 求平均函数 AVERAGE4、 求个数函数 COUNT5、 求最大值/最小值函数 MAX/MIN3（二）（二） EXCEL 基本操作（基本操作（ 1）1实验目的实验目的 通过具体实例完成对工作簿的建立、打开、保存等操作。通过具体实例完成对工作簿的建立、打开、保存等操作。 熟练掌握对工作表中各种数据类型的输入方法和技巧。熟练掌握对工作表中各种数据类型的输入方法和技巧。 熟练掌握数据的编辑及工作表的编辑方法。熟练掌握数据的编辑及工作表的编辑方法。 掌握数据填充、公式和函数运算技巧。掌握数据填充、公式和函数运算技巧。 对工作表数据进行编辑和格式化处理。对工作表数据进行编辑和格式化处理。 2实验内容实验内容1）建立如图所示的）建立如图所示的 “学生成绩表学生成绩表 ”和和 “学生消费表学生消费表 ” 42）工作表的基本操作）工作表的基本操作 再次打开工作簿文件再次打开工作簿文件 “成绩表成绩表 .xls”。练习求和与平均数的计算。练习求和与平均数的计算。 将工作表将工作表 “学生成绩表学生成绩表 ”复制到工作表复制到工作表 “学生消费表学生消费表 ”之后。之后。 将工作簿另存为将工作簿另存为 “姓名实训一姓名实训一 .xls”。 在工作簿在工作簿 “姓名实训一姓名实训一 .xls”中的工作表中的工作表 “学生消费表学生消费表 ”之前插入一个空白工作表。之前插入一个空白工作表。 删除刚刚插入的空白工作表。删除刚刚插入的空白工作表。 关闭工作簿关闭工作簿 “姓名实训一姓名实训一 .xls”。 3）单元格内容的基本操作）单元格内容的基本操作 再次打开工作簿再次打开工作簿 “姓名实训一姓名实训一 .xls”。 单击工作表单击工作表 “成绩表成绩表 ”标签，使其成为当前工作表，计算标签，使其成为当前工作表，计算 “总分总分 ”“平均分平均分 ”。 保存工作簿保存工作簿 “姓名实训一姓名实训一 .xls”。4）单元格数据的格式化）单元格数据的格式化 选取整个数据区，单击选取整个数据区，单击 “格式格式 ”工具栏上的工具栏上的 “居中居中 ”按钮按钮 ，将所有单元格的数据居中。，将所有单元格的数据居中。 选择学生成绩表标题选择学生成绩表标题 A1： K1 区域，单击区域，单击 “合并及居中合并及居中 ”按钮按钮 ，使标题，使标题 “学生成绩表学生成绩表 ”在此区域合并居中，并设置文字为隶书、在此区域合并居中，并设置文字为隶书、 16 号。还可以设置字形、颜色及数据区的背景等。号。还可以设置字形、颜色及数据区的背景等。5）排列学生成绩名次）排列学生成绩名次计算出计算出 “总分总分 ”与与 “平均分平均分 ”之后，可使用之后，可使用 RANK()函数进行成绩名次的排列。函数进行成绩名次的排列。 打开工作簿文件打开工作簿文件 “姓名实训一姓名实训一 .xls”。 选中选中 K3 单元格，执行单元格，执行 “插入插入 ”“”“ 函数函数 ”命令，在拉出的命令，在拉出的 “粘贴函数粘贴函数 ”对话框中选择对话框中选择“统计统计 ”“RANK” 命令，在弹出的命令，在弹出的 “函数参数函数参数 ”对话框命令中输入各参数，如图所示。对话框命令中输入各参数，如图所示。 5Number：判断顺序的数值。此例输入：判断顺序的数值。此例输入 I3（总分）（总分） 。 Ref：判断顺序的引用地址，若非数值则被忽略。用鼠标选取引用地址区域：判断顺序的引用地址，若非数值则被忽略。用鼠标选取引用地址区域 I3： I12。 Order：用来指定排序方式。输入数值：用来指定排序方式。输入数值 0 或忽略，降序；输入非或忽略，降序；输入非 0 值，升序。此处输入值，升序。此处输入0，降序排列。，降序排列。单击单击 “确定确定 ”按钮，第一位学生的名次排好。按钮，第一位学生的名次排好。 拖动拖动 K3 右下角的填充柄至右下角的填充柄至 K12 单元格，出现一列数据，但这并不是真正的名次，因为单元格，出现一列数据，但这并不是真正的名次，因为Ref 自变量没有取同一区域值。自变量没有取同一区域值。 分别将分别将 K4 K12 单元格中的单元格中的 Ref 自变量区域更改为自变量区域更改为 I3： I12。 至此，真正的名次就排好了。至此，真正的名次就排好了。 保存文件，退出保存文件，退出 Excel。 （二）（二） EXCEL 基本操作（基本操作（ 2）1实验目的实验目的 熟悉图表类型及其含义。熟悉图表类型及其含义。 掌握在掌握在 Excel 工作簿中插入图表的方法。工作簿中插入图表的方法。 掌握对图表进行编辑的方法。掌握对图表进行编辑的方法。 学会在各种不同图表中的转换。学会在各种不同图表中的转换。2实验内容实验内容1）建立如图）建立如图 2.1 所示某市近年来某大类的所示某市近年来某大类的 “姓名实训一姓名实训一 .xls”文件，输入各列数据。文件，输入各列数据。 图图2.162）生成直方图）生成直方图对表文件中第对表文件中第 9 行行 “电子计算机电子计算机 ”的历年数据生成直方图。的历年数据生成直方图。提示：（提示：（ 1）选择生成图表的数据区）选择生成图表的数据区按住按住 Ctrl 键，然后用鼠标单击第键，然后用鼠标单击第 2 行（用做图表的行标题）和第行（用做图表的行标题）和第 9 行（用做图表的数据）行（用做图表的数据）行号（呈反向显示状态）行号（呈反向显示状态） 。（ 2）利用图表向导生成图表）利用图表向导生成图表 用鼠标单击菜单项用鼠标单击菜单项 “插入插入 ”“”“ 图表图表 ”或单击常用工具栏上的或单击常用工具栏上的 “插入图表插入图表 ”工具按钮工具按钮 ，系统弹出图表向导对话框。，系统弹出图表向导对话框。 在对话框的在对话框的 “标准类型标准类型 ”选项卡中，选择左侧选项卡中，选择左侧 “图表类型图表类型 ”列表框中的列表框中的 “柱形图柱形图 ”，在，在右侧的右侧的 “子图表类型子图表类型 ”选择第一种子图表类型（常用的图表类型一般均放置在最前面，以方选择第一种子图表类型（常用的图表类型一般均放置在最前面，以方便用户选择）如图便用户选择）如图 2.2（ a）所示。）所示。此时可以用鼠标按下对话框下方的此时可以用鼠标按下对话框下方的 “按住以查看示例按住以查看示例 ”按钮，预览将生成的图表样例，帮助按钮，预览将生成的图表样例，帮助用户选择满意的图表类型，如图用户选择满意的图表类型，如图 2.2（ b）所示。单击）所示。单击 “下一步下一步 ”按钮。按钮。 7 通过对图表的预览，可以改变图表中的一些选项（如图表的标题、坐标轴的标题、刻度、通过对图表的预览，可以改变图表中的一些选项（如图表的标题、坐标轴的标题、刻度、图例说明、数据标志等）图例说明、数据标志等） ，以便更好地适应用户的实际需要。，以便更好地适应用户的实际需要。在在 “图表选项图表选项 ”对话框中，图表的各个选项分别安排在六个选项卡中。对话框中，图表的各个选项分别安排在六个选项卡中。“标题标题 ”选项卡：可以对图表的标题进行编辑。单击选项卡：可以对图表的标题进行编辑。单击 “图表标题图表标题 ”输入框，当鼠标指针为输入框，当鼠标指针为 I字形后，输入文字，将标题改为字形后，输入文字，将标题改为 “电子计算机数量成倍增长电子计算机数量成倍增长 ”。单击单击 “图例图例 ”选项卡，因为只对选项卡，因为只对 “计算机计算机 ”一项数据进行图表的制作，图表标题已明确说明，一项数据进行图表的制作，图表标题已明确说明，所以图表右侧的所以图表右侧的 “图例图例 ”复选项可以去掉。复选项可以去掉。在在 “图例图例 ”选项卡中，用鼠标单击选项卡中，用鼠标单击 “显示图例显示图例 ”前面的方框，将其中的前面的方框，将其中的 “”“” 去掉，在图表去掉，在图表预览区中立即可以看到改变后的图表，单击预览区中立即可以看到改变后的图表，单击 “下一步下一步 ”按钮。按钮。 可以选择可以选择 “作为新工作表插入作为新工作表插入 ”还是还是 “作为其中的对象插入作为其中的对象插入 ”。选择。选择 “作为其中的对象插作为其中的对象插入入 ”，便于图表与数据表对照浏览。，便于图表与数据表对照浏览。 单击单击 “完成完成 ”按钮，生成图表（内嵌图表）按钮，生成图表（内嵌图表） 。将工作表标签。将工作表标签 Sheet1 改为改为 “计算机增长表计算机增长表 ”（方法：鼠标右键单击工作表标签（方法：鼠标右键单击工作表标签 Sheet1，在弹出的快捷菜单中选择，在弹出的快捷菜单中选择 “重命名重命名 ”）如图）如图 2.3所示。所示。 3）图表的简单修饰：图表生成后，需对其位置、大小等做进一步修饰，已达到满意的效果。）图表的简单修饰：图表生成后，需对其位置、大小等做进一步修饰，已达到满意的效果。试试看：在工作表中的试试看：在工作表中的 E9 单元格中，将数据改为单元格中，将数据改为 37602，将，将 D9 单元格数据改单元格数据改 9859，观察数，观察数据改动后，图表中的改动部分是否会发出相应的改变。据改动后，图表中的改动部分是否会发出相应的改变。4）增加数据区，更改图表类型）增加数据区，更改图表类型8在在 “计算机增长表计算机增长表 ”工作表中，增加汽车、电子计算机、电视机、钢琴逐年增加的数据，工作表中，增加汽车、电子计算机、电视机、钢琴逐年增加的数据，在在 “Sheetx”工作表中生成折线图（独立图表）工作表中生成折线图（独立图表） 。 操作提示：操作提示：（ 1）图表数据的取值）图表数据的取值用鼠标选定第用鼠标选定第 2 行数据后，按住行数据后，按住 Ctrl 键，用鼠标分别单击键，用鼠标分别单击 “汽车汽车 ”、 “电子计算机电子计算机 ”、 “电视电视机机 ”和和 “钢琴钢琴 ”所在行，可选中这些行的数据。所在行，可选中这些行的数据。（ 2）生成图表）生成图表单击单击 “图表向导图表向导 ”按钮，在按钮，在 “图表类型图表类型 ”对话框中，选择对话框中，选择 “图表类型图表类型 ”为为 “折线图折线图 ”， “子子图表类型图表类型 ”为第为第 4 种子图表类型。种子图表类型。 在如图在如图 2.4 所示的所示的 “图表源数据图表源数据 ”对话框中，直接单击对话框中，直接单击 “下一步下一步 ”按钮。按钮。在在 “图表选项图表选项 ”对话框中，选定对话框中，选定 “标题标题 ”选项卡，在选项卡，在 “图表标题图表标题 ”栏中输入表格标题栏中输入表格标题 “城城市主要工业产品产量成倍增长市主要工业产品产量成倍增长 ”，在，在 “分类（分类（ X）轴）轴 ”栏中输入栏中输入 “年份年份 ”，在，在 “数值（数值（ Y）轴）轴 ”栏中输入栏中输入 “产量（万台产量（万台 /辆）辆） ”，然后单击，然后单击 “网格线网格线 ”标签。标签。 在在 “网格线网格线 ”选项卡中，将选项卡中，将 “分类（分类（ X）轴）轴 ”复选框复选框 “主要网格线主要网格线 ”选定，在图表预览选定，在图表预览区会看到图表中的网格线增加了竖线。区会看到图表中的网格线增加了竖线。单击单击 “图例图例 ”选项卡，选中选项卡，选中 “底部底部 ”单选项，将单选项，将 “图例图例 ”置于图表底部，如图置于图表底部，如图 2.5 所示。所示。单击单击 “下一步下一步 ”按钮，在按钮，在 “图表位置图表位置 ”对话框中，鼠标指向对话框中，鼠标指向 “嵌入工作表嵌入工作表 ”下拉列表框的下拉列表框的下箭头按钮，在其中选择下箭头按钮，在其中选择 “Sheetx”。 单击单击 “完成完成 ”按钮。图表（独立图表）在按钮。图表（独立图表）在 “Sheetx”工作表中生成。将工作表标签工作表中生成。将工作表标签“Sheetx”改为改为 “主要产品增长表主要产品增长表 ” ，如图，如图 2.6 所示。所示。 95）对生成的图表进行适当的调整）对生成的图表进行适当的调整 用鼠标单击图表中的标题，出现调整点，在用鼠标单击图表中的标题，出现调整点，在 “格式格式 ”工具栏中将字体设定为工具栏中将字体设定为 “华文新华文新魏魏 ”，将字号设定为，将字号设定为 “16”。 依次单击图表中的分类轴、图例等文字和数据部分（出现调整点）依次单击图表中的分类轴、图例等文字和数据部分（出现调整点） ，将其字号都设定为，将其字号都设定为“10”。把绘图区（中间的灰色区域）适当调高。把绘图区（中间的灰色区域）适当调高。6）图表的修饰）图表的修饰备注：（ 1）数字（值）型数据及输入）数字（值）型数据及输入 输入分数时，应在分数前输入输入分数时，应在分数前输入 0（零）和一个空格，如分数（零）和一个空格，如分数 3/8 应输入应输入 “0 3/8”。 输入负数时，应在负数前输入负号，或将其置于括号中。如输入负数时，应在负数前输入负号，或将其置于括号中。如 -8 应输入应输入 “-8 或（或（ 8） ”。 在数字之间可以使用千分位号在数字之间可以使用千分位号 “， ”分隔，如分隔，如 12002 可输入为可输入为 “12， 002”。（ 2）日期和时间型数据及输入）日期和时间型数据及输入 一般情况下，日期分隔符使用一般情况下，日期分隔符使用 “/”或或 “”。例如，。例如， 2005/5/20、 2005-5-20、 20/May/2005 或或 20-May-2005 都表示同一个日期都表示同一个日期 2005 年年 5 月月 20 日。日。 如果只输入月和日，如果只输入月和日， Excel 2000 取计算机内部时钟的年份作为默认值。取计算机内部时钟的年份作为默认值。 时间分隔符一般使用冒号时间分隔符一般使用冒号 “： ”。例如，输入。例如，输入 7： 0： 1 或或 7： 00： 01 都表示都表示 7 点点 0 分分 1 秒。秒。可以只输入时和分，也可以只输入小时数和冒号，还可以输入小时数大于可以只输入时和分，也可以只输入小时数和冒号，还可以输入小时数大于 24 的时间数据的时间数据（系统将自动换算）（系统将自动换算） 。如果要基于。如果要基于 12 小时制输入时间，则在时间（不包括只有小时数和冒号小时制输入时间，则在时间（不包括只有小时数和冒号的时间数据）后输入一个空格，然后输入的时间数据）后输入一个空格，然后输入 AM 或或 PM（也可以是（也可以是 A 或或 P） ，分别用来表示上午或，分别用来表示上午或下午，否则，下午，否则， Excel 2000 将基于将基于 24 小时制计算时间。小时制计算时间。 如果在单元格中既输入日期又输入时间，中间必须用空格隔开。如果在单元格中既输入日期又输入时间，中间必须用空格隔开。（ 3）自动填充数据）自动填充数据（ 4） 相对引用和绝对引用相对引用和绝对引用1）相对引用）相对引用相对引用是指当把一个含有单元格或单元格区域地址的公式复制到新的位置时，公式中的相对引用是指当把一个含有单元格或单元格区域地址的公式复制到新的位置时，公式中的单元格或单元格区域地址随着改变，公式中的值将会依据更改后的单元格或单元格区域地址单元格或单元格区域地址随着改变，公式中的值将会依据更改后的单元格或单元格区域地址的值重新计算。使用相对引用能很快得到其他的单元格的公式结果，在实际的应用中使用较的值重新计算。使用相对引用能很快得到其他的单元格的公式结果，在实际的应用中使用较多。多。2）绝对引用）绝对引用绝对引用是指公式中的单元格或单元格区域地址不随公式位置的改变而发生改变。不论公绝对引用是指公式中的单元格或单元格区域地址不随公式位置的改变而发生改变。不论公式的单元格处在什么位置，公式中所引用的单元格位置都是其在工作表中的确定位置。绝对式的单元格处在什么位置，公式中所引用的单元格位置都是其在工作表中的确定位置。绝对10单元格引用的形式是在每一个列标及行号前加一个单元格引用的形式是在每一个列标及行号前加一个 $符号。如符号。如 $A$5。 3）混合引用）混合引用混合引用是指单元格或单元格区域的地址部分是相对引用，部分是绝对引用，如混合引用是指单元格或单元格区域的地址部分是相对引用，部分是绝对引用，如 $B2 或或B$2。4）相对引用与绝对引用之间的切换）相对引用与绝对引用之间的切换如果创建了一个公式并希望将相对引用更改为绝对引用，或将绝对引用更改为相对引用，如果创建了一个公式并希望将相对引用更改为绝对引用，或将绝对引用更改为相对引用，则先选定包含该公式的单元格，然后在编辑栏中选择要更改的引用并按则先选定包含该公式的单元格，然后在编辑栏中选择要更改的引用并按 F4 键。每次按键。每次按 F4 键键时，时， Excel 2000 会在以下组合间切换：绝对列与绝对行（如会在以下组合间切换：绝对列与绝对行（如 $C$1） 、相对列与绝对行（、相对列与绝对行（ C$1） 、绝对列与相对行（绝对列与相对行（ $C1）及相对列与相对行（）及相对列与相对行（ C1） 。 利用 Excel 设计一个会计凭证表，并完成一下操作，如图 2 所示。 设置会计凭证表格式：要求包含“月” 、 “日” 、 “科目代码” 、 “科目名称、 “借方金额”和“贷方金额”字段。 输入 10 笔经济业务。 对会计凭证表进行自动筛选。图 211实训二 财务分析与财务预测实训目标：建立财务分析模型，对企业财务状况进行分析，对企业未来的发展情况进行预测。实训要求：（1）根据给定的资产负债表和利润表，建立财务比率分析模型，建立杜邦分析图，并进行分析、预测。P170-179。12（2）根据给定的数据，绘制相应的趋势分析图。P180-18213（3）根据给定的数据预测下一阶段的销售量。14（4）操作完毕，关闭所有文件，把操作结果发到班级文件夹。15实训三 工资核算系统实训目标：学会使用 EXCEL 对公司员工进行工资管理。实训要求：掌握基本工资数据的输入，工资项目的设置，工资数据的查询及工资数据的汇总分析。操作完毕，关闭所有文件，把操作结果发到班级文件夹。1、 建立“员工信息”工作表2、建立工资明细表（该公司工资项目有基本工资、岗位工资、奖金、日工资、交通补贴、应发工资、病假扣款、事假扣款、计税基数、实发工资等）具体计算标准如下：（1）岗位工资：企业管理人员 800 元，福利人员 750 元，其他人员 850 元（2）奖金：企业管理人员和福利人员 200 元，其他人员 300 元（3）交通补贴：销售人员 200 元，其他人员 150 元16（4）计税基数：基本工资+岗位工资+奖金-病假扣款-事假扣款（5）日工资：（基本工资+岗位工资+奖金）/21.17（6）病假扣款：工龄 10 年以上，每天扣日工资的 20%，工龄为 510 年，每天扣日工资的30%，工龄为 5 年以下的，每天扣日工资的 50%。（7）事假扣款：事假天数*日工资3、建立请假记录表4、计算每个员工的工资，并制成工资条及部门汇总表。（1）岗位工资（2）奖金：（3）病假扣款：（4）工资条的制作一、选中单元格区域，定义单元格名称17二、制作工资条1、插入一张工作表，命名为“工资条”2、在单元格 A3 中输入公式 “=NOW()” 单元格格式为“2001 年 3 月” 3、在单元格 B3 输入编号，然后单击单元格 C3，输入公式 “=VLOOKUP(B3,工资表，2，0)” ，按回车键，结果如下图。4、后面项目同上步骤（5）部门汇总表(安实发工资求和项汇总 )1）打开工资明细表，将数据按部门排序，然后选中整个单元格区域。2）单击“数据”|“分类汇总 ”。18实训四实训四 投资决策投资决策实训目标：通过实习使学生掌握折旧函数分析、更新决策模型设计、投资风险分析模型设计。实训要求：能快速分析给定的问题，能准确熟练的使用 EXCEL 提供的投资决策指标函数，模型选定的方式合理、准确。操作完毕，关闭所有文件，把操作结果发到班级文件夹。实训资料：实训资料： 1、5 年中，每年存入银行 100 元，存款利息为 8%，求第 5 年末年金终值。2、现在存入一笔钱，准备在以后 5 年中每年得到 100 元，如果存款利率为 8%。3、现值存入银行 736 元，在年利率为 6%的条件下，10 年内，每年能从银行提取多少元。4、某校为设立奖学金，每年存入银行 20000 元，存款利率为 10%第 5 年年末可的款 122100 元，求第 3年得到的存款利息。5、某企业租用一台设备，租金 36000 元，年利率为 8%，每年年末支付租金，租期为 5 年。（1）每期支付租金（2）第 3 年支付的本金（3）第 3 年支付的利息6、某企业租用一台设备，租金 36000，年利率为 8%，每年年末支付租金，若每年支付 10000 元，需要多少年支付完租金？7、某企业租用一台设备，租金 36000 元，每年年末支付租金。若租期为 5 年，每年支付 10000 元，则支付利息为多少？样式一、资金时间价值一、资金时间价值1、年金终值函数 FV()192、年金现值函数 PV()203、年金函数 PMT()214、年金中的利息函数 IPMT()225、年金中的本金函数 PPMT()6、计息期数函数 NPER()7、利率函数 RATE()8、净现值函数 NPV（）239、内含报酬率函数 IRR()2410、修正内含报酬率函数11、净现值指数 PI12、固定资产折旧函数（1）直线折旧法函数 SLN（cost，salvage，life）25（2）双倍余额递减法函数 DDB（cost，salvage，life，period，factor）（3）年数总和法函数 SYD（cost，salvage，life，per）（4）倍率余额递减法函数 VDB（cost，salvage，life，START-period，END-period，factor）2613、固定资产更新决策（寿命相等）净现值差额法272814、固定资产更新决策（寿命不相等）年均现金流量29实训五实训五 筹资决策筹资决策一、实习目标：学会长期借款筹资双变量分析模型设计，建立借款分析模型、租赁筹资模型设计，租赁筹资与借款筹资方案比较分析模型设计。二、实习要求：能快速分析给定的问题，能准确熟练的使用 EXCEL 提供的资金时间价值函数，模型选定的方式合理、准确。操作完毕，关闭所有文件，把操作结果发到班级文件夹。三、实验步骤1、建立借款分析模型（单变量、双变量、基本模型） 。2、建立租赁分析模型。303、建立租赁与借款对比分析模型。四、实验内容根据实验资料，按步骤进行有关的操作。五、实训资料资料一:.金华某企业向银行申请长期贷款，用于购买设备，借款金额为 10 万元，银行贷款年利率为 8%，借款期限是四年，现有三种还款方式：等额还款。每年年未只付利息，本金到最后一年末付清。最后一年一次全部偿还本金及利息。请根据所提供的数据，建立分析模型（数据计算公式） ，计算偿还款数据（形成计算表） ，请选择一种还款方式，并说明选择的理由。 （列出偿还及尚欠金额）成果样式资料二:租赁分析1、建立如下图租赁筹资分析模型2、建立图形控制项按钮。打开“视图”菜单，选择“工具栏/窗体”命令，即可显示“窗体”工具栏。31 建立“设备名称”项目下的下拉控制项。在“窗体”工具栏中单击“组合框” ，鼠标指针变为“+”形状，按住鼠标左键经过 F2:G2 单元格区域，松开鼠标，此时拖动鼠标经过的区域会出现一个带有三角形标记的矩形框，双击该矩形框，如下图所示：选择数据源。将“单元格链接”选项设为“D2” ，显示被选中下拉列表中某项的序号，以便其他单元格使用。单击确定 建立“每年付款次数”微调控制项。在“窗体”工具栏中单击“微调项” ，鼠标指针变为“+”形状，按住鼠标左键在 G5 单元格拖动，松开鼠标，在 G5 单元格中会出现一个矩形框。双击该矩形框，打开如下图所示的“设置控件格式”对话框。32 设置“租金年利率”滚动条。在“窗体”工具栏中单击“滚动条” ，鼠标指针变为“+”形状，按住鼠标左键在 G6 单元格拖动，松开鼠标，在 G6 单元格中会出现矩形框双击该矩形框，打开如下图所示的“设置控件格式”对话框。 设置“租赁期限”微调控制项。在“窗体”工具栏中单击“微调项” ，鼠标指针变为“+”形状，按住鼠标左键在 G5 单元格拖动，松开鼠标，在 G5 单元格中会出现一个矩形框。双击该矩形框，打开如下图所示的“设置控件格式”对话框。33 设置模型中其他单元格的公式。租金总额 F3= INDEX(B3:B8,D2)支付租金方式 F4=INDEX(C3:C8,D2)总付款期限 F8=F5*F7每期应付租金 F9=IF(F4=后付,ABS(PMT(F6/100/F5,F8,F3),ABS(PMT(F6/100/F5,F8,F3,0,1)3租赁筹资分析模型的应用当租赁筹资分析模型建好以后，财务管理人员可通过单击“设备名称”的下拉列表框选择要分析的租赁设备，通过调整租赁期限、每年付款次数和租金年利率三个项目的数值来计算所选租赁设备应付租金数额，并且由于在公式中定义了单元格链接，所以，即使租赁公司提供的设备某些参数发生改变，该模型会自动调整相应的计算结果。资料三：租赁筹资与借款筹资方案比较分析模型设计假设企业决定添置一台价值 200000 元的设备，该台设备使用期为 5 年，预计 5 年后将无残34值。如果企业采用租赁方式取得该设备，租赁公司要求该设备原价在 5 年内摊销，并要求得到 10%租赁费（收益）率，按照惯例每年租金需要预付。该企业也可以用银行的年利率为 11%，每年年末等额偿还的贷款购买这台设备。该企业的所得税率为 33%，折旧采用直线法，贴现率为 5%。选择筹资方式。分析： 该企业如果租用设备，按租赁公司的条件，每年初支付租金。由于这些支出的租金是费用，可从应税收益中扣除，而且，这些租金支出应在付税当年扣税。如果企业采用借债购买设备，那么企业每年支付给银行的利息及设备折旧都属于费用，该从应税收益中扣除。因此采用 NPV 进行分析，分析计算出各自的税后现金流量，然后把它们转换成现值，选择成本现值较小的方案进行筹资。步骤一1建立租赁筹资现金流量表基本模型2定义模型中各项目的勾稽关系租金支付额=每期应付租金避税额=租金支付额*所得税税率税后现金净流量=租金支付额-避税额现值=租赁现金净流量/（1+贴现率）租赁期限3定义模型中各有关单元格的公式（1）租金支付额B13=$C$935将此公式分别复制到 B14:B17 单元格区域。B19=SUM(B13:B18)（2）避税额C13=B13*$B$11将此公式分别复制到 C14:C17 单元格区域。C19=SUM(C13:C18)（3）税后净现金流量D13=B13-C13将此公式分别复制到 D14:D17 单元格区域。D19=SUM(D13:D18)（4）净现值E13=D13/(1+$D$11)A1336将此公式分别复制到 E14:E17 单元格区域。E19=SUM(E13:E18)最终计算的结果如图步骤二1建立举债筹资现金流量表基本模型2定义模型中各项目的勾稽关系还款额=每期偿还金额本期利息=本期期初本金余额*利率本期偿还本金=本期偿还金额-本期利息本期期末余额=本期期初余额-本期偿还本金避税额=（每期偿还利息+折旧额）*所得税税率税后净现金流量=本期还款额-避税额现值=每期净现金流量/（1+贴现率）租赁期数借款成本总现值=各期税后净现金流量的现值之和3定义模型中各有关单元格的公式37（1）总还款次数 E3=E2*B4（2）每期偿还金额（3）还款额B9=$E$4将此公式分别复制到 B10:B13 单元格区域。B14=SUM(B9:B13)（4）期初所欠本金C9=B2C10=C9-D9将 C10=C9-D9 分别复制到 C11:C13 单元格区域。C14=SUM(C9:C13)38（5）偿还本金D9=B9-E9将此公式分别复制到 D10:D13 单元格区域。D14=SUM(D9:D13)（6）偿还利息E9=C9*$B$3将此公式分别复制到 E10:E13 单元格区域。E14=SUM(E9:E13)39（7）折旧额F9=SLN($B$2,$B$4)将此公式分别复制到 F10:F13 单元格区域。F14=SUM(F9:F13)（8）避税额G9=(E9+F9)*$B$7将此公式分别复制到 G10:G13 单元格区域。G14=SUM(G9:G13)40（9）净现金流量H9=B9-G9将此公式分别复制到 H10:H13 单元格区域。H14=SUM(H9:H13)（10）现值I9=H9/(1+$D$7)A9将此公式分别复制到 I10:I13 单元格区域。I14=SUM(I9:I13)最终计算结果如下41步骤三对比可以看出，租赁方案总成本现值为 146085.62，借款方式筹资总成本现值为 156393.7元，租赁方式的总成本低，所以该企业应该选择租赁的方式取得该设备。附加题某企业计划添置一台机器设备,需要投入 150000 元,预计使用寿命为 8 年,设备按平均年限法计提折旧,预计期末残值为 5000 元,所得税率为 33%.该企业为筹集这笔资金,有两种选择:一是从银行取得贷款,利率为 10%,8 年后等额分期偿还，并假设在每年年末偿还;二是从租赁公司租赁该设备,租赁公司要求该设备在 8 年内摊销,并要求取得 8%的资金收益率,租金在每年年初支付,该设备由出租人负责维修,承租人不再承担其他附加费用.要求:根据以上资料，试分析哪一种筹资方式对企业有利。42实训六 投资风险分析在市场经济的今天，投资活动愈发显得频繁和重要。由于投资活动充满不确定性，所以任何投资总要承担一定的风险。如果决策面临的不确定性比较大，足以影响投资方案的选择，就应该对不同的方案进行计量，例如计算比较各种方案的期望净现值，作为投资决策的依据。Excel中大量的财务、统计等各种函数及其强大的表格功能，加上简单易行的操作，使其成为辅助投资风险分析的良好助手。其中的“方案管理器”更有助于如投资决策这种多方案问题的分析。本例中，某企业现在面临两种投资方案：新建厂房生产新产品和扩建厂房生产现有产品。新建厂房须投资300万元，扩建厂房须投资100万元。产品的市场前景不能确定。究竟使用那种方案，须考虑多种因素，而两种方案的预计净现值比较是必须考虑的重要依据。本例目标： 学习使用 IF 函数 学习设置单元格的有效数据范围 学习使用 NPV 函数计算净现值 学习在工作表中进行方案管理 学习设置共享工作簿步骤一：建立工作表该企业目前面临5种可能的市场前景，各前景的说明及预计发生概率如表11-1。已知基本折现率为15%，厂房使用年限为4年。表 11-1 各前景的说明及发生概率前 景 说 明 概 率前景1 新产品畅销，现有产品滞销 25%前景2 第一年现有产品畅销，一年后新产品畅销35%前景3 前两年现有产品畅销，两年后新产品畅销20%前景4 前三年现有产品畅销，三年后新产品畅销10%前景5 现有产品畅销，新产品滞销 10%首先新建名为“投资风险分析”的工作簿，并在其中建立计算净现值的表格（如图11-1所示）。拟在工作表中先由各种前景的概率计算出各年的期望年净收益，再用函数计算净现值。43图 11-1 建立净现值计算表格步骤二：输入逻辑公式在本例中，要计算两种不同的方案的预计净现值，并加以比较。为在一张表格中计算两种不同的方案的预计净现值，使用逻辑函数IF来计算各种前景下的各年净收益。一、IF 函数简介IF函数用于执行真假值判断，根据逻辑测试的真假值，返回不同的结果。可以使用函数IF对数值和公式进行条件检测。语法：IF(logical_test,value_if_true,value_if_false)参数：Logical_test 可以是计算结果为 TRUE 或 FALSE 的任何数值或表达式。Value_if_true 是 Logical_test 为 TRUE 时函数的返回值。如果 logical_test 为 TRUE 并且省略value_if_true，则返回 TRUE。Value_if_true 可以为某一个公式。Value_if_false 是 Logical_test 为 FALSE 时函数的返回值。如果 logical_test 为 FALSE 并且省略value_if_false，则返回 FALSE。Value_if_false 可以为某一个公式。说明：函数IF可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。在计算参数value_if_true和value_if_false后，函数IF返回相应语句执行后的返回值。如果函数IF的参数包含数组，则在执行IF语句时，数组中的每一个元素都将计算。如果某些value_if_true和value_if_false参数为操作提取函数，则执行所有的操作。二、使用 IF 函数下面使用IF函数计算各年净现值。已知如果产品畅销，预计年净收益为180万元。如果产品滞销，预计年净收益为20万元。操作步骤如下：1. 将单元格B4命名为“投资”，将单元格B5命名为“产品”。2. 单击选中B10单元格。由前景说明可知该单元格对应的情况下，新产品畅销，现有产品滞销。也就是说如果企业生产的产品为新产品，则年净收益为180万元，如果企业生产的产品是现有产品，则年净收益为20万元。3. 单击“粘贴函数”按钮，弹出“粘贴函数”对话框（如图11-2所示）。44图 11-2 粘贴 IF 函数4. 在“函数分类”列表框中单击选中“逻辑”，在“函数名”列表框中单击选中“IF”，单击“确定”，弹出“IF函数”框（如图11-3所示）。图 11-3 使用 IF 函数5. 在“Logical_test”编辑框中键入“产品=新产品”。6. 在“Value_if_true”编辑框中键入“180”，在“Value_if_false”编辑框中键入“20”，单击“确定”按钮。由于“产品”单元格中还没有数值，即不为“新产品”，所以B10单元格中显示数值“20”（如图11-4所示）。45图 11-4 逻辑函数计算结果将B10单元格中的公式复制到所有对应新产品畅销的单元格中。然后在对应现有产品畅销的单元格中输入逻辑计算公式。在熟悉IF函数以后，也可以直接在编辑栏中键入引用IF函数的公式，而不必使用“粘贴函数”按钮。操作步骤如下：1. 单击B11单元格。2. 在编辑栏中键入“=IF（产品=现有产品，180，20）”。3. 单击“输入”按钮。4. 将B11单元格中的公式
展开阅读全文