Excel 在资产管理中的应用.ppt

1、第五章Excel 在资产管理中的应用,第一节 流动资产管理 第二节 固定资产折旧与现金流量 第三节 项目投资评价指标分析 第四节 证券投资分析,第一节流动资产管理,流动资产是指可以在一年或超过一年的一个营业周期内变现或耗用的资产，主要包括现金、有价证券、应收账款和存货等项目。流动资产具有投资回收期短、流动性强、较强的波动性等特点。因此，加强流动资产管理，是企业日常财务管理的重要内容。 一、最佳现金持有量成本分析图表 现金是指在生产过程中暂时停留在货币形态的资金，包括库存现金、银行存款和其他货币资金。现金是变现能力最强的资产，它对增强企业资产的流动性和债务的可清偿性有着重要的作用；但现金属于非盈

2、利资产，持有量过多会导致企业的收益水平降低。所以应确定最佳现金持有量，Excel 中可用成本分析等模式确定最佳现金持有量。,返回,下一页,第一节流动资产管理,【技能储备】 1. Excel中成本分析公式。成本分析模式是根据现金有关成本，分析预测其总成本最低的现金持有量的一种方法。这种模式下的最佳现金持有量，就是持有现金而产生的机会成本与短缺成本之和最小时的现金持有量。 （1）机会成本是指因持有现金而丧失的再投资收益，它与现金持有量成正比例关系，用公式表示如下： 机会成本=现金持有量有价证券年利率或再投资报酬率 （2）短缺成本是指在现金持有量不足而又无法及时通过有价证券变现加以补充而给企业造成的

3、损失，包括直接损失与间接损失。它与现金持有量成反向变动关系，一般根据企业的历史资料、融资环境等因素加以确定。,返回,上一页,下一页,第一节流动资产管理,2. Excel 中可使用图表形象直接的反映经济数据的含义、关系或规律等。Excel 提供了标准类型和自定义类型两类图表，如图 51 所示。标准类型图表有柱形图、气泡图、棱锥图等 14 类图表，每类中又有若干子图表。选定某类图表，该界面右部将显示其下的子图表，图表可用绝对数（直引用的数值）显示，也可用相对数（百分比）显示，图表可用二维图形表示，也可进行三维立体显示。选定子图表后，其下部将显示该图表的名称或适用范围等；若单击“按下不放可查看示例”

4、按钮，可直观的查看成图后的效果。,返回,上一页,下一页,第一节流动资产管理,3. Excel 中分析最佳现金持有量时，可使用相对位置函数 MATCH 查找目标单元格所在的相对行数、列数；使用数组 INDEX 函数引用某单元格的值；使用极值 MIN 函数查找最小值等。这些函数的使用方法参见第 4 章。 4. Excel中为了便于分析不同数值水平下的数据状况，可使用窗体工具栏的相关功能，使用方法参见第 4 章。 【案例 51】某公司根据历年的资料估计，未来现金持有量可能为 20 万元、30 万元、40万元和 50 万元，其对应的短缺成本为 4 万元、3 万元、2 万元和 1 万元。若现金持有量的机

5、会成本在8%16%之间，请分析该公司最佳现金持有量，并用图表反映相关总成本构成情况。,返回,上一页,下一页,第一节流动资产管理,【操作提示】 在 Excel 中完成分析计算后的界面如图 52 所示。该表的使用方法是，单击第 2 行中的滚动条，则 F2、C6:F8 单元格中的数据随之变动，C9、C10 单元格中的提示及时更新。下部的图形亦随之改变形状，其中，柱形图区分方案、用颜色反映短缺成本、机会成本与总成本情况；图 52 的柱形图是再投资收益率为 16%时的状况（滚动条的滑块处于最右部），而图 53 的柱形图是再投资收益率为 8%时的状况（滚动条的滑块处于最左部）。为了达到这种效果，应按以下方

6、法在 Excel 中进行操作。 （1）设计表格。如图 53 所示。,返回,上一页,下一页,第一节流动资产管理,（2）设计滚动条与代码取值的方法如下： a.通过菜单“视图/工具栏/窗体”命令调出窗体工具栏。 b.单击窗体工具栏中的滚动条按钮，在 D2:E2 单元格中拖动一个适当的滚动条控件。 c.右击工作表中的滚动条进入“设置控件格式”界面，在最小值、最大值、步长、页步长中录入“8”、“16”、“1”、“2”，在单元格链接中输入“$D$2”（绝对引用 D2），并勾选“三维阴影”；单击“确定”按钮回到工作表中，D2 单元格显示最小代码值“8”。 d.在 F2 单元格中输入“=D2/100”，再单击

7、工具栏的百分比 按钮，将 D2 中的代码转换为百分数表示；然后将 D2 单元的对齐方式设置为居中，以隐藏设计代码。,返回,上一页,下一页,第一节流动资产管理,（3）录入成本计算公式。在C6单元格中按前述机会成本的公式输入“=C4*$F$2”（F2要绝对引用）；在 C7 单元中输入“=C5”；在 C8 单元格中输入“=C6+C7”；然后选定 C6:C8单元格区域，自动填充 D6:F8 单元格区域的公式。 （4）用 MIN函数计算最小成本。在C9 单元格中输入“=MIN(C8:F8)”；也可按第4章介绍的方法，通过插入函数的方式选择“统计”类别中的 最小值 MIN函数，进行向导设置。 （5）将相对

