084605計(jì)算機(jī)財(cái)務(wù)管理實(shí)驗(yàn)指導(dǎo)書(shū)

1、山東工商學(xué)院 上上 機(jī)機(jī) 實(shí)實(shí) 驗(yàn)驗(yàn) 指指 導(dǎo)導(dǎo) 書(shū)書(shū) 課程編號(hào)： 084605 課程名稱：計(jì)算機(jī)財(cái)務(wù)管理 院（部）會(huì)計(jì)學(xué)院 教研室會(huì)計(jì)信息化 日 期2011 年 9 月 一、實(shí)驗(yàn)課時(shí)安排 課時(shí)分配(學(xué)時(shí)) 章 節(jié) 名 稱課堂 授課 實(shí) 驗(yàn) 上 機(jī) 討 論 課 外 備 注 實(shí)驗(yàn)一 復(fù)習(xí) excel 基本操作 4 實(shí)驗(yàn)二 excel 函數(shù)、公式、數(shù)據(jù)管理和分析 4 實(shí)驗(yàn)三 財(cái)務(wù)分析模型的設(shè)計(jì)和建立 4 實(shí)驗(yàn)四 籌資決策模型的設(shè)計(jì)和建立 4 實(shí)驗(yàn)五 項(xiàng)目投資決策模型的設(shè)計(jì)和建立 4 實(shí)驗(yàn)六 excel 最優(yōu)化問(wèn)題模型的建立 4 實(shí)驗(yàn)七 財(cái)務(wù)預(yù)測(cè)模型的設(shè)計(jì)和建立 4 實(shí)驗(yàn)八 財(cái)務(wù)管理系統(tǒng)的建立 4

2、合 計(jì)32 總 課 時(shí)32 二、實(shí)驗(yàn)參考書(shū)目 1劉蘭娟等經(jīng)濟(jì)管理中的計(jì)算機(jī)應(yīng)用excel 數(shù)據(jù)分析、統(tǒng)計(jì)預(yù)測(cè)和決策 模擬北京：清華大學(xué)出版社，2006 2韓良智excel 在財(cái)務(wù)管理中的應(yīng)用北京：清華大學(xué)出版社，2009 3黃操軍excel 在財(cái)務(wù)分析與投資管理中的應(yīng)用北京：中國(guó)水利水電出版 社,2008 4劉捷萍等excel 在財(cái)務(wù)管理中的應(yīng)用北京：高等教育出版社，2008 5恒盛杰資訊excel 會(huì)計(jì)與財(cái)務(wù)管理經(jīng)典 108 例北京：中國(guó)青年出版社， 2007 6excel homeexcel 圖表實(shí)戰(zhàn)技巧精粹北京：人民郵電出版社，2008 目 錄 實(shí)驗(yàn)一 復(fù)習(xí) excel 基本操作.1 一

3、、實(shí)驗(yàn)?zāi)康?1 二、實(shí)驗(yàn)內(nèi)容.1 三、實(shí)驗(yàn)步驟.3 實(shí)驗(yàn)二 excel 函數(shù)、公式、數(shù)據(jù)管理和分析 .6 一、實(shí)驗(yàn)?zāi)康?6 二、實(shí)驗(yàn)內(nèi)容.6 三、實(shí)驗(yàn)步驟.10 實(shí)驗(yàn)三 財(cái)務(wù)分析模型的設(shè)計(jì)和建立.11 一、實(shí)驗(yàn)?zāi)康?11 二、實(shí)驗(yàn)內(nèi)容.11 三、實(shí)驗(yàn)步驟.11 實(shí)驗(yàn)四 籌資決策模型的設(shè)計(jì)和建立.13 一、實(shí)驗(yàn)?zāi)康?13 二、實(shí)驗(yàn)內(nèi)容.13 三、實(shí)驗(yàn)步驟.14 實(shí)驗(yàn)五 項(xiàng)目投資決策模型的設(shè)計(jì)和建立.27 一、實(shí)驗(yàn)?zāi)康?27 二、實(shí)驗(yàn)內(nèi)容.27 三、實(shí)驗(yàn)步驟.28 實(shí)驗(yàn)六 excel 最優(yōu)化問(wèn)題模型的建立 .31 一、實(shí)驗(yàn)?zāi)康?31 二、實(shí)驗(yàn)內(nèi)容.31 三、實(shí)驗(yàn)步驟.32 實(shí)驗(yàn)七 財(cái)務(wù)預(yù)測(cè)模型的設(shè)

4、計(jì)和建立.36 一、實(shí)驗(yàn)?zāi)康?36 二、實(shí)驗(yàn)內(nèi)容.36 三、實(shí)驗(yàn)步驟.37 實(shí)驗(yàn)八 財(cái)務(wù)管理系統(tǒng)的建立.42 一、實(shí)驗(yàn)?zāi)康?42 二、實(shí)驗(yàn)內(nèi)容.42 三、實(shí)驗(yàn)步驟.42 0 實(shí)驗(yàn)一實(shí)驗(yàn)一 復(fù)習(xí)復(fù)習(xí) excelexcel 基基本操作本操作 一、實(shí)驗(yàn)?zāi)康?1掌握 excel 的基本操作。熟練掌握各種啟動(dòng)退出 excel 的有關(guān)操作； 熟悉 excel 的窗口及初始屏幕各部分的功能；掌握 excel 聯(lián)機(jī)幫助功能的使用方 法。 2掌握工作表的建立方法。熟練掌握工作表的建立方法和操作技巧，如建 立一個(gè)新的工作薄、向工作表中輸入各種類型的數(shù)據(jù)、設(shè)置數(shù)據(jù)的有效性、數(shù) 據(jù)的編輯和修改、格式的編排、查找和替換

5、單元格中的數(shù)據(jù)以及各種引用的操 作；保存工作薄、工作薄密碼的設(shè)置。 3掌握工作表的編輯方法。熟練掌握工作表的各種編輯操作，包括移動(dòng)、 復(fù)制、刪除、更名、隱藏等，熟練掌握工作表的管理方法，包括打開(kāi)、查看多 個(gè)工作薄等。 4掌握窗口的切換、隱藏、拆分、凍結(jié)等操作。 5掌握打印操作，包括頁(yè)面設(shè)置、分頁(yè)設(shè)置與調(diào)整、打印預(yù)覽等。 6學(xué)會(huì)用圖表的形式表示數(shù)據(jù)間的復(fù)雜關(guān)系，直觀地分析統(tǒng)計(jì)數(shù)據(jù)。包括 圖標(biāo)的建立步驟、圖表的編輯等。 7了解 excel 的網(wǎng)絡(luò)功能。包括創(chuàng)建超級(jí)鏈接、獲取或發(fā)布數(shù)據(jù)等。 二、實(shí)驗(yàn)內(nèi)容 1在磁盤(pán)上建立一個(gè)文件名為“學(xué)生成績(jī)表”的工作薄，在工作表 sheet1 中創(chuàng)建一個(gè)如表 1-1

6、 所示的工作表。 表 1-1 2011 級(jí)學(xué)生成績(jī)表 姓名性別高數(shù)線代英語(yǔ)物理語(yǔ)文會(huì)計(jì)學(xué)財(cái)務(wù)管理總成績(jī)平均分 張三男92807372809086 梁齊男86767576828367 王文女75617671868481 呂梁男68806980797986 王青女59738069657672 岳韓男71697167746890 戴衛(wèi)女82816585816274 杜平男73787064836592 實(shí)驗(yàn)要求如下。 （1）練習(xí)添加記錄，并用公式和函數(shù)求出每位同學(xué)的總成績(jī)和平均分。 （2）將工作表重新命名為“學(xué)生成績(jī)表” 。 （3）將“2011 級(jí)學(xué)生成績(jī)表”這一標(biāo)題設(shè)置格式為黑體 14 號(hào)字，居中顯

