Excel分析模型

关键词： 家庭理财 核心思想 实用型 分析模型

Excel分析模型（精选十篇）

Excel分析模型 篇1

一、建立参数配置表

在工作簿中选择一个工作表, 将其命名为“参数设置表”, 相关项目设置, 如表1。对于账户的设置, 是结合各个家庭自身特点而确定, 可以在现有账户设置的基础上进行增减。例如, 有的家庭喜欢出行, 每年在旅游上有不少消费, 则建议在“费用账户”下单独设置“旅游”账户, 独立核算以便进行细化的统计分析, 更好把握家庭资产流向。

选定B3:B30的所有单元, 右击选中“命名单元格区域 (R) ”, 在弹出单元框的“名称”输入“Account”。

二、建立家庭日记账

在工作簿中选择一个工作表, 将其命名为“家庭日记账”, 相关项目的设置和使用, 如表2所示。选定C3单元, 从菜单栏中选择“数据”下的“数据有效性”:“允许”选择“序列”, “来源”填入“=Account”, 勾选“忽略空值”、“提供下拉箭头”和“对有同样设置的所有其他单元格应用这些更改 (P) ”。同理, 可以设定C列和D列中所有单元的下拉列表。

从菜单栏中选择“公式”下的“名称管理器”, 点击新建, “名称”输入“Time”, “引用位置”输入:=家庭日记账!﹩B1:﹩B10000。同理, 分别新建“Out”、“In”和“Amount”, 其对应“引用位置”分别输入:=家庭日记账!﹩C1:﹩C10000、=家庭日记账!﹩D1:﹩D10000和=家庭日记账!﹩E1:﹩E10000。

三、建立家庭资产负债表

在工作簿中选择一个工作表, 将其命名为“家庭资产负债表”, 相关项目设置如表3所示。

家庭资产负债表统计截至当前时点的资产和负债最终情况。对于资产类账户, 如“现金”项目, 对应“金额”为单元B4, 输入目标函数:=SUMIF (In, A4, Amount) -SUMIF (Out, A4, Amount) ;对应“占资产比重”为单元C4, 输入:B4/B9。对于负债账户, 如“信用卡”项目, 对应“金额”为单元B12, 输入目标函数:=SUMIF (Out, B12, Amount) -SUMIF (In, B12, Amount) ;对应“占负债比重”为单元C12, 输入:B12/B16。其余同理。

四、建立家庭现金流量表 (月度)

在工作簿中选择一个工作表, 将其命名为“家庭现金流量表 (月度) ”, 相关项目设置如表4所示。

家庭资产负债表 (月度) 统计某一个月现金流量状况。如查询了解2010年5月现金流量情况, 需要在单元B2输入:2010、单元C2输入:5。对于收入类账户, 如“月薪收入”项目, 对应“金额”为单元B7, 输入目标函数:=SUM (IF ( (YEAR (Time) =﹩B﹩2) , IF ( (MONTH (Time) =﹩C﹩2) , IF (Out=A7, Amount, 0) , 0) , 0) ) , 然后按“ctrl+shift+enter” (此为数组函数的输入方法, 以下同) ;对应“占总收入比重”为单元C7, 输入:B7/B11。对于费用类账户, 如“吃”项目, 对应“金额”为单元B14, 输入目标函数:=SUM (IF ( (YEAR (Time) =﹩B﹩2) , IF ( (MONTH (Time) =﹩C﹩2) , IF (In=A14, Amount, 0) , 0) , 0) ) , 然后按“ctrl+shift+enter”;对应“占总费用比重”为单元C14, 输入:B14/B16。其余同理。

五、建立家庭现金流量表 (年度)

在工作簿中选择一个工作表, 将其命名为“家庭现金流量表 (年度) ”, 相关项目设置, 如表5。家庭资产负债表 (年度) 统计某一年现金流量状况。如查询了解2010年现金流量情况, 需要在单元C2输入:2010。对于收入类账户, 如“月薪收入”项目, 对应“金额”为单元B7, 输入目标函数:=SUM (IF ( (YEAR (Time) =﹩C﹩2) , IF (Out=A7, Amount, 0) , 0) ) , 然后按“ctrl+shift+enter”;对应“占总收入比重”为单元C7, 输入:B7/B11。对于费用类账户, 如“吃”项目, 对应“金额”为单元B14, 输入目标函数:=SUM (IF ( (YEAR (Time) =﹩C﹩2) , IF (In=A14, Amount, 0) , 0) ) , 然后按“ctrl+shift+enter”;对应“占总费用比重”为单元C14, 输入:B14/B16。其余同理。上年现金和银行存款, 对应“金额”需要在单元B28输入。净现金流量, 对应金额为单元B31, 输入:=B28+B11-B26, 对应“占总收入比重”为单元C31, 输入:=B31/B11。

使用者只需要在家庭日记账中录入每天的日记账, Excel强大的统计功能即可完成各个分类统计。考虑到非财务类使用者可能对于“资产、费用类账户, 借方登记增加贷方, 贷方登记减少;负债、收入类账户, 借方登记减少, 贷方登记增加”这一入账规则较难理解和掌握, “家庭日记账”中采用简单易懂的“从哪个账目支出”和“流入哪个账目”取而代之。资产负债表根据每一笔家庭日记账的录入, 进行分析统计, 核算出实时的资产和负债对应的总账和明细账目的结果。家庭现金流量表 (月度) 和家庭现金流量表 (年度) 只需要输入指定的年度和月份时间, Excel会自动按照该时间条件, 归集所有符合条件的数据, 进行分析统计, 核算出收入和费用对应的总账和明细账的结果。

在家庭理财越来越受到重视的今天, 利用Excel建立家庭理财模型, 帮助使用者了解到自己资产、负债现状, 以及收入、支出情况, 以便更好控制金钱流向, 实现量入为出、有计划地花费, 有效控制家庭开支, 真正实现家庭的储蓄计划和投资计划。

参考文献

[1]王煜:《家庭账户及个人理财研究》, 《财会通讯》2010年第3期。

[2]郑皓斌:《晋身EXCEL高手》, 上海交通大学出版社2004年版。

Excel在财务预测模型中的应用 篇2

【关键词】 Excel；养老年金；内部收益率

下面作者将以保险业中的年金保险作为切入点，运用Excel进行投资收益分析。在了解年金保险的内部收益率的同时，我们可以体会到科技发展带来的Excel这类工具强大的数据处理能力。

一、重要概念介绍

（一）养老年金：年金是商业保险公司销售的一种保险产品，它可以在一定时期内周期性地给购买者提供一系列的支付款项。购买者首先向保险公司一次性或者分期支付保险费；作为回报，在购买者生存的情况下，保险公司按照合同约定的金额、方式和期限，有规则并且定期向购买者给付保险赔偿金（简称保险金）。

（二）内部收益率：内部收益率是效率型经济评价指标中重要的评价指标之一。简单地说，就是净现值为零时的折现率。在计算净现值时，随着折现率的不断增大，净现值不断减小，当折现率等于内部收益率时，净现值为零。

二、模型条件假设

（一）我国的养老保险由三个部分（或层次）组成。第一部分是基本养老保险；第二部分是企业补充养老保险；第三部分是个人储蓄性养老保险。本文中为了研究问题的方便，假设投资者都是个人储蓄性养老保险，而不涉及到企业和国家政策方面的保险。

（二）按照不同的标准，年金可以分为不同的种类。常见的分类方法如下：（1）按缴费方式不同，分为趸缴年金和期缴年金。前者是指购买者一次性地缴清全部保险费，然后从年金合同约定的给付开始日起按期领取年金。后者也称为分期缴费年金，购买者在年金给付开始日前分期缴纳保险费，然后再在约定的年金给付开始日起领取年金。（2）按年金给付开始时间不同，分为即期年金和延期年金。前者是購买者一次性或分期交清年金保险费后，年金立即进入给付周期。后者则指年金保险合同成立生效后，待购买者到达合同规定的年龄或等待一定的时间后，保险公司在购买者仍然生存的条件下开始给付年金保险金。（3）按年金给付额是否变动，分为固定年金和变动年金。一般来说年金的保险赔偿金可以是固定的，可以是固定增长的，也可以是随机变动的。固定年金是指保险金给付额是固定的，或者按照一定规律，固定增长比例和固定增长幅度增长的年金品种。变动年金的保险金给付额则随年金基金的投资收益变化，年金投资基金收益越高，年金支付额越大，反之则越小。这里作者假设本文中所考察的是趸缴年金和即期年金，且年金是固定的，即购买者一次性付清保险费后，保险公司立即按合同期限在固定的时间按固定金额支付保险金。

