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

模拟题（III）操作步骤
【第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，需在图形上添加当前订货量和经济订货量的垂直参考线及显示参考点的数据值来满足。

年需求量(D)
15000

一次订货的订货成本(A)
500

单位年储存成本(PK)
30

订货量(Q)
900.0

年订货成本

年储存成本

年订储成本

经济订货量(EOQ)

EOQ下的年订货成本

EOQ下的年储存成本

EOQ下的年订储成本

操作步骤：

1、计算年订货成本、年储存成本、年订储成本：
C7=C3*C2/C6
C8=C4*C6/2
C9=C7+C8

2、依据基本的经济订货量模型，计算经济订货量及经济订货量时的年订储成本：
C11=SQRT(2*C3*C2/C4)
C12=C3*C2/C11
C13=C4*C11/2
C14=C12+C13

3、 在本工作表中生成一个运算表，计算当该零件的年订货成本、年储存成本、年订储成本随订货量从200～1400(按行分布)变化的值(此值的计算方式不限，填入对应的单元格中)。

①在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中

4、基于上述运算表绘制反映该零件的年订货成本、年储存成本、年订储成本随订货量(从200～1400)变化的图形(无数据点平滑线散点图)

①选中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
⑥利用绘图工具栏中的文本框按钮添加文本框 → 在地址栏中输入：= 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→复制→点击图表区域→编辑→选择性粘贴→添加单元格为：新建系列→数值（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)
10000
30
15000
25
20000
20
25000
15
30000
10

年需求量（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+B19

2、 在本工作表中先建立一个运算表(“静态”的二维表格)。计算当年需求量(按列分布)分别为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
③添加文本框→在编辑栏输入：= A22

6、在图中添加一个“不同年需求量下的经济订货量”垂直参考线，该参考线经过红色成本线的最低点并显示该点（大小为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*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→主要刻度单位：20000

3、（1）在本工作表中建立一运算表（二维表格），利用理论公式计算当储存1吨的年成本（按行分布）从50按步长1变化到60时的最优订货量和全年总成本极小值。
①在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→复制→进入图表页面→编辑→选择性粘贴→添加单元格为：新建系列
→数值（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
③在绘图区添加一个文本框→ 在编辑栏输入：=B20

6、依据要求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(B11<3000,8,IF(B11<6000,7,IF(B11<8000,6,5)))
②B13 =B7*B12+B7/B11*B8+B12*B9*B11/2

3、根据基本的经济订货量模型在A16:A19单元格区域计算出不同单价折扣价下的经济订货量。
A16=SQRT(2*$B$7*$B$8/(B2*$B$9)) → 复制到A17:A19

4、判断以上计算出的经济订货量是否有效，并使用IF()函数嵌套AND()函数的公式在B16:B19的单元格区域显示“有效”或“无效”。
①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时总存储费用随订货量变化的值。
①生成订货量列：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(E2<3000,8,IF(E2<6000,7,IF(E2<8000,6,5)))→ 复制到G3:G18中
→计算不同订货量下的总存储费用：F2=$B$7*G2+$B$8*$B$7/E2+E2/2*G2*$B$9

7、依据此运算表的相关数据和有效经济订货量的总存货费用，求出总存货费用的最小值。
计算总存货量最小值：B21=MIN(F2:F18)

8、根据总存货费用的最小值找出订货量的最优解。
计算订货量的最优解：B23=INDEX(E2:E18,MATCH(B21,F2:F18,0))

9、基于此运算表制作一个订货量从1000按步长500变化到9000时，总存货费用随订货量变化的曲线（绿色、粗细为“次粗线”）图形(无数据点平滑线散点图)。

①选中E2:F18→图标向导→XY散点图→无数据点平滑线散点图
②右击Y轴→坐标轴格式→刻度→最小值：100000→最大值：200000→主要刻度单位:10000
③右击X轴→坐标轴格式→刻度→最小值：1000→最大值：9000→主要刻度单位:1000

10、在图形中添加一个垂直参考线（粉红色、粗细为“次粗线”），该参考线显示“折扣起点批量下的总存货费用”数据参考点（大小为6磅）及其值。

①在A29:B32中添加垂直线数据：A30=B11→ A31=A30→ A32=A31→ B30=100000→ B31=B13→ B32=200000
②选中A30:B32→ 复制→ 选中图形工作表→ 编辑→ 选择性粘贴→ 点击垂直线
→右击垂直线与曲线的交叉点→数据点格式→数据标志→勾选“Y值”

第5题 采用例1的数据，假定供货单位提供给公司的折扣优惠政策条件为：每次订货量大于或等于1500件(折扣阈限值)，则每件的采购单价在原价4.5元/件的基础上可以享受5%的优惠折扣。请依据题给模型作选择，按经济订货量（未采用折扣）订货，还是按折扣阈限值订货？要求：
1、以折扣阈限值作为实际订货量，求出其年订货成本、年储存成本、年采购成本和年总成本(为前三项成本之和)。
2、用基本的经济订货量模型计算：
（1）经济订货量及其下的年订货成本、年储存成本、年采购成本和年总成本。
（2）在本工作表的单元格区域E2:F12的运算表中，计算不同订货量下的年总成本。
3、使用IF()函数计算“实际采购单价”的值(要考虑订货量与单价折扣的关系)。
4、基于运算表绘制XY散点图，在该图形上分别添加两个微调框和两个文本框用以控制当折扣阈限值从1000按增量50变化到3500、单价折扣率从5%按增量1%变化到20%。修改C2和C3单元格的内容，使之随控件同步变化。
5、在图形中，添加经过经济订货量的垂直参考线与参考点（显示值）和年总成本极小值的水平参考线与参考点（显示值）。
6、以上图形及参考线可随控件而变化。
7、在图形上添加一个文本框，显示是采用经济订货量、还是接受折扣优惠。
8、生成的图形如下所示，并按所给图示对图形进行相应的格式化。
注：生成的图线在折扣阈限值处应有一个陡降的效果。

