經濟管理軟件上機實驗

指導手冊

參考教材：《數據、模型與決策-運用電子表格建模與案例研究》

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.

曾?！辏唬篍xcel￡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游泳池網域場運動場已使用資?？傻觅猎?/p>

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、仔細地分析問題，確定決策變量、目標函數、約束條件。

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,投資失?。?）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_