EXCEL财务实训手册

小程外贸布业岸施

EXCEL

财务管理实训手册

编写组袁东霞万顽钧

杨淑慧徐惮

山东外贸职业学院经济管理系

2013年2月

刖百

为便利老师的讲授和学生及自习者的学习，提高教学效果，构建以实力为

导向的人才培育模式，我们为财务管理课程配套了本实训手册。

微软的OFFICE是目前最为流行的办公套装软件，Excel是微软办公套装软

件的一个重要的组成部分，在Office2000中各个组件有着比较明确的分工：一般

说来，Word主要用来进行文本的输入、编辑、排版、打印等工作；Excel主要用

来进行有繁重计算任务的预算、财务、数据汇总等工作；PowerPoint主要用来制

作演示文稿和幻灯片及投影片等。本配套实训手册分为两大模块：EXCEL高级操

作模块与日常财务管理与分析模块。

实践中的繁琐而困难的财务管理工作，大部分可以通过EXCEL工具建立模

型并快速解决，但目前各职业院校没有针对EXCEL财务管理实际应用的特地教

材与特地培训，本配套实训教材针对这一空白，将财务管理原理融入到Excel应

用中，针对财务管理的每一章，都细心设计了EXCEL应用操作实训案例，并给

出了具体细致、图文并茂的实训操作的具体步骤，易学、易懂、易仿照，即使是

对Excel不是很熟识，也完全可以依据其具体操作步骤进行操作，得到一个可以

用于实际工作的EXCEL财务模型，职业性特征显著，利于学生职业素养的培育

和职业实力的养成。

通过本配套实训，使学生一方面进一步能够深刻领悟财务管理基本理论与方

法；另一方面能够以EXCEL为工具，驾驭利用EXCEL工具建立财务管理模型的

方法与技巧，把财务管理工作变得形象而可操作，从而培育学生的现代化工具应

用实力与实践操作实力，驾驭工作技巧，利于今后在职业生涯中高效完成繁琐而

困难的财务管理工作。

目录

项目一EXCEL高级操作..........................................................4

任务1：创建财务表格体系.....................................................4

任务2：制作生产记录表........................................................6

任务3：美化生产记录表（制作出令人赏心悦目的电子表格）........................7

任务4.制作销售记录表.........................................................8

任务5：计算产品销售额........................................................9

任务6制作特性的销售报表....................................................11

任务7：快速制作大量销售报表................................................16

任务8：汇总产品销售状况....................................................23

任务9：分析销售状况.........................................................28

任务10:创建销售图表........................................................32

任务11:管理仓库存货........................................................41

任务12：制作员工工资表.....................................................48

任务13：计算个人收入所得税.................................................52

任务14：评定销售业绩........................................................53

任务15：建立相关表格的联系.................................................57

任务16：爱护重要的财务数据.................................................59

任务17：打印评定表和工资表.................................................61

项目二：日常财务管理与分析......................................................62

子项目1货币时间价值与风险衡量............................................62

任务1单利的计算.......................................................62

任务2复利终值计算及资金变更图表.......................................63

任务3复利现值计算及资金变更图表......................................72

任务4终值函数应用.....................................................74

任务5现值函数应用....................................................76

任务6年金函数应用....................................................77

任务7贷款方式选择......................................................78

任务8期数计算函数应用................................................81

任务9利率计算函数应用................................................82

任务10风险的衡量.......................................................83

子项目2筹资决策.........................................................84

任务1设计债券发行价格模型...........................................84

任务2比较综合资本成本................................................85

任务3建立财务杠杆与资本结构分析模型.................................86

任务4创建资金结构分析图表............................................87

子项目3投资决策.........................................................88

任务1利用EXCEL内置折旧函数计算固定资产的折旧......................88

任务2利用EXCEL内置财务净现值函数进行项目决策.....................90

任务3利用EXCEL内置内含酬劳率函数进行项目决策.....................91

