Excel會(huì)計(jì)與財(cái)務(wù)管理-理論、方案暨模型（第二版） 桂良軍課件第9章 Excel最優(yōu)化問(wèn)題應(yīng)用

第9章Excel最優(yōu)化問(wèn)題應(yīng)用本章教學(xué)要求：了解最優(yōu)化問(wèn)題的數(shù)學(xué)模型和解決方法掌握Excel規(guī)劃求解工具掌握使用Excel規(guī)劃求解工具解決運(yùn)輸問(wèn)題、資金管理問(wèn)題、生產(chǎn)管理和投資組合問(wèn)題主要內(nèi)容：9.1最優(yōu)化問(wèn)題概述9.2最優(yōu)化問(wèn)題求解工具——Excel規(guī)劃求解9.3常見的最優(yōu)化問(wèn)題第9章Excel最優(yōu)化問(wèn)題應(yīng)用9.1最優(yōu)化問(wèn)題概述最優(yōu)化問(wèn)題定義：最優(yōu)化問(wèn)題就是在給定條件下尋找最佳方案的問(wèn)題。即在資源給定時(shí)尋找最好的目標(biāo)，或在目標(biāo)確定下使用最少的資源。最佳——成本最小、收益最大、利潤(rùn)最多、距離最短、時(shí)間最少、空間最小等等。最優(yōu)化問(wèn)題的分類：根據(jù)有無(wú)約束條件

無(wú)約束條件的最優(yōu)化問(wèn)題

有約束條件的最優(yōu)化問(wèn)題根據(jù)決策變量在目標(biāo)函數(shù)與約束條件中出現(xiàn)的形式線性規(guī)劃問(wèn)題非線性規(guī)劃問(wèn)題二次規(guī)劃問(wèn)題根據(jù)決策變量是否要求取整數(shù)整數(shù)規(guī)劃問(wèn)題0-1規(guī)劃問(wèn)題任意規(guī)劃問(wèn)題最優(yōu)化問(wèn)題的數(shù)學(xué)模型……最優(yōu)化問(wèn)題的求解方法公式法用規(guī)劃求解工具求解√

用查表法求解常見的最優(yōu)化問(wèn)題運(yùn)輸問(wèn)題資金管理問(wèn)題生產(chǎn)管理問(wèn)題最優(yōu)投資組合問(wèn)題9.2最優(yōu)決策分析工具——Excel規(guī)劃求解作用：它是優(yōu)化和資源配置工具，用來(lái)解決含有多變量和多限制的問(wèn)題，幫助企管人員使用最好的方法、利用有效的資源、達(dá)到目標(biāo)成本最小。工具的安裝：開發(fā)工具/加載項(xiàng)簡(jiǎn)單舉例：1.線性規(guī)劃問(wèn)題

