任務5數(shù)據(jù)分析工具的應用

運用Excel分析與處理財務數(shù)據(jù)基礎(chǔ)知識EXCEL數(shù)據(jù)分析工具及應用Excel在財務管理中的應用Excel數(shù)據(jù)分析工具及應用

數(shù)據(jù)分析工具的安裝與卸載模擬運算表

方案分析1234

規(guī)劃求解數(shù)據(jù)分析工具的安裝與卸載安裝分析工具庫“分析工具庫”是一個加載宏文件，文件名為Analys32.xll（加載宏文件是可以自動執(zhí)行一系列復雜任務的操作命令的組合）。但這一工作在安裝MicrosoftOffice組件時已經(jīng)加載。安裝步驟如下打開Excel工作簿，選擇菜單欄中【工具】菜單中的【加載宏】，如圖所示。加載宏在打開“加載宏”對話框，對話框如圖5-2所示，單擊【分析工具庫】復選框，框前即出現(xiàn)√號，表明已被選定，選擇【確定】。選擇分析工具庫卸載分析工具庫當不需要使用分析工具庫時，可以通過設(shè)置來卸載分析工具庫。操作步驟：打開Excel工作簿，選擇菜單欄中【工具】菜單中的【加載宏】，如圖5-1所示。在打開“加載宏”對話框，對話框如圖5-4所示，單擊【分析工具庫】復選框，將框前出現(xiàn)√號取消。選擇取消后，單擊【確定】按鈕，即可自動卸載分析工具庫。自此在“工具”菜單中的“數(shù)據(jù)分析”選項自動隱藏。圖5-4卸載分析工具庫模擬運算表什么是模擬運算表？Excel模擬運算表是對工作表中一個單元格區(qū)域內(nèi)的數(shù)據(jù)進行模擬運算，測試使用一個或兩個變量的公式中變量對運算結(jié)果的影響。模擬運算表的類型①基于一個輸入變量的表，用這個輸入變量測試它對多個公式的影響；——單變量模擬運算表②基于兩個輸入變量的表，用這兩個變量測試它們對于單個公式的影響——雙變量模擬運算表單變量模擬運算表【1】某人想買房，由于資金不足需要向銀行貸款30萬，還款年限為10年，目前年利率假設(shè)為4%，采取每月等額還款的方式。①請幫助該買房人計算在現(xiàn)有利率下每月的償還額。②根據(jù)國家經(jīng)濟的發(fā)展，銀行利率也會有調(diào)整，要了解利率變動對自身的償還能力的影響。請幫助該買房人計算不同利率條件下的每月償還額。③由于買房人經(jīng)濟收入并不是很穩(wěn)定，需要根據(jù)實際情況調(diào)整還款期限。請幫助該買房人計算出還款期限是5年、10年、15年、20年、25年和30年時，每月等額還款的數(shù)額。單變量模擬運算表主要是用來分析當其他因素不變時，一個參數(shù)的變化對目標結(jié)果的影響。問題①求解：可利用PMT函數(shù)計算。(1)使用【文件/新建】或【新建】工具，建立一新工作簿，并選擇一張工作表，將住房貸款有關(guān)的基本數(shù)據(jù)輸入該工作表，如圖5-5（B4:D8）單元格區(qū)域所示。(2)在D9單元格中輸入公式“=PMT(D5/12,D8,D4)”，按下【Enter】鍵，計算出所要的結(jié)果。上述公式中三個參數(shù)的含義是：第一個參數(shù)是利率，由于償還額按月計算，所以年利率除以12，將其轉(zhuǎn)換為月利率。第二個參數(shù)是還款期，10年共120各月。第三個參數(shù)是貸款金額。住房貸款還款的Excel分析圖問題②求解：可利用模擬運算表計算不同利率下的月償還額。輸入貸款期各種可能的不同利率數(shù)據(jù)，如圖5-6（F4:F16）單元格區(qū)域所示。住房貸款還款的Excel模擬運算表分析圖選擇包含公式和需要進行模擬運算目標單元區(qū)域（F3:G16），如圖5-7所示。住房貸款還款的Excel模擬運算表分析圖單擊【數(shù)據(jù)】→【模擬運算表】命令，彈出如圖5-8所示的對話框。在“輸入引用列的單元格”中輸入$D$5，再單擊【確定】按鈕，各分析值自動填入分析表中，如圖5-9（G4：G16）單元格區(qū)域所示。住房貸款還款的Excel模擬運算表分析圖模擬運算表工具使用示意圖雙變量模擬運算表對于問題③我們不僅要考慮貸款年利率的變化，而且還要考慮還款年限。這時需要分析不同的利率和不同的貸款期限對貸款的償還額的影響，這時需要使用雙變量模擬運算表。問題③求解：選擇某個單元格區(qū)域作為模擬運算表存放區(qū)域，在該區(qū)域的最左列輸入假設(shè)的利率變化范圍數(shù)據(jù)；在該區(qū)域的第一行輸入可能的貸款年限數(shù)據(jù)。如圖5-10（B11:G11）單元格區(qū)域所示。模擬運算表工具使用示意圖在模擬運算表區(qū)域的左上角單元格輸入計算月償還額的計算公式。選定整個模擬運算表區(qū)域。如圖（A11:G24）單元格區(qū)域所示。模擬運算表工具使用示意圖單擊數(shù)據(jù)菜單中的模擬運算表命令。在模擬運算表對話框（如圖5-12）的輸入引用行的單元格框中輸入“$C$6”（年份）；在輸入引用列的單元格框中輸入“$C$5”（年利率）,單擊【確定】。雙變量模擬運算表的計算結(jié)果如圖5－13所示。其中B12：G24單元格區(qū)域的計算公式為“{=表(C6,C5)}”，表示其是一個以C6為行變量，C5為列變量的模擬運算表。單變量求解什么是單變量求解？單變量求解就是求解只有一個變量的方程的根，方程可以是線性方程，也可以是非線性方程?！纠?】某公司想向銀行貸款900萬元人民幣，貸款利率是8%，貸款限期為5年，如果公司每年可償還100萬元，該公司最多可貸款多少金額？(1)在工作表中輸入原始數(shù)據(jù)，如圖：

