2. Làm việc với Goal SeekThêm một câu hỏi What-If cho bạn:"Điều gì sẽ xảy ra" nếu bạn đã biết trước kết quả? Ví dụ, có thể bạn đã biết rằng bạn phải tiết kiệm được $50,000 thì mới đủ tiền để sắm thêm thiết bị trong vòng 5 năm sắp tới, vậy ngay từ bây giờ bạn phải tiết kiệm bao nhiêu mỗi tháng, mỗi năm? Hoặc là trong năm tài chính sắp tới, bạn phải đạt được 30% tổng lợi nhuận, vậy ngay từ bây giờ bạn phải làm gì? Hoặc bạn có phương trình x + 3 = 15, và bạn phải tính x bằng bao nhiêu...
Nếu bạn dùng Excel, và phải xử lý một biến nào đó để đạt được một kết quả đã cho trước, bạn có thể sử dụng tính năng Goal Seek. Bạn cho Goal Seek biết kết quả sau cùng, và biến nào phải thay đổi, nó sẽ tìm ra kết quả của việc thay đổi ở biến đó dùm cho bạn, nếu có thể.
2.1. Goal Seek làm việc như thế nào?Khi bạn thiết lập một bảng tính để sử dụng Goal Seek, bạn thường có một công thức trong một ô, và các biến cho công thức này (với một giá trị ban đầu) trong những ô khác. Công thức có thể có nhiều biến, nhưng Goal Seek chỉ cho phép bạn xử lý mỗi lần một biến mà thôi.
Goal Seek hoạt động bằng cách sử dụng phương pháp lặp đi lặp lại (iterative method) để tìm ra lời giải. Nghĩa là, Goal Seek sẽ thử giá trị ban đầu của biến để xem nó có tạo ra kết quả mà bạn muốn hay không. Nếu không, Goal Seek sẽ thử tiếp với những giá trị khác nhau, cho đến khi nó tìm ra lời giải, hay nói cách khác, cho đến khi nào kết quả mà nó tìm được gần giống với kết quả của bạn muốn nhất.
2.2. Chạy Goal SeekTrước khi chạy Goal Seek, bạn cần thiết lập bảng tính theo một mẫu nào đó, mà cụ thể là nên thực hiện 3 điều sau đây:
- Thiết lập một ô làm ô thay đổi (changing cell). Đây là giá trị mà Goal Seek sẽ xử lý lặp đi lặp lại để cố gắng đạt được kết quả gần đúng nhất. Rồi nhập một giá trị ban đầu trong ô này, cho nó bằng 0 chẳng hạn.
- Thiết lập các giá trị nhập liệu khác cho công thức và đặt cho chúng những giá trị thích hợp.
- Tạo một công thức để Gioal Seek sử dụng trong quá trình cố gắng đạt được mục tiêu.
Ví dụ, bạn đang ước mơ sắm được một thiết bị có trị giá $50,000, và muốn là trong vòng 5 năm tính từ hôm nay, ước mơ sẽ thành hiện thực. Giả sử rằng, tiền gửi ngân hàng có lãi suất là 5% một năm, vậy bạn cần phải gửi vào ngân hàng mỗi năm tối thiểu là bao nhiêu để đạt được ước mơ của mình? Hình 9 minh họa một bảng tính đã được thiết lập để sử dụng Goal Seek cho ước mơ này:
Hình 9
Trong hình trên:
- Ô C6 là ô thay đổi (changing cell): số tiền tối thiểu phải gửi vào ngân hàng mỗi năm (với giá trị ban đầu là không có đồng nào cả).
- Các ô C4 và C5 được sử dụng làm các hằng cho hàm FV() ở ô C8.
- Ô C8 chứa hàm FV(), là một hàm chuyên dùng để tính một giá trị tương lai cho một khoản đầu tư. Và bạn muốn rằng kết quả ở đây sẽ là $50,000.
Bạn theo các bước sau đây để chạy Goal Seek:
- Chọn Data, What-If Analysis, Goal Seek. Excel hiển thị hộp thoại Goal Seek.
- Nhập tham chiếu đến ô chứa công thức trong hộp Set Cell. Trong trường hợp này, là $C$8.
- Nhập giá trị mà bạn muốn sẽ là kết quả của công thức trong hộp To Value. Trong trường hợp này, bạn nhập vào 50000.
- Nhập theo chiếu đến ô thay đổi, hay nói cách khác, để đạt được giá trị ở To Value cho công thức ở Set Cell, thì thay đổi giá trị ở đây: By changing cell (là ô C6 trong hình 9).
Hình 15.10 minh họa bảng tính cùng với hộp thoại Goal Seek đã điền đầy đủ:
Hình 10 - Sau khi nhấn OK của hộp thoại, Goal Seek sẽ "rùng mình" một tí, và hiển thị kết quả mà nó tìm được vào ô thay đổi (là giá trị $-9,059 ở ô C6) và hộp thoại Goal Seek Status cho bạn biết nó có tìm được lời giải hay không, cũng cho bạn so sánh kết quả áp dụng lời giải này (Current value) với kết quả mà bạn muốn có (Target value):
Hình 11
Ở đây, Goal Seek tính ra rằng, nếu muốn có được $50,000 sau 5 năm, thì ngay từ bây giờ, mỗi năm bạn phải gửi vào ngân hàng ít nhất $9,059, với giả thiết lãi suất tiền gửi trong suốt 5 năm là 5% một năm. - Nếu bạn ưng ý với kết quả của Goal Seek tìm được, thì nhấn OK để chấp nhận kết quả ở ô thay đổi (là giá trị $-9,059 ở ô C6), còn nếu muốn bỏ qua kết quả này, bạn nhấn Cancel.
2.3. Bài toán tối ưu hóa lợi nhuận sản phẩm
Có nhiều doanh nghiệp sử dụng lợi nhuận từ sản phẩm như là thước đo cho tình hình tài chính của mình. Một mức lợi nhuận mạnh, có nghĩa là các chi phí đang được kiểm soát tốt, và cho thấy thị trường hài lòng với giá cả của sản phẩm. Dĩ nhiên, lợi nhuận còn phụ thuộc vào nhiều yếu tố khác nữa, và bạn có thể sử dụng Goal Seek để tìm ra mức lợi nhuận tối ưu dựa vào một trong những yếu tố này.
Ví dụ, bạn muốn đưa ra một dòng sản phẩm mới, và bạn muốn thu được 30% lợi nhuận từ nó trong năm đầu tiên. Và giả sử rằng bạn có những giả định sau đây:
- Trong năm đầu tiên này sẽ bán được 100,000 sản phẩm (Unit Sold).
- Mức chiết khấu trung bình (Average Discount) cho các đại lý là 40%
- Tổng chi phí cố định (Fixed Costs) là $750,000, và chi phí riêng cho mỗi sản phẩm (Costs per Unit) là $12.63
Từ những thông tin trên, chúng ta sẽ tìm ra mức giá bán hợp lý nhất (Price per Unit) cho sản phẩm để kiếm được 30% lợi nhuận (Margin).
Hình 12 minh họa một bảng tính được thiết lập để giải bài toán này. Một giá trị ban đầu là $1.00 được nhập ở ô Price Per Unit (giá bán cho mỗi sản phẩm, ô C4). Với mức giá này, nếu bán hết 100,000 sản phẩm, và sau khi chiết khấu 40% cho đại lý, chúng ta sẽ có doanh thu là $60,000. Chi phí cho 100,000 sản phẩm này cộng với chi phí cố định sẽ là $2,013,000. Như vậy, nếu như bán với giá $1.00 một sản phẩm, chúng ta sẽ lỗ $1,953,000, hay tương đương với -3255% (!)
Để tìm ra giá bán cho sản phẩm (giá trị ở C4) mà kiếm được 30% lợi nhuận (ở C14), tôi thiết lập các tham số trong hộp thoại Goal Seek như sau:
- Tham chiếu cho Set Cell là C14
- Giá trị cho To Value là 0.3 (tức 30%)
- Tham chiếu cho By Changing Cell là C4
Hình 12
Hình 13
2.4. Ghi chú về những giá trị xấp xỉ của Goal SeekỞ hình 13 của phần trên, bạn thấy rằng Goal Seek đưa ra một con số xấp xỉ: 29.92%, chứ không phải là 30% như chúng ta mong muốn. Giá trị đó khá gần (lệch chỉ 0.008) nhưng không chính xác. Tại sao Goal Seek không tìm ra lời giải chính xác?
Câu trả lời nằm ở một trong những tùy chọn mà Excel sử dụng để điều khiển các phép tính lặp đi lặp lại (iterative calculations). Đôi khi, việc lặp đi lặp lại có thể phải mất một thời gian cực kỳ dài để tìm ra được lời giải chính xác, do đó, Excel đã hòa hợp bằng việc xác lập những giới hạn nhất định trong quá trình lặp lại.
Để thấy những giới hạn này, bạn mở Excel Options ra rồi nhấn vào ngăn Formulas (xem hình 14). Trong đó có hai tùy chọn xác lập cho việc lặp lại:
- Maximum Iterations — Giá trị trong text box này quy định số lần lặp lại tối đa. Trong Goal Seek, chính là số giá trị tối đa mà Excel đưa vào ô thay đổi (changing cell) để thử.
- Maximum Change — Giá trị trong text box này là giới hạn mà Excel sử dụng để quyết định xem nó có hội tụ đến một lời giải hay không. Nếu hiệu số giữa lời giải hiện hành và mục tiêu muốn đạt được nhỏ hơn hoặc bằng giá trị này, Excel sẽ ngừng lại.
Hình 14
Chính giá trị Maximum Change = 0.001 (mặc định) đã ngăn Excel đưa ra lời giải chính xác cho phép tính lợi nhuận ở bài trước. Trong một lần lặp lại nào đó, Goal Seek đã tìm ra đáp số là 0.2992 (tương đương 29.92%), nhưng bởi vì 0.008 (chênh lệch giữa 0.3 và 0.2992) thì nhỏ hơn giá trị mặc định 0.001 trong text box Maximum Change, nên Excel đã ngưng quá trịnh lặp lại và đưa ra lời giải.
Để đạt được lời giải chính xác = 30%, hay 0.3, bạn chỉ cần sửa lại con số trong Maximum Change là 0.0001.
2.5. Bài toán phân tích điểm hòa vốnBạn được yêu cầu tìm số sản phẩm tối thiểu phải bán được để làm cho lợi nhuận bằng 0 (nghĩa là tổng chi phí cho sản phẩm bằng doanh thu từ sản phẩm), hay nói cách khác, phải bán được ít nhất là bao nhiêu sản phẩm thì mới hòa vốn? Thiết lập một phương trình lợi nhuận có kết quả là 0 bằng cách thay đổi số sản phẩm sẽ bán ra, là một trong những thế mạnh của Goal Seek.
Để thử làm điều đó, chúng ta quay lại với bài toán "Tối ưu lợi nhuận sản phẩm". Nhưng trong trường hợp này, tôi sẽ giả sử rằng giá bán của sản phẩm đã được xác định là $47.95 (lời giải của bài toán trên). Để tìm ra số sản phẩm tối thiểu phải bán được để có lợi nhuận bằng 0, tôi thiết lập các tham số cho Goal Seek như sau (xem hình 15):
- Tham chiếu cho Set Cell là C13, là ô tính lợi nhuận theo số tiền.
- Giá trị cho To Value là 0 (tức là mục tiêu của lợi nhuận, trong trường hợp này)
- Tham chiếu cho By Changing Cell là C5 (số sản phẩm được bán)
Hình 15
Và lời giải cho chúng ta được Goal Seek tìm ra như minh họa ở hình 16: Phải bán được ít nhất là 46,468 sản phẩm, thì chúng ta mới hòa vốn.
Hình 16
2.6. Giải phương trình đại sốCác phương trình đại số thì thường không xuất hiện trong một mô hình kinh doanh, tuy nhiên, vì đây là một trong những khả năng của Goal Seek, nhất là đối với các phương trình có một biến, nên tôi đề cập đến vấn đề này tại đây.
Ví dụ, bạn cần phải giải một phương trình hơi khó chịu như sau:
Trước hết, bạn cần phải viết lại vế bên trái của phương trình theo kiểu mà Excel có thể hiểu được (với A2 là ô tượng trưng cho biến x):=(((3 * A2 - 8) ^ 2) * (A2 - 1)) / (4 * A2 ^ 2 - 5)
Tiếp theo, bạn dùng Goal Seek để xác lập mục tiêu cho công thức trên bằng 1 (vế phải của phương trình), bằng cách thay đổi giá trị của A2, như minh họa ở hình 17 sau đây:
Hình 17
Hình 18 minh họa kết quả, với giá trị tại ô A2 là lời giải cho nghiệm x của phương trình. Bạn chú ý, kết quả của phương trình (ô B2) không chính xác bằng 1. Như đã nói ở bài 2.4, nếu bạn cần kết quả chính xác hơn, bạn phải thay đổi giới hạn hội tụ (Maximum Change) của Excel. Ví dụ, bạn có thể thiết lập cho Maximum Change là 0.000001.
Hình 18
Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 2)
Nguồn: giaiphapexcel
Blog tổng hợp những kinh nghiệm được học, áp dụng và chia sẻ
