《Excel财务与会计应用》-学情5资产管理与Excel数据可视化

掌握Excel可视化数据图表/饼图；掌握Excel柱形图，条形图，圆柱图；掌握Excel散点图，折线图，K线图与趋势线；掌握Excel批注、条件格式；使用Excel滚动条、微调项、单选按钮；Excel97—Excel2019任意版本

【教学做融合】技能目标学习情境5资产管理与Excel数据可视化基于工作过程导向的任务驱动法技能是学出来而非讲出来或听出来的净现值NPV函数、内涵报酬率IRR函数；取右字符RIGHT函数、取左字符LEFT函数、取指定位置字符MID函数、字符计数LEN函数；取右字节RIGHTB函数、取左字节LEFTB函数、取指定位置字节MIDB函数、字节计数LENB函数；线性折旧SLN函数、年数总和折旧SYD函数、余额递减折旧DDB函数Excel97—Excel2019任意版本

【教学做融合】技能目标学习情境5资产管理与Excel数据可视化基于工作过程导向的任务驱动法技能是学出来而非讲出来或听出来的理解现金日常财务管理的理论与决策公式；理解应收款日常财务管理的理论与决策公式；理解存货日常财务管理的理论与决策公式；理解固定资产折旧的计算公式、函数；理解现金流量的内容、计算方法与公式；理解项目投资评价指标、计算公式；理解证券投资的内容与估价方法；理解Excel字符与字节的区别知识目标学习情境5资产管理与Excel数据可视化过程分析法+任务还原法+知识筛选法+任务归并法+理实融合法学习情境5资产管理与Excel数据可视化学习内容5.1流动资产管理与Excel动态图表（1）现金构成与Excel饼图；（2）现金持有量与Excel动态立柱图；（3）现金持有量Excel动态分析（滚动条）；（4）信用天数与Excel动态条形图；（5）信用条件与Excel动态圆柱图；（6）进化批量与Excel动态警示；（7）储存天数与Excel动态选择

任务工单：15个【工作任务】5.2现金流量与Excel数据图表（1）折旧与Excel散点图；（2）现金流量与Excel条形图5.3Excel项目投资分析与批注（1）Excel净现值与内涵报酬率；（2）回收期计算与Excel批注5.4证券投资与Excel动态图表（1）债券动态估价与Excel折线图；（2）股票Excel动态估价；（3）Excel股票K线与趋势线5.5学习效果检查单选题、多选题、判断题、自主学习题（二维码）上机2题【教学做融合】=做中学+做中教+做中考=知行合一【任务工单】=工作任务+工作成果=工作目标学习情境5

资产管理与Excel数据可视化5学习任务5.1

流动资产管理与Excel动态图表现金构成与Excel饼图；现金持有量与Excel动态立柱图；现金持有量Excel动态分析（滚动条）；信用天数与Excel动态条形图；信用条件与Excel动态圆柱图；进货批量与Excel动态警示；储存天数与Excel动态选择

7个任务工单

【情境创建+任务实现】学习任务5.1流动资产管理与Excel动态图表

6一、现金构成与Excel饼图1．现金的构成2．数据可视化3.Excel图表学习任务5.1流动资产管理与Excel动态图表

7某公司2019年末资产负债表中的货币资金构成如表5-1所示。要求：在Excel中用三维（立体）饼图反映其构成百分比。学习任务5.1流动资产管理与Excel动态图表

8Excel中设计反映货币资金构成图表如图5-1所示。若修改B2至B4单元格区域的数值，B5单元格的值，以及右部的饼图、其上的百分比将随之更新。8学习任务5.1流动资产管理与Excel动态图表

9二、现金持有量与Excel动态立柱图

1．成本分析模式（1）机会成本是指因持有现金而丧失的再投资收益，它与现金持有量成正比例关系，用公式表示如下：机会成本=现金持有量×有价证券年利率或再投资报酬率（2）短缺成本是指在现金持有量不足而又无法及时通过有价证券变现加以补充而给企业造成的损失，包括直接损失与间接损失。2．Excel图表工具3．Excel查找引用与极值函数4．Excel窗体工具学习任务5.1流动资产管理与Excel动态图表

10

某公司根据历年的资料估计，未来现金持有量可能为20万元、30万元、40万元和50万元，其对应的短缺成本为4万元、3万元、2万元和1万元。若现金持有量的机会成本在8%～16%之间，请分析该公司最佳现金持有量，并用立柱图反映相关总成本的构成情况。学习任务5.1流动资产管理与Excel动态图表

