EXCEL上机操作1文档格式.docx

EXCEL上机操作1文档格式.docx

《EXCEL上机操作1文档格式.docx》由会员分享，可在线阅读，更多相关《EXCEL上机操作1文档格式.docx（13页珍藏版）》请在冰豆网上搜索。

函数练习，结合IF函数完成工资表的计算，即把图1－4中的所需要计算数字填上，个人所得税扣除金额为1600元。

图1－4

练习五：

方案分析练习。

利用方案分析工具完成企业在不同情况下的利润计算。

资料如图1－5所示。

图1－5

计算结果如图1－6所示。

图1－6

练习六：

年金函数、本金函数、利息函数练习。

⑴一名学生在入学时，从银行申请了助学贷款40000元。

按照贷款合同，该生从毕业起10年内分期偿还这笔贷款，每年年终偿还固定金额；

贷款利率均为3％，从该生毕业起开始计息，要求计算该生每年年末须偿还的贷款金额，以及各年偿还金额中本金和利息各为多少。

⑵该项贷款还规定，借款人毕业后可以自己选择每年偿还金额，要求是还款期限必须保证在10年以内且每年偿还进而固定。

假如该生拟每准备用5年的时间偿还，计算为实现该目标该同学每年末应偿还多少金额。

练习七：

单变量求解练习。

父母准备为其子女将来的求学进行储蓄。

他们计划从孩子的12岁起到他17岁，每年年初存入一定金额，从孩子18岁上大学开始到21岁，每年年初支取一定金额作为学费。

首先，假定在孩子12岁到17岁每年存款6000元，利率为3％。

然后，从18岁开始每年年初至取10000元，到21岁的年终。

问到21岁年终时该存款的帐面余额为多少？

如果其他条件不变，要求计算前6年每年存款为多少时，可以到21岁年终收支平衡，即节余金额为零。

练习八：

预测函数、索引函数、窗体工具的练习：

某企业过去12年的销售量与销售成本的历史数据如图1－7所示。

若下一年度的销售量为9500件，分别采用一元一次模型和一元二次模型预测下一年度的销售成本。

图1－7

练习九：

Excel在固定资产更新决策一般模型建立中的应用

㈠模型结构设计

基于以上的考虑，建立一个名为“固定资产更新决策一般模型.xls”的工作簿，在工作簿建两个工作表，一个工作表名为“资料”，另一个名为“决策模型”。

模型由数据区、计算过程区和决策三部分构成。

“资料”工作表是数据区，包括原始数据输入区和参数计算过程区两部分。

如图1所示。

“决策模型”工作表，包括现金流量计算过程区域和决策区两部分。

如图2所示。

根据需要定义合适的行高、列宽，合并单元格，设置表格线，并在有关单元格录入文字、按钮和公式。

原始数据输入区还对新旧设备设置了折旧方法的组合框，以方便选取不同的折旧方法，其中，旧设备的折旧方法组合框设置过程是：

打开【视图】菜单，选择【工具】项，单击【窗体】，在弹出的窗体工具中选取组合框按钮，在B15单元格画组合框，并单击鼠标右键，在弹出的快捷菜单中，单击【设置控件格式】，在控件的数据源区输入“$D$13：

$D$15”，单元格链接为“$B$15”，下拉项数可采用默认数“8”，最后单击【确定】。

新设备的折旧方法组合框的设置过程、数据源区域、下拉项数均和旧设备的相同，新设备组合框的位置在C15，单元格链接为“$C$15”。

图1数据区域

参数计算过程区设计及说明：

⑴新旧设备的使用年限根据决策的需要定义；

⑵新旧设备折旧方法是由设备折旧组合框的选择决定，并通过INDEX函数自动填上，具体操作为，就旧设备来讲，在单元格D5中输入公式“=INDEX（D13:

D15,B15）”，利用索引函数返回由折旧方法组合选取的折旧方法。

在E5单元格输入公式“=IF（$B$15=1,SLN（$B$4,$B$4*$B$5,$B$6）,IF（$B$15=2,SYD（$B$4,$B$4*$B$5,$B$6,E4）,IF（E4<

