模拟试题(III)操作步骤.doc

模拟题（III）操作步骤【第1题】某公司需采购某零件，全年需求量为15000件，每次订货成本为500元，单件零件的年储存成本为30元，当订货量为900件时，要求：1、 计算年订货成本、年储存成本、年订储成本。2、 依据基本的经济订货量模型，计算经济订货量及经济订货量时的年订储成本。3、 在本工作表中生成一个运算表，计算当该零件的年订货成本、年储存成本、年订储成本随订货量从2001400(按行分布)变化的值(此值的计算方式不限，填入对应的单元格中)。4、基于上述运算表绘制反映该零件的年订货成本、年储存成本、年订储成本随订货量(从2001400)变化的图形(无数据点平滑线散点图)5、在图形中使用微调框与文本框控制当该零件年需求量从10000按增量1000变化到20000时，经济订货量及经济订货量下的年订储成本的值，并在图形上反映出来。6、在图形中使用微调框与文本框反映出当订货量从400按增量10变化到1000时年订货成本、年储存成本、年订储成本的值，并在图形上反映出来。7、生成的图形如下所示，并按所给图示对图形进行相应的格式化。注： 储存成本=(单位存储成本订货量)/2=(存货单价存储费率订货量)/2、订储成本=订货成本+储存成本。 要求5与6，需在图形上添加当前订货量和经济订货量的垂直参考线及显示参考点的数据值来满足。年需求量(D)15000一次订货的订货成本(A)500单位年储存成本(PK)30订货量(Q)900.0年订货成本年储存成本年订储成本经济订货量(EOQ)EOQ下的年订货成本EOQ下的年储存成本EOQ下的年订储成本操作步骤：1、计算年订货成本、年储存成本、年订储成本： C7=C3*C2/C6 C8=C4*C6/2 C9=C7+C82、依据基本的经济订货量模型，计算经济订货量及经济订货量时的年订储成本： C11=SQRT(2*C3*C2/C4) C12=C3*C2/C11 C13=C4*C11/2 C14=C12+C133、 在本工作表中生成一个运算表，计算当该零件的年订货成本、年储存成本、年订储成本随订货量从2001400(按行分布)变化的值(此值的计算方式不限，填入对应的单元格中)。 在F3:F15中输入：200、300、1400G3=$C$2/F3*$C$3 复制到G4:G15中H3=F3/2*$C$4 复制到H4:H15中I3=G3+H3 复制到I4:I15中4、基于上述运算表绘制反映该零件的年订货成本、年储存成本、年订储成本随订货量(从2001400)变化的图形(无数据点平滑线散点图) 选中F3:I15图表向导XY散点图无数据点平滑线散点图 调整X轴刻度：右击X轴坐标轴格式刻度最小值：200最大值：1400主要刻度单位：100 同样的方法调整Y轴刻度5、在图形中使用微调框与文本框控制当该零件年需求量从10000按增量1000变化到20000时，经济订货量及经济订货量下的年订储成本的值，并在图形上反映出来。在数据表中添加可动态显示年需求量的单元格B16： =年需求量=&C2在数据表中添加可动态显示当前订货量的单元格B17： =当前订货量=&C6在图表中添加反映年需求量变化的微调框与文本框： 添加微调框最小值：10000最大值：30000步长：1000单元格链接：$C$2 利用绘图工具栏中的文本框按钮添加文本框 在地址栏中输入：= B16在图表中添加反映当前订货量变化的微调框与文本框： 添加微调框最小值：400最大值：1000步长：10单元格链接： $C$6 利用绘图工具栏中的文本框按钮添加文本框 在地址栏中输入：= B176、添加当前订货量和经济订货量的垂直参考线在数据表中的B21:C25区域中添加用于制作当前订货量垂直参考线的数据，其中： B21=C6、B22=B21、B23=B22、B24=B23、B25=B24 C21=0、C22=C7、C23=C8、C24=C9、C25=35000添加当前订货量垂直参考线 选中B21:C25复制点击图表区域编辑选择性粘贴添加单元格为：新建系列数值（Y）轴在：列勾选“首列为分类X值”在数据表中的G21:H25区域中添加用于制作经济订货量垂直参考线的数据，其中： G21=C11、G22= C11、G23= C11、G24= C11、G25= C11 H21=0、H22=C12、H23=C14、H24=C15、H25=35000添加经济订货量垂直参考线 选中G21:H25复制点击图表区域编辑选择性粘贴添加单元格为：新建系列数值（Y）轴在：列勾选“首列为分类X值”7、生成的图形如下所示，并按所给图示对图形进行相应的格式化。8、右击经济订货量垂直线 源数据 系列 系列5 名称：=“经济订货量”右击当前订货量垂直线 源数据 系列 系列4 名称：=“当前订货量”第2题利用Excel建立基本的经济订货量模型。假设一件商品在仓库里储存一年的费用与年需求量的关系如本工作表中单元格区域A1:B6所示，订一次货的成本为300元。要求：1、在本工作表中先建立一个运算表(静态的二维表格)。计算当年需求量(按列分布)分别为10000、15000、20000、25000和30000，订货量(按行分布)以增量100从100起始变化到1500时，年总成本(为年订货成本与年储存成本之和)的各个值(此值的计算方式不限，填入对应的行与列的交叉单元格中)。2、计算在不同年需求量下，当订货量为500时的年订货成本、年储存成本和年总成本。3、计算在不同年需求量下的经济订货量(EOQ)以及EOQ下的年订货成本、年储存成本和年总成本。4、基于建立的运算表，绘制在不同年需求量下的年总成本随订货量变化的图形(无数据点平滑线散点图)。其中在当前年需求量下的年总成本线设为大红色，其它年总成本线为“灰色-25%”，线形的粗细均为“次粗线”。5、在图中添加一个可以对当前年需求量进行调节的微调框和文本框，年需求量的最小值为10000，最大值为30000，步长为5000。使在当前年需求量下的红色年总成本线可以随之移动。6、在图中添加一个“不同年需求量下的经济订货量”垂直参考线，该参考线经过红色成本线的最低点并显示该点（大小为6磅）的数据值。7、生成的图形如下图所示，并按所给图示对图形进行相应的格式化。提示： 在建立的运算表中应添加一“动态数据列”，该列数据将“动态”反映不同年需求量下的年总成本随订货量变化的值；“对应的单位年储存成本”单元格的值可使用INDEX()函数嵌套MATCH()函数的方式依据不同的年需求量从单元格区域A1:B6“动态”获取； B8单元格中的“年需求量”的值可从微调框所链接的单元格获取，此单元格可为“动态数据列”的第一个单元格。年需求量(D)单位年储存成本(PK)10000301500025200002025000153000010年需求量（D）对应的单位年储存成本一次订货的订货成本（A）300订货量（Q）500年订货成本年储存成本年总成本 经济订货量(EOQ)EOQ下的订货成本EOQ下的储存成本EOQ下的年总成本提示： 在建立的运算表中应添加一“动态数据列”，该列数据将“动态”反映不同年需求量下的年总成本随订货量变化的值；“对应的单位年储存成本”单元格的值可使用INDEX()函数嵌套MATCH()函数的方式依据不同的年需求量从单元格区域A1:B6“动态”获取； B8单元格中的“年需求量”的值可从微调框所链接的单元格获取，此单元格可为“动态数据列”的第一个单元格。操作步骤：1、 根据公式计算如下数据：输入年需求量：B8=30000 计算对应的单位年存储成本：B9=INDEX(B2:B6, MATCH(B8, A2:A6, 0)或：B9=VLOOKUP(B8, A2:B6, 2)计算年订货成本：B13=B10*B8/B12计算年储存成本：B14=B9*B12/2计算总成本： B15=B13+B14计算经济订货量（EOQ）：B17=SQRT(2*B10*B8/B9)计算EOQ下的订货成本：B18=B10*B8/B17计算EOQ下的储存成本：B19=B9*B17/2计算EOQ下的年总成本：B20=B18+B192、 在本工作表中先建立一个运算表(“静态”的二维表格)。计算当年需求量(按列分布)分别为10000、15000、20000、25000和30000，订货量(按行分布)以增量100从100起始变化到1500时，年总成本(为年订货成本与年储存成本之和)的各个值(此值的计算方式不限，填入对应的行与列的交叉单元格中)。在E3:E17中输入：100、200、300、1500在F2、G2、H2、I2 、J2中输入：10000、15000、20000、25000、30000 F3 =$B$10*F$2/$E3+$B$2*$E3/2复制到F4:F17中G3 =$B$10*G$2/$E3+$B$3*$E3/2复制到G4:G17中H3 =$B$10*H$2/$E3+$B$4*$E3/2复制到H4:H17中I3 =$B$10*I$2/$E3+$B$5*$E3/2复制到I4:I17中J3 =$B$10*J$2/$E3+$B$6*$E3/2复制到J4:J17中或：F3=E$2/$D3*$B$10+$D3/2*INDEX($B$2:$B$6,MATCH(E$2,$A$2:$A$6,0) 复制到G3:J3中 选中F3:J3公式复制到F4:J17或：选中E2:J17数据模拟运算表输入引用行的单元格：B8输入引用列的单元格：B12确定3、绘制曲线图选中E3:J17图表向导XY散点图无数据点平滑线散点图将所有曲线的颜色都设置成灰色。右击Y轴坐标轴格式刻度最小值：10000最大值：50000主要刻度单位:5000右击X轴坐标轴格式刻度最小值：100最大值：1500主要刻度单位:200 4、其中在当前年需求量下的年总成本线设为大红色，其它年总成本线为“灰色-25%”，线形的粗细均为“次粗线”。添加一列数据用于显示当前年需求量下的年总成本：K2=B8 K3 =$B$10*K$2/$E3+$B$9*$E3/2 或 =HLOOKUP($K$2,$F$2:$J$17,ROW()-1) 将K3中的公式复制到K4:K17中 选中图表，单击右键选择“源数据”，选择“系列”选项卡，在“系列（S）”下单击“添加”按钮，添加了“系列6”，在右侧“X值”后设置为E3:E17，“Y值”后设置为K3:K17，单击“确定”按钮 将新添加的曲线设置成红色。5、在图中添加一个可以对当前年需求量进行调节的微调框和文本框，年需求量的最小值为10000，最大值为30000，步长为5000。使在当前年需求量下的红色年总成本线可以随之移动。在数据表的A22单元格中输入：= “当前年需求量=”&B8设置A22单元格为白底红字 添加微调框右击微调框设置控件格式控制 最小值：10000 最大值：30000步长：5000单元格链接：B8添加文本框在编辑栏输入：= A226、在图中添加一个“不同年需求量下的经济订货量”垂直参考线，该参考线经过红色成本线的最低点并显示该点（大小为6磅）的数据值。添加垂直线数据：F22=B17、F23=B17、F24=B17 G22=10000、G23=B20、G24=50000添加垂直线：选择F22:G24复制选择图表编辑选择性粘贴添加单元格为：新建系列数值（Y）轴在：列首列为分类X值调整垂直线的属性：红色中粗在于成本线最低点交叉处显示数值数字大小为：6磅右击选择垂直线与红色曲线的交叉点 数据点格式 图案 数据标记 自定义样式：矩形 前景色 枚红色 数据标志 勾选Y值7、并按所给图示对图形进行相应的格式化。第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、生成的图形如下所示，并按所给图示对图形进行相应的格式化。年需求量(D)20000每次订货成本(A)6000储存1吨的年成本(PK)50每吨矿石价格(P)250每次订货量(Q)3000全年采购成本全年订货成本全年储存成本全年总成本公式法：最优订货量全年总成本极小值查表法：最优订货量全年总成本极小值操作步骤：1、在本工作表中构造一个模型来确定当矿石订购量等于3000吨时的全年采购成本、全年订货成本、全年储存成本、全年总成本（为前三项成本之和）。利用理论公式计算出最优订货量及全年总成本极小值。 C7=C2*C5 C8=C3*C2/C6 C9=C4*C6/2 C10=C7+C8+C9 C12=SQRT(2*C3*C2/C4) C13=SQRT(2*C3*C2*C4)+C5*C22、（1）在本工作表中建立一运算表（二维表格），利用理论公式计算当订货量（按行分布）从1400按步长100变化到3400时全年总成本随订货量变化的值。 在E2:E22中生成不同的订货量：1400、1500、3400 在F2:F22中计算不同订货量下的全年总成本：F2=$C$2*$C$5+$C$2*$C$3/E2+$C$4*E2/2 公式复制到F3:F22中（2）基于此运算表制作一个订货量从1400按步长100变化到3400时表示全年总成本随订货量变化的曲线（蓝色、粗细为“次粗线”）图形(无数据点平滑线散点图)。 选中E2:F22图表向导XY散点图无数据点平滑线散点图调整Y轴刻度最小值：5105000最大值：5125000主要刻度单位：200003、（1）在本工作表中建立一运算表（二维表格），利用理论公式计算当储存1吨的年成本（按行分布）从50按步长1变化到60时的最优订货量和全年总成本极小值。在E26:E36中输入：50、51、52、60F26=SQRT(2*$C$3*$C$2/E26) 复制到F27:F36中G26=SQRT(2*$C$3*E26*$C$2)+$C$5*$C$2 复制到G27:G36中（2）基于此运算表，在图形上添加当储存1吨的年成本从50按步长1变化到60时的最优订货量和全年总成本极小值构成的空心红色点子(大小为6磅)和红色曲线。选中F26:G36复制进入图表页面编辑选择性粘贴添加单元格为：新建系列数值（Y）轴在：列确定右击最优订货量和全年总成本极小值构成的曲线数据系列格式线形颜色：红色粗细：中粗样式：圆形前景色：红色背景色：白色大小（Z）：6磅4、在图形上添加一条反映当前最优订货量且随控件变化的红色水平参考线（粗细为“次粗线”）及实心的红色点子(大小为6磅)和该参考点的数据值。设置水平参考线数据： B25=1400、B26=C13、B27=3400、C25=C14、C26= C14、C27= C14根据水平参考线数据绘制水平参考线： 选中B25:C27复制进入图表编辑选择性粘贴添加单元格为：新建系列数值（Y）轴在：列首列为分类X值5、在图形上添加一个微调框和文本框控制当储存1吨的年成本从50按步长1变化到60时，红色水平线、点子及蓝色曲线随之而动。在绘图区添加一个微调框 属性 最小值：50 最大值：60 步长：1单元格链接：C4设置文本框内容：B20=当前单位年储存成本=&C4在绘图区添加一个文本框 在编辑栏输入：=B206、依据要求2中建立的运算表的数据，使用INDEX()函数嵌套MATCH()函数与MIN()函数，分别查表求出最优订货量及对应的全年总成本的极小值。 C18= MIN(G26:G36)C17= INDEX(F26:F36, MATCH(C18, G26:G36, 0)7、按所给图示对图形进行相应的格式化。第4题 某企业每年需要配件20000件，每次订货费用为1000元，存储费用是零件单价的15%。供货商规定：凡一次性购买3000件以下的价格为8元/件、3000件或以上但6000件以下的价格为7元/件、6000件或以上但8000件以下的价格为6元/件、8000件或以上的价格为5元/件。根据此规定建立的折扣起点批量与零件单价折扣价的对应关系如本工作表中单元格区域A1:B5所示。要求：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、6000、8000)时，B11单元格显示相应的折扣起点值，并且垂直参考线也将随之而动。11、生成的图形如下所示，并按所给图示对图形进行相应的格式化。提示：（1）此题使用非连续价格的折扣优惠方式处理； （2）“对应的配件单价折扣价”单元格的值可使用INDEX()函数嵌套MATCH()函数的方式依据不同的“折扣起点批量”从单元格区域A1:B5“动态”获取；（3）订货量的最优解请使用INDEX()函数嵌套MATCH()函数的方式根据总存货费用的最小值从建立的运算表中获取。操作步骤：1、（先做第10题中的添加组合框控件）在图形上添加一个可以对折扣起点批量进行选择的组合框及文本框。当选择不同的折扣起点(3000、6000、8000)时，B11单元格显示相应的折扣起点值。添加组合框 属性 数据源区域：A3:A5 单元格链接：C11建立列表框与折扣起点批量（B11）的关联：B11=INDEX(E20:E22,F20)2、当B11单元格显示不同的折扣起点批量时，在B13单元格中计算不同折扣起点批量下的总存货费用。总存货费用=年采购费用+年订货费用+年储存费用。B12=IF(B113000,8,IF(B116000,7,IF(B118000,6,5)B13 =B7*B12+B7/B11*B8+B12*B9*B11/23、根据基本的经济订货量模型在A16:A19单元格区域计算出不同单价折扣价下的经济订货量。A16=SQRT(2*$B$7*$B$8/(B2*$B$9) 复制到A17:A194、判断以上计算出的经济订货量是否有效，并使用IF()函数嵌套AND()函数的公式在B16:B19的单元格区域显示“有效”或“无效”。B16=IF(A16=3000,A17=6000,A18=8000,有效,无效)5、对有效的经济订货量求出其对应的总存货费用。C16=IF(B16=有效,$B$7*B2+$B$7/A16*$B$8+A16/2*B2*$B$9,无效)复制到C17:C19中6、在本工作表中建立一运算表（二维表格），利用理论公式计算当订货量（按行分布）从1000按步长500变化到9000时总存储费用随订货量变化的值。生成订货量列：E2=1000 E3=1500 选中E2:E3 拖动句柄至E18计算不同订货量下的总存储费用：在F2：F5中单价用B2：F2=$B$2*$B$7+$B$8*$B$7/E2+$B$2*$B$9*E2/2在F6：F11中单价用B3：F2=$B$3*$B$7+$B$8*$B$7/E2+$B$3*$B$9*E2/2在F12：F15中单价用B4：F2=$B$4*$B$7+$B$8*$B$7/E2+$B$4*$B$9*E2/2在F16：F18中单价用B5：F2=$B$5*$B$7+$B$8*$B$7/E2+$B$5*$B$9*E2/2另一种方法：添加单价折扣列：G2=IF(E23000,8,IF(E26000,7,IF(E2=1500,$C$8,$C$7)+$C$4*$C$5/E3+E3*$C$6/2将F3中的公式复制到