Excel商务数据处理与分析 第11章 财务数据分析.pdf

第11章财务数据分析Excel商务数据处理与分析a11.2 利润表分析11.3 提身与技巧q(回11.1.1利润预测A利润预测，是指对企业未来应当达到和可望实现的利润水平及其变动趋势作出的预计和 测算。下面将在利润预测与分析工作簿中，利用回归数据分析工具对商品实现的利润进 行预测，其具体操作如下。1.输入公式2.复制公式二三痛入大面布局公式效宪市阅次图开度工H 特色功能 G Q U*10%A工花贴心，B Z JJ I A.A，事妻多弟元格.$A 笠，聿季诊 1 剪贴板 G字体对齐方式SUIIFX.八=D3-E3ABcDEF畅销产品-1_充电宝2018.选择后隼入）c月份销售蚩售价销售软销售成本实也利涧153293.00 142,476.00 113,980.80=D3-E3|4 2230598.00 225,890.00 180,712.005 3198095.00 188,100.00 150,480.00-C A L cV CCU CCd c cf cA CC6 CC实现利润 A=D3-E3ABCDEF53198095.00 188,100.00 150,480.00 37,620.0064225691.00 205,296.00 164,236.8041,059.20752365 93.00219,945.00 175,956.0043,989.0086302591.00 275,275.00 220,220.00 55,055.0097286097.00277,420.00221,936.0055,484.001084500 92.00414,000.00331,200.00 82,800.00119356097.00 345,320.00 276,256.00 69,064.001210645698.00 632,688.00506,150.40 126,537.601311560093.00520,800 复制 10%160.0014128652 94.00813,288.00650,630.40 162,657.60H 1 明细 刹涧箱制 蜘1涧公桁 _1回11.1.1利润预测3.定义名称4.新建名称1文件H瓶入式固布局公式亚S盲网税更开发工具特色切能 合。O物1投白两Q 皿信人函数做量近使用的电效,包文本.财务 6日期如90.m湾.份.名称华用衿:.公式干核利建*8计算卅0 TT蕈F实现利润、A=D3-E3 ABC D EFG112畅销产品充电宝2018年销售明4 T耳手）月份销售量售价销售软销售成本实现利涧31153293.00 142,476.00 113,980.8028,495.2042230598.00 225,890.00 180,712.0045,178.005631980 95.00 188,100.00 150,480.0037,620.004225691.00 205,296.00 164,236.8041,059.2075236593.00219,945.00 175,956.0043,989.00实现利涧ABD245678910畅销产品月份2457充电宝2018年销售明细180.80712.00480.00236.80956.00220.00936.00414,000.0092.004500，明细利涧颈测8利润分本实现利涧28,495.2045,178.00 37,620.0041,059.2043,989.00 55,055.00 55,484.00|331,200.000_82,800.00平均值：71,008.30 计数：12 求和：852.099.60的 100%0-Q 金回n.i.i利润预测A6.引用定义的名称5.查看定义的单元格名称回11.1.1利润预测A7.复制公式8.利用定义名称引用数据B3-A=销售量ABC5卜19806 422567 523658 630259匚286010 845001-复制：11 9356012 10645613 11560014 128652is r X明细构币瓦啊利涧分析 5变动范困3.504，6固定成本 3,408,398.407利闰=B3*B4-B5*B3-B61选择后输入8回11.1.2动态分析产品利润A5.输入公式6.复制公式SUM IF X 八=B3*D3/100+B3B|C|D E充电宝利润敏感性分析12项目实际数据变化后数据变化率控制条3消售里45091=B3*D3/100+B3|4售价94.33T5变动范围3.5046固定成本 3,408,398.402输入7利闰687,367.43V J8单因素利润敏感性分析C3 A=B3*D3/100+B3ABC _1 D E1充电宝利润敏感性分析2项目实际数据变化后数据 变化率 控制条3俏售里4509145091 4 4售价 94.335变动范困3.504 T复制J,6固定成本3,408,398.403408398 1利词687,367.438单因素利润敏感性分析回11.1.2动态分析产品利润7.退置额字类型r_八八一一 TLi文件插入负局布局公式-nmc ruu”cawi数据亩阅视图开发工具特色功能 4 G3*布贴金”1，7 1 嵬贴板弓咻 103，U Z N三 今笠字体-=室室专里，串串诊-对齐万式 G%A 制工T讣数字样式单兀格 .aC4.=B4*D4/100+B4|3|AB_C_微皂容12日1充电宝利润敏感性行焉南j2项目实际数据变讪后年据3i肖售里4509145091 1gm货而4售价94.3394一会计专用*_旦二 94.335变动范围3.5046固定成本 3,408,398.403408398短日期；用 1900/4/31利闰687,367.43=8.计算变化后的利润字体对齐万式 QC7.=C3*C4-C5*C3-C6ABCDE1充电宝利润敏感性分析（2.您人）2项目实际数据变化后数据变化率控制条3俏售里450914509144售价 94.3394.3345变动范困3.503.5046固定成本3,408,398.403,408,398.4041利闰687,367.43687,367.43 1.选择J81 9单因素利润敏感性分析M 4iJW利涧预测利润分析2n.1$t s8|国回四100%0士+回11.1.2动态分析产品利润rQ 琉入公才 ail一 td u，、-a v-E1充电宝利润敏感性分析2项目 实际数据 变化后数据 变化率控制条3俏售里 45091 22546-50，-1售价 94.33 94.33，5变动范围 3.50 3.506固定成本 3,408,398.40 3,408,398.40，r利润 687,367.43(1,360,515.48)二单因素利润敏感性分析10.复制公式充电宝利润敏感性分析2项目实际数据变化后数据变化率控制条3俏售里4509122546-504 4售价94.3347.17-50,5变动范围3.501.75-504|6固定成本3,408,398.401,704,199.20-504 1利闻687,367.43(680,257.74)T5+电因素利润敏感性分析D7 G=(C7-B7)/B7（输入ABCE充电宝利润敏感性分析 1项目实际数据变化后数据变化率控制条3徜售更4509122546-5041售价94.33*47.17-5045变动范围3.50*1.75-5045固定成本 3,408,398.4011,704,199.20-5047利司687,367.43(680.257.74)-1.989656636B单因素利润敏感性分析|11.输入公式BU A D3ABT CDE7利闰687,367.43-1.9896566368输入9单因素利润敏感性分析10项目变化率利涧利润变化量利涧变化率H请售置-5012售价13支动范国14固定成本12.引用单元格1L1.2动态分析产品利润78910利词 1687,367.43(680,257.74)T.98965663618910利闰687,367.43(1680/1n n 二&1.9896566362.册人单因素利润敏感性分析单因素利润敏感性分析项目变化率利涧利涧变化量利润项目变化率利涧利润变化量利润变化率11消售里-5011消售里-50(680,257.74)_1,选择厂_L.J 1_12售价-50 复制12售价-5013支幼范国-5013变动范困-5014固定成本-5014固定成本-5015151616c ii_ G w：=$C$7J11 A=$C$7-$E；$7-2.输入1890A 利闰_ B687,367.43C(*680,257.74)D-1.989656636E78910A 利闰B 687,367.43C(*680,257.74)D-1.989656636E单因素利润敏感性分析单因素利润敏感性分析项目变化率利涧利涧变化量利涧变项目变化率利涧利涧变化量利涧变化率1徜售量-50(680,257.74)11倘售里-50(680,257.74)(1,367,625.18)23价-50(680,257.74)._复制1213141516售价-50(680,257.74)T变动范国-50(680,257.74)变动范围-50(680,257.74)14固定成本-50(680,257.74)固定成本-50(680,257.74)1.选择56无15.复制公式,-/16输入公式回11.1.2动态分析产品利润17.复制公式D7利闰687,367.43(*680,257.74)-1.98965663689单因素利润敏感性分析10项目变化率利涧利涧变化量利涧11请售量-50(680,257.74)(1,367,625.18)12售价-50复制(1,367,625.18)13焚动范国-50(680,257.74)(1,367,625.18)14固定成本-50(680,257.74)(1,367,625.18)1516EU_A=D11/$B$7 U2.输入ABCD E7利闰687,367.43(680,257.74)-1.98965663689单因素利润敏感性分析10项目变化率利涧利涧变化量 利涧变化率11消售量-50(680,257.74)(1,367,625.18)-198.97%12售价-50(680,257.74)(1,367,625.18)13费动道围-50(680,257.74)(1,367,625.If 1“父 14固定成本-50(680,257.74)(1 3670迅祥151618.输入公式E11 A=D11/$B$719.复制公式7687,367.43(680,257.74)-1.98965663689利润敏感性分析10变化率利涧利涧变化量利涧变化率11-50(680,257.74)(1,367,625.18)-198.97%12-50(680,257.74)复制，1198.97X(680,257.74)5.18)-198.97%13-50 X明细M润预测利润分斤/匕J/H 1 皿 1 11 a I|理回凹 100%;D3._=F3-50ABCDB1充电宝利润敏感性分析2项目实际数据变化后数据变化率控制条3徜售里4509149600104售价 94.3394.330 T5变动范围3.503.50与、田击女16固定成本5,682.005,682.00日调整j,1利闰4,090,083.834,499,660.420.10013892189单因素利润敏感性分析10项目变化率利润利涧变化量利润变化率11稍售里104,499,660.42409,576.5810.01%12售价04,499,660.42409,576.5810.01%13变动范围04,499,660.42409,576.5810.01%H 明细利润预测利润分 折/匕1 i“利润表利润表由选孑十后彳刖入J HW12,563.00J-利清表分析,xl sx-Mic ro so t t Exc el插入页面布局公式数据窗阅视图开发】2.复制公式 11三三三番数值箱贴电,回/,3第贴板Q A：A”8 A 笠”字体 6=：MmW里，串享诊，对开月式 Q券，%4-.0.00.00+.0A1*删除，e r 图一a单元格B15 A 0-B1 l-BlBl3+B14ABCD4一、营北收入69,288.0055,957.0079,450.005其中：主营务收入56,932.0045,692.0065,800.006其他业务收入12,356.0010,265.0013,650.007减：营业成本33,854.0030,048.0051,880.008其中：主营也务成本23,600.0020,365.0036,250.009其他北务成本10,254.009,683.0015,630.0010稍售费用5,360.00%62r E,l I o o11管理费用2,560.003,65(艮市I 0012财务费用1,530.00i,286?inr2,b60.OU13资产减值陨失0.000.000.0014加：投资净收益0.001,263.00 0.0015-、营业利间25,984.0017.614.0011,487.0016加：营北外收入15,600.008,960.0016,320.0017城：营业外支出6,350.003,250.005,600.0018三、利翅总薮19减：所得税1,560.00560.002,563.004 4“利润表利涧表比率分析She e t 3 JR“利润表不阑表比率分析Shee t 3 匕JEh提醒利润表中不同层次的利润依次为：营业利润、利润总额和净利润。其中，营业利润=营业收入-营 业成本-税金及附加-销售费用-管理费用-财务费用-资产减值损失+公允价值变动收益公允价值变动损 失）+投资收益卜投资损失）+资产处置收益卜资产处置损失）+其他收益；利润总额=营业利润+营业外收入-营业夕卜支出；净利润=利润总额-所得税。回11.2.1利润表比率的计算八X=（利涧表!SUIIFBC D1顼目2016 年2017年-LJ-邓18 年3毛利率!B7)/利润表!B4._/输入I_4争利车早5678910111213i选择）B3%=（利润表B4-利润表!B7）/利涧表!B4ABCD1项目2016 洋2017 年2018 年毛利率51%46%4等利率T51、67复制,89101112137.输入公式E，叫利涧表，利润表比率分析,Shee t 3 98.复制公式IM 7 m 利润表，利润表比率分析，She et 3。1 一回11.2.2利润表比率变化趋势分析变化趋势的分析方法包括定比和环比两种，其中，定比是以某一时期为基数，其他各期均 与该期的基数进行比较；而环比则是分别以上一时期为基数，下一时期与上一时期的基数进行 比较。下面将在利率表比率工作表中插入图表，并采用环比法对毛利率和利润率进行比较 分析，其具体操作如下。1.选择图表类型2.单击选择数据按钮回11.2.2利润表比率变化趋势分析4.添加数据系列3.编辑数据系列回11.2.2利润表比率变化趋势分析5.设置水平轴标签6.选择图表样式回11.2.2利润表比率变化趋势分析7.设置图表区8.选择图表区的填充颜色区J 19 _)t 利再度分析.xl sx-Mic ro so ft Exc e l文件开始也入页面赛公式S38亩樊视图开发I特色歌设计|布局格式as衰区与设置所m为重设以.用国总插入图表标翼生乐轴标英创图例，词数援标签,苴融运算表阉生玩轴身分析届性2单击卜711./择)-6 7 89 10 11 12 13 14 15 16 17 18 19 20 21II*,*1利海口利润表比率分析、She e t 3.咋I,便口围o o%qLUG回11.2.2利润表比率变化趋势分析9.添加其他趋势线利分析,3$Mic ro so ft Exc e l开始插入 RS4公式因S市阅视35开发工特色区设计布置梏式JS6A坐原轴分析座性图表坐当前所送内容图表5趋势线U-JMB3 例&SI露标至 di模拟运JK衰为所选图表系列添加蓿数壮鹦线为所近图衰系列添加线性演测趋瞥线G或o各第折线涨/跌俣差刽 侬显圻话内容格式 段以四E样式I Tj：至土,则除所有酒 平田JgB国图1.，X:利涧表利涧表比率分析，She e t 3 铝(|南口四100%2018 年毛，条表系列添加 平均趋势比|I10.为毛利率数据系列添加趋势线目口-ii_-2.选择上回11.2.2利润表比率变化趋势分析11.设置趋势线格式12.查看添加的趋势线设置趋势媒格式趋势线选项线条颜色线型阴影发光和柔化边壕趋势线选项趋势预测 1/回归分析类型O移动平均血)周期 匚-laH趋势线名称。自动&):乘幕(毛利率)自定义:趋势预测前推电)：2.0 3.0.0&6)=叵显示公式)显示R平方值CB)4.单击匚全回11.2.2利润表比率变化趋势分析13.利用趋势线分析利率表比率Sd*9-J-利润表分析xkx Mic ro so ft Exc e ld 回 had开始 插入页面布局 公式 数据 室阅 视图 开发工具 特色功能文件目录CONTENTS11.1 利润预测与分析11.2 利润表分析11.3提高与技巧回113.1将图表保存为模板将图表保存为模板的方法为：在工作表中设置好图表后，在图表工具设计选项卡的 类型组中单击另存为模板按钮，并在打开的对话框中设置保存名称，如左图所示，然 后单击保存按钮确认设置。当需要使用此模板时，单击图表组中的展开按钮，在 打开的对话框中选择模板选项，然后选择保存的图表模板，如右图所示，最后单击确定 按钮即可应用保存的图表模板。更改图表类型管理模福山-S)模板 柱形图 折线图 饼图 条形图 面租图x Y儆点图）股价图 曲面图 圆环图 气泡图 雷达图回11.3.1将图表保存为模板单变量求解用于解决假定一个公式要取的某一结果值，其中变量的引用单元格应取值为多 少的问题。例如，假设某职工的年终奖金是其全年销售额的10%,其前三个季度的销售额在已 知的情况下，该职工想知道第四季度的销售额为多少时，才能保证年终奖金为15 000元。此时，便可利用单变量求解工具进行计算。其方法为：打开编辑好的工作表后，在数据 选项卡的数据工具组中单击模拟分析按钮，在打开的下拉列表中选择单变量求解 选项，然后在打开的对话框中依次设置目标单元格、目标值以及可变单元格，如图所示，最后 单击确定按钮即可得到需要的结果。感谢聆听！学习进步!Excel商务数据处理与分析