管理数据分析

管理数据分析实验教程

第1章 财务会计模型 孙梦楠

会计为管理者提供信息，会计的目标是帮助管理者制定经济活动的决策。

1.1 会计凭证概述

会计要真实、准确、及时、完整地反映各单位的经济活动，就必须按照一定的格式填制会计凭证，并按照严格的程序审核、处理会计凭证。

1.1.1 会计凭证

会计凭证是记录经济业务、明确经济责任的书面证明，是登记账簿的重要依据。 会计凭证一般分为原始凭证和记账凭证两类，在实际工作中一般采用通用记账凭证格式。

1.1.2 记账凭证的填制

记账凭证填制的程序是：首先审核原始凭证；其次，根据原始凭证或原始凭证汇总表中所记录的经济业务内容，按照会计制度的规定，运用复式记账原理，确定应借、应贷的会计科目和金额；再次，填制记账凭证；最后，将填制好的记账凭证交由审核人员进行审核，审核通过后作为登记账簿的依据。

1.2 上机一 财务会计模型（一）

一、实验目的

1．掌握记账凭证模板的修改方法 2．学习记账凭证的填制方法

二、实验内容

1．记账凭证模板的修改：自动生成合计；函数校验；单元格内容的保护；保存记账凭证模板

2．记账凭证的填制：复制工作表；填制经济业务的会计分录

三、操作指导

1．记账凭证模板的修改

Excel的模板是一个含有选定内容和格式的工作簿，可以将常用的文本、数据公式以及格式、规则等，事先设置好加以保存，在需要时调用该模板，以方便数据的输入，确保数据的一致性。因此，模板在会计中被广泛应用于制作记账凭证、单据、会计报表等方面。

1

管理数据分析实验教程

（1）记账凭证模板的修改

启动Excel，打开记账凭证文件，如图1-1所示。

图1-1 记账凭证文件

? 自动求和 K10单元格的借方金额合计数可以自动生成。方法是选中该单元格，单击“自动求和”按钮∑，再拖动鼠标选择K6:L9回车即可。同理，M10单元格的贷方金额合计数也可以自动生成。

? 条件函数 记账凭证中的数据是登记账簿的直接依据，如果发生错误，会造成会计账簿数据、会计报表数据的错误，进而可能会给会计信息使用者带来决策的失误。如记账凭证的同一行不能同时有数、借方金额合计必须等于贷方金额合计等。为此，可以利用Excel提供的函数进行校验。先分别将单元区域C13:M13、C14:M14、C15:M15、C16:M16、C17:M17合并居中，然后分别在这些区域中输入如下函数，

注意：输入函数时，引号、括号必须在半角状态下输入。 （2）单元格内容的保护

为了防止模板中有些单元格的内容（如文本、公式等）、格式被破坏，可利用Excel的保护功能。具体方法是，在保存被修改的模板之前，先选定不需要保护的单元区域，如图1-1所示。

注意：不相邻的单元区域Ctrl键选择，图1-1中深色区为不保护单元区域。

在“格式”菜单中的“单元格格式”对话框中，单击“锁定”前的复选框，撤消其选定，

2

管理数据分析实验教程

并单击“确定”按钮。接着单击“工具”菜单中的“保护”，选择“保护工作表”，单击“确定”按钮（如图1-2所示），这样就设定了相关的单元格内容的保护。

图1-2 保护工作表

保护后的数据单元格为只读属性，不允许进行修改，如图1-3所示。

图1-3 被保护单元格为只读属性

（3）保存记账凭证模板

在“文件”菜单中选择“另存为”命令，在“另存为”对话框中的“文件名”处输入“记账凭证模板”，单击“保存类型”框右边的下拉箭头，并从弹出的下拉列表中选择“模板”类型，选择保存位置后单击“保存”按钮（如图1-4所示），生成后的模板格式可以进行修改。

3

管理数据分析实验教程

图1-4 保存记账凭证模板文件

注意：确定模板文件的保存位置 2．记账凭证的填制

由于事先设置好了记账凭证的模板，当要填制记账凭证时，单击“文件”菜单中的“新建”命令，弹出“新建”对话框，从中选取“记账凭证模板”，如图1-5所示。

图1-5 利用模板新建记账凭证文件

