Cách cài đặt và sử dụng Goal Seek trong Google sheet

Goal Seek for Sheets là một tiện ích được xây dựng cho Google Sheets, giúp bạn tìm kiếm những mục tiêu chưa được xác định. Giúp bạn dễ dàng giải quyết những bài toán phức tạp như xác định giá bán ra tối thiểu để hòa vốn, lãi suất và số tiền phải đóng cho khoản trả góp,...

Trong bài viết này, Gitiho sẽ hướng dẫn bạn từng bước cài đặt và sử dụng Goal Seek trong Google Sheets. Để bạn có thể dễ dàng theo dõi các thao tác có trong hướng dẫn này, chúng tôi đã đính kèm một trang tính với đầy đủ các ví dụ ngay bên dưới.

Goal Seek là gì?

Goal Seek  là một kỹ thuật cực kỳ mạnh mẽ và hữu ích trong phân tích dữ liệu. Trong kỹ thuật này, bạn xác định trước mục tiêu cần đạt được (ví dụ: hòa vốn, bán được 10 ngàn sản phẩm, tiết kiệm 1 triệu đô la) và để máy tính tìm xem giá trị đầu vào nào sẽ đưa bạn đến mục tiêu đó (ví dụ mời 500 người tham dự, đầu tư 1000 triệu đồng cho sản phẩm mới, tiết kiệm 8 triệu động cho kế hoạch nghỉ hưu của mình).

Để sử dụng tiện ích Goal Seek, bạn cần có ba thành phần sau

1. Biến đầu vào chưa biết
2. Phương trình hoặc phép tính được thực hiện trên các biến đầu vào để có đầu ra
3. Đầu ra đã biết

Thuật toán Goal Seek thực hiện một loạt các phép tính "what-if" bằng cách cắm các giá trị đầu vào khác nhau. Mỗi phỏng đoán (hy vọng) sẽ ngày càng gần hơn với giải pháp.

Ví dụ: một trường hợp sử dụng cổ điển của Goal Seek là xác định số lượng hàng bán cần thiết để hòa vốn, với các biến khác như chi phí cố định,…

Cách dùng Goal Seek để tính giá hòa vốn

Hãy tưởng tượng bạn đang điều hành một hội nghị hàng năm dành cho các chuyên gia về Google Sheets.

Bạn đã chọn được một địa điểm tuyệt vời có sức chứa 500 và tự tin rằng mình có thể lấp đầy nó. Bạn đã xác định được tổng chi chí, bao gồm các khoản như phí thuê phòng, chi phí ăn uống, chi phí quảng bá hội nghị,... Bên cạnh đó, bạn cũng thỏa thuận trả 1.500 đô la cho 15 chuyên gia hàng đầu về Google Sheets đến tham dự và chia sẽ về những kinh nghiệm và phát kiến mới nhất trong việc phát triển Google Sheets.

Bạn phải tính mức phí đăng ký tham dự hội nghị này là bao nhiêu để đảm bảo chi trả được toàn bộ các mục trên?

Đây là một bài toán phân tích chi phí hòa vốn cổ điển mà Tiện ích bổ sung Goal Seek có thể giúp bạn giải quyết.

Thiết lập Sheets

Đầu tiên, bạn chỉ cần thêm tất cả các biến đã biết vào một Sheets, như sau:

Đây là những biến số mà bạn biết khi lên kế hoạch tổ chức hội nghị.

Tiếp theo, bạn hãy thêm một dòng cho phí đăng ký cho mỗi người tham dự, nhưng hãy đặt nó thành 0 ngay bây giờ. Chúng ta sẽ đánh dấu ô đó thành  màu vàng, và đây là nơi mà Goal Seek sẽ giúp chúng ta giải quyết.

Cuối cùng, hãy thêm một dòng lợi nhuận, có công thức là doanh thu  (# người tham dự * phí đăng ký) trừ đi Chi phí (chi phí cố định + (# diễn giả * phí diễn giả)):

= ( B7 * B8 ) - ( B4 + ( B5 * B6 ) )

Tất nhiên, ban đầu, lợi nhuận của bạn là - 47.500 đô la vì chưa có người tham dự và do đó doanh thu bằng 0.

Đã đến lúc sử dụng Goal Seek và để nó tính phí đăng ký hòa vốn cho chúng ta.

Làm cách nào để bạn thêm Goal Seek trong Google Sheets?

Goal Seek là một Add-on, nghĩa là bạn cần thêm nó vào Google Sheets của mình trước mới có thể sử dụng được.

Đầu tiên, bạn cần tìm kiếm "Goal Seek" trong Add-Ons marketplace, bên trong menu Add-ons > Get add-ons

Trang thông tin chính thức về Tiện ích bổ sung của Google sẽ xuất hiện. Hãy bấm để cài đặt. Tiện ích Goal Seek  sẽ nhanh chóng được cài đặt trong Sheets của bạn

Cách sử dụng Goal Seek

Mở thanh bên Goal Seek: Add-ons > Goal Seek > Open

Bạn cần nhập vào đó các thông tin sau:

• Set Cell
• To value
• By changing cell

Ở ví dụ bên trên, bạn muốn biết mức giá phải trả để hòa vốn. Nói cách khác, giá vé tham dự tối thiểu để đảm bảo lợi nhuận của bạn là 0 đồng và bạn không mất bất kỳ khoản tiền nào cho hội nghị này. Ba thông tin chính, chúng ta cần thiết lập trong Goal Seek sẽ là:

1. Set Cell

Trong phần Set Cell này, chúng ta chọn ô mà chúng ta muốn chỉ định giá trị - hay mục tiêu cần hướng tới. Đây là ô có công thức tính toán.

Đó là ô có công thức tính toán trong.

• Khi thanh bên Goal Seek mở, hãy nhấp vào ô có công thức, đó là ô B10 trong ví dụ này (1).
• Thêm tham chiếu đó vào  Goal Seek bằng cách nhấp vào biểu tượng lưới bên cạnh hộp Set cell (2). Thao tác này sẽ giúp tự động điền B10 vào Goal Seek.

2. To value

Tiếp theo, nhập giá trị của đầu ra bạn muốn đạt được vào hộp “Set cell”.

Trong ví dụ này, chúng ta sẽ đặt giá trị lợi nhuận thành 0, vì vậy bạn chỉ cần nhập 0 vào hộp này.

3. By changing cell

Chúng ta muốn thay đổi biến đầu vào nào để giải phương trình trên?

Trong trường hợp này, đó là phí đăng ký. Đây chính là biến số mà chúng ta đang cố gắng tìm ra, sao cho lợi nhuận là 0 đô la và hòa vốn.

Chọn ô chứa biến này và sau đó nhấp vào biểu tượng lưới bên cạnh trường nhập để tự động điền biến này.

Khi bạn đã điền cả ba mục nhập cho ứng dụng Goal Seek, nút Solve sẽ chuyển sang màu xanh lam và hoạt động. Để tiếp tục thực hiện, bạn hãy nhấm vào nút này.

Giá trị phí đăng ký sẽ bắt đầu thay đổi nơi khi máy tính thử các dự đoán khác nhau để xem điều gì lợi nhuận về gần 0.

Cuối cùng giải pháp sẽ được tìm ra và bạn sẽ nhận được thông báo hoàn tất.

Trong ví dụ này, chúng ta thấy rằng phí đăng ký hòa vốn là $ 94,99999 đô la, hoặc 95 đô. 

Kiểm tra thủ công

Để đảm bảo kết quả trên là chính xác, chúng ta sẽ thử kiểm tra lại kết quả trên. Trong ví dụ này, bạn có thể tính toán lại kết quả khá đơn đơn.

Chi phí cần để điều hành hội nghị gồm:

$25,000 chi phí cố định + ( 15 diễn giả*$1,500 ) = 25,000 + ( 15 * 1,500 ) = $47,500

Và doanh thu sẽ được tính toán bằng công thức sau:

500 người tham dự * $94.99 phí tham dự (số mà Goal Seek tính ra) = $47,499.99

Sự khác biệt ở đây chỉ đơn giản là làm tròn. Như vậy, kết quả Goal Seek đưa ra thực sự chính xác, giúp bạn xác định được mức giá vé tham dự hội nghị để đảm bảo hòa vốn.

Bây giờ, chúng ta hãy cùng tìm hiểu thêm một số ví dụ thú vị khác.

Cách dùng Goal Seek để tính số người tham dự tối thiểu

Trong ví dụ hội nghị ở trên, thay vì đã biết có bao nhiêu người tham dự, giả sử bạn đã xác định được phí tham gia hội nghị là bao nhiêu.

Bạn dự định thu 299 đô la cho mỗi người đăng ký tham dự. Bây giờ, chúng ta sẽ cần tính toán xem nên có ít nhất bao nhiêu thính giả để hòa vốn.

"By changing cell" trong trường hợp này là số người tham dự, chứ không phải phí đăng ký.

Thiết lập sẽ là:

Công thức trong ô B10 không thay đổi so với ví dụ ban đầu. Nó vẫn là:

= ( B7 * B8 ) - ( B4 + ( B5 * B6 ))

Cài đặt Goal Seek là:

Bấm vào nút Solve chạy Goal Seek và kết quả là 158,86.

Nói cách khác, bạn chỉ cần có 159 người tham dự trả phí đăng ký 299 đô la để hòa vốn.

Cách dùng Goal Seek để tính số tiền trả góp

Bây giờ, chúng ta hãy thử một ví dụ khác. Giả sử bạn đang tìm mua một ngôi nhà mới. Chỉ có khả năng thanh toán nhiều nhất 1.500 đô la mỗi tháng.

Các biến khác được biết đến trong trường hợp này là: kỳ hạn 30 năm với lãi suất hàng năm là 4,5%. Số tiền tối đa bạn có thể vay là bao nhiêu? Goal Seek có thể giúp bạn tìm thấy câu trả lời.

Trước tiên, hãy thiết lập Sheets với các biến đã biết trong Sheets của bạn:

Phương thức thanh toán, bạn sử dụng hàm PMT trong Google Sheets ở ô B8:

= -PMT( B6 / 12 , B5 , B7 )

Trong Goal Seek, đặt "Set cell" thành ô B8.

Đặt “To Value” thành 1.500 đô la (khoản thanh toán hàng tháng tối đa có thể chấp nhận).

Đặt “By Changing Cell” thành Số tiền đã vay trong ô B7 (hiện tại là $ 0,00).

Nhấp vào Solve và để Goal Seek tìm giải pháp phù hợp.

Thuật toán sẽ duyệt qua các giải pháp có thể cho đến khi nó ổn định trên một giải pháp thỏa mãn với cài đặt dung sai (độ chính xác) của bạn.

Trong trường hợp này, số tiền tối đa bạn có thể vay là $ 296.041,75.

Cách dùng Goal Seek để lập kế hoạch nghỉ hưu

Giả sử bạn muốn nghỉ hưu với số tiền 1,5 triệu đô la trong thời gian 40 năm. Bạn tự tin nhận được lợi tức 5% từ các khoản đầu tư của mình.

Khoản tiết kiệm hàng năm bạn cần thực hiện mỗi năm để đạt được mục tiêu này là bao nhiêu? Hãy thử dùng Goal Seek để tìm hiểu.

Trước tiên, hãy thiết lập Sheets với các biến đã biết trong Sheets của bạn:

Bạn sử dụng hàm FV để tính toán số tiền nghỉ hưu trong ô Retirement pot, trong Google Sheets ở ô B7:

= FV( B5 , B4 , -B6 , 0 , 0 )

Trong Goal Seek, đặt "Set cell" thành phương trình trong ô B7.

Đặt “To Value” thành $ 1.500.000 (số tiền hưu trí mục tiêu của bạn).

Đặt “By Changing Cell” thành ô B6 (hiện tại là $ 0,00).

Nhấp vào Solve và để Goal Seek tìm ra giải pháp của bạn.

Trong trường hợp này, bạn cần tiết kiệm 12.417 đô la mỗi năm để đạt được mục tiêu có số tiền hưu trí là 1,5 triệu đô la.

Các tính năng hữu ích khác của Goal Seek

Tất cả các tính năng hữu ích này đều được tìm thấy trong thanh bên bên dưới phần đầu vào cho các biến Goal Seek.

1. Options

Trong menu Options, bạn có thể điều chỉnh cài đặt mặc định cho Goal Seek. Bạn có thể thay đổi:

1. Số lần lặp tối đa
2. Dung sai (độ chính xác bạn cần)
3. Giới hạn thời gian tối đa cho quy trình, tính bằng giây.

Tốt nhất là bạn nên để nguyên thiết lập mặc định, nhưng bạn cũng có thể nghiên cứu thêm các tùy chọn này để sử dụng nếu cần.

2. Solve Status

Hộp Solve Status hiển thị những thông tin hữu ích về giải pháp Goal Seek hiện tại.

Nó cho bạn biết khi nào thuật toán kết thúc, trạng thái cuối cùng là gì, nó cần bao nhiêu lần lặp và mất bao lâu.

3. History

Bạn có thể dễ dàng truy cập các lần chạy trước của Goal Seek trong menu History.

Sử dụng menu thả xuống để chọn giải pháp trước theo thời gian ghi nhận.

4. Error Messages

Đôi khi Goal Seek không tìm ra giải pháp, có thể là do các phỏng đoán của máy tính liên xa hơn so với giải pháp thực tế. Nếu điều này xảy ra, bạn sẽ thấy thông báo lỗi như sau:

Chúng tôi hy vọng, với những ví dụ cụ thể bên trên, bạn đã biết cách sử dụng tiện ích Goal Seek trong Google Sheets. Để không bỏ lỡ các thủ thuật tin học văn phòng hữu ích khác, hay tham gia cùng với Gitiho ngay hôm nay.