1、會計學1Excel最優化模型解析最優化模型解析主要內容：主要內容：一、最優化問題概述一、最優化問題概述二、線性規劃二、線性規劃三、非線性規劃三、非線性規劃四、常見規劃問題四、常見規劃問題五、多目標規劃問題五、多目標規劃問題六、最優投資組合模型六、最優投資組合模型第1頁/共55頁一、最優化問題概述一、最優化問題概述第2頁/共55頁一、最優化問題概述一、最優化問題概述第3頁/共55頁一、最優化問題概述一、最優化問題概述nxxxfyMax,:210,:211nxxxsSt0,212nxxxs0,21nmxxxs第4頁/共55頁一、最優化問題概述一、最優化問題概述第5頁/共55頁一、最優化問題概述一、

2、最優化問題概述第6頁/共55頁一、最優化問題概述一、最優化問題概述avFbabvbabvPvaFpbvabpvQFpQCRopt4/2/2max2第7頁/共55頁一、最優化問題概述一、最優化問題概述操作簡單，求解最多操作簡單，求解最多200個決個決策變量的規劃問題，可以達到策變量的規劃問題，可以達到很高的精度，對于線性規劃問很高的精度，對于線性規劃問題可以找到全局最優解。當模題可以找到全局最優解。當模型中其他參數發生變化時，規型中其他參數發生變化時，規劃求解工具不能自動計算出新劃求解工具不能自動計算出新的最優解。的最優解。第8頁/共55頁一、最優化問題概述一、最優化問題概述第9頁/共55頁一、

3、最優化問題概述一、最優化問題概述1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 A B C D E F G 2226固定成本5000-2100單位變動成本1010-500單價截距 (a)16020942單價斜率 (b)-0.79302226單價30403352銷售數量136.3504320總成本1863605130銷售收益4089705782利潤2226806276906612最優單價1101006790利潤極大值681011068101206672初始最優單價1001306376步長1014059221505310160454

4、0170361218025261901282200-120壟斷商品利潤隨單價的變化圖形2226.006821.02-10000100020003000400050006000700080001030507090110130150170190單價利潤單價=30元時，利潤=2226元單位變動成本=10元時，最優單價=106.27元第10頁/共55頁二、線性規劃二、線性規劃bxaxaxayMaxnn2211:0:11212111bxaxaxaStnn022222121bxaxaxann02211nnmnmmbxaxaxa第11頁/共55頁二、線性規劃二、線性規劃第12頁/共55頁二、線性規劃二、線性

5、規劃21210200:xxyMax30073:21 xxSt2505421 xx4204921 xx0,21xx第13頁/共55頁二、線性規劃二、線性規劃產品1產品2需要量可提供量工時3710.00300.00用電量459.00250.00原材料9413.00420.00單位利潤200.00210.00產量1.001.00總利潤410.00第14頁/共55頁二、線性規劃二、線性規劃第15頁/共55頁二、線性規劃二、線性規劃0510152025303540455001020304050工時用電量原材料利潤0102030405002550020004000600080001000012000140

6、0016000180002000022000第16頁/共55頁三、非線性規劃三、非線性規劃nxxxfyMax,:210,:211nxxxsSt0,212nxxxs0,21nmxxxs第17頁/共55頁三、非線性規劃三、非線性規劃第18頁/共55頁三、非線性規劃三、非線性規劃0,420492505430073:1000080262250247210000330528803250503000:21212121222211212211xxxxxxxxstxxxxxxxxxxyMax第19頁/共55頁三、非線性規劃三、非線性規劃2 3 4 5 6 7 8 9 10 11 12 13 14 B C D

7、E F G 產品1產品2需要量可提供量 單位成本工時3710.0030010用電量459.0025012原材料9413.0042050產量1.001.00a30003250b-50-80單價2950.003170.00收益2950.003170.00單位變動成本528.00330.00變動成本528.00330.00總固定成本10000.00總利潤-4738.00第20頁/共55頁三、非線性規劃三、非線性規劃第21頁/共55頁三、非線性規劃三、非線性規劃第22頁/共55頁三、非線性規劃三、非線性規劃第23頁/共55頁三、非線性規劃三、非線性規劃總利潤產品1產品2產品1產品21.001.0013

