EXC基础及其应用 3

项目八资金需求量的预测Excel在财务中的应用参考教材：《Excel在财务中的应用》高等教育出版社（第五版）目录CONTENTS01任务一用销售百分比法预测资金需求量02任务二用回归分析法预测资金需求量03任务三用高低点法预测资金需求量04课后实训（附参考答案）01任务一用销售百分比法预测资金需求量任务目标与任务描述任务一·用销售百分比法预测资金需求量知识目标掌握IF函数及数据有效性在实务中的应用技巧，能在Excel中制作下拉式菜单，能制作一个通用的销售百分比法资金需求量预测模型；掌握Excel的公式输入方法，能正确运用"+""-""*""/"等运算符进行相关数据的计算。素养目标培养前瞻性管理思维，增强独立思考、自主钻研的学习能力；掌握举一反三的知识迁移能力，将所学方法用于财务决策。任务描述江南公司的资产负债表、利润表等相关资料如表8-1、表8-2所示，请采用销售百分比法预测该公司2027年的资金需求量。其他资料：销售增长率按营业收入三年平均增长率确定；营业净利率按近三年平均营业净利率确定；固定资产投资金额按投资计划表金额495万元确定；股利支付率假定为30%。销售百分比法的基本原理任务一·用销售百分比法预测资金需求量销售百分比法，是在分析资产负债表有关项目与销售额关系的基础上，根据市场调查和销售预测资料，确定资产、负债和所有者权益有关项目占销售收入的百分比，并依此推算流动资金需求量的方法。（1）预计销售增长率根据历史数据，预计销售收入增长率。（2）区分敏感项目敏感项目随销售额同步变动（如库存现金、应收账款、存货、应付账款等）；非敏感项目与销售额无紧密变动关系（如长期负债、实收资本等）。（3）计算需要增加的资金需增加的资金=敏感资产销售百分比×新增销售额-敏感负债销售百分比×新增销售额。（4）计算内部留存收益增加额内部留存收益增加额=预计销售额×预计销售净利率×（1-股利支付率）。（5）计算外部融资需求外部融资需求=需要增加的资金-内部留存收益增加额。