貸款總額計算(2)建立可變數(shù)公式；在單元格B2中輸入公式=PMT(B1,B3,B4)”；(3)設(shè)置求解公式：菜單【工具】—單變量求解—對話框中輸入：目標單元格、目標值、可變單元格，如圖所示?！締巫兞壳蠼狻繉υ捒?.3方案分析方案，就是已命名的一組輸入值，這組輸入值保存在工作表中，并可用來自動替換某個計算模型的輸入值，用來預測模型的輸出結(jié)果。對于同一解題方案的模型參數(shù)，可以創(chuàng)建多組不同的參數(shù)值，得到各組不同的結(jié)論，每組參數(shù)和結(jié)論都是一個方案?！?】某化工企業(yè)生產(chǎn)產(chǎn)品

乙烯、

丙烯、丁二烯，在2008年的銷售額分別為400萬元、600萬元和300萬元，銷售成本分別為300萬元、380萬元和220萬元。根據(jù)市場情況推測，2009年產(chǎn)品的銷售情況有好、一般和差三種情況，每種情況下的銷售額及銷售成本的增長率如圖5-17所示。產(chǎn)品銷售資料及預計增長率建立方案（1）設(shè)計方案計算分析，如圖5-17所示，選擇單元格H7并輸入公式“=SUMPRODUCT(B3:B5,1+H4:H6)-SUMPRODUCT(C3:C5,1+I4:I6)”。（2）對不同的產(chǎn)品進行命名，以便分析。分別將單元格H4的名字為“乙烯銷售額增長率”，單元格I4的名字為“乙烯銷售成本增長率”，單元格H5的名字為“丙烯銷售額增長率”，單元格I5的名字為“丙烯銷售成本增長率”，單元格H6的名字為“丁二烯銷售額增長率”，單元格I6的名字為“丁二烯銷售成本增長率”，單元格H7的名字為“總銷售利潤”。（3）單擊【工具】菜單，選擇【方案】項，系統(tǒng)彈出【方案管理器】對話框，如圖所示，單擊【添加】按鈕，系統(tǒng)彈出【添加方案】對話框，如圖所示?！痉桨腹芾砥鳌繉υ捒颉咎砑臃桨浮繉υ捒颍?）在【添加方案】對話框中，【方案名】編輯框中輸入“方案1好”，【可變單元格】編輯框中輸入“$H$4:$I$6”，單擊【確定】按鈕，系統(tǒng)彈出【方案變量值】對話框，如圖所示?！痉桨缸兞恐怠繉υ捒颍?）在【方案變量值】對話框中輸入每個可變單元格的值（這里要按行輸入），完畢后單擊【添加】按鈕，系統(tǒng)會彈出如圖所示的【添加方案】對話框，對第2個方案進行輸入；待所有方案輸入完畢后，單擊【方案變量值】對話框中的【確定】按鈕，系統(tǒng)返回到【方案管理器】對話框，如圖所示。此時，可單擊【關(guān)閉】按鈕，回到工作表?！痉桨腹芾砥鳌繉υ捒驗g覽方案（1）打開原工作表，并激活工作表。（2）單擊【工具】菜單，選擇【方案】項，系統(tǒng)彈出【方案管理器】對話框，如圖所示，選擇要想查看的方案，單擊【顯示】按鈕，則系統(tǒng)就自動顯示出該方案的執(zhí)行結(jié)果，如圖所示。編輯方案對做好的方案進行修改，只需在圖5-21所示的【方案管理器】對話框中選中需要修改的方案，單擊【編輯】按鈕，系統(tǒng)彈出如圖5-20所示的對話框，進行相應的修改即可。若要刪除某一方案，則在圖5-21所示的【方案管理器】對話框中選中需要刪除的方案，單擊【刪除】按鈕。若要增加方案，則在圖5-21所示的【方案管理器】對話框中單擊【添加】按鈕，然后在圖5-19所示的對話框填寫相關(guān)的項目。