7、 示，顏色為紅色。表格中的文字型數(shù)據(jù)居中顯示。 （4）將“學(xué)生成績(jī)表”調(diào)整到適當(dāng)?shù)牧袑捄托懈摺?（5）將“學(xué)生成績(jī)表”復(fù)制到同一工作簿的另一工作表中，命名為“學(xué)生 1 成績(jī)表 2” 。 （6）在“學(xué)生成績(jī)表”中顯示平均分大于等于 75 且小于 80 的記錄，然后 恢復(fù)原狀。 （7）在“2011 級(jí)學(xué)生成績(jī)表”數(shù)據(jù)區(qū)域使用自動(dòng)套用格式。 （8）在“學(xué)生成績(jī)表 2”數(shù)據(jù)區(qū)的最后添加一行，利用公式計(jì)算出各科成 績(jī)和全班同學(xué)總成績(jī)的平均分。 （9）將“學(xué)生成績(jī)表 2”的標(biāo)題設(shè)為楷體，22 號(hào)字，水平居中顯示，并且 將標(biāo)題單元格所在的行高設(shè)為 24。 （10）在）在“學(xué)生成績(jī)表學(xué)生成績(jī)表2”中只顯示姓王

8、的成績(jī)記錄。中只顯示姓王的成績(jī)記錄。 （11）為“學(xué)生成績(jī)表”工作簿設(shè)置文件打開(kāi)密碼 123456。 （12）在“學(xué)生成績(jī)表 2”中，根據(jù)每位同學(xué)的總成績(jī)建立柱形圖，并在 該圖中添加數(shù)據(jù)標(biāo)志。 （13）為“學(xué)生成績(jī)表”設(shè)置頁(yè)邊距頁(yè)眉和頁(yè)腳，并練習(xí)設(shè)置打印區(qū) 域設(shè)置打印標(biāo)題及其他打印選項(xiàng)，最后進(jìn)行打印預(yù)覽。 2某部門職工工資表如下表 1-2 所示。 表 1-2 某部門職工工資表 編號(hào)科室姓名 性 別 基本 工資 職務(wù) 工資 獎(jiǎng) 金 應(yīng)發(fā) 工資 公積金 扣款 其他 扣款 實(shí)發(fā) 工資 1 教務(wù)處張建蘭女 89630030050 2 總務(wù)處楊審評(píng)男 75620020080 3 學(xué)生處石為國(guó)男 1250

9、40015045 4 財(cái)務(wù)處楊芳女 95015024035 5 財(cái)務(wù)處柳絲從女 64553030025 6 學(xué)生處黃連素女 100020020084 7 教務(wù)處王小鄭男 45631028025 8 學(xué)生處華麗偉男 78925027030 9 教務(wù)處李鐵牛男 35434045035 10 學(xué)生處廖晨女 114542340045 11 總務(wù)處趙學(xué)軍男 75032130028 12 總務(wù)處楊家輝男 45030032034 13 學(xué)生處張德華男 56026045265 14 總務(wù)處李楠女 78025332010 實(shí)驗(yàn)要求如下： （1）輸入上述職工工資表（“性別”列除外） ，將工作表命名為“工資表” ，

10、 并計(jì)算應(yīng)發(fā)工資=基本工資+職務(wù)工資+獎(jiǎng)金。 （2）利用數(shù)據(jù)的有效性，在“性別”列中只允許用戶選擇男或女輸入每個(gè) 人的性別。 （3）公積金扣款計(jì)算公式為當(dāng)應(yīng)發(fā)工資小于 1300 元時(shí)，扣款為應(yīng)發(fā)工資 的 2%，否則為 5%（用 if 函數(shù)） ，格式保留 2 位小數(shù)。 （4）計(jì)算實(shí)發(fā)工資應(yīng)發(fā)工資公積金扣款其他扣款。 2 （5）在現(xiàn)有表格的下一行計(jì)算基本工資、職務(wù)工資、獎(jiǎng)金、應(yīng)發(fā)工資、公 積金扣款、其他扣款和實(shí)發(fā)工資的總額。 （6）將該表標(biāo)題設(shè)定為“職工工資表” ，合并居中， “姓名” 、 “性別”列水 平垂直均分散對(duì)齊。 （7）給表格加邊框，外粗內(nèi)細(xì)，外紅內(nèi)黑。 （8）將表格內(nèi)每行的高度設(shè)置為

11、18（24 像素） ， “編號(hào)”列寬度設(shè)為 4.5（41 像素） 。 （9）根據(jù)（5）計(jì)算的各工資項(xiàng)目總額，分別計(jì)算基本工資總額、職務(wù)工 資總額和獎(jiǎng)金總額占應(yīng)發(fā)工資總額的比例，建立可分離的餅狀圖，標(biāo)明具體數(shù) 值。 （10）按科室分類匯總實(shí)發(fā)工資、應(yīng)發(fā)工資。 （11）將“職工工資表”設(shè)置密碼保護(hù)，整個(gè)表格的“實(shí)發(fā)工資”列以及 表外的各行各列不允許鼠標(biāo)到達(dá)，其它各列用戶可以輸入數(shù)據(jù)。 （12）將“職工工資表”中的首行凍結(jié)起來(lái)，使得向下按垂直滾動(dòng)條時(shí)， 首行保持不動(dòng)。 （13）再添加若干行的數(shù)據(jù)，進(jìn)行頁(yè)面設(shè)置，使得瀏覽（或打?。r(shí)每頁(yè) 都顯示首行標(biāo)題，頁(yè)腳顯示頁(yè)碼。 3從上海證券交易所（http:/

12、） 、中國(guó)上市公司咨詢網(wǎng) （http:/）或某上市公司的網(wǎng)頁(yè)中，找到相關(guān)的數(shù)據(jù)資料，在 excel 中創(chuàng)建 web 查詢來(lái)獲取相關(guān)數(shù)據(jù)。 三、實(shí)驗(yàn)步驟 1 啟動(dòng) excel，在 sheet1 工作表中輸入表 1-1 數(shù)據(jù)，保存工作簿，將其命名為 “學(xué)生成績(jī)表” ，如下圖所示。 （1）在 j3 單元格中輸入公式“=sum(c3:i3)” ，利用填充柄向下填充至 j10；在 k3 單元格中輸入公式“=average(c3:i3)” ，利用填充柄向下填充至 3 k10。 （2）雙擊 sheet1 工作表標(biāo)簽，輸入“學(xué)生成績(jī)表” 。 （3）選擇 a1:k1 單元格區(qū)域，單擊格式工具欄相應(yīng)按鈕，設(shè)置字體

13、、字 號(hào)、顏色及居中。 （4）略 （5）鼠標(biāo)單擊“學(xué)生成績(jī)表”標(biāo)簽，按住“ctrl”鍵，拖動(dòng)鼠標(biāo)到標(biāo)簽的 其他位置，釋放鼠標(biāo)；再同（2）將其名稱改為“學(xué)生成績(jī)表 2” 。 （6）選擇 a2:k10 單元格區(qū)域，選擇“數(shù)據(jù)/篩選/自動(dòng)篩選” ，鼠標(biāo)單擊 “平均分”右側(cè)的下三角按鈕，選擇“自定義” ，在【自定義自動(dòng)篩選方式】 ” 對(duì)話框中設(shè)置相應(yīng)的條件后，按【確定】按鈕，顯示符合條件的記錄。若想恢 復(fù)原狀，再用鼠標(biāo)單擊“平均分”右側(cè)的下三角按鈕，選擇“全部”即可。 （7）選擇“2011 級(jí)學(xué)生成績(jī)表”數(shù)據(jù)所在單元格區(qū)域，單擊【格式】 /【自動(dòng)套用格式】 ，出現(xiàn)對(duì)話框，選擇相應(yīng)項(xiàng)格式即可。 （8）選

