1、江西j ian gx i科sc ience學第 28卷第 1期2010年 2 月vo l. 28 no. 1feb. 2010文章編號 : 1001 - 3679 ( 2010 ) 01 - 0011 - 04利用exce l建立利潤敏感分析模型楊樺(東北財經大學職業技術學院 ,遼寧 大連 116023)摘要 :利潤是企業的綜合性經濟指標 ,它反映企業一個時期的經營成果 。在利潤的形成過程中 ,會存在許多不確定因素對利潤產生影響 ,各因素對利潤的影響程度和敏感程度不同 。所謂利潤敏感性分析是衡量單一變量 如產品售價或組合變量 (售價 、銷量都變動 ) 變動對利潤影響的一種分析方法 。利用 ex

2、cel 的分析工具 ,分 析了各因素變動對利潤的影響 ,并在此基礎上建立了利潤敏感分析模型與動態圖表 。關鍵詞 :利潤 ;敏感分析 ;模型中圖分類號 : f275. 4文獻標識碼 : ato e sta b l ish the prof it sen s it iv ity ana ly s is m ode l by exce lyan g h ua( co llege of voca tiona l and techn ica l, dongbe i u n ive rsity of f inance and econom ic s, l iaon ing d a lian 116023

3、prc)a b stra c t: p rofit is a comp rehen sive econom ic ind ica to r of an en te rp rise, and it reflec ts the en te rp rises op e ra ting re su lts fo r a p e riod. d u ring the fo rm a tion of p rofit, the re a re m any unce rta in fac to rs tha t w ill have imp ac ts on p rofit. the effec t degr

4、ee and the sen sitivity of p rofit of each fac to r a re d iffe ren t. the sen sitivity ana lysis is a m e thod tha t d iscu sse s the influence of a single va riab le, such a s p rice, o r com b ined va riab le ( p rice, sa le s a re bo th sub jec t to change) on the p rofit.in th is p ap e r, the

5、influ2 ence of each va riab le fac to r on p rofit is d iscu ssed w ith excel ana lysis too ls. then the p rofit sen si2 tivity ana lysis mode l and dynam ic cha rts a re e stab lished on th is ba sis.key word s: p rofit, sen sitivity ana lysis, mode l小 。那些對利潤影響大的因素稱之為敏感因素 ,反之 ,則稱為非敏感因素 。0 前言利潤敏感性分析

6、法是指從定量分析的角度研 究有關因素發生某種變化對某一個或一組關鍵指 標影響程度的一種不確定分析技術 。其實質是通 過逐一改變相關變量數值的方法來解釋關鍵指標 受這些因素變動影響大小的規律 。有些因素雖然 變化幅度較大 ,卻只對利潤產生微小的影響 。所 以對一個企業的管理者來說 ,不僅需要了解哪些 因素對利潤增減有影響 ,而且需要了解影響利潤 的若干因素中 , 哪些因素影響大 , 哪些因素影響1 利潤敏感性分析指標的確定在運用利潤敏感性對實際業務進行定量分析 時 ,常運用的方法可以包括質性的分析和量性的 分析兩方面 。1. 1 質性的分析即與利潤變化的方向分析 。如果因素 i對利 潤的敏感度

7、s i 0, 則說明此因素的變化方向與利潤的變化方向相同 , 或說增大此因素會使利潤增收稿日期 : 2009 - 12 - 16;修訂日期 : 2010 - 01 - 25作者簡介 :楊 樺 ( 1963 - ) ,女 ,遼寧大連人 ,經濟學碩士 ,從事信息管理方面的研究工作 。大 ; 反之 , 若 s i 1,即當某影響因素發生變化時 , 利潤發生更大程度 的變化 , 該影響因素為敏感因素 ; ( 2 ) 敏感系數的 絕對值 1, 即利潤變化的幅度小于影響因素變化 的幅度 , 該因素為非敏感因素 ; ( 3 ) 敏感系數的絕對值 = 1, 即影響因素變化合導致利潤相同程度的 變化 , 該因素

