Bài giảng Tin học ứng dụng trong kinh doanh - Chương 3: Dự báo và phân tích hồi quy tương quan - Nguyễn Kim Nam

 10/21/2012
 TIN H C NG D NG TRONG KINH DOANH
 11--PHÂNPHÂN TÍCH T ƯƠ NG QUAN
 ThSThS.. Nguy n Kim Nam
 http//:www.namqtkd.come.vn
 CH  NG III: 
n n Kim Nam D BÁO VÀ PHÂN TÍCH H I QUY n Kim Nam
ễ T NG QUAN ễ
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 ThS. Nguy n Kim Nam-Tr  ng b  môn Lý thuy t c ơ s  - Khoa QTKD 1 Khoa QTKD - ThS. Nguy n Kim Nam 2
 VD2:  nghiên c u quan h  gi a tu i ngh  (y) vàvàss sn ph m sai 
 hng (x) ca công nhân trong phân x ư ng ng ư i ta th ng kê ng u 
 nhiên 18 công nhân, s  li u thu ư c nh ư trong b ng sausau..TìmTìm t ươ ng 
 quan gi  tu i ngh  và s  s n ph m sai h ng
n n Kim Nam n Kim Nam
ễ ễ
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 3 Khoa QTKD - ThS. Nguy n Kim Nam 4
 22--PHÂNPHÂN TÍCH H ỒI QUY ((RegresssionRegresssion)) 22--PHÂNPHÂN TÍCH H ỒI QUY ((RegresssionRegresssion))
 --HHồi quy đơ n tuy ến tính:: tính --HHồi quy đơ n tuy ến tínhtính::
 Dng ơ n gi n nh t c a m t mô hình h i qui ch a m t 
 bi n ph  thu c (còn g i là "bi n  u ra," "bi n n i sinh," 
 hay "bi nn--Y")Y") và m t bi n  c l p ơ n (còn g i là "h  
 s," "bi n ngo i sinhsinh,"," hay ""bibi nn--X").X").
n n Kim Nam n Kim Nam
ễ Ph ươ ng trình h i quy tuy n tính ơ n có th  bi u di n ễ
 theo d ng 
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 5 Khoa QTKD - ThS. Nguy n Kim Nam 6
 1
 10/21/2012
n n Kim Nam n Kim Nam
ễ ễ
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 7 Khoa QTKD - ThS. Nguy n Kim Nam 8
 22--PHÂNPHÂN TÍCH H ỒI QUY 22--PHÂNPHÂN TÍCH H ỒI QUY
 --HHồi quy đa tuy ến tính
 vvììggiiáátrtr ị 7,72E7,72E--0606 < 0,05 nênph ươ ng trình h ồi quy
 này làlà thích hợp
 VíVíd ụ 2:2:
n n Kim Nam n Kim Nam
ễ ễ
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 9 Khoa QTKD - ThS. Nguy n Kim Nam 10
n n Kim Nam n Kim Nam
ễ ễ
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 11 Khoa QTKD - ThS. Nguy n Kim Nam 12
 2
 10/21/2012
n n Kim Nam n Kim Nam
ễ ễ
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 13 Khoa QTKD - ThS. Nguy n Kim Nam 14
n n Kim Nam n Kim Nam
ễ ễ
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 15 Khoa QTKD - ThS. Nguy n Kim Nam 16
n n Kim Nam n Kim Nam
ễ ễ
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 17 Khoa QTKD - ThS. Nguy n Kim Nam 18
 3
 10/21/2012
 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH
 D báo theo ph ư ng pháp phân tích nhân D báo theo ph ư ng pháp phân tích nhân
 qu  (h i quy và phân tích tư ng quan). qu  (hi quy và phân tích tư ng quan).
 
  d báo bng ph ư ng pháp s dng mô hình Hàm FORECAST
n n Kim Nam n Kim Nam
ễ hi quy tuy n tính  n y= ax + b (ylà bi n ph  ễ
 thu c, x là bi n  c lp) khi bi t ư c mt trong 
 Ý ngh a: Hàm Forecast tính, ư c lư ng giá tr 
 hai giá tr  ta có th  s dng các hàm TREND, tư ng lai cn c vào giá tr  hi n ti và quá kh .
 FORECAST, LINEST, SLOPE và INTERCEPT. 
 Cú pháp: 
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 =FORECAST(x, known_y’s, known_x’s)
 Khoa QTKD - ThS. Nguy n Kim Nam 19 Khoa QTKD - ThS. Nguy n Kim Nam 20
 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH
 
 Trong ó: Hàm Trend
 ◦ x là giá tr  dùng  d  báo. 
 Ý ngh a: Hàm Trend dùng  tr  v  giá tr  
n n Kim Nam n Kim Nam
ễ ◦ known_y’s là các giá tr  ho c vùng  a ễ dc theo ư ng h i quy (theo ph ư ng 
 ch  c a t p s  li u ph  thu c quan sát pháp bình ph ư ng nh  nh t)
 ư c
 
 Cú pháp: 
 ◦ known_x’s là các giá tr  ho c vùng  a 
Khoa Khoa QTKD - ThS. Nguy ch  c a t p s  li u  c l p quan sát Khoa QTKD - ThS. Nguy =TREND(known_y’s, known_x’s, 
 ư c. new_x’s, const)
 Khoa QTKD - ThS. Nguy n Kim Nam 21 Khoa QTKD - ThS. Nguy n Kim Nam 22
 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH
 
 Trong ó: 
 Hàm SLOPE và INTERCEPT
 ◦ known_y’s, known_x’s, new_x’s là các 
 Ý ngh a: Hàm SLOPE  tính h s góc a và
n n Kim Nam n Kim Nam hàm INTERCEPT  tính h s t do b ca
ễ giá tr  ho c vùng  a ch  ch a giá tr  ã ễ
 bi t c a x, y t ư ng ng và giá tr  m i hàm hi quy tuy n tính  n y=ax+b. Thay các
 h s a, b nàyvào hàms vi giá tr  ã bi t ca
 ca x. x ho c y ta s tìm ra giá tr  còn li cn d báo.
Khoa Khoa QTKD - ThS. Nguy ◦ const là h ng s . Ng m  nh n u const = Khoa QTKD - ThS. Nguy 
 Cú pháp:
 1 (True) thì h i quy theo hàm y = ax + b, 
 = SLOPE(known_y’s, known_x’s)
 nu const = 0 (False) thì h i quy theo 
 hàm y = ax. = INTERCEPT(known_y’s, known_x’s)
 Khoa QTKD - ThS. Nguy n Kim Nam 23 Khoa QTKD - ThS. Nguy n Kim Nam 24
 4
 10/21/2012
 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH
 
 Ví d : L i nhu n c a m t DN ph  thu c 
 
 Trong ó: vào giá thành s n ph m. DN ã th ng kê 
 ư c s  li u nh ư sau:
 ◦ known_y’s là các giá tr  ho c vùng  a
n n Kim Nam n Kim Nam
ễ ch  ca tp s li u ph  thu c quan sát ễ
 ư c
 ◦ known_x’s là các giá tr  ho c vùng  a
Khoa Khoa QTKD - ThS. Nguy ch  ca tp s li u  c lp quan sát Khoa QTKD - ThS. Nguy
 ư c.
 
 Nu sang k ti giá thành ca DN là 270000 thì
 li nhu n s là bao nhiêu?
 Khoa QTKD - ThS. Nguy n Kim Nam 25 Khoa QTKD - ThS. Nguy n Kim Nam 26
 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH
 
 Sử dụng hàm FORECAST :
 
 Dùng hàm Trend:
n n Kim Nam n Kim Nam
ễ ễ
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 27 Khoa QTKD - ThS. Nguy n Kim Nam 28
 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH
 
 Sử dụng hàm SLOPE và INTERCEPT :
 Hàm LINEST
 
 Ta có th  s dng hàm LINEST cho ph ư ng
 pháp d báo mô hình hi quy tuy n tính  n y
n n Kim Nam n Kim Nam
ễ ễ = ax + b và mô hình hi quy tuy n tính bi
 y = a1x1 + a2x2 ++ anxn + b.
 
 Cú pháp:
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy =LINEST(known_y’s, known_x’s, const, stats)
 
 Nh p xong ư c kt thúc bng t hp phím Ctrl
 + Shift + Enter.
 Khoa QTKD - ThS. Nguy n Kim Nam 29 Khoa QTKD - ThS. Nguy n Kim Nam 30
 5
 10/21/2012
 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH
 Hàm LINEST
 Sử d ụng hàm LINEST
 
 Trong ó: Ví d : L i nhu n c a doanh nghi p (y) ph  
 
 known_y’s, known_x’s , là các giá tr  ho c vùng
n n Kim Nam n Kim Nam thu c và giá thành s n ph m (x 1), chi phí 
ễ  a ch  ch a giá tr  ã bi t ca x và y tư ng ễ
 qu n lý (x 2), chi phí bán hàng (x 3). D  báo 
 ng. li nhu n c a doanh nghi p  t ư c khi 
 
 const là hng s. Ng m  nh nu const = 1 x1 = 600, x 2 = 35, x 3 = 25 b ng hàm 
Khoa Khoa QTKD - ThS. Nguy (True) thì tính toán h s t do b, nu const = Khoa QTKD - ThS. Nguy LINEST nh ư hình sau:
 0 (False) b qua b (b = 0).
 
 stats là các tham s th ng kê. 
 Khoa QTKD - ThS. Nguy n Kim Nam 31 Khoa QTKD - ThS. Nguy n Kim Nam 32
 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH
 
 Bài t p: M t DN nh n th y doanh thu c a 
 công ty ph  thu c vào chi phí qu ng cáo 
 trên truy n hình. DN ã th ng kê ư c s  
n n Kim Nam n Kim Nam
ễ ễ li u qua 5 tháng nh ư sau:
 Tháng Chi phí qu ng cáo (tri u) Doanh thu (t)
 Kéo, 
 vào fx 
 ri 1 16 5,1
 Ctr+sh
Khoa Khoa QTKD - ThS. Nguy +ent Khoa QTKD - ThS. Nguy 2 20 6,16
 3 18 5,43
 4 13 4,65
 5 17 5,3
 Khoa QTKD - ThS. Nguy n Kim Nam 33 Khoa QTKD - ThS. Nguy n Kim Nam 34
 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH
 
 S dng hàm TREND, FORECAST, 
 S dng hàm FORECAST  d báo cho
 SLOPE và INTERCEPT  d báo cho tháng 6 nu tháng 6 công ty d  nh chi
 tháng 6 nu tháng 6 công ty d  nh chi cho qu ng cáo là 14,75 tri u:
n n Kim Nam n Kim Nam
ễ cho qu ng cáo là 14,75 tri u: ễ
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 35 Khoa QTKD - ThS. Nguy n Kim Nam 36
 6
 10/21/2012
 33-- ỨNG D ỤNG EXCEL TRONG D Ự BÁO KINH DOANH
 SSử dụng ph ươ ng pháp bình quân didi độ ngng..
 
 Trong Excel s dng menu Tools / Data Analysis
 
 Tư ng t , b n có th  dùng các hàm / Moving average moving dùng  d báo theo
 TREND, SLOPE và INTERCEPT để d ự ph ư ng pháp trung bình di  ng.
 báo. (sv t ự th ực hi ện) 
 Mc interval  nh p s k tính bình quân.Tùy
n n Kim Nam n Kim Nam
ễ ễ ch n Chat Output  v  th  ca dãy d li u
 quan sát và d li u tính toán. Tùy ch n Standard
 Errors  hi n th  sai s gi a s quan sát và s
 tính toán .
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy 
 Ví d:
 Khoa QTKD - ThS. Nguy n Kim Nam 37 Khoa QTKD - ThS. Nguy n Kim Nam 38
 
 Có s li u th ng kê v doanh thu  mt ca hàng SSử dụng ph ươ ng pháp san bằng số mũ..
 trong mt nm nh ư bng sau. Vi s k tính bình 
 Ph ư ng pháp san bng hàm m (Exponential
 quân n=3. Hãy tính toán và d báo doanh thu cho Smoothing) còn có tên gi là ph ư ng pháp iu
 tháng 1 nm ti p theo. hòa m. Ph ư ng pháp này ư a ra các d báo cho
 giai on tr ư c và thêm vào ó mt lư ng iu
n n Kim Nam n Kim Nam
ễ ễ ch nh  có ư c lư ng d báo cho giai on k
 ti p. S iu ch nh này là mt t l nào ó ca sai
 s d báo  giai on tr ư c và ư c tính bng
 cách nhân s d báo ca giai on tr ư c vi h s
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy  nm gi a 0 và 1. H s này gi là h s iu
 hòa
 
 Ví d:
 Khoa QTKD - ThS. Nguy n Kim Nam 39 Khoa QTKD - ThS. Nguy n Kim Nam 40
 
 Có s  li u th ng kê v  doanh thu  m t c a hàng 
 trong m t n m nh ư b ng sau. Hãy tính toán và d  
 báo doanh thu cho tháng 1 n m ti p theo, theo 
 ph ư ng pháp san b ng s m vi  = 0.3 .
n n Kim Nam
ễ
Khoa Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy n Kim Nam 41
 7