14、定“學(xué)生成績(jī)表 2” ，選擇 c11 單元格，輸入公式 “=average(c3:c10)” ，在利用填充柄填充至 j11 即可。 （9）略 （10）選擇 a2:k11 單元格區(qū)域，選擇“數(shù)據(jù)/篩選/自動(dòng)篩選” ，鼠標(biāo)單擊 “姓名”右側(cè)的下三角按鈕，選擇“自定義” ，在【自定義自動(dòng)篩選方式】 ”對(duì) 話框中的“姓名”行中輸入“王*” ，按【確定】按鈕，顯示符合條件的記錄。 （11）單擊【格式】/【選項(xiàng)】 ，選擇“安全性”選項(xiàng)卡，在“打開(kāi)權(quán)限密 碼”看重輸入“123456” ，按【確定】按鈕。 （12）選定“學(xué)生成績(jī)表 2” ，選擇 a3:a10，j3;j10 單元格區(qū)域，單擊常用 工具欄上的【圖

15、表向?qū)А堪粹o，選擇“柱形圖” ，單擊“分類軸標(biāo)志”框右邊的 “折疊”按鈕，選擇 a3:a10；單擊【下一步】按鈕，在“數(shù)據(jù)標(biāo)志”選項(xiàng)卡中 選擇“值” ；接下來(lái)可以進(jìn)行其他設(shè)置，單擊【完成】按鈕即可。 （13）略 2 啟動(dòng) excel，在 sheet1 工作表中輸入表 1-2 數(shù)據(jù)，保存工作簿，將其命名為 “某部門職工工資表” （也可以在上題的工作簿中的另一工作表中輸入相應(yīng)數(shù)據(jù)） ，如下圖所示。 4 （1）將工作表命名為“工資表” （略） ；選擇 h3，輸入公式“=sum(e3:j3)” ，并填充至 h16。 （2）在 v2、v3 單元格中分別輸入“男”和“女” ，將 v 列隱藏（也可以 部隱藏

16、） ，選擇 d3，單擊【數(shù)據(jù)】菜單下的【有效性】命令，在“允許”框中 選擇“序列” ，單擊“來(lái)源”框右側(cè)的“折疊”按鈕，選擇 v2:v3，單擊【確定】 按鈕，再利用填充柄將 d3 填充至 d16。 （3）選擇 i3 單元格，輸入公式“=round(if(h33000) 公式：=sumif(a3:a10，a1，b3:b10) 公式：=sumif(a3:a10，*a*，b3:b10) 注意：*a*是使用*（星號(hào)）模糊求和，星號(hào)代表任意多個(gè)字符 公式：=sumif(a3:a10，?a2?，b3:b10) 注意：?a2?：利用通配符“？”（問(wèn)號(hào)）實(shí)現(xiàn)占位功能。問(wèn)號(hào)在字符中 是強(qiáng)行占用一個(gè)字符的位置。

17、（3） 公式：=countif(e2:e7，2500) 公式：=countif(a2:a7，財(cái)務(wù)部) （4）略 （5） 10 公式：=daverage(a3:g9,總工資,a1:g2) （6）-（9）略 3-6 略 實(shí)驗(yàn)三實(shí)驗(yàn)三 財(cái)務(wù)分析模型的設(shè)計(jì)財(cái)務(wù)分析模型的設(shè)計(jì)和建立和建立 一、實(shí)驗(yàn)?zāi)康?掌握獲取數(shù)據(jù)的方法并能設(shè)計(jì)財(cái)務(wù)分析模型。 二、實(shí)驗(yàn)內(nèi)容 1仿照課本將“pz.txt”和“pz.cvs”文本文件的數(shù)據(jù)引入 excel 中； 2建立數(shù)據(jù)庫(kù)查詢，將所給的 access 數(shù)據(jù)庫(kù)（文件名為 zzbb.mdb，內(nèi)容 參見(jiàn)第 3 章）中的資產(chǎn)負(fù)債表和利潤(rùn)表數(shù)據(jù)引入到 excel 工作表中； 3建立

18、web 查詢，地址欄中輸入 “http:/ ，將華能國(guó)際 近三年年的資產(chǎn)負(fù)債表、利潤(rùn)表數(shù)據(jù)導(dǎo)入到 excel 中。 4對(duì)華能國(guó)際當(dāng)年的財(cái)務(wù)報(bào)表進(jìn)行分析（包括比率分析和杜邦分析） 。 三、實(shí)驗(yàn)步驟 1略 2 （1）啟動(dòng) microsoft query。單擊“數(shù)據(jù)”菜單上的“導(dǎo)入外部數(shù)據(jù)”子 菜單，然后單擊“新建數(shù)據(jù)庫(kù)查詢”命令，出現(xiàn)選擇數(shù)據(jù)源對(duì)話框，如圖 3-1 所示。 （2）在指定的數(shù)據(jù)庫(kù)文件中選擇欲導(dǎo)入的數(shù)據(jù)。使用 microsoft query 可以檢索多種類型數(shù)據(jù)庫(kù)中的數(shù)據(jù)，包括 microsoft access、microsoft sql server 和 olap services

19、等等。我們?cè)谶@里選擇數(shù)據(jù)庫(kù)中的“ms access database*” ，按“確定”按鈕，出現(xiàn)選擇數(shù)據(jù)庫(kù)對(duì)話框，如圖 3-2 所示。 圖 3-1 選擇數(shù)據(jù)源對(duì)話框 11 （3）選擇 zzbb.mdb 數(shù)據(jù)庫(kù)，按“確定”按鈕，出現(xiàn)選擇數(shù)據(jù)表對(duì)話框。 選擇資產(chǎn)負(fù)債表，按照向?qū)нM(jìn)行操作。 （4）數(shù)據(jù)返還給 excel 工作表。在查詢向?qū)Ъ磳⑼瓿刹樵冏鳂I(yè)時(shí)，選定數(shù) 據(jù)結(jié)果需返回的工作表：本工作表、新建工作表和數(shù)據(jù)透視表。如果選擇 “將 數(shù)據(jù)返回到 microsoft excel” ，然后再選擇放置外部數(shù)據(jù)的位置，如圖 3-3 所示。 （5）同理，將利潤(rùn)表引入 excel 中即可。 3略 4略 圖 3

20、-3 確定數(shù)據(jù)返還位置對(duì)話框 圖 3-2 選擇數(shù)據(jù)庫(kù)對(duì)話框 12 實(shí)驗(yàn)四實(shí)驗(yàn)四 籌資決策模型的設(shè)計(jì)籌資決策模型的設(shè)計(jì)和建立和建立 一、實(shí)驗(yàn)?zāi)康?練習(xí)相關(guān)的財(cái)務(wù)函數(shù)，并能設(shè)計(jì)和建立相關(guān)的籌資決策模型。 二、實(shí)驗(yàn)內(nèi)容 1按圖 5-4 所示的模型結(jié)構(gòu)，進(jìn)行長(zhǎng)期借款籌資決策動(dòng)態(tài)模型擴(kuò)展設(shè)計(jì)。 要求至少完成除還款方式選擇、還款期間選擇和還款時(shí)點(diǎn)選擇以外的所有設(shè)計(jì) 內(nèi)容。這 3 個(gè)選項(xiàng)在你設(shè)計(jì)的模型中分別固定為等額本息、月付和期末還款。 2根據(jù) 5.2.3 節(jié)所述，按照凈利租賃現(xiàn)值法設(shè)計(jì)借款購(gòu)入設(shè)備或融資租入 設(shè)備的決策模型。 3根據(jù) 5.2.3 節(jié)所述，按照損益平衡租金法設(shè)計(jì)借款購(gòu)入設(shè)備或融資租入 設(shè)備