11

在Excel中，完成分析计算后的界面如图5-8所示。该表的使用方法是，单击第2行中的滚动条，则F2、C6至F8单元格中的数据随之变动，C9、C10单元格中的提示将更新。下部的图形亦随之动态改变，其中，柱形图区分方案、用颜色反映短缺成本、机会成本构成情况。图5-8的柱形图是再投资收益率为16%时的状况（滚动条的滑块处于最右部）。学习任务5.1流动资产管理与Excel动态图表

12学习任务5.1流动资产管理与Excel动态图表

13三、现金持有量Excel动态分析（滚动条）1．存货模式的原理2．存货模式的前提条件3．最佳现金持有量公式学习任务5.1流动资产管理与Excel动态图表

14某公司根据历史资料分析，全年的现金需求量可能在4000万元至8000万元之间，每次证券固定转换成本在400元至800元之间，证券年利率在6%至12%之间。请计算以上情况下该公司的最佳现金持有量。学习任务5.1流动资产管理与Excel动态图表

15工作成果如图5-11所示，通过滚动条确定相关的数据后，Excel自动计算出该公司的最佳现金持有量。学习任务5.1流动资产管理与Excel动态图表

16•••••••••（2）滚动条的设置与代码取值，方法如下：a．单击窗体工具（或开发工具）中的滚动条按钮，在B2单元格中拖动一个滚动条控件。然后复制并粘贴到B3、B4单元格中。b．右击B2单元格中的滚动条选择“设置控件格式”命令，进入“设置控件格式”对话框；将其最小值、最大值、步长、页步长设置为“400、800、5、10”，将单元格链接到“$B$2”；单击“确定”按钮。B3单元格滚动条控件的最小值、最大值、步长、页步长设置为“400、800、10、100”，单元格链接到“$C$3”。学习任务5.1流动资产管理与Excel动态图表

17B4单元格滚动条控件的最小值、最大值、步长、页步长设置为“6、12、1、2”，单元格链接到“$B$4”。c．在C2单元格键入公式“=B2*100000”；以将代码值转换为资金需要量。在C4单元格键入公式“=B4/100”；以将代码值转换为百分比。采用水平居中的方式，将B2、B4单元格中的代码值隐藏于滚动条后部。（3）在C5单元格键入开平方公式“=SQRT(2*C2*C3/C4)”或“=(2*C2*C3/C4)^(1/2)”。学习任务5.1流动资产管理与Excel动态图表

18四、信用天数与Excel动态条形图1．信用天数决策原理2．相关成本的计算3．取右部字符函数学习任务5.1流动资产管理与Excel动态图表

19

根据历史资料，某公司变动成本率为50%～75%；机会成本率为8%～20%；为了加强赊销管理，提出了A、B、C三套方案。A方案信用条件为N/30，估计年销售3600万元，坏账损失率为2%，收账费用为38万元；B方案为N/60，估计年销售3960万元，坏账损失率为3%，收账费用为60万元；C方案为N/90，估计年销售4200万元，坏账损失率为5%，收账费用为100万元。请根据不同情况对企业的信用天数进行择优。学习任务5.1流动资产管理与Excel动态图表

20学习任务5.1流动资产管理与Excel动态图表

21工作成果的使用方法是：通过图5-12的滚动条确定公司的变动成本率与机会成本率，Excel自动提取相关字段的数据进行分析，计算相关成本、信用前后的收益等指标，进行方案择优的动态提示；信用后收益用三维条形图在右部进行动态提示。•••••••••（8）Excel2003插入三维堆积条形图，方法如下：a．选定D4至F4单元区域，按下键盘上的Ctrl键，再选定D16至F16单元区域。b．单击插入图表按钮进入图表向导对话框并选择“条形图”，选择并插入一幅“三维堆积条形图”。•••••••••学习任务5.1流动资产管理与Excel动态图表

221．信用条件分析的原理2．现金折扣成本的计算3．平均收款天数的计算4．Excel的字符取数函数5．Excel的字节取数函数五、信用条件与Excel动态圆柱图学习任务5.1流动资产管理与Excel动态图表

