Goal Seek, Solver Và What-If Analysis Trong Excel: Tìm Đáp Án Ngược

1. What-If Analysis là gì? #

What-If Analysis trả lời câu hỏi: "NẾU thay đổi giá trị này THÌ kết quả sẽ ra sao?" — Excel cung cấp 3 công cụ: Goal Seek (tìm ngược), Scenario Manager (so sánh kịch bản), Data Table (phân tích nhạy).

2. Goal Seek — Tìm đáp án ngược #

2.1. Bài toán #

Bạn biết KẾT QUẢ muốn, cần tìm GIÁ TRỊ ĐẦU VÀO.

Ví dụ: Cần lợi nhuận 500 triệu. Biết chi phí cố định. Doanh số tối thiểu bao nhiêu?

2.2. Cách dùng #

1. Tạo công thức: =Doanh_so - Chi_phi (ô C1)

2. Data → What-If Analysis → Goal Seek

3. Điền:

• Set cell: C1 (ô chứa kết quả)

• To value: 500000000 (kết quả mong muốn)

• By changing cell: A1 (ô doanh số cần tìm)

1. OK → Excel tự điều chỉnh A1 cho đến khi C1 = 500 triệu

2.3. Ví dụ: Tính lãi suất vay #

Bạn vay 1 tỷ, trả góp 36 tháng, muốn trả tối đa 35 triệu/tháng.

=PMT(B1/12, 36, -1000000000)   → Ô C1 (số tiền trả/tháng)

Goal Seek: Set C1 = 35000000, By changing B1 → tìm lãi suất tối đa.

2.4. Hạn chế Goal Seek #

• Chỉ thay đổi 1 ô input

• Chỉ tìm 1 kết quả cụ thể

• Không có constraints (ràng buộc)

3. Scenario Manager — So sánh kịch bản #

3.1. Bài toán #

So sánh 3 kịch bản: Lạc quan, Trung bình, Bi quan.

3.2. Cách dùng #

1. Data → What-If Analysis → Scenario Manager

2. Add → đặt tên "Lạc quan" → chọn ô thay đổi (B1:B3) → nhập giá trị

3. Add → "Trung bình" → nhập giá trị khác

4. Add → "Bi quan" → nhập giá trị khác

5. Show → xem từng kịch bản

6. Summary → tạo bảng so sánh tự động

3.3. Kết quả Summary #

4. Data Table — Phân tích nhạy #

4.1. Data Table 1 biến (chiều) #

Xem kết quả thay đổi khi 1 input thay đổi:

1. Cột A: các giá trị input (5%, 6%, 7%, 8%, 9%, 10%)

2. Ô B1: công thức gốc =PMT(A1/12, 360, -1000000000)

3. Chọn vùng A1:B6

4. Data → What-If Analysis → Data Table

5. Column input cell: A1

6. OK → Excel tính kết quả cho mọi giá trị input

4.2. Data Table 2 biến #

Ma trận kết quả khi 2 input thay đổi đồng thời:

Hàng = Lãi suất (5%-10%), Cột = Số tháng (12, 24, 36, 48, 60)

1. Góc trên trái: công thức PMT

2. Hàng đầu: số tháng

3. Cột đầu: lãi suất

4. Data Table → Row input = ô số tháng, Column input = ô lãi suất

5. OK → ma trận kết quả

5. Solver — Tối ưu hóa #

5.1. Cài đặt Solver #

File → Options → Add-ins → Manage: Excel Add-ins → Go → tick Solver Add-in

5.2. Bài toán ví dụ #

Tối đa hóa lợi nhuận, biết:

• 3 sản phẩm: A, B, C

• Mỗi SP có margin khác nhau

• Ràng buộc: nguyên vật liệu giới hạn, lao động giới hạn, sản lượng tối thiểu

5.3. Cách dùng #

1. Tạo model trên Excel:

• Ô quyết định: Số lượng sản xuất A, B, C

• Ô mục tiêu: Tổng lợi nhuận (=SUMPRODUCT)

• Constraints: NVL <= 1000kg, Lao động <= 500h

1. Data → Solver:

• Objective: ô tổng lợi nhuận

• To: Max

• By Changing: ô số lượng A, B, C

• Subject to: Add constraints

1. Solve → Excel tìm giá trị tối ưu

5.4. Solver Methods #

6. Ứng dụng thực tế #

6.1. Break-even Analysis #

Goal Seek: Set Lợi nhuận = 0, By changing Số lượng bán → tìm điểm hòa vốn.

6.2. Pricing Optimization #

Solver: Max doanh thu, ràng buộc giá >= giá vốn, cầu giảm khi giá tăng.

6.3. Resource Allocation #

Solver: Max output, ràng buộc ngân sách, nhân sự, thời gian → phân bổ nguồn lực tối ưu.

6.4. Loan Comparison #

Data Table 2 biến: so sánh PMT với nhiều lãi suất × nhiều kỳ hạn → chọn phương án tốt nhất.

7. Mẹo What-If Analysis #

1. Goal Seek cho câu hỏi đơn giản: "Cần X bao nhiêu để Y = Z?"

2. Scenario cho báo cáo: In summary table cho ban lãnh đạo

3. Data Table cho sensitivity: Thấy ngay input nào ảnh hưởng nhiều nhất

4. Solver cho tối ưu: Khi có ràng buộc → Solver là duy nhất

5. Save trước khi chạy: Goal Seek/Solver thay đổi giá trị ô → khó undo

8. Tổng kết #

What-If Analysis biến Excel từ "máy tính" thành "cỗ máy ra quyết định". Goal Seek tìm ngược, Scenario Manager so sánh, Data Table phân tích nhạy, Solver tối ưu — 4 công cụ giúp bạn trả lời mọi câu hỏi "nếu... thì..." mà kinh doanh cần.