21、的決策模型。 4根據(jù) 5.3.2 節(jié)所述，參考圖 5-11 中優(yōu)先股資本成本計(jì)算分析模型，按股 利折現(xiàn)模型設(shè)計(jì)普通股資本成本計(jì)算分析模型。 5根據(jù)圖 5-11 模型原理，參照公司普通股（外部權(quán)益）資本成本計(jì)算模 型，設(shè)計(jì)留存收益（內(nèi)部權(quán)益）資本成本計(jì)算分析模型。 6按圖 5-20 所示的模型結(jié)構(gòu)，利用雙變量模擬運(yùn)算表，設(shè)計(jì)一個(gè)針對(duì)貸 款金額和貸款年限的長(zhǎng)期借款分析模型。要求：年利率、每年還款次數(shù)和貸款 年限用控件工具箱中的數(shù)值調(diào)節(jié)鈕或滾動(dòng)條使其成為可變參數(shù)。 7某公司發(fā)行面額為 500 萬(wàn)元的 10 年期債券，票面利率 12%，發(fā)行費(fèi)率 為 5%，所得稅稅率為 33%，設(shè)計(jì)一個(gè)計(jì)算該債券資本成

22、本的模型，并在此基 礎(chǔ)上利用窗體控件使該模型具有一定的通用性。 8某公司目前的資本來(lái)源包括每股面值 1 元的普通股 800 萬(wàn)股和平均利 率 10%的 3000 萬(wàn)元債券?，F(xiàn)有一項(xiàng)目需投資 4000 萬(wàn)元，預(yù)計(jì)投產(chǎn)后每年可增 加營(yíng)業(yè)利潤(rùn)（息稅前利潤(rùn)）400 萬(wàn)元。該項(xiàng)目備選的籌資方案有三個(gè)：按 11%的 利率發(fā)行債券；按面值發(fā)行股利率為 12%的優(yōu)先股；按每股 20 元的價(jià)格增 發(fā)普通股。公司目前的息稅前利潤(rùn)為 1600 萬(wàn)元；公司所得稅率為 25%；證券發(fā) 圖 5-20 長(zhǎng)期借款模型結(jié)構(gòu) 13 行費(fèi)可忽略不計(jì)。要求設(shè)計(jì)一模型完成以下輔助決策功能：計(jì)算按不同方案 籌資后的普通股每股收益；計(jì)算增

23、發(fā)普通股和債券籌資的每股收益無(wú)差別點(diǎn)， 以及增發(fā)普通股和優(yōu)先股籌資的每股收益無(wú)差別點(diǎn)；計(jì)算籌資前的財(cái)務(wù)杠桿 和按三個(gè)方案籌資后的財(cái)務(wù)杠桿；根據(jù)計(jì)算結(jié)果，該模型能給出輔助決策結(jié) 果，即應(yīng)選擇哪一種籌資方案及其理由。 9按圖 5-21 所示的結(jié)構(gòu)設(shè)計(jì)一個(gè)購(gòu)房貸款分析模型，模型右半部分是對(duì) 總還款額和總利息額在等額本息和等額本金還款方式下的分析比較圖。 要求如下： （1）用戶只需輸入房?jī)r(jià)、房屋面積以及等額本金還款分析中的還款月份； 用滾動(dòng)條調(diào)節(jié)首付百分比、貸款利率和貸款年限，其他單元格內(nèi)容由模型根據(jù) 公式自動(dòng)計(jì)算。 （2）數(shù)據(jù)有效性規(guī)則：房?jī)r(jià)、房屋面積為大于等于 0 的數(shù)值；還款月份 為大于等于 1

24、 但小于等于總還款月數(shù)的整數(shù)；首付百分比為 5%80%，無(wú)小數(shù)； 貸款利率為 1.00%10.00%，保留兩位小數(shù)；貸款年限為 530 的整數(shù)。 （3）根據(jù)等額本息和等額本金還款方式下的總還款額、總還款利息繪制 柱形圖，以便更加直觀地對(duì)兩種還款方式進(jìn)行對(duì)比分析。 （4）將房?jī)r(jià)、房屋面積、還款月份以及滾動(dòng)條鏈接單元格以外的整個(gè)工 作表保護(hù)，以防誤錄數(shù)據(jù)。 10某公司的普通股發(fā)行價(jià)格為每股 20 元，上年股利率為每股 1 元，新 發(fā)行普通股的籌資費(fèi)率為 9.62%，股利預(yù)計(jì)增長(zhǎng)率為 17.88%。要求設(shè)計(jì)一模型 計(jì)算該公司的普通股和留存收益的資本成本率，并具有一定的通用性。 三、實(shí)驗(yàn)步驟 1 模型

25、總體結(jié)構(gòu)布局如教材圖 5-4 所示。 參數(shù)調(diào)節(jié)按鈕主要屬性設(shè)置如下表所示。 linkedcellmaxminsmallchange 貸款總額(元) j310000001000010000 圖 5-21 購(gòu)房貸款分析模型 可調(diào)節(jié)參數(shù) 屬性 14 貸款總期限(年) j41511 年利率 l71000022501 貸款期次(月) l1218011 起始期次 l1918011 結(jié)束期次 l2018011 主要單元格公式設(shè)置如下表所示。 單元格公式備注 a3=j12 a4=a3+1 復(fù)制到 a5a27 b3=if(a3=j$6,-ppmt($j$9,$a3,$j$6,$j$3),0) 復(fù)制到 b4-b2

26、7 c3=-cumprinc(j9,j6,j3,1,j12,0) c4=if(a4=j$6,c3+b4,0) 復(fù)制到 c5-c27 d3=if(a3=j$6,j$3-c3,0) 復(fù)制到 d4d27 e3=if(a3=j$6,-ipmt($j$9,$a3,$j$6,$j$3),0) 復(fù)制到 e4e27 f3=-cumipmt(j9,j6,j3,1,j12,0) f4=if(a4j6,j6,l12) j13=-pmt($j$9,$j$6,$j$3) j14=-ppmt($j$9,$j$12,$j$6,$j$3) j15=-cumprinc(j9,j6,j3,1,j12,0) j16=j3-j15

27、j17=-ipmt($j$9,$j$12,$j$6,$j$3) j18=-cumipmt(j9,j6,j3,1,j12,0) j19=if(l19j20,j20,l19) j20=if(l20j6,j6,l20) j21=-cumprinc(j9,j6,j3,j19,j20,0) j22=-cumipmt(j9,j6,j3,j19,j20,0) 2 15 模型總體結(jié)構(gòu)布局如下圖所示。 控件及參數(shù)調(diào)節(jié)按鈕主要屬性設(shè)置如下表所示。 數(shù)據(jù)源區(qū)域 linkedcell 單元格鏈接 max 最大值 min 最小值 smallchange 步長(zhǎng) 租賃設(shè)備名稱 $f$3:$f$11$a$2 租賃年限 $a$

28、5711 租金支付時(shí)點(diǎn) $d$9:$d$10$a$6 每年付款期數(shù) $d$3:$d$8$a$7 租金年利率 $a$920002251 所得稅稅率 $a$1033101 基準(zhǔn)收益率 k $a$1118008001 借款比例 $f$121001001 借款年利率 $k$1230002251 主要單元格公式設(shè)置如下表所示。 單元格公式備注 c3 =index(生產(chǎn)廠家,a2) c4 =租賃年限*index(年租金,a2) c5 =if(a5a15,每期應(yīng)付租金*每年付款 期數(shù),0),if(and(a150,租賃年限=a15),每期應(yīng)付租金*每年付款期 數(shù),0) c15 =b15*所得稅稅率 d15

