經(jīng)濟(jì)管理軟件上機(jī)實(shí)驗(yàn)指導(dǎo)手冊(cè)

經(jīng)濟(jì)管理軟件上機(jī)實(shí)驗(yàn)

指導(dǎo)手冊(cè)

參考教材：《數(shù)據(jù)、模型與決策-運(yùn)用電子表格建模與案例研究》

FrederickS.HillierandMark.S.Hillier

浙江理工大學(xué)經(jīng)濟(jì)管理學(xué)院

周曉林編

2011年12月

實(shí)驗(yàn)一：EXCEL電子表格應(yīng)用初步

一、實(shí)驗(yàn)?zāi)康?/p>

1、了解EXCEL電子表格的結(jié)構(gòu)；

2、掌握EXCEL電子表格中單元格的表示方法；

3、掌握EXCEL電子表格中對(duì)單元格的各種操作；

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

二、實(shí)驗(yàn)內(nèi)容

對(duì)某種產(chǎn)品進(jìn)行盈虧平衡分析。

例題：特殊產(chǎn)品公司生產(chǎn)在商店銷售的昂貴而不常見的禮品，禮品是為那些已經(jīng)

幾乎什么都有的富人生產(chǎn)的。公司研發(fā)部最新的產(chǎn)品計(jì)劃是有限版落地?cái)[鐘（〃疝儂/

editiongrandfatherclock）o公司管理部門需要決定是否生產(chǎn)這個(gè)新產(chǎn)品，生產(chǎn)量為

多少時(shí)才能盈利？

?Data:

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

-Thevariablecost（變動(dòng)成本）is$400perclockproduced.

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

-Asalesforecastwillbeobtained

決策：如果要生產(chǎn)，生產(chǎn)多少落地?cái)[鐘？

三、實(shí)驗(yàn)步驟

1、首先在草稿紙上對(duì)這個(gè)問題進(jìn)行比較細(xì)致的分析

首先引入變量Q表示生產(chǎn)的落地?cái)[鐘的數(shù)量，即

Q二落地?cái)[鐘的生產(chǎn)數(shù)量

決策的目標(biāo)是使公司從該產(chǎn)品中所獲利潤(rùn)最大：

利潤(rùn)=總收入-總成本

總收入=900Q

總成本二固定成本十變動(dòng)成本

固定成本=50000當(dāng)Q>0

變動(dòng)成本=400Q

因此，

總成本=「0如果Q=0

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

利潤(rùn)=總收入-總成本=9OOQ-5OOOO-4OOQ=-5OOOO+5OOQ如果Q>0

盈虧平衡點(diǎn)(利潤(rùn)=0)=固定成本/(單位產(chǎn)品銷售收入一單位產(chǎn)品變動(dòng)成本)

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

完整的數(shù)學(xué)模型：

Max利潤(rùn)如果Q=0

V

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

其中!0<=Q<=S

其中：S是預(yù)測(cè)所能售出的落地?cái)[鐘數(shù)量

50000

盈虧平衡點(diǎn)二

900-40()

如果SW100,則Q=0

如果s>100,則Q=s

2、在數(shù)據(jù)單元格輸入：?jiǎn)挝皇杖?900,固定成本$50000,變動(dòng)成本$400,銷售量預(yù)

測(cè)值300o

3、在輸出單元格輸入公式。在輸入公式之前，首先對(duì)有關(guān)單元格定義名稱,

如單位產(chǎn)品收入，產(chǎn)量，固定成本，變動(dòng)成本等。

在excel中的操作:

MicrosoftExcel-Break-evenanalysis,xls

8］文件電)編輯視圖9插入復(fù))［格式(Q)工具①數(shù)據(jù)@)窗口也)幫助?

cJAl行⑥?$2，包外出，

列

:宋體▼12?3噂%，too點(diǎn)事學(xué)

；藝片B工作表位)

圖表地)...

B12

函數(shù)9...

BF

名稱?定義CD).

iSpecialProdiysis

圖片9粘貼9.

2

3幢超鏈接復(fù))…Ctrl+K指定?...

4y應(yīng)用結(jié)果

5單位產(chǎn)品銷售收入900標(biāo)簽?…270001

6固定成本50000回ztj因令50001

excel2010中定義名稱:

回

工因-MicrosoftExcel

端

4警之二策六i§IE

想融二次::會(huì)黨謠落旗"m儂前F鼓與鼻公㈤具贄：然?曲物吼咒片久事女亮力卷七2’或虹1tBll

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