搜集整理企业近年来的销售、盈利资料任务一·用销售百分比法预测资金需求量二、Excel模型的设计：（一）搜集整理资料搜集整理企业近年来的营业收入、净利润、营业净利率、股利支付率等资料，作为资金需求预测的基本数据（图8-1）。在B20:D20区域计算各年度营业净利率，并用SUMIF函数在B36单元格录入公式，汇总2027年度固定资产投资额（图8-2）。=SUMIF(B24:B34,B35,C24:C34)图8-1历年来销售、盈利情况资料图8-2固定资产投资额条件求和知识链接8-1SUMIF函数任务一·用销售百分比法预测资金需求量知识链接8-1SUMIF函数使用SUMIF函数可以对区域中符合指定条件的值求和。其语法为：SUMIF(range,criteria,[sum_range])range：用于条件计算的单元格区域，区域中的单元格必须是数字或名称、数组或包含数字的引用，空值和文本值将被忽略；criteria：确定对哪些单元格求和的条件，形式可以为数字、表达式、单元格引用、文本或函数，如32、">32"、B5、"苹果"或TODAY()；任何文本条件或含有逻辑、数学符号的条件必须使用双引号括起来，条件为数字则无须双引号；sum_range：求和的实际单元格区域，可省略；省略时对range区域中符合应用条件的单元格求和。criteria中可使用通配符：问号（?）匹配任意单个字符，星号（*）匹配任意一串字符；若要查找实际的问号或星号，请在该字符前键入波形符（~）。需要多条件求和时，可以使用SUMIFS函数实现。设计资产负债表敏感性分析选择表任务一·用销售百分比法预测资金需求量（二）设计资产负债表敏感性分析选择表敏感项目需要随时调整，应在资产负债表中增加一列作为敏感项目调整列，由财务人员分析选择"是"或"否"填列（图8-3）。选择C4:C54单元格区域，执行"数据"→"数据工具"→"数据验证"：在"允许"框中选择"序列"，"来源"框中输入"是，否"（逗号为英文输入法状态下的逗号），单击"确定"（图8-4），即完成敏感项目的选择录入。图8-3敏感性分析选择表图8-4数据有效性的设置知识链接8-2数据验证任务一·用销售百分比法预测资金需求量知识链接8-2数据验证数据验证是对单元格或单元格区域输入的数据从内容到数量上进行限制：符合条件的数据允许输入，不符合条件的数据禁止输入。这样可以依靠系统检查数据的有效性，避免错误的数据录入。数据验证功能可以在尚未输入数据时预先设置，以保证输入数据的正确性。允许输入符合条件的数据正常录入，如下拉序列中的"是/否"禁止输入不符合条件的数据被系统拒绝并提示预先设置在录入数据前先行设置，从源头保证数据正确计算敏感项目的销售百分比任务一·用销售百分比法预测资金需求量（三）计算敏感项目销售百分比确定敏感项目后，根据"敏感项目占销售收入的百分比=（基期敏感项目数额÷基期销售额）×100%"计算各项目销售百分比。在D6单元格中输入：=IF(C6="是",B6/利润表!$B$3,"")公式含义：如果C6单元格的值是"是"，则执行资产负债表中敏感项目金额（B6）除以"利润表"工作表中营业收入（B3）的操作；如果C6不是"是"，则D6显示为空。采用销售百分比法预测资金需求，是假定预计年度敏感项目金额占销售收入的比重不变。式中采用绝对引用（$B$3），公式填充复制到其他单元格时营业收入数据始终保持不变。上述公式计算的是货币资金项目占上年销售收入的比重；将公式向下填充，即可完成所有报表项目销售百分比的计算。计算敏感性资产、负债的销售百分比之和任务一·用销售百分比法预测资金需求量（四）分别计算敏感性资产和敏感性负债之和对各敏感项目的销售百分比自动求和，计算结果保留4位小数，以减少尾数误差带来的影响（图8-5、图8-6）。D28：=ROUND(SUM(D5:D26),4)D54：=ROUND(SUM(D31:D53),4)结果解读：敏感资产销售百分比合计53.28%；敏感负债销售百分比合计34.83%。图8-5敏感资产的销售百分比图8-6敏感负债的销售百分比按销售百分比法预测资金需求量任务一·用销售百分比法预测资金需求量操作提示8-1用SUMIF函数条件求和D28单元格也可用SUMIF函数实现：敏感项目为"是"时对D5:D26区域的销售百分比求和，即用"=SUMIF(C5:C26,"是",D5:D26)"代替"=SUM(D5:D26)"。公司每增加销售收入100元，需要增加53.28元的资产，同时增加34.83元的商业信用（属自发性负债筹资），最终需净增加资金18.45元（53.28-34.83）。在B39单元格录入"=(B3/E3)^(1/3)-1"，完成营业收入三年平均增长率的计算（32.03%）。为提高模板通用性，表中所有数据均采用链接方式，便于动态更新（图8-7）。图8-7按销售百分比法预测资金需要量计算新增资金需求与外部融资需求任务一·用销售百分比法预测资金需求量（五）需要增加的资金B48：=B42*(B44-B45)因销售增长新增资金=29000×32.03%×(53.28%-34.83%)≈1,713万元引用固定资产投资B49：=B36引用2027年固定资产投资新增资金495万元新增资金总需求B50：=B48+B492027年新增资金需求=1,713+495=2,208万元（六）内部留存收益B52：=INT(B43*B40*(1-B41))38288×6.67%×(1-30%)≈1,789万元（七）外部融资需求B53：=B50-B522,208-1,789=419万元结论：依据基期报表项目占销售收入的比例关系和项目投资计划，2027年度新增资金需求为2,208万元，其中内部留存收益可提供1,789万元，公司需向外部筹资419万元。至此，一个简易的销售百分比法资金需求预测模型制作完成。知识链接8-3INT函数向下取整任务一·用销售百分比法预测资金需求量知识链接8-3INT函数向下取整INT函数将数字向下舍入到最接近的整数。其语法为：INT(number)，式中number为需要进行向下舍入取整的实数。例如：INT(8.9)=8，INT(-8.9)=-9。本任务中用INT函数对新增留存收益等计算结果取整。操作提示8-2数据验证的作用如果修改资产负债表中的敏感项目（"是/否"选择），最终的计算结果是否会发生变化？由于模型中各计算环节均通过公式与敏感性分析选择列联动，调整任一项目的"是/否"选择后，销售百分比合计、新增资金需求与外部融资需求都会自动重算——这正是数据验证设置在通用预测模型中发挥的作用。02任务二用回归分析法预测资金需求量任务目标与任务描述任务二·用回归分析法预测资金需求量知识目标能调用Excel分析工具库中的工具用于财务实务决策；能正确使用分析工具库中的回归分析法，正确设置Y值与X值，能根据计算结果编写分析项目的回归公式。素养目标培养统计分析思维，理解数据规律，揭示数据背后的因果关系；增强用数据说话的实证意识，提高依据数据进行客观、公正、科学分析的能力；坚持守正创新，在传统方法基础上探索更精准的预测工具。任务描述中南公司2022—2026年度的资金占用与销售收入如表8-3所示。假设2027年的预计销售收入为700万元，请用回归分析法预测中南公司2027年的资金需求量。年度业务量x/万元资金占用y/万元20225001002023520110202448012020255401252026690130表8-3资金占用与销售收入回归分析法的基本原理任务二·用回归分析法预测资金需求量