29、=(b15-c15)/(1+基準(zhǔn)收益率 k)a15 e15 =if(and(租金支付時(shí)點(diǎn)=先付),if(租賃年限a15,借款金額*借款年 利率,0),if(and(a150,租賃年限=a15),借款金額*借款年利率,0) f15 =if(租金支付時(shí)點(diǎn)=先付,if(租賃年限a15,sln(index(設(shè)備價(jià)值, a$2),index(設(shè)備殘值,a$2),index(使用年限,a$2),0), if(and(a150,租賃年限=a15),sln(index(設(shè)備價(jià)值,a$2),index(設(shè) 備殘值,a$2),index(使用年限,a$2),0) g15 =(e15+f15)*所得稅稅率 h15

30、=g15/(1+借款年利率)a15 i15 =if(租金支付時(shí)點(diǎn)=先付,if(租賃年限a15,index(每年維修費(fèi), $a$2),0),if(and(a150,租賃年限=a15),index(每年維修費(fèi),$a$2), 0) j15 =i15*基準(zhǔn)收益率 k k15 =(i15-j15)/(1+基準(zhǔn)收益率 k)a15 選中 b15: k15 區(qū)域 拖動(dòng) 該區(qū) 域右 下角 的復(fù) 制柄 復(fù)制 到 b16: k22 區(qū)域 b23=sum(b15:b22) 選中 b23 并將其復(fù)制到 c23:k23 區(qū)域 c24 =index(設(shè)備價(jià)款,a2)+k23對(duì)應(yīng)于公式 5-1 的項(xiàng) f24 =d23+h23

31、+index(設(shè)備殘值,a2)/(1+基準(zhǔn)收益率 k)租賃年限 對(duì)應(yīng)于公式 5-1 的項(xiàng) g24=if(c24f24,nal0,if(c24f24,nalf24,融資租入設(shè)備,借款購(gòu)入設(shè)備) 3 模型總體結(jié)構(gòu)布局如下圖所示。 控件及參數(shù)調(diào)節(jié)按鈕主要屬性設(shè)置和主要單元格公式設(shè)置與上機(jī)實(shí)驗(yàn) 2 類 似。不同之處在于：h24 =c24-f24；利用單變量求解工具求得 nal=0 時(shí) 的年租金，具體過(guò)程為：選定某一租賃設(shè)備，點(diǎn)擊菜單“工具” 、 “單變量求解” ， 目標(biāo)單元格 h24，目標(biāo)值 0，可變單元格為該設(shè)備的“nal=0 年租金” ，確定后， 可得到相應(yīng)的“nal=0 年租金”和輔助決策方案。

32、 17 4 模型總體結(jié)構(gòu)布局如下圖所示。 控件及參數(shù)調(diào)節(jié)按鈕主要屬性設(shè)置如下表所示。 租賃或借款決策模型-損益租金平衡法(nal=0) 普通股股利折現(xiàn)模型 18 數(shù)據(jù)源區(qū)域 linkedcell 單元格鏈接 max 最大值 min 最小值 smallchange 步長(zhǎng) 普通股 $b$213000100 1 年股利率 $c$3170001001 籌資費(fèi)率 e $c$4130001001 股利固定年增 長(zhǎng)率 $g$4130001001 主要單元格公式設(shè)置如下表所示。 單元格公式備注 b3=c3/10000 b4=c4/10000 b5 =普通股*年股利率 f2 =年固定股利/(普通股*(1-籌資費(fèi)

33、率 e) f3 =年固定股利 f4=g4/10000 f5 =第一年股利/(普通股*(1-籌資費(fèi)率 e)+股利固定年增長(zhǎng)率 a9 =年固定股利/(普通股*(1-籌資費(fèi)率 e) a10 =籌資費(fèi)率 e a11 =a10+0.0001,并將其拖動(dòng)復(fù)制到 a12:a14 區(qū)域. b9 =年股利率 c9 =b9+0.0001,并將其拖動(dòng)復(fù)制到 d9:g9 區(qū)域. 選中 a9:g14 區(qū)域， 對(duì)其調(diào)用模擬運(yùn)算表， 引用行的單元格為 b3，引用列的單元格 為 b4，確定即可。 a18 =第一年股利/(普通股*(1-籌資費(fèi)率 e)+股利固定年增長(zhǎng)率 a19 =籌資費(fèi)率 e a20 =a19+0.0001,并

34、將其拖動(dòng)復(fù)制到 a21:a23 區(qū)域. b18 =股利固定年增長(zhǎng)率 c18 =b18+0.0001,并將其拖動(dòng)復(fù)制到 d18:g18 區(qū)域. 選中 a18:g23 區(qū)域， 對(duì)其調(diào)用模擬運(yùn)算表， 引用行的單元格為 f4，引用列的單元格 為 b4，確定即可。 5 模型總體結(jié)構(gòu)布局如下圖所示。 可調(diào)節(jié)參數(shù) 屬性 留存收益資本成本計(jì)算分析模型 19 控件及參數(shù)調(diào)節(jié)按鈕主要屬性設(shè)置如下表所示。 數(shù)據(jù)源區(qū)域 linkedcell 單元格鏈接 max 最大值 min 最小值 smallchange 步長(zhǎng) 普通股(萬(wàn)元) $c$213000100 1 年股利率 $d$3130001001 籌資費(fèi)率 d $d$

35、4130001001 股利固定年增長(zhǎng)率 $d$8130001001 無(wú)風(fēng)險(xiǎn)報(bào)酬率 $d$10130001001 市場(chǎng)組合預(yù)期報(bào)酬率 $d$11150001001 股票貝塔系數(shù) $d$1221001001 債券投資收益率 $d$1480001001 普通股風(fēng)險(xiǎn)溢價(jià) $d$1595001001 年股利額(元/股) $g$23000101 普通股每股市價(jià) $g$33000050010 主要單元格公式設(shè)置如下表所示。 單元格公式備注 c3=d3/10000 c4=d4/10000 c5 =普通股_萬(wàn)元*年股利率 c6 =年固定股利/(普通股_萬(wàn)元*(1-籌資費(fèi)率 d) c7 =年固定股利 c8=d8/

36、10000 c9 =第一年股利/(普通股_萬(wàn)元*(1-籌資費(fèi)率 d)+固定股利年增長(zhǎng)率 c10=d10/10000 c11=d11/10000 c12=d12/100 c13 =無(wú)風(fēng)險(xiǎn)報(bào)酬率+股票貝塔系數(shù)*(市場(chǎng)組合預(yù)期報(bào)酬率-無(wú)風(fēng)險(xiǎn)報(bào)酬率) c14=d14/10000 c15=d15/10000 c16 =債券投資收益率+普通股風(fēng)險(xiǎn)溢價(jià) f2=g2/100 f3=g3/1000 f6 =年股利額/普通股每股市價(jià) f7 =年股利額*(1+普通股利年增長(zhǎng)率) f8 =固定股利年增長(zhǎng)率 f9 =預(yù)期年股利額/普通股每股市價(jià)+普通股利年增長(zhǎng)率 f10 =無(wú)風(fēng)險(xiǎn)報(bào)酬率 f11 =市場(chǎng)組合預(yù)期報(bào)酬率 f

37、12 =股票貝塔系數(shù) f13 =無(wú)風(fēng)險(xiǎn)報(bào)酬率+股票貝塔系數(shù)*(市場(chǎng)組合預(yù)期報(bào)酬率-無(wú)風(fēng)險(xiǎn)報(bào)酬率) f14 =債券投資收益率 可調(diào)節(jié)參數(shù) 屬性 20 f15 =普通股風(fēng)險(xiǎn)溢價(jià) f16 =債券投資收益率+普通股風(fēng)險(xiǎn)溢價(jià) 6 如教材圖 5-20 所示。 數(shù)據(jù)源區(qū)域 linkedcell 單元格鏈接 max 最大值 min 最小值 smallchange 步長(zhǎng) 年利率 $e$31000100 1 每年還款次數(shù) $g$31211 貸款年限 $i$32011 主要單元格公式設(shè)置如下表所示。 單元格公式備注 a5 =pmt(年利率/每年還款次數(shù),每年還款次數(shù)*貸款年限,-貸款金額,0) a6 =貸款金額 a

38、7 =a6+50000,并將其拖動(dòng)復(fù)制到 a8:a14 區(qū)域. b5 =貸款年限 c5 =b5+1,并將其拖動(dòng)復(fù)制到 d5:i5 區(qū)域. 選中 a5:i14 區(qū)域， 對(duì)其調(diào)用模擬運(yùn)算 表，引用行的單元 格為 i3，引用列的 單元格為 b3，確定 即可。 7 模型總體結(jié)構(gòu)布局如下圖所示。 可調(diào)節(jié)參數(shù) 屬性 21 控件及參數(shù)調(diào)節(jié)按鈕主要屬性設(shè)置如下表所示。 數(shù)據(jù)源區(qū)域 linkedcell 單元格鏈接 max 最大值 min 最小值 smallchange 步長(zhǎng) 借款金額 $b$3100010 1 借款年利率 $c$412002251 借款期限_年 $b$51511 每年付息次數(shù) a $b$612

39、11 籌資費(fèi)率 a $c$71000501 所得稅稅率 $c$840171 債券發(fā)行總額 $e$3100001001 債券總面值 $e$320001001 票面利率 $f$512003001 債券期限_年 $e$62011 每年付息次數(shù) b $e$71211 籌資費(fèi)率 b $f$81200501 主要單元格公式設(shè)置如下表所示。 單元格公式備注 b10 =借款年利率*(1-所得稅稅率)/(1-籌資費(fèi)率 a) b11 =每年付息次數(shù) a*rate(借款期限_年*每年付息次數(shù) a,-(借 款金額*借款年利率/每年付息次數(shù) a)*(1-所得稅稅率),借 款金額*(1-籌資費(fèi)率 a),-借款金額) e1

40、0 =債券總面值*票面利率*(1-所得稅稅率)/(債券發(fā)行總額 *(1-籌資費(fèi)率 b) e11 =每年付息次數(shù) b*rate(債券期限_年*每年付息次數(shù) b,-(債 券總面值*票面利率/每年付息次數(shù) b)*(1-所得稅稅率),債 券發(fā)行總額*(1-籌資費(fèi)率 b),-債券總面值) a15=b11 選中 a23:f28 區(qū)域，對(duì)其調(diào)用模擬運(yùn)算表，引用 行的單元格為 b5，引用列的單元格為 b4，確定即 可。 a23=e11 選中 a15:f20 區(qū)域，對(duì)其調(diào)用模擬運(yùn)算表，引用 行的單元格為 e3，引用列的單元格為 e8，確定即 可。 8 模型總體結(jié)構(gòu)布局如下圖所示。 可調(diào)節(jié)參數(shù) 屬性 22 控件及

41、參數(shù)調(diào)節(jié)按鈕主要屬性設(shè)置如下表所示。 數(shù)據(jù)源區(qū)域 linkedcell 單元格鏈接 max 最大值 min 最小值 smallchange 步長(zhǎng) 普通股股數(shù) $b$310001 1 每股面值 $b$410011 債務(wù)資本 $b$6100001001 債務(wù)利率 $b$71211 所得稅稅率 $c$1240171 債券利率 $f$4100001001 股利率 $f$8100001001 普通股股價(jià) $e$1130011 主要單元格公式設(shè)置如下表所示。 單元格公式備注 b5 =普通股股數(shù)*每股面值 b7=c7/10000 b11 =目前的 ebit+新增 ebit b12=c12/10000 e5

42、=發(fā)行債券*債券利率 e8=f8/10000 e10 =普通股股價(jià)*增發(fā)普通股股數(shù) e14 =(籌資后的 ebit-債務(wù)資本*債務(wù)利率-年利息額)*(1-所得稅稅率) -優(yōu)先股*股利率)/普通股股數(shù) e15 =(籌資后的 ebit-債務(wù)資本*債務(wù)利率)*(1-所得稅稅率)-優(yōu)先股* 股利率)/普通股股數(shù) e16 =(籌資后的 ebit-債務(wù)資本*債務(wù)利率)*(1-所得稅稅率)-優(yōu)先股* 可調(diào)節(jié)參數(shù) 屬性 每股收益分析法籌資決策模型 23 股利率)/(普通股股數(shù)+增發(fā)普通股股數(shù)) f14 =籌資后的 ebit/(籌資后的 ebit-債務(wù)資本*債務(wù)利率-年利息額) f15 =籌資后的 ebit/(

43、籌資后的 ebit-債務(wù)資本*債務(wù)利率-優(yōu)先股*股利 率/(1-所得稅稅率) f16 =籌資后的 ebit/(籌資后的 ebit-債務(wù)資本*債務(wù)利率) f17 =目前的 ebit/(目前的 ebit-債務(wù)資本*債務(wù)利率) c14 =(與每股收益無(wú)差異點(diǎn)的 ebit-債務(wù)資本*債務(wù)利率-年利息額)*(1-所得稅稅率)-優(yōu)先 股*股利率)/普通股股數(shù)-(與每股收益無(wú)差異點(diǎn)的 ebit-債務(wù)資本*債務(wù)利率)*(1-所得 稅稅率)-優(yōu)先股*股利率)/(普通股股數(shù)+增發(fā)普通股股數(shù)) c15 調(diào)用單變量求解工具，目標(biāo)單元格 c14，目標(biāo)值 0，可變單元格 c15，確定即可。 c16 =(與每股收益無(wú)差異點(diǎn)

44、的 ebit-債務(wù)資本*債務(wù)利率)*(1-所得稅稅率)-優(yōu)先股*股利率) /普通股股數(shù)-(與每股收益無(wú)差異點(diǎn)的 ebit-債務(wù)資本*債務(wù)利率)*(1-所得稅稅率)-優(yōu) 先股*股利率)/(普通股股數(shù)+增發(fā)普通股股數(shù))，利用單變量求解工具求得 c17 之值。 c17 調(diào)用單變量求解工具，目標(biāo)單元格 c16，目標(biāo)值 0，可變單元格 c17，確定即可。 9 模型總體結(jié)構(gòu)布局如下圖所示。 控件及參數(shù)調(diào)節(jié)按鈕主要屬性設(shè)置如下表所示。 數(shù)據(jù)源區(qū)域 linkedcell 單元格鏈接 max 最大值 min 最小值 smallchange 步長(zhǎng) 貸款年限 $d$3201 1 貸款利率 $e$430001001

45、首付比例 $e$7100101 還款月份 $f$924011 主要單元格公式設(shè)置如下表所示。 單元格公式備注 b5 =房?jī)r(jià)*房屋面積 b6 =房?jī)r(jià)總額-首付款 b7 =房?jī)r(jià)總額*首付比例 可調(diào)節(jié)參數(shù) 屬性 購(gòu)房貸款分析模型 24 d5 =貸款利率/12 d6 =貸款年限*12 d7=e7/100 d9 =if(f9貸款年限*12,f9,貸款年限*12) b9=d9 b10 =pmt(貸款利率/12,貸款年限*12,-貸款總額,0) b11 =ppmt(貸款利率/12,b9,貸款年限*12,-貸款總額) b12 =ipmt(貸款利率/12,b9,貸款年限*12,-貸款總額) b13 =-cump

46、rinc(貸款月利率,貸款總期數(shù),貸款總額,1,貸款總期數(shù),0)+b14 b14 =-cumipmt(貸款月利率,貸款總期數(shù),貸款總額,1,貸款總期數(shù),0) d4=e4/10000 d5 貸款利率/12 d6 =貸款年限*12 d7=e7/100 d10=d11+d12 d11 =貸款總額/貸款總期數(shù) d12=index(d16:d256,d9) d13 =貸款總額+d14 d14=index(e16:e256,d6) b17 =if(a17=貸款總期數(shù),d$11*a17,0) c17 =if(a17=貸款總期數(shù),貸款總額-b17,0) 選中 b17:c17，并將其公式拖動(dòng) 復(fù)制到 b18:c

47、256 區(qū)域。 d17 =貸款總額*貸款月利率 e17=d17 d18 =if(a18=貸款總期數(shù),c17*貸款月利率,0) e18 =if(a18e10,項(xiàng)目 a 較優(yōu),項(xiàng)目 b 較優(yōu))”. （2） 將 2 個(gè)項(xiàng)目各年的數(shù)據(jù)輸入（或采用公式計(jì)算）到 excel 中，如下圖所示。 29 在單元格 d12 中輸入公式“=irr(d24:d34)” ，將單元格 d12 中的公式復(fù)制 到單元格 e12 中。 （3）在單元格 d13 中輸入公式“=irr(d24:d34-e24:e34)” 。 （4）建立貼現(xiàn)率為 1%-20%的凈現(xiàn)值單變量模擬運(yùn)算表，如上圖所示。發(fā) 現(xiàn) 12%時(shí)是-0.28，13%時(shí)

48、是 6.8，利用插值法公式： “=c17+(d18-d17)/(d19-d17)*(c19-c17)”計(jì)算出的貼現(xiàn)率為 12.04%。 4略 30 實(shí)驗(yàn)六實(shí)驗(yàn)六 excelexcel 最優(yōu)化問(wèn)題模型的建立最優(yōu)化問(wèn)題模型的建立 一、實(shí)驗(yàn)?zāi)康?1練習(xí)掌握 excel 規(guī)劃求解工具的使用。 2掌握與財(cái)務(wù)管理相關(guān)的最優(yōu)化求解問(wèn)題。 二、實(shí)驗(yàn)內(nèi)容 1 “猛?！蹦讨破窂S用牛奶為原料生產(chǎn) a 和 b 兩種奶制品。一桶牛奶可以 在 1 號(hào)生產(chǎn)線上用 12 小時(shí)加工成 3 公斤 a 產(chǎn)品，或者在 2 號(hào)生產(chǎn)線上用 8 小 時(shí)加工成 4 公斤 b 產(chǎn)品。由于市場(chǎng)對(duì) a、b 兩種奶制品的需求非常旺盛，因此 每天所生

49、產(chǎn)的 a、b 兩種奶制品都能全部出售。根據(jù)目前的市場(chǎng)情況，銷售每 公斤 a 產(chǎn)品可獲利 24 元，銷售每公斤 b 產(chǎn)品可獲利 16 元。 “猛?！蹦讨破窂S 每天能得到 50 桶牛奶的供應(yīng)，每天工人的總勞動(dòng)時(shí)間為 480 小時(shí)，并且 1 號(hào) 生產(chǎn)線每天至多能加工 100 公斤 a 產(chǎn)品，2 號(hào)生產(chǎn)線的加工能力沒(méi)有限制。請(qǐng) 為該廠制定一個(gè)生產(chǎn)計(jì)劃，如何安排 a、b 兩種奶制品的生產(chǎn)，能使每天獲利 最大。 2nutri-jenny 是一個(gè)體重控制中心。它為客戶提供各種各樣的冷凍主餐。 這些主餐受到了嚴(yán)格的營(yíng)養(yǎng)成分監(jiān)控，保證客戶膳食平衡。一種新的主餐稱為 “牛腰間肉大餐” 。是由牛腰間肉和肉汁，加上一

50、些豌豆、胡羅卜和面包卷制 成的。nutri-jenny 需要確定主餐中各種配料的數(shù)量以滿足營(yíng)養(yǎng)需求，同時(shí)使 成本盡可能地低。每一種配料的營(yíng)養(yǎng)成分和成本如表 9-4 所示。 表表 9 9- -4 4 每每種種配配料料的的營(yíng)營(yíng)養(yǎng)養(yǎng)成成分分和和成成本本表表 配料 卡路里 （每盎司） 脂肪所含卡路里 （每盎司） 維生素 a （國(guó)際單位 每盎司） 維生素 c （毫克每盎 司） 蛋白質(zhì) （克每盎 司） 成本 （每盎司） 牛腰間肉 54198 40 美分 肉汁 20151 35 美分 豌豆 151531 15 美分 胡蘿卜 835011 18 美分 面包卷 40101 10 美分 主餐的營(yíng)養(yǎng)要求如下：必須含

51、有 280 到 320 之間的卡路里；脂肪所含 卡路里不能超過(guò)總卡路里含量的 30%；必須至少含有 600 國(guó)際單位的維生素 a、10 毫克的維生素 c、30 克的蛋白質(zhì)，而且由于實(shí)際原因，至少必須包含 2 盎司的牛腰間肉，每盎司牛腰間肉至少配有半盎司的肉汁。 要求：建立線性規(guī)劃模型并求解，生成規(guī)劃求解報(bào)告。 3某企業(yè)有三個(gè)工廠，生產(chǎn)的產(chǎn)品將運(yùn)到 5 個(gè)倉(cāng)庫(kù)，三個(gè)工廠的生產(chǎn)能 力分別為 310、260 和 280，每個(gè)倉(cāng)庫(kù)的需求量為 180、80、200、160 和 220。 從工廠運(yùn)到各倉(cāng)庫(kù)的運(yùn)費(fèi)如表 9-5 所示。問(wèn)該公司怎樣安排，所花費(fèi)的總運(yùn)費(fèi) 最?。?31 表表 9 9- -5 5 從

52、從工工廠廠到到倉(cāng)倉(cāng)庫(kù)庫(kù)的的運(yùn)運(yùn)費(fèi)費(fèi)表表 運(yùn)費(fèi)倉(cāng)庫(kù) 1倉(cāng)庫(kù) 2倉(cāng)庫(kù) 3倉(cāng)庫(kù) 4倉(cāng)庫(kù) 5 工廠 a 108654 工廠 b 65436 工廠 c 34559 4現(xiàn)有一筆資金，準(zhǔn)備購(gòu)買甲、乙、丙三個(gè)公司的股票，各公司在過(guò)去 12 年的收益率如表 9-6 所示。 表 9-6 各公司過(guò)去 12 年的收益率 年份甲乙丙 111.20%8.00%10.90% 210.80%9.20%22.00% 311.60%6.60%37.90% 4-1.60%18.50%-11.80% 5-4.10%7.40%12.90% 68.60%13.00%-7.50% 76.80%22.00%9.30% 811.90%14.0

53、0%48.70% 912.00%20.50%-1.90% 108.30%14.00%19.10% 116.00%19.00%-3.40% 1210.20%9.00%43.00% 要求：在保證收益率不低于 12%的前提下，怎樣組合可以使風(fēng)險(xiǎn)最?。吭?方差不大于 0.01 的前提下，怎樣組合可以使收益率最大？若風(fēng)險(xiǎn)厭惡度等于 0.5，則最優(yōu)證券組合是什么？此時(shí)的收益率和方差各為多少？ 5求解方程組。 三、實(shí)驗(yàn)步驟 1 步驟 1：分析問(wèn)題，整理出決策變量、目標(biāo)變量、目標(biāo)函數(shù)和各項(xiàng)約束條 件函數(shù)。 本生產(chǎn)計(jì)劃是要解決如何安排 a、b 兩種奶制品生產(chǎn)數(shù)量，才能獲利最大， 因此目標(biāo)變量為總利潤(rùn)，設(shè)為 y。

54、決策變量為 a、b 兩種奶制品生產(chǎn)數(shù)量，設(shè) 6640128 80221830 100162520 zyx zyx zyx 32 x1 和 x2 分別為 a、b 兩種奶制品的產(chǎn)量（公斤） ，顯然，x1 和 x2 必須大于等 于 0.因?yàn)槊抗?a 產(chǎn)品可獲利 24 元，每公斤 b 產(chǎn)品可獲利 16 元，那么目標(biāo) 函數(shù)即為：y=24x1+16x2。由于 1 號(hào)生產(chǎn)線可用 12 小時(shí)加工成 3 公斤 a 產(chǎn)品， 因此生產(chǎn)每公斤 a 產(chǎn)品所需要的時(shí)間為 4 小時(shí)；同理，2 號(hào)生產(chǎn)線生產(chǎn)每公斤 b 產(chǎn)品所需要的時(shí)間為 2 小時(shí)，生產(chǎn)兩種產(chǎn)品的總時(shí)間不能超過(guò) 480 小時(shí)，這 樣工時(shí)的約束條件為：4x1+

55、2x2480。因?yàn)橐煌芭Ｄ炭杉庸こ?3 公斤 a 產(chǎn)品或 4 公斤 b 產(chǎn)品，每天只能得到 50 桶牛奶，所以原材料的約束條件為： x1/3+x2/450。又因?yàn)?1 號(hào)生產(chǎn)線每天只能加工 100 公斤 a 產(chǎn)品，2 號(hào)生產(chǎn) 線的加工能力沒(méi)有限制，所以生產(chǎn)能力的約束條件為 x1100.根據(jù)上述分析， 可列出以下目標(biāo)函數(shù)和約束條件： 0 x,x 100 x 50 4 x 3 x 4802x4x:st 16x24xy:max 21 1 21 21 21 步驟 2：根據(jù)目標(biāo)函數(shù)和約束條件，在 excel 中建立參考模型。如下圖所 示。 步驟 3：檢查模型中各單元格的計(jì)算公式。 c9 單元格中輸入公式