根据业务所需填制记账凭证。假设某单位2011年11月份发生的经济业务的会计分录Ⅰ如下：

(1) 11月1日，厂办王浩报销办公用品70.00元，以现金支付。 借：管理费用 70.00

贷：现金 70.00

4

管理数据分析实验教程

(2) 11月5日，用银行存款支付所欠A企业贷款30 000.00元。 借：应付账款——A企业 30 000.00

贷：银行存款 30 000.00

(3) 11月6日，用现金支付采购员张三差旅费借款300.00元。 借：其他应收款——张三 300.00

贷：现金 300.00

(4) 11月8日，向A企业此案够甲材料200件，每件70.00元，计14 000.00元，乙材料50件，每件20.00元，计1 000.00元；向B企业采购丙材料100件，每件50.00元，及5 000.00元。材料均已验收入库但货款未付。

借：原材料——甲材料 14 000.00 ——乙材料 1 000.00 ——丙材料 5 000.00

贷：应付账款——A企业 15 000.00 ——B企业 5 000.00

(5) 11月12日，车间领用甲材料100件，计7 000.00元；领用丙材料50件，计5 000.00元。

借：生产成本 12 000.00

贷：原材料——甲材料 7 000.00 ——丙材料 5 000.00

(6) 11月15日，以银行存款支付所欠A企业贷款15 000.00元，所欠B企业贷款5 000.00元。

借：应付账款——A企业 15 000.00 ——B企业 5 000.00

贷：银行存款 20 000.00

(7) 11月20日，从银行提取现金10 000.00元，备发工资。 借：现金 10 000.00

贷：银行存款 10 000.00 (8) 11月20日，用现金支付职工工资10 000.00元。 借：应付工资 10 000.00

贷：现金 10 000.00 (9) 11月30日，结转本月损益。 借：本年利润 70.00

贷：管理费用 70.00 会计以借贷记账法记录交易。 ? “制单人”中填写自己的名字

5

管理数据分析实验教程

? 多张记账凭证可通过复制工作表（以编号1-10命名）来实现

按业务发生的情况输入凭证的内容，所有记账凭证填制完成后，单击工具栏中的“保存”按钮，Excel文件名为“上机一”，记账凭证样本如图1-6所示。

图1-6 记账凭证样本

注意：第4笔经济业务涉及的会计科目超过记账凭证模板的行数，需要分两张记账凭证来记录该项业务。

四、实验要求

1．根据图1-1完成通用记账凭证模板的修改，文件为“上机一.xlt”； 2．按照会计分录Ⅰ填制9笔经济业务的记账凭证，文件为“上机一.xls”； 完成后通过作业提交软件上交作业“上机一.xls”至服务器上

五、课外作业

1．设计出差旅费报告单的格式

（1）按表1-1进行电子表格的设计：合并单元格；表格列宽为5.50，A:B列宽为2.75 ；O8:O9需自动换行；6-9行的行高为26像素；绘制表格边框和网格。

（2）对A6:C7和M6:O7的单元格，在“单元格格式”中，选择“分散对齐（缩进）” ；设置N3:Q3日期格式为“2001年3月14日”，并选择“分散对齐（缩进）”且缩进1字符。

（3）建立下拉式列表，对N4:Q4执行“数据” →“有效性”，“设置”有效性条件为允许→“序列”、来源→“总经理,副总经理,经理,副经理,主任”，如图1-7所示。

6

管理数据分析实验教程

图1-7 数据有效性设置

（4）选取A1:Q18，设置打印区域。

表1-1 出差旅费报告单

2．完成后上交作业“上机一课外”至服务器上。

六、课后预习

上机二 财务会计模型（二）

7

管理数据分析实验教程

1.3工资表的建立

工资数据对很多事情都很重要。应付员工的工资数额都保存在员工资料里，人力资源部门使用这些数据处理工资信息。 1.3.1 工资表结构

通常情况下，一个工资表包括编号、姓名、部门、基本工资、岗位工资、奖金、应发合计、水电费、代扣税、扣款合计、实发工资等项目。 1.3.2 所得税的计算方法

按照个人所得税法的有关规定，个人所得税适用超额累进税率。 每月应纳所得税额=每月应纳税所得额×适用税率-速算扣除数 每月应纳税所得额=月工资、薪金所得-800元

