Cách dùng Goal Seek trong Excel: Hướng dẫn với ví dụ thực tế

Cách hoạt động của Goal Seek

Goal Seek được dùng khi bạn biết kết quả mong muốn từ một công thức nhưng chưa xác định được giá trị đầu vào cần để đạt được nó. Bạn làm ngược lại: đặt ra đầu ra mục tiêu và để Excel điều chỉnh biến đầu vào để tìm lời giải.

Ứng dụng phổ biến của Goal Seek

Dù Goal Seek được dùng trong nhiều lĩnh vực, các trường hợp phổ biến nhất là lập mô hình tài chính và bối cảnh học thuật. Một số ứng dụng bao gồm:

• Dự báo ngân sách: Goal Seek giúp xác định khoản tiết kiệm hoặc đầu tư cần có để đạt một mục tiêu tài chính. Ví dụ, nếu bạn muốn tiết kiệm một số tiền nhất định vào cuối năm, Goal Seek có thể tính khoản tiết kiệm hàng tháng cần thiết để đạt mục tiêu đó.
• Khoản trả nợ vay: Công cụ có thể tính số tiền vay bạn có thể chi trả dựa trên mức trả hàng tháng cố định. Nếu bạn có một khoản có thể trả mỗi tháng, Goal Seek có thể điều chỉnh tiền gốc để xem bạn có thể vay bao nhiêu.
• Phân tích hòa vốn: Doanh nghiệp có thể dùng Goal Seek để tìm sản lượng bán cần thiết để bù đắp chi phí và đạt điểm hòa vốn. Bằng cách đặt lợi nhuận về 0, Goal Seek có thể điều chỉnh số lượng bán hoặc giá để xác định điểm hòa vốn.
• Tính điểm số: Học sinh và giảng viên có thể dùng Goal Seek để xác định điểm cần đạt ở bài thi cuối kỳ nhằm đạt điểm môn học mong muốn. Ví dụ, nếu một sinh viên muốn biết cần bao nhiêu điểm ở bài cuối để đạt tổng kết 85%, Goal Seek có thể tính dựa trên điểm hiện tại và trọng số bài thi cuối.
• Mục tiêu GPA: Goal Seek cũng có thể xác định điểm cần trong các học phần sắp tới để đạt hoặc duy trì một mức GPA cụ thể. Chẳng hạn, nếu sinh viên muốn nâng GPA lên một mức nào đó, Goal Seek có thể xác định điểm cần đạt ở các học phần tương lai để chạm mục tiêu đó.

Giờ hãy khám phá các ví dụ thực tế trong Microsoft Excel.

Cách dùng Goal Seek trong Excel

Áp dụng Goal Seek cho mô hình dữ liệu của bạn chủ yếu gồm ba bước lặp lại.

Bước 1: Thiết lập dữ liệu

Bắt đầu bằng cách thiết lập dữ liệu và công thức theo cách Excel có thể hiểu. Đảm bảo công thức bạn muốn giải được liên kết đúng với ô đầu vào sẽ được điều chỉnh.

Dữ liệu mẫu cho phép tính Goal Seek. Ảnh: Tác giả.

Theo công thức trong hình trên, vì Kết quả C phụ thuộc vào Đầu vào A và Đầu vào B, chúng ta có thể thay đổi Đầu vào A hoặc B.

Bước 2: Chạy Goal Seek

Trước hết, chọn ô có công thức mà bạn muốn điều chỉnh kết quả (ví dụ C2). Vào thẻ Data, nhấp What-If Analysis, rồi chọn Goal Seek.

Chọn Goal Seek trong Excel. Ảnh: Tác giả.

Trong hộp thoại Goal Seek, chỉ định ô chứa công thức (Set cell), kết quả mong muốn (To value), và ô đầu vào sẽ thay đổi (By changing cell).

Hộp thoại Goal Seek. Ảnh: Tác giả.

Nhấp OK để Excel tìm giá trị đầu vào phù hợp.

Bước cuối của Goal Seek. Ảnh: Tác giả.

Ngoài ra, bạn có thể nhấp Cancel để hủy thao tác Goal Seek nếu muốn giữ nguyên các giá trị ban đầu. Điều này thường xảy ra khi chúng ta chưa hài lòng với thay đổi và muốn chạy một thao tác Goal Seek khác sau đó.

Bước 3: Phân tích kết quả 

Sau khi chạy Goal Seek, Excel tự động điều chỉnh ô đầu vào đã chọn để đạt đầu ra mục tiêu. Giá trị cập nhật trong ô đầu vào giờ có thể được phân tích để hiểu kết quả mô hình của bạn.

Kết quả Goal Seek. Ảnh: Tác giả.

Trong hình trên, bằng cách thay đổi Đầu vào B từ 100 lên 200, chúng ta đã đạt Kết quả C mục tiêu là 1000. Ba bước này tạo thành quy trình chuẩn để dùng Goal Seek cho nhiều mô hình dữ liệu khác nhau. 

