Excel財務數據模型與模板瘋狂實戰(zhàn)

【課程大綱】

第一篇Excel財務建模之理論基礎

1.  Excel財務建模的概念與方法論

2.  Excel財務模型的結構設計

2.1 數據輸入區(qū)、數據計算區(qū)和數據輸出區(qū)

2.2 輸入區(qū)：輸入模型需要的各種假設條件

2.3 計算區(qū)：完成各種數據計算的區(qū)域

2.4 輸出區(qū)：集中展示模型的各種假設條件和計算結果，一般包括報表與圖表

3.  Excel財務模型的調整與持續(xù)優(yōu)化

第二篇Excel財務建模必備的技術修煉

1.  經典常用的函數是財務建模的靈魂

1.1 IF、IFERROR、CHOOSE等條件選擇或條件糾錯函數

1.2 VLOOKUP、HLOOKUP、LOOKUP等查詢函數

1.3 SUMIF、SUMIFS、COUNTIF、COUNTIFS及SUMPRODUCT等求和計數函數

1.4 INDEX、OFFSET、INDIRECT及MATCH等引用偏移、引用轉換、定位函數

1.5 TEXT文本格式轉換函數，還有一些新函數如XLOOKUP、FILTER函數等

2.  定義名稱技術讓財務模型簡約且易于維護

3.  交互觸發(fā)技術讓財務模型更智能更自動化

3.1 表單控件技術與ActiveX控件技術

3.2 數據驗證（有效性）提供下拉列表

4.  條件格式讓財務模型的格式顯示呈現動態(tài)效果

5.  數據透視表、數據透視圖與切片器給財務模型的智能化提供巨大想象空間

6.  PowerQuery及PowerBI模型，為Excel財務模型打開無限廣闊的世界

7.  數據隱身與數據保護讓財務模型的呈現與安全增添保障

第三篇構建經典的財務數據模板或模型

模型1年度資金預算模型

1) 表單控件“滾動條”的插入與設置

2) 圖表的輔助區(qū)域設置及坐標軸標簽的公式設計

3) 利用柱形圖的漲跌柱線展示資金的收入與支出

4) 把文本框與動態(tài)變化的單元格用公式連接起來

模型2用模擬運算表制作利潤敏感度分析模型

1) 單因素敏感度分析，用模擬運算表展現單價的變動

2) 插入表單控件“微調框”并設置，精細調節(jié)固定成本與變動成本

3) 雙因素敏感度分析，用模擬運算表同時模擬單價和銷量的變動

4) 用條件格式根據單元格值展現不同的填充顏色

模型3運輸費用或網點布局測算模型

1) 隱秘功能“規(guī)劃求解”，幫你求解最小運輸費用

2) 隱秘功能“規(guī)劃求解”，助你進行網點布局決策分析

模型4材料自制或外購決策模型

1) 插入表單控件“微調框”并設置，對相關變量進行微調

2) 制作多個用于繪圖的輔助數據區(qū)域

3) 用帶直線的散點圖和普通散點圖制作更直觀的動態(tài)決策分析圖表

模型5財務報表自動化模板

1) 使用宏表函數批量制作各個分表的超鏈接

2) 使用透視表和VLOOKUP函數等實現財務報表的動態(tài)匯總

3) 運用SUMIF函數匯總得到現金流量表

模型6動態(tài)杜邦分析模型

1) 用MATCH函數制作圖表所需的輔助區(qū)域

2) 借助INDIRECT函數與滾動條控件構建杜邦分析結構圖

3) 利用雙餅圖組合圖表設計凈利潤儀表盤和權益凈利率儀表盤

模型7基于管理費用科目余額表的年度匯總分析模板

1) 對12個月份的原始表格批量用函數公式增添若干輔助列

2) 插入表單控件“選項”按鈕與ActiveX控件“組合框”

3) 運用INDEX、SUMIF及INDIRECT等函數對多表按部門或科目進行匯總

模型8產品進銷存Excel管理系統

1) 運用形狀、圖表、超鏈接等功能制作導航主頁

2) 利用下拉框表單控件、切片器功能及查詢函數制作供貨商、產品列表

3) 利用透視表和經典的查詢公式、求和公式及條件格式等實現出入庫動態(tài)統計

4) 運用數組公式實現一對多查詢，實時統計需要緊急采購的產品信息

5) 使用數據驗證及各種經典函數公式對產品的進銷存情況進行多條件的動態(tài)查詢

模型9應收賬款管理系統

1) 應收結余的實時計算及應收賬款匯總，竟然只是用了SUMIF函數

2) 這種應收賬齡分析表真的很燒腦，SUMPRODUCT函數無處不在且千頭萬緒

3) 動態(tài)生成往來對賬函夢寐以求，此刻已經絲滑般成為現實

模型10供應商管理系統

1) 供應商查詢、供應商評級與評級雷達圖，背后都有經典函數的影子

2) 付款提醒、發(fā)票查詢、付款查詢與對賬單，被這幾個函數收拾得服服帖帖

3) 供應商余額統計匯總，耳熟能詳的幾大求和計數函數主力悉數閃耀登場

李承高老師的其它課程