Excel財(cái)務(wù)數(shù)據(jù)模型與模板瘋狂實(shí)戰(zhàn)

【課程大綱】

第一篇Excel財(cái)務(wù)建模之理論基礎(chǔ)

1.  Excel財(cái)務(wù)建模的概念與方法論

2.  Excel財(cái)務(wù)模型的結(jié)構(gòu)設(shè)計(jì)

2.1 數(shù)據(jù)輸入?yún)^(qū)、數(shù)據(jù)計(jì)算區(qū)和數(shù)據(jù)輸出區(qū)

2.2 輸入?yún)^(qū)：輸入模型需要的各種假設(shè)條件

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

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

3.  Excel財(cái)務(wù)模型的調(diào)整與持續(xù)優(yōu)化

第二篇Excel財(cái)務(wù)建模必備的技術(shù)修煉

1.  經(jīng)典常用的函數(shù)是財(cái)務(wù)建模的靈魂

1.1 IF、IFERROR、CHOOSE等條件選擇或條件糾錯(cuò)函數(shù)

1.2 VLOOKUP、HLOOKUP、LOOKUP等查詢函數(shù)

1.3 SUMIF、SUMIFS、COUNTIF、COUNTIFS及SUMPRODUCT等求和計(jì)數(shù)函數(shù)

1.4 INDEX、OFFSET、INDIRECT及MATCH等引用偏移、引用轉(zhuǎn)換、定位函數(shù)

1.5 TEXT文本格式轉(zhuǎn)換函數(shù)，還有一些新函數(shù)如XLOOKUP、FILTER函數(shù)等

2.  定義名稱技術(shù)讓財(cái)務(wù)模型簡約且易于維護(hù)

3.  交互觸發(fā)技術(shù)讓財(cái)務(wù)模型更智能更自動(dòng)化

3.1 表單控件技術(shù)與ActiveX控件技術(shù)

3.2 數(shù)據(jù)驗(yàn)證（有效性）提供下拉列表

4.  條件格式讓財(cái)務(wù)模型的格式顯示呈現(xiàn)動(dòng)態(tài)效果

5.  數(shù)據(jù)透視表、數(shù)據(jù)透視圖與切片器給財(cái)務(wù)模型的智能化提供巨大想象空間

6.  PowerQuery及PowerBI模型，為Excel財(cái)務(wù)模型打開無限廣闊的世界

7.  數(shù)據(jù)隱身與數(shù)據(jù)保護(hù)讓財(cái)務(wù)模型的呈現(xiàn)與安全增添保障

第三篇構(gòu)建經(jīng)典的財(cái)務(wù)數(shù)據(jù)模板或模型

模型1年度資金預(yù)算模型

1) 表單控件“滾動(dòng)條”的插入與設(shè)置

2) 圖表的輔助區(qū)域設(shè)置及坐標(biāo)軸標(biāo)簽的公式設(shè)計(jì)

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

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

模型2用模擬運(yùn)算表制作利潤敏感度分析模型

1) 單因素敏感度分析，用模擬運(yùn)算表展現(xiàn)單價(jià)的變動(dòng)

2) 插入表單控件“微調(diào)框”并設(shè)置，精細(xì)調(diào)節(jié)固定成本與變動(dòng)成本

3) 雙因素敏感度分析，用模擬運(yùn)算表同時(shí)模擬單價(jià)和銷量的變動(dòng)

4) 用條件格式根據(jù)單元格值展現(xiàn)不同的填充顏色

模型3運(yùn)輸費(fèi)用或網(wǎng)點(diǎn)布局測算模型

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

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

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

1) 插入表單控件“微調(diào)框”并設(shè)置，對相關(guān)變量進(jìn)行微調(diào)

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

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

模型5財(cái)務(wù)報(bào)表自動(dòng)化模板

1) 使用宏表函數(shù)批量制作各個(gè)分表的超鏈接

2) 使用透視表和VLOOKUP函數(shù)等實(shí)現(xiàn)財(cái)務(wù)報(bào)表的動(dòng)態(tài)匯總

3) 運(yùn)用SUMIF函數(shù)匯總得到現(xiàn)金流量表

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

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

2) 借助INDIRECT函數(shù)與滾動(dòng)條控件構(gòu)建杜邦分析結(jié)構(gòu)圖

3) 利用雙餅圖組合圖表設(shè)計(jì)凈利潤儀表盤和權(quán)益凈利率儀表盤

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

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

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

3) 運(yùn)用INDEX、SUMIF及INDIRECT等函數(shù)對多表按部門或科目進(jìn)行匯總

模型8產(chǎn)品進(jìn)銷存Excel管理系統(tǒng)

1) 運(yùn)用形狀、圖表、超鏈接等功能制作導(dǎo)航主頁

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

3) 利用透視表和經(jīng)典的查詢公式、求和公式及條件格式等實(shí)現(xiàn)出入庫動(dòng)態(tài)統(tǒng)計(jì)

4) 運(yùn)用數(shù)組公式實(shí)現(xiàn)一對多查詢，實(shí)時(shí)統(tǒng)計(jì)需要緊急采購的產(chǎn)品信息

5) 使用數(shù)據(jù)驗(yàn)證及各種經(jīng)典函數(shù)公式對產(chǎn)品的進(jìn)銷存情況進(jìn)行多條件的動(dòng)態(tài)查詢

模型9應(yīng)收賬款管理系統(tǒng)

1) 應(yīng)收結(jié)余的實(shí)時(shí)計(jì)算及應(yīng)收賬款匯總，竟然只是用了SUMIF函數(shù)

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

3) 動(dòng)態(tài)生成往來對賬函夢寐以求，此刻已經(jīng)絲滑般成為現(xiàn)實(shí)

模型10供應(yīng)商管理系統(tǒng)

1) 供應(yīng)商查詢、供應(yīng)商評級與評級雷達(dá)圖，背后都有經(jīng)典函數(shù)的影子

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

3) 供應(yīng)商余額統(tǒng)計(jì)匯總，耳熟能詳?shù)膸状笄蠛陀?jì)數(shù)函數(shù)主力悉數(shù)閃耀登場

李承高老師的其它課程