经济管理软件上机实验指导手册

经济管理软件上机实验

指导手册

参考教材：《数据、模型与决策-运用电子表格建模与案例研究》

FrederickS.HillierandMark.S.Hillier

浙江理工大学经济管理学院

周晓林编

2011年12月

实验一：EXCEL电子表格应用初步

一、实验目的

1、了解EXCEL电子表格的结构；

2、掌握EXCEL电子表格中单元格的表示方法；

3、掌握EXCEL电子表格中对单元格的各种操作；

4、掌握EXCEL电子表格中公式的输入方法。

二、实验内容

对某种产品进行盈亏平衡分析。

例题：特殊产品公司生产在商店销售的昂贵而不常见的礼品，礼品是为那些已经

几乎什么都有的富人生产的。公司研发部最新的产品计划是有限版落地摆钟（〃疝侬/

editiongrandfatherclock）o公司管理部门需要决定是否生产这个新产品，生产量为

多少时才能盈利？

•Data:

-Iftheygoaheadwiththisproduct,fixedcost（固定成本）of$50,000isincurred.

-Thevariablecost（变动成本）is$400perclockproduced.

-Eachclocksoldwouldgenerate$900inrevenue（销售收入）.

-Asalesforecastwillbeobtained

决策：如果要生产，生产多少落地摆钟？

三、实验步骤

1、首先在草稿纸上对这个问题进行比较细致的分析

首先引入变量Q表示生产的落地摆钟的数量，即

Q二落地摆钟的生产数量

决策的目标是使公司从该产品中所获利润最大：

利润=总收入-总成本

总收入=900Q

总成本二固定成本十变动成本

固定成本=50000当Q>0

变动成本=400Q

因此，

总成本=「0如果Q=0

l5()()00+40()Q如果Q>()

利润=总收入-总成本=9OOQ-5OOOO-4OOQ=-5OOOO+5OOQ如果Q>0

盈亏平衡点(利润=0)=固定成本/(单位产品销售收入一单位产品变动成本)

一般情况下，Q<=需求量

完整的数学模型：

Max利润如果Q=0

V

―500()0+50()Q如果Q>()

其中!0<=Q<=S

其中：S是预测所能售出的落地摆钟数量

50000

盈亏平衡点二

900-40()

如果SW100,则Q=0

如果s>100,则Q=s

2、在数据单元格输入：单位收入$900,固定成本$50000,变动成本$400,销售量预

测值300o

3、在输出单元格输入公式。在输入公式之前，首先对有关单元格定义名称,

如单位产品收入，产量，固定成本，变动成本等。

在excel中的操作:

MicrosoftExcel-Break-evenanalysis,xls

8］文件电)编辑视图9插入复)［格式(Q)工具①数据@)窗口也)帮助®

cJAl行⑥•$2，包外出，

列

:宋体▼12©3噂%，too点事学

；艺片B工作表位)

图表地)...

B12

函数9...

BF

名称®定义CD).

iSpecialProdiysis

图片9粘贴9.

2

3幢超链接复)…Ctrl+K指定©...

4y应用结果

5单位产品销售收入900标签©…270001

6固定成本50000回ztj因令50001

excel2010中定义名称:

回

工因-MicrosoftExcel

端

4警之二策六i§IE

想融二次::会党谣落旗"m侬前F鼓与鼻公㈤具贽：然©曲物吼咒片久事女亮力卷七2’或虹1tBll

，移.....................=*®S-，?281瑞趴幺照段外&期迫，电皿=

可以通过Ctrl+〜来切换数据和公式界面:

1±具酉1敛帖囱1_|叱稽即四AdobeFUF也J陡八而季而助|出

3I0▼一宴工▼21攵I由Qio。%

童聂至零％，to?4°?聿阜圣▼的▼

EF_

结果

总收入二单位产品销售收入*MIN（产量,预测需求量）

总固定成本二IF（产量＞0,固定成本,0）

总变动成本二变动成本*产量

利润二总收入-总固定成本-总变动成本

t盈亏平衡点=C6/(C5-C7)

i^MicrosoftExcel-Break-evenanalysis.xls

巴］文件但）编辑也）视图9插入（X）格式（Q）工具①数据@）窗口也）帮助（H）AdobePDF3）

」占00◎3丛与£11可，寓E▼驾攵I1。。*

宋体B

:齿

G16

ABCDEF

1Co.Break-EvenAnalysis

2

3

4数据结果

5单位产品销售收入900总收入270000

6固定成本50000总固定成本50000

7变动成本400总变动成本120000

8预测需求量300利涧100000

9

10300盈亏平衡点100

11—

YC

4、可通过改变蓝色区域内的数据进行灵敏度分析及决策分析。

实验二：应用EXCEL电子表格求解线性规划