Hãy xem các bước này áp dụng vào giải quyết bài toán thực tế như thế nào. Những ví dụ dưới đây cho thấy Goal Seek có thể giải hiệu quả các bài toán tài chính thường gặp, chẳng hạn xác định khoản trả nợ vay hoặc đạt mục tiêu doanh số.

Ví dụ Goal Seek trong Excel

Hãy xem một vài ví dụ về Goal Seek trong Excel mà bạn có thể gặp trong công việc. 

Ví dụ 1: Tính khoản trả nợ vay

Giả sử bạn dự định mua nhà và muốn xác định số tiền vay tối đa bạn có thể chi trả với khoản thanh toán hàng tháng 1.000 đô la. Dựa trên nghiên cứu về các ngân hàng, bạn đã có lãi suất và thời hạn vay trong đầu, nhưng cần tìm tiền gốc cần đề nghị. Bạn có thể dùng Goal Seek để giải.

Bước 1: Thiết lập dữ liệu 

Mở một trang tính Excel mới và tạo thiết lập như hình dưới. 

Tập dữ liệu khoản trả nợ vay. Ảnh: Tác giả.

Nhập lãi suất bạn đã tìm (ví dụ 5%) vào ô A2, thời hạn vay (ví dụ 30 năm) vào ô B2, và ước tính ban đầu cho số tiền vay (ví dụ 200.000 đô la) vào ô C2. Trong ô D2, nhập công thức tính khoản thanh toán hàng tháng bằng hàm PMT:

=PMT(B1/12, B2*12, -B3)Được hỗ trợ bởi

Công thức này chia lãi suất năm cho 12 (ra lãi suất tháng), nhân thời hạn vay với 12 (ra tổng số kỳ thanh toán), và dùng số tiền vay mang dấu âm để tính khoản trả hàng tháng.

Bước 2: Chạy Goal Seek 

Mục tiêu là tìm số tiền vay sao cho khoản trả hàng tháng là 1.000 đô la. Nói cách khác, chúng ta sẽ chỉnh ô C2 để D2 đặt về 1.000 đô la. Như đã học, chúng ta mở Goal Seek trong mục What-If Analysis ở thẻ Data để có hộp thoại sau.

Tùy chọn Goal Seek cho ví dụ khoản trả nợ vay. Ảnh: Tác giả.

Trong hộp thoại Goal Seek, chúng ta nhập:

• Set Cell: D2, ô khoản trả hàng tháng.

• To Value: Nhập 1000 (khoản trả hàng tháng mong muốn).

• By Changing Cell: Nhập C2 (số tiền vay).

Khi nhấp OK, Excel sẽ điều chỉnh số tiền vay trong ô C2 để xác định bạn có thể vay bao nhiêu trong khi giữ khoản trả hàng tháng ở mức 1.000 đô la.

Bước 3: Diễn giải kết quả 

Thao tác Goal Seek chạy và dừng ở hình dưới:

Kết quả Goal Seek cho ví dụ khoản trả nợ vay. Ảnh: Tác giả.

Sau khi nhấp OK, Excel sẽ hiển thị số tiền vay đã điều chỉnh ở ô C2. Đây là tiền gốc tối đa bạn có thể vay với lãi suất, thời hạn và khoản trả hàng tháng đã cho.

Số tiền vay đã điều chỉnh là 186.282 đô la. Chúng ta có thể chi trả mức vay tối đa này với khoản trả hàng tháng 1.000 đô la. Bạn có thể thử các con số khác và chạy Goal Seek nhiều lần.

Ví dụ 2: Đạt mục tiêu doanh thu

Giả sử bạn là quản lý bán hàng đặt mục tiêu doanh thu 500.000 đô la cho một sản phẩm cụ thể. Bạn đã biết giá bán mỗi đơn vị nhưng chưa rõ cần bán bao nhiêu đơn vị để đạt mục tiêu. Goal Seek có thể giúp bạn xác định chính xác số lượng cần bán.

Bước 1: Thiết lập dữ liệu  

Mở một trang tính mới và nhập dữ liệu như hình dưới. 

Tập dữ liệu mục tiêu doanh số. Ảnh: Tác giả.

Tại ô A2, chúng ta nhập giá bán mỗi đơn vị thực tế và ước lượng số lượng đơn vị ở ô B2. Nhập công thức sau để tính tổng doanh thu ở ô C2:

=A2 * B2Được hỗ trợ bởi

Công thức này nhân giá mỗi đơn vị với số lượng bán ra để tính tổng doanh thu.

Bước 2: Chạy Goal Seek  

Hãy mở Goal Seek trong mục What-If Analysis ở thẻ Data để có hộp thoại sau.

Tùy chọn Goal Seek cho ví dụ mục tiêu doanh thu. Ảnh: Tác giả.

Trong hộp thoại Goal Seek, chúng ta nhập:

• Set Cell: Gõ C2 (ô tổng doanh thu).

• To Value: Nhập 500000 (mục tiêu doanh thu mong muốn).

• By Changing Cell: Nhập B2 (số lượng đơn vị bán).