（三）假设顾客购买的是终生年金，即只要被保险人生存且年龄小于最高年龄规定（一般是100岁），保险公司就有义务要支付保险金维持被保险人的生活。

三、模型的建立

该模型需要的要素如下：

（一）购买保险的时间。由于我国现在的平均退休年龄在51～52岁之间，我们假设购买保险的时间为50岁。

（二）支付保险金的期限。由于我们假设是终生年金，所以支付保险的期限与寿命是关系的，而最高年龄规定是100岁，所以我们考虑的寿命范围是50～100岁。

（三）保费支出。即购买保险时支付的金额，本文中就可以作为最初的投资支出。这里我们通过考虑单位固定年金价格来计算保费支出。所谓单位固定年金， 是指保险公司承诺在年金的给付期内每期向购买者支付1个单位货币( 如￥1) 的年金。我们决定采用李志生在《养老年金的定价模型及其保险金结构》中得出的有关单位固定年金的结论。

由于本文中我们假设购买时间是50岁，这里我们选择5%。

（四）保险金收入。即每期（这里假设是每月末）保险公司需要向被保险人支付的金额，相当于固定的现金流入。由于保险人购买年金保险的主要原因还是为了保障生活需要，所以我们根据每年的年消费需要来确定每期支付金额。

根据以上要素，我们利用Excel中有关资金的时间价值的函数进行模型的建立并计算相应的内部收益率。

在这个模型中，我们假设每月获得的保险金收入是800元，寿命为55岁，通过单变量求解计算出净现值为0时的投资报酬率即内部收益率。然后变化寿命的取值，以5为间隔，我们可以求出一系列的内部收益率。将其画成图表

如果将每月的保险金收入变化，而将寿命固定在平均寿命85岁的话，结果如图2所示：

四、结果分析

从图1中我们看到随着被保险人的寿命的延长，年金保险的内部收益率是呈上升的趋势的，开始上升的比较快，后来趋向平缓，而且男性的内部收益率曲线基本上一直在女性的收益率曲线的上方。这些说明的在相同的情况下男性年金保险的内部收益率不会小于女性的。而且在终生年金保险的情况下被保险人的寿命越长，内部收益率越高。

从图2中我们看到当每期的保险金收入发生变化时，不论是男性的收益率曲线还是女性的，都是与x轴平行的，这说明年金保险的内部收益率不会受到每期保险金收入的变化而变化，我想，这大概是因为我们所选用的固定年金的单位价格就是根据所有保险金的折现值计算出来的缘故。

五、总结

本文中作者通过对年金保险的条件假设，运用Excel强大的数据处理能力，得出了关于年金保险作为新的投资产品的内部收益率的一些结论。不仅体现了Excel在财务管理中的应用，同时，得出的结论也能让读者对于年金保险有进一步的了解。

当然，模型与现实生活中的情况往往还是有差距的，现实中的情况一般要复杂得多，所以通过模型分析得出的结论不能全面地反映各种情况，但这并不影响它的参考价值。

参考文献：

[1]吴晓求.《证券投资学》.中国人民大学出版社，第二版. 2003：1～2

[2]李志生.养老年金的定价模型及其保险金结构.统计与决策.2007年第9期：117～118

Excel分析模型 篇3

“有房才有家,有房才有根。”是绝大多数国人对于买房而不租房过日子的论调。房价如此的高,贷款买房成为必然。贷款方式多种多样,如何选择,如何计算又成为买房路上的拦路虎。Excel是数据处理最有效的工具,使用Excel构建还款模型、展示结果、分析成因就显得顺理成章。房贷还款方式主要分为等额本金、等额本息和公积金自由三种还款方式。

2 贷款方式及对应Excel函数介绍

2. 1 房贷还款方式概述及差异比较

等额本金、等额本息、公积金自由三种还款方式各有优缺点,如表1 所示。

2. 2 Excel财务函数介绍

Excel中包含多种类型函数,其中财务函数可进行常见财务计算,如确定贷款的支付额、投资的未来值或净现值,以及债券或息票的价值,功能十分强大。结合实例,函数PMT、IPMT、PPMT分别用于计算等额本息还款额、利息、本金。

3 还款模型构建

3. 1 案例

某人拟购置一处房产,需从银行贷款80 万元,公积金月贷款利率为: 3. 54167‰,贷款30 年,试构建还款模型,比较等额本金、等额本息及自由还款方式的差异,从总还款额、支付的利息及每期还款额等几个方面说明。

3. 2 构建模型

模型如下图所示,在进行公式录入前先来介绍几个公式:

支付的利息= 期初余额 × 贷款利率

本期还款额=支付的利息+偿还的本金

期末余额=期初余额-偿还的本金

下一期期初余额= 上一期期末余额

基于上述公式,期初余额已知,则支付的利息确定,根据不同还款方式确定本期还款额或偿还的本金即可确定其余所有项目。

等额本金偿还方式,偿还的本金= 贷款额/总付款期数;等额本息偿还方式,本期还款额= PMT ( $ B $ 4, $ B$ 6, - $ B $ 3 ) ,第三个参数pv为负值,支付的利息=IPMT ( $ B $ 4,A377, $ B $ 6, - $ B $ 3 ) ,偿还的本金= PPMT ( $ B $ 4,A377, $ B $ 6, - $ B $ 3 ) ; 自由还款方式,最低还款额为合同规定的最低值,案例设定为3641元,其余项可由公式计算得到。

4 结果与分析

根据房贷还款模型对案例进行计算,结果如下图、表2所示。显见,自由还款方式所要支付的利息最多,且最后一期需还 ￥ 217,413. 46; 等额本金方式最初还款额为 ￥ 5,055. 56,逐年递减,最后一期为 ￥ 2,230. 09; 等额本息方式每期还款额固定为 ￥ 3,935. 52。

5 小结

通过使用Excel函数,构建了房贷还款模型,充分展示了三种房贷还款方式的差异。模型实现了计算过程自动化,只需输入贷款额、利率、年限即可由此计算出三种还款方式的每期数值。模型既从细节上展示了每期的还款额、利息和本金,又从整体上给出了三种方式的利弊差异。

总的来说,模型的构建阐明了三种房贷还款方式的特点及适用对象,为无所适从的 “准房奴”提供了详尽的数据支持。

参考文献

[1]王海林.Excel财务管理建模与应用[M].北京:电子工业出版社,2014.

[2]张山风,周凤.Excel财务函数应用解析[J].办公自动化杂志,2010(178):47-50.

Excel分析模型 篇4

样本数据分布区间、标准差等都是描述样本数据范围及波动大小的统计量，统计标准差需要得到样本均值，计算较为繁琐。这些都是描述样本数据的常用变量，使用Excel数据分析中的“描述统计”即可一次完成。

注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数据库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项。

操作步骤

1.打开原始数据表格，制作本实例的原始数据无特殊要求，只要满足行或列中为同一属性数值即可。

2. 选择“工具”-“数据分析”-“描述统计”后，出现属性设置框，依次选择：

输入区域：原始数据区域，可以选中多个行或列，注意选择相应的分组方式;

如果数据有标志，注意勾选“标志位于第一行”;如果输入区域没有标志项，该复选框将被清除，Excel 将在输出表中生成适宜的数据标志;

输出区域可以选择本表、新工作表或是新工作簿;

汇总统计：包括有平均值、标准误差(相对于平均值)、中值、众数、标准偏差、方差、峰值、偏斜度、极差、最小值、最大值、总和、总个数、最大值、最小值和置信度等相关项目，

其中：

中值：排序后位于中间的数据的值;

众数：出现次数最多的值;

峰值：衡量数据分布起伏变化的指标，以正态分布为基准，比其平缓时值为正，反之则为负;

偏斜度：衡量数据峰值偏移的指数，根据峰值在均值左侧或者右侧分别为正值或负值;

极差：最大值与最小值的差。

第K大(小)值：输出表的某一行中包含每个数据区域中的第 k 个最大(小)值。

平均数置信度：数值 95% 可用来计算在显著性水平为 5% 时的平均值置信度。

结果示例如下(本实例演示了双列数据的描述统计结果)： 结果省略!