任务4快速计算短期证券收益率.......................................93

任务5利用EXCEL快速计算长期证券收益率..............................93

任务6建立债券估价模型..............................................94

子项目4营运资金管理......................................................95

任务1利用EXCEL制作应收账款透视表、透视图.........................95

任务2制作应收账款催款单.............................................102

任务3提取坏账准备...................................................105

任务4利用EXCEL进行最优订货批量决策................................107

子项目5财务分析...........................................................108

任务1企业日常费用分析...............................................108

任务2企业收支结构分析...............................................110

任务3利用EXCEL进行趋势分析.........................................111

任务4利用EXCEL进行比较分析.........................................113

任务5利用EXCEL绘制杜邦分析图并进行综合分析........................117

项目一EXCEL高级操作

任务1：创建财务表格体系

基本概念与基本学问介绍

一、主界面介绍

相对于word,EXCEL2003的标题栏、主菜单、工具栏相同，但内部窗口完全不

同，以下介绍一些基本概念。

1、工作簿：全部工作表的集合，一个EXCEL文件就是一个工作簿。

2、工作表：工作簿中的每一张表格，常称为电子表格，用于对数据的存放、

处理与分析。留意主界面左下角的工作表标签。

3、单元格：表格中的每一个小方格，单元格的引用通过指定其行列坐标实现,

格式：A5:B5;A8：E8

4、表元区域：单元格的集合，例如：A2:F8

5、活动单元格：目前正在操作的单元格。

二、工作簿的速立与保存

EXCEL中文件的保存与关闭都是针对工作簿而言的。

1、新建工作簿方法：1）启动时自动创建；

2）利用“文件—新建——新建工作簿”；

3）利用“新建”按钮；利用“文件—另存为”；

2、保存：利用“文件—保存”或者是“保存”按钮，首次保存将弹出保存

对话框，选择文件名称与存放位置进行保存。

三、工作表的操作

1、输入表标题、表头

2、表格内容的输入：单元格格式：常规、数字、日期、货币等

3、鼠标托放快速输入：指针指向单元格右下角，指针变十字时按鼠标左键向下托

放到所需位置。

4、重命名工作表

5、增减工作表

操作任务

1、新建公司常务、会计报表、资产管理、财务分析与预算四个工作簿，保存

在E盘的“财务体系”文件夹下。

2、打开“公司常务”工作建。

3、在''公司常务”中建5个工作表，Sheet1-Sheet5,方法：“插入”工作表、

“Ctrl”+工作表拖动复制

4、重命名“公司常务”中四个工作表名称分别为：生产记录表、销售记录表、

仓库存货管理、员工工资表。右键一重命名

5、将“产品生产记录”工作表标签改为“天蓝色”，其他工作表标签依据自

己的爱好设置颜色。

6、复制“仓库存货管理”、“销售记录表”到“财务分析与预算”工作簿中。

方法：“Ctrl”+仓库存货管理、销售记录表T“编辑”T“移动或复制工作表”

（同时选择建立副本）T财务分析与预算

留意：财务分析与预算工作表必需处于打开状态。

7、将Excel“保存自动复原信息，每隔：”设为10分钟，即让系统每隔10

分钟自动保存一次。

工具——选项-保存

选项⑵区

视图重新计算编辑常规-2-3的帮助自定义序列

图表『频色际保存型送检查』拼写检查安全隹

设置

0保存自动恢复信息，每隔⑤）：，.分钟®

自动恢复文件保存位置&）.C:\DocumentsandSettings\DY001'ApplicationDw1

工作海选项

口禁用自动恢复也）

任务2:制作生产记录表

学问点：单元格、十字光标拖曳

1.输入表名：银翎有限责任公司生产记录表

输入表头：编号、名称、生产数量、单位、单价、生产单位、生产日期、备注

2.输入编号拦：快速填充编号：SXOO1,采纳方法：十字光标拖曳；（用于连续输

入相同的字），ZH008,GPDDS同理；