可以通過Ctrl+?來切換數(shù)據(jù)和公式界面:

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

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

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

EF_

結(jié)果

總收入二單位產(chǎn)品銷售收入*MIN（產(chǎn)量,預(yù)測(cè)需求量）

總固定成本二IF（產(chǎn)量＞0,固定成本,0）

總變動(dòng)成本二變動(dòng)成本*產(chǎn)量

利潤(rùn)二總收入-總固定成本-總變動(dòng)成本

t盈虧平衡點(diǎn)=C6/(C5-C7)

i^MicrosoftExcel-Break-evenanalysis.xls

巴］文件但）編輯也）視圖9插入（X）格式（Q）工具①數(shù)據(jù)@）窗口也）幫助（H）AdobePDF3）

」占00◎3叢與￡11可，寓E▼駕攵I1。。*

宋體B

:齒

G16

ABCDEF

1Co.Break-EvenAnalysis

2

3

4數(shù)據(jù)結(jié)果

5單位產(chǎn)品銷售收入900總收入270000

6固定成本50000總固定成本50000

7變動(dòng)成本400總變動(dòng)成本120000

8預(yù)測(cè)需求量300利澗100000

9

10300盈虧平衡點(diǎn)100

11—

YC

4、可通過改變藍(lán)色區(qū)域內(nèi)的數(shù)據(jù)進(jìn)行靈敏度分析及決策分析。

實(shí)驗(yàn)二：應(yīng)用EXCEL電子表格求解線性規(guī)劃

一、實(shí)驗(yàn)?zāi)康?/p>

1、了解EXCEL規(guī)劃求解宏模塊的功能；

2、掌握EXCEL規(guī)劃求解宏模塊的加載；

3、掌握在EXCEL電子表格中建立線性規(guī)劃模型；

4、掌握用EXCEL規(guī)劃求解宏模塊求解線性規(guī)劃。

二、實(shí)驗(yàn)內(nèi)容

EXCEL規(guī)劃求解確定偉恩德玻璃制品公司產(chǎn)品組合問題

偉恩德玻璃制品公司生產(chǎn)高質(zhì)量的玻璃制品，包括工藝精湛的窗和玻璃門,公司

有三個(gè)工廠：

工廠1：生產(chǎn)鋁框和五金件

工廠2：生產(chǎn)木框

工廠3：生產(chǎn)玻璃和組裝窗與門

公司打算生產(chǎn)的新產(chǎn)叢

8英尺玻璃門

4英尺X6英尺雙層窗

現(xiàn)在管理部門要考慮卜.列問題：

決策：如果生產(chǎn)，兩個(gè)產(chǎn)品的生產(chǎn)組合如何？-每周分別生產(chǎn)多少數(shù)量?

基本生產(chǎn)信息如下表：

單位產(chǎn)品的生產(chǎn)時(shí)間

工廠每周可得時(shí)間

門

11小時(shí)04小時(shí)

■702小時(shí)12小時(shí)

33小時(shí)2小時(shí)18小時(shí)

單位利潤(rùn)（美元）300500

maxZ=3OOX,+5OOA\

再<4

理論模型為:

3天I2X2<18

xx,x2>0

運(yùn)用電子表格建立數(shù)學(xué)模型（線性規(guī)劃模型）的過程中有三個(gè)問題需要得到回答：

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

2.在作出這些決策上有哪些約束條件（constrains）?

3.這些決策的全部績(jī)效測(cè)度（measureofperfoiynance）是什么？

三、實(shí)驗(yàn)步驟

1、選擇決策變量單元格C12：D12（稱為可變單元格，changingcell）,決策變量的初

始值一般賦0,并用較醒目的顏色（黃色）表示。

2、確定目標(biāo)單元格（對(duì)應(yīng)目標(biāo)函數(shù)，largelceH）G24,用函數(shù)公式表示，并用較醒目

的顏色（桔黃色）表示。

11

12

13

14

-門窗

15

單位利淮

16300500

單

17乳

n品的生產(chǎn)時(shí)間己使用時(shí)間每周可得時(shí)間

廠

18工

1<=

廠004

19工21

-

工

廠3020<=12

20320<=18

21

22

23

24窗總利潤(rùn)

251rl

產(chǎn)呈I000

3、用公式輸入每一個(gè)約束條件左邊項(xiàng)，即確定輸出單元格(outputcell),E18：E20。

3

n_I

I靴神300500

)

瓢產(chǎn)助生畫同Elm