8、位置 MATCH 函数作为数组单元格取值 INDEX 函数的嵌套函数，进行方案择优，即在 C10 单元格中输入嵌套函数公式“=INDEX(C3:F3,MATCH(C9,C8:F8,0)”。,返回,上一页,下一页,第一节流动资产管理,（6）插入图表的方法如下。 a.选定要制作图表的数据源区域。选定 B3:F3，按下键盘上的 CTRL 键选定 B6:F7。 b.选择图表类型。如图 54 所示。 c.向导 2 中的上部有两个页签和所选数据源区域的图形效果。在“数据区域”页签将显示所选的单元格区域“=现金成本!$B$3:$F$3,现金成本!$B$6:$F$7”，其中“现金成本!” 表示引用区域所在的工

9、作表，“$B$3:$F$3”表示绝对引用 B3:F3；在此可确定系列产生在行还是在列；在“ 系列”页签中，如图 55 所示，列出了所选区域中的系列名称、引用单元格，以及分类轴引用的单元格。若所选区域不当或对图表不满意，可在此单击引用重新选择。,返回,上一页,下一页,第一节流动资产管理,d.图表向导 3 中有标题、图例、数据表等 6 个页签，如图 56 所示。在“标题”页签中可输入图表上方要显示的标题名称、左部要显示的 X 轴名称、下部要显示的 Z 轴名称，以便在坐标轴中用文字标识其含义、数值等（本例不必输入）。其他页签的设置内容如图 57 所示。在“数据标志”页签可确定标签包括的内容、分隔符等

10、。在“数据表”页签可确定在图形中是否显示数据表等。 e.在图表向导 4 中，应确定创建的图形放入何种工作表中，如图 58 所示。在此可新建一个工作表，也可插入选定单元格区域所在的工作表中，还可插入本工作簿的其他工作表中。,返回,上一页,下一页,第一节流动资产管理,（7）选定图表或对象。创建图表的外观可能不符合设计者的要求，可进行修饰，图表中有图表区、图表对象等，鼠标指向某对象时将会有该对象的名称等提示信息；要对插入的图表进行修改、修饰，必须先将其选定。选定方法如下。 a.单击选定。如选定图表区的方法是，单击图表中的边框线使四周出现 8 个小黑方块，同时上部单元格名称框中出现“图表区”字样，说明

11、选定成功，用类似的方法选定图例。2 个小黑方框，而“系列”（本例的立体柱）选定后显示的是 4 个反底色小方块（如图 59 所示）等。 b.工具选定法。如图 510 所示。,返回,上一页,下一页,第一节流动资产管理,（8）修饰图表时，修改修饰的方法如下。 a.选定图表对象后，若需对位置、大小等进行调整，可直接用鼠标进行拖动调整。 b.进行精确设置则可右击该对象，在环境菜单中选择相应的命令，进行不同内容的修改。可进行数据系列格式的设置、重选图表类型、修改数据源等操作，如选择其中的“源数据”命令则进入 “图表源数据”界面，进行数据区域、系列的修改等。 c.还可通过图表工具栏的相关按钮，选定相关对象进

12、行修改、修饰。,返回,上一页,下一页,第一节流动资产管理,二、最佳现金持有量的存货模式分析 企业确定最佳现金持有量时可用存货模式进行分析计算。存货模式的着眼点是现金相关总成本最低，但它只对机会成本和固定转换成本予以考虑，而不考虑短缺成本。该方法下的最佳现金持有量是指能够使现金管理的变动性机会成本与固定性转换成本之和保持最低的现金持有量。机会成本的含义与计算见前述；固定转换成本是指企业用现金购入有价证券以及转让有价证券换取现金时付出的固定性交易费用，即现金与有价证券之间相互转换的固定成本，如证券过户费、实物交割费等。,返回,上一页,下一页,第一节流动资产管理,【技能储备】 1. Excel中进行

13、存货模式的最佳现金持有量分析时，应具备相应的条件，如企业所需要的现金可通过证券变现取得，且变现的不确定性很小；预算期内的现金需求总量可以预测；现金的支出过程比较稳定、波动较小，而且每当现金余额降至零时，均可通过证券变现得以补足；证券的利率或报酬率以及每次固定性交易费用可以获悉等。 2. Excel 中可使用以下公式计算最佳现金持有量： 式中，Q 表示最佳现金持有量；T 表示一个周期内现金总需求量；F 表示每次转换有价证券的固定成本；K 表示有价证券年利率（机会成本）。 3. Excel的单元格公式是为了进行数值、字符等的计算而设置的；如图 512 所示。,返回,上一页,下一页,第一节流动资产管

14、理,【案例 52】某公司根据历史资料分析，全年的现金需求量可能在 4 000 万8 000 万元之间，每次证券固定转换成本在 400800 元之间，证券年利率在 6%12%之间。请计算以上情况下该公司的最佳现金持有量。 【操作提示】 （1）设计表格。在 A1:A5 单元格区域中录入相关文字；合并 A1:C1 单元格区域；设置字体、字号、边框等。 （2）Microsoft 公式 3.0 的输入方法。其输入方法如下。 a.安装图片公式的工具栏。 b.选择菜单“插入/对象”命令，如图 514 所示。,返回,上一页,下一页,第一节流动资产管理,c.在光标闪烁的虚框中输入大写的“Q”及“=”号；然后选定

15、“公式”工具栏“分式和根式模板”中的开方按钮，再输入“2TF/K”，即可完成 B5单元格中文本公式的输入；输入完毕后在工作表空白处单击，以完成公式输入。 d. 可双击公式，调出“公式”工具栏进行修改。 （3）滚动条的设置与代码取值，方法如下： a.单击窗体工具栏上的滚动条按钮，在 B2 单元格中拖动一个滚动条控件；然后复制并粘贴到 B3、B4 单元格中。,返回,上一页,下一页,第一节流动资产管理,b.右击 B2 单元格中的滚动条进入“设置控件格式”界面，将最小值、最大值、步长、页步长设置为“400”、“800”、“5”、“10”，将单元格链接到“$B$2”；将 B3 中的控件格式的值设置为“4

16、00”、“800”、“10”、“100”，单元格链接到“$C$3”；将 B4 中的控件格式的值设置为“6”、“12”、“1”、“2”，单元格链接到“$B$4”。 c.在 C2 单元格中输入“=B2*100000”，将代码值转换为资金需要量；在 C4 单元格中输入“=B4/100”，将代码值转换为百分比。然后采用水平居中的方式将 B2、B4单元格中的代码值隐藏于滚动条后部。 （4）在 C5 单元格中输入开平方函数公式“=SQRT(2*C2*C3/C4)”。,返回,上一页,下一页,第一节流动资产管理,三、信用天数动态分析图表 企业赊销产品是一种重要的促销手段，对加速产品销售的实现，开拓并占领市场，

17、减少存货数量以降低存货管理费、仓储费、保险费等具有重要的意义。企业赊销的同时，会因持有应收账款而付出一定的代价，即应收账款的成本。故此，企业应制定合理的信用政策，加强应收账款管理，提高应收账款的投资效益。应收账款管理中很重要的一项是对信用天数的决策，即赊销商品后延迟的收款天数，它一般用“N/30”、“N/90”等形式反映，其中 N 表示 没有现金折扣，30、90 表示赊销后的最迟付款天数（即信用期限）。,返回,上一页,下一页,第一节流动资产管理,【技能储备】 1. Excel中分析信用天数时，应根据不同信用天数的赊销方案，计算信用前收益、相关成本、信用后收益；然后以信用后收益最大的方案作为最优

18、方案。其中相关成本包括机会成本、坏账费用、收账费用。它们的计算方法如下。 （1）机会成本。机会成本是指因资金投放在应收账款上而丧失的再投资收益。它应根据应收账款平均收款天数计算应收账款的平均余额，再乘以变动成本率计算出应收账款占用的资金额，然后乘以机会成本率计算出机会成本。计算公式如下： 应收账款平均余额 =全年赊销额360平均收账天数 应收账款占用资金额 =应收账款平均余额变动成本率 机会成本 =应收账款占用资金额机会成本率,返回,上一页,下一页,第一节流动资产管理,（2）坏账损失。因应收账款无法收回而产生的损失，称为坏账损失。它与赊销额的大小成正比，所以可用以下公式计算： 坏账损失额 =全

19、年赊销总额坏账损失率 （3）收账费用。企业对拖欠的应收账款，应采用一定的方式进行催收，需要支付邮电通讯费、催款差旅费、法律诉讼费等收账费用。赊销期限越长，收账费用越大。它一般根据企业、客户、市场环境等实际情况进行估计。 （4）信用前后收益。它们的计算公式如下： 信用前收益 =年赊销收入 (1变动成本率) 信用后收益 =信用前收益机会成本坏账损失收账费用,返回,上一页,下一页,第一节流动资产管理,2. Excel 分析信用天数时，可使用最大值 MAX 函数、相对位置查找 MATCH 函数、数组INDEX 函数，以及取右部字符 RIGHT 函数等。其中取右部字符 RIGHT 函数公式如下：= RI

20、GHT(Text,Num_chars) 式中，Text 表示要提取字符的字符串；Num_chars 表示要提取的字符个数，忽略则取 1。 【案例 53】根据历史资料，某公司变动成本率为 50%75%；机会成本率为 8%20%；为了加强赊销管理，提出了 A、B、C 三套方案。A 方案信用条件为 N/30，估计年销售 3 600万元，坏账损失率为 2%，收账费用为 38 万元；B 方案为 N/60，估计年销售 3 960 万元，坏账损失率为 3%，收账费用为 60 万元；C 方案为 N/90，估计年销售 4 200 万元，坏账损失率。,返回,上一页,下一页,第一节流动资产管理,（1）设计表格。输入

21、相关文字、已知数据；合并相关单元格；设置字体字号、行高列宽等。 （2）设计滚动条与取值。在窗体工具栏上单击滚动条按钮，在 D2、D3 单元格中拖动创建两个滚动条。右击 D2 单元格中的滚动条进入“设置控件格式”界面，在最小值、最大值、步长、页步长中输入“50”、“75”、“5”、“10”，在单元格链接中输入“$D$2”。D3 中滚动条的最小值、最大值、步长、页步长应输入“8”、“20”、“1”、“2”，在单元格链接中输入“$D$3”。在 E2 单元格中输入“=D2/100”，在 E3 单元格中输入“=D3/100”，以便将代码值转换为百分比格式；然后将 D2、D3 单元格的代码值居中隐藏。,返

22、回,上一页,下一页,第一节流动资产管理,3）计算信用前收益。在 D9 单元格中按信用前收益的公式输入“=D6*(1$E$2)”，E2 单元格要绝对引用；然后自 动填充 E9、F9 单元格的公式。 （4）用“取右部字符 RIGHT 函数”计算应收账款平均 收款天数，方法如下。 a.选定 D11 单元格，图 516 所示。 b.在没有现金折扣的情况下，信用天数即为平均收款天数，所以 A 方案的天数为 D5 单元格中的后两个字符，也即是说要提取的字符串为 D5 单元格，要提取的是该单元格右部的 2个字符数；所以在 Text 参数中输入“D5”，在 Num_chars 参数中输入“2”。 c.单击确定

23、按钮回到工作表中，D11单元格中将显示为“30”，工作表上部编辑框中将显示字符运算公式“=RIGHT(D5,2)”。,返回,上一页,下一页,第一节流动资产管理,d.采用自动填充的方式填入 E11、F11 单元格的字符运算公式。 （5）机会成本的计算。在 D12 单元格中按应收账款平均余额公式输入“=D6/360*D11”；再自动填充E12、F12单元格公式。在D13单元格中按应收账款占用资金额的公式输入“=D12*$E$2”，E2 单元格要绝对引用；再自动填充 E13、F13 单元格公式。在 D10 单元格中按机会成本的公式输入“=D13*$E$3”，E3 单元格要绝对引用；再自动填充 E10

24、、F10 单元格公式。 （6）计算信用后收益。在 D14 中按坏账损失公式输入“=D6*D7”，再自动填充 E14、F14单元格公式。在 D15 单元格中输入“=D8”，再自动填充 E15、F15 单元格公式。在 D16 单元格中输入公式“=D9D10D14D15”，再自动填充 E16、F16 单元格公式。,返回,上一页,下一页,第一节流动资产管理,（7）择优。用最大值 MAX 函数确定最大收益值，即 D17“=MAX(D16:F16)”。用相对位置 MATCH 函数返回最大值所在的单元格相对列数；然后作为数组 INDEX 的 嵌套函数，即根据最大值的相对位置引用其对应于 D4:F4 单元格区

25、域中的方案名称；这两个函数的使用方法，参见第 4章。D18单元格中的嵌套函数为“=INDEX(D4:F4,MATCH(D17,D16:F16)”。 （8）插入三维堆积条形图，方法如下。 a. 选定 B16:F16 单元区域。 b.单击插入图表按钮进入图表向导界面，如图 51 所示，选择“条形图”中的“三维堆积条形图”，除了在“图表标题”框中输入“信用后收益比较”，在图例中取消“显示图例”。,返回,上一页,下一页,第一节流动资产管理,完成图表插入后，在图表的上部将显示其标题名称，图表中将不会出现图例（本例仅有“信用后收益”一个系列，不必用颜色和图例区分不同的系列）。 c.修饰图表。插入图表的方案

26、名称由上到下是 C 方案、B 方案、A 方案，不符合阅读习惯。可右击图表左部分类轴名称选择“坐标轴格式”命令进入“坐标轴格式”界面，如图 517所示，在该界面的“刻度”页签中勾选“分类次序反转”复选框，即可将A、C 方案的位置对换。再通过鼠标拖动等方式调整图表的大小、位置等，或通过右击选择环境菜单中的命令进行图表对象的修改、修饰。,返回,上一页,下一页,第一节流动资产管理,四、现金折扣动态分析图表 应收账款管理中，对信用条件的分析具有十分重要的作用。信用条件是指企业接受客户信用订单时所提出的付款要求，主要包括信用期限、折扣期限和现金折扣率等；它一般用“2/30、N/60”的形式来表示，其中 2

27、 表示现金折扣率为 2%，30 表示折扣天数，60 表示信用天数；即若客户在 30 天内付款，可以享受 2%的现金折扣，如果放弃折扣，则全部款项必须在 60 天内付清。 【技能储备】 1. Excel中现金折扣分析与信用天数的分析类似，现金折扣成本计算公式如下： 现金折扣=(赊销额享受折扣的客户比率现金折扣率),返回,上一页,下一页,第一节流动资产管理,2. Excel中现金折扣分析时，也应根据顾客是否享受现金折扣计算平均收款天数。例如：某公司预测全年赊销额为 600 万元，信用条件为 2/20、1/60、N/90，估计有 60%的顾客享受 2%的现金折扣、10%的顾客享受 1%的现金折扣。则

28、： 现金折扣=6002%60%+6001%10%=7.8（万元） 平均收款天数=2060%+6010%+90(160%10%)=45（天）,返回,上一页,下一页,第一节流动资产管理,3. Excel 的字符取数函数。Excel 可用取右字符函数 RIGHT 从右部起取指定位数的字符数，用取左字符函数 LEFT 从左部起取指定位数的字符数，用取指定位置字符函数 MID 取特定位置的字符数，用字符计数函数 LEN 检测字符的长度值。它们的函数公式如下： = MID(Text,Start_num,Num_chars) = LEFT (Text,Num_chars) = LEN(Text) 式中，te

29、xt 表示要进行运算的字符表达式，1 个空格也将作为 1 个字符，若为文本型数据应用半角双引号进行引用；Num_chars 表示要提取的字符数，它必须大于或等于 0，如果省略则取 1；Start_num 表示要提取的第一个字符的位置。,返回,上一页,下一页,第一节流动资产管理,【案例 54】某公司近年赊销额为 4 000 万6 000 万元，变动成本率为 50%75%，机会成本率为 8%20%；为了加强收款管理，提出了 A、B 两套方案。A 方案的信用条件是 N/60，估计坏账损失率为 3%，收账费用为 65 万元；B 方案的信用条件为 2/20、N/60，估计有 70%的顾客会享受此项折扣优

30、惠，坏账损失率为 1.5%，收账费用为 45 万元。请为该公司进行决策分析。 【操作提示】 （1）设计表格。录入 A、B 列单元格文字，合并单元格区域，录入 C5:D9 单元格区域的已知数据，调整行高列宽、设置字体字号，如图 518 所示。,返回,上一页,下一页,第一节流动资产管理,（2）设计滚动条与代码取值。在窗体工具栏中单击滚动条动一个滚动条控件，并将其复制粘贴到 C3、C4 单元格中。右击C2中的滚动条进入“设置控件格式”界面，在最小值、最大值、步长、页步长中输入“4000”、“6000”、“100”、“300”，在单元格链接中输入“$D$2”；C3单元格中滚动条的值分别为“50”、“7

31、5”、“1”、“10”、“$C$3”；C4 单元格中滚动条的值分别为“8”、“20”、“1”、“2”、“$C$4”。在 D3 单元格中输入“=C3/100”，在 D4 单元格中输入“=C4/100”，以便将代码值转换为百分比数值；然后将 C3、C4 中的代码值居中隐藏。 （3）方法如下。 a.在 D12 单元格中单击插入函数数，进入函数参数界面，如图 519 所示。,返回,上一页,下一页,第一节流动资产管理,b.由于现金折扣率为D6单元格左部的第1个字符，所以Text参数中输入“D6”，在Num_chars 参数中输入“1”。 c.单击“确定”按钮后回到工作表，上部编辑框中的公式为“=LEFT

32、(D6,1)”，D6单元格的值为 2；所以还应将公式取值除以 100 使其转换为百分比格式。 （4）计算信用前收益与现金折扣。在 C10 单元格中输入“=$D$2*(1$D$3)”，C11 单元格中输入“=$D$2*C7*C12”；然后自动填充 D10、D11 单元格公式。 （5）方法如下： a.在 D14 单元格中单击插入函数数参数界面，如图 520 所示。,返回,上一页,下一页,第一节流动资产管理,b.由于折扣天数为 D6 单元格的第 3、第 4 这两个字符；即字符的起始位置为 3、要取 2个字符。所以在 Text 中输入“D6”，在 Start_num 中输入“3”，在 Num_char

33、s 参数中输入“2”。 c.单击“确定”按钮后回到工作表中，上部编辑框中显示公式“=MID(D6,3,2)”，而 D14单元格中显示取值为“20”。 （6）用取右字符 RIGHT 函数计算信用天数，在 C15 单元格中输入“=RIGHT(C6,2)”，然后自动填充到 D15 单元格中。 （7）机会成本的计算。按平均收款天数公式在 C16 单元格中输入“=C7*C14+(1C7)*C15”，在 C17 单元格中输入应收款平均余额公式“=$D$2/360*C16”，在 C18 单元格中输入赊销占用资金公式“=C17*$D$3”，再自动填充 D16:D18 单元格公式。,返回,上一页,下一页,第一节

34、流动资产管理,（8）计算信用后收益。在 C19 单元格中输入坏账损失公式“=$D$2*C8”，在 C20 中输入收账费用“=C9”，在 C21 中输入信用后收益公式“=C10C11C13C19C20”；然后自动填充 D19:D21 的单元格公式。 （9）方案择优。在 C22 中输入函数公式“=MAX(C21:D21)”，以计算信用后收益的最大值。在 C23 单元格中用嵌套函数确定最优方案，即先用相对位置 MATCH 函数查找最大值所在的相对列数，再用数组 INDEX 函数根据最大值的相对列数查找 C5:D5 单元格区域的对应单元格，并返回该单元格的值，其嵌套函数为“=INDEX(C5:D5,M

35、ATCH(C22,C21:D21)”。 （10）插入百分比堆积柱形圆柱图，方法如下。,返回,上一页,下一页,第一节流动资产管理,a.选定 B5:D5、B11:D11、B13:D13、B19:D19、B20:D20 单元格区域作为源数据区。 b.单击插入图表按钮进入图表向导界面，如图 51 所示。 c.修改数据标志值的显示。默认情况下，数据标志值置于不同颜色的系列（圆柱条图）上并以黑色显示，但系列为黑色时将无法辨识，所以可将其拖动到图形的空白处。方法是单击圆柱图上的数值，此时数据左右将显示两个小黑块，表示该对象被选中，然后将该数值拖动到合适的位置即可。 d.修饰图表。通过鼠标拖动等方式调整图表对

36、象的大小、位置等，也可通过右击选择环境菜单中的命令，或图表工具栏进行图表对象的修改、修饰。,返回,上一页,下一页,第一节流动资产管理,五、经济进货批量动态模型 经济进货批量是指能够使一定时期存货的相关总成本达到最低点的进货数量。变动的进货费用如进货差旅费、邮资、电话电报费等，与进货次数成正比例变动关系；变动的储存成本如存货占用资金的利息费、存货残损和变质损失、存货仓储保险费等，与存货的储存数量成正比例变动（与进货次数成反比例变动）关系；这两者是进货决策主要的相关成本。所以，在不允许缺货的情况下，与进货批次、进货批量相关的成本是变动进货费用和变动储存成本。,返回,上一页,下一页,第一节流动资产管

37、理,【技能储备】 1. Excel 中进行经济进货批量基本模式的分析。 2. Excel中可用条件格式对单元格或单元格内的数据、单元格区域或区域内的数据进行强调、警示等，以便引起足够的重视。所谓条件格式是指当指定的条件为真时，Excel 自动在单元格中按该条件设置的格式进行显示，例如，单元格底纹或字体颜色等。,返回,上一页,下一页,第一节流动资产管理,【案例 55】由于未来经济环境的不确定性，某公司甲材料的全年需求量为 40 00080 000千克，单位采购成本为 5070 元，每次固定进货费用 400800 元，单位年储存成本为 2030 元。请计算甲材料的经济进货批量、相关成本、平均占用资

38、金、全年最佳进货次数、每次进货间隔天数；相关总成本大于或等资金占用总额、进货间隔天数小于 10 天时，用不同的颜色进行警示。 【操作提示】 （1）设计表格。录入单元格文字，用“Microsoft 公式 3.0”工具栏输入 C6、C7 单元格的文本公式，合并单元格区域，调整行高列宽、设置字体字号。 （2）设计滚动条与代码取值。单击窗体工具栏上滚动条按钮并在C2单元格中拖动一个滚动条控件，然后将其复制粘贴到 C3、C4、C5 单元格中。,返回,上一页,下一页,第一节流动资产管理,右击 C2 单元格滚动条进入“设置控件格式”界面，设置最小值“400”、最大值“800”、步长“50”、页步长“100”

39、，单元格链接“$C$2”；同样的方法，C3 单元格滚动条的值别为“50”、“70”、“2”、“5”、“$D$3”；C4 单元格滚动条的值分别为“400”、“800”、“50”、“100”、“$D$4”；C5 单元格滚动条的值分别为“20”、“30”、“1”、“2”、“$D$5”。在 D2 单元格中输入“=C2*100”，将代码值转换为数值，居中隐藏 C2 中的代码值。 （3）计算相关指标。在 D6 单元格中按经济进货批量公式输入“=SQRT(2*D2*D4/D5)”；在 D7 单元格中按相关总成本公式输入“=SQRT(2*D2*D4*D5)”；在 D8 单元格中按平均占用资金公式输入“=D3*

40、D6/2”；在D9单元格中按最佳进货次数公式输入“=D2/D6”；在D10单元格中按进货间隔天数公式输入“=360/D9”。,返回,上一页,下一页,第一节流动资产管理,（4）用条件格式进行单元格的颜色警示，方法如下。 a.选定 D7 单元格，选择“格式/条件格式”命令进入“条件格式”界面，如图 522 所示。 b.在条件中选择“单元格数值”、“大于或等于”、“=$D$8”；再单击右部的“格式”按钮进入“单元格格式”界面，如图 523 所示。 c.在单元格格式界面的“图案”页签中选择合适的颜色，单击“确定”按钮回到“条件格式”界面，再单击“确定”按钮回到工作表中。 d.重复以上 a、b、c 三步

41、骤，为 D10 单元格设置条件格式（颜色警示）。,返回,上一页,下一页,第一节流动资产管理,六、存货储存天数动态分析 存货日常管理的目标是保证企业生产经营正常进行的前提下尽量减少库存，防止积压。实践中形成的行之有效的管理方法有存货储存天数控制、存货 ABC 分类管理、存货定额控制、存货供应时点控制等多种方法。本书重点介绍存货储存天数的分析。 【技能储备】 1.存货管理成本的计算。它们的计算公式如下： 固定成本总额 = 1 次性费用 +销售税金及附加 式中，每日变动储存费率可用“流动资金年贷款利率360+月仓储费用率30”计算；由于购进货物后需支付增值税款，所以投资于存货上的资金需加上增值税，并

42、据此计算储存费。,返回,上一页,下一页,第一节流动资产管理,2. Excel中对存货购销类型的处理。企业购进的存货进行销售，可能整进整出（如直运业务、批发业务等），整进均匀零出（如零售、超市等），也可能无规律的进出等。购销类型要影响到存货储存占用资金额、每日的变动储存费用等；一般来说，整进整出的调整系数为1、整进均匀零出的调整系数为 0.5，因为整进均匀零出的资金占用、日变动储存费为整进整出的50%。Excel中对存货购销类型的选定，可采用窗体工具栏中的单选按钮、列表框等进行处理。 3.存货储存保本保利分析。存货保本保利一般用以下指标分析：,返回,上一页,下一页,第一节流动资产管理,目标利润

43、=购货数量进价(1+增值税率) 投资收益率调整系数 保利天数 = (毛利总额固定成本目标利润) 日均变动储存费 保本天数 = (毛利总额固定成本) 日均变动储存费 实际盈亏 = (实际储存天数保本天数) 日均变动储存费 式中，毛利总额 = (单位售价单位进价) 购进批量。 【案例 56】某公司准备购进 4 000 件甲产品，单位进价 150 元，单位售价 180 元，增值税率为 17%；一次性费用 50 000 元，销售税费 5 600 元。经测算年贷款利率 8%20%，存货月保管费率 310，期望投资净收益率 4%10%。,返回,上一页,下一页,第一节流动资产管理,要求：区分该公司为均匀出售

44、的超市、整进整出的批发公司，计算下列指标：（1）保本天数；（2）保利天数；（3）实际储存了 23 000 天时的盈亏情况。 【操作提示】 （1）设计表格。录入 A1、A2、A13、A17、B2:B19 单元格的文字；录入 D3:D8 单元的已知数值；合并相关单元格区域。如图 524 所示。 （2）设计单选按钮及代码取值，方法如下（可参见第 4 章介绍）。 a.通过菜单“视图/工具栏/窗体”命令调出窗体工具栏，用单选按钮在 C2 单元格中拖动出两个单选按钮；右击该按钮将其标签修改为“整进整出”、“整进均匀零出”。 b.右击单选按钮进入“设置控件格式”界面，在单元格链接中输入“$C$2”。,返回,

45、上一页,下一页,第一节流动资产管理,c.由于整进零出的日均费用、占用资金额均是整进整出的一半，所以在 D2 单元格中输入条件函数公式“=IF(C2=1,1,0.5)”， （3）设计滚动条与代码取值，设置方法如表 51 所示。 （4）输入计算分析公式。按毛利总额公式在D13单元格中输入“=D3*(D5D4)”；在工作表的 D14 单元格中输入固定成本公式“=D7+D8”；在 D15 单元格中在输入日均变动储存费用的计算公式“=D3*D4*(1+D6)*D2*(D9/360+D10/30)”；在 D16 单元格中输入目标利润的计算公式“=D3*D4*(1+D6)*D2*D11”；在 D17 单元格

46、中输入保本天数公式“=(D13D14)/D15”； 在 D18 单元格中输入保利天数公式“=(D13D14D16)/D15”；在 D19 单元格中输入实际储存 天数盈亏公式“=D15*(D17D12)”。,返回,上一页,下一页,第一节流动资产管理,（5）用条件格式对单元格内部的字体进行强调提示，方法如下。 a.选定 D19 单元格，如图 525 所示。 b.在条件 1 中选择小于 0；单击“格式”按钮进入单元格格式界面。c.单击“添加”按钮，在条件 2 中选择介于 0 与“=$D$16”；单击“格式”按钮进入单元格格式界面，选择“字体”页签中的字形为“加粗”、颜色为“金色”，单击“确定”按钮回

47、到条件格式界面。 d.单击“添加”按钮，在条件 3 中选择大于“=$D$16”；单击“格式”按钮进入单元格格式界面，选择“字体”页签中的字形为“加粗”、颜色为“蓝色”，单击“确定”按钮回到条件格式界面。,返回,上一页,第二节固定资产折旧与现金流量,一、固定资产折旧计算 固定资产是指使用期限较长、单位价值较高，在使用过程中保持原有实物形态的长期资产。在会计核算、财务管理中均需对固定资产的支出进行资本化处理，然后在规定的使用年限内进行折旧。固定资产的折旧方法主要有平均年限法、年数总和法、双倍余额递减法、工作量法。本书介绍前三者在 Excel 中的计算方法。 【技能储备】 1.平均年限法又称使用年限

48、法，是将固定资产的折旧额均衡地分摊到固定资产折旧年限的各个会计期间的一种方法。Excel 中它可用数学公式、也可用函数公式计算其年折旧额。,返回,下一页,第二节固定资产折旧与现金流量,（1）Excel 数学公式计算法。其主要计算公式如下： 月折旧额 =年折旧额12 年折旧额 = (固定资产原值预计净残值) 预计使用年限 （2）Excel 函数公式计算法。Excel 提供了线性折旧函数 SLN 来计算平均年限法的折旧额，其年折旧额函数公式如下：= SLN(Cost,Salvage,Life) 式中，Cost 表示固定资产原值；Salvage 表示固定资产在折旧期末的价值（也称净残值）；Life表

49、示折旧期限（也称固定资产的使用寿命）。 2.双倍余额递减法，是在不考虑净残值的情况下，以固定资产的期初账面余额为折旧基数，以直线法折旧率的 2 倍作折旧率来计算各期折旧额的方法。Excel 中它可用数学公式、也可用函数公式计算双倍余额递减年折旧额。,返回,上一页,下一页,第二节固定资产折旧与现金流量,（1）Excel 数学公式计算法。其年折旧额计算公式如下： 年折旧额 =期初固定资产账面净值年折旧率 年折旧率 = 2预计使用年限 在双倍余额递减法下，应在折旧年限到期前两年内，将固定资产净值扣除预计净残值后的净额平均摊销。其计算公式如下： 最后两年的年折旧 = (固定资产原值累计折旧预计净残值)

50、 2 （2）Excel 函数公式计算法。Excel 提供了余额递减折旧函数 DDB，计算双倍、多倍余额递减法指定各期的折旧额（不含最后 2 年），其年折旧额函数公式如下： = DDB(Cost,Salvage,Life,Period,Factor),返回,上一页,下一页,第二节固定资产折旧与现金流量,式中，Period 表示需要计算折旧额的期间，它必须使用与 Life 相同的单位；Factor 表示余额递减速率，如果省略则为 2（双倍余额递减法）。其他参数的含义与平均年限法相同。 3. Excel 中可用数学公式、也可用函数公式计算年数总和法年折旧额。 （1）Excel 数学公式计算法。其年折

51、旧额计算公式如下： 年折旧率 =尚可使用年限预计使用年限的年数总和 年折旧额 = (固定资产原值预计净残值) 年折旧率 （2）Excel 函数公式计算法。其年折旧额函数公式如下： = SYD(Cost,Salvage,Life,Per) 式中，Per 参数即 Period，表示需要计算折旧值的期间。,返回,上一页,下一页,第二节固定资产折旧与现金流量,【案例 57】某项固定资产原值为 85 万元，预计使用年限为 5 年，净残值率 5%。要求用平均年限法、年数总和法、双倍余额递减法分别计算各年折旧额。 【操作提示】 （1）设计表格。在 A1、A2、A4:A11、B2:D2、B5:D5 单元格或单

52、元格区域中录入相关文字；在 B3:D3 中录入已知数据；合并 A1:D1、A2:A3、A4:D4 单元格区域；设置字体字号等。如图 526 所示。 a.选定 B6 单元格，如图 527 所示。 b.在原 值参数Cost中输入“ $B$3 ”（绝对引用）；在净残值参数Salvage中输入“$B$3*$C$3”；在折旧年限参数 Life 中输入“$D$3”。 C.输入 B11 单元格的求和公式“=SUM(B6:B10)”。,返回,上一页,下一页,第二节固定资产折旧与现金流量,（3）用 SYD 函数计算年数总和法年折旧额，其方法如下： a.选定 C6 单元格，单击上部“插入函数”按钮选择“财务”类别

53、的“SYD”函数进入“函数参数”界面，如图 528 所示。 b.在原 值参数Cost中输入“ $B$3 ”（绝对引用）；在净残值Salvage参数中输入“$B$3*$C$3”；在折旧年限 Life 参数中输入“$D$3”；在折旧期次 Per 参数中输入“A6”（相 对引用）。 c.单击“确定”按钮回到工作表中，C6 单元格公式为“=SYD($B$3,$B$3*$C$3,$D$3,A6)”。再自动填充 C7:C10 单元格的公式；然后输入 C11 单元格的求和公式“=SUM(C6:C10)”。,返回,上一页,下一页,第二节固定资产折旧与现金流量,（4）用 DDB 函数及平均法计算年数总和法各年折

54、旧额，其方法如下。 a.选定D6单元格，单击上部“插入函数”按钮选择“财务”类别的“DDB”函数进入“函数参数”界面，如图 529 所示。 b.在原 值参数Cost中输入“ $B$3 ”（绝对引用）；在净残值参数Salvage中输入“$B$3*$C$3”；在折旧年限参数 Life 中输入“$D$3”；在折旧期次参数 Per 中输入“A6”；在余额递减速率参数 Factor 中输入“2”（也可省略不填）。 c.单击“确定”按钮回到工作表，D6 单元公式为“=DDB($B$3,$B$3*$C$3,$D$3,A6,2)”。再自动填充D7:D8 单元格的公式。,返回,上一页,下一页,第二节固定资产折旧

55、与现金流量,d.由于双倍余额递减法应在到期前两年内将剩余未提取的折旧总额进行平均计提，所以应在 D9 单元格中输入“=($B$3$B$3*$C$3SUM($D$6:$D$8)/2”；再自动填充到 D10 单元。 a. 单击工具栏的图表向导点图”中选择“平滑线散点图。 b.在数据区域中选择系列产生在“列”；其他内容按向导的提示单击“下一步”或“完成”按钮即可。 c.修饰图表。右击左部的坐标轴选“坐标轴格式”命令，进入“坐标轴格式”界面，在“刻度”页签中将最小值改为“50000”，最大值改为“350000”，将数值交叉于改为“50000”；同样的方法将下部的坐标轴的最大值改为“5”，主要刻度单位改

56、为“1”。然后拖动图表进行位置、大小等的调整。,返回,上一页,下一页,第二节固定资产折旧与现金流量,二、项目投资现金流量的计算 项目投资是一种以特定项目为对象，直接与新建项目或更新改造项目有关的长期投资行为，通常包括固定资产投资、无形资产投资和流动资金投资等内容。在财务管理中进行项目投资决策时，必须计算现金流量。项目投资的现金流量是指投资项目在计算期内各项现金流入量与现金流出量的统称。Excel 中对现金流量一般采用数学运算公式、固定资产折旧函数公式进行计算。 【技能储备】 1.初始现金流量的计算。初始现金流量是指开始投资时发生的现金流量，包括固定资产、无形资产和流动资金等的投资。由于没有现金

57、流入，所以初始现金流量一般用负数表示。,返回,上一页,下一页,第二节固定资产折旧与现金流量,2.营业现金流量的计算。 （1）Excel 流量调整法，计算公式如下： 某年现金净流量 =该年净利润 +该年非付现成本 式中，非付现成本是指固定资产折旧费、无形资产摊销费、开办费摊销额、借款利息等；净利润可用“(营业收入付现成本非付现成本) (1所得税率)”进行计算。 （2）Excel 流量扣除法，计算公式如下： 某年现金净流量 =该年税后收入该年税后付现成本 +非付现成本抵税 式中，税后收入为“营业收入 (1所得税率)”；税后付现成本为“付现成本(1所得税率)”； 非付现抵税为“非付现成本所得税率”。

58、,返回,上一页,下一页,第二节固定资产折旧与现金流量,3.终结现金流量的计算。终结现金流量是指投资项目寿命终结时所发生的现金流量，主要包括固定资产残值或变价收入、收回垫支的流动资金和停止使用的土地变价收入等。它一般作为最后一年的营业现金净流量的加项处理。 【案例 58】某公司因扩大再生产准备新建一条小型生产流水线，预计固定资产投资 860万元，垫支营运资金 25 万元。该流水线当年即可建成并投产，可用 5 年时间，固定资产采用平均年限法提取折旧，预计净残值率为 5%；投产后每年收入 2 000 万元，每年付现成本 1 550万元，所得税率 25%，营运资金于最后一年收回。要求计算该投资项目的现

59、金净流量。,返回,上一页,下一页,第二节固定资产折旧与现金流量,【操作提示】 （1）设计表格。如图 531 所示。 （2）计算各年平均年限法折旧额。在C8单元格中按前述固定资产折旧计算法，输入平均年限法的函数公式“=SLN($B$3,$B$3*$D$3,$F$3)”；然后自动填充 C9:C12 单元区域公式；在 C13 单元格中输入自动求和公式“=SUM(C7:C12)”。 （3）计算各年净利润。在 D8 单元格按前述净利润公式输入“=($D$5$E$5C8)*(1$F$5)”；然后自动填充 D9:D12 单元区域公式；在 D13 单元格输入自动求和公式“=SUM(D7:D12)”。 （4）计算资金回收额。在 E12 单元格中输入期满回收营运资金、固定资产净残值的公式“=B3*D3+B5”；在 E13 单元格输入自动求和公式“=SUM(E7:E12)”。,返回,上一页,下一页,第二节固定资产折旧与现金流量,（5）计算各年现金净流量。该项目当年投资并投产，即第 0 年（第 1 年初）的现金净流量为投额的负数，所以在 F7 单元格中输入“=B3B5”。按前述现金净流量的“净利润+非付现成本+回收额”计算公式在F8单元格中输入自动 求和公式“=SUM(C8:E8)”；然后自动填充 F9:F12 单元格区域公式；在 F13 单元格中输入自动求和公式“=SUM(F7:F12)”