3.输入名称栏：见“生产记录表”名称，采纳方法：十字光标拖曳；（用于连续输

入相同的字）：在B9中输入6098-65女童，B10中输入6098-70女童，选中B9和

B10十字光标拖曳，下同。

4.输入生产数量：单元格格式——先设置格式后输入数量（无小数，千分位分隔）

5.输入单价：先输入数值后设置格式（货币，小数2,¥）

6.输入单位：

（1）输入“条"，采纳方法：十字光标拖曳；（用于连续输入相同的字）

（2）输入“套”，采纳方法：十字光标拖曳；（用于连续输入相同的字）

7.输入生产日期：先输入后设置格式

CTRL+十字光标拖曳复制，右键拖曳+“复制单元格”

8.输入生产单位：利用记忆功能，即输入一个数据后，当再次在其他单元格中输

入曾输入过数据的第一个字符时，系统自动显示该数据，按ENTER即可。

9.调整行高与列宽：选中行号中的1-26行，点击右键在“行高”中设置输入为“16”

的高度，列宽通过鼠标放置列线上拉的方法将各列拉到适当的列宽。

10.将文件另存为“任务2”，在“任务2”文件中删除其他工作表

小窍门：双击鼠标左键快速输入到末尾，CTRL+;输入当前日期CTRL+SHIFT+;输

入当前时间；数字前加'变为文本型

相关学问：数据类型、工具一选项一编辑——记忆输入、按ENTER后移动

效果图：

ABCDEFG1H

1银翎有限责任公司产品生产记录

2编号名称生产致曼单tfc单价生产单位生尸日期备注

3SX0011856-S女稀24,500条¥56.50二车间一生产线2012年2月10日

4SX0011856-M女精24,800条¥56.00二车间二生产线2012年2月10日

5SX0011856-L女特20,300条¥60.50二车间二生产线2012年2月11日

6SX0011856-XL女裨14,500条¥60.50二车间一生产线2012年2月11日

7SX0011856-XXL女裤13,800条¥60.50二车间二生产线2012年2月12日

8SX0011856-XXXL女裤9,800条¥60.50二车同一生产线2012年2月12日

9ZH0086098-65女童8,700套¥25.00一车间二生产线2012年2月10日

10ZH0086098-70女童8,300套¥25.00一车间一生产线2012年2月10日

11ZH0086098-75女童9,200套¥25.00一车间二生产线2012年2月11日

12ZH0086098-80女童6,800套¥25.00一车间一生产线2012年2月11日

13ZH0086098-85女童5,900套¥25.00一车间二生产线2012年2月12日

14ZH0086098-90女童6,500套¥26.00一车间一生产线2012年2月12日

15ZH0086098-95女童12,300套¥26.00一车间一生产线2012年2月13日

16ZH0086098-100女童15,200套¥26.00一车间二生产线2012年2月13日

17ZH0086098-105女童16,300套¥26.00一车间i生产线2012年2月11日

18GPDDS3078-22牛仔梅11,500条¥73.00三车间二生产线2012年2月12日

19GPDDS3078-23牛仔拓10,500条¥73.00三车同一生产线2012年2月12日

20GPDDS3078-24牛仟神9,900条¥73.00三车间二生产线2012年2月13日

21GPDDS3078-25牛仔神6,700条¥73.00三车间一生产线2012年2月13日

22GPDDS3078-26牛仟祈9,600条¥73.00三车间二生产线2012年2月14日

23GPDDS3078-27牛仔福7,500条¥75.00三车同一生产线2012年2月14日

24GPDDS3078-28牛仔梅7.501条¥75.00三车间一生产线2012年2月15日

25GPDDS3078-29牛仔神7,502条¥75.00三车间一生产线2012年2月16日

26GPDDS3078-30牛仔播7,503条¥75.00三车同一生产线2012年2月17日

27GPDDS3078-31牛仔拓7,504条¥75.00三车间一生产线2012年2月18日

