項(xiàng)目十八 制作產(chǎn)品銷售明細(xì)表

制作產(chǎn)品銷售明細(xì)表Office辦公軟件實(shí)用案例教程項(xiàng)目十八預(yù)測分析的方法主要有兩種，即定量預(yù)測法和定性預(yù)測法。定量預(yù)測法是在掌握與預(yù)測與對象有關(guān)的各種要素的定量資料的基礎(chǔ)上，運(yùn)用現(xiàn)代數(shù)學(xué)方法進(jìn)行數(shù)據(jù)處理，據(jù)以建立能夠反映有關(guān)變量之間規(guī)律聯(lián)系的各類預(yù)測方法體系，它可分為趨勢外推分析法和因果分析法；定性預(yù)測法是指由有關(guān)方面的專業(yè)人員或?qū)＜腋鶕?jù)自己的經(jīng)驗(yàn)和知識，結(jié)合預(yù)測對象的特點(diǎn)進(jìn)行分析，對事物的末來狀況和發(fā)展趨勢作出推測的預(yù)測方法，圖18.1就是用Excel軟件制作的產(chǎn)品銷售明細(xì)表。項(xiàng)目簡介知識點(diǎn)導(dǎo)入1．單元格區(qū)域命名：選中單元格區(qū)域，在名稱框輸入命名；2．?dāng)?shù)據(jù)分析：單擊【數(shù)據(jù)】→【分析】組中的

按鈕，打開“數(shù)據(jù)分析”對話框，在列框中選擇“回歸”選項(xiàng)，單擊“確定”按鈕。任務(wù)實(shí)施樣張圖18.1產(chǎn)品銷售明細(xì)表重點(diǎn)步驟1．啟動Excel2010，新建空白工作。2．將新建的文檔保存在桌面上，文件名為“產(chǎn)品銷售明細(xì)表.XLSX”。1．新建工作表新建工作表，并依次命名為個工作表的名稱為“明細(xì)”、“銷售預(yù)測”、“利潤預(yù)測”、“成本預(yù)測”，如圖18.2所示。

圖18.2新建四個工作表重點(diǎn)步驟2．錄入明細(xì)工作表（1）切換到“明細(xì)”工作表，輸入標(biāo)題、項(xiàng)目、基本數(shù)據(jù)，效果如圖18.3所示。

圖18.3輸入基本數(shù)據(jù)重點(diǎn)步驟（2）計(jì)算銷售額公式為：“銷售額=銷售量*售價(jià)”（3）計(jì)算銷售成本公式為：“銷售成本=銷售額*0.8”（4）計(jì)算實(shí)現(xiàn)利潤公式為：“實(shí)現(xiàn)利潤=銷售額-銷售成本”，如圖18.4所示。

圖18.4計(jì)算銷售額、銷售成本、實(shí)現(xiàn)利潤重點(diǎn)步驟（5）命名單元格區(qū)域①選中A3:A14單元格區(qū)域，在名稱框輸入月份如圖18.5所示；②按相同的方法為其他單元格區(qū)域命名。3．預(yù)測銷售數(shù)據(jù)（1）切換到“銷售預(yù)測”工作表，在A1:B14單元格內(nèi)創(chuàng)建框架數(shù)據(jù)，如圖18.6所示；

圖18.6框架圖18.5對A3:A14單元格區(qū)重點(diǎn)步驟（2）月份引用選中A3:A14單元格區(qū)域，在編輯欄中輸入“=月分”，按【Ctrl+Enter】組合鍵引用“明細(xì)”工作表中的數(shù)據(jù)。（3）預(yù)測“銷售額”①選中B3:B14單元格區(qū)域，在編輯欄中輸入“=銷售額”，按【Ctrl+Enter】組合鍵引用“明細(xì)”工作表中的數(shù)據(jù)；②單擊【數(shù)據(jù)】→【分析】組中的按鈕，打開“數(shù)據(jù)分析”對話框，在列框中選擇“回歸”選項(xiàng)，單擊“確定”按鈕，如圖18.7所示；

圖18.7“回歸”對話框重點(diǎn)步驟③在Y值輸入?yún)^(qū)域和X值輸入?yún)^(qū)域分別“$B$2:$B$14”和“$A$2:$A$14”,單擊選中“輸出區(qū)域”，將輸出區(qū)域指定為“$D$2”,然后分別單擊選中“標(biāo)志”、“殘差”、“線性擬合圖”復(fù)選框，最后單擊“確定”按鈕，參數(shù)如圖18.8所示；

圖18.8參數(shù)設(shè)置重點(diǎn)步驟④此時將顯示回歸數(shù)據(jù)分析工具根據(jù)提供的單元格區(qū)域的數(shù)據(jù)，得到的預(yù)測結(jié)果，其中還配以散點(diǎn)圖來直觀地顯示數(shù)據(jù)趨勢。通過圖表以及工作中的“SUMMARYOUTPUT”欄下的數(shù)據(jù)便可查看預(yù)測的銷售數(shù)據(jù)及趨勢，如圖18.9所示。

圖18.9預(yù)測結(jié)果重要步驟4．預(yù)測利潤數(shù)據(jù)