一、实验目的

1、了解EXCEL规划求解宏模块的功能；

2、掌握EXCEL规划求解宏模块的加载；

3、掌握在EXCEL电子表格中建立线性规划模型；

4、掌握用EXCEL规划求解宏模块求解线性规划。

二、实验内容

EXCEL规划求解确定伟恩德玻璃制品公司产品组合问题

伟恩德玻璃制品公司生产高质量的玻璃制品，包括工艺精湛的窗和玻璃门,公司

有三个工厂：

工厂1：生产铝框和五金件

工厂2：生产木框

工厂3：生产玻璃和组装窗与门

公司打算生产的新产丛

8英尺玻璃门

4英尺X6英尺双层窗

现在管理部门要考虑卜.列问题：

决策：如果生产，两个产品的生产组合如何？-每周分别生产多少数量?

基本生产信息如下表：

单位产品的生产时间

工厂每周可得时间

门

11小时04小时

■702小时12小时

33小时2小时18小时

单位利润（美元）300500

maxZ=3OOX,+5OOA\

再<4

理论模型为:

3天I2X2<18

xx,x2>0

运用电子表格建立数学模型（线性规划模型）的过程中有三个问题需要得到回答：

1.要作出的决策（decisions）是什么？

2.在作出这些决策上有哪些约束条件（constrains）?

3.这些决策的全部绩效测度（measureofperfoiynance）是什么？

三、实验步骤

1、选择决策变量单元格C12：D12（称为可变单元格，changingcell）,决策变量的初

始值一般赋0,并用较醒目的颜色（黄色）表示。

2、确定目标单元格（对应目标函数，largelceH）G24,用函数公式表示，并用较醒目

的颜色（桔黄色）表示。

11

12

13

14

-门窗

15

单位利淮

16300500

单

17乳

n品的生产时间己使用时间每周可得时间

厂

18工

1<=

厂004

19工21

-

工

厂3020<=12

20320<=18

21

22

23

24窗总利润

251rl

产呈I000

3、用公式输入每一个约束条件左边项，即确定输出单元格(outputcell),E18：E20。

3

n_I

I靴神300500

)

瓢产助生画同Elm

1in5o酬颊明加岖侬⑫)

)工「22硼PRODUCT姻叫您幽12

)工「32=SUTODin(C20:D20JC21：D24)18

?

n窗

C酬顺施都

注：sumproduct函数在规划求解中很常用，含义：

sumproduct(C4:D4,C12:D12)指把C4:D4变化范围内的每个值与

C12:D12变化范围内对应的每个值相乘，然后将各个积相加。该函数中

的参数要求同是行或同是列，并且单元格数一样。

如果要求行和列对应单元格乘积之和，可用MMULT函数。

MMULT(array1,array2)是要进行矩阵乘法运算的两个数组。

4、ExcelSolver的安装。Excel工具菜单中选择加载宏

加我宏0®

当前加戴宏⑥:

：

rODBCJDI确定I

SolverTable3

厂雁营班厂取消

厂查同向导

分析工印I:浏览⑤…

r分析数亮序-VBA函数

r更珞加兹宏植技

P规划求解

r模板工耳色

SolverTable

Excel2010的操作:

文件-选项-加载项-转到，即可出现加载界面。

国H

casesanaly$is；new）兼容模式•MicrosoftExcel

通«A用布息俎3亩月睡

有关casesanalysis(new)的信息

以另劭

G:\教字物•金J经济富建笈悍实法(用八casesanalysis(new)jdS

港打亓

ci叛

兼容模式

信息与旧技本的Office一记便用时，某蓟功能祓禁月,以防止出现问鼠，二

酗烤经月逅功能，瓯烫的衣扃敢

病近平角文件

Ife-

揩

大小782KB

打印权限锭邀做

空I人都朗无复航更让工作的眄部分,市B起际3

保存并发送

睁工审海I温第！

帮助

铉莪

上次航靖悯冬16:29

准备共享

日逼日包堂时尚2011-12-300:16

1唉享此文件前.武三§其包含以下内容

文毡圄三作者笆三名*二言敏拿式房上戒幽间从不

建®Ii铸

Excel选项国区

匾近曾和雷理MhosoftOffke却蓑叮.

公式

g油抵俊

总存

吾・

««

自定义防*S

俣速石同工ME

'tcftK

艇3

tdKff：ChineseCorwersio<iAddir

35巧■:MicrosohCorporation

英春也：受■等冏七箕咨0a堂

QBC:\Pf09nnFilts\MkrosoftOffict\Offictl4\AOOIN$\TCSCCONV.O<.l

说吗COMaddrthatconvertbetweenTraditionalCNreseandSimplifiedChireie.

曾恚£；：Excel£C/胃v|彳到®“

|2||K；0

5、调用规划求解，确定可变单元格和目标单元格

Exccl2010调用规划求解宏：数据-规划求解

工Id。.-casesana^sinew）整期］■UcosoftEae

Bill1I■建丫二加昌二喝用即M温

台Access纲《刍拈台期糕五有彗缢爵寸生

Z［盼总/分列麟照虻喧勃并季源合孑上

,・*丽A,5颗1窈故M，，，

踊筵城履领豌0颁

6、增加约束条件

添加约束区|

单元格引用位置_约束值C）：

$E$18:$E$20[^S]]<=3|$G$18：$G$20|自