8、亦為非敏感因素 。2 利用實例建立模型在企業只生產單品種產品 , 且產銷平衡的條 件下 , 本量利之間的基本關系為 :利潤 =銷售量 (單價 - 單位變動成本 ) -固定成本 。在進行利潤敏感性分析時 , 假定條件如下 :利 潤只受銷售量 、產品單價 、單位變動成本和固定成本的影響 ; 上述各因素的變動均不會影響其他因 素的變動 。2. 1 多因素綜合變動對利潤的影響2. 1. 1 新建工作簿與工作表 打開 exce l,新建一工作簿 ,命名為 敏感分析. xls ,再新建一工作 表 ,取名為 敏感分析模型 ,建立 敏感分析模型 表 ,如圖 1所示 。圖 1 敏感分析模型2. 1. 2 指標計

9、算打開【窗體 】控件 單擊【滾動條 】按鈕 在 e3: e6單元格分別添加【滾動條 】控件 右擊 e3 單元格【滾動條 】選擇【設置控件格式 】在彈出的【設置控件格式 】對話框中作如圖 2 的設置 。其它控件同理 , 分別鏈接到 f3、f4、f5、f6單元格 。圖 2 參數設置本例 中 各 因 素 的 變 動 范 圍 為 - 25 % +25 % ,而滾動條控制按鈕的值的變化范圍為 0 50 ,為了使滾動條控制按鈕的變化表示為百分數 的變化 ,這里將控制按鈕的值除以 100 后再減去25 % ,則每次單擊滾動條兩端的箭頭 ,單元格中的變動百分比就變化 1 % ,而當滾動條在中間位置 ,百分比恰

10、好為零 ; 當單擊滾動框與滾動條兩端之間時 ,每單擊一次 ,變動百分比就增 (或減 ) 10 % 。選取 c3: c6 單元格區域 在編輯欄中以數組方式輸入 = b 3: b 6 3 ( 1 + d 3: d6 ) ,即可計算 出變動后的數值 ; 單擊 d 3 單元格 在編輯欄中輸入 = f3 /100 - 25 % 復 制 公 式 至 d 6 單 元格 ,即可設置出變動的幅度 ,如圖 3所示 。2. 1. 3 因素分析單擊 b 11 單元格 在編輯欄 中輸入 = b 6 3 (b 3 - b 4 ) - b 5 復 制公 式至c11 單 元 格 ; 單 擊 d11 單 元 格 在 編 輯 欄

11、 中 輸 入 = c11 - b 11 ;單擊 e11單元格 在編輯欄中時 ,利潤增加 3 % 。因此 ,產品單價對利潤的影響最大 , 敏感性最強 , 其次是單位變動成本和銷售 量 ,而固定成本對利潤的影響最小 。2. 2. 2 建立單因素動態圖 單擊 b 19 單元格 在編輯欄中輸入公式 = if (a 18 = 1 , f14 , if (a 18= 2 , f15 , if (a 18 = 3 , f16 , f17 ) ) ) ,即當 a18 = 1 時 ,讓其值等于單價變動的幅度 ; 當 a 18 = 2 時 , 讓其值等于單位變動成本變動的幅度 ;當 a18 = 3 時 ,讓其值等

12、于固定成本變動的幅度 ; 當 a 18 不 等于 1 或 2 或 3 時 ,讓其值等于銷售量變動的幅度 。選中 b 18: b 19 單元格區域 利用圖表向導 建立變動幅度的直方圖 ;利用【窗體 】控件添加一 個【組合框 】右擊【組合框 】選擇【設置控件格 式 】在彈出的【設置控件格式 】對話框中作如圖6的設置 。建立的動態圖表如圖 7所示 。圖 3 變動后的數值計算輸入 = d 11 /b 11 ,這樣就計算出了多因素綜合 變動對利潤的綜合影響 ,如圖 4所示 。圖 4 多因素分析當上述 4個因素同時朝著使利潤增加的方向 變動 5 %時 ,則利潤的增加幅度為 44. 60 % 。2. 2 單

13、因素變動對利潤的影響2. 2. 1 編輯指標計算公式 選取 b 14: b 17 單元 格區域 在編輯欄中以數組公式輸入 = b 11 ; 選取 c14: c17 單元格區域 在編輯欄中以數組公式輸入 = d3: d 6 ,復制原始數據 。單擊 d 14單元格 在編輯欄中輸入 = b 6 3( c3 - b 4 ) - b 5 ; 單擊 d15 單元格 在編輯欄 中輸入 = b 6 3 ( b 3 2c4 ) 2b 5 ; 單擊 d16 單元 格在編輯 欄中 輸 入 = b 6 3 (b 3 2b 4 ) 2c5 ; 單 擊d17單元格 在編輯欄中輸入 = c6 3 (b 3 2b 4 ) 2

14、b 5 ,這樣就分別計算出了銷售量 、產品單價 、單 位變動成本和固 定成 本 單獨 變動 時 對利 潤的 影 響 ;單擊 e14 單元格 在編輯欄中輸入 = d14 2b 14 復 制 公式 至 e17 單 元格 ; 選 取 f14: f17單元 格 區 域 在 編 輯 欄 中 以 數 組 公 式 輸 入 = e14: e17 /b 14: b 17 , 將 全 部 因 素 的 變 動 數 值 計算完成 ,如圖 5所示 。2. 2. 3 建立銷售收入變動與利潤變動動態圖( 1 )建立銷售收入變動與利潤變動表 , 如圖 8 所 示 。圖 5 單因素分析這樣利潤敏感性分析模型就建立起來了 。單

15、擊各個影響因素滾動條的箭頭 ,改變其變動幅度 , 就可以很方便地了解各個因素變動對利潤的單獨 影響程度以及綜合影響程度 。例如 ,當產品銷量 單獨增加 5 %時 ,利潤增加 8 % ; 當產品單價單獨 增加 5 %時 ,利潤增加 20 % ;當單位變動成本減少5 %時 ,利潤增加 12 % ; 當固定成本單獨減少 5 %圖 8 銷售收入與利潤變動表其中 d 21 單元格的計算公式為“ = c21 2b 6 3b 4 2b 5 ”; d 22 單元格的計算公式為“ = c22 2c6 3c4 2c5 ”。( 2 )建立模擬運算表 。復制 c24: d 24 單元格區域的數值至 g20: h20

16、,在 f21: f26 單元格區域 填入必要的銷售收入數值 ,選取 f20: h26 單元格 區域 單擊【數據 】菜單 選擇【模擬運算表 】 在彈出的【模擬運算表 】對話框“輸入引用列的單元格 ”中輸入“c21 ”,這樣就建立起了不同銷售收 入所對應的銷售收入的變動幅度與利潤的變動幅 度 。如圖 9所示 。綜合利用各有關因素之間的相互聯系 ,采取綜合措施 ,是成功的關鉸 。3. 1 慎重實施價格決策單價為絕對敏感因素 ,單價的變化會引起利 潤以幾倍于單價變化的速度發生變化 。所以在經濟決策中 ,對降價必須給予格外的關注 。價格下 調帶來的利潤損失 ,若不能通過擴大銷售量或降 低單位成本予以更大

17、程度的補償 ,則企業的整體 利潤肯定會下降 ,目標利潤難以實現 。同時 ,價格 上漲的同時 ,應盡可能地抑制銷量的大幅縮減和成本的大幅上升 。否則目標利潤同樣難以實現 。3. 2 降低單位成本單位變動成本同樣屬于利潤的敏感因素 ,所 以降低單位成本對實現企業目標利潤具有重要意 義 。特別是從長期看 ,成本的高低將是企業能否生存和發展的關鍵 。企業降低單位成本的主要措 施有 : ( 1 )源頭控制 , 降低材料的采購成本 , 其主 要方式有招標采購或網上采購等 ; ( 2 ) 生產過程圖 9 模擬運算表( 3 )建立動態圖 。根據模擬運算表 ,利用【圖 表向導 】,建立 x、y散點圖 ;利用窗體

18、控件添加一個微調控件 , 鏈接到 c21 單元 格 , 該 動態 圖顯 示了隨著銷售收入絕對值的變動 ,銷售收入與利潤 百分比的變動 ,如圖 10所示 ?？刂?,降低生產成本 ,對生產過程中的材料浪費 、不必要的機器停工損失都必須通過嚴格管理加以控制 ; ( 3 )實行批量生產 ,實現規模效益 ; ( 4 )必要 時 ,建立責任成本控制制度 ,將成本控制的責任落 實到具體的部門或個人 。3. 3 擴大銷售量銷量的增加會導致企業利潤的大幅度增加 。但這里有一個前提 ,就是單價的降低幅度不能太大 ,否則 ,由于價格的敏感程度大于銷量的敏感程 度 ,銷量的增加反而會減少企業的利潤 。 2000 年 度中國彩電行業第一次出現全行業虧損 ,就是價 格戰的惡果 ,而不是銷售出了問題 。一般而言 ,企 業擴大