23（1）某公司预测全年赊销额为600万元，信用条件为2/20、1/60、N/90，估计有60%的顾客享受2%的现金折扣、10%的顾客享受1%的现金折扣。则：现金折扣=600×2%×60%＋600×1%×10%=7.8（万元）；平均收款天数=20×60%＋60×10%＋90×(1-60%-10%)=45（天）。（2）在空白工作表中练习字符函数：键入“=RIGHT("第12月收入56万元",4)”，则显示为“56万元”；键入“=LEFT("第12月收入56万元",4)”，则显示“第12月”；键入“=MID("第12月收入56万元",3,4)”，则显示“2月收入”；键入“=LEN("第12月收入56万元")”，则显示“10”。（3）在空白工作表中练习字节函数：键入“=RIGHTB("第12月收入56万元",4)”，则显示为“万元”；键入“=LEFTB("第12月收入56万元",4)”，则显示“第12”；键入“=MIDB("第12月收入56万元",3,4)”，则显示“12月”；键入“=LENB("第12月收入56万元")”,则显示“16”。

学习任务5.1流动资产管理与Excel动态图表

24某公司近年销赊销额为4000万元～6000万元，变动成本率为50%～75%，机会成本率为8%～20%；为了强收款管理，提出了A、B两套方案。A方案信用条件是N/60，估计坏账损失率为3%，收账费用为65万元；B方案信用条件为2/20、N/60，估计有70%的顾客会享受此项折扣优惠，坏账损失率为1.5%，收账费用为45万元。请为该公司进行决策分析。学习任务5.1流动资产管理与Excel动态图表

25Excel中设计如图5-17所示，使用方法是：通过滚动条确定公司的赊销额、变动成本率与机会成本率，Excel自动提取相关字段的数据进行分析，计算相关成本、信用前后的收益等指标，进行方案择优的动态提示；信用成本用三维圆柱图在右部进行动态提示。学习任务5.1流动资产管理与Excel动态图表

26

1．经济进货批量的相关成本2．经济进货批量基本模式3．Excel条件格式六、进货批量与Excel动态警示学习任务5.1流动资产管理与Excel动态图表

27由于未来经济环境的不确定性，某公司甲材料的全年需求量为40000~80000千克，单位采购成本为50~70元，每次固定进货费用400~800元，单位年储存成本为20~30元。请计算甲材料的经济进货批量、相关成本、平均占用资金、全年最佳进货次数、每次进货间隔天数；相关总成本大于或等于资金占用总额、进货间隔天数小于10天时，用不同的颜色进行警示。学习任务5.1流动资产管理与Excel动态图表

28在Excel中设计进货经济批量模型如图5-20所示。通过滚动条确定公司不同条件下甲材料的需用量、采购成本、进货与储存成本；模型自动计算相关成本、最佳进货次数与间隔天数；当相关总成本大于或等于平均资金占用，或进货间隔天数小于10天时，用不同的颜色进行警示。学习任务5.1流动资产管理与Excel动态图表

29

1．存货管理成本的计算2．Excel购销类型的处理3．存货储存保本保利分析七、储存天数与Excel动态选择

学习任务5.1流动资产管理与Excel动态图表

30某公司准备购进4000件甲产品，单位进价150元，单位售价180元，增值税率为13%；一次性费用50000元，销售税费5600元。经测算，贷款年利率8%~20%，存货月保管费率3%o~10%。，期望投资净收益率4%~10%。要求：区分该公司分别为均匀出售的超市、整进整出的批发公司，计算下列指标：（1）保本天数；（2）保利天数；（3）实际储存了2~3000天时的盈亏情况。学习任务5.1流动资产管理与Excel动态图表

31Excel中设计存货储存动态分析模型如图5-23所示。通过单选按钮选择销售类型，用滚动条确定费用与天数；模型自动计算毛利、费用与目标利润，并分析保本保利天数与盈亏数。学习情境5

资产管理与Excel数据可视化32学习任务5.2

现金流量与Excel数据图表固定资产折旧与Excel散点图；项目现金流量与Excel条形图

3个任务工单

【情境创建+任务实现】学习任务5.2

现金流量与Excel数据图表33一、折旧与Excel散点图

1．平均年限法2．双倍余额递减法3．年数总和法学习任务5.2

现金流量与Excel数据图表34

某项固定资产原值为85万元，预计使用年限为5年，净残值率5%。要求用平均年限法、年数总和法、双倍余额递减法分别计算各年折旧额。学习任务5.2

现金流量与Excel数据图表35Excel中设置不同折旧方法的折旧计算表如图5-25所示，将这些折旧方法各年的折旧额用平滑线散点图反映。学习任务5.2

现金流量与Excel数据图表36二、现金流量与Excel条形图

1．项目投资的现金流量2．初始现金流量的计算3．营业现金流量的计算4．终结现金流量的计算学习任务5.2