=$B$6-2,DDB（$B$4,$B$4*$B$5,$B$6,E4）,（$B$4-SUM（$E$5:

$H$5）-$B$4*$B$5）/2）））”，利用IF函数和折旧函数计算通过折旧组合框选取的折旧方法所对应的第1年的折旧额，然后选取单元格E5，将其向右一直填充复制到单元格区域F5:

J5，得到其他年份计算的年折旧额。

新设备的操作是，在单元格D8中输入公式“=INDEX（D13:

D15,C15）”，返回所选取的折旧方法，在单元格E8中输入公式“=IF（$C$15=1,SLN（$C$4,$C$4*$C$5,$C$8）,IF（$C$15=2,SYD（$C$4,$C$4*$C$5,$C$8,E4）,IF（E4<

=$C$8-2,DDB（$C$4,$C$4*$C$5,$C$8,E4）,（$C$4-$E$8-$F$8-$C$4*$C$5）/2）））”，计算新设备第1年的折旧额，再选取单元格E8，将其向右一直填充复制到单元格区域F8:

H8，得到其他年份计算的年折旧额。

选择单元格D10，输入公式“=B12-（B4-（E5+F5+G5））”，计算变卖旧设备产生的损益，用来计算继续使用旧设备的现金流量。

数据区域设置完成后，将新旧设备的原始数据录入所对应的单元格，根据决策要求利用折旧组合框按钮选取对应的折旧方法。

图2计算过程与决策区域

㈡模型的应用

假设某公司有一台设备，购于3年前，先考虑是否更新。

旧设备采用直线法计提折旧，新设备采用年数总和法提折旧，其他资料见图1中的原始数据输入区所示。

1、继续使用旧设备计算过程区建立过程如下：

第一步，选择“决策模型”工作表。

第二步，在单元格B5中输入公式“=资料!

B12”，通过两表之间的连接，取旧设备的变现价值。

第三步，在单元格B6中输入公式“=IF（资料!

D10<

0,ABS（资料!

D10*资料!

B13）,"

-资料!

B13"

）”，利用假设函数和绝对值函数，计算旧设备变现损失产生的现金流量。

第四步，选取单元格区域C7:

F7，并输入公式“=资料!

B9*（1-资料!

B13）”，按数组组合键“Crtl+Shift+Enter”计算各年税后付现成本。

第五步，在单元格D8中输入公式“=资料!

B10*（1-资料!

B13）”，计算第二年税后修理费用支出。

第六步，在单元格F9中输入公式“=IF（资料!

B11>

资料!

B4*资料!

B5,（资料!

B11-资料!

B5）*资料!

B13,0）”，计算残值净收益纳税金额。

第七步，选取单元格区域B10:

F10，并输入公式“=B5:

F5+B6:

F6+B7:

F7+B8:

F8+B9:

F9”，按组合键“Crtl+Shift+Enter”计算各年现金流出合计数。

第八步，选取单元格区域C12:

E12，并输入公式“=资料!

H5:

J5*资料!

B13”，按组合键“Crtl+Shift+Enter”计算各年折旧抵税额。

第九步，在单元格F13中输入公式“=资料!

B11”，取旧设备的残值收入金额。

第十步，选取单元格区域B14:

F14，并输入公式“=B12:

F12+B13:

F13”，按组合键“Crtl+Shift+Enter”计算各年现金流入合计数。

第十一步，选取单元格区域B15:

F15，并输入公式“=B14:

F14-B10:

F10”，按组合键“Crtl+Shift+Enter”计算年现金净流量。

第十二步，选取单元格区域B16:

F16，并输入公式“=B15:

F15/（1+资料!

B14）^决策模型!

B3:

F3”，按组合键“Crtl+Shift+Enter”计算各年现金净流量的现值。

第十三步，在单元格B17中输入公式“=SUM（B16:

F16）”，计算继续使用旧设备各年现金净流量现值的和。

2、更新设备计算过程区建立过程如下：

第二步，在单元格G5中输入公式“=资料!