Excel sẽ điều chỉnh số lượng đơn vị bán trong ô B2 để xác định số lượng cần bán nhằm đạt doanh thu 500.000 đô la.

Bước 3: Diễn giải kết quả  

Thao tác Goal Seek chạy và dừng ở bước dưới đây:

Kết quả Goal Seek cho ví dụ mục tiêu doanh thu. Ảnh: Tác giả.

Khi bạn nhấp OK, Excel sẽ hiển thị số lượng đơn vị đã điều chỉnh ở ô B2. Kết quả là 2.000 đơn vị — bạn cần bán 2.000 đơn vị với giá 250 đô la mỗi đơn vị để đạt mục tiêu doanh thu 500.000 đô la. Nếu giá đơn vị hoặc mục tiêu doanh thu thay đổi sau này, bạn có thể chạy lại Goal Seek với các giá trị cập nhật.

Các lỗi thường gặp khi dùng Goal Seek và cách xử lý

Nếu xem lại hai ví dụ trên, chúng ta làm theo cùng một chuỗi bước để có kết quả. Tuy nhiên, một vài lỗi thường gặp có thể làm sai lệch phép tính khi dùng Goal Seek trong Excel. Hãy cùng xem.

1. Excel Goal Seek có thể không tìm được lời giải

Một vấn đề thường gặp là Goal Seek không tìm được lời giải đáp ứng mục tiêu. Điều này có thể xảy ra trong hai trường hợp:

• Lời giải không tồn tại.
• Lời giải không hội tụ trong số bước được định nghĩa.

Hãy hiểu điều này từ ví dụ mục tiêu doanh thu trước đó. Giả sử ta nhầm lẫn nhập giá mỗi đơn vị là 0 đô thay vì 250 đô. Dữ liệu như sau:

Tập dữ liệu mục tiêu doanh số có lỗi. Ảnh: Tác giả.

Chúng ta sẽ thực hiện các bước Goal Seek như thường lệ:

Goal Seek trên tập dữ liệu mục tiêu doanh số có lỗi. Ảnh: Tác giả.

Sau khi nhấp OK, điều gì xảy ra tiếp theo?

Lỗi Goal Seek có thể không tìm được lời giải. Ảnh: Tác giả.

Chúng ta thấy kết quả: “Goal Seek may not have found a solution.” Lỗi này xuất hiện vì phương trình không thể giải. Trước khi chạy Goal Seek, cần kiểm tra công thức và giá trị đầu vào để đảm bảo có thể giải được.

Nếu bạn chắc rằng lời giải tồn tại, có thể là Goal Seek không hội tụ trong 100 bước mặc định. Để giúp Goal Seek hội tụ đến lời giải:

1. Tăng Max Iterations: Giá trị lặp cao hơn cho phép Goal Seek chạy lâu hơn. Bạn có thể đổi tại File -> Options -> Formulas -> Max Iterations.
2. Bắt đầu với ước lượng tốt hơn: Nếu bạn đoán được khoảng giá trị đúng, hãy nhập giá trị ban đầu nằm trong khoảng đó.

Kiểm tra tính khả giải của phương trình và cho phép hội tụ trong số bước đã định sẽ tránh lỗi không tìm được lời giải.

2. Lỗi ô phải chứa một giá trị

Goal Seek yêu cầu ô được thay đổi phải là một giá trị và sẽ báo lỗi “Cell must contain a value” nếu không. Điều này thường xảy ra khi giá trị trong ô được dẫn xuất từ các ô khác thông qua công thức.

Hãy hiểu lỗi này bằng ví dụ mục tiêu doanh thu. Giả sử giá mỗi đơn vị được tính bằng cách chia tổng chi phí cho tổng số đơn vị. Thiết lập dữ liệu sẽ như sau:

Tập dữ liệu mục tiêu doanh số đã cập nhật. Ảnh: Tác giả.

Khi thử chạy Goal Seek, lỗi xuất hiện khi chúng ta chọn ô A2 cho tùy chọn By changing cell.

Lỗi ô phải chứa một giá trị. Ảnh: Tác giả.

Điều này xảy ra vì Goal Seek không thể chạy khi ô cần chỉnh không chứa một giá trị. Đảm bảo ô cần chỉnh chứa giá trị sẽ giúp tránh lỗi này.

Kết luận

Bài viết đã giới thiệu tới bạn một công cụ What-if Analysis thường dùng — Goal Seek. Qua nhiều kịch bản ví dụ, chúng ta học cách dùng trong Excel theo các bước đơn giản. Chúng ta cũng hiểu các lỗi thường gặp trong quá trình triển khai và cách tránh chúng.

Tôi khuyến khích bạn thử nghiệm Goal Seek trong các mô hình dữ liệu để làm quen với công cụ và phạm vi áp dụng. Nếu bạn muốn học thêm về lập mô hình tài chính trong Excel với các công cụ tương tự Goal Seek, bạn có thể xem khóa học dành cho người mới bắt đầu Financial Modelling in Excel.