原始数据文档在这里下载>>操作结果文档在这里下载>>

Excel分析模型 篇5

摘要：本文介绍了应用Excel制作学生成绩考核表及试卷分析表模块的方法，为分析学生学习情况和教师教学效果提供了快捷而准确的统计软件。并根据教育测量学原理，提出了改进建议。

关键词：成绩考核；试卷分析；教育统计

1引言

成绩统计与试卷分析是教学考核工作的一个重要环节，也是一项非常复杂细致的工作。如果采取手工计算，不仅费力费时而且结果易出差错。专业的统计软件SPSS、SYS-TAT等其功能固然强大，统计分析的专业性、权威性不可否认，但是对于没有开设统计学专业的院校这些软件并不常用。学生自己也比较难以找到相应的工具。微软公司开发的Excel软件作为一款优秀的表格软件，其提供的统计分析功能虽然比不上专业统计软件，但它比专业统计软件易学易用。便于掌握。以学生试卷成绩为依据，利用Excel建立了试卷分析和成绩统计两个模块，并能够根据录入信息自动进行成绩统计和试卷分析，避免了大量、复杂的手工计算，使用效率很高。

2成绩录入模块

大量的原始成绩需要输入，一旦有误便会影响分析结果。人工多次校对也费时费力，效果得不到保证。在这里我们应用“二次校验输入法”，在格式相似的成绩表2中再次录入，和成绩表1做比较，如果不一致则自动显示出错。这种方法高效且出错率在万分之一以下。

(1)录入成绩

把成绩录入到Excel工作薄中。命名为“1”。

(2)二次校验

新建校验工作薄，命名为“2”。“姓名”和“学号”与表1布局相同，同时在每门分数后增加一列。这里以检验英语成绩为例，在D3中输入公式IF(AND(1＇!C3=C3，AND(C3＞＝0.C3＞=120))，“pass”，“error”)。如果出现单元格的数值与表1中对应的数据不一致或越界就显示错误。此时设定满分为150分。单击菜单栏上的“格式一条件格式”，设计单元格内容为“error”时显示红色。如果表2中C3单元格中输入110分，和表1的原始分数一致，D3就显示“pass”；C4单元格与表1相关单元格分数不一致时，D4显示红色“error”。

(3)锁定原始数据

点击菜单栏上的“工具”。进入“保护→保护工作表”，输入密码确定。这样做以防疏忽导致的错误。设置密码对数据处理起到保护作用。

3成绩指标分析模块

对成绩进行录入、汇总之后，新建工作表命名为“试卷分析表”，试卷分析表格式按照人数、平均分、标准差、优秀率四个方面对试卷自动进行相关数据分析。

(1)人数

人数指每门课程参加考试的总人数。在此单元格中输入“COUNT(C9:C1000)”，由于每门课程参加考试的人数不同，可以设计大于最大的人数。

(2)平均分

平均分指每门课程的平均成绩，是衡量学习成绩的重要参数。在此单元格输入“AVERAGE(C9:C1000)”。

(3)标准差

样本中各数据与样本平均数的差的平方和的平均数叫做样本方差，标准差指样本方差的算术平方根。如果标准差大，则反映学习成绩波动大，均衡的素质教育没有得到好的实现。在此单元格输入“STDEV(D9:D1000)”。

(4)优秀率

本例满分为150分，输入“COUNTIF(C9:C1000，“＞=120”)／COUNT(C9:C1000)”。

获得每个单位成绩统计数据后，数据分项放入同一表内，以完成总成绩分析数据统计。

4改进建议

根据教育统计与测量学原理，可增加以下分析指标：

(1)增加对试卷效度分析

试卷效度是反映试卷有效性和准确性的指标，即考试达到预期目标的程度。

(2)增加对试卷信度分析

试卷信度是检验考试结果稳定性和可靠性的指标。

(3)增加对试卷区分度分析

试卷区分度是指考试题目对考生水平的区分程度。

5结束语

Excel分析模型 篇6

(一)Sheet 2:单项投资项目决策模型(输入区)的设计(建设期为1年)

1)如图1所示,采用组合框对固定资产的折旧方法进行控制,并为组合框指定相应的数据源:直线法,年数总和法,双倍余额递减法。该组合框的最大项数为3,其中直线法:1,年数总和法:2,双倍余额递减法:3,其数据连接单元格为:Sheet 1.B10。各年的营业利润和借款利息的控制分别选定对应的工作表名称并执行“视图”→“工具栏”→“窗体”,用相应的按钮来控制。

2)按钮——单项投资项目决策模型(输出区)的设计:

Sheets("单项投资项目决策模型(输入区)").Select

3)按钮——返回首页的设计(Sheet 1工作表名称为“首页”):

Sheets("首页").Select

(二)Sheet 3:单项投资项目决策模型(输出区)的设计(见图2)

1. 各年现金流量的确定

建设期某年的净现金流量=-该年发生的固定资产投资额;

经营期某年净现金流量=因该项投资而增加的营业收入+该年回收的固定资产净残值-付现成本-因该项投资而增加的所得税=因该项投资而增加的营业收入+该年回收的固定资产净残值-(因该项投资而增加的营业成本-该年因使用该固定资产新增的折旧-该年因使用该固定资产新增的利息)-因该项投资而增加的所得税。

2. 设计步骤

(1)现金的流入

1)对各年营业状况的按钮和净残值回收的控制(Sheet2中B11所对应的按钮采用VBA代码控制):

a=Val(Input Box("请输入项目的计算期(1-10):"&Chr(10)&"建设期均假设为1年","单项投资项目决策分析"))

If a=0 Then

Msg Box"请输入各年的利润!",vb OKCancel,"单项投资项目决策分析"

2)对各年借入资金的控制:

单项投资项目决策模型(输出区).

C13='单项投资项目决策模型(输入区)'!E9

(2)现金的流出

1)固定资产的投资:

单项投资项目决策模型(输出区).B16=-'单项投资项目决策模型(输入区)'!B7

2)对付现成本借款资本化利息的控制(Sheet 2中B12所对应的按钮采用VBA代码控制):

3)固定资产折旧的处理(利用B10中的组合框):

4)所得税的处理:

(3)各年现金流量的确定

单项投资项目决策模型(输出区).B21=B12-B16;

单项投资项目决策模型(输出区).C21=B12-B16;

单项投资项目决策模型(输出区).D21==D12+D14-(D17-D18-D19)-D20;

E~L列可以通过自左向右拖曳,即可以得到各年的现金流量。

(4)各年净现值的确定

单项投资项目决策模型(输出区).B22=B21;

单项投资项目决策模型(输出区)C22=C21/(1+'单项投资项目决策模型(输入区)'!$E$7)^C10;同理,E~L列可以通过自左向右拖曳即可以得到各年的净现值。

(5)累计净现值的确定

(6)项目投资决策指标的计算(见图3)

1)静态投资决策指标:

(1)会计收益率(AAR):

(2)投资回收期(PP):

2)动态投资决策指标:

(1)净现值(NPV):

NPV=NPV('单项投资项目决策模型(输入区)'!$E$7,C21:L21)+B21

(2)净现值率(NPVR):

('单项投资项目决策模型(输入区)'!E7,'单项投资项目决策模型(输出区)'!D21:L21)/(1+'单项投资项目决策模型(输入区)'!E7))/ABS('单项投资项目决策模型(输出区)'!B21+NPV('单项投资项目决策模型(输入区)'!E7,'单项投资项目决策模型(输入区)'!E7,'单项投资项目决策模型(输出区)'!C21))

(3)获利指数(PI):

(4)内含报酬率(IRR):

(7)投资决策的建议

有关投资决策分析分为4种情况,具体应设置的公式为:

IF(AND(C30>=0,C32>=0,C34>=1,C36>='单项投资项目决策模型(输入区)'!E7,C26>='单项投资项目决策模型(输出区)'!L10/2),"完全具备财务可行性",IF(OR(C30>=0,C32>=0,C34>=1,C36>='单项投资项目决策模型(输入区)'!E7,C26>='单项投资项目决策模型(输入区)'!E7),"完全不具备财务可行性","基本具备财务可行性"))

二、完整工业项目决策模型

(一)Sheet 4:完整工业项目决策模型(输入区)的设计(建设期为1年)

数据输入区的设置(如图4所示)与Sheet 2的设计类似,但增加了对流动资金回收和开办费摊销的考虑。

