1、Office職場應用書庫 Excel 2019Excel 2019函數圖表函數圖表入門與實戰體驗入門與實戰體驗在產品銷售本量利分析中應用 盈虧平衡銷售量預測分析 不同單位售價對利潤的影響 分析單位可變成本對利潤的影響 利用模擬運算計算不同單位售價保本點 單位利潤的計算盈虧平衡銷售量預測分析 單一產品保本點預測 1本、量、利分析法 2貢獻毛利分析法 多產品保本點預測 1利用加權平均法進行多產品保本點的預測 2利用貢獻毛利分解法進行多產品保本點的預測 3利用貢獻毛利分解法計算利潤額 4保本點預測中應注意的問題貢獻毛利分析法具體的計算操作步驟如下：步驟1：根據所給資料在“銷售公司本量利分析工作簿中創

2、建一個“用貢獻毛利法計算保本點工作表，將生產成本和銷售單價記錄到工作表中，如圖5-5所示。步驟2：計算單位貢獻毛利單位貢獻毛利=銷售單價單位變動成本）。在“用貢獻毛利法計算保本點工作表中的C16單元格中輸入公式“=C19-C15”，即可計算出單位貢獻毛利，如圖5-6所示。貢獻毛利分析法具體的計算操作步驟如下：步驟3：計算單位貢獻毛利率貢獻毛利率=單位貢獻毛利/銷售單價）。在“用貢獻毛利法計算保本點工作表的C17單元格中輸入公式“=C16/C19”，即可計算出貢獻毛利率，如圖5-7所示。步驟4：計算保本銷售量保本銷售量=固定成本總額/單位貢獻毛利）。在“用貢獻毛利法計算保本點工作表的C20單元格

3、中輸入公式“=C18/C16”，即可計算出保本銷售量，如圖5-8所示。貢獻毛利分析法具體的計算操作步驟如下：步驟5：計算保本銷售額保本銷售額=固定成本總額/單位貢獻毛利率）。在“用貢獻毛利法計算保本點工作表的C21單元格中輸入公式“=C18/C17”，即可計算出保本銷售額，如圖5-9所示。不同單位售價對利潤的影響 建立基本計算表 1計算指定銷售單價時盈虧平衡點及不同銷量所對應的利潤額 2添加滾動條控制單位售價 建立本量利分析圖添加滾動條控制單位售價添加滾動條以控制單位售價的具體操作步驟如下：步驟1：添加“開發工具主選項卡。在Excel工作界面中選擇【文件】主選項卡，即可進入【文件】窗口，如圖5

4、-47所示。步驟2：單擊【選項】按鈕，即可打開【Excel選項】對話框，在其中選擇【自定義功能區】選項，再右側自定義功能區的“主選項卡設置窗格中勾選“開發工具復選框，如圖5-48所示。添加滾動條控制單位售價添加滾動條以控制單位售價的具體操作步驟如下：步驟3：單擊【確定】按鈕，即可將該主選項卡添加到Excel工作界面中。單擊【插入】按鈕下的三角按鈕，即可打開添加的“控件工具箱”，如圖5-49所示。步驟4：繪制文本框用于顯示當前的售價。在“插入主選項卡中單擊【文本框】下的三角按鈕，即可打開該下拉菜單，在其中選擇“橫排文本框選項，在工作表中繪制一個橫排文本框，在其中輸入“單位售價文字，如圖5-50所

5、示。添加滾動條控制單位售價添加滾動條以控制單位售價的具體操作步驟如下：步驟5：再復制一個相同的文本框并設置與B5單元格鏈接，這樣“單位售價就可以實時變化了，如圖5-51所示。步驟6：繪制“滾動條并設置滾動條的格式。在“插入控件按鈕下單擊“滾動條按鈕，在“單位售價橫排文本框下方繪制一個滾動條，如圖5-52所示。添加滾動條控制單位售價添加滾動條以控制單位售價的具體操作步驟如下：步驟7：在繪制完畢后，從右鍵彈出菜單中選擇“設置控件格式選項，即可打開【設置控件格式】對話框，如圖5-53所示。選擇“控制選項卡，在其中設置控件的當前值、最小值、最大值、步長、頁步長等值，這些值都在130000之間的整數，如

6、圖5-54所示。添加滾動條控制單位售價添加滾動條以控制單位售價的具體操作步驟如下：步驟8：選擇控件鏈接單元格。在【設置控件格式】對話框中單擊“單元格鏈接文本框后面的 按鈕，即可激活【設置控件格式】對話框，在工作表中選擇B5單元格，如圖5-55所示。選擇完畢后，按下Enter鍵，即可將滾動條的單元格鏈接設置為B5，如圖5-56所示。添加滾動條控制單位售價添加滾動條以控制單位售價的具體操作步驟如下：步驟9：在對滾動條的格式設置完畢后，單擊【確定】按鈕返回到工作表，通過拖動滾動條或單擊左右側按鈕，即可以0.1為步長更改單位售價，如圖5-57所示。在拖動滾動條時，不同單位售價下，Excel會自動計算出

7、盈虧平衡銷量與各個不同銷量所對應的收入額與利潤額，如圖5-58所示。單位可變成本對利潤的影響 建立基本計算表 建立盈虧平衡圖表利用模擬運算計算不同單位售價保本點 求解不同售價下的保本點 建立圖標直觀顯示盈虧平衡銷售量、盈虧平衡銷售額與單位售價單位利潤的計算 求解不同售價下的保本點 不同單位可變成本、單位售價對單位收入的影響專家課堂常見問題與解答）點撥點撥1 1：在分析不同單位售價對利潤的影響時，為什么不可以直接設置：在分析不同單位售價對利潤的影響時，為什么不可以直接設置B5B5單元格為鏈單元格為鏈接單元格，而是在接單元格，而是在C5C5單元格中輸入單位售價單元格中輸入單位售價1010倍的數值，

8、并把該單元格設置為鏈倍的數值，并把該單元格設置為鏈接單元格呢？接單元格呢？解答：這主要是因為在繪制了滾動條控件并設置控件格式時，其最大值、最小值、解答：這主要是因為在繪制了滾動條控件并設置控件格式時，其最大值、最小值、當前值、步長等值的取值范圍為當前值、步長等值的取值范圍為0 03000030000之間的整數，為單位售價要是之間的整數，為單位售價要是0.10.1元為元為步長增長，必須在步長增長，必須在C5C5單元格中輸入整數值，且該整數為單元格中輸入整數值，且該整數為B5B5單元格值的單元格值的1010倍，在倍，在B5B5單元格中設置公式為單元格中設置公式為“=C5/10=C5/10”，從而控制，從而控制B5B5單元格的數值。單元格的數值。點撥點撥2 2：在創建圖表的過程中，如果需要將默認圖表中的數據系列更改為其他數值，：在創建圖表的過程中，如果需要將默認圖表中的數據系列更改為其他數值，該如何操作？該如何操作？解答：在圖表創建完成后，如果想要重新設置數據系列的值，或添加新的數據系列解答：在圖表創建完成后，如果想要重新設置數據系列的值，或添加新的數據系列，都需要在選中圖表后，從右鍵彈出菜單中選擇，都需要在選中圖表后，從右鍵彈出菜單中選擇“選擇數據選項，即可打開【選選