8、.9215.2518293.330.0015.0026.8214.6715951.825.0025.0023.3422.5015660.930.0010.0028.4511.0012297.4初值終值第24頁/共55頁三、非線性規劃三、非線性規劃第25頁/共55頁四、常見規劃問題四、常見規劃問題 某公司有某公司有3 3個工廠，生產的產品運到個工廠，生產的產品運到5 5個倉庫，個倉庫，3 3個工廠的生產能力為個工廠的生產能力為310310，260260和和280280，每個倉庫的需，每個倉庫的需求量為求量為180180、8080、200200、160160和和220220。從工廠運到各倉庫。從工

9、廠運到各倉庫的運費如下表。該公司怎樣安排，所花費的總運費最的運費如下表。該公司怎樣安排，所花費的總運費最小。小。運費倉庫1倉庫2倉庫3倉庫4倉庫5工廠A108654工廠B65436工廠C34559第26頁/共55頁四、常見規劃問題四、常見規劃問題2 3 4 5 6 7 8 9 10 11 12 13 B C D E F G H I 運費倉庫1倉庫2倉庫3倉庫4倉庫5工廠A108654工廠B65436工廠C34559運量倉庫1倉庫2倉庫3倉庫4倉庫5總計提供量工廠A111115310工廠B111115260工廠C111115280總計33333需求量18080200160220總運費83第27頁

10、/共55頁四、常見規劃問題四、常見規劃問題第28頁/共55頁四、常見規劃問題四、常見規劃問題 某移動通訊公司準備在某一城市建立發射塔，該城某移動通訊公司準備在某一城市建立發射塔，該城有有4 4個地區，現有個地區，現有4 4個建塔位置，每個位置對各地區的個建塔位置，每個位置對各地區的覆蓋情況和費用見下表：該公司怎樣選擇建塔位置，覆蓋情況和費用見下表：該公司怎樣選擇建塔位置，既能覆蓋所有地區，又使總費用最小。既能覆蓋所有地區，又使總費用最小。 第29頁/共55頁四、常見規劃問題四、常見規劃問題2 3 4 5 6 7 8 9 B C D E F G 覆蓋位置1位置2位置3位置4覆蓋次數地區A1113

11、地區B13地區C13地區D113費用350400300380選擇1111總費用1430第30頁/共55頁四、常見規劃問題四、常見規劃問題第31頁/共55頁四、常見規劃問題四、常見規劃問題 現有現有1000010000元準備存入銀行，可以選擇一年元準備存入銀行，可以選擇一年期、二年期和三年期存款。三種存款的年利率期、二年期和三年期存款。三種存款的年利率分別為分別為2.5%2.5%、2.7%2.7%和和2.9%2.9%。第。第3 3年初和第年初和第5 5年初年初需要使用現金需要使用現金10001000元和元和20002000元，第元，第4 4年初有年初有50005000元的現金收入可以存入銀行。問

12、如何分配每年元的現金收入可以存入銀行。問如何分配每年的各種存款額才能使第的各種存款額才能使第6 6年末的現金余額最大？年末的現金余額最大？ 第32頁/共55頁四、常見規劃問題四、常見規劃問題2 3 4 5 6 7 8 9 10 11 12 13 14 15 B C D E F G H 年利率到期總利率一年期2.5%2.5%二年期2.7%5.5%三年期2.9%9.1%第1年第2年第3年第4年第5年第6年期初現金10000.007000.005025.003105.039275.629446.21到期本金1000.002000.003000.003000.003000.00到期利息25.0080.

13、03170.59170.59170.59現金需要額1000.00-5000.002000.00一年期存款1000.001000.001000.001000.001000.00二年期存款1000.001000.001000.001000.00三年期存款1000.001000.001000.00期末現金7000.005025.003105.039275.629446.2112616.80第33頁/共55頁四、常見規劃問題四、常見規劃問題第34頁/共55頁四、常見規劃問題四、常見規劃問題 已知某公司生產的產品在不同月份的需求量、單位生產成本與已知某公司生產的產品在不同月份的需求量、單位生產成本與生產

