实验报告三(采购管理中的应用).docVIP

实验报告三 Excel在采购管理中的应用 一、实验事项 1.实验项目名称 Excel在采购管理中的应用 2.实验目标 通过对采购管理过程中最常用的几个表格，如“供应商评价”、“采购成本分析”等的编制与创建掌握使用Excel对供应商分类、评估的方法，以及计算采购成本和储存成本的最小值的方法，并学会使用Excel分析采购批次和单位采购量之间的关系，掌握绘制采购成本和存储成本与采购批次之间关系图表的方法。 3.实验任务 （1）创建“供应商评定表”并对供应商进行分级评价。 （2）创建“采购成本分析表”，计算最低采购成本、采购批次和采购量，添加调整“年采购量”、“采购成本”和“单位存储成本”数值的滚动条，绘制采购成本分析折线图。 二、实验数据 1.数据背景 采购业务是公司各项业务中很重要的一项内容，采购部门不仅要对公司年采购的各项数据进行对比分析，而且还要对供应商进行评价。采购管理实质上是针对供应链中上游厂商的管理，目的在于有效地整合供应商的资源以实现共赢。如何将众多的供应商分类，并做好供应商的资格评估，以及制定供应商管理办法是采购管理中的一项重要主题。采购成本分析是采购管理另一重要内容。企业需要时刻关注采购成本和存储成本的变化，调整采购策略。 2.实验数据 详见附表。 三、实验中所用到的公式 1.供货商公式VLOOKUP(C3,supplier,2,FALSE) 2.原料公式VLOOKUP(C3,supplier,3,FALSE) 3.分级符公式 =IF(F3=0,,IF(F395,C,IF(F385,F,IF(F375,%,#)))) 4.分级往来公式 =IF(F3=0,,IF(F395,优先往来,(IF(F385,正常往来,IF(F375,后备往来,不予往来))))) 5.采购数量=年采购量/年采购批次 平均存量=采购数量/2 存储成本=平均存量*单位存储成本 采购成本=年采购批次*采购成本 总成本=存储成本+采购成本 6.最低采购总成本=MIN(F2:F13) 采购批次=INDEX(A2:A13,MATCH(B16,F2:F13,0)) 采购量==INDEX(B2:B13,MATCH(B16,F2:F13,0)) 四、实验步骤 1.供应商评定管理 （1）打开“供应商评定工作簿”，在基础资料表中将A1:C11区域定义名称为“supplier”。 （2）在“供应商评定表”D3单元格定义公式，根据供应商编号，在上述步骤中定义名称为“supplier”的区域中查找对应的供应商。并将D3单元格的公式复制到D4:D110区域。 （3）在“供应商评定表”E3单元格定义公式，根据供应商编号，在上述步骤中定义名称为“supplier”的区域中查找对应的原料。并将E3单元格的公式复制到E4:E110区域。 （4）在“供应商评定表”G3单元格定义公式，根据月得分显示对应的分级符，并将G3单元格的公式复制到G4:G110区域。 （5）在“供应商评定表”H3单元格定义公式，根据月得分显示对应的分级往来方式，并将H3单元格的公式复制到H4:H110区域。 （6）在$B$2:$I$110区域创建列表。 2.采购成本分析 （1）打开“采购成本分析”表，在A2:A13区域用拖拽方式输入年采购批次12→1。 （2）定义B16单元格格式为“#,##0.00” ,定义E16单元格格式为“0”次/年””, 定义H16单元格格式为“0”件/次”” (3)在B2、C2、D2、E2、F2单元格分别输入采购数量、平均存量、存储成本、采购成本和总成本的计算公式，并将B2:F2已经定义了计算公式的区域拖拽至合适的位置复制公式。 (4) 在B16单元格输入最低总成本的计算公式，在E16输入采购批次的计算公式，在H16输入采购量的公式。 (5) 添加“年采购量”，“采购成本”和“单位存储成本”的滚动条窗体。设置“年采购量”滚动条控件格式为最小值为“2000”，最大值为4000，步长为200，单元格链接为B19,三维阴影。设置“采购成本”滚动条控件格式为最小值为“1000”，最大值为6000，步长为100，单元格链接为E19，三维阴影。设置“单位存储成本”滚动条控件格式为最小值为“20”，最大值为50，步长为1，当前值为50，单元格链接为H19，三维阴影。 (6)使用图表向导，绘制存储成本和采购成本随采购批次变化的折线图。图例显示位置为靠上，图例字号为9。坐标轴字号为9。绘图区图案区域颜色为“象牙色”。 (7)分析 “年采购量”、“采购成本”、“单位存储成本”、“最低总成本”、“采购批次”、和“采购量”以及折线图之间的动态变化关系。 五、实验总结 六、实验评价分析 1. 供应商评定管理 1. 打开“供应商评定工作簿”，在基础资料表中将A1:C1