模拟试题III操作步骤Word格式文档下载.docx

1、2、依据基本的经济订货量模型，计算经济订货量及经济订货量时的年订储成本： C11=SQRT（2*C3*C2/C4） C12=C3*C2/C11 C13=C4*C11/2 C14=C12+C13 在F3:F15中输入：200、300、1400G3=$C$2/F3*$C$3 复制到G4:G15中H3=F3/2*$C$4 复制到H4:H15中I3=G3+H3 复制到I4:I15中 选中F3:I15图表向导XY散点图无数据点平滑线散点图 调整X轴刻度：右击X轴坐标轴格式刻度最小值：200最大值：1400主要刻度单位：100 同样的方法调整Y轴刻度在数据表中添加可动态显示年需求量的单元格B16： =年需

2、求量=&C2在数据表中添加可动态显示当前订货量的单元格B17：当前订货量=C6在图表中添加反映年需求量变化的微调框与文本框： 添加微调框最小值：10000最大值：30000步长：1000单元格链接：$C$2 利用绘图工具栏中的文本框按钮添加文本框 在地址栏中输入：= B16在图表中添加反映当前订货量变化的微调框与文本框：400最大值：1000步长：10单元格链接： $C$6 利用绘图工具栏中的文本框按钮添加文本框 在地址栏中输入：= B176、添加当前订货量和经济订货量的垂直参考线在数据表中的B21:C25区域中添加用于制作当前订货量垂直参考线的数据，其中： B21=C6、B22=B21、B2

3、3=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复制点击图表区域编辑选择性粘贴添加单元格为：8、 右击经济

4、订货量垂直线 源数据 系列 系列5 名称：=“经济订货量” 右击当前订货量垂直线 源数据 系列 系列4 名称：=“当前订货量”第2题利用Excel建立基本的经济订货量模型。假设一件商品在仓库里储存一年的费用与年需求量的关系如本工作表中单元格区域A1:B6所示，订一次货的成本为300元。要求：1、在本工作表中先建立一个运算表（静态的二维表格）。计算当年需求量（按列分布）分别为10000、15000、20000、25000和30000，订货量（按行分布）以增量100从100起始变化到1500时，年总成本（为年订货成本与年储存成本之和）的各个值（此值的计算方式不限，填入对应的行与列的交叉单元格中）。

5、2、计算在不同年需求量下，当订货量为500时的年订货成本、年储存成本和年总成本。3、计算在不同年需求量下的经济订货量（EOQ）以及EOQ下的年订货成本、年储存成本和年总成本。4、基于建立的运算表，绘制在不同年需求量下的年总成本随订货量变化的图形（无数据点平滑线散点图）。其中在当前年需求量下的年总成本线设为大红色，其它年总成本线为“灰色-25%”，线形的粗细均为“次粗线”。5、在图中添加一个可以对当前年需求量进行调节的微调框和文本框，年需求量的最小值为10000，最大值为30000，步长为5000。使在当前年需求量下的红色年总成本线可以随之移动。6、在图中添加一个“不同年需求量下的经济订货量”垂

6、直参考线，该参考线经过红色成本线的最低点并显示该点（大小为6磅）的数据值。7、生成的图形如下图所示，并按所给图示对图形进行相应的格式化。提示： 在建立的运算表中应添加一“动态数据列”，该列数据将“动态”反映不同年需求量下的年总成本随订货量变化的值；“对应的单位年储存成本”单元格的值可使用INDEX（）函数嵌套MATCH（）函数的方式依据不同的年需求量从单元格区域A1:B6“动态”获取； B8单元格中的“年需求量”的值可从微调框所链接的单元格获取，此单元格可为“动态数据列”的第一个单元格。1000025200002025000153000010对应的单位年储存成本300年总成本 EOQ下的订货成

7、本EOQ下的储存成本EOQ下的年总成本1 在建立的运算表中应添加一“动态数据列”，该列数据将“动态”反映不同年需求量下的年总成本随订货量变化的值；1、 根据公式计算如下数据：输入年需求量：B8=300002 计算对应的单位年存储成本：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计算

8、EOQ下的储存成本：B19=B9*B17/2计算EOQ下的年总成本：B20=B18+B192、 在本工作表中先建立一个运算表（“静态”的二维表格）。在E3:E17中输入：100、200、300、1500在F2、G2、H2、I2 、J2中输入：10000、15000、20000、25000、300003 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:

9、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轴坐标轴格式刻度最小值：50000主要刻度单位:5000右击X轴坐标轴格式刻度最小值：100最大值：1500主要刻度单位:200 4、其中在当前年需

10、求量下的年总成本线设为大红色，其它年总成本线为“灰色-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，单击“确定”按钮 将新添加的曲线设置成红色。 在数据表的A22单元格中输入：= “当前年需求量=”&B8设置

11、A22单元格为白底红字 添加微调框右击微调框设置控件格式控制 最小值：10000 最大值：步长：5000单元格链接：B8 添加文本框在编辑栏输入：= A22 添加垂直线数据：F22=B17、F23=B17、F24=B17 G22=10000、G23=B20、G24=50000 添加垂直线：选择F22:G24复制选择图表编辑选择性粘贴添加单元格为：新建系列数值（Y）轴在：列首列为分类X值 调整垂直线的属性：红色中粗在于成本线最低点交叉处显示数值数字大小为：6磅 右击选择垂直线与红色曲线的交叉点 数据点格式 图案 数据标记 自定义 样式：矩形 前景色 枚红色 数据标志 勾选Y值7、并按所给图示对图