(二)Sheet 5:完整工业项目决策模型(输出区)的设计(见图5)

1. 各年现金流量的确定

建设期某年的净现金流量=-该年原始投资额-开办费-流动资金;

经营期某年净现金流量=因项目投资而增加的营业收入+该年回收额+流动资金回收额-(因项目投资而增加的营业成本-该年折旧—该年摊销-该年利息)-因该项投资而增加的所得税。

2. 设计步骤

(1)现金的流入

对各年的营业状况的按钮和净残值及流动资金回收的控制(Sheet 4中B11所对应的按钮采用VBA代码控制):

d=Val(Input Box("请输入第"&i&"年的营业收入:(单位:万元)","完整工业项目决策分析"))

b=Val(Input Box("请输入第"&i&"年的营业成本:(单位:万元)","完整工业项目决策分析"))

(2)现金的流出

1)固定资产的投资:

完整工业项目决策模型(输出区)!B16='完整工业项目决策模型(输入区)'!B7

2)无形资产投资:

完整工业项目决策模型(输出区)!B17='完整工业项目决策模型(输入区)'!B10

3)开办费的发生和摊销:

4)固定资产折旧与借款资本化利息以及因该项投资而增加的所得税的处理:与单项投资项目决策模型中的处理相同。

(3)各年现金流量的确定

E~M列可以通过自左向右拖拽即可以得到各年的净现值。

(4)各年净现值的确定

完整工业项目决策模型(输出区).B24=B23

完整工业项目决策模型(输出区)C23=C21/(1+'完整工业项目决策模型(输入区)'!$E$7^C10

E~L列可以通过自左向右拖拽即可以得到各年的净现值。

(5)累计净现值的确定

(6)项目投资决策指标的计算

所有的指标计算与前面单项投资项目决策模型均相同,在此不再赘述。

三、更新改造投资项目决策模型

(一)Sheet 6:更新改造投资项目决策模型(输入区)的设计

(1)新旧设备折旧方法的选择则采用两个组合框来对其进行控制,设计方式如下:在此工作表中的A25~A27中分别输入:直线法,年数总和法,双倍余额递减法。并设置控件格式如下:

(2)按钮——更新改造投资项目决策模型(输出区)的设计:

Sheets("更新改造投资项目决策模型(输出区)").Select

(3)按钮——返回首页的设计:Sheets("首页").Select

具体模型设计如图6所示。

(二)Sheet 7:更新改造投资项目决策模型(输出区)的设计(建设期为1年)

1. 新旧设备各年折旧明细表的设计(见图7)

此表的设计主要是基于计算各年的净现金流量来准备的。具体公式设置如下:

(1)旧设备各年的折旧(利用VBA函数来控制)

注意:这里旧设备的成本采用的是设备的变现价值,而新设备的成本采用的是设备的原值。利用VBA函数的具体控制方式与旧设备的处理类似。

(2)每年增加的折旧

2. 更新改造投资项目决策分析表的设计(见图8)

(1)各年差额现金流量(ΔNCF)的确定

建设期某年的净现金流量=-(该年发生的新固定资产投资-旧固定资产变价净收入);

建设期末的净现金流量=因固定资产提前报废发生净损失而抵减的所得税额;

经营期第一年净现金流量=该年因更新改造而增加的净利润+该年因更新改造而增加的折旧+因固定资产提前报废发生净损失而抵减的所得税额;

经营期其他各年净现金流量=该年因更新改造而增加的净利润+该年因更新改造而增加的折旧+该年回收新固定资产净残值超过假定继续使用的旧固定资产净残值之差额。

(2)设计步骤

1)每年增加折旧、每年营业成本变动、每年营业净利润变动及各年差额现金流量(ΔNCF)的确定:

2)报废旧设备抵减所得税:

3)差额内部收益率(ΔIRR):

ΔIRR=IRR(B21:G21)

4)建议投资决策:

B23=IF(B22>'更新改造投资项目决策模型(输入区)'!B16,"应当更新设备!","可以继续使用旧设备!")。并对决策的字体颜色进行控制。当更新设备时,字体颜色为红色;可以继续使用旧设备时,字体颜色为绿色。

摘要：固定资产的投资作为企业的一项重要投资活动,事关企业的获利能力和内在价值,具有较大的风险。如何更快更好地进行固定资产投资并有效决策是企业高层管理者非常关心的问题。本文以单项投资项目、完整工业项目和更新改造投资项目(一般为10年左右)为例,利用Excel和VBA函数来建立一套完整的固定资产项目投资决策分析模型,以期为企业的高层管理者在进行固定资产项目投资决策时提供参考性建议。

关键词：项目投资,项目决策,Excel模型

参考文献

[1][美]罗斯,威斯特菲尔德,杰富.公司理财[M].吴世农,沈艺峰,王志强,等译.北京:机械工业出版社,2003.

[2][美]贝斯利,布里格姆.财务管理精要[M].刘爱娟,张燕,译.北京:机械工业出版社,2003.

[3][美]格莱葛·W·霍顿.基于Excel的财务管理[M].卢俊,杨飞虎,等译.北京:中国人民大学出版社,2003.

[4]中国注册会计师协会.财务成本管理[M].北京:经济科学出版社,2007.

[5]财政部会计资格评价中心.财务管理[M].北京:中国财政经济出版社,2007.

[6]林宏谕,熊汉琳.Excel在财会分析与经济上的应用[M].北京:中国铁道出版社,2002.

Excel分析模型 篇7

关键词：EXCEL,管理决策,模型

0 引言

现代企业在做盈亏临界分析时, 很多大中型企业一般采用委托软件公司开发专业软件, 但由于软件开发需要投入较大的资金、专业技术人才及开发时间等, 对于小微企业来讲, 并不切合实际。微软公司推出的Excel、VBA等软件, 早已被国内外经济管理人员公认为强有力的信息分析与决策支持软件工具。为了节约小微企业的有限资金, 缩短软件开发时间, 笔者试图在EXCEL电子表格平台上开发设计出一套盈亏临界分析决策模型。下面, 就此问题展开如下分析。

1 本文涉及的基本概念

(1) 决策:是基于一定的目标, 运用科学地方法、手段, 从两个或两个以上的方案中筛选出最优方案分析判断过程。决策问题有结构化、半结构化和非结构化之分。

(2) 结构化决策:是指用确定的模型或语言描述某一决策过程的环境及规则, 形成决策方案, 对多套方案进行比选之后确定最优决策方案。对结构化决策问题而言, 只要建立了模型就可在此基础上找到最优解或满意的解, 因此完全可以运用计算机完成结构化决策。这就是本文的研究课题。

(3) 模型:是所研究的系统、过程、事物或概念的一种表达形式。模型可分为物理模型、模拟模型和数学模型三大类。数学模型即通常所说的定量模型, 是在现实系统中通过数学公式来量化分析各种本质属性, 并描述各种变量之间的依赖关系。数学模型能够以量化的形式对各个特征量的变化规律进行描述。

(4) 决策模型:是为辅助决策而构建的数学模型, 主要用于管理决策。近年来, 运筹学的内涵不断延伸, 专家学者通过研究提出了许多决策分析法, 如对策论、排队论、调度模型、存贷模型、线性规则、动态规则等等。计算机系统是实施这些分析法的必备载体, 它使决策方法数学化和模型化。可以利用计算机来编制重复性的数学模型, 并形成管理决策, 然后用Excel电子表格实现, 提高效率。企业经营管理决策常用的模型有很多, 下面笔者以介绍建立盈亏临界分析模型为例, 从建模分析工具、所需函数、具体步骤等三方面讲解如何用Excel电子表格建立决策模型。

(5) 盈亏临界点:也称损益平衡点、保本点, 它是指企业当期销售收入与当期成本刚好相等、不亏不盈即达到盈亏平衡的状态。

(6) 盈亏临界分析:是财务管理中的基础性分析方法, 其最基本的应用领域是“本—量—利”分析, 它通过成本、销量和利润三者关系的分析, 找出三者之间联系的规律, 从而有效地制定经营决策, 为目标控制提供非常有用的方法。

2 EXCEL建模分析工具

2.1“单变量求解”

