Exce练习10(重点)模拟3_参考答案.xls

年需求量 D 12000订货量 Q 年订货成本年储存成本 一次订货的订货成本 A 5006250 014400 0 单位年储存成本 PK 3020030000 03000 0 30020000 04500 0 订货量 Q 960 040015000 06000 0 年订货成本6250 050012000 07500 0 年储存成本14400 060010000 09000 0 年订储成本20650 07008571 410500 0 8007500 012000 0 经济订货量 EOQ 632 59006666 713500 0 EOQ下的年订货成本9486 810006000 015000 0 EOQ下的年储存成本9486 811005454 516500 0 EOQ下的年订储成本18973 712005000 018000 0 13004615 419500 0 14004285 721000 0 年需求量 12000 当前订货量 960 当前订货量的垂直参考线数据 960 045000 960 06250 0 960 014400 0 960 020650 0 960 00 当前年需求量下的经济订货量垂直参考线数据 632 545000 632 518973 7 632 50 0 6250 0 14400 0 20650 0 18973 7 经济订货量图形 14001300120011001000900800700600500400300200 0 5000 10000 15000 20000 25000 30000 35000 40000 45000 订 货 量 年订货成本年储存成本年订储成本 当前订货量 960年需求量 12000 ABCDEFGH 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 年订储成本使用公式或模拟运算表均可 20650 0 33000 0 24500 0 21000 0 19500 0 19000 0 19071 4 19500 0 20166 7 21000 0 21954 5 23000 0 24115 4 25285 7 第第1题题 某公司需采购某零件 全年需求量为15000件 每次订货成本为500元 单件零件的年储存成本为30元 当订货量为900件时 要求 1 计算年订货成本 年储存成本 年订储成本 2 依据基本的经济订货量模型 计算经济订货量及经济订货量时的年订储成本 3 在本工作表中生成一个运算表 计算当该零件的年订货成本 年储存成本 年订储成本随订货量从200 1400 按行分布 变化的值 此值 的计算方式不限 填入对应的单元格中 4 基于上述运算表绘制反映该零件的年订货成本 年储存成本 年订储成本随订货量 从200 1400 变化的图形 无数据点平滑线散点图 5 在图形中使用微调框与文本框控制当该零件年需求量从10000按增量1000变化到20000时 经济订货量及经济订货量下的年订储成本的值 并在图形上反映出来 6 在图形中使用微调框与文本框反映出当订货量从400按增量10变化到1000时年订货成本 年储存成本 年订储成本的值 并在图形上反映 出来 7 生成的图形如下所示 并按所给图示对图形进行相应的格式化 注 储存成本 单位存储成本 订货量 2 存货单价 存储费率 订货量 2 订储成本 订货成本 储存成本 要求5与6 需在图形上添加当前订货量和经济订货量的垂直参考线及显示参考点的数据值来满足 6250 0 14400 0 20650 0 18973 7 经济订货量图形 14001300120011001000900800700600500400300200 0 5000 10000 15000 20000 25000 30000 35000 40000 45000 订 货 量 年订货成本年储存成本年订储成本 当前订货量 960年需求量 12000 IJKLMNOPQR 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 第第1题题 某公司需采购某零件 全年需求量为15000件 每次订货成本为500元 单件零件的年储存成本为30元 当订货量为900件时 要求 1 计算年订货成本 年储存成本 年订储成本 2 依据基本的经济订货量模型 计算经济订货量及经济订货量时的年订储成本 3 在本工作表中生成一个运算表 计算当该零件的年订货成本 年储存成本 年订储成本随订货量从200 1400 按行分布 变化的值 此值 的计算方式不限 填入对应的单元格中 4 基于上述运算表绘制反映该零件的年订货成本 年储存成本 年订储成本随订货量 从200 1400 变化的图形 无数据点平滑线散点图 5 在图形中使用微调框与文本框控制当该零件年需求量从10000按增量1000变化到20000时 经济订货量及经济订货量下的年订储成本的值 并在图形上反映出来 6 在图形中使用微调框与文本框反映出当订货量从400按增量10变化到1000时年订货成本 年储存成本 年订储成本的值 并在图形上反映 出来 7 生成的图形如下所示 并按所给图示对图形进行相应的格式化 注 储存成本 单位存储成本 订货量 2 存货单价 存储费率 订货量 2 订储成本 订货成本 储存成本 要求5与6 需在图形上添加当前订货量和经济订货量的垂直参考线及显示参考点的数据值来满足 STUVWXY 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 年需求量 D 单位年储存成本 PK 不不同同需需求求量量下下的的年年总总成成本本随随订订货货量量的的变变化化 1000030205001000015000 15000251003150046250 20000202001800025000 25000153001450018750 30000104001350016250 5001350015250 年需求量 D 300006001400015000 对应的单位年储存成本10700 14785 7143 15178 5714 一次订货的订货成本 A 3008001575015625 900 16833 333316250 订货量 Q 50010001800017000 年订货成本180001100 19227 2727 17840 9091 年储存成本250012002050018750 年总成本 205001300 21807 6923 19711 5385 1400 23142 8571 20714 2857 经济订货量 EOQ 1341 615002450021750 EOQ下的订货成本6708 203932 EOQ下的储存成本6708 203932 EOQ下的年总成本13416 40786订货量年需求量 D 年订货成本 500100006000 500150009000 当前年需求量 300005002000012000 5002500015000 经济订货量的垂直参考线5003000018000 1341 6450000 1341 6413416 40786 1341 6410000年需求量 D EOQEOQ年订货成本 10000447 213595 6708 20393 150006007500 20000774 596669 7745 96669 2500010007500 300001341 64079 6708 20393 不同年需求量下的年总成本随订货量的变化图形 150013001100900700500300100 10000 15000 20000 25000 30000 35000 40000 45000 50000 订货量 年 总 成 本 当前年需求量 30000 不同年需求量下的年总成本随订货量的变化图形 150013001100900700500300100 10000 15000 20000 25000 30000 35000 40000 45000 50000 订货量 年 总 成 本 当前年需求量 30000 20000250003000030000 61000757509050090500 32000390004600046000 23000272503150031500 19000217502450024500 17000187502050020500 16000170001800018000 15571 42857 15964 2857 16357 1429 16357 14 15500153751525015250 15666 66667 15083 33331450014500 16000150001400014000 16454 54545 15068 1818 13681 8182 13681 82 17000152501350013500 17615 38462 15519 2308 13423 0769 13423 08 18285 71429 15857 1429 13428 5714 13428 57 19000162501350013500 年存储成本年总成本 750013500 625015250 500017000 375018750 250020500 EOQ年存储成本 EOQ年总成本 6708 203932 13416 4079 750015000 7745 966692 15491 9334 750015000 6708 203932 13416 4079 第第2 2题题 1 在本工作表中先建立一个运算表 静态 的二维表格 计算当年需求量 按列分布 分别为10000 15000 20000 25000和30000 订货量 按行分布 以增量100从100起始变化到1500时 年总成本 为年订货成本与年储存成本之和 的各个值 此值的计算方式不限 填入对应的行与列的交叉单元格中 2 计算在不同年需求量下 当订货量为500时的年订货成本 年储存成本和年总成本 3 计算在不同年需求量下的经济订货量 EOQ 以及EOQ下的年订货成本 年储存成本和年总成本 4 基于建立的运算表 绘制在不同年需求量下的年总成本随订货量变化的图形 无数据点平滑线散点图 其中在当 前年需求量下的年总成本线设为大红色 其它年总成本线为 灰色 25 线形的粗细均为 次粗线 5 在图中添加一个可以对当前年需求量进行调节的微调框和文本框 年需求量的最小值为10000 最大值为30000 步长为5000 使在当前年需求量下的红色年总成本线可以随之移动 6 在图中添加一个 不同年需求量下的经济订货量 垂直参考线 该参考线经过红色成本线的最低点并显示该点 大小为6磅 的数据值 7 生成的图形如下图所示 并按所给图示对图形进行相应的格式化 提提示示 B8单元格中的 年需求量 的值可从微调框所链接的单元格获取 此单元格可为 动态数据列 的第一个单元格 不不同同需需求求量量下下的的年年总总成成本本随随订订货货量量的的变变化化 不同年需求量下的年总成本随订货量的变化图形 150013001100900700500300100 10000 15000 20000 25000 30000 35000 40000 45000 50000 订货量 年 总 成 本 当前年需求量 30000 不同年需求量下的年总成本随订货量的变化图形 150013001100900700500300100 10000 15000 20000 25000 30000 35000 40000 45000 50000 订货量 年 总 成 本 当前年需求量 30000 第第2 2题题 1 在本工作表中先建立一个运算表 静态 的二维表格 计算当年需求量 按列分布 分别为10000 15000 20000 25000和30000 订货量 按行分布 以增量100从100起始变化到1500时 年总成本 为年订货成本与年储存成本之和 的各个值 此值的计算方式不限 填入对应的行与列的交叉单元格中 2 计算在不同年需求量下 当订货量为500时的年订货成本 年储存成本和年总成本 3 计算在不同年需求量下的经济订货量 EOQ 以及EOQ下的年订货成本 年储存成本和年总成本 4 基于建立的运算表 绘制在不同年需求量下的年总成本随订货量变化的图形 无数据点平滑线散点图 其中在当 前年需求量下的年总成本线设为大红色 其它年总成本线为 灰色 25 线形的粗细均为 次粗线 5 在图中添加一个可以对当前年需求量进行调节的微调框和文本框 年需求量的最小值为10000 最大值为30000 步长为5000 使在当前年需求量下的红色年总成本线可以随之移动 6 在图中添加一个 不同年需求量下的经济订货量 垂直参考线 该参考线经过红色成本线的最低点并显示该点 大小为6磅 的数据值 7 生成的图形如下图所示 并按所给图示对图形进行相应的格式化 提提示示 B8单元格中的 年需求量 的值可从微调框所链接的单元格获取 此单元格可为 动态数据列 的第一个单元格 年需求量 D 20000订货量 Q 全年总成本 每次订货成本 A 60005119500 0 储存1吨的年成本 PK 5314005122814 3 每吨矿石价格 P 25015005119750 0 每次订货量 Q 300016005117400 0 全年采购成本500000017005115638 2 全年订货成本4000018005114366 7 全年储存成本7950019005113507 9 全年总成本511950020005113000 0 公公式式法法 21005112792 9 最优订货量2127 98070622005112845 5 全年总成本极小值5112782 97723005113123 9 24005113600 0 25005114250 0 查查表表法法 26005115053 8 最优订货量210027005115994 4 全年总成本极小值5112792 8628005117057 1 29005118229 3 30005119500 0 31005120859 7 32005122300 0 33005123813 6 34005125394 1 5112782 977 34003200300028002600240022002000180016001400 5105000 5125000 订货量 全 年 总 成 本 储存1吨的年成本 PK 储存1吨的年成本 PK 最优订货量全年总成本极小值 2128 05112782 977 502190 890235109544 512 512169 3045785110634 533 522148 3446225111713 92 532127 9807065112782 977 542108 1851075113841 996 552088 9318715114891 253 562070 1966785115931 014 572051 9567045116961 532 582034 1905115117983 05 592016 8779365118995 798 6020005120000 储存1吨的年成本 PK 53元 经济订货量的水平参考线977 2127 9807065112782 977 34005112782 977 第第3题题某炼钢厂每年生产需要消耗20000吨矿石 每吨矿石价格250元 该厂每次向矿石供应商订购时 需要花费手 续费等合计6000元 储存1吨矿石的年成本为50元 要求 1 在本工作表中构造一个模型来确定当矿石订购量等于3000吨时的全年采购成本 全年订货成本 全年储存成本 全年总成本 为前三项成本之和 利用理论公式计算出最优订货量及全年总成本极小值 2 1 在本工作表中建立一运算表 二维表格 利用理论公式计算当订货量 按行分布 从1400按步长100变化 到3400时全年总成本随订货量变化的值 2 基于此运算表制作一个订货量从1400按步长100变化到3400时表示全年总成本随订货量变化的曲线 蓝色 粗细为 次粗线 图形 无数据点平滑线散点图 3 1 在本工作表中建立一运算表 二维表格 利用理论公式计算当储存1吨的年成本 按行分布 从50按步长1 变化到60时的最优订货量和全年总成本极小值 2 基于此运算表 在图形上添加当储存1吨的年成本从50按步长1变化到60时的最优订货量和全年总成本极小 值构成的空心红色点子 大小为6磅 和红色曲线 4 在图形上添加一条反映当前最优订货量且随控件变化的红色水平参考线 粗细为 次粗线 及实心的红色点子 大小为6磅 和该参考点的数据值 5 在图形上添加一个微调框和文本框控制当储存1吨的年成本从50按步长1变化到60时 红色水平线 点子及蓝色曲 线随之而动 6 依据要求2中建立的运算表的数据 使用INDEX 函数嵌套MATCH 函数与MIN 函数 分别查表求出最优订货量及 对应的全年总成本的极小值 7 生成的图形如下所示 并按所给图示对图形进行相应的格式化 5112782 977 34003200300028002600240022002000180016001400 5105000 5125000 订货量 全 年 总 成 本 储存1吨的年成本 PK 第第3题题某炼钢厂每年生产需要消耗20000吨矿石 每吨矿石价格250元 该厂每次向矿石供应商订购时 需要花费手 续费等合计6000元 储存1吨矿石的年成本为50元 要求 1 在本工作表中构造一个模型来确定当矿石订购量等于3000吨时的全年采购成本 全年订货成本 全年储存成本 全年总成本 为前三项成本之和 利用理论公式计算出最优订货量及全年总成本极小值 2 1 在本工作表中建立一运算表 二维表格 利用理论公式计算当订货量 按行分布 从1400按步长100变化 到3400时全年总成本随订货量变化的值 2 基于此运算表制作一个订货量从1400按步长100变化到3400时表示全年总成本随订货量变化的曲线 蓝色 粗细为 次粗线 图形 无数据点平滑线散点图 3 1 在本工作表中建立一运算表 二维表格 利用理论公式计算当储存1吨的年成本 按行分布 从50按步长1 变化到60时的最优订货量和全年总成本极小值 2 基于此运算表 在图形上添加当储存1吨的年成本从50按步长1变化到60时的最优订货量和全年总成本极小 值构成的空心红色点子 大小为6磅 和红色曲线 4 在图形上添加一条反映当前最优订货量且随控件变化的红色水平参考线 粗细为 次粗线 及实心的红色点子 大小为6磅 和该参考点的数据值 5 在图形上添加一个微调框和文本框控制当储存1吨的年成本从50按步长1变化到60时 红色水平线 点子及蓝色曲 线随之而动 6 依据要求2中建立的运算表的数据 使用INDEX 函数嵌套MATCH 函数与MIN 函数 分别查表求出最优订货量及 对应的全年总成本的极小值 7 生成的图形如下所示 并按所给图示对图形进行相应的格式化 折扣起点批量 Q 配件单价折扣价 P 18 30007 60006 80005 99999999 年需求量 件 D 20000 每次订货费用 A 1000 年存储费率 K 15 折扣起点批量 Q 60002 对应的配件单价折扣价6 折扣起点批量下的总存货费用126033 3333 不同单价折扣价下的经济订货量 EOQ EOQ是否有效左边界的总费用 5773 502692无效20160000 6 6172 133998无效148241 6667 6666 666667有效126033 3333 7302 967433无效105500 总存货费用的最小值105500 0 订货量的最优解8000 126033 3333 900080007000600050004000300020001000 100000 110000 120000 130000 140000 150000 160000 170000 180000 190000 200000 6000 折扣起点批量 右边界的总费用EOQ下的总费用有效的最低总存货费用 168466 6667166928 2032168466 6667 146483 3333146480 7407146483 3333 126100126000126000 37599999 83105477 2256105500 126033 3333 900080007000600050004000300020001000 100000 110000 120000 130000 140000 150000 160000 170000 180000 190000 200000 6000 折扣起点批量 订货量 Q 总存货费用订货量 Q 折扣起点批量 Q 126033 3126033 338765 1000180600 01000180600160525140450120375 1500174233 31500 174233 3 154120 8 134008 3 113895 8 2000171200 02000171200151050130900110750 2500169500 02500169500 149312 5129125 108937 5 3000148241 73000 168466 7 148241 7 128016 7 107791 7 3500147551 83500 167814 3 147551 8 127289 3 107026 8 4000147100 04000167400147100126800106500 4500146806 94500 167144 4 146806 9 126469 4 106131 9 5000146625 05000167000146625126250105875 5500146523 95500 166936 4 146523 9 126111 4 105698 9 6000126033 36000 166933 3 146483 3 126033 3 105583 3 6500126001 96500 166976 9 146489 4 126001 9 105514 4 7000126007 17000 167057 1 146532 1 126007 1 105482 1 7500126041 77500 167166 7 146604 2 126041 7 105479 2 8000105500 08000167300146700126100105500 8500105540 48500 167452 9 146815 4 126177 9 105540 4 9000105597 29000 167622 2 146947 2 126272 2 105597 2 折扣点批量下的垂直参考线 6000 00200000 6000 00126033 3333 6000 00100000 第第4 4题题 1 当B11单元格显示不同的折扣起点批量时 在B13单元格中计算不同折扣起点批量下的总存货费用 总 存货费用 年采购费用 年订货费用 年储存费用 2 根据基本的经济订货量模型在A16 A19单元格区域计算出不同单价折扣价下的经济订货量 3 判断以上计算出的经济订货量是否有效 并使用IF 函数嵌套AND 函数的公式在B16 B19的单元格区 域显示 有效 或 无效 4 对有效的经济订货量求出其对应的总存货费用 5 在本工作表中建立一运算表 二维表格 利用理论公式计算当订货量 按行分布 从1000按步长500 变化到9000时总存储费用随订货量变化的值 6 依据此运算表的相关数据和有效经济订货量的总存货费用 求出总存货费用的最小值 7 根据总存货费用的最小值找出订货量的最优解 8 基于此运算表制作一个订货量从1000按步长500变化到9000时 总存货费用随订货量变化的曲线 绿色 粗细为 次粗线 图形 无数据点平滑线散点图 9 在图形中添加一个垂直参考线 粉红色 粗细为 次粗线 该参考线显示 折扣起点批量下的总 存货费用 数据参考点 大小为6磅 及其值 10 在图形上添加一个可以对折扣起点批量进行选择的组合框及文本框 当选择不同的折扣起点 3000 6 000 8000 时 B11单元格显示相应的折扣起点值 并且垂直参考线也将随之而动 11 生成的图形如下所示 并按所给图示对图形进行相应的格式化 提提示示 1 此题使用非连续价格的折扣优惠方式处理 第第4 4题题 1 当B11单元格显示不同的折扣起点批量时 在B13单元格中计算不同折扣起点批量下的总存货费用 总 存货费用 年采购费用 年订货费用 年储存费用 2 根据基本的经济订货量模型在A16 A19单元格区域计算出不同单价折扣价下的经济订货量 3 判断以上计算出的经济订货量是否有效 并使用IF 函数嵌套AND 函数的公式在B16 B19的单元格区 域显示 有效 或 无效 4 对有效的经济订货量求出其对应的总存货费用 5 在本工作表中建立一运算表 二维表格 利用理论公式计算当订货量 按行分布 从1000按步长500 变化到9000时总存储费用随订货量变化的值 6 依据此运算表的相关数据和有效经济订货量的总存货费用 求出总存货费用的最小值 7 根据总存货费用的最小值找出订货量的最优解 8 基于此运算表制作一个订货量从1000按步长500变化到9000时 总存货费用随订货量变化的曲线 绿色 粗细为 次粗线 图形 无数据点平滑线散点图 9 在图形中添加一个垂直参考线 粉红色 粗细为 次粗线 该参考线显示 折扣起点批量下的总 存货费用 数据参考点 大小为6磅 及其值 10 在图形上添加一个可以对折扣起点批量进行选择的组合框及文本框 当选择不同的折扣起点 3000 6 000 8000 时 B11单元格显示相应的折扣起点值 并且垂直参考线也将随之而动 11 生成的图形如下所示 并按所给图示对图形进行相应的格式化 提提示示 1 此题使用非连续价格的折扣优惠方式处理 单价折扣率5 0 5订货量年总成本 折扣阈限值155092213 71 年需求量 D 1500030097000 00 一次订货的订货成本 A 500 0040092250 00 单位年储存成本 PK 30 0050090000 00 名义采购单价 P 4 5060089000 00 实际采购单价4 2870088714 29 80088875 00 实际订货量 Q 155090089333 33 年订货成本4838 71155095588 70 年储存成本23250 00155092213 71 年采购成本64125 004000 126000 00 年总成本92213 71 经济订货量 EOQ 707 1 经济订货量的垂直参考线 EOQ下的年订货成本10606 60707 1130000 EOQ下的年储存成本10606 60707 188713 20 EOQ下的年采购成本67500 00707 180000 EOQ下的年总成本88713 20 年总成本极小值的水平参考线 30088713 20 707 188713 20 折扣阈限值 1550单价折扣率 5 400088713 20 92213 71 8871