1in5o酬頰明加嶇儂?)

)工「22硼PRODUCT姻叫您幽12

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

?

n窗

C酬順施都

注：sumproduct函數(shù)在規(guī)劃求解中很常用，含義：

sumproduct(C4:D4,C12:D12)指把C4:D4變化范圍內(nèi)的每個(gè)值與

C12:D12變化范圍內(nèi)對(duì)應(yīng)的每個(gè)值相乘，然后將各個(gè)積相加。該函數(shù)中

的參數(shù)要求同是行或同是列，并且單元格數(shù)一樣。

如果要求行和列對(duì)應(yīng)單元格乘積之和，可用MMULT函數(shù)。

MMULT(array1,array2)是要進(jìn)行矩陣乘法運(yùn)算的兩個(gè)數(shù)組。

4、ExcelSolver的安裝。Excel工具菜單中選擇加載宏

加我宏0?

當(dāng)前加戴宏⑥:

：

rODBCJDI確定I

SolverTable3

廠雁營(yíng)班廠取消

廠查同向?qū)?/p>

分析工印I:瀏覽⑤…

r分析數(shù)亮序-VBA函數(shù)

r更珞加茲宏植技

P規(guī)劃求解

r模板工耳色

SolverTable

Excel2010的操作:

文件-選項(xiàng)-加載項(xiàng)-轉(zhuǎn)到，即可出現(xiàn)加載界面。

國(guó)H

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

通?A用布息俎3畝月睡

有關(guān)casesanalysis(new)的信息

以另劭

G:\教字物?金J經(jīng)濟(jì)富建笈悍實(shí)法(用八casesanalysis(new)jdS

港打亓

ci叛

兼容模式

信息與舊技本的Office一記便用時(shí)，某薊功能祓禁月,以防止出現(xiàn)問鼠，二

酗烤經(jīng)月逅功能，甌燙的衣扃敢

病近平角文件

Ife-

揩

大小782KB

打印權(quán)限錠邀做

空I人都朗無(wú)復(fù)航更讓工作的眄部分,市B起際3

保存并發(fā)送

睜工審海I溫第！

幫助

鉉莪

上次航靖憫冬16:29

準(zhǔn)備共享

日逼日包堂時(shí)尚2011-12-300:16

1唉享此文件前.武三§其包含以下內(nèi)容

文氈圄三作者笆三名*二言敏拿式房上戒幽間從不

建?Ii鑄

Excel選項(xiàng)國(guó)區(qū)

匾近曾和雷理MhosoftOffke卻蓑叮.

公式

g油抵俊

總存

吾?

??

自定義防*S

俁速石同工ME

'tcftK

艇3

tdKff：ChineseCorwersio<iAddir

35巧■:MicrosohCorporation

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

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

說嗎COMaddrthatconvertbetweenTraditionalCNreseandSimplifiedChireie.

曾?！辏唬篍xcel￡C/胃v|彳到?“

|2||K；0

5、調(diào)用規(guī)劃求解，確定可變單元格和目標(biāo)單元格

Exccl2010調(diào)用規(guī)劃求解宏：數(shù)據(jù)-規(guī)劃求解

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

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

臺(tái)Access綱《芻拈臺(tái)期糕五有彗縊爵寸生

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

,?*麗A,5顆1窈故M，，，

踴筵城履領(lǐng)豌0頒

6、增加約束條件

添加約束區(qū)|

單元格引用位置_約束值C）：

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

[確定]|取消]|添加||幫助但）

7、求解對(duì)話框

規(guī)劃求解選項(xiàng)區(qū)

最長(zhǎng)運(yùn)算時(shí)間（X）:[確定]

迭代次數(shù)Q）：I取消]

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

允許誤差QE）：保存模型G）..]

收斂度9：I幫助QD一|

0米用線性模型皿）□自動(dòng)按比例縮放電）

0假定非負(fù)□顯示迭代結(jié)果CR）

估計(jì)導(dǎo)數(shù)搜索

@正切函數(shù)（A）?向前差分化）?牛頓法頷

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

9、如果該問題的相關(guān)參數(shù)發(fā)生了變化，或要進(jìn)行靈敏度分析（what-ifanalysis）,則

重新求解即可。

補(bǔ)充：excel2010加載宏的操作:

1.文件-選項(xiàng)

有關(guān)casesanalysis的信息

CADocunw?syufyrtoh

因

大小

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

上EWW

20111230015

冷修興9XOTKWW