单变量求解是通过对另一个单元格中的值进行调整, 并计算指定单元格中的特定值的方法。单变量求解, 需要在Excel中调整指定单元格中的值, 直至与该单元格关联的公式返回符合要求的值。“单变量求解”是组成一组命令的关键部分, 通常将这些命令视为工具。假设单个公式的预期结果为已知数, 用来确定此公式结果的输入值为未知数, 就能运用“工具”菜单中的“单变量求解”功能进行求解。在单变量求解的过程中, Excel中特定单元格中的值会不断做出调整, 直至与该单元格相关联的公式返回符合要求的结果。

例如, 表1中使用“单变量求解”逐渐增加单元格B3中的利率, 直到B4中的付款额等于900。

2.2“模拟运算表”

模拟运算表实际是一个单元格区域。该表包括单/双输入模拟运算表, 可以显示一个或多个公式中替换不同值时的结果。单输入模拟运算表中, 可以针对某一变量输入不同的数值, 观察其数值变化对公式产生了什么影响。而双输入模拟运算, 则需要键入两个变量的不同值。

3 盈亏临界分析模型设计中使用的函数

IF函数在逻辑运算中比较常见。该函数执行真假值判断, 即基于逻辑运算的真假值返回不同结果。通常用此函数来检测数值和公式。

3.1 函数语法

Logical_test是计算结果为TRUE或FALSE的任意值或表达式。譬如, “A10=100”是一个逻辑表达式, 若单元格A10=100, 其表达式即为TRUE, 反之则为FALSE。在实际运算中, 可以通过任何比较运算符得出本参数。

Value_if_true logical_test为TRUE时返回的值。假设本参数是文本字符串“预算内”, 且logical_test参数值是TRUE, 那么IF函数就会相应的显示“预算内”。假设logical_test为TRUE, 而value_if_true为空, 则本参数返回0 (零) 。若要显示TRUE, 就应为本参数使用逻辑值TRUE。Value_if_true也可以是其它公式。

Value_if_false logical_test是FALSE时返回的值。假设本参数是文本字符串“超出预算”, 且logical_test参数值是FALSE, 那么IF函数就会显示文本“超出预算”。假设logical_test为FALSE且Value_if_false忽略不计 (也就是说value_if_true后没有逗号) , 那么系统就会返回逻辑值FALSE。假设logical_test为FALSE且Value_if_false为空 (即value_if_true后有逗号, 并紧跟着右括号) , 则本参数返回0 (零) 。当然, Value_if_false也可以是其它公式。

3.2 函数说明

函数IF可以嵌套七层, 通过value_if_false及value_if_true参数能构造出复杂的检测条件。

在计算参数value_if_true和value_if_false后, 函数IF返回相应语句执行后的返回值。假设IF的参数包含数组 (数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式;数组常量是用作参数的一组常量。) , 则执行IF语句时, 必须逐一计算数组中的所有元素。

4 建立盈亏临界分析模型

盈亏临界分析主要用于确定企业达到盈亏平衡时的销售水平, 即分析销量高于或低于这一平衡点时的盈利和亏损状况。盈亏平衡点的基本算法:假定利润为零和利润为目标利润时, 先分别测算原材料保本采购价格和保利采购价格;再分别测算产品保本销售价格和保利销售价格。基本公式如下:

(1) 按产品销售量计算:

盈亏平衡点=固定成本/ (产品销售单价-单位产品变动成本)

(2) 按产品销售额计算:

盈亏平衡点=固定成本/ (1-变动成本/产品销售收入) =固定成本/ (1-变动成本率)

4.1 案例分析

YH公司制造一种高质量运动鞋。公司最大生产能力为1500双, 固定成本为38800元, 每双可变成本为38元, 当前的销量为900双, 平均销售价格为92元, 公司管理层需要建立一个决策模型用于盈亏平衡分析, 模型应包含以下功能:

(1) 需要计算的项目:1) 单位边际贡献及边际贡献率, 2) 销售收入、总成本及利润, 3) 盈亏平衡销量及盈亏平衡销售收入;

(2) 假定公司希望获得24000元利润, 计算为达到利润目标所需要的销量及销售收入;

(3) 提供反映公司的销售收益、总成本、利润等数据的本-量-利图形, 基于图形动态反映出销量从100按增量10变化到2000时利润的调整情况以及“盈利”、“亏损”、“保本”的决策信息;

(4) 考虑到销售价格受市场影响可能有波动, 用图形模型反映销售价格从70元按增量1变化到100元时, 盈亏平衡销量和盈亏平衡销售收入的相应变化。

4.2 模型设计界面

4.3 建模步骤

(1) 新建表, 计算相关指标。

在“成本管理.xls”工作簿中新建一“盈亏临界分析”工作表, 分别输入相关数据, 并按公式法计算出盈亏平衡销量与销售额。如图2所示。

其中:B8=B4-B5, B9=B8/B4, B10=B1*B4, B11=B5*B1+B6, B12=B10-B11, B14=B6/B8, B15=B14*B4。

(2) 利用“单变量求解”工具计算目标利润对应的目标销量和目标销售额。

单击B12单元格→选择菜单栏中的“工具”→“单变量求解”→在弹出的“单变量求解”对话框中做如图3所示的设置。这样即可得到目标利润为24000元时的销量为1163双, 销售额为106996元。如图4所示。

(3) 建立模拟运算表。

在单元格C2:F5单元格区域中建立收入、成本和利润的模拟运算表, 具体做法是:在单元格D2、E2、F2分别输入公式“=B10”、“=B11”、“=B12”, 选中单元格区域C2:F5, 单击菜单“数据”→选择“模拟运算表”→在弹出的“模拟运算表”对话框中做如图5所示的设置。得到的结果如图6所示。

这样即可得到不同销量时的销售收入、总成本以及利润等。修改不同的销量, 其三个指标也自动调整。

(4) 使用If函数得到决策结论。

单击A22单元格→在编辑栏中输入“="销量="&ROUND (B1, 0) &"时, "&IF (B12>0, "盈利", IF (B12=0, "保本", "亏损") ) ”→按“回车”键确认。该公式的含义是判断B12单元格的利润, 如果>0, 显示“盈利”, 如果<0, 显示“亏损”, 如果=0, 显示“保本”。

(5) 添加微调控件, 建立模型。

打开窗体控件, 添加两个微调控件, 分别调控销量与价格。右击微调控件, 在弹出的“设置控件格式”对话框中做如图7所示的设置。

这样即可动态显示出不同销量、不同价格的收入、成本与利润的变化情况。

(6) 建立动态图表。

选择C2:F5单元格区域, 利用图表向导建立收入、成本、利润的XY散点图, 每个曲线分别代表收入、成本、利润, 可以清楚地看到三条曲线随价格或销量的变化情况, 并添加如前所述的控件按钮。结果如图8所示。

其中:垂直线是盈亏平衡销量垂直参考线和当前销量垂直参考线。采用垂直参考点的图形十分有助于决策者了解利润随销售单价变化的全貌, 它反映出当固定成本与单位可变成本不变而销售单价由小变大时, 盈亏平衡销量由大变小, 垂直参考线向左移动。

综上, 通过Excel成功建立了一个盈亏临界分析决策模型和可调动态图表, 决策者可以在图形上边调节参数, 边观察反映决策结果的曲线及其特征的变化。该方法可以帮助小微企业提高决策分析效率、节约企业资金、缩短开发时间、降低开发风险, 帮助小微企业实现降低成本的目的。管理人员甚至可以举一反三, 无须专职程序员帮助, 利用Excel自行建立成本决策模型、最佳产品组合分析模型、设备更新改造的投资决策模型等企业经营管理决策常用模型。

参考文献

[1]刘继伟, 杨桦.EXCEL在财务管理中的应用[M].清华大学出版社.

[2]杨兵, 肖燕松.EXCEL财务管理高级应用[M].中国电力出版社.

基于Excel的筹资决策模型设计 篇8

企业各种资金的构成及其比例关系是企业筹资决策的核心问题。企业应综合考虑有关影响因素, 运用适当方法确定最优资本结构, 并在以后追加筹资中继续保持最优资本结构。根据资本结构理论的基本原理, 企业可以根据选择的筹资方法, 主要包括比较资本成本法和每股收益分析法等。 (1) 比较资本成本法。比较资本成本法, 是通过计算比较各方案加权平均资本成本, 并根据加权平均资本成本的高低来确定最优资本结构的方法。最优资本结构亦即加权平均资本成本最低的资本结构。 (2) 每股收益分析法 (EBIT—EPS分析法) 。每股收益分析法是西方财务学中用以分析筹资方式决策中较常用的一种方法, 它追求企业每股盈余 (EPS) 最优为目的, 综合考虑负债资本成本、税收作用、企业市场状况等, 确定企业的最佳资本结构。该方法假设是企业的债务增加引起企业风险的增加, 因而债务资本成本和权益资本成本的变化可不予考虑, 企业只要每股盈余增加, 就会实现价值最大。