56、“=c8/c3*c4” ，將其復(fù)制到 d9 中。 c10 單元格中輸入公式“=c8/c3” ，將其復(fù)制到 d10 中。 c11 單元格中輸入公式“=c8*c5” ，將其復(fù)制到 d11 中。 e8 單元格中輸入公式“=c8” ，e9 公式“=sum(c9:d9)” ，e10 公式 “=sum(c10:d10)” ，e11 公式“=sum(c11:d11)” 步驟 4：設(shè)置規(guī)劃求解的各項(xiàng)參數(shù)并求解。 選擇“工具”中的“規(guī)劃求解”選項(xiàng)，設(shè)置求解此問(wèn)題的規(guī)劃求解的各項(xiàng) 參數(shù)。其中$e$11 為目標(biāo)函數(shù)的因變量，$c$8:$d$8 為決策變量， $c$8:$d$80 和$e$8:$e$10$f$8:$f

57、$10 為約束條件。 33 步驟 5：分析求解結(jié)果，仔細(xì)閱讀“運(yùn)算結(jié)果報(bào)告” 、 “敏感性報(bào)告”與 “極限值報(bào)告” ，并理解報(bào)告中的各項(xiàng)內(nèi)容。 求解后發(fā)現(xiàn)：當(dāng) 1 號(hào)生產(chǎn)線安排 60 公斤的生產(chǎn)量且 2 號(hào)生長(zhǎng)線上安排 120 公斤的生產(chǎn)量時(shí)， “猛?！惫镜墨@利最大，達(dá)到 3360 元。 2 步驟 1：分析問(wèn)題，整理出決策變量、目標(biāo)變量、目標(biāo)函數(shù)和各項(xiàng)約束條 件函數(shù)。此步略。 步驟 2：根據(jù)目標(biāo)函數(shù)和約束條件，在 excel 中建立參考模型。如下圖所 示。 步驟 3：檢查模型中各單元格的計(jì)算公式。 g14 單元格中輸入公式 “=b14*$b$22+c14*$c$22+d14*$d$22+e1