在共享工交忤..Hi主，《包金以下內(nèi)合

2、選項(xiàng)-加載項(xiàng)

3、加載項(xiàng)-轉(zhuǎn)到

4、出現(xiàn)如下界面:

BBe信號(hào)畫%備角

ZU?e，:“essayskxk?MkrowftExcel

開1.入加布.公式ns陽(yáng)！開發(fā)工n

?國(guó)與?雪?￡

^I8a?c寸囪的E向孝闞龔設(shè):

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

用于優(yōu)化案緣?woo工n

22

23健身房

措磔邀.最殖叵國(guó)』("C”)罩小野間?_

?D[㈣口ffl|100%-

5數(shù)據(jù)■規(guī)劃求解

cawsan??y$i$.xl5-MkKHOftExcel

，士三—二獷罵。-U二二二

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

aAccet*SRU口如irtBft-廣皿*.分列?*maxtfw**用屆臺(tái)分如s

?M氏?

E24a

ZZ_AB-CD-

iRecreationalfacilitiesproblem

原始信息,理論模型,

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

海

泊B

場(chǎng)

網(wǎng)’24>Xj*7xa?17M,?63x《<必

場(chǎng)

運(yùn)

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

虎

他st

X]?X,M1

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

16游泳池網(wǎng)域場(chǎng)運(yùn)動(dòng)場(chǎng)已使用資?？傻觅猎?/p>

1?24.5717.5$30<=84

180.82.81.20<=4.8

191000<=1

20

2130090400150

22

23游泳池網(wǎng)里場(chǎng)運(yùn)骷場(chǎng)注身應(yīng)

24

實(shí)驗(yàn)三：用EXCEL電子表格求解整數(shù)規(guī)劃

一、實(shí)驗(yàn)?zāi)康?/p>

1、了解整數(shù)規(guī)劃的建模；

2、掌握在EXCEL電子表格中建立整數(shù)規(guī)劃模型；

3、掌握用EXCEL規(guī)劃求解宏模塊求解整數(shù)規(guī)劃。

二、實(shí)驗(yàn)內(nèi)容

娛樂設(shè)施建設(shè)

?個(gè)社區(qū)的業(yè)主委員會(huì)要決定在社區(qū)建設(shè)哪種娛樂設(shè)施，他們有四種選擇，具體

信息見表。業(yè)主委員會(huì)現(xiàn)有資金84萬(wàn)元，土地4.8萬(wàn)平方米。游泳池和網(wǎng)球場(chǎng)必須

被建在同一塊土地上，也就是說這兩個(gè)設(shè)施只能建一個(gè)。業(yè)主委員會(huì)想知道建設(shè)怎

樣的娛樂設(shè)施組合能最大化總的預(yù)期日使用量。

決策：如何組合優(yōu)化這些娛樂設(shè)施建設(shè)項(xiàng)目使總的預(yù)期日使用量最大？

娛樂設(shè)施預(yù)期使用（人/天）成本（萬(wàn)元）土地需求（萬(wàn)平方米）

游

泳池30024.51.6

網(wǎng)

球場(chǎng)

9970.8

運(yùn)

動(dòng)場(chǎng)

40017.52.8

健

身房

150631.2

該問題的理論模型:

內(nèi)=建設(shè)游泳池

x2=建設(shè)網(wǎng)球場(chǎng)

＜工3二建設(shè)運(yùn)動(dòng)場(chǎng)

七二建設(shè)健身房

為等于0或1,0表示未建設(shè)J表示建設(shè)

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=?；?

三、實(shí)驗(yàn)步驟

1、仔細(xì)地分析問題，確定決策變量、目標(biāo)函數(shù)、約束條件。

2、選擇決策變量單元格(changingcell),決策變量的初始值一般賦0.并用較醒目

的顏色(黃色)表示。

3、確定目標(biāo)單元格(targetcell),用函數(shù)公式表示，并用較醒目的顏色(桔黃色)表

不O

4、ExcelSolver的安裝。Excel工具菜單中選擇加載宏

5、調(diào)用規(guī)劃求解，確定可變單元格和目標(biāo)單元格

規(guī)劃求解參數(shù)

設(shè)置目標(biāo)單元格也）：總使用￡

等于：?最大值?）「最小值3）值為9［o

可變單元格也）：

區(qū)］:推測(cè)缸］

UC$24:$F$24選項(xiàng),

約束@）：

添加Q）

全部重設(shè)也）

「更改?1

國(guó)助砥二）