一、Excel的分析工具及相关函数介绍

1.单变量求解。单变量求解是一组命令的组成部分, 这些命令有时也称作工具。如果已知单个公式的预期结果, 而用于确定此公式结果的输入值未知, 则可使用单变量求解功能。具体步聚是单击“工具”菜单上的“单变量求解”, 在弹出的“单变量求解”对话框中进行相应的操作。当进行单变量求解时, Microsoft Excel会不断改变特定单元格中的值, 直到依赖于此单元格的公式返回所需的结果为止。

2.数组公式。数组公式可以同时进行多个计算并返回一种或多种结果。数组公式对两组或多组被称为数组参数的数值进行运算, 每个数组参数必须有相同数量的行和列。使用组合键Ctrl+Shift+Enter生成。

3.MATCH函数。MATCH函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。

函数语法:

MATCH (lookup_value, lookup_array, match_type) 。

Lookup_value为需要在数据表中查找的数值, 它可以是数值 (或数字、文本或逻辑值) 、对数字、文本或逻辑值的单元格引用。

Lookup_array是可能包含所要查找的数值的连续单元格区域, Lookup_array可以是数组或数组引用。

Match_type为数字-1、0或1, 它说明Excel如何在lookup_array中查找lookup_value。

如果match_type为1, 函数MATCH查找小于或等于lookup_value的最大数值。

如果match_type为0, 函数MATCH查找等于lookup_value的第一个数值。

如果match_type为-1, 函数MATCH查找大于或等于lookup_value的最小数值。

如果match_type为0且lookup_value为文本, lookup_value可以包含通配符 (“*”和“?”) 。星号可以匹配任何字符序列, 问号可以匹配单个字符。

函数说明:

l MATCH函数返回lookup_array中目标值的位置, 而不是数值本身。

例如MATCH ("b", {"a", "b", "c"}, 0) 返回2, 即“b”在数组{"a", "b", "c"}中的相应位置。

查找文本值时, MATCH函数不区分大小写字母。

如果MATCH函数查找不成功, 则返回错误值“#N/A”。

函数示例 (见下页表1) :

4.IF函数。IF函数是较为常用的逻辑函数之一, 它执行真假值判断, 根据逻辑计算的真假值, 返回不同结果。可以使用函数IF对数值和公式进行条件检测。

函数语法:

Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。

Value_if_true logical_test为TRUE时返回的值。

Value_if_false logical_test为FALSE时返回的值。

函数说明:

函数IF可以嵌套七层, 用value_if_false及value_if_true参数可以构造复杂的检测条件。

在计算参数value_if_true和value_if_false后, 函数I返回相应语句执行后的返回值。

如果函数IF的参数包含数组, 则在执行IF语句时, 数组中的每一个元素都将计算。

函数示例 (见表2) :

5.SUMPRODUCT函数。SUMPRODUCT函数可以在给定的几组数组中, 将数组间对应的元素相乘, 并返回乘积之和。

函数语法:

Array1, array2, array3, …为2~30个数组, 其相应元素需要进行相乘并求和。

函数说明:

数组参数必须具有相同的维数, 否则, 函数SUMPROD-UCT将返回错误值#VALUE!。

函数SUMPRODUCT将非数值型的数组元素作为0处理。

函数示例 (见表3) :

二、利用实例建立模型

(一) 比较资本成本模型

1. 新建表。新建一个工作簿, 将sheet1改名为“比较资本成本模型”, 建立筹资方案计算表格, 分别输入三个筹资方案的相关指标及其数值 (如图1所示) 。

2.计算综合成本。单击B9单元格→在编辑栏中输入“=SUMPRODUCT (B4:B7/B8, C4:C7) /100”, 计算出方案1的加权平均资本成本, 复制公式到D9、F9单元格中, 得出方案2和方案3的加权平均成本。

3. 确定最优方案。单击B10单元格→在编辑栏中输入“=INDEX (B2:G2, MATCH (MIN (B9, D9, F9) , B9:F9, 0) ) ”, 得出最优方案为方案2 (如图2所示) 。

注:该公式的含义是首先在B9:F9单元格区域中找到最小值所在单元格的位置 (相对行号) , 作为INDEX函数的参数, 返回单元格区域B2:G2中的相应值。

(二) EBIT—EPS分析模型

1. 新建表。

在新建工作簿中将sheet2改名为“每股收益分析模型”, 建立“筹资方案”计算表格, 分别输入两个筹资方案的相关指标及其数值 (如下页图3所示) 。

2. 计算两个筹资方案的EPS。

选取B14:G14单元格→在编辑栏中输入计算公式“= (B13:G13-C3*D3-C9*D9) * (1-F2) /E4”→按[CTRL+SHIFT+ENTER]键确认, 即可得到方案1的每股利润。

选取B15:G15单元格→在编辑栏中输入计算公式“= (B13:G13-C3*D3) * (1-F2) / (E4+C10/E10) ”→按[CTRL+SHIFT+ENTER]键确认, 即可得到方案2的每股利润。

选取了B16单元格→输入计算公式“=IF (B14) >B15, "方案1", "方案2") ”→按[ENTER]键确认, 复制公式至G16单元格, 即可得到不同息税前利润所对应的最优筹资方案 (如图4所示) 。

3. 筹资决策。

单击B18单元格→在编辑栏中输入“= (B19-C3*D3-C9*D9) * (1-F2) /E4- (B19-C3*D3) * (1-F2) / (E4+C10/E10) ”, 计算两个方案的每股利润之差, 并将其作为目标函数。

单击[工具]菜单→[单变量求解]命令→弹出[单变量求解]对话框→在[目标单元格]文本框中输入“B18”→在[目标值]文本框中输入“0”→在[可变单元格]文本框中输入“B19”→按[确定]按钮即可求出两个方案无差别点的息税前利润 (如图5所示) 。

单击B20在单元格→在编辑栏中输入“= (B19-C3*D3-C9*D9) * (1-F2) /E4”, 计算两个方案的无差别点的每股利润 (如图6所示) 。

4. 绘制关系图。

选取A13:G15单元格区域→单击工具栏上[图表向导]按钮→选取[XY散点图], 按照图表向导的相关步骤操作, 即可绘制出每股利润与息税前利润关系图 (如图7所示) 。

由计算和图示结果可以看出, 当预计的息税前利润为98万元时, 两个方案每股利润相等, 均为0.60元/股。当预计的息税前利润小于98万元时, 方案2每股利润更高, 应采用方案2;当预计的息税前利润大于98万元时, 方案1每股利润更高, 应采用方案1。

小结

根据上述两个模型, 如各相关指标发生变化, 决策方案的结果也将随之变化。

摘要：筹资决策是指为满足企业融资的需要, 对筹资的途径、筹资的数量、筹资的时间、筹资的成本、筹资风险和筹资方案进行评价和选择, 从而确定一个最优资金结构的分析判断过程。筹资决策的核心, 就是在多种渠道、多种方式的筹资条件下, 如何利用不同的筹资方式力求筹集到最经济、资金成本最低的资金来源, 其基本思想是实现资金来源的最佳结构, 即使公司平均资金成本率达到最低限度时的资金来源结构。利用Excel的函数及其分析工具来建立比较资本成本法和每股收益分析法的两种决策分析模型, 以期为企业的高层管理者在进行筹资决策时提供参考性建议。

关键词：Excel,筹资决策,模型

参考文献

基于现金预算的Excel模型设计 篇9

一、现金预算的Excel模型设计背景

企业进行现金预算时应由三部分组成, 即:

1. 进行初步计算的工作表区;

2. 全部现金流入与现金流出项清单;

3. 计算期末现金余额和短期借款需求。

由于企业的现金预算起始点是销售预算, 进行现金预算时许多假设变量均来

自其他预算, 如销售预算、资本支出预算等, 所以, 企业在使用现金预算时, 要先从其他部门获取相应的预算数据才行。由于该企业所销售的产品有很强的季节性特点, 4-5月为实际销售额, 6-10月为预期销售额, 预计6月份的销售额将达到最高点, 秋冬季则大幅下降。

