E17: Phân tích độ nhạy của dữ liệu với Data Table

  • Post author:
  • Post category:Excel
  • Post comments:0 Comments
  • Reading time:8 mins read

Trong Excel, data table là một trong những công cụ What-If Analysis dùng để phân tích độ nhạy dữ liệu. Mục đích của công cụ này để so sánh các kết quả đầu ra tương ứng với sự thay đổi của các biến đầu vào.

Bài viết dưới đây sẽ phân tích bài toán ứng dụng data table với một biến và hai biến để phân tích số liệu đầu ra tương ứng.

Bài toán:

Giả thiết bạn muốn mở một cửa hàng bán trà sữa. Bạn đang rất băn khoăn xem nên bán một cốc trà sữa với giá bao nhiêu để thu được mức lợi nhuận cao nhất, cũng như là chi phí quản lý để tối ưu doanh thu và lợi nhuận.

Sử dụng bảng dữ liệu 1 biến (one variable data table)

Bảng dữ liệu 1 biến cho phép tính toán các kết quả đầu ra tương ứng của công thức dựa trên sự biến đổi của chuỗi đầu vào

Hãy xem xét bài toán ở trên với file excel có những giả thiết dưới đây:

  • Giá cho 1 cốc trà sữa (price) D1 = $4.00
  • Nhu cầu của khách hàng được ước lượng theo hàm đường cầu (demand curve) như sau demand D2 = 29000 = 65000-9000*price
  • Chi phí biến đổi để sản xuất cho mỗi cốc trà sữa là unit cost D3 = $0.45
  • Chi phí cố định fixed cost D4 = $45,000
  • Doanh thu được xác định như sau revenue D5 = $116,000 = demand*price
  • Tổng chi phí biến đổi variable cost D6 = $13,050 = unit_cost*demand
  • Lợi nhuận thu được profit D7 = $57,950 = revenue – fixed_cost – variable_cost
  • Thực hiện đặt tên cho vùng dữ liệu C1:D7 với công cụ Formulas | Defined Names | Create from Selection để tiện quan sát

Giả thiết bạn đang cân nhắc mức giá (price) từ $1.00 đến $4.00 với mỗi mức giá chênh nhau $0.25 được thiết lập từ C11:C23. Bạn cần so sánh mức lợi nhuận ở cột D, doanh thu ở cột E và chi phí biến đổi ở cột F. Ta thiết lập bảng dữ liệu 1 biến (vì chỉ căn căn cứ vào sự biến đổi của giá) như sau:

  • Profit ta copy công thức của ô D7 vào D10 thu được giá trị 57000
  • Revenue ta copy công thức của ô D5 vào ô E10 thu được giá trị 116000
  • Variable cost ta copy công thức của ô D6 vào ô F10 ta thu được giá trị 13050
  • Thực hiện lựa chọn đánh dấu bảng dữ liệu từ (C10:F23). Với bảng dữ liệu này, ô đầu tiên sẽ là biến đầu vào đầu tiên để tính toán các giá trị cho hàng thứ nhất, biến này sẽ được chọn trong bước sau trong công cụ What-If Analysis. Cột đầu tiên sẽ là các tham số đầu vào biến đổi cần để tính toán.
  • Thực hiện chọn Data | What-If Analysis | Data Table…
  • Tại cửa sổ Data Table, lúc này ta sẽ chọn biến đầu vào đầu tiên. Ở đây tham số biến đổi theo price được sắp xếp ở cột đầu tiên nên ta chọn Column input cell: = D$1 rồi bấm OK

Ta thu được kết quả dưới đây.

Qua số liệu này ta thấy được, lợi nhuận sẽ thu được cao nhất là $58,125 khi ta bán một cốc trà sữa với giá $3.75. Với mức giá thấp hơn $1.25 thì cửa hàng sẽ lỗ ít nhất là $2,000

Sử dụng bảng dữ liệu 2 biến (two variable data table)

Ở trên ta mới giá thiết chỉ thay đổi về giá bán, các chi phí còn lại là cố định. Tuy nhiên trên thực tế chi phí biến đổi sẽ không cố định tại một giá trị. Giả thiết rằng chi phí biến đổi dao động từ $0.30 đến $0.60 với biên độ biến đổi là $0.05. Và bạn đang cân nhắc mức giá từ $1.50 tới $5.00 cho mỗi cốc trà sữa, với biên độ giá dao động là $0.25. Với sự dao động cả về giá và chi phí biến đổi như vậy thì mức lợi nhuận thu được sẽ biến đổi như thế nào?

Với bài toán này, chúng ta có 2 tham số biến đổi là giá price và chi phí biến đổi cho mỗi đơn vị sản phẩm unit_cost. Chúng ta sẽ sử dụng bảng dữ liệu 2 biến cho trường hợp này. Thiết lập như sau

  • Biến thứ nhất mức giá thiết lập theo cột từ I11: I25
  • Biến thứ hai chi phí biến đổi đơn vị thiết lập theo hàng từ J10:P10
  • Với bảng dữ liệu 2 biến chúng ta chỉ có thể tính toán được 1 giá trị đầu ra là lợi nhuận profit. Giá trị này sẽ là ô đầu tiên của bảng dữ liệu. Chúng ta có thể hiểu rằng giá trị này được tính toán tham số dữ liệu 2 chiều hàng và cột.
  • Thiết lập ô I10 = D7 = 57950 bằng cách copy công thức ở ô D7 cho ô I10. Các giá trị trong bảng dữ liệu 2 biến sau đó sẽ được tính toán dựa trên công thức này.
  • Thực hiện việc chọn bảng dữ liệu từ (I10:P25)
  • Thực hiện chọn Data | What-If Analysis | Data Table…
  • Tại cửa sổ Data Table ta có 2 biến đầu vào.
    • Row input cell hiện đang thiết lập là tham số chi phí biến đổi đơn vị nên ta thiết lập cho giá trị D$3
    • Column input cell hiện đang thiết lập là tham số giá nên ta thiết lập cho giá trị D$1

  • Bấm OK ta thu được kết quả

Nhìn vào số liệu trên ta có thể thấy, mức giá tối ưu nhất cho 1 cốc trà sữa trong trường hợp trên là $3.75 và chi phí biến đổi đơn vị tối ưu nhất là $0.30 khi đó mức lợi nhận thu được sẽ đạt đỉnh ở mức $62,812.50

Ứng dụng

Phân tích độ nhạy của dữ liệu sử dụng data table có thể được ứng dụng ở rất nhiều các bài toán phân tích dự đoán số liệu khác nhau. Ví dụ bạn có thể sử dụng data table để ước lượng số tiền mà bạn phải trả hàng tháng cho 1 khoản vay với giả thiết số tiền vay và lãi suất hàng tháng chưa chưa xác định. Hoặc bạn có thể sử dụng data table để dự đoán lợi nhuận thu được cho 1 khoản đầu tư dựa trên số tiền đầu tư ban đầu dự kiến…

Leave a Reply