C4”，通过两表之间的连接，取新设备的投资额。

第三步，选取单元格区域H7:

K7，并输入公式“=资料!

C9*（1-资料!

C13）”，按数组组合键“Crtl+Shift+Enter”计算各年税后付现成本。

第四步，在单元格K9中输入公式“=IF（资料!

C11>

C4*资料!

C5,（资料!

C11-资料!

C5）*资料!

C13,0））”，计算残值净收益纳税金额。

第五步，选取单元格区域G10:

K10，并输入公式“=G5:

K5+G7:

K7+G9:

K9”，按组合键“Crtl+Shift+Enter”计算各年现金流出合计数。

第六步，选取单元格区域H12:

K12，并输入公式“=资料!

E8:

H8*资料!

C13”，按组合键“Crtl+Shift+Enter”计算各年折旧抵税额。

第七步，在单元格K13中输入公式“=资料!

C11”，取新设备的残值收入金额。

第八步，选取单元格区域G14:

K14，并输入公式“=G12:

K12+G13:

K13”，按组合键“Crtl+Shift+Enter”计算各年现金流入合计数。

第九步，选取单元格区域G15:

K15，并输入公式“=G14:

K14-G10:

K10”，按组合键“Crtl+Shift+Enter”计算年现金净流量。

第十步，选取单元格区域G16:

K16，并输入公式“=G15:

K15/（1+资料!

C14）^决策模型!

G3:

K3”，按组合键“Crtl+Shift+Enter”计算各年现金净流量的现值。

第十一步，在单元格G17中输入公式“=SUM（G16:

K16）”，计算更新设备各年现金净流量现值的和。

3、决策区建立过程

第一步，在单元格C20中输入公式“=IF（资料!

B8=资料!

C8,"

相同"

"

不同"

）”，通过IF函数判断新旧设备尚可使用年限是否相同。

第二步，在单元格C21中输入公式“=PMT（资料!

B14,-资料!

B8,B17）”，利用年金函数将旧设备的现值合计换算成等值年成本。

第三步，在单元格D21中输入公式“=PMT（资料!

C14,资料!

C8,-G17）”,利用年金函数将新设备的现值合计换算成等值年成本。

第四步，在单元格C22中输入公式“=IF（C21>

D21,"

继续使用旧设备"

更新设备"

）”，利用IF函数判断方案的优劣。

练习十：

企业四种还贷方法的比较

例：

某企业拟向金融机构贷款100万元，期限5年，贷款利率6％。

试比较该企业分别采用等额摊还法、等额本金法、等额利息法和一次偿付法现金流出额及其现值的大小。

建立比较模型的具体步骤如下：

第一步，建立一个名为“四种还贷方式比较模型.xls”的工作簿，在工作簿中建名为“资料及分析”的电子表。

根据需要设计模型结构，定义合适的行高、列宽，合并单元格，设置表格线，并在有关单元格录入文字和输入已知数据，图1所示。

第二步，在单元格C8中输入公式“=IPMT（$C$4,C7,$C$3,-$C$2）”，计算等额摊还法还款时第1年支付的利息。

第三步，在单元格C9中输入公式“=C8*（1-$G$3）”，计算等额摊还法还款时第1年的税后利息。

第四步，在单元格C10中输入公式“=PPMT（$C$4,C7,$C$3,-$C$2）”，计算等额摊还法还款时第1年偿还的本金。

第五步，在单元格C11中输入公式“=C9+C10”，计算等额摊还法还款时第1年的现金流出金额，即税后利息和偿还本金的和。

图1：

不同贷款偿还方式计算分析图

第六步，在单元格C12中输入公式“=C11/（1+$G$2）^C7”，计算等额摊还法还款时第1年的现金流出金额的现值。

第七步，在单元格C13中输入下面公式，计算采用等额本金法还款时，第1年支付的利息。

公式为“=IF（C7=1,$C$2*$C$4,（$C$2-SUM（B$15:

$C$15））*$C$4）”。

第八步，在单元格C14中输入公式“=C13*（1-$G$3）”，计算等额本金法还款时第1年的税后利息。