由n组观测值构成方程组，可求出固定资金a与单位变动资金b：

年度业务量x资金占用yx·yx²202250010050000250000202352011057200270400202448012057600230400202554012567500291600202669013089700476100合计Σx=2730Σy=585Σxy=322000Σx²=1518500表8-4中南公司回归分析计算表（万元）利用Excel分析工具库：加载分析工具库任务二·用回归分析法预测资金需求量二、利用Excel分析工具库进行公式的设置（1）加载宏：单击Office按钮进入Excel选项，单击"加载项"，选择"分析工具库"（图8-8）。（2）调用分析工具库：管理Excel加载项，单击"转到"按钮，进入"加载宏"选项界面，勾选"分析工具库"（图8-9、图8-10）。图8-8加载分析工具库图8-9转到分析工具库图8-10"加载宏"选项界面选择回归分析并设置计算区域任务二·用回归分析法预测资金需求量（3）选择回归分析方法：单击"数据"选项卡，选择"数据分析"功能，在"数据分析"对话框中选择"回归"，单击"确定"（图8-11）。（4）设置因变量和自变量计算区域：Y值输入区域选择$B$3:$F$3（资金占用金额，因变量），X值输入区域选择$B$2:$F$2（业务量，自变量），输出区域选择$A$6（图8-12）。图8-11选择回归分析法图8-12自变量与因变量区域的选择输入编辑资金预测的线性回归方程任务二·用回归分析法预测资金需求量（5）编辑资金预测的线性回归方程：在系统输出的回归结果中，根据B26、B27单元格的系数（a值为固定资金需求量，b值为单位变动资金需求量），写出资金预测的线性回归方程（图8-13）：

假设2027年预计销售收入为700万元：2027年资金需求量＝66.3503+0.0928×700≈131.31万元图8-13根据输出结果编制资金需求公式同步训练8-1：回归分析方程式的确定任务二·用回归分析法预测资金需求量同步训练8-1回归分析法不仅可以用来预测资金需求量，也可以用于分解混合成本。假设江东机械厂1—5月机器工作小时与维修成本的变动情况如表8-5所示，请利用回归分析法写出维修费的混合成本预测公式。月份1月2月3月4月5月业务量x/千机器小时68569维修费y/元120130100125140表8-5机器工作小时与维修成本变动情况

03任务三用高低点法预测资金需求量任务目标与任务描述任务三·用高低点法预测资金需求量知识目标掌握MAX、MIN函数的使用，能利用该函数在一组数据中找到最大值与最小值；理解HLOOKUP函数的使用，能在数据表首行按指定条件查找，并精确返回指定行数的值；掌握CONCATENATE函数的使用，能将多个单元格中的数字或文本合并成一个简要的字符串。素养目标增强方法论意识，根据实际条件选择恰当的预测工具；理解极端值与常态的关系，培养去伪存真、去粗取精的分析能力。任务描述中南公司2022—2026年度的资金占用情况与销售收入之间的关系如表8-6所示。假设2027年的预计销售收入为700万元，请用高低点法预测中南公司2027年的资金需求量。年度业务量x/万元资金占用y/万元202250010020235201102024（低点）48012020255401252026（高点）690130表8-6资金占用情况与销售收入高低点法的含义任务三·用高低点法预测资金需求量一、高低点法的含义

方法特点：简便易算，只要有两个不同时期的业务量和资金占用情况即可求解，使用较为广泛；只根据最高、最低两组数据，不考虑其他业务量变化，计算结果往往不够精确；选用的历史数据应能代表业务活动的正常情况，不应含有异常状态下的数据；求得的公式只适用于相关范围（本例为业务量480万～690万元），超出相关范围即不适用。编制资金需求预测的线性公式任务三·用高低点法预测资金需求量二、编制资金需求预测的线性公式