（1）建立表格框架數(shù)據(jù)切換到“利潤預(yù)測”工作表，在A1:C8單元格中建立表格框架數(shù)據(jù)，然后在C8單元格中輸入目標(biāo)利潤數(shù)據(jù)。（2）計(jì)算銷售量選中B3單元格，在編輯欄中輸入“=SUM(銷售量)”后按【回車】鍵，表示計(jì)算名稱為“銷售量”的單元格所有數(shù)據(jù)之和，如圖18.10所示。

圖18.10所示計(jì)算實(shí)際銷售量重要步驟（3）計(jì)算售價(jià)選中B4單元格，在編輯欄中輸入“=AVERAGE(售價(jià))”后按【回車】鍵，表示計(jì)算名稱為“售價(jià)”的單元格所有數(shù)據(jù)的平均值，如圖18.11所示。

圖18.11計(jì)算實(shí)際售價(jià)重要步驟（4）計(jì)算變動范圍選中B5單元格，在編輯欄中輸入“=AVERAGE(MAX(售價(jià))-B4,B4-MIN(售價(jià)))”后按【回車】鍵，表示計(jì)算名稱為“售價(jià)”的單元格所有數(shù)據(jù)的平均值，如圖18.12所示。

圖18.12計(jì)算變動范圍重要步驟5）計(jì)算固定成本選中B6單元格，在編輯欄中輸入“=SUM(銷售成本)”后按【回車】鍵，如圖18.13所示。

圖18.13計(jì)算固定成本重要步驟（6）計(jì)算目前利潤選中B7單元格，在編輯欄中輸入“=SUM(實(shí)現(xiàn)利潤)”后按【回車】鍵，如圖18.14所示。

圖18.14計(jì)算目前利潤重要步驟（7）計(jì)算預(yù)測銷售量選中C3單元格，在編輯欄中輸入“=(C8+B6)/(B4-B5)”后按【回車】鍵，如圖18.15所示。

圖18.15預(yù)測銷售量重要步驟（8）預(yù)測售價(jià)選中C4單元格，在編輯欄中輸入“=(C8+B6)/B3”后按【回車】鍵，如圖18.16所示。

圖18.16預(yù)測售價(jià)重要步驟（9）預(yù)測固定成本選中C6單元格，在編輯欄中輸入“=B3*（B4-B5）-C8”后按【回車】鍵，如圖18.17所示；

圖18.17預(yù)測固定成本重要步驟5.預(yù)測成本數(shù)據(jù)（1）創(chuàng)建框架數(shù)據(jù)切換到“成本預(yù)測”工作表，創(chuàng)建框架數(shù)據(jù),如圖18.18所示。

圖18.18預(yù)測成本數(shù)據(jù)框架重要步驟（2）引用數(shù)據(jù)引用月份、銷售量和銷售成本數(shù)據(jù)，如圖18.19所示。

圖18.19引用的數(shù)據(jù)重要步驟（3）預(yù)測成本單擊【數(shù)據(jù)】→【分析】組中的

按鈕，打開“數(shù)據(jù)分析”對話框，在列框中選擇“回歸”選項(xiàng)，單擊“確定”按鈕，將Y值輸入?yún)^(qū)域和X值輸入?yún)^(qū)域分別“$C$2:$C$14”和“$B$2:$B$14”,單擊選中“輸出區(qū)域”單選項(xiàng)，將輸出區(qū)域指定為“$E$2”，最后單擊【確定】按鈕，得到預(yù)測結(jié)果，如圖18.20所示。

圖18.20預(yù)測成本重要步驟1.

明細(xì)工作表選擇A1:F1單元格區(qū)域，選擇【開始】→【對齊方式】→【合并后居中】。2.設(shè)置標(biāo)題文字單擊標(biāo)題，選擇【開始】→【字體】，字體選擇【宋體】，字號選擇【20】，字形選擇【加粗】，顏色選擇【藍(lán)色】。3.

設(shè)置單元格底紋選中A2:K2,單擊鼠標(biāo)右鍵→【設(shè)置單元格格式】→【填充】命令，彈出如圖18.21所示的【填充】對話框中選擇黃色，如圖18.22所示。圖18.21“填充”對話框重要步驟圖18.22對明細(xì)工作表設(shè)置4.設(shè)置其他工作表按照“明細(xì)”工作表的設(shè)置方法對另外三個工作表進(jìn)行設(shè)置。拓展項(xiàng)目制作投資公方案表樣張圖18.23投資方案表重點(diǎn)步驟1.新建并保存“投資方案表.xlsx”,輸入并美化信貸方案的框架數(shù)據(jù)。2.輸入相關(guān)內(nèi)容，如圖18.24所示。

圖18.24基本數(shù)據(jù)重點(diǎn)步驟3．選中E3:E6單元格區(qū)域，在編輯欄中輸入“=PMT(D3,C3,-B3)，按【Ctrl+Enter】組合鍵計(jì)算每年還款額；4．選中F3:F6單元格區(qū)域，在編輯欄中輸入“=PMT(D3/4,C3*4,-B3)，按【Ctrl+Enter】組合鍵計(jì)算每季度還款額；5.選中G3:G6單元格區(qū)域，在編輯欄中輸入“=PMT(D3/12,C3*12,-B3)，按【Ctrl+Enter】組合鍵計(jì)算每月還款額，如圖18.25所示；

圖18.25還款額課后練習(xí)1．制作成本預(yù)測趨勢圖，效果如圖18.26所示。

圖18.26成本預(yù)測趨勢圖