整理excel与财务管理.docx
《整理excel与财务管理.docx》由会员分享，可在线阅读，更多相关《整理excel与财务管理.docx（48页珍藏版）》请在冰豆网上搜索。
整理excel与财务管理
第四篇Excel与财务管理
第10章货币时间价值
教学目标
1、掌握货币时间价值的含义，了解有关计算公式。
2、能熟练运用货币时间价值函数进行有关计算。
10.1货币时间价值概念与计算
10.1.1货币时间价值概念
货币时间价值的计算，在财务管理中有着广泛的用途，如长期投资决策、租赁决策、养老金决策、资产和负债估价等方面。
随着财务问题的日益复杂化，时间价值观念的应用也将日益广泛。
资金时间价值是指一定量资金在不同时点上的价值量的差额。
由于资金时间价值的存在，不同时点的等量货币具有不同的价值。
资金时间价值存在的前提是商品经济的高度发展和借贷关系的普遍存在。
从定性方面看，资金时间价值是一种客观经济现象，是资金循环和周转而产生的增值；从定量方面看，资金时间价值是在没有风险和没有通胀条件下的社会平均资金利润率。
在货币时间价值计算中涉及到终值和现值两个概念。
终值是现在一定量资金在未来某一时点上的价值，俗称本利和。
现值是指未来某一时点上的一定量资金折合为现在的价值。
终值现值两者关系为：
终值＝现值＋（时间价值）利息。
在计算中经常使用的符号及其含义为：
P—本金，又称现值；
I—利息；
i—利率，指利息与本金之比；
F—本金和利息之和，又称本利和或终值；
t—时间，通常以年为单位。
10.1.2 单利终值与现值的计算
单利是一种不论时间长短，仅按本金计算利息，其所生利息不加入本金重复计算利息的方法。
单利计息方式下，利息的计算公式为：
I=P·i·n
单利计息方式下，终值的计算公式为：
F=P·（1+i·n）
单利计息方式下，现值的计算公式为：
P=F/（1+i·n）
单利现值与单利终值互为逆运算。
10.1.3复利终值与现值的计算
货币时间价值通常是按复利计算。
复利是指在一定时间内按一定利率将本金所生利息加入本金再计算利息，也就是通常说的“利滚利”。
复利终值的计算公式为：
F=P·（1+i）^n。
公式中（1+i）^n称作"复利终值系数"，记为（F/P,i,n），可通过查表取得。
公式可记为F=P·（F/P,i,n）。
复利现值的计算公式为：
P=F·（1+i）^-n。
公式中（1+i）^-n称作"复利现值系数"，记为（P/F,i,n），可通过查表取得。
公式可记为F=P·（P/F,i,n）。
如果每年复利m次，则每年的利率为i/m，时间周期数为m*n，此时复利终值公式为：
F＝P*（1+i/m）^m*n。
复利终值系数与复利现值系数互为倒数。
10.1.4 普通年金终值与现值的计算
年金是指一定时期内间隔相等、连续等额收付的系列款项。
年金按其每次收付款项发生的时点不同，分为普通年金、即付年金、递延年金、永续年金等类型。
普通年金是指从第一期起，在一定时期内每期期末等额收付的系列款项，又称为后付年金。
普通年金终值的计算公式为：
F=A·（1+i）^n-1/I。
公式中，分式[（1+i）^n-1]/i称作"年金终值系数"，记为（F/A,i,n），可通过查表取得。
公式可记为F=A·（F/A,i,n）。
普通年金现值的计算公式为：
P=A·1-（1+i）^-n/i。
公式中，分式1-（1+i）^-n/i称作"年金现值系数"，记为（P/A,i,n），可通过查表取得。
公式可记为P=A·（P/A,i,n）。
10.1.5 即付年金终值与现值的计算
即付年金是指从第一期起，在一定时期内每期期初等额收付的系列款项，又称先付年金。
即付年金终值的计算公式：
F=A·［（1＋i）^n+1-1/i-1］=A·［（F/A，i，n+1）-1］。
公式中，［（F/A，i，n+1）-1］称作"即付年金终值系数"，它相当于在同期普通年金终值系数的基础上，期数加1，系数减1，可通过查表取得。
即付年金终值的另一个计算公式：
F=A·（F/A，i，n）·（1+i），对这个公式理解：
即付年金终值等于同期普通年金终值的基础上乘以（1+i）。
即付年金现值的计算公式：
P=A·［（P/A，i，n-1）+1］。
公式中，［（P/A，i，n-1）+1］称作"即付年金现值系数"，它相当于在同期普通年金现值系数的基础上，期数减1，系数加1，可通过查表取得。
即付年金现值的另一个计算公式：
P=A·（P/A，i，n）·（1+i），对这个公式理解：
即付年金现值等于同期普通年金现值的基础上乘以（1+i）。
10.1.6永续年金现值的计算
如果年金定期等额收付一直持续到永远，称为永续年金。
永续年金现值的计算公式为：
P=每期的等额收付金额/利率=A/i。
永续年金没有终值。
10.1.7年金的计算
根据年金现值公式或年金终值公式进行推导来计算年金。
10.1.8利率、期数的计算
根据年金现值公式、年金终值公式进行推导，求出现值系数、终值系数后，查表即可算出利率和期数。
10.2货币时间价值函数
10.2.1复利终值函数
复利终值有普通复利终值、普通年金终值和即付年金终值等形式。
复利终值函数名为FV。
复利终值函数用途是基于固定利率，返回某项投资的未来值。
复利终值函数语法为：
FV（rate，nper，pmt，pv，type）。
各参数含义为：
rate为各期利率；nper为总投资期；pmt代表各期支出金额，在整个投资期内不变，若该参数为0或省略，则函数值为普通复利终值；Pv为现值，也称为本金；Type取值为数字0或1，当Type取值为0或忽略时，表示收付款时间是期末，当Type取值为1时，表示收付款时间是期初。
注意：
在pmt不为0，pv=0，type＝1时、函数值为即付年金终值。
在Excel中，对函数涉及金额的参数，是有特别规定的，支出的款项用负数表示：
收入的款项用正数表示。
（1）普通复利终值的计算
【例10-1】某人将10000元投资于一项事业，年报酬率为6％，3年后的复利终值为：
FV（6％，3，0，-10000，0）＝11910（元）。
操作如图10-1所示。
（2）普通年金终值的计算
【例10-2】某人每年年末存入银行10000元、年利率为10％，计算第3年年末可以从银行取得的本利和为：
FV（10％，3，-10000，0，0）＝33100（元）。
操作如图10-2所示。
（3）即付年金终值的计算。
仍以上例为例，若款项每年年初存入银行，即付年金终值为：
FV（10％，3，-10000，0，1）＝36410（元）。
操作如图10-3所示。
10.2.2复利现值函数
复利现值与复利终值是一对对称的概念，复利现值包括普通复利现值、普通年金现值和即付年金现值。
复利现值函数名为PV。
复利现值函数用途是基于固定利率，返回投资的现值。
复利现值函数语法为：
PV（rate，nper，pmt，fv，type）。
（1）普通复利现值的计算。
【例10-3】某人拟在5年后获得本利和10000元，投资报酬率为10％，他现在应投入的金额为：
PV（10％，5，0，10000，0）＝-6209（元）。
操作如图10-4所示。
（2）普通年金现值的计算
【例10-4】某人要购买一项养老保险，购买成本为60000元，该保险可以在20年内于每月末回报500元、投资报酬率为8％，计算这笔投资是否值得。
PV（0.08／12，12×20，500，0，0）＝-59777（元）。
操作如图10-5所示。
由于养老保险的现值59777元小于实际支付的现值60000元，因此，这项投资不合算。
（3）即付年金现值的计算
【例10-5】用6年时间分期付款购物，每年年初预付200元。
设银行利率为10％，该项分期付款相当于一次现金交付的货款是多少？
PV（10％，6，200，0，1）＝-958（元）。
操作如图10-6所示。
10.1.3年金函数
年金函数名为：
PMT。
年金函数用途是基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。
年金函数语法为：
PMT（rate，nper，pmt，fv，type）。
【例10-6】若需要10个月付清的年利率为8%的10,000贷款，则每月支付额为：
PMT（8%/12,10,10000,0,0）=-1,037.03（元）。
操作如图10-7所示。
10.2.4年金中的利息函数
年金中的利息函数名为：
IPMT。
年金中的利息函数用途是基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。
年金中的利息函数语法为：
IPMT（rate,per,nper,pv,fv,type）。
【例10-7】某企业取得3年期，本金8,000元，年利率10%的银行贷款。
若按年支付贷款利息，则第三年支付贷款利息为：
IPMT（0.1,3,3,8000）=-292.45（元）。
操作如图10-8所示。
10.2.5年金中的本金函数
年金中的本金函数名为：
PPMT。
函数用途是基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。
函数语法为：
PPMT（rate,per,nper,pv,fv,type）。
【例10-8】某企业租用一台设备，设备租金36000元，年利率8％，每年年末支付租金，租期5年。
（1）每期支付租金＝PMT（8%,5,-36000）＝9016.43（元）。
操作如图10-9所示。
（2）第3年支付的本金＝PPMT（8%,3,5,-36000,0）＝7157.53（元）。
操作如图10-10所示。
（3）第3年支付的利息＝IPMT（8%,3,5,-36000）＝1858.90（元）。
操作如图10-11所示。
从上述数据中可以知道，PMT（）＝PPMT（）+IPMT（）。
10.2.6计息期数函数
计息期数函数名为：
NPER。
函数用途是基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。
函数语法为：
NPER（rate，pmt，pv，fv，type）。
【例10-9】A公司准备从B公司购买一台设备，B公司有两种销货方式供A公司选择：
一是现在一次全额付款90万元；二是分若干年每年初付款16万元。
假设资金成本为10％，如果A公司选择第二种付款方式，B公司在签定合同时可接受的收款次数至少为多少次，其收入才不低于一次性全额收款？
由于A公司和B公司一个属于付款，另一个属于收款，所以pmt和pv必需有一个用负数表示，则可接受的收款次数为：
NPER（10％,-160000,900000,0,1）＝7.51（次）。
因为收款次数应为正整数，并且不能小于7.51，所以收款次数至少为8次。
操作如图10-12所示。
10.2.7利率函数
利率函数名为：
RATE。
利率函数用途是返回年金的各期利率。
利率函数语法为：
RATE（nper，pmt，pv，fv，type，guess）。
【例10-10】某企业贷款30000元，租期为5年，每年年末支付租金，每年支付9000元。
则支付利率为：
RATE（5,9000,-30000,0,0）=15.24%。
操作如图10-13所示。
第11章流动资金管理模型设计
教学目标
1、掌握应收账款赊销策略的内容以及分析方法，学会用成本分析法和鲍曼模型做出最佳现金持有量的决策，可以运用不同的经济订货批量模型进行最优化订货批量决策
2、熟练掌握Excel下规划求解功能，学会利用Excel的数据计算和图表功能设计应收帐款赊销策略模型、最佳现金持有量决策模型和最优订货批量决策模型。
11.1应收账款赊销策略分析模型设计
应收账款是企业流动资产的一个重要项目。
随着商品经济的发展，商业信用的推行，企业应收账款数额明显增多，因此，它已成为流动资产管理中的一个日益重要的问题。
应收账款是指企业因销售产品、材料、提供劳务以及其他原因，应向购货单位或接受劳务单位以及其他单位收取的款项。
企业提供的商业信用，有赊销、分期收款等销售方式，它可以扩大销售，增加利润。
但是应收账款的增加，也会造成资金成本、坏帐损失等的增加。
应收账款管理的基本目标，就是在充分发挥应收账款功能的基础上，降低应收账款的成本，使商业信用扩大销售所增加的收益大于有关的各项成本。
11.1.1应收账款赊销策略
企业要管好应收账款，必须事先制定合理的信用政策，这主要包括信用标准、信用期间和现金折扣三个基本因素。
（1）信用标准
信用标准是客户获得交易信用应具备的条件，通常以预期的坏帐损失率作为判别标准。
如客户达不到该条件便不能享受企业的信用优惠，或只能享受较少的信用优惠。
客户的信用标准有以下五个方面组成，称为“5C系统”。
Ø客户品质
指客户的信誉，即客户过去履行偿还义务、遵守信用的品德。
如有无拖欠债务逾期不还，财务状况披露不实，或有无商业行为不良受到司法机关判处的案件等。
Ø偿债能力
企业是以客户流动资产的数量和质量，以及与流动负债的比例反映客户的偿债能力。
Ø资本
是指客户的财务实力和财务状况，表明客户可能偿还债务的背景，通常根据客户财务报表的数据进行分析。
Ø提供的抵押品
是指客户拒付款项或无力支付款项时用做抵押的财产，它对于信用期较长或信用状况有争议的顾客，尤其重要。
Ø经济情况
是指可以影响客户付款的客观经济环境的变化情况。
如市场供求关系的变化、市场购买能力的高低、经济形势好坏等都会对购货单位经营活动产生影响。
（2）信用期间
信用期间又称信用期，是企业允许顾客从购货到付款间的时间，或者是企业给予顾客的付款期限。
信用期过短不足以吸引顾客，在竞争中会使销售额下降；信用期过长对销售额增加固然有利，但是只顾销售增长的同时也会增加资金合同成本，收账费用和坏账损失。
因此，企业必须全面权衡，评价得失，从而确定其适用的信用期。
例如，若允许客户在购货后30天内付款，则信用期为30天。
（3）现金折扣
现金折扣是企业给客户在商品价格上的扣减。
其主要目的在于吸引客户提前付款，缩短企业的平均收款期。
现金折扣也能招揽一些客户，借此扩大销售量。
例如，（3/10，1/20，n/30）为现金折扣表达形式，它的含义是：
10天内付款可享受3%的价格优惠；10天后20天以内付款可享受1%的价格优惠；20天以后30天内付款的无价格优惠，而且付款的最后期限为30天。
11.1.2应收帐款赊销策略的增量分析法
赊销策略中每一种因素发生变化都会影响企业的利益。
探讨不同的赊销策略方案可能产生的财务效果，应首先测定每种因素的变化与经济效益变化之间的关系。
在制定赊销策略时，将各种相关因素予以一定程度的放宽或收紧，然后考虑企业销售收入和成本的相应变化，这种方法称为增量分析。
增量分析的结果为正数，则方案可行；否则不可行。
增量分析的基本公式如下：
（1）信用标准变化对利润的影响P
P＝新方案销售额增减量×销售利润率
（2）信用期间变化对应收账款机会成本的影响I
I＝（平均收账期增量/360×原方案销售额＋平均收账期/360×新方案销售额增量）×变动成本率×应收账款机会成本率
（3）信用标准变化对坏账成本的影响K
K＝销售额增量×增加销售额的坏账损失率
（4）现金折扣变化情况D
D＝（原方案销售额＋新方案销售额增量）×需付现金折扣的销售额占总销售额的百分比×现金折扣率
（5）赊销策略变化带来的净损益Pm
Pm＝P-I-K-D
判别准则：
若Pm>0，则方案可行；若Pm<0，则方案不可行。
11.1.3应收帐款赊销策略分析模型设计
【例11-1】某企业拟改变信用政策，现有两个可供选择的信用条件方案，有关资料如图11-1所示。
试确定企业应采用哪个方案。
操作步骤如下：
（1）输入数据公式。
在单元格B23中输入公式为：
“=B16*$B$7”，在单元格B24中输入公式为：
“=（（B20-$B$11）/360*$B$4+B20/360*B16）*$B$5*$B$12”，在单元格B25中输入公式为：
“=（$B$4+B16）*B18*B19”，在单元格B26中输入公式为：
“=B16*B17”，在单元格B27中输入公式为：
“=B23-B24-B25-B26”。
（2）把B23:
B27单元格的数据公式复制到C23:
C27单元格中，从而得出方案B各项目数据结果如图2-2所示。
通过对B27和C27单元格的数值结果对比，显然B方案的增量利润更大，因此，企业应该选择B方案的信用政策。
11.2最佳现金持有量决策模型设计
11.2.1成本分析模型
成本分析模式是通过分析持有现金的成本，寻找持有成本最低的现金持有量。
企业持有的现金，将会有三种成本：
（1）机会成本
现金作为企业的一项资金占用，是有代价的，这种代价就是它的机会成本。
假定某企业的资本成本为10%，年均持有50万元的现金，则该企业每年现金的成本为5万元（50*10%）。
现金持有额越大，机会成本越高。
企业为了经营业务，需要持有一定的现金，付出相应的机会成本代价是必要的，但现金持有量过多，机会成本代价大幅度上升，就不合算了。
（2）管理成本
企业拥有现金，会发生管理费用，如管理人员工资、安全措施费等。
这些费用是现金的管理成本。
管理成本是一种固定成本，与现金持有量之间无明显的比例关系。
（3）短缺成本
现金短缺成本，是因缺乏必要的现金，不能应付业务开支所需，而使企业蒙受损失或为此付出的代价。
现金的短缺成本随现金持有量的增加而下降，随现金持有量的减少而上升。
上述三项成本之和最小的现金持有量，就是最佳现金持有量。
最佳现金持有量的具体计算，可以先分别计算出各种方案的机会成本、管理成本、短缺成本之和，再从中选出总成本之和最低的。
【例11-2】某企业现有A、B、C、D四种现金持有方案，有关成本资料如表11-1所示。
表11-1现金持有方案成本资料
项目
A
B
C
D
平均现金持有量（元）
100000
200000
300000
400000
机会成本率
10%
10%
10%
10%
短缺成本
48000
25000
10000
5000
管理成本
40000
40000
40000
40000
根据表11-1资料，利用Excel工具，运用成本分析法编制该企业的最佳现金持有量测算表。
操作步骤如下。
（1）建立相关Excel新工作表，如图11-3所示。
图11-3中，B3=A3*0.1，B4=A4*0.1，B5=A5*0.1，B6=A6*0.1。
（2）进行汇总计算。
在E3中输入公式为：
=SUM（B3:
D4），将E3单元格的公式用“复制”和“粘贴”命令复制到E4:
E6单元格，从而得出所有汇总数据，如图11-4所示。
（3）生成相关图表。
按“ctrl”键同时选中A3:
A6和E3:
E6两个数据区域，点击“图表向导”，利用图表向导功能，选择柱状图表类型，并进行美化处理，可生成相关数据的柱状直观图，如图11-5所示。
通过图表分析比较各方案的总成本可知，C方案的相关总成本最低，因此企业平均持有300000元的现金时，各方面的总代价最低。
11.2.2鲍曼模型
鲍曼模型又称存货模型，它由美国经济学家威廉·鲍曼提出的。
他认为公司现金持有量在许多方面与存货相似，存货经济订货批量模型可用于确定目标现金持有量,并以此为出发点，创立了鲍曼模型。
鲍曼模型的着眼点也是现金相关成本之和最低，并且以以下假设为前提：
（1）企业所需要的现金可通过证券变现取得，且证券变现的不确定性很小；
（2）企业预算期内现金需要总量可以预测；（3）现金的支付过程比较稳定、波动性较小，而且每当现金余额为零时，均可通过部分证券变现得以弥补；（4）证券的利率或报酬率以及每次固定性交易费用可以获悉。
设T为一个周期内现金总需求量；F为每次转换有价证券的固定成本；Q为最佳现金持有量（每次证券变现的数量）；K为有价证券利息率（机会成本）；TC为现金管理总成本。
则，现金管理相关总成本=持有机会成本+转换成本，即：
式中Q／2——现金平均持有量；
T／Q——为一个周期内的现金转换次数；
最佳现金持有量就是货币资产余额总成本为最低点的资产余额。
应用数学求最大值（极值）的方法，令总成本公式的一阶导数为零，推导出最佳现金持有量的计算公式为：
将上式代入现金管理总成本公式得：
【例11-3】某企业现金收支状况比较稳定，预计全年（按360天）需要现金400万元，现金与有价证券的转换成本为每次400元，有价证券的年利率为8%，则企业的最佳现金持有量为多少？
操作步骤如下。
（1）建立相关Excel新工作表，如图11-6所示。
（2）数据结果计算。
在B5单元格中输入公式为：
=SQRT（2*B2*B3/B4）；在B6单元格中输入公式为：
=SQRT（2*B2*B3*B4）；在B7单元格中输入公式为：
=B2/B5，结果如图11-7所示。
其中SQRT（）函数为求括号内数值的平方根，例如，SQRT（2*8）=4，和（2*8）^0.5所求结果一致。
由计算结果可知，企业的最佳现金持有量为20万元，，这时的现金管理相关总成本为16000元，一年内变现的次数为20次。
11.3最优订货批量决策模型设计
11.3.1基本经济订货批量模型
一定量的存货是企业正常生产所必须的。
存货在流动资产中占据很大的比重，一般占30%~40%。
存货资金占用越低，存货资金的周转就越快。
但是不能没有存货，到底存货多少为最好呢？
因为存货的取得、保管都需要付出成本，同时缺少存货会影响生产，同样要付出成本，怎样才能确定最优的库存水平？
一种比较普遍应用的方法是经济订货批量法。
经济订货批量也称经济订货量，是指订购费用和保管费用的合计数为最低的订购批量。
经济订货量的基本模型是一种理想的市场状况，其基本假设为：
（1）企业能及时补充存货，即需要订货时即可立即取得存货；
（2）能够集中到货，而不是陆续到货；（3）不允许缺货，即无缺货成本；（4）存货单价不因订货批量的变化而变化。
如果用Q
表示存货的经济订货量，K表示存货的一次订货成本，D表示存货每年耗用量，Kc表示该存货的单位储存成本。
则经济订货量的基本模型为：
Q
=
【例11-4】某企业每年耗用甲材料1600吨，该材料的单价为1000元，单位储存成本为800元，一次订货成本为3600元，则
Q
=
=
=120（吨）
11.3.2经济订货批量的陆续到货模型
经济订货批量的基本公式是在前面的假设条件下建立的，但是现实生活中能够满足这些条件的情况很少，为使模型更接近于实际情况，应该放宽条件改进模型。
如果考虑到存货不能一次到达，存货可能陆续入库，库存货物陆续增加。
在这种情况下，对基本模型要做一些修改。
计算公式：
T=（Q-Q/P*D）/2*C+D/Q*K
即，年成本合计=储存成本+订货成本。
式中，（Q-Q/P*D）/2*C为储存成本；
D/Q*K为订货成本；P为每日送货量；
Q/P为每批存货全部送达所需日数；
D为每日消耗量；Q/P*D为送货期内全部耗用量。
通过推倒可以得出：
（1）最优订货批量Q*
（2）每年最佳订货次数N*=D/Q*
（3）最佳订货周期T*=1年/N*
（4）经济订货量占用资金I*=Q*/2*单价。
以上的模型没有涉及到数量折扣。
数量折扣是指，供应商对一次购买某货品的数量大或超过规定限度的客户，在价格上给予优惠。
如果供应商实行数量折扣，那么，除了订货成本和储存成本之外，采购成本也成了决策中的相关成本。
这时，三种成本的年成本合计最低的方案，才是最优方案。
计算公式：
T=储存成本+订货成本+采购成本
=（Q-Q/P*D）/2*C+D/Q*K+D*U*（1-Di）
式中，U为采购单价；Di为数量折扣
【例11-5】某企业有四种货物要采购，供应商规定了数量折扣，其数量折扣条
升级会员 