二、现金预算的Excel收入与支出的模型设计

【案例1】某企业销售额中, 40%为现款销售, 60%为赊销, 而在60%的赊销款中, 大约有75%可在销售发生后1个月内能收回来, 而25%则要在销售发生后两个月内收回。

1. 收款。

这里主要计算的是企业每月的应收款总额, 一般企业都或少或多存在赊销的情况, 因此, 对企业而言, 掌握什么时间能够收回应收款就非常重要。如图1为创建6-9月份的预算, 计算预期应收款时在单元格E7中输入=E5*$B7, 在单元格E8中输入=D5*$B8, 在单元格E9中输入=C5*$B9, 在E10单元格中输入=SUM (E7:E9) 。

2. 购买与付款。

这里主要是指计算支付购买存货的货款, 因为企业在赊帐购买时并没有发生现金流出, 所以只需要计算每月实际的存货购买付款。在单元格C11中输入=$B11*D5, 然后将该公式复制到单元格D11:H11区域中, 以完成各个月的计算。在单元格E13中输入=$B13*D11, 然后将该公式复制到单元格F13:H13区域中, 来计算第一个月的付款。在单元格E14中输入=$B14*C11, 然后将该公式复制到单元格F14:H14区域中, 则可以计算出每个月的总应付款。

3. 收款与支出。

这里主要是计算企业预计要发生的现金流入和现金流出。在单元格E17中输入=E10, 然后将该公式复制到单元格F17:H17区域中, 在单元格E19中输入=E15, 在单元格E20中输入=$B20*E5, 然后将该公式复制到单元格F20:H20区域中。

三、现金预算的Excel期末现金余额计算的模型设计

现金预算的最后一部分内容需要在每月末计算预计期末现金金额。该部分的内容对现金预算很重要, 可以帮助管理者了解现金余额的变化情况, 以此来了解企业的短期借款需求。该企业期末现金余额的计算步骤为:期初现金余额+总应收款-总应付款=调整前现金余额+当期借款=期末现金余额

【案例2】为满足预期外的支出需要, 该企业的管理层决定维持15000元的最低现金余额。如果预期的现金余额不足该要求, 那么企业就会通过借款来维持此最低余额状态。在单元格E29中输入=E17-E27, 计算出6月份的结果为-18800元, 说明企业的预计付款大于预计收款。

如果企业当月没有短期借款, 那么当月的调整前现金余额则等于现金余额。

这样, 只需要将当月的企业期初现金余额与净收款相加就可以了。那么, 在单元格E30中输入=SUM (E28:E29) , 计算结果为1200元, 比企业最低可接受现金余额15000元少。因此, 该企业需借入13800元来使现金余额达到这一最低现金余额要求。而为了计算6月份的借款需求, 在E31单元格中输入=IF (E30<$B37, $B37-E30, 0) 。在这里需要说明的是8月或9月企业有大额正的净收款, 公司则不需要借入资金。

【案例3】假设企业正在考虑六七月所需借入款项的数量问题, 若使企业减少借款需求, 那么可能的方法就是加快销售款的回收和推迟存货采购付款。假设企业可在第1个月收回50%的销售款, 第2个月的收款将减少至10%, 另外, 假设公司的存货采购在第1个月需支付50%的货款, 而不是60%。

基于现金预算的大额现金支出的时间决策。现金预算不仅对计划企业的短期借款需求有用, 对于收款与付款的时间决策也很有用。因此, 借助Excel中的方案管理器这个工具, 用户可以在电子表格中存储若干个方案 (输入各种变量) , 而且可以随时显示出来, 得出资本支出的最优调度。选择“数据”标签, 点击“数据工具”工作组中的“假设分析”按钮, 现选择“方案管理器”。在弹出的对话框中可以创建4个方案。点击“添加”按钮, 在弹出的下一个对话框中输入方案名“6月份支出”, 在可变单元格中输入中每月的资本支出, 即“:E26:H26”, 之后点击“确定”按钮。然后, 在当前方案的第一个可变单元格输入值200000, 其他框输入0。此时, Excel会显示方案变量值的对话框, 再点击“添加”按钮可创建下一个方案。重复上述步骤可创建完成该企业不同月份支出的4种方案。返回方案管理器并点击“摘要”按钮, 在Excel显示每个方案最终结果的单元格或单元格区域, 即该企业所关注的借款总额中输入“I30”, 并点击确定。因此, Excel就会创建一个新的工作表来汇总方案结果, 通过这一结果信息, 按照借款需求最小化这一准则, 安排支出的最好时间应为8月或9月, 如图2所示。

四、利息与超额现金投资的模型设计

1. 当期借款的计算。

假设该企业拟将超过40000元的现金之外的部分用于投资。因此, 在表中填加一项“最大可接受现金额”, 并在该单元格中输入40000即可, 企业还需支付短期借款的利息, 同时企业也能获得投资收益。

因为累计金额等于上月累计金额与本月借款之和再扣除本月投资额, 其中, 正数代表借款, 负数代表投资。如果计算5月份的借款累计金额, 则可在单元格D34中输入公式“=C34+D31-D32”, 可以看到结果为零, 同理, 将D34单元格公式向右拖至H34单元格。

短期利息支出 (收入) 的计算可通过将上月累计借款 (投资) 额与适当的利率相乘。因此, 在选择哪一种和利率时就需要用一个判断语句来完成。如果累计借款 (投资) 额为正数, 就需要使用借款利率, 否则需要使用放款利率, 若结果为零, 则表时该企业在以前期间没有发生借款或贷款。同理, 将E23中的公式复制到F23:H23中去。

另外, 现金预算中较为复杂的工作应当属于当期借款额和当期投资额的计算。调整前现金余额低于最低可接受现金数, 可以利用借款额补足最低可接受现金数为准。但若企业存在一部分投资, 那么就需要减少借款额, 减少的额度就等于投资数。如果调整现金余额大于最小可接受现金额, 并且企业还可以前的借款, 那么就以超出最小可接受现金额的部分来归还未偿付借款。那我们就需要在定义公式时用IF语句的嵌套来完成。因此, 在E31单元格中输入6月份的当期借款额的公式, 即:

“=IF (E30<$B37, IF (D34<0, MAX ($B$37+D34-E30, 0) , $B$37-E30) , IF (D34>0, -MIN (D34, E30-$B$37) , 0) ) , 然后将该公式复制到F31:H31单元格区域中。需要在此进行公式说明的是, 在投资额不足以支付现金的需要时, 最大值函数MAX可确保不出现负的借款额, 也就是如果不需要更多的现金, 则可不必出售所有的投资;当企业存在超额现金和需要归还的未偿付贷款时, 则需要使用最小值MIN函数来完成, 由最小值函数可知 (1) 累计未偿还借款额, (2) 调整前现金余额与最小可接受现金余额间差额之中的最小数。但需注意的是, 为确保正确结果, 特在MIN函数前加了负号。

2. 当期投资的计算。

假如该企业的超额现金已达到最大值, 即40000元, 那么该企业应将这部分现金进行短期投资, 这也是进行当期投资的本质。又因为要考虑该企业当期投资底线的要求, 所以期末现金余额的计算公式应该进行调整, 在D33单元格中输入公式:“=SUM (D30:D31) -D32”, 也就是, 期末现金余额应等于“调整前现金余额+当期借款额-当期投资”。同理, 将该公式复制到E33:H33单元格中去。

设置当期借款额计算公式的目的主要是便于该企业掌握在借款前可先出售那些短期投资项目。如果调整前现金余额与当期借款额之和小于最低现金需求, 那么企业就需要出售部分投资项目。反之, 如果调整前现金余额与当期借款额之和大于最大可接受现金余额, 那么该企业就应该将超额现金余额用于投资。既然这样, 我们还需要在定义公式时用IF语句的嵌套来完成。因此, 在E32单元格中输入当期投资额的公式, 即:“=IF (AND (E30+E31<$B$37, D34<0) , E30+E31-$B$37, IF (E30+E31>$B$38, E30+E31-$B$38, 0) ) ”, 然后将该公式到F32:H32单元格区域中, 如图1。

3. 实例分析。