某工廠生產(chǎn)A、B兩種產(chǎn)品，每個(gè)A產(chǎn)品可賺50元，每個(gè)B產(chǎn)品可賺30元。制造一個(gè)A產(chǎn)品需要2小時(shí)機(jī)工和1小時(shí)手工；制造一個(gè)B產(chǎn)品需要1小時(shí)機(jī)工和1小時(shí)手工。由于機(jī)工和手工小時(shí)均有限，每周機(jī)工不超過(guò)150小時(shí)，每周手工不超過(guò)120小時(shí)，每周制造A產(chǎn)品不超過(guò)60件，試進(jìn)行生產(chǎn)決策。設(shè)生產(chǎn)A產(chǎn)品X1件、B產(chǎn)品X2件，利潤(rùn)為Z規(guī)劃求解選項(xiàng)2.求解方程組保存和裝入規(guī)劃求解模型1.保存模型2.裝入模型目標(biāo)單元格值可變單元格個(gè)數(shù)約束條件1約束條件2………選項(xiàng)對(duì)話框設(shè)置信息9.3常見的最優(yōu)化問(wèn)題1.運(yùn)輸問(wèn)題【例9-3】某公司有3個(gè)工廠，生產(chǎn)的產(chǎn)品需要運(yùn)往4個(gè)倉(cāng)庫(kù)，每個(gè)工廠的供應(yīng)量、每個(gè)倉(cāng)庫(kù)的需求量以及單位運(yùn)輸成本數(shù)據(jù)下表所示。該公司如何安排運(yùn)輸才能使得總運(yùn)費(fèi)最??？目的地（倉(cāng)庫(kù)）單位運(yùn)輸成本（元）倉(cāng)庫(kù)1倉(cāng)庫(kù)2倉(cāng)庫(kù)3倉(cāng)庫(kù)4供應(yīng)量出發(fā)地：工廠A46451365486775工廠B352416690791125工廠C995682388685100需求量806570852.資金管理問(wèn)題【例9-4】現(xiàn)有10000元準(zhǔn)備存入銀行，可以選擇一年期、二年期和三年期存款。三種存款的年利率分別為2.5%、2.7%和2.9%。第3年末和第5年末需要使用現(xiàn)金1000元和2000元，第4年末有5000元的現(xiàn)金收入可以存入銀行。試在Excel中建立模型，計(jì)算每年年初的到期本金、到期利息和年末現(xiàn)金余額；用規(guī)劃求解工具求解每年年初每種存款的最優(yōu)存款額，使第6年末的現(xiàn)金余額最大；把規(guī)劃求解參數(shù)保存在合適的單元格中。3.生產(chǎn)管理問(wèn)題【例9-5】已知某公司生產(chǎn)的產(chǎn)品在不同月份的需求量、單位生產(chǎn)成本與生產(chǎn)能力不同，見下表：

每月的儲(chǔ)存成本等于單位儲(chǔ)存成本與月平均庫(kù)存量（月初庫(kù)存量與月末庫(kù)存量的平均值）的乘積，而每月的單位儲(chǔ)存成本等于當(dāng)月單位生產(chǎn)成本的2.5%。公司要求每月的生產(chǎn)量既不超過(guò)當(dāng)月生產(chǎn)能力又不低于當(dāng)月生產(chǎn)能力的一半，另外，為防備急需，管理人員還要求每月月末庫(kù)存量不少于1500件（安全庫(kù)存量），倉(cāng)庫(kù)容量為6000件，當(dāng)前庫(kù)存量為2750件。假設(shè)每月生產(chǎn)量為1件，試在Excel中建立規(guī)劃求解模型，并且按如下要求操作：計(jì)算每月的月初庫(kù)存量、生產(chǎn)量下限、月末庫(kù)存量、月平均庫(kù)存量、單位儲(chǔ)存成本和總成本；用規(guī)劃求解工具求解每月的最優(yōu)生產(chǎn)量和6個(gè)月總成本的最小值；把規(guī)劃求解參數(shù)保存在單元格中。4.最優(yōu)投資組合問(wèn)題投資組合預(yù)期收益率：投資組合整體風(fēng)險(xiǎn)：最優(yōu)投資組合：4.最優(yōu)投資組合問(wèn)題投資組合風(fēng)險(xiǎn)收益率RpD：風(fēng)險(xiǎn)厭惡度，R：預(yù)期收益率，：預(yù)期方差4.最優(yōu)投資組合問(wèn)題4.最優(yōu)投資組合問(wèn)題【例9-6】現(xiàn)有一筆資金，準(zhǔn)備購(gòu)買A、B和C三個(gè)公司的股票。各公司在過(guò)去12年的收益率見下表：?jiǎn)枺涸诒ＷC收益率不低于12%的前提下，怎樣組合可以使風(fēng)險(xiǎn)最??？在方差不大于0.01的前提下，怎樣組合可以使收益率最大？當(dāng)風(fēng)險(xiǎn)厭惡度分別等于O、0.5和1時(shí)，求解最優(yōu)證券組合，計(jì)算出收益率和方差。1．規(guī)劃求解報(bào)告的生成運(yùn)算結(jié)果報(bào)告敏感