建立方案摘要（1）在圖所示的【方案管理器】對話框中單擊【摘要】按鈕，彈出【方案摘要】對話框，如圖所示，在【結(jié)果類型】中選擇“方案摘要”項，在【結(jié)果單元格】中輸入“H7”，然后單擊【確定】按鈕，則系統(tǒng)在當前工作簿中自動建立一個名為“方案總結(jié)”的工作表，如圖所示。【方案總結(jié)】對話框方案摘要規(guī)劃求解在經(jīng)濟管理中，經(jīng)常會遇到各種規(guī)劃問題，例如：人力資源的調(diào)度、產(chǎn)品生產(chǎn)的安排、運輸線路的規(guī)劃、生產(chǎn)材料的搭配、采購批次的確定等。這類問題有一個共同要求，那就是：如何合理利用各種有限的資源實現(xiàn)最佳的經(jīng)濟效益，也就是達到利潤最大、成本最低、費用最省等目標。規(guī)劃求解具有如下三個特點：（1）所求問題都有單一的目標，如求生產(chǎn)的最低成本，求運輸?shù)淖罴崖肪€，求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時間以及求其他目標函數(shù)的最佳值等。（2）總是有明確的不等式約束條件。比如庫存不能低于一定的數(shù)量，否則造成原料短缺或產(chǎn)品缺貨；生產(chǎn)產(chǎn)品不能超過一定額度，否則會造成商品積壓等。（3）問題都有直接或間接影響約束條件的一組輸入值。建立規(guī)劃求解模型Excel規(guī)劃求解問題的基本構(gòu)成（1）決策變量（variable）一個或一組可變單元格，可變單元格稱為決策變量，一組決策變量代表一個規(guī)劃求解的方案（2）目標函數(shù)目標函數(shù)表示規(guī)劃求解要達到的最終目標，是規(guī)劃求解的關(guān)鍵。它是規(guī)劃求解中可變量的函數(shù)（3）約束條件約束條件是實現(xiàn)目標的限制條件。建立規(guī)劃求解模型：第1步——建立求解工作表(輸入原始數(shù)據(jù)及相應的各公式)第2步——設(shè)置求解參數(shù)選擇“工具”—“規(guī)劃求解”菜單，設(shè)置以下求解的各項參數(shù)：設(shè)置目標單元格：輸入目標函數(shù)所在單元格(為總余額單元格)設(shè)置目標：最大值、最小值或值的數(shù)值(最大利潤,即最大值)設(shè)置可變單元格：它的確定決定結(jié)果（為生產(chǎn)數(shù)量）設(shè)置約束條件：單擊【添加】按鈕—輸入約束條件—按添加—依次輸入所有約束條件—確定第3步——保存求解結(jié)果在規(guī)劃求解對話框中按“求解”—在規(guī)劃求解結(jié)果對話框中按“保存規(guī)劃求解結(jié)果”求解優(yōu)化問題【4】某農(nóng)場每天至少使用800磅特殊飼料。這種特殊飼料由玉米和大豆粉配制而成，含有以下成份：特殊飼料的營養(yǎng)要求是至少30%的蛋白質(zhì)和至多5%的纖維。該農(nóng)場希望確定每天最小成本的飼料配制。求解過程：步驟1：根據(jù)問題建立數(shù)學模型因為飼料由玉米和大豆粉配制而成，所以模型的決策變量定義為：

x=每天混合飼料中玉米的重量（磅）

y=每天混合飼料中大豆粉的重量（磅）目標函數(shù)是使配制這種飼料的每天總成本最小，因此表示為：z=0.3×x+0.9×y模型的約束條件是飼料的日需求量和對營養(yǎng)成份的需求量，具體表示為：x+y≥800//每天所需飼料0.09×x+0.6×y≥0.3(x+y)//蛋白質(zhì)0.02×x+0.06×y≤0.05(x+y)//纖維將上述不等式化簡后，完整的模型為：minz=0.3×1+0.9×2s.t.x+y≥8000.21×x-0.3×y≤00.03×x-0.01×y≥0x,y≥0步驟2建立工作表

工作表設(shè)計如圖所示：建立營養(yǎng)配方模型工作表其中，輸入數(shù)據(jù)的單元格使用了陰影格式，即B4:C7和F5:F7；變量和目標函數(shù)單元格為B12:D12；D4:D7中輸入了約束公式到D4=SUMPRODUCT(B4:C4,$B$12:$C$12)D5=SUMPRODUCT(B5:C5,$B$12:$C$12)D6=SUMPRODUCT(B6:C6,$B$12:$C$12)D7=SUMPRODUCT(B7:C7,$B$12:$C$12)步驟3應用規(guī)劃求解工具單擊【工具——規(guī)劃求解】，出現(xiàn)如圖所示的“規(guī)劃求解參數(shù)”對話框，設(shè)計相應的參數(shù)。規(guī)劃求解參數(shù)設(shè)置并且單擊【