任务3:美化生产记录表（制作出令人赏心悦目的电子表格）

学问点：美化“公司常务”中的“产品生产记录表”

1.将标题合并并居中：选中需合并的单元格，按工具栏中的“合并及居中”按钮

（再按则清除合并及居中）

2.设置标题格式：字体（华文彩云）、字号（18）、调整行高（鼠标拖动置合适大

小）、填充颜色（填充颜色工具按钮，选褐色）、字体颜色（字体颜色工具按钮，

选白色）、加粗（B按钮）——除常用选项卡更改外，也可以用单元格格式更改

3.设置表头格式：选中A2：H2单元格区域，在“单元格格式”对话框中进行以下

更改：字体（隶书）、字号（16号）、颜色（深红）、对齐（“水平对齐”选居中；

“垂直对齐”也选居中）、图案（“单元格底纹”中选“淡蓝”色）——确定

4.数据区的文字格式和对齐格式：

选择各列的字体：CTRL选择“名称”、“生产日期”两列的数据区域——格式：字

体（方正细等线简体）；“编号”列（方正细圆简体）；“单位”与“生产单位”列

（楷体）

选择各列对齐方式：选择cE列——增加缩进量+右对齐

选择ABDFG列——选择“居中”按钮

5.为表格添加边框：选中A2：H27区域——单元格格式——边框：线条（选较细的

一种）、预置（选内边框）；线条（选较粗的一种）、预置（选外边框），达到内细

外粗的边框效果。

6.为数据区域配置底纹：选中A3:H27区域——单元格格式——图案一一颜色（选

象牙色）；在下面的“图案（P）”：下拉列表框中（选“细对角线剖面线”图案）；

同时在“图案（P）”：下拉列表框中选择天蓝色（此为图案颜色）。

效果图:

B1clDIEIF1G

2编号名称生产数量单位单价生产单位生产日期备*注_

3SXOO11856-S女神24,500条¥56.50二车间一生产线2012年2月1。日

4SXOO11856・M女神24,800徐¥56.00二车间二生产线2012《2月10日

5SXOO11856-L.文蕾20.300条¥60.50二车间二生产线2012年2月11日

6SXOO11856・XL女携14,500条¥60.50二车间一生产线2012年2月11日

7SXOO11856-XXL女莒13,800条¥60.50二车间二生产线2012年2月12日

8SXOO11856-XXXL发营9,800条¥60.50二车间一生产线2012年2月12日

9ZH0086098-65女t&700套¥25.00一车间二生产级2乳222用。日

10ZH0036098-70女簟8,300套¥25.00一车间一生产线2012年2月10日

11ZH00860g8-75女童9,200套¥25.00一车间二生产线2012年2月11日

12ZH0086098-80女童6,800套¥25.00一车间一生产线2012年2月11日

13ZH0036098-85女童5,900餐¥25.00一车间二生产线2012^2^120

14ZH0086098・90女童6,500套¥26.00一车间一生产线2伪2隼2月12日

15ZH0086098-95女童12,300套¥26.00i车间f生产线2012年2月13日

16ZH0086098-1004«15,200套¥26.00一车间二生产线201222213日

17ZH0086098-105女童16,300套¥26.00f车同一生产线2012年2月11日

18GPDDS3078-224仔精11,500杀¥73.00三车向二生产线2012母2月12日

家

19GPDDS3078-23牛仟雷10,500¥73.00三车间一生产线2012年2月12日

20GPODS3078・24牛仔看9,900条¥73.00三车间二生产线2012年2月13日

21GPDDS3078-25牛任售6.700条¥73.00三车间i生产戏201222月13日

22GPDDS3078-26牛仟神9.600条¥73.00三车间二生产线2012年2月14日

23GPDDS3078-27牛仔售7,500条¥75.00三车间一生产线2012^2^140

24GPODS3078・28牛仔相7,501杀¥75.00三车同一生产线2012^2^150

