Excel財務(wù)管理建模與應(yīng)用（第2版）課件 第8、9章 全面預(yù)算編制、銷售預(yù)測與利潤規(guī)劃

8全面預(yù)算編制本章主要內(nèi)容銷售預(yù)算模型生產(chǎn)預(yù)算模型直接材料采購預(yù)算模型直接人工預(yù)算模型制造費用預(yù)算模型產(chǎn)品成本預(yù)算模型銷售費用預(yù)算模型管理費用預(yù)算模型財務(wù)費用預(yù)算模型2Excel會計應(yīng)用8.1全面預(yù)算業(yè)務(wù)場景分析8.1.1預(yù)算管理內(nèi)容（1）銷售預(yù)算是以企業(yè)經(jīng)營成果為核心，包括銷售預(yù)算、產(chǎn)品預(yù)算、產(chǎn)品銷售收入預(yù)算、銷售費用預(yù)算等。（2）生產(chǎn)預(yù)算包括生產(chǎn)預(yù)算、直接材料預(yù)算、直接人工預(yù)算、制造費用預(yù)算、產(chǎn)品成本預(yù)算等。（3）財務(wù)預(yù)算是關(guān)于資金籌措和使用的預(yù)算，包括長期的資本預(yù)算和短期的現(xiàn)金收支與信貸預(yù)算。（4）其他預(yù)算。3Excel會計應(yīng)用8.1.3預(yù)算管理的業(yè)務(wù)場景4Excel會計應(yīng)用8.2Excel業(yè)務(wù)預(yù)算編制8.2.1業(yè)務(wù)預(yù)算編制內(nèi)容分析（1）銷售預(yù)算。（2）生產(chǎn)預(yù)算。（3）直接材料預(yù)算。（4）直接人工預(yù)算。（5）制造費用預(yù)算。5Excel會計應(yīng)用6Excel會計應(yīng)用8.2.2銷售預(yù)算模型7Excel會計應(yīng)用8.2.2銷售預(yù)算模型8Excel會計應(yīng)用8.2.3生產(chǎn)預(yù)算模型9Excel會計應(yīng)用8.2.3生產(chǎn)預(yù)算模型8.2.4直接材料采購預(yù)算模型10Excel會計應(yīng)用8.2.4直接材料采購預(yù)算模型11Excel會計應(yīng)用8.2.4直接材料采購預(yù)算模型12Excel會計應(yīng)用8.2.4直接材料采購預(yù)算模型13Excel會計應(yīng)用凍結(jié)表格的行或列8.2.4直接材料采購預(yù)算模型14Excel會計應(yīng)用組合表格的行或列8.2.5直接人工預(yù)算模型15Excel會計應(yīng)用8.2.5直接人工預(yù)算模型16Excel會計應(yīng)用8.2.6制造費用預(yù)算模型17Excel會計應(yīng)用8.2.6制造費用預(yù)算模型18Excel會計應(yīng)用8.2.6制造費用預(yù)算模型19Excel會計應(yīng)用【公式】|【錯誤檢查】8.2.6制造費用預(yù)算模型20Excel會計應(yīng)用8.2.6制造費用預(yù)算模型追蹤錯誤21Excel會計應(yīng)用22Excel會計應(yīng)用8.3Excel財務(wù)預(yù)算編制8.3.1財務(wù)預(yù)算編制內(nèi)容分析（1）產(chǎn)品成本預(yù)算。（2）銷售及管理費用預(yù)算。（3）現(xiàn)金預(yù)算。（4）預(yù)計利潤表。23Excel會計應(yīng)用8.3.2產(chǎn)品成本預(yù)算模型24Excel會計應(yīng)用8.3.2產(chǎn)品成本預(yù)算模型25Excel會計應(yīng)用8.3.2產(chǎn)品成本預(yù)算模型顯示公式26Excel會計應(yīng)用8.3.2產(chǎn)品成本預(yù)算模型追蹤引用單元格27Excel會計應(yīng)用8.3.2產(chǎn)品成本預(yù)算模型追蹤從屬單元格28Excel會計應(yīng)用8.3.3銷售費用預(yù)算模型【例】接上例，公司在預(yù)算年度內(nèi)，預(yù)計銷售費用各項開支數(shù)額及計算如下：工資每季均為5000000元，業(yè)務(wù)費、運輸費、保管費、包裝費每季均為預(yù)計銷售額的0.1%，第二季度廣告費開支144000元。編制銷售費用預(yù)算表。29Excel會計應(yīng)用8.3.3銷售費用預(yù)算模型單元格公式注釋C5=銷售預(yù)算!$E$7*0.1%業(yè)務(wù)費C7=銷售預(yù)算!$E$7*0.1%包裝費C8=銷售預(yù)算!$E$7*0.1%保管費C9=銷售預(yù)算!$E$7*0.1%運輸費C10=SUM(C4:C9)合計30Excel會計應(yīng)用8.3.4管理費用預(yù)算模型【例】接上例，公司在預(yù)算年度內(nèi)，預(yù)計管理費用各項開支數(shù)額如下：工資8000000元，折舊費300000元，辦公費180000元，差旅費300000元，物料消耗50000元，無形資產(chǎn)攤銷50000，工會經(jīng)費120000元，印花稅1000元，其他15000元。編制管理費用預(yù)算表。31Excel會計應(yīng)用8.3.4管理費用預(yù)算模型32Excel會計應(yīng)用8.3.4管理費用預(yù)算模型【公式】|【監(jiān)視窗口】33Excel會計應(yīng)用8.3.4管理費用預(yù)算模型【公式】|【監(jiān)視窗口】34Excel會計應(yīng)用8.3.5財務(wù)費用預(yù)算模型【例】接上例，公司在預(yù)算年度內(nèi)，預(yù)計財務(wù)費用各項開支數(shù)額如下：利息支出3000000元，利息收入2800000元，匯兌損失40000元，匯兌收益36000元，手續(xù)費4000元，其他200000元。編制財務(wù)費用預(yù)算表。35Excel會計應(yīng)用8.3.5財務(wù)費用預(yù)算模型36Excel會計應(yīng)用8.3.6現(xiàn)金預(yù)算模型【例】接上例，公司在預(yù)算年度內(nèi)，其他相關(guān)資料如下：預(yù)計預(yù)算期第四季度營業(yè)外收入為76000元；營業(yè)外支出第一季度為7600元，第二季度為18000元，第四季度為15600元。根據(jù)資本預(yù)算，該公司計劃預(yù)算期第二季度以分期付款方式購入一套設(shè)備，價值817000元，該季度付款550000元，第三季度付款167000元，其余第四季度支付。預(yù)計各季度支付所得稅2175000元，銷售稅金736000元，第二、四季度各支付股利400000元。假設(shè)該公司2018年末，資產(chǎn)負(fù)債表所示貨幣資金余額為100000元。編制現(xiàn)金預(yù)算表。37Excel會計應(yīng)用8.3.6現(xiàn)金預(yù)算模型38Excel會計應(yīng)用8.3.6現(xiàn)金預(yù)算模型【公式】|【顯示公式】39Excel會計應(yīng)用8.3.6現(xiàn)金預(yù)算模型條件格式40Excel會計應(yīng)用8.3.6現(xiàn)金預(yù)算模型修改條件格式規(guī)則41Excel會計應(yīng)用8.3.6現(xiàn)金預(yù)算模型最終效果9銷售預(yù)測與