第九步，在单元格C15中输入公式“=$C$2/$C$3”，计算等额本金法还款时第1年偿还的本金。

第十步，在单元格C16中输入公式“=C14+C15”，计算采用等额本金法还款时第1年的现金流出金额。

第十一步，在单元格C17中输入公式“=C16/（1+$G$2）^C7”，计算等额本金法还款时第1年的现金流出金额的现值。

第十二步，在单元格C18中输入公式“=$C$2*$C$4”，计算等额利息法还款时第1年支付的利息。

第十三步，在单元格C19中输入公式“=C18*（1-$G$3）”，计算等额利息法还款时第1年的税后利息。

第十四步，在单元格C20中输入公式“=IF（C7=$C$3,$C$2,0）”，计算等额利息法还款时第1年偿还的本金。

第十五步，在单元格C21中输入公式“=C19+C20”，计算采用等额利息法还款时第1年的现金流出金额。

第十六步，在单元格C22中输入公式“=C21/（1+$G$2）^C7”，计算等额利息法还款时第1年的现金流出金额的现值。

第十七步，在单元格C23中输入公式“=IF（C7=$C$3,$C$2*（1+$C$4）^$C$3-$C$2,0）”，计算一次偿付法还款时第1年支付的利息。

第十八步，在单元格C24中输入公式“=C23*（1-$G$3）”，计算一次偿付法还款时第1年的税后利息。

第十九步，在单元格C25中输入公式“=IF（C7=$C$3,$C$2,0）”，计算一次偿付法还款时第1年偿还的本金。

第二十步，在单元格C26中输入公式“=C24+C25”，计算采用一次偿付法还款时第1年的现金流出金额。

第二十一步，在单元格C27中输入公式“=C26/（1+$G$2）^C7”，计算一次偿付法还款时第1年的现金流出金额的现值。

第二十二步，选取单元格区域C8:

C27，将其向又填充复制到单元格区域D8:

G27,计算四种还款方式下其余各年所对应的数值。

第二十三步，选取单元格区域H8:

H27，单击工具栏上的【自动求和】按钮∑，计算每种还款方式下各年支付利息、税后利息、支付本金、现金流出和现金流出现值的代数和。

练习十一：

某企业的某一投资项目，总投资1000万元，寿命期10年，期末固定资产残值回收100万元，流动资金回收150万元，所得税率33％，投资收益率为15％。

投资分2年投入，第0年和第1年分别投资800万元和200万元，第2年和第3年分别投入流动资金100万元和50万元。

项目于第2年投产，年销售收入600万元，年付现成本200万元。

采用直线法折旧、年数总和法、双倍余额递减法计算项目的净现值、内涵报酬率。

练习十二：

王先生刚刚与银行签订了一份商业贷款合同，贷款金额45万元，贷款年利率5.31%，期限30年，每月末等额偿还本金。

几天之后，王先生与一位朋友谈起此事。

他对朋友说，与银行欠下这份贷款合同后，他自己测算了一下，到期支付利息的总额高达45万元，笔贷款的本金多。

朋友提醒他说，如果你选择另外一种还款方式的话，支付的利息总额会少很多。

“怎么还有另外一种还款方式？

”王先生一场惊讶地问。

“应该有的，”朋友告诉他，“银行一般会提供等额摊还法和等额本金法两种方法还款，共借款人选择。

”王先生说，“我去银行申请贷款的时候，银行的工作人员抱来一大堆贷款合同，其中的条款密密麻麻的，然后她帮我一回翻到这里、一会儿翻到那里，指着一些空白的地方让我写，最后让我签上姓名、天上身份证号、加盖名章就完了，好像根本没有提到还有另外一种还款方法。

”

王先生当天回家后，赶紧找处于银行签订的贷款合同，仔细翻了好几遍，发现合同里却有两种还款的方法可供选择，但空白处已经天上了等额本金法。

此后，他又一次找到那位朋友，请他帮忙计算一下，如果套用另外一种还款方法，自己可以少支付银行多少利息。