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

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

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