25GPODS2g牛仔箫7,502条¥75.00三车同一生产线2012年2月16日

26GPDDS3078-30牛仔群7,503条¥75.00三车间一生产线2012年2月17日

27GPDDS3078-31牛仔梅7,504条¥75.00三车间一生产线2012年2月18日

任务4制作销售记录表

学问点：驾驭复制其他工作表中数据——套用内置表格样式

1.录入产品销售记录数据：输入标题”销售记录表”

复制生产记录表中编号、名称两列数据（到22行即可），方法：右键（复制、

粘贴）或用CTRL+C与CTRL+V,——智能粘贴标记（选择匹配目标区域格式）

复制单位、单价两列数据，方法：复制—粘贴——选择性粘贴（数值）——

被粘贴的数据不带源格式，其中单价为不带¥货币类型

将“单价”拦的数据格式设为为货币形式但不带¥符号的数值，再录入其他

数据，在表头的“单价”和“销售额”后面输入“¥”——合并并居中标题区

2.套用表格格式：

选取A1：F22单元格区域——格式——自动套用格式——序列2——单击对

话框右侧的“选项”按纽——清除边框复选框——确定

3.修改套用的表格格式：

标头：字体颜色（黄色）、填充颜色（浅橙色）、字号（14号）、字体（华文

中宋）

“单位”列：居中对齐

“单价、销售量”两列：增加缩进量+右对齐

最终保存，以“任务4”为名进行另存，删除“任务4”文件中的其他工作表。

4.相关学问：单元格名称在默认的状况下都是用行号与列标定义的，也可以自定

义单元格区域名称——插入一名称一自定义，键入要定义的名称

作用：在大型工作表中可以快速跳转到该单元格中。方法：单击名称框右侧

的下拉按纽.

任务4效果图：

AIB1clD|EIF

1销售记录表

2编号名称单位单价（¥）销售量销售额（¥）

TSX0011856-S女裤56.508269

4SX0011856-M女箍

5SX0011856-L女裤60.508700

6SX0011856-XL女裤60.502300

7SX0011856-XXL女裤60.508600

8SX0011856-XXXL女裤60.505000

9ZH0086098-65女童25.0086700

10ZH0086098-70女童25.0094500

11ZH0086098-75女童25.0096000

12ZH0086098-80女童25.0054000

13ZH0086098-85女童25.0072200

14ZH0086098-90女童26.0094900

15ZH0086098-95女童26.0037000

16.ZH0086098-100女童26.0032400

17ZH0086098-105女童26.0086700

18GPDDS3078-22牛杼裤73.0032400

19GPDDS3078-23牛杼林73.006856

20GPDDS3078-24牛仔裤73.006589

21GPDDS3078-25牛仔裤73.005987

22GPDDS3078-26牛仔雉73.008000

任务5:计算产品销售额

学问点：驾驭编辑公式、填充公式、自动求和

打开“公司常务”中的“销售记录表”

1.插入折价率：销售额前面插入列一输入折价率——格式设置为百分比，小数位

数2——输入各种商品的折价率

2.计算销售额：先计算一种产品销售额，激活G3单元格，输入一个“="，点击

D3单元格，该单元格地址自动输入到G3单元格，点击*,依次录入使其为

（=D3*E3*F3）,（即销售额=单价*销售量*折价率），按ENTER键计算销售额——右

键拖曳（不带格式填充），计算出全部销售额。

3.计算销售总额：在A23输入“销售总额”——激活G19,单击“Z”进行SUM

求和——选取A23：G23单元格区域，单击格式刷复制与G20单元格相同格式的底

纹。

4.小窍门：编辑栏中会显示格式，也可以在单元格中显示公式——工具一选项一

视图一公式

5.公式的规则：

（1）运算次序：最前面是等号“="，后面是参加计算的元素（如D3,E3等）与运

算符（如*）。

（2）运算符类型（算术5个、比较6个、文本1个、引用2个）

①算术运算符：+,*（:乘），/（除）「（乘方）

例如，请尝试，在H3、I3分别尝试计算G3/E3,D3的3次方，然后分别右键

拖曳（不带格式填充），计算出全部H列、G列的相关数据。尝试完后清除这两列

的内容。

②比较运算符：=,>,<,>=,<=.◊（不等于）

用于比较两个值，得到的结果是一个逻辑值：TRUE或FALSE.

例如，请尝试：在H3、I3单元格分别尝试，H3=D3<F3,I3=D3>F3,比较计算结

果。尝试完后清除这两个单元格的内容。

③文本运算符：&（将两个文本值连接或串起来产生一个连续的文本值）

例如，请尝试：在H3单元格中尝试，H3=B3&B7,查看完运算结果后清除该单

元格内容。

④引用运算符：：（对包括引用在内的全部单元格进行引用，例如B5:B15）

,（英文状态下的逗号，是联合操作符，将多个引用合并为一个引

用，例如SUM(B5：B15,D5：D15)

6.相对引用和肯定引用：

(1)相对引用：依据源公式单元格相对位置引用，例如G3单元格的公式复制到

G4单元格，公式中引用单元格的地址就相应变成“二D4*E4*F4"，通常在公式中对

单元格的引用都是相对引用。

(2)肯定引用：单元格地址保持不变，因此计算的结果也不变，方法，在单元格

列标与行号之前分别添加$符号。例如"H3=$D$3*$E$3*$F$3"，计算出结果后快速

填充到其他单元格，查看计算结果。

(3)混合引用：在同一个公式中同时运用相对引用与肯定引用。

(4)在已创建的公式中，F4可以在单元格肯定和相对引用之间切换。

B1clD

销售记录表

SX0011856-S女裤56.50826986.00%401790.71

SX0011856-M女樨56.00588085.00%279888.00

SX0011856-L女裤60.50870092.00%484242.00

SX0011856-XL女裤60.50230088.00%122452.00

SX0011856-XXL女裤60.50860086.00%447458.00

SX0011856-XXXL女裤60.50500092.00%278300.00

ZH0086098-65女童25.008670095.00%2059125.00

ZH0086098-70女童25.009450093.00%2197125.00

ZH0086098-75女童25.009600085.00%2040000.00

ZH0086098-80女童25.005400080.00%1080000.00

ZH0086098-85女童25.007220085.00%1534250.00

ZH0086098-90女童26.009490088.00%2171312.00

ZH0086098-95女童26.003700092.00%885040.00

ZH0086098-100女童26.003240090.00%758160.00

ZH0086098-105女童26.008670087.00%1961154.00

GPDDS3078-22牛仔裤73.003240084.00%1986768.00

GPDDS3078-23牛仔裤73.00685681.00%405395.28

GPDDS3078-24牛仔裤73.00658978.00%375177.66

GPDDS3078-25牛仔裤73.00598775.00%327788.25

GPDDS3078-26牛存裤73.00800085.00%496400.00

销售总额20291825.90

任务6制作特性的销售报表

学问点：到账百分比计算、添加背景、创建艺术字

设置工作表背景与创建艺术字，编辑艺术字。

1.录入与运算销售数据：

新建工作簿——命名为销售报表——在“sheet」’中录入销售记录

在D3中输入公式“=C3/B3”计算到账百分比——设置百分比，小数两位——左键

拖曳计算到账百分比

ABCD

1——月份产品批发销售报表

2产品名称签单额到账额到账百分比

31856-S女裤383763.82364575.6395.00%

41856-M女裤166600.00143276.0086.00%

51856-L女裤326167.60303335.8793.00%

61856-XL女裤189534.40181194.8995.60%

71S56-XXL女裤237256.80221835.1193.50%

S1856-XXXL女裤75474.9662380.0582.65%

96098-65女童29830.0025722.4186.23%

10609ST0女童75702.0072916.1796.32%

116098-75女童96963.758301