模拟试题III操作步骤Word格式文档下载.docx
《模拟试题III操作步骤Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《模拟试题III操作步骤Word格式文档下载.docx（27页珍藏版）》请在冰豆网上搜索。
2、依据基本的经济订货量模型，计算经济订货量及经济订货量时的年订储成本：
C11=SQRT（2*C3*C2/C4）
C12=C3*C2/C11
C13=C4*C11/2
C14=C12+C13
①在F3:
F15中输入：
200、300、……1400
②G3=$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：
="
年需求量="
&
C2
②在数据表中添加可动态显示当前订货量的单元格B17：
当前订货量="
C6
③在图表中添加反映年需求量变化的微调框与文本框：
添加微调框→最小值：
10000→最大值：
30000→步长：
1000→单元格链接：
$C$2
④利用绘图工具栏中的文本框按钮添加文本框→在地址栏中输入：
=B16
⑤在图表中添加反映当前订货量变化的微调框与文本框：
400→最大值：
1000→步长：
10→单元格链接：
$C$6
⑥利用绘图工具栏中的文本框按钮添加文本框→在地址栏中输入：
=B17
6、添加当前订货量和经济订货量的垂直参考线
①在数据表中的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→复制→点击图表区域→编辑→选择性粘贴→添加单元格为：
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单元格中的“年需求量”的值可从微调框所链接的单元格获取，此单元格可为“动态数据列”的第一个单元格。
10000
25
20000
20
25000
15
30000
10
对应的单位年储存成本
300
年总成本
EOQ下的订货成本
EOQ下的储存成本
EOQ下的年总成本
1在建立的运算表中应添加一“动态数据列”，该列数据将“动态”反映不同年需求量下的年总成本随订货量变化的值；
1、根据公式计算如下数据：
①输入年需求量：
B8=30000
2计算对应的单位年存储成本：
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+B19
2、在本工作表中先建立一个运算表（“静态”的二维表格）。
①在E3:
E17中输入：
100、200、300、……、1500
②在F2、G2、H2、I2、J2中输入：
10000、15000、20000、25000、30000
3F3=$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轴→坐标轴格式→刻度→最小值：
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，单击“确定”按钮→将新添加的曲线设置成红色。
①在数据表的A22单元格中输入：
=“当前年需求量=”&
B8→设置A22单元格为白底红字
②添加微调框→右击微调框→设置控件格式→控制→最小值：
10000→最大值：
→步长：
5000→单元格链接：
B8
③添加文本框→在编辑栏输入：
=A22
①添加垂直线数据：
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（）函数，分别查表求出最优订货量及对应的全年总成本的极小值。
每次订货成本（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*C2
2、
（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→主要刻度单位：
①在E26:
E36中输入：
50、51、52、……、60
②F26=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、B26=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元，存储费用是零件单价的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（B11<
3000,8,IF（B11<
6000,7,IF（B11<
8000,6,5）））
②B13=B7*B12+B7/B11*B8+B12*B9*B11/2
3、根据基本的经济订货量模型在A16:
A16=SQRT（2*$B$7*$B$8/（B2*$B$9））→复制到A17:
A19
4、判断以上计算出的经济订货量是否有效，并使用IF（）函数嵌套AND（）函数的公式在B16:
①B16=IF（A16<
3000,"
有效"
"
无效"
）
②B17=IF（AND（A17>
=3000,A17<
6000）,"
③B18=IF（AND（A18>
=6000,A18<
8000）,"
④B19=IF（A19>
=8000,"
5、对有效的经济订货量求出其对应的总存货费用。
①C16=IF（B16="
$B$7*B2+$B$7/A16*$B$8+A16/2*B2*$B$9,"
②复制到C17:
C19中
6、在本工作表中建立一运算表（二维表格），利用理论公式计算当订货量（按行分布）从1000按步长500变化到9000时总存储费用
升级会员 