[确定]|取消]|添加||帮助但）

7、求解对话框

规划求解选项区

最长运算时间（X）:[确定]

迭代次数Q）：I取消]

精度9：偏入模型©../

允许误差QE）：保存模型G）..]

收敛度9：I帮助QD一|

0米用线性模型皿）□自动按比例缩放电）

0假定非负□显示迭代结果CR）

估计导数搜索

@正切函数（A）®向前差分化）©牛顿法颔

O二次方程@）O中心差分。O共痂法Q）

9、如果该问题的相关参数发生了变化，或要进行灵敏度分析（what-ifanalysis）,则

重新求解即可。

补充：excel2010加载宏的操作:

1.文件-选项

有关casesanalysis的信息

CADocunw«syufyrtoh

因

大小

的5A开.妙典瞋Mt工牝BftWh

上EWW

20111230015

冷修兴9XOTKWW

在共享工交忤..Hi主，《包金以下内合

2、选项-加载项

3、加载项-转到

4、出现如下界面:

BBe信号画%备角

ZU♦e，:“essayskxk•MkrowftExcel

开1.入加布.公式ns阳！开发工n

®国与〜雪・£

^I8a«c寸囱的E向孝阚龚设:

MXCOMMWC"<ht«3：9，所W5

towsXML

-C2

Recreationalfacilitiesproblem

H_2J

原帖信息，理诧模型,

MaxZ=30Qr1+90xa♦400x,4

14”]+%+175勺+63乙

l65+08X3♦2够+12X4

『1+X,41

/].x.x.x=09(1

IaK(DI2s4

12

13IaabtgI

14

15

16・游泳花馋身厉已住用资杓可得资金

1724.56342

181・6L4

19

20

21於期使用300

用于优化案缘•woo工n

22

23健身房

措磔邀.最殖叵国』("C”)罩小野间・_

«D[㈣口ffl|100%-

5数据■规划求解

cawsan««y$i$.xl5-MkKHOftExcel

，士三—二犷骂。-U二二二

面ui注言必向«r，豳Y**

aAccet*SRU口如irtBft-广皿*.分列•*maxtfw**用届台分如s

・M氏•

E24a

ZZ_AB-CD-

iRecreationalfacilitiesproblem

原始信息,理论模型,

MaxZ・%0x+90-400x3+150勺

海

泊B

场

网’24>Xj*7xa♦17M,♦63x《<必

场

运

IM,+08xa+2.8X,+12X4£48

虎

他st

X]♦X,M1

5>.XJ.X).X4-05tl

16游泳池网域场运动场已使用资海可得倭源

1?24.5717.5$30<=84

180.82.81.20<=4.8

191000<=1

20

2130090400150

22

23游泳池网里场运骷场注身应

24

实验三：用EXCEL电子表格求解整数规划

一、实验目的

1、了解整数规划的建模；

2、掌握在EXCEL电子表格中建立整数规划模型；

3、掌握用EXCEL规划求解宏模块求解整数规划。

二、实验内容

娱乐设施建设

•个社区的业主委员会要决定在社区建设哪种娱乐设施，他们有四种选择，具体

信息见表。业主委员会现有资金84万元，土地4.8万平方米。游泳池和网球场必须

被建在同一块土地上，也就是说这两个设施只能建一个。业主委员会想知道建设怎

样的娱乐设施组合能最大化总的预期日使用量。

决策：如何组合优化这些娱乐设施建设项目使总的预期日使用量最大？

娱乐设施预期使用（人/天）成本（万元）土地需求（万平方米）

游

泳池30024.51.6

网

球场

9970.8

运

动场

40017.52.8

健

身房

150631.2

该问题的理论模型:

内=建设游泳池

x2=建设网球场

＜工3二建设运动场

七二建设健身房

为等于0或1,0表示未建设J表示建设

MaxZ-300X1+90x2+400.0+150A4

24.5x+7x,+17.5x,+63x.<84

H6X]+0.8X2+2.8%3+\.2X4<4.8

x1+x2=1

工[,12，13，工4=。或1

三、实验步骤

1、仔细地分析问题，确定决策变量、目标函数、约束条件。

2、选择决策变量单元格(changingcell),决策变量的初始值一般赋0.并用较醒目

的颜色(黄色)表示。

3、确定目标单元格(targetcell),用函数公式表示，并用较醒目的颜色(桔黄色)表

不O

4、ExcelSolver的安装。Excel工具菜单中选择加载宏

5、调用规划求解，确定可变单元格和目标单元格

规划求解参数

设置目标单元格也）：总使用£

等于：®最大值®）「最小值3）值为9［o

可变单元格也）：

区］:推测缸］

UC$24:$F$24选项,

约束@）：

添加Q）

全部重设也）

「更改©1

国助砥二）

册赊

6、增加约束条件

理论模型:

（万元）土地南0x4

4.534

7

1.8

7.5

球场

7

）.8力.«1.NU4.«

10001

或者:

7、求解结果

游泳池网球场运动场健身房已使用资源可得资源

成本24.5717.56342<=84

土地需求1.60.82.81.24.4<=4.8

互斥约束11001=1

预期使用30090400150

游泳池网球场运动场健身房总使用量

设施选择I01070()

8、如果该问题的相关参数发生了变化，或要进行灵敏度分析(what-ifanalysis),则

重新求解即可。

实验四：蒙特卡洛模拟

一、实验目的

1、了解蒙特卡洛模拟的过程；

2、掌握随机数发生器和rand()函数的应用。

二、实验内容

风险投资问题

有一个风险投资的机会，成功和失败的概率都是0.5。投资1元，如果成功可以得到

1.6元的利润，即资本成为2.6元。如果失败，则损失1元，即资本成为0。开始的资本为

100万元。投资的次数和每次投资额不限。为了不至于把钱输光，投资者采取如下的策

略：每次总是将资本的一半去投资。

问题：这项投资的结局如何，是一本万利，还是一贫如洗？

三、实验步骤

1、建立一张Excel表，模拟投资次数设定为100次,当前资本为100万元。第二次投资

前的资本(B5)等于第一次投资后的资本(E4),……,依次定义每次投资前的资本为

上一次投资后的资本。

DlicrasoftEwcul-BonklK

乌)文片0)5的®AXQ)格式Q)XA<LCS02)■口OU，勤如,|g|X|