58、4*$e$22+f14*$f$22” ，并利用填充柄 復(fù)制到 g18。 h20 單元格中輸入公式“=0.5*b22” ，h22 單元格中輸入公式 “=b10*b22+c10*c22+d10*d22+e10*e22+f10*f22” 。 步驟 4：設(shè)置規(guī)劃求解的各項(xiàng)參數(shù)并求解。 選擇“工具”中的“規(guī)劃求解”選項(xiàng)，設(shè)置求解此問(wèn)題的規(guī)劃求解的各項(xiàng) 參數(shù)。其中$h$22 為目標(biāo)函數(shù)的因變量，$b$22:$f$22 為決策變量，$b$22 =$h$19；$b$22:$f$220；$c$22=$h$20；$g$14$i$14；$g$14$h $14；$g$15$i$15；$g$16:$g$18$=$h$1

59、6:$h$18 為約束條件。單擊【確 定】即可。 3 同上，步驟 1 略。 步驟 2：根據(jù)目標(biāo)函數(shù)和約束條件，在 excel 中建立參考模型。如下圖所 示。 34 步驟 3：檢查模型中各單元格的計(jì)算公式。 h8 單元格中輸入公式“=sum(c8:g8)，并利用填充柄復(fù)制到 h10。 c11 單元格中輸入公式“=sum(c8:c10)” ，并利用填充柄復(fù)制到 g11。 c13 單元格中輸入公式“=sum(c8:g10*c3:g5)” 。 步驟 4：設(shè)置規(guī)劃求解的各項(xiàng)參數(shù)并求解。 選擇“工具”中的“規(guī)劃求解”選項(xiàng)，設(shè)置求解此問(wèn)題的規(guī)劃求解的各項(xiàng) 參數(shù)。其中$c$13 為目標(biāo)函數(shù)的因變量，$c$8:

60、$g$10 為決策變量， $c$11:$g$11=$c$12:$g$12；$c$8:$g$100；$h$8:$h$10$i$8:$i$10, 單擊【確定】即可。 4.仿照課本例 9-6即可。 5略 35 實(shí)驗(yàn)七實(shí)驗(yàn)七 財(cái)務(wù)預(yù)測(cè)模型的設(shè)計(jì)和建立財(cái)務(wù)預(yù)測(cè)模型的設(shè)計(jì)和建立 一、實(shí)驗(yàn)?zāi)康?1練習(xí)掌握預(yù)測(cè)方法和預(yù)測(cè)函數(shù)，包括 intercept、slope、linest、forcast、trend 等。 2掌握利用數(shù)據(jù)分析工具進(jìn)行財(cái)務(wù)預(yù)測(cè)。 二、實(shí)驗(yàn)內(nèi)容 1甲企業(yè)只生產(chǎn)一種產(chǎn)品，在 2010 年 1 月至 2011 年 12 月生產(chǎn)的產(chǎn)品數(shù) 量（單位：件）如表 10-10 所示。 表 10-10 甲企業(yè)