利潤規(guī)劃本章主要內(nèi)容預(yù)測函數(shù)：FORECAST()、TREND()、GROWTH()、LINEST()和LOGEST()銷售分布數(shù)據(jù)地圖銷售收入預(yù)測模型銷售收入趨勢預(yù)測模型規(guī)劃求解的方法規(guī)劃求解模型43Excel會計應(yīng)用9.1銷售預(yù)測與利潤規(guī)劃業(yè)務(wù)場景分析9.1.1銷售預(yù)測1、定性預(yù)測法2、調(diào)查分析法3、趨勢分析法：移動平均法、指數(shù)平滑法和季節(jié)預(yù)測法4、因果分析法44Excel會計應(yīng)用9.1.2利潤規(guī)劃目標(biāo)利潤=預(yù)計收入－標(biāo)準(zhǔn)成本在利潤規(guī)劃的本、量、利分析中，存在著盈虧平衡點。在該點上，企業(yè)的總成本和總收入相等而出現(xiàn)不盈不虧的現(xiàn)象。45Excel會計應(yīng)用9.1.3銷售情況分析企業(yè)產(chǎn)品銷售情況對企業(yè)是至關(guān)重要的，因此除了了解企業(yè)的銷售總額外，常常還要對企業(yè)產(chǎn)品銷售的分布情況進(jìn)行統(tǒng)計分析，也就是對不同產(chǎn)品在不同地區(qū)的銷售情況進(jìn)行分析，從中尋找銷售不利的原因和進(jìn)一步擴(kuò)大市場份額的途徑。46Excel會計應(yīng)用9.2Excel企業(yè)銷售情況分析9.2.1創(chuàng)建銷售分布數(shù)據(jù)地圖47Excel會計應(yīng)用48Excel會計應(yīng)用9.3Excel銷售收入預(yù)測9.3.1銷售收入預(yù)測模型【例】已知2009年到2018年公司的銷售收入如圖所示，試預(yù)測該公司2019年的銷售收入。49Excel會計應(yīng)用9.3.1銷售收入預(yù)測模型安裝【數(shù)據(jù)分析工具】50Excel會計應(yīng)用9.3.1銷售收入預(yù)測模型計算移動平均數(shù)9.3.1銷售收入預(yù)測模型一次平均和二次平均51Excel會計應(yīng)用9.3.1銷售收入預(yù)測模型輸出圖表52Excel會計應(yīng)用9.3.2預(yù)測函數(shù)1．TREND函數(shù)語法：TREND(known_y's,known_x's,new_x's,const)功能：TREND()使用最小二乘法計算對已知數(shù)據(jù)進(jìn)行最佳直線擬和，并返回描述此直線的數(shù)組。即找到適合給定的數(shù)組known_y's和known_x's的直線并返回指定數(shù)組new_x's值在直線上對應(yīng)的y值。53Excel會計應(yīng)用9.3.2預(yù)測函數(shù)54Excel會計應(yīng)用【例】已知2009—2018年的銷售收入，如圖所示。試用TREND函數(shù)預(yù)測該公司2019年的銷售收入。9.3.2預(yù)測函數(shù)55Excel會計應(yīng)用2．FORECAST.LINEAR函數(shù)語法：FORECAST.LINEAR(x,known_y's,known_x's)功能：已知x值和y值，利用線性回歸對未來值進(jìn)行預(yù)測?？梢允褂迷摵瘮?shù)對未來銷售額、庫存需求或消費趨勢進(jìn)行預(yù)測。9.3.2預(yù)測函數(shù)56Excel會計應(yīng)用【例】采用前面的資料，試用FORECAST.LINEAR函數(shù)預(yù)測該公司2019年的銷售收入。9.3.2預(yù)測函數(shù)57Excel會計應(yīng)用3．GROWTH函數(shù)語法：GROWTH(known_y's,known_x's,new_x's,const)功能：GROWTH工作表函數(shù)用通過擬合滿足現(xiàn)有x值和y值的指數(shù)曲線預(yù)測未來值。9.3.2預(yù)測函數(shù)58Excel會計應(yīng)用【例】采用前面的資料，試用GROWTH函數(shù)預(yù)測該公司2019年的銷售收入。9.3.2預(yù)測函數(shù)59Excel會計應(yīng)用4．LINEST函數(shù)語法：LINEST(known_y's,known_x's,const,stats)功能：使用最小二乘法對已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返回描述此直線的數(shù)組。因為此函數(shù)返回數(shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。9.3.2預(yù)測函數(shù)60Excel會計應(yīng)用5．LOGEST函數(shù)語法：LOGEST(known_y's,known_x's,const,stats)功能：在回歸分析中，計算最符合數(shù)據(jù)的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)值數(shù)組。此函數(shù)返回數(shù)值數(shù)組，必須以數(shù)組公式的形式輸入。9.3.3銷售收入趨勢預(yù)測模型61Excel會計應(yīng)用【例】采用前面的資料，公司管理層希望得到一張圖表以幫助預(yù)測和規(guī)劃公司未來三年的銷售收入狀況。1.建立銷售收入圖表9.3.3銷售收入趨勢預(yù)測模型2.選擇圖表布局和圖表樣式62Excel會計應(yīng)用9.3.3銷售收入趨勢預(yù)測模型3.添加銷售收入趨勢線63Excel會計應(yīng)用9.3.4因果分析64Excel會計應(yīng)用1.單變量求解【例】現(xiàn)在已知某公司2018年利潤表，如圖所示。分析在現(xiàn)有的水平下，將利潤總額提高到5000000000元，相應(yīng)的營業(yè)收入要增加多少?9.3.4因果分析65Excel會計應(yīng)用1.單變量求解9.3.4因果分析66Excel會計應(yīng)用2.單變量模擬運算表【例】仍然引用q前一例，要求得到不同營業(yè)收入下的營業(yè)利潤。9.3.4因果分析輸入引用列的單元格模擬運算結(jié)果67Excel會計應(yīng)用9.3.4因果分析68Excel會計應(yīng)用3.雙變量模擬運算表【例】仍然引用前一例子采用的模擬數(shù)據(jù)，分析不同營業(yè)收入和不同投資收益的影響。9.3.4因果分析69Excel會計應(yīng)用輸入引用行的單元格、輸入引用列的單元格9.3.4因果分析70Excel會計應(yīng)用輸入引用行的單元格、輸入引用列的單元格9.4Excel利潤規(guī)劃71Excel會計應(yīng)用9.4.1分析規(guī)劃求解問題72Excel會計應(yīng)用生產(chǎn)條件列表9.4.1分析規(guī)劃求解問題73Excel會計應(yīng)用建立生產(chǎn)規(guī)劃模型9.4.1分析規(guī)劃求解問題74Excel會計應(yīng)用在Excel中建立生產(chǎn)規(guī)劃基礎(chǔ)模型9.4.2建立規(guī)劃求解模型75Excel會計應(yīng)用規(guī)劃求解參數(shù)9.4.2建立規(guī)