图8-14高低点法预测资金需求模型公式说明：B8=MAX(B3:F3)确定业务量高点；B9=MIN(B3:F3)确定业务量低点；B10/B11=HLOOKUP(…)精确查找高、低点对应的资金占用额；B15=ROUND((B10-B11)/(B8-B9),4)计算单位变动资金b；B16=ROUND(B10-B8*B15,4)计算固定资金a；B17=CONCATENATE("y=",B16,"+",B15,"x")合成资金预测公式。知识链接：MAX、MIN与HLOOKUP函数任务三·用高低点法预测资金需求量知识链接8-4MAX与MIN函数MAX(number1,number2,…)返回一组数值中的最大值；MIN(number1,number2,…)返回一组数值中的最小值。参数为1到255个要找出最大值或最小值的数字，可以是数字或包含数字的名称、数组或引用。知识链接8-5HLOOKUP函数当比较值位于数据表的首行、要查找下方给定行中的数据时，使用HLOOKUP函数（H代表"行"；比较值位于左侧列时用VLOOKUP）。语法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)lookup_value：需要在数据表第一行中查找的数值，可为数值、引用或文本字符串；table_array：用于查找数据的数据表，其第一行可为文本、数字或逻辑值；row_index_num：待返回匹配值的行序号（为1返回第一行，为2返回第二行，以此类推）；range_lookup：逻辑值，TRUE或省略返回近似匹配值；FALSE查找精确匹配值，找不到则返回错误值#N/A。同步训练8-2：用高低点法分解混合成本任务三·用高低点法预测资金需求量同步训练8-2高低点法不仅可用于预测资金需求，也可用于分解混合成本。请根据江东机械厂1—5月机器工作小时和维修成本的变动情况（表8-5），将图8-14模型中A3:F4区域的数据修改为表8-5的数据，验证其混合成本预测公式是否为y=50+10x。月份1月2月3月4月5月业务量x/千机器小时68569维修费y/元120130100125140表8-5机器工作小时与维修成本变动情况

04课后实训及参考答案课后实训：函数基础·判断正误（附参考答案）课后实训·附参考答案一、函数基础（判断正误）（√／×）为参考答案1．如果A3:A12单元格区域包含数字，则公式"=AVERAGE(A3:A12)"将返回这些数字的平均值。（√）2．使用AVERAGE函数时，如果区域或单元格引用的参数包含文本、逻辑值或空单元格，这些值将被忽略，但包含零值的单元格将被计算在内。（√）3．若只对符合某些条件的值计算平均值，请使用AVERAGEIF函数或AVERAGEIFS函数。（√）4．如果单元格区域A3:A12包含数字，则公式"=INT(AVERAGE(A3:A12))"将返回这些数字的平均值的整数。（√）5．INT函数将数字向下舍入到最接近的整数。（√）6．表达式"=INT(8.9)"将8.9向下舍入到最接近的整数，其结果为8。（√）7．表达式"=INT(-8.9)"将-8.9向下舍入到最接近的整数，其结果为-9。（√）8．表达式"=SUMIF(A1:A12,">5")"对数据区域中大于5的值进行求和。（√）课后实训：函数基础·判断正误（附参考答案）课后实训·附参考答案一、函数基础（判断正误）（√／×）为参考答案9．表达式"=(27/1)^(1/3)-1"的计算结果为2。（√）10．表达式"=SUMIF(A:A,">5",C:C)"表示，若A列单元格的值">5"，则对相应C列的数值进行累计求和。（√）11．表达式"=IF(5>9,"这是不可能的","这是真的吗？")"，其值将显示"这是真的吗？"。（√）12．表达式"=A1"表示对A1单元格的引用，将该公式复制到A3单元格，则引用A3单元格的值。（√）13．表达式"=A$1"表示对A1单元格的绝对行引用，将该公式复制到A3单元格，则引用A1单元格的值。（√）14．表达式"=$A1"表示对A1单元格的绝对列引用，将该公式复制到B3单元格，则引用A3单元格的值。（√）15．表达式"=$A$1"表示对A1单元格的绝对引用，将该公式复制到A3单元格，则仍引用A1单元格的值。（√）课后实训：函数应用1（附参考答案）课后实训·附参考答案二、函数应用1．东北公司拟采用销售百分比法预测2027年资金需求量，项目敏感性分析如表8-7、表8-8所示（摘要）。其他资料：销售增长率20%，近年来平均销售净利率10%，平均股利支付率40%。表8-7资产负债表（摘要）20252026敏感性货币资金10005169是交易性金融资产09000是应收票据及应收账款700018954是存货1260010283是应付票据及应付账款73907959是应交税费04659是表8-8利润表（摘要）20252026营业收入180000216000净利润2002520369单位：万元——参考答案：（1）按表中敏感性分析，用销售百分比法测算，2027年需向外融资9,395万元。（2）若存货、交易性金融资产改为非敏感项目（数据验证改选"否"，模型自动重算），2027年需向外融资13,251万元。将实训结果以"××××（学号）81.xls"命名保存。课后实训：函数应用2（附参考答案）课后实训·附参考答案二、函数应用2．南方公司是一家高负债企业，收入与筹资活动密切相关。公司近年来营业收入与筹资活动现金流入如表8-9所示，要求采用回归分析法预测其2027年的筹资额。年份历年营业收入x/元筹资活动现金流入y/元2023638006310229202476672255351720251055885256042202617848201692373表8-9南方公司近年营业收入与筹资活动现金流入

课后实训：函数应用3（附参考答案）课后实训·附参考答案二、函数应用3．假设江南公司流动资产项目中，除"一年内到期的非流动资产"与"其他流动资产"为非敏感资产外其余均为敏感资产；长期资产项目中除"固定资产"为敏感资产外其余均为非敏感资