2、对每一次模拟投资，设置一个在［0,1］区间均匀分布的随机变量。按功能键F9,所

有随机数会重新产生一次。

E3licrosroftExcel-Bookl

期文件a）«a<D视图①独入9格式1S）工具9毅东Q）«□«）帚的⑨-4X

口修。1后。沙名的电Os，0£分外公阳@皿•⑦.

宋体•>2，B/g土豆w苗等%，温/津厚一

3、定义投资成功与否，如果相应的随机变量小于0.5,投资失败（1）4:0）,否则投资

成功（D4=l）C由干随机变量在区间［0,1）中是均匀分布的.因此投资成功和失败的次

数各占一半。

E?liciosoftExcel-Dookl。回国

③文件a）偏税口机密9钻入9格式地）工具9制宪9团口①）帮助⑥也闾

口晋/聆电。6，，工6到打”也闲•⑦.

•12.|B/U|——・国@%，.宝I*集I0▼A•,

)4二］==IF(C4<C,5,0,1)

一

ABCDEFG

1风险投资的模拟实验

2

3实验次数投资前的资本随机数投资成功/失败投资后的资本

411000.6015031

5200.2610110

6300.2340950

7400.1895870

8500.1411740

9600.6816161

10700.3656720

11R004767760

4、计算投资后的资本，按F9键，刷新随机数，进行新的100次模拟投资实验。

回MicrosoftExc«l-Bndcmninilysis.xls

多文件K>«ta（iT视图而一插入H）格式@）xaET数据向？⑥口上超助理）人/口PDFGT-就入需要帮助的i

」序021普9X。g•/需工，21外叨©由一

宋体一2，B/U至妾三里吗%，端以承本巴

E3▼立=IF（D3=0,0.5*B3,0.5*63+2.6*0.5*B3）

1AlB1clDIEIFIJ

2

3实验次数投资前的资本趋机数投资成功/失败,投资后的资本,

411000.744163111801

521800.368603090

63900.442335045

74450.273965022.5

85122.50.438581_____________0L________11.25

611.250.01275705.625

975.6250.44688302.8125

1082.81250.09189101.40625

1191.406250.562212112.53125

12102.531250.6925261L4.55625

13114.556250.906095i|8.20125

14128.201250.26786104.100625

15134.1006250.22724102.0503125

5、用图形表示100次模拟投资实验中资本变化。按F9键，刷新随机数，可以得到新的资

本变化图形（插入一一期表一折线图）

F3_