单价折扣率
5.0%

订货量
年总成本

折扣阈限值
1500

300

年需求量(D)
15000

400

一次订货的订货成本(A)
500.00

500

单位年储存成本(PK)
30.00

600

名义采购单价(P)
4.50

700

实际采购单价

800

900

实际订货量(Q)
1500

1500

年订货成本

1500

年储存成本

4000

年采购成本

年总成本

经济订货量(EOQ)

EOQ下的年订货成本

EOQ下的年储存成本

EOQ下的年采购成本

EOQ下的年总成本

操作步骤：
1、 按公式计算对以下单元格进行计算：

1、以折扣阈限值作为实际订货量，求出其年订货成本、年储存成本、年采购成本和年总成本(为前三项成本之和)。
①实际采购单价：C8= C7*(1-C2)
②年订货成本：C11=C4/C10*C5
③年储存成本：C12=C6*C10/2
④年采购成本：C13=C4*C8
⑤年总成本： C14=C11+C12+C13

2、用基本的经济订货量模型计算：
（1）经济订货量及其下的年订货成本、年储存成本、年采购成本和年总成本。
①经济订货量（EOQ）：C16=SQRT(2*C4*C5/C6)
②EOQ下的年订货成本：C17=C4/C16*C5
③EOQ下的年储存成本：C18=C16/2*C6
④EOQ下的年采购成本：C19=C4*C8
⑤EOQ下的年总成本： C20=C17+C18+C19
（2）在本工作表的单元格区域E2:F12的运算表中，计算不同订货量下的年总成本。
3、使用IF()函数计算“实际采购单价”的值(要考虑订货量与单价折扣的关系)。
①F3=$C$4*IF(E3>=1500,$C$8,$C$7)+$C$4*$C$5/E3+E3*$C$6/2
②将F3中的公式复制到F4:F12中

4、基于运算表绘制XY散点图，在该图形上分别添加两个微调框和两个文本框用以控制当折扣阈限值从1000按增量50变化到3500、单价折扣率从5%按增量1%变化到20%。修改C2和C3单元格的内容，使之随控件同步变化。

（1）绘制无数据点平滑散点图
①选中E3:F12→图表向导→XY散点图→无数据点平滑散点图
②右击Y轴→坐标轴格式→刻度→最小值：80000→最大值：120000→主要刻度单位：10000
③右击X轴→坐标轴格式→刻度→最小值：300→最大值：3800→主要刻度单位：500
④右击曲线→数据系列格式→图案→不勾选平滑线
（2）添加微调框、文本框
①B22 ：=B3&"="&C3→ B23： =B2&"="&C2*100&"%"
→B25： =IF(C20=F23,"不接受优惠价格，采用经济订货量","采用价格优惠政策")

②添加折扣卡阀限值微调框：最大值：3500→最小值：1000→步长：50→链接单元格：C3
③添加折扣卡阀限值文本框：在编辑栏输入：=B22
④添加折扣率微调框：最大值：20→最小值：5→步长：1→链接单元格：C23→ C2 =C23/100
⑤添加折扣率文本框：在编辑栏输入：=B23
⑥添加结论文本框：在编辑栏输入：=B25

5、在图形中，添加经过经济订货量的垂直参考线与参考点（显示值）和年总成本极小值的水平参考线与参考点（显示值）。
①添加垂直参考线数据

E18=C16→ E19=C16→ E29=C16→ F18=80000→ F19=C20→ F20=120000
②添加垂直参考线
选中E18:F20→ 复制→ 选中图表→ 编辑→ 选择性黏贴→ 添加单元格为：新建系列
→数值（Y）轴在：列→ 勾选首列为分类X值
③添加水平参考线数据

E23=300→ E24:=IF(C20=F24,C16,INDEX(E3:E12,MATCH(F24,F3:F12,0)))→ E25=3800→ F23: =MIN($F$3:$F$12,$C$20) → F24=F23→ F25=F23
④添加水平参考线
选中E23:F25→ 复制→ 选中图表→ 编辑→ 选择性黏贴→ 添加单元格为：新建系列
→数值（Y）轴在：列→ 勾选首列为分类X值
⑤右击垂直线、水平线与曲线的交叉点→ 数据系列格式→ 数据标志→ 勾选Y值

6、以上图形及参考线可随控件而变化。

7、在图形上添加一个文本框，显示是采用经济订货量、还是接受折扣优惠。
（在第4小题处已完成）

8、生成的图形如下所示，并按所给图示对图形进行相应的格式化。

注：生成的图线在折扣阈限值处应有一个陡降的效果。

20