14、能力不同（見下表），每月的儲存成本等于單位儲存成本與月生產能力不同（見下表），每月的儲存成本等于單位儲存成本與月平均庫存量（月初庫存量與月末庫存量的平均值）的乘積，而每月平均庫存量（月初庫存量與月末庫存量的平均值）的乘積，而每月的單位儲存成本等于當月單位生產成本的的單位儲存成本等于當月單位生產成本的1.5%。公司要求每月的生。公司要求每月的生產量既不超過當月生產能力又不低于當月生產能力的一半，另外，產量既不超過當月生產能力又不低于當月生產能力的一半，另外，為防備急需，管理人員還要求每月月末庫存量不少于為防備急需，管理人員還要求每月月末庫存量不少于1500件（安全件（安全庫存量），倉庫容量為庫存

15、量），倉庫容量為6000件，當前庫存量為件，當前庫存量為2750件。試確定一種件。試確定一種生產安排使得既能滿足每個月的需求量同時又使作為生產成本與儲生產安排使得既能滿足每個月的需求量同時又使作為生產成本與儲存成本之和的總成本達到極小。存成本之和的總成本達到極小。 1月2月3月4月5月6月需求量100045006000550035004000生產量上限400035004000450040003500單位生產成本240250265285280260第35頁/共55頁四、常見規劃問題四、常見規劃問題2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 B C D E F

16、G H 倉庫容量6000安全庫存量1500儲存成本系數0.015當前庫存量27501月2月3月4月5月6月需求量100045006000550035004000月初庫存量27501751-2748-8747-14246-17745生產量111111生產量上限400035004000450040003500生產量下限200017502000225020001750月末庫存量1751-2748-8747-14246-17745-21744月平均庫存量2250.5-498.5-5747.5-11496.5-15995.5-19744.5單位生產成本240250265285280260單位儲存成本3.

17、63.753.9754.2754.23.9總成本-208366.08第36頁/共55頁四、常見規劃問題四、常見規劃問題第37頁/共55頁四、常見規劃問題四、常見規劃問題現有現有1 1個個3 3元元1 1次方程組，求該方程組的解次方程組，求該方程組的解。 664012880221830100162520zyxzyxzyx第38頁/共55頁四、常見規劃問題四、常見規劃問題2 3 4 5 6 7 B C D E F G xyz等式左邊 等式右邊方程120251661.00100方程230182270.0080方程38124060.0066自變量1.00001.00001.0000左右差值 55.00

18、00第39頁/共55頁四、常見規劃問題四、常見規劃問題第40頁/共55頁五、多目標規劃問題五、多目標規劃問題第41頁/共55頁五、多目標規劃問題五、多目標規劃問題第42頁/共55頁五、多目標規劃問題五、多目標規劃問題2 3 4 5 6 7 8 B C D E F 產品1產品2需要量提供量工時3710300用電量459250原材料9413420單位利潤1215產量1.001.00總利潤27第43頁/共55頁五、多目標規劃問題五、多目標規劃問題第44頁/共55頁五、多目標規劃問題五、多目標規劃問題第45頁/共55頁六、最優投資組合模型六、最優投資組合模型iRijiWn1iiiRWRn1in1jji

19、ij2W第46頁/共55頁六、最優投資組合模型六、最優投資組合模型n1in1jjiij2WMin:n1iiiRW R:Maxpn1iiiRRW St.n1in1jpjiijWSt2.1n1iiW 1n1iiW 第47頁/共55頁六、最優投資組合模型六、最優投資組合模型22)1 (DDRR2)1 (:DDRRMAX1W .n1iiSt第48頁/共55頁六、最優投資組合模型六、最優投資組合模型第49頁/共55頁六、最優投資組合模型六、最優投資組合模型2 3 4 5 6 7 8 9 10 11 12 13 14 15 C D E F G H I J K L 年份IBCNMCNBSIBCNMCNBS 111.20%8.00%10.90%IBC0.00258-0.000250.00440210.80%9.20%22.00%NMC-0.000250.00276-0.00542311.60%6.60%37.90%NBS0.00440-0.005420.036774-1.60%18.50%-11.80%5-4.10%7.40%12.90%IBCNMCNBS 68.60%13.00%-7.50%平均收益率7.64%13.43%14.93%76.80%22.00%9.30%投資比例0.00%42.51%57.49%811.90%14.00%48.70%912.00%20.50%-1