6月份的调整前现金余额预计为1200元, 小于最小值15000元, 因此该企业需要筹集资金, 若该企业又没有可出售的投资项目, 只能借入13800元来使期末现金余额达到15000元。7月份该企业预计将透支76492元, 但依然没有可出售的投资项目, 因此就需要追加借入91492元, 这时该企业已累计借款达到105292元。8月份该企业预计的调整前现金余额为120798元, 显然超过了最大允许现金额。当然, 在对过剩现金余额进行投资前, 应先偿还现有的短期债务105292元。这样, 该企业可还清全部余额并扔保有高于最低现金需求的现金额。但是, 在偿还贷款后, 现金余额已不足以进行过剩资金投资。9月份该企业预计的调整前现金余额为44856元, 这样, 企业因没有借款余额, 所以超过最大允许现金额的4856元资金就可以用于投资, 而期末余额仍为40000元。若累计借款 (投资) 为负数, 则表明该笔资金为投资。如果我们把表中最大可接受现金额改为15000元时, 就是一个新的方案, 分析方法同上, 在这里就不再分析了。

五、结论

目前Excel作为工具已经普遍被企业所应用, 特别是一些中小企业在管理中应用的更加广泛, 如果让企业利用Excel工具在现金管理方面加以应用, 则本文所应用的关于现金预算的模型设计, 就能够较好地帮助企业充分利用Excel工具, 加强对现金的管理, 提高现金管理的效率。该模型计算步骤清晰, 操作步骤简单, 具有较强的实践操作性。如果作为学生实践环节的模型, 也有利于提高学生的理解分析能力, 理论与实践相结合, 加强了学生的实践动手能力, 也满足了社会对人才的需求。

摘要：现金预算是指用于预测企业还有多少库存现金, 以及在不同时点上对现金支出的需要量。因为涉及到相互关联的科目较多, 现金预算的内容也就相对会复杂一些。如果我们借助Excel来完成现金预算的模型设计, 只要变化预期值就会得到相应的结果, 为企业进行现金预算管理提供方便。同时, 该模型设计也为会计教学提供一个较好的实践教学模板。

关键词：现金预算,Excel,模型设计

参考文献

Excel分析模型 篇10

一、企业年金基金综合测算模型的理论依据及其基本参数的确定

1. 增长年金投资理论。

企业年金基金的保值、增值属于财务管理中增长年金投资理论的范畴。增长年金指的是一定期限内, 一个首期支付既定金额、持续一定期限、每期增长率既定的年金。企业年金基金在其缴费和投资过程中, 其资金的积累计算要运用增长年金的计算公式。根据复利现值理论分析原理, 首期支付为c、投资收益率为r、支付增长率为g、期限为n的增长年金的终值为:

其中:PV为企业年金基金现值, FV为企业年金基金终值, 公式中r≠g。该公式对于计算个人企业年金基金账户余额有重要意义, 是建立企业年金基金综合预测模型的主要公式。

2. 企业年金基金参数确定。

根据上面的企业年金基金计算公式可知, FV为企业年金基金终值、首期支付为c、投资收益率为r、支付增长率为g、期限为n的增长年金是一种“双复利”增长模式。准确确定各参数的取值范围, 对构建企业年金基金测算模型显得十分重要。

(1) 首期支付:c=2 000~2 500元。根据《企业年金试行办法》的规定, 企业缴费率的上限为本企业上年度职工工资总额的十二分之一, 企业缴费基数是本企业上年度职工工资总额;而企业和职工合计缴费的上限为本企业上年度职工工资总额的六分之一, 按规定, 职工缴费基数也应是本人上年度工资, 缴费比率一旦确定, 不能随意更改。根据国家统计局网站2009年4月9日公布的信息, 2008年我国企业在职职工的人均年收入为28 359元, 月平均为2 363元, 按职工上年度职工工资总额的十二分之一缴纳费用计算, 企业年金首期支付额为2 000~2 500元。

(2) 投资收益率:r=6%~8%。投资收益率是最难确定、取值范围最有争议的参数。由于企业年金基金的市场化管理于2005年8月才正式启动, 其平均投资收益率的参考价值不大。劳动和社会保障部养老保险研究室2006年提出, 养老保险资金未来15年的投资收益率最低的要求是年均达到8%。因此在中国目前经济快速发展阶段, 投资收益率选择在6%~8%之间是较为谨慎和合理的。同时也可参考路透社中国年金指数和中信标普推出的年金指数。

(3) 年金投入增长率 (即职工工资年增长率) :g=7.2%。按照十六大提出的国内生产总值到2020年力争比2000年翻两番的目标, 未来的年平均经济增长率为7.2%左右, 考虑到人民生活水平与经济发展水平相一致的原则, 则此时职工的工资增长率应与GDP增长率相等, 即g=7.2%。

(4) 年金基金期限 (职工在企业服务年限) :n=1~36 (年) 。企业建立企业年金制度, 年金基金是按年度缴纳, 与职工为企业服务的年限相同。按照我国目前劳动人事制度方面的法规政策, 员工一般是六十岁退休, 假设该员工二十三岁开始工作 (已正式转正) , 企业为其办理企业年金, 则企业为该名员工缴费年限最长为36年。

(5) 权益归属比例:0~100% (根据员工工龄逐年递增) 。员工在企业不同时期离开企业时, 企业年金基金中企业缴费及其产生的收益, 员工并不一定能全部带走, 而是根据员工在企业持续工作时间的长短来确定相应的归属个人的比例。员工在企业持续工作时间越长, 则归属个人的比例越高, 这从经济上建立起了降低员工流失率的长效机制, 企业年金企业缴费权益归属比例体现出年金计划的“金手铐”作用和强度。

(6) 企业年金服务管理机构收取的相关费用:总计1.8%。根据《企业年金基金管理试行办法》的规定, 受托人费用上限为0.2%, 托管人费用上限为0.2%, 投资管理人可提取的管理费上限为1.2%, 几项费率合计上限为1.8%。

(7) 首期一次性特别缴费:10 000~30 000元。根据联想公司实施的企业年金方案, 对核心技术人才在为其办理企业年金缴费计划时还启动了首期一次性特别缴费, 就是企业还另外拿出资金来奖励核心员工, 提高对优秀人才的吸引力。

二、Excel中企业年金基金综合测算模型的建立

对企业年金基金计算公式及公式中的参数进行确定后, 可利用Excel中的相对引用、绝对引用等功能, 快速建立企业年金基金综合测算模型。具体操作如表所示。

1. 第一行至第四行的内容 (包括数值) 直接输入, 假设员工为企业服务年限为1~36年, A5:A40的单元格输入1~36。权益归属比例根据上面的定义, 在C5:C40单元格输入0~100%。

2. 企业年金测算模型建立如下:第一、二、三行事先录入相关的内容, 员工为企业服务年限为1~36年, 企业缴费归属个人比例的值根据企业人力资源管理规划进行确定。根据上面增长年金理论推导出的公式, 可确定企业年金测算模型各单元格的公式:

只要在单元格中输入以上公式, 分别选中该单元格进行公式引用, 即可完成企业年金基金综合测算模型的编制。

三、企业年金基金综合测算模型的使用方法及注意事项

该模型的使用较为简单明了, 只要根据企业实施年金计划的具体情况调整B3、C3、D3、E3、F3、G3单元格的参数, 即可对测算模型进行数据重算。信息使用者可根据表格中的数据自行插入相关的图表, 使该数据模型更为直观。从表中的数据可知, 若企业实施企业年金制度, 某一男员工从23岁在企业持续工作, 到其退休时 (60岁) , 其年金基金账户的数额将达到981 511.97元。企业年金制度为员工提供了较为丰厚的退休金待遇, 表明企业有较好的员工薪酬福利, 这无异于是企业人力资源管理战略中吸引人才的“金名片”。再结合权益归属比例、员工年金数额、员工离职损失三项参数, 企业年金留住人才的“金手铐”作用一目了然。因篇幅所限, 此处不再赘述。使用该数据模型进行企业年金基金测算的数据仅做参考, 并不作为评判员工个人年金基金账户余额的依据。

参考文献

[1].斯蒂芬.A.罗斯著.吴世龙译.公司理财.北京:机械工业出版社, 2005

[2].杨长汉.中国企业年金投资运营研究.北京:经济管理出版社, 2010

[3].殷俊.中国企业年金计划设计与制度创新研究.北京:人民出版社, 2008

[4].中国养老金网编.中国企业年金规范与发展.北京:中华工商联合出版社, 2007