【正文】 此資料來自 企業(yè) (), 大量管理資料下載 7 第 2章 模型設(shè)計導(dǎo)論 在第一章里，我們已經(jīng)看到了 傳統(tǒng)的模型設(shè)計的缺陷之處。如果我們的目的是依據(jù) Excel做出決策或者我們要完全依賴于這個結(jié)果，那么我們必須采用一種不同的途徑，一種更專業(yè)化的途徑。圖 21概括了設(shè)計中的幾個階段。 、模型設(shè)計基礎(chǔ) 設(shè)計是個性化的，你開發(fā)出自己認(rèn)可的、欣賞的模型風(fēng)格，你就能輕而易舉地將其重復(fù)運用。設(shè)計的必要性取決于應(yīng)用的復(fù)雜性，你必須對不同種類的工作表采用不同的計劃和方法。想要做到不過多考慮就能正 確地使用關(guān)鍵的公式其實是很簡單的。 所有的模型設(shè)計都遵循統(tǒng)一的設(shè)計步驟和方法，并且工作表也遵循一定的設(shè)計格式。對于一個人來說，簡單的工作表就可能夠用，而模型的設(shè)計也必須符合簡單的規(guī)則，特別是模型會被其他人使用或者被合并用于決策的制定。 、對象 很多人并不對對象和目標(biāo)進(jìn)行深入地考慮。 模型常常能給我們提供更多的信息，但在很多例子中，由于答案隱藏在計算中，所以我們很難獲取信息。有了預(yù)算的和實際的數(shù)字，在絕對數(shù)和相對數(shù)差異基礎(chǔ)之上的差異分析報告就可以和管理報告還有圖表一起形成單個的報告。也許對于同一個模型會有許多不同的用戶，他們對于輸入、其他一些細(xì) 節(jié)和信息有不同的要求。但是，用戶可能希望在同一個地方看到所有的輸入，并且需要有關(guān)于在哪里輸入數(shù)據(jù)和輸入什么樣的數(shù)據(jù)的提示與指導(dǎo)。 Visual Basic 編程系統(tǒng)是通過先設(shè)計對話框，然后對按鈕賦予一段操作程序，對它進(jìn)行控制并使之工作。 這種對話框應(yīng)該是： ? 直觀的； 此資料來自 企業(yè) (), 大量管理資料下載 9 ? 清楚的； ? 能通過提供符合邏輯的信息來指導(dǎo)用戶操作。用戶根據(jù)提示給變量賦值，并按下按鈕來計算出一個答案，這就如同 O17BⅡ那樣的手持財務(wù)計算器一樣。從圖中可以看出，這一簇信息是從上到下的全面顯示。變量的值不是硬性編碼的，這一點尤其重要。在這個過程中，他們可能會發(fā)現(xiàn)一些需要納入模板的新變量。公共稅收的支付方法在英國正從年支付體系變化為四季度支付體系。給主要的變量和模板命名的方法有助于簡化現(xiàn)有模型的維護。現(xiàn)在的 Excel可以將分離的工作表組合在一個二維的工作簿中，而不必象在原來的 Lotus123和 Excel做的那樣，將一組分離的工作表連接起來。 圖 23的例子把設(shè)計分解 為： ? 用戶輸入； ? 小結(jié)處理 —— 使更新后的輸入可視化。 模型中的信息流遵循這樣一種邏輯構(gòu)架，正如用戶所期待的那樣，輸入放在左上角。但是需要再次強調(diào)的是，輸入?yún)^(qū)域和計算不應(yīng)該混合在一起，應(yīng)該分成若干個邏輯區(qū)域。本書 中的模型就遵循著這樣的格式。布局的設(shè)計便于用戶和模型設(shè)計者對模型的理解，這點是非常重要的，同時，布局設(shè)計對于日后深入地開發(fā)模型更是至關(guān)重要。這樣做是為了確保計算的完整性。利用有一定輸入范圍的輸入單元格，或者是用一個已命名的單元格意味著你只需要改動一個單元格，則整個文件將會準(zhǔn)確地自我更新。 圖 24（ ）中的模型運用了按鈕或者說是組合框連接另外兩個名稱為“ Inputs”和 ”Reports”的工作表。 此資料來自 企業(yè) (), 大量管理資料下載 11 我們將會在 下一章節(jié)中詳細(xì)闡述這些特征。 圖 24（原書第 14頁， ） 、管理報告 對于較大的模型，管理報告和小結(jié)通常是必不可少的，它們應(yīng)該在一個完整的管理報告區(qū)域中。 例如，一個項目管理的應(yīng)用應(yīng)該在模型中顯示覆蓋比率和證券化程度。我們可以通過查看如何添加新變量和審核設(shè)計中的漏洞來測試它的可發(fā)展性。一個單一的凈現(xiàn)值模型對于決策的制定是遠(yuǎn)遠(yuǎn)不夠的，模型的發(fā)展應(yīng)該包含一些更深入的對最終結(jié)果 8產(chǎn)生變化程度的測試。模擬使得模型的發(fā)展包含一系列的輸入而不僅僅是單一的數(shù)字，這樣的輸入就會得出相應(yīng)的一組結(jié)果。人們常?？梢酝ㄟ^圖表輕易地理解復(fù)雜的東西。 、測試 測試是很有必要的，它可以確保沒有計算上的錯誤和由模型得到的信息流是正確的。測試的數(shù)據(jù)必須要用到所有的按鈕、輸入、頻率、支付方式等。 、保護 如果模型會被別人使用，那么模型的保護就變得非常重要。所有的工作表格都能被保護，對輸入單元格也可以不進(jìn)行保護。例如，如果一個預(yù)算模型讓用戶使用，而用戶卻在單元格里輸入具體的數(shù)字而不是公式。當(dāng)然，其他人在使用的時候可能會從檢查每一個單元格可能的錯誤開始。這樣做可能會面臨這樣的一種風(fēng)險：他們或者他們的同事在 日后對模型編碼進(jìn)行維護的時候可能會在某些方面遇到困難。理想的情況是將說明寫進(jìn)模型而不是寫在文件的一小片紙上，并且要遵循： ? 采用一個特定的設(shè)計或模板的原因； ? 寫出關(guān)鍵的公式和計算步驟； ? 規(guī)則和方法。盡管這個過程很痛苦，尤其在你耗用的大量的時間來制造出這樣一個杰作之后。讓用戶參與到模型的設(shè) 計過程，并且詢問他們的意見能夠充分調(diào)動用戶的積極性。 以上的 13點將會幫助你更有序地工作。顯然，模型的復(fù)雜程度影響著你所需要完成的工作量。 我們在下一章節(jié)中會討論幾種能使你的模型功能更強大的特征。這樣做的目的是為了向您展示如何應(yīng)用Excel來建立功能更強大而沒有錯誤的模型。設(shè)計的連貫性和遵循統(tǒng)一的設(shè)計方法是很重要的。 此資料來自 企業(yè) (), 大量管理資料下載 14 第 3章 特征和技術(shù) 在上一章里，我們討論了模型設(shè)計的計劃和在設(shè)計中的邏輯問題。本章不會把所有的特征一一詳盡地列出，但本章所要達(dá)到的目的就是要揭示最初的和最終的模型之間的差異。 圖 31 的模型是在文件 中。打開文件并依次點擊來瀏覽工作表的制作過程。在 C14這個單元格中，凈現(xiàn)值就是把折現(xiàn)后的現(xiàn)金流量相加得到的。這個選項能讓你看到計算用的公式（見圖 32）。正如你所看到的，它只是根據(jù)如下的現(xiàn)金流量的公式來計算凈現(xiàn)值： N u m b e rP e r i o dF ac t o rP e r io d _%)101( 1_ ?? 圖 32 (原書第 19 頁 ) 圖 33顯示了每個單元格的公式 圖 33(原書第 19頁 ) 、格式 圖 3— 4 中的模型是將數(shù)據(jù)輸入和公式計算混合在一起，因此我們的第一項工作就是對它的布局重新進(jìn)行設(shè)計。例如 B9=C3； ? 用一個輸入來更正所有的因素； ? 用不同的字體來做出區(qū)分。這個模型遵循了在圖 3— 5中顯示的布局。點擊“格式(O)”，“單元格 (E)”，“數(shù)字”來改變默認(rèn)的數(shù)字格式（見圖 36）。方格中用了多種顏色。你可以將你自定義的格式添加到格式框中也可以修改現(xiàn)有的格式（如圖 3— 7）。零是破折號。 會計格式類型： _* ,0_。_* _ 這樣做的效果是控制數(shù)字視圖中最多只有兩個小數(shù)點位。最好將“格式 (O)”保持可視，選擇“視圖 (V)”，“工具欄 (T)”，“格式”來顯示格式條（如圖 3— 8所示）。 圖 38(原書第 23頁 ) 圖 3— 9和圖 3— 10顯示的是如何通過應(yīng)用工具包中的邊框來突出單元格。 此資料來自 企業(yè) (), 大量管理資料下載 17 圖 39(原書第 24頁 ) 圖 310(原書第 24頁 ) 、顏色和圖案 顏色和圖案的使用也可以被用來幫助定義輸入和輸出。這些顏色的使用是個性化的，但重要的是保持多種顏色和格式的一致性使用。作者常常用藍(lán)色表示輸入，綠色或黑色表示總計，紅色或黑色表示計算結(jié)果（如圖 312所示）。 圖 311(原書第 25頁 ) 圖 312(原書第 25頁 ) 對模型添加幾種顏色之后，模型變得更加清晰，并且顏色的使用也迫使作者從一致性的角度考慮將輸入放在一起。 、數(shù)據(jù)的有效性 數(shù)據(jù)有效 性的設(shè)置使你可以設(shè)定單元格數(shù)據(jù)的界限。有效性的設(shè)置可以通過選擇主菜單欄的“數(shù)據(jù) (D)”，“有效性 (L)”來設(shè)置。若是錯誤的輸入，會彈出出錯警告的對話框。 圖 314(原書第 27頁 ) 出錯警告顯示的是你輸入了一個錯誤的數(shù)字，并且在你遵守了數(shù)據(jù)有效性條件后才能繼續(xù)（如圖 3— 15所示）。 圖 315(原書第 27頁 ) 由于每期的現(xiàn)金流量也符合同樣的有效性約束條件，你可以選擇“復(fù)制 (C)”，然后在“編輯 (E)”，“選擇性粘貼 (P)”，“有效性”來完成， 而不用再一次的鍵入有效性約束條件。這樣做是為了縮小輸入的范圍，也是為了讓用戶能得到正確的答案。 再次說明，這是從用戶的角度來看模型的使用，并試圖讓用戶知道什么是他要做的。在 Assess或 Visual Basic中都能找到類似的控制器。這樣的話我們就不可能利用數(shù)據(jù)的有效性來進(jìn)行處理，必須另覓他途。 圖 317(原書第 28頁 ) 第一步是先在工作表的底部開辟一塊工作區(qū)域，然后通過剪切和粘貼將貼現(xiàn)率輸入到區(qū)域中（如圖 3— 18 所示）。 圖 318(原書第 29頁 ) 工作區(qū)域 顯示了一個數(shù)值間隔，然后貼現(xiàn)率從 8%開始并且每次都按步長值增加。步長值并不是固定不變的，它取決于單元格 C26 的值。 組合框控制器中有八個可能的選擇，它返回你所選擇的序號。點擊“工具欄 (T)”中的“組合框”按鈕，你就可以在單元格中畫出組合框。在圖 320中我們可以看出，貼現(xiàn)率的取值是在區(qū)域 B28:B35，結(jié)果的輸出是在單元格 C27中。由于C28 用于計算，需要將它的顏色改成紅色以免混淆。 此資料來自 企業(yè) (), 大量管理資料下載 20 圖 321（原書第 31 頁 ） 通過這個函數(shù)，你可以先定義一個起始數(shù)值，然后向下 移動 X 行向左右移動 Y 列，最后得到另一個數(shù)值。最終得到控制器所需要的數(shù)值用作貼現(xiàn)率進(jìn)行現(xiàn)值的計算。特別需要說明的是，用戶仍然可以在 B2 C2 C27中輸入數(shù)據(jù)。 在工具欄中同樣還有其他的控制器能使你的輸入更直觀。 圖 323（原書第 32 頁 ） 工作表 Spinner_Scrollbar中應(yīng)用了這兩種控制器進(jìn)行了替換操作。這種操作會有一點點的復(fù)雜，原因是這個控制器沒有分?jǐn)?shù)。 在圖 324 中的滾動條的作用是取從 1 到 8的數(shù)值并且以一個單位遞增。 圖 324（原書第 33 頁 ) 、條件格式 條件格式可以使我們根據(jù)單元格的不同數(shù)值進(jìn)行不同的單元格顯示。在這個例子中，我們將會引入一個很有用的操作試驗來說明如果如何根據(jù)項目的成功與否來相應(yīng)地顯示結(jié)果。用格式按鈕來設(shè)置格式，使得當(dāng)現(xiàn)值大于或是等于單元格 C7的值時，顯示粉色。 圖 325（原書第 34 頁 ） 圖 326（原書第 34 頁 ） 你還可以通過點擊“添加 (I)”鍵，并且選擇“編輯 (E)”，“選擇性粘貼 (S)”，“格式”來復(fù)制，以便添加更多的格式?，F(xiàn)在，從單元格 C17到 H19這個區(qū)域中的單元格都已經(jīng)被直接賦值，這也就意味著可能會有 12處錯誤存在。這種利用 Excel的 解決方法比查貼現(xiàn)率表的方法要便利多了。函數(shù)被劃分成幾個部分以便查找。 圖 327（原書第 35 頁 ） 凈現(xiàn)值公式可將每期的現(xiàn)金流貼現(xiàn)。 =NPV(C25,D15:H15)+C15 根據(jù)這個公式可以得到貼現(xiàn)率為 %時正確的答案是 7,。你總是可以通過點擊問號標(biāo)示（如圖 3— 28所示）來獲取關(guān)于公式的幫助信息。 圖 328（原書第 36 頁 ） 、加載宏以便獲得更多的功能 對 Excel的典型安裝只包含了基本的函數(shù)公式。例如， NPV 函數(shù)假定每一期包含同樣的日數(shù)。（在第 19章中討論的估價模型就是用到了這一函數(shù)。選擇此選項并選擇“確定”進(jìn)行安裝。如果沒有“加載宏（ I） … ”選項，那么你就需要重裝一下 Excel了。 EDATE 函數(shù)是一個日期函數(shù)，它的功能是每次將事先確定的日期增加或減少若干個月。由于提前或推后的步長是可以變動的，因此在輸入?yún)^(qū)域中又有了一個新的控制。 再次輸入初始的現(xiàn)金流得到的結(jié)果是 7,，與原來的結(jié)果 7,。如果凈現(xiàn)值在底線之上，你就應(yīng)該建立一個標(biāo)簽來通知用戶。 ? 反饋計算后的凈現(xiàn)值。 Text 函數(shù)將數(shù)字轉(zhuǎn)化為文本并保留原來的格式?！庇脕磉B接文本串： =NPV at amp。為了減少代碼， IF中的文本陳述將由凈現(xiàn)值決定。IF(C20=C7,above,below)amp。TEXT(C7,0) 現(xiàn)在工作表能告訴用戶所用的貼現(xiàn)率并對結(jié)果進(jìn)行說明（見圖 3— 30）。 圖 330（原書第 38 頁 ） 此資料來自 企業(yè) (), 大量管理資料下載 23 、記錄版本序號、作者等 正如在前面的章節(jié)中提到的那樣，給模型備案是模型的一部分。在模型建立一段時間后，你可以比照并記錄不同版本之間的差異。另外，版本序號會出現(xiàn)在你打印的每一張工作表的頂部（見圖 3— 31）。將文字說明寫入模型自然是有益的，你也可以通過選擇“格式 (O)”，“ 工作表 (H)”，“隱藏 (H)”來隱藏一個工作表。就如上 圖 3— 31 中的 Version、 Author等，他們使你的模型更加標(biāo)準(zhǔn)，如：“ =Version”表示插入版本序號。 你可以在“插入 (I)”，“名稱 (N)”，“定義（ D）”來定義名稱，或者在 Excel 里用標(biāo)簽在選中的單 元格區(qū)域的一側(cè)建立多個名稱（見圖 3— 32），通過“插入 (