冊(cè)賒

6、增加約束條件

理論模型:

（萬(wàn)元）土地南0x4

4.534

7

1.8

7.5

球場(chǎng)

7

）.8力.?1.NU4.?

10001

或者:

7、求解結(jié)果

游泳池網(wǎng)球場(chǎng)運(yùn)動(dòng)場(chǎng)健身房已使用資源可得資源

成本24.5717.56342<=84

土地需求1.60.82.81.24.4<=4.8

互斥約束11001=1

預(yù)期使用30090400150

游泳池網(wǎng)球場(chǎng)運(yùn)動(dòng)場(chǎng)健身房總使用量

設(shè)施選擇I01070()

8、如果該問題的相關(guān)參數(shù)發(fā)生了變化，或要進(jìn)行靈敏度分析(what-ifanalysis),則

重新求解即可。

實(shí)驗(yàn)四：蒙特卡洛模擬

一、實(shí)驗(yàn)?zāi)康?/p>

1、了解蒙特卡洛模擬的過程；

2、掌握隨機(jī)數(shù)發(fā)生器和rand()函數(shù)的應(yīng)用。

二、實(shí)驗(yàn)內(nèi)容

風(fēng)險(xiǎn)投資問題

有一個(gè)風(fēng)險(xiǎn)投資的機(jī)會(huì)，成功和失敗的概率都是0.5。投資1元，如果成功可以得到

1.6元的利潤(rùn)，即資本成為2.6元。如果失敗，則損失1元，即資本成為0。開始的資本為

100萬(wàn)元。投資的次數(shù)和每次投資額不限。為了不至于把錢輸光，投資者采取如下的策

略：每次總是將資本的一半去投資。

問題：這項(xiàng)投資的結(jié)局如何，是一本萬(wàn)利，還是一貧如洗？

三、實(shí)驗(yàn)步驟

1、建立一張Excel表，模擬投資次數(shù)設(shè)定為100次,當(dāng)前資本為100萬(wàn)元。第二次投資

前的資本(B5)等于第一次投資后的資本(E4),……,依次定義每次投資前的資本為

上一次投資后的資本。

DlicrasoftEwcul-BonklK

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

2、對(duì)每一次模擬投資，設(shè)置一個(gè)在［0,1］區(qū)間均勻分布的隨機(jī)變量。按功能鍵F9,所

有隨機(jī)數(shù)會(huì)重新產(chǎn)生一次。

E3licrosroftExcel-Bookl

期文件a）?a<D視圖①獨(dú)入9格式1S）工具9毅東Q）?□?）帚的⑨-4X

口修。1后。沙名的電Os，0￡分外公陽(yáng)@皿?⑦.

宋體?>2，B/g土豆w苗等%，溫/津厚一

3、定義投資成功與否，如果相應(yīng)的隨機(jī)變量小于0.5,投資失敗（1）4:0）,否則投資

成功（D4=l）C由干隨機(jī)變量在區(qū)間［0,1）中是均勻分布的.因此投資成功和失敗的次

數(shù)各占一半。

E?liciosoftExcel-Dookl?；貒?guó)

③文件a）偏稅口機(jī)密9鉆入9格式地）工具9制憲9團(tuán)口①）幫助⑥也閭

口晉/聆電。6，，工6到打”也閑?⑦.

?12.|B/U|——?國(guó)@%，.寶I*集I0▼A?,

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

一

ABCDEFG

1風(fēng)險(xiǎn)投資的模擬實(shí)驗(yàn)

2

3實(shí)驗(yàn)次數(shù)投資前的資本隨機(jī)數(shù)投資成功/失敗投資后的資本

411000.6015031

5200.2610110

6300.2340950

7400.1895870

8500.1411740

9600.6816161

10700.3656720

11R004767760

4、計(jì)算投資后的資本，按F9鍵，刷新隨機(jī)數(shù)，進(jìn)行新的100次模擬投資實(shí)驗(yàn)。

回MicrosoftExc?l-Bndcmninilysis.xls

多文件K>?ta（iT視圖而一插入H）格式@）xaET數(shù)據(jù)向？⑥口上超助理）人/口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實(shí)驗(yàn)次數(shù)投資前的資本趨機(jī)數(shù)投資成功/失敗,投資后的資本,

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次模擬投資實(shí)驗(yàn)中資本變化。按F9鍵，刷新隨機(jī)數(shù)，可以得到新的資

本變化圖形（插入一一期表一折線圖）

F3_