现金流量与Excel数据图表37

某公司因扩大再生产准备新建一条小型生产流水线，预计固定资产投资860万元，垫支营运资金25万元。该流水线当年即可建成并投产，可用5年时间，固定资产采用平均年限法提取折旧，预计净残值率为5%；投产后每年收入2000万元，每年付现成本1550万元，所得税率25%，营运资金于最后一年收回。要求计算该投资项目的现金净流量。学习任务5.2

现金流量与Excel数据图表38在Excel中用平均年限法计算折旧，用流量调整法计算各年营业现金流量；同时，用堆积条形圆柱图反映各年现金净流量的百分比构成情况，如图5-27所示。学习任务5.2

现金流量与Excel数据图表39

某公司进行固定资产投资500万元，预计可用5年，期满净残值率为5%，采用平均年限法折旧。当年投产后可实现收入1600万元，以后逐年递增6%；投产当年的付现成本为1390万元，以后逐年递增4%；所得税率25%。要求计算各年现金净流量。学习任务5.2

现金流量与Excel数据图表40在Excel中用平均年限法计算折旧，用流量扣除法计算各年营业现金流量，如图5-29所示。学习情境5

资产管理与Excel数据可视化41学习任务5.3

Excel项目投资分析与批注Excel净现值与内涵报酬率；回收期计算与Excel批注

2个任务工单

【情境创建+任务实现】学习任务5.3Excel项目投资分析与批注42一、Excel净现值与内含报酬率1．净现值指标的分析2．内涵报酬率指标的分析学习任务5.3Excel项目投资分析与批注43某公司的期望投资收益率为10%，现有A、B、C、D四个投资方案，经计算各方案的现金净流量如表5-3所示。用净现值、内含报酬率分析投资方案的财务可行性。学习任务5.3Excel项目投资分析与批注44Excel中根据各方案年现金净流量计算净现值与内含报酬率，并进行财务可行性提示，如图5-30所示。学习任务5.3Excel项目投资分析与批注45二、投资回收期与Excel批注

1．静态投资回收期2．动态投资回收期3．Excel批注功能学习任务5.3Excel项目投资分析与批注46

某公司有甲、乙两个投资方案，现金流量如表5-4所示，该公司要求的期望收益率为10%，请计算静态投资回收期、动态投资回收期。学习任务5.3Excel项目投资分析与批注47Excel中用公式或函数计算各方案的静态、动态投资回收期，并用批注提示各单元格的计算公式，如图5-32所示。学习情境5

资产管理与Excel数据可视化48学习任务5.4证券投资与Excel动态图表债券动态估价与Excel折线图；股票Excel动态估价；Excel股票K线与趋势线

3个任务工单

【情境创建+任务实现】学习任务5.4证券投资与Excel动态图表49一、债券动态估价与Excel折线图1．证券投资的内容与因素2．Excel时间价值函数3．Excel窗体工具学习任务5.4证券投资与Excel动态图表50

某公司准备进行债券投资，经市场调查有多种债券：面值为1000元～30000元，年付息次数为1次～4次，票面利率为5%～20%，期限为1年～8年。由于不同债券风险不同，所以公司投资的期望收益率为6%～24%。要求计算相关债券在不同情况下的最高购置价。学习任务5.4证券投资与Excel动态图表51

Excel中设计的动态估价模型如图5-33所示；用微调按钮选择债券发行的条件，模型自动计算不同条件下各年的发行价格；同时，用折线图动态提示发行价格。学习任务5.4证券投资与Excel动态图表52二、股票Excel动态估价

1．股票的理论价值2．期望投资收益率学习任务5.4证券投资与Excel动态图表53

某公司拟对A、B、C三只股票进行投资，经调查它们比较稳定的每股收益分别为0.23元、0.51元、0.49元；它们的风险系数（β系数）分别为0.9、1.3、1.5。现行无风险年收益率为7%，估计未来股市平均年收益率在10%～30%之间。要求计算各股票在不同的股市收益率下的理论购买价值。学习任务5.4证券投资与Excel动态图表54在Excel中用滚动条确定股市平均收益率，模型自动计算其理论价值，如图5-35所示。学习任务5.4证券投资与Excel动态图表55三、Excel股票K线图与趋势线

证券K线图、趋势线、成交量、KDJ等是证券价格走势的晴雨表；进行证券投资特别是上市公司股票投资时，应掌握它们的分析技术。以下仅对K线图、趋势线作简单介绍。1．股票