[MPKD3] Mô phỏng trên bảng tính

MÔ PHỎNG TRÊN BẢNG TÍNH

NỘI DUNG

Phần mềm mô phỏng

Mô phỏng bằng Excel

Các hàm phân phối xác suất

Các hàm/công cụ phát số ngẫu nhiên

Mô phỏng bằng Crystal Ball

2

Vị trí của mô phỏng

Các mô hình định lượng (bao gồm OR/MS, Statistics &

Data mining)

3

Loạimô

hình

Dạnghàm của

biến phụ thuộc

Giátrị của

các biến độc lậpCáckỹ thuật điển hình

Chính tắc/chỉ

dẫn/tối ưu

(Prescriptive)

Đã biết, được

định nghĩa rõ

ràng

Đãbiết hoặc trong sự

kiểm soát của người ra

quyết định

Quy hoạchtoán học (tối ưu)

CPM (Critical Path Method)

EOQ (Econimic Order Quantity)

Mô tả

(Descriptive)

Đã biết, được

định nghĩa rõ

ràng

Chưabiết hoặc bất

định

Môphỏng (simulation)

Hàng đợi (waiting lines/queuing)

Phân tích Markov

PERT (Pro Eval Rev Tech)

Phân tích cụm (cluster)

Phân tích quyết định (bảng/cây QĐ)

Dựbáo

(Predictive)

Chưabiết, được

định nghĩa

không rõ ràng

Đãbiết hoặc trong sự

kiểm soát của người ra

quyết định

Phân tích hồi quy (regression)

Phân tích chuỗi thời gian (time series)

Phân tích phân biệt (discriminant)

ANN (artificial neural networks)

Các mô hình định lượng (bao gồm OR/MS, Statistics & Data mining)

Phần mềm mô phỏng

Excel

Crystall Ball

@RISK

Risk Solver

SLAM

Extend

SIMSCRIPT

GPSS

Arena

FlexSim

SimWiz

…

Quen thuộc

Các tính năng:

• Cập nhật động

• Đồ họa

• Thống kê

• Phân tích what-if

4


Bảng tính Excel

Các hàm/công cụ phát số ngẫu nhiên

Các công cụ phân tích what-if

Các add-in mô phỏng chuyên dụng

• Oracle’s Crystall Ball

• Palisade’s @RISK

• Frontline’ Risk Solver

• …

5

Các công cụ phân tích what-if

Ví dụ: Một người kinh doanh một mặt hàng A có giá

mua là $8 và giá bán là $10. Hai yếu tố này mang tính

chất rủi ro/bất định. Xét sự biến động của lợi nhuận?

Các công cụ phân tích what-if trong Excel:

Data Table

Scenario Manager

Goal Seek

Solver

6

Các hàm thống kê

7

Dạng thức tổng quát

Hàm phân phối tổng quát có dạng:• =DIST(x_value, distribution_parameters, cumulative_value)

Hàm phân phối nghịch đảo tổng quát có dạng:• =DISTINV(probability, distribution_parameters)

Ví dụ:

NORMDIST NORMINV=NORMDIST(25,20,3,TRUE) =NORMINV(.55,20,3)Trả về P(X25) khi m = 20 Trả về x0 sao cho P(Xx0)=.55 và s = 3 khi m = 20 và s = 3

NORMSDIST NORMSINV=NORMSDIST(1.78) =NORMSINV(.55)Trả về P(Z1.78) Trả về z0 sao cho P(Zz0)=.55

POISSON=POISSON(7,5,TRUE)

Trả về P(X7) với l = 5

Các hàm phân phối xác suất8

21

21( )

2

m

s

s

x

f x e

( ) ; 0,1,2,...!

ll

ke

f k kk

2

21

( )2

z

f z e

Standard Normal

Normal

Poisson

Phát số ngẫu nhiên

Phát số ngẫu nhiên phân phối đều trong khoảng

[0,1)

=RAND()