12、形进行相应的格式化。第3题某炼钢厂每年生产需要消耗20000吨矿石，每吨矿石价格250元，该厂每次向矿石供应商订购时，需要花费手续费等合计6000元，储存1吨矿石的年成本为50元。1、在本工作表中构造一个模型来确定当矿石订购量等于3000吨时的全年采购成本、全年订货成本、全年储存成本、全年总成本（为前三项成本之和）。利用理论公式计算出最优订货量及全年总成本极小值。2、（1）在本工作表中建立一运算表（二维表格），利用理论公式计算当订货量（按行分布）从1400按步长100变化到3400时全年总成本随订货量变化的值。 （2）基于此运算表制作一个订货量从1400按步长100变化到3400时表示全年总成

13、本随订货量变化的曲线（蓝色、粗细为“次粗线”）图形（无数据点平滑线散点图）。3、（1）在本工作表中建立一运算表（二维表格），利用理论公式计算当储存1吨的年成本（按行分布）从50按步长1变化到60时的最优订货量和全年总成本极小值。 （2）基于此运算表，在图形上添加当储存1吨的年成本从50按步长1变化到60时的最优订货量和全年总成本极小值构成的空心红色点子（大小为6磅）和红色曲线。4、在图形上添加一条反映当前最优订货量且随控件变化的红色水平参考线（粗细为“次粗线”）及实心的红色点子（大小为6磅）和该参考点的数据值。5、在图形上添加一个微调框和文本框控制当储存1吨的年成本从50按步长1变化到60时，

14、红色水平线、点子及蓝色曲线随之而动。6、依据要求2中建立的运算表的数据，使用INDEX（）函数嵌套MATCH（）函数与MIN（）函数，分别查表求出最优订货量及对应的全年总成本的极小值。每次订货成本（A）6000储存1吨的年成本（PK）50每吨矿石价格（P）250每次订货量（Q）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）在本工作表中建立一运算表（二维

15、表格），利用理论公式计算当订货量（按行分布）从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主要刻度单位

16、：在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复制进入图表页面编辑选择性粘贴添加单元格为：列确定右击最优订货量和全年总成本极小值构成的曲线数据系列格式线形颜色：红色粗细：中粗样式：圆形前景色：红色背景色：白色大小（Z）：设置水平参考线数据： B25=1400、B

17、26=C13、B27=3400、C25=C14、C26= C14、C27= C14根据水平参考线数据绘制水平参考线： 选中B25:C27复制进入图表编辑选择性粘贴添加单元格为： 在绘图区添加一个微调框 属性 最小值：50 最大值：60 步长：1单元格链接：C4 设置文本框内容：B20=当前单位年储存成本= 在绘图区添加一个文本框 在编辑栏输入：=B20 C18= MIN（G26:G36）C17= INDEX（F26:F36, MATCH（C18, G26:G36, 0）7、按所给图示对图形进行相应的格式化。第4题 某企业每年需要配件20000件，每次订货费用为1000元，存储费用是零件单价的1

18、5%。供货商规定：凡一次性购买3000件以下的价格为8元/件、3000件或以上但6000件以下的价格为7元/件、6000件或以上但8000件以下的价格为6元/件、8000件或以上的价格为5元/件。根据此规定建立的折扣起点批量与零件单价折扣价的对应关系如本工作表中单元格区域A1:B5所示。1、当B11单元格显示不同的折扣起点批量时，在B13单元格中计算不同折扣起点批量下的总存货费用。总存货费用=年采购费用+年订货费用+年储存费用。2、根据基本的经济订货量模型在A16:A19单元格区域计算出不同单价折扣价下的经济订货量。3、判断以上计算出的经济订货量是否有效，并使用IF（）函数嵌套AND（）函数的

19、公式在B16:B19的单元格区域显示“有效”或“无效”。4、对有效的经济订货量求出其对应的总存货费用。5、在本工作表中建立一运算表（二维表格），利用理论公式计算当订货量（按行分布）从1000按步长500变化到9000时总存储费用随订货量变化的值。6、依据此运算表的相关数据和有效经济订货量的总存货费用，求出总存货费用的最小值。7、根据总存货费用的最小值找出订货量的最优解。8、基于此运算表制作一个订货量从1000按步长500变化到9000时，总存货费用随订货量变化的曲线（绿色、粗细为“次粗线”）图形（无数据点平滑线散点图）。9、在图形中添加一个垂直参考线（粉红色、粗细为“次粗线”），该参考线显示“

20、折扣起点批量下的总存货费用”数据参考点（大小为6磅）及其值。10、在图形上添加一个可以对折扣起点批量进行选择的组合框及文本框。当选择不同的折扣起点（3000、6000、8000）时，B11单元格显示相应的折扣起点值，并且垂直参考线也将随之而动。11、生成的图形如下所示，并按所给图示对图形进行相应的格式化。（1）此题使用非连续价格的折扣优惠方式处理； （2）“对应的配件单价折扣价”单元格的值可使用INDEX（）函数嵌套MATCH（）函数的方式依据不同的“折扣起点批量”从单元格区域A1:B5“动态”获取；（3）订货量的最优解请使用INDEX（）函数嵌套MATCH（）函数的方式根据总存货费用的最小值

21、从建立的运算表中获取。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: A16=SQRT（2*$B$7*$B$8/（B2*$B$9） 复制到A17:A194、判断以上计算出的经济订货量是否有效，并使用IF（）函数嵌套AND（）函数的公式在B16: 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时总存储费用