应纳税所得额、适用税率和速算扣除数可从表1-1中对应查询。

表1-2 个人所得税税率表

全月应纳税所得额J4-800 不超过500元的 超过500-2000元的 超过2000-5000元的 超过5000-20000元的 超过20000-40000元的 超过40000-60000元的 超过60000-80000元的 超过80000-100000元的 超过100000元的 注意：个人所得税的现行政策。

税率（%） 5 10 15 20 25 30 35 40 45 速算扣除数 0 25 125 375 1375 3375 6375 10375 15375 1.4 上机二 财务会计模型（二）

一、实验目的

1．掌握工资表的结构建立和数据输入 2．学习IF函数和条件格式的应用

二、实验内容

1．工资表结构的建立和数据输入：IF函数的格式和含义。 2．工资表格式的设置：设置条件格式；表格格式化。

8

管理数据分析实验教程

三、操作指导

某单位的工资表如表1-3所示。

表1-3 工资表数据

1．工资表结构的建立和数据输入

打开Excel，在新建工作簿中的工作表Sheet1中A3单元格开始输入工资表的第一行列名：编号、姓名、部门、基本工资、岗位工资、奖金、应发合计、水电费、代扣税、扣款合计、实发工资、签名。

按图2-1中的数据，输入编号、姓名、部门、基本工资、岗位工资、奖金、水电费七列数据。

（1）计算应发合计 应发合计=基本工资+岗位工资+奖金

G4=SUM(D4:F4)，G4以下的单元格都可以利用向下拖动的方法进行复制，并且所引用的数据位置也会相应调整，不用重复输入公式。 ? 求和函数SUM(number1,number2,···) （2）设置代扣税项目的计算公式

在设置计算公式时，需要用到条件函数IF（）

? 条件函数IF(logical_text,value_if_true,value_if_false)

IF函数的具体格式为“=IF（条件，真值，假值）”，这里的“条件”是一个逻辑表达式，“真值”和“假值”都是数值或逻辑表达式。当条件成立时，结果取第二项——“真值”，否则结果取第三项——“假值”。

一般单位个人的工资不会高得太多，我们可根据人员的实际工资水平减少函数的嵌套数目简化运算。

I4=IF((G4-800)<0,0,IF((G4-800)<500,5%*(G4-800),IF((G4-800)<2000,10%*(G4-800)-25,IF((G4-800)<5000,15%*(G4-800)-125)))

最后利用自动填充功能快速填充第I列，如图1-8。 （3）计算扣款合计 扣款合计=水电费+代扣税

J4=SUM(H4:I4)，J4以下的单元格可以利用向下拖动的方法进行快速复制。 （4）设置实发工资的计算公式。实发工资=应发合计-扣款合计 K4=G4-J4，K4以下单元格的公式利用自动填充功能求出。

9

管理数据分析实验教程

建立好某个月的工资表后，下一个月表中的很多基本数据不变，可以直接利用。

图1-8 条件函数的应用

2．工资表格式的设置 ?

条件格式：当单元格的值满足条件或公式返回的值为 TRUE 时，选择要应用的格式。

可以指定至多三个条件。如果指定条件中没有一个为真，则单元格将保持已有的格式。 （1）条件格式：当“实发工资”≥1,500元时，按红底黄字格式显示，如图1-9所示；

图1-9 条件格式的设置

（2）设置字体、字号、对齐方式、表格框线

“应发合计”列加粗突出；设D4:F13，H4:J13字体为蓝色；“制表人”中填写自己的名字。

完成后的工资表如图1-10 “工资表样本”所示。

图1-10 工资表样本

10

管理数据分析实验教程

四、实验要求

1．按表1-3制成“工资表”，参照工资表样本，计算相关项目，设置表格格式。 2．完成后上交作业“上机二”到服务器上。

五、课外作业

1．创建一个“清屏”的记录宏

如果经常在 Microsoft Excel 中重复某项任务，那么可以用宏自动执行该任务。宏是一系列命令和函数，存储于 Visual Basic 模块中，并且在需要执行该项任务时可随时运行。 （1）录制新宏：在图1-10中，选择“工具” →“宏” →“录制新宏”，选中F4：F13，H4：H13；单击“Del”键；单击D4单元格，“停止录制”。

（2）执行宏：通过“工具” →“宏”，执行“宏”，选择录制的宏并执行宏命令（执行前先保存，执行后恢复数据需先不保存退出，再进入，否则执行后数据删除无法撤消）。

如果希望通过单击特定按钮或按下特定组合键来运行宏，可将宏指定给某个工具栏按钮、键盘快捷键或工作表中的图形对象。

（3）创建窗体按钮：选择“视图” →“工具栏” →“窗体”，在窗体工具栏中选择窗体按钮，用来指定宏，命名为“清屏”。

完成后的工资表如图1-11“工资表模板样本”所示。

图1-11 工资表模板样本

2．最后上交作业“上机二课外”到服务器上。

六、课后复习

Excel在财务会计中的应用。

第2章 筹资与投资决策模型

筹资与投资决策是企业财务管理的重要内容。

11

管理数据分析实验教程

2.1 筹资决策概述

企业筹集资金就是企业根据其生产经营、对外投资和调整资本结构的需要，通过筹资渠道和资金市场，运用筹资方式，经济有效地筹措和集中资金。企业的资金筹集方式有股票、债券、银行借款、租赁、商业信用、商业票据等。

利用Excel提供的函数和计算功能，可以建立有关筹资方式的各种模型，通过对比分析，选择合理的筹资方案。

2.1.1 资金的时间价值及函数

从投资者角度看，增值特性使资金具有时间价值；从消费者角度看，时间价值体现为对放弃现期消费的损失所就做的补偿。 1．资金的时间价值的概念与计算公式 （1）复利终值

复利终值又称未来值，是指现在的货币经过若干年后的本利。其计算公式为：

n FV?PV?(1?R)其中，FV为第n年之后的终值；PV为期初数额或现值；R为利率，一般为年利率；n为时间周期数，一般为年数。 （2）复利现值

复利现值是指未来某一时期一定数额的货币折合成现在的价值。其计算公式为：

PV?FV?1n??1?R

其中，FV为第n年之后的终值；PV为期初数额或现值；R为利率，一般为年利率；n为时间周期数，一般为年数。 （3）年金终值

年金是指定期、等额的系列收支。年金按复利计算，于若干期期末后可得的本利和称为年金终值。

普通年金终值：每次收支发生在每期期末，其计算公式为：

n??1?R?1 FVA?A?R其中：R为年利率；n为年金周期数；A为年金，即每年固定支付或收入的数额。 先付年金终值：每次收支发生在每期期初，其计算公式为：

n?1???1?R?1? FVA?A??1??R??（4）年金现值

普通年金现值：在每期期末取得相等金额，现在需要投入的金额，称为年金现值。若每年复利一次，其计算公式为：

12

管理数据分析实验教程

?? 1?1?RPVA?A?R?n先付年金现值：在每期期初取得相等金额，现在需要投入的金额，称为先付年金现值，若每年复利一次，其计算公式为：

??n?1?????1?1?R PVA?A??1??R??（5）年金的计算

RR

A?FVA?n或A?PVA??n?????1?R11?1?R（6）利率、期数的计算

根据年金现值、年金终值公式进行推导，求出现值函数或终值系数，然后查表，便可求出利率和期数。

2．资金时间价值函数 （1）年金终值函数FV()

格式：FV(rate, nper, pmt, pv, type)

功能：在已知期数、利率及每期付款金额的条件下，返回年金终值数额。 说明：rate为各期利率，是一固定值。

nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。

pmt为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。如果忽略pmt，则必须包括pv参数。

pv为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金。如果省略 PV，则假设其值为零，并且必须包括pmt参数。 type为数字0或1，用以指定各期的付款时间是在期初还是期末。0表示在期末，1表示在期初。如果省略type，则假设其值为零。

注意：应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。

在所有参数中，支出的款项表示为负数；收入的款项表示为正数。

（2）年金现值函数PV()

格式：PV(rate, nper, pmt, fv, type)

功能：在已知期数、利率及每期付款金额的条件下，返回年金现值数额。

说明：rate、nper、pmt、fv、type等各参数含义及要求同上。

（3）年金函数PMT()

格式：PMT(rate, nper, pv, fv, type)

功能：在已知期数、利率及现值或终值的条件下，返回年金数额。

13

管理数据分析实验教程

说明：rate、nper、pmt、fv、type、pv等各参数含义及要求同上。 （4）年金中的利息函数IPMT() 格式：IPMT(rate, per, nper, pv, fv)

功能：在已知期数、利率及现值或终值的条件下，返回年金处理的每期固定付款每期所含的利息。

说明：rate、nper、fv、type等各参数含义及要求同上。 per用于计算其利息数额的期次，必须在1至nper之间。

pv为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金。 （5）年金中的本金函数PPMT() 格式：PPMT(rate, per, nper, pv, fv, type)

功能：在已知期数、利率及现值或终值的条件下，返回年金处理的每期固定付款所含的本金。

说明：rate、per、nper、pv、fv、type等各参数含义及要求同上。

（6）计息期数函数NPER() 格式：NPER(rate, pmt, pv, fv, type)

功能：返回每期付款金额及利率固定的某项投资或贷款的期数。

说明：rate、pmt、pv、fv、type等各参数含义及要求同上。

（7）利率函数RATE()

格式：RATE(nper, pmt, pv, fv, type, guess)

功能：在已知期数、期付款金额及现值的情况下，返回年金的每期利率。 说明：nper、pmt、pv、fv、type等各参数含义及要求同上。

guess为预期利率（估计值）。如果省略预期利率，则假设该值为10%。 2.1.2 长期借款筹资分析模型

长期借款是指企事业单位向银行等金融机构及其他单位借入的期限在一年以上的各种借款，主要用于小额的固定资产投资和流动资产的长期占用。

建立长期借款分析模型就是利用Excel提供的筹资函数和工具，对贷款金额、贷款利率、贷款期限和归还期等因素进行多种测算，在多种方案中选择一种比较合理的贷款方案。 1．长期借款分析基本模型

可以通过建立长期借款分析基本模型，判断哪一种还款方式对企业最为合理。 2．长期贷款模拟运算表分析模型

在实际工作中，长期借款中的各项因素，如本金、利率、还款期限等，是相互影响的。借款期限的长短会影响利率的高低，本金的多少会影响支付利息的多少，而其中每一项因素的变化都最终会对长期借款决策产生一定的影响。单因素变动对借款决策的影响，分析起来较为容易，如果是两个因素同时变化，分析起来就会有一定的难度，而这种情况又是会经常

14

管理数据分析实验教程

发生的。为此，Excel专门提供了模拟运算表工具，用来解决这一问题。

模拟运算表是Excel工作表中的一个单元格区域，用以显示公式中某些值的变化对计算结果的影响。

（1）长期借款单变量模拟运算表分析模型

单变量运算表为用户提供查看某个变化因此数值改变时对一个或多个公式先进技术的影响。

在分析借款决策中如果只有一个变量，并且财务管理人员也能根据相关资料或经验测算出变动值，则这时可以利用Excel的单变量模拟运算表工具来帮助分析决策。 （2）长期借款双变量模拟运算表分析模型

双变量模拟运算表为用户提供查看两个变化因素同时改变数值时对一个或多个公式结果的影响。

在长期借款分析决策中，如果是两个因素同时变化，如借款期限有长有短、利率有高有低，这时，在分析这些因素对最终决策的影响时，必须使用Excel的双变量模拟运算表。

2.2 上机三 筹资与投资决策模型（一）

一、实验目的

1．掌握长期借款筹资分析模型的建立 2．学习模拟运算表工具的使用

二、实验内容

1．建立长期借款分析基本模型：长期贷款中的四种还款方式中，判断总付款最少的还款方式。

2．长期借款模拟运算表分析模型：利用模拟运算表工具，分析在利率、借款期限发生变化的情况下，企业每期偿还金额的变化情况。

三、操作指导

1．建立长期借款分析基本模型

例：某企业拟向银行申请长期贷款，用于购买机器设备。借款金额为80000元，银行贷款年利率为5%，借款期限为4年，有四种还款方式可供选择： 第一种：每年年末偿还本金20000元和所欠利息； 第二种：每年年末只付利息，本金到第4年末一次还清； 第三种：每年年末偿还相等金额；

第四种：第4年末一次全部偿还贷款本金及利息。

可以通过建立长期借款分析基本分析模型，判断上述哪一种还款方式对企业最为合理。

15

本文来源：https://www.bwwdw.com/article/y5uf.html