Phát số nguyên ngẫu nhiên phân phối đều trong

khoảng [a,b]

=RANDBETWEEN(a,b)

Phát số ngẫu nhiên phân phối đều trong khoảng

[a,b]?

9


Mặc định, giá trị của các ô chứa hàm RAND()

sẽ thay đổi sau mỗi hoạt động trên Excel

Gõ RAND() rồi nhấn F9

10

Tắt tính năng tự động tính toán lại: File > Options > Formulas,

chọn Manual

Khi cần tính tính toán lại: Nhấn F9

11


Về mặt nguyên tắc, có thể phát một số ngẫu

nhiên có phân phối bất kỳ thông qua số ngẫu

nhiên phân phối đều U(0,1).

Ví dụ:

Số ngẫu nhiên phân phối đều U(a,b)

f(x) = 1/(b-a) F(x) = (x-a)/(b-a)

=RAND()*(b-a)+a

sử dụng hàm INT khi cần số nguyên

Số ngẫu nhiên phân phối mũ E(m)

f(x) = m e-mx F(x) = 1-e-mx

=-(1/m)*ln(RAND())

12

F(x0)

F(x0)

F-1(U(0,1))

x0 F-1(U(0,1)) x

F(x)

13

U(0,1)


Để phát ra một số ngẫu nhiên thuộc một phân

phối cụ thể, có thể sử dụng hàm phân phối

nghịch đảo (inverse distribution function).

=DISTINV(probability, distribution_parameters)

Sử dụng hàm RAND() làm giá trị cho tham số

probability (nhằm phát ra một số nằm giữa 0 và

1). Ví dụ: Để phát số ngẫu nhiên phân phối

chuẩn, có thể sử dụng công thức như sau:

=NORMINV(RAND(), mean, standard_dev)

14


Bài tập: Phát ra 10 số ngẫu nhiên tuân theo phân phối

chuẩn với trung bình là 50 và độ lệch chuẩn là 15

=NORMINV(RAND(),50,15)

15

Data > Data Analysis Random Number Generation

16

Trả về xi+1 nếu P(xi) U(0,1) < P(xi+1)

xi

U(0,1)

P(x)

xxi+1

P(xi)

P(xi+1)

17


18

Bài tập: Phát ra 10 số

ngẫu nhiên có quy luật

phân phối xác suất theo

bảng sau:

Data > Data Analysis > Random Number Generation

19

Tìm phân phối phù hợp

Các bước theo lý thuyết:

Xây dựng biểu đồ tần suất từ dữ liệu

Chọn phân phối xác suất có hình dáng hàm mật độ xác suất giống

với biểu đồ tần suất nhất

Ước lượng các tham số của phân phối xác suất

Kiểm định Goodness-of-Fit (Thích hợp tốt)

• Chi-Square (>0.5)

• Kolmogorov-Smirnov (<0.03)

• Anderson-Darling (<1.5)

Sử dụng công cụ cho nhanh: Crystal Ball, @RISK, EasyFit,…

Bài tập: Tìm phân phối xác suất phù hợp với dữ liệu trong file

C:\Program Files\Oracle\Crystal Ball\Examples\TESTDATA.txt

20


22

Lập bảng và vẽ biểu đồ tần số:

Data > Analysis >

Data Analysis > Histogram


23

Lập bảng tần số:

=FREQUENCY(data,bins)

Giới

hạn

trên


24

Lập bảng và vẽ biểu đồ tần số:

Insert > Tables > Pivot Table


Không có dữ liệu quá khứ

Kinh nghiệm, ý kiến chuyên gia. Ví dụ:

Phân phối Poisson thường được sử dụng để mô

tả các sự kiện độc lập xảy ra với cường độ là hằng

số. Ví dụ: Sinh viên đến trạm xe buýt với cường

độ 0.8 sinh viên/phút

Phân phối mũ thường được sử dụng để mô tả

thời gian phục vụ.

Biết cận trên và cận dưới [a,b] phân phối đều

Biết cận trên và cận dưới [a,b], một số c[a,b] có

khả năng xuất hiện cao phân phối tam giác

…

26


Phát số ngẫu nhiên phân phối đều

=RAND() [0,1)

=RANDBETWEEN(a,b)

Phát số ngẫu nhiên thuộc vài phân

phối khác

Hàm phân phối nghịch đảo

Data > Analysis > Data Analysis

> Random Number Generation

Lập bảng tần số (để tìm phân phối

phù hợp)

=FREQUENCY(data,bins)

Data > Analysis >

Data Analysis > Histogram

Insert > Tables > PivotTable

27

Mô phỏng tung đồng xu

Hai mặt “Sấp” và “Ngửa” xuất hiện ngẫu nhiên

với xác suất bằng nhau (=0.5)

=IF(RAND()<0.5,“Sấp”,“Ngửa”)

28

Mô phỏng tung xúc sắc

Các giá trị 1, 2, 3, 4, 5, 6 xuất hiện ngẫu nhiên với

xác suất bằng nhau (=1/6)

Nếu 6*RAND() nằm INT(6*RAND())+1

trong khoảng: trả về giá trị:

0.0 0.999 1

1.0 1.999 2

2.0 2.999 3

3.0 3.999 4

4.0 4.999 5

5.0 5.999 6

=INT(6*RAND())+1

=RANDBETWEEN(1,6)

29

Bài toán cửa hàng tivi

Quan sát doanh số bán máy truyền hình của 30 ngày ở

một cửa hàng nọ, người chủ cửa hàng thấy được:

Người chủ muốn mô phỏng doanh số bán trong tương

lai. Tính lợi nhuận biết biến phí là 2 triệu/TV, giá bán 3

triệu/TV, định phí 5 triệu/ngày.

Số hàng bán Số ngày bán

4 5

5 8

6 10

7 7

30

Mô phỏng bằng Crystal Ball

Mở rộng khả năng dự báo của mô hình bảng tính: Cung

cấp các thông tin dự báo cần thiết hỗ trợ ra quyết định

với độ chính xác cao hơn, hiệu quả và tin cậy hơn.

Thuận lợi của việc sử dụng Crystal Ball

Quản lý quá trình phát ra số ngẫu nhiên thông qua

định nghĩa phân phối xác suất

Quản lý quá trình lặp

Hiển thị kết quả ở dạng đồ thị

Phân tích độ nhạy


Cho phép sự tương quan giữa các biến

Mở rộng Solver bằng OptQuest

31


•Chi-Square (>0.5)

•Kolmogorov-Smirnov (<0.03)

•Anderson-Darling (<1.5)

32

Phân tích độ nhạy

Ô giả thiết

Ô giả thiết

Ô dự báo

Ô dự báo

Độ nhạy (sensitivity) là độ lớn của sự không chắc chắn trong các ô dự báo gây ra

do bởi cả sự không chắc chắn và độ nhạy của các ô giả thiết trong mô hình.

34

C:\Program Files\Oracle\Crystal Ball\Examples\Toxic Waste Site.xls

Độ nhạy cao nhất Giả thiết quan trọng

nhất trong mô hình cần khảo sát thêm

giả thiết này nhằm giảm độ không chắc

chắn, từ đó giảm ảnh hưởng đến mục

tiêu dự báoĐộ nhạy thấp nhất Giả thiết kém quan

trọng nhất trong mô hình ảnh hưởng

không lớn đến mục tiêu dự báo có thể

bỏ qua và loại hẳn khỏi mô hình

35

Một số phân phối thông dụng

Phân phối liên tục (Continuous)

Phân phối đều (Uniform)

Phân phối chuẩn (Normal)

Phân phối tam giác (Triangular)

Phân phối mũ (Exponential)

Phân phối rời rạc (Discrete)

Phân phối Yes-No (Bernoulli)

Phân phối đều rời rạc (Discrete Uniform)

Phân phối nhị thức (Binominal)

Phân phối Poisson

Phân phối hình học (Geometric)

Phân phối siêu bội (Hypergeometric)

Phân phối tùy biến (Custom)

37

Phân phối tùy biến (Custom)

Giúp ta xây dựng phân phối phù hợp với từng tình

huống đặc thù (không thể mô tả bằng các phân phối

thông dụng có sẵn)

Có thể mô tả một chuỗi các giá trị riêng lẻ, những

khoảng rời rạc hoặc những khoảng liên tục

Range 1st Param. 2nd Param. 3rd Param. 4th Param. 5th Param.

Unweighted Values Value -- -- -- --

Weighted Values Value Probability

Continuous Ranges Minimum Maximum Probability

Discrete Ranges Minimum Maximum Probability Step

Sloping Ranges Minimum Maximum Height of Min. Height of Max. Step

38

39

Chi phí bán lẻ có thể

có cho một sản phẩm

mới sẽ là 5$, 8$ hoặc

10$

75% cơ hội sẽ là một

giá trị nào đó trong

khoảng 5$, 15$;

25% cơ hội sẽ là một

giá trị nào đó trong

khoảng 15$, 25$

40

75% cơ hội sẽ là lượng

tiền giữa 5$, 15$;

25% cơ hội sẽ là lượng

tiền giữa 16$, 20$

41

Một bài toán cực kỳ đơn giản

Một người kinh doanh một mặt hàng A có giá

mua là $8 và giá bán là $10.

Hãy phân tích sự thay đổi của tiền lời khi giá

mua và giá bán thay đổi. Biết rằng giá mua có

dạng phân phối chuẩn N(m=8, s2=4), giá bán có

dạng phân phối chuẩn N(m=10, s2=4).

Hãy cho biết khả năng lỗ là bao nhiêu phần

trăm?

42

Quá trình mô phỏng với Crystal Ball

43

1. Lập mô hình trên bảng tính Excel

2. Khai báo các biến giả thiết

3. Khai báo các biến dự báo

4. Khai báo các thông số mô phỏng

5. Chạy mô phỏng

6. Xem xét kết quả mô phỏng

7. Tạo báo cáo

1. Lập mô hình trên bảng tính

44

Để trống? Hằng số?

Công thức?

Ba loại biến

Biến giả thiết (assumption)

Biến dự báo (forecast)

Biến quyết định (decision) bàn sau!

2. Khai báo biến giả thiết (Giá mua)

Vào Crytall Ball Define Define Assumption

45

Chọn kiểu

phân phối xác

xuất và nhập

các thông số

tương ứng

2. Khai báo biến giả thiết (Giá bán)

Vào Crytall Ball Define Define Assumption

46

Chọn kiểu

phân phối xác

xuất và nhập

các thông số

tương ứng

3. Khai báo biến dự báo (Lợi nhuận)

Vào Crytall Ball Define Define Forecast

47

Nhập tên,

đơn vị tính

Chọn More

để khai báo

thêm

4. Khai báo các thông số

Vào Crystal Ball Run Run References

Khai báo số lần mô phỏng, giá trị hạt nhân ban đầu,

chế độ màn hình khi chạy...

48

5. Chạy mô phỏng

Vào Crystal Ball Run Start

49

6. Xem kết quả

Các kết quả được thể hiện ở nhiều dạng khác nhau:

50

7. Tạo báo cáo

Vào Crystal Ball

Analyze

Create Report

kiểu report

Chọn Full để

Crystal Ball tạo

báo cáo đầy đủ

nhất

51


•Chi-Square (>0.5)

•Kolmogorov-Smirnov (<0.03)

•Anderson-Darling (<1.5)

55

-HẾT-

[MPKD3] Mô phỏng trên bảng tính

Business

Transcript of [MPKD3] Mô phỏng trên bảng tính