Phân tích giả định là một cách tiếp cận mạnh mẽ để khám phá sự không chắc chắn và thử nghiệm các kịch bản khác nhau trước khi đưa ra các quyết định quan trọng. Nó giúp đạt được kết quả kinh doanh hoặc nghiên cứu, chẳng hạn như “Điều gì xảy ra nếu giá tăng?” hoặc “Chúng ta sẽ kiếm được bao nhiêu lợi nhuận nếu chi phí giảm?” Công cụ Phân tích What-If của Excel giúp bạn khám phá các khả năng khác nhau mà không cần viết lại công thức.
Trong hướng dẫn này, chúng tôi trình bày ba kỹ thuật phân tích What-If để dự báo và lập mô hình các kịch bản trong Excel.
Chúng ta sẽ khám phá ba kỹ thuật phân tích What-If cần thiết:
- Tìm kiếm mục tiêu: Làm ngược lại để tìm thông tin đầu vào cần thiết cho một kết quả cụ thể
- Bảng dữ liệu: Phân tích xem một hoặc hai biến số tác động như thế nào đến kết quả của bạn
- Trình quản lý kịch bản: Lưu và so sánh nhiều bộ giả định
1. Tìm kiếm mục tiêu:Tìm đầu vào cho kết quả mong muốn
Goal Seek lý tưởng cho các mô hình kỹ thuật đảo ngược. Nó điều chỉnh một giá trị đầu vào duy nhất để đạt được đầu ra cụ thể, giúp trả lời các câu hỏi như “Tôi cần giá trị đầu vào nào để đạt được kết quả mong muốn?”
Goal Seek sử dụng phép lặp để thay đổi một ô (đầu vào) cho đến khi ô phụ thuộc vào công thức (đầu ra) đạt đến giá trị mong muốn của bạn. Nó rất tốt cho những tối ưu hóa đơn giản nhưng bị giới hạn ở một biến.
Khi nào nên sử dụng tính năng Tìm kiếm mục tiêu:
- Xác định khối lượng bán hàng cần thiết để đạt được mục tiêu lợi nhuận
- Tính lãi suất cần thiết để đáp ứng các ràng buộc thanh toán khoản vay
- Tìm điểm hòa vốn cho sản phẩm hoặc dịch vụ
- Xác định số điểm cần thiết trong bài kiểm tra cuối kỳ để đạt được điểm nhất định
Hãy cùng tìm hiểu:“Chúng ta phải bán bao nhiêu sản phẩm để kiếm được 20.000 USD lợi nhuận?”
Các bước:
- Thiết lập mô hình của bạn trong Excel (ví dụ:nhập chi tiết sản phẩm và công thức lợi nhuận)
- Chèn công thức sau để tính lợi nhuận
- Đi tới Dữ liệu tab>> chọn Phân tích What-If>> chọn Tìm kiếm mục tiêu
- Trong hộp thoại:
- Đặt ô: Chọn ô đầu ra (ví dụ:E2, công thức lợi nhuận)
- Để đánh giá: Nhập sản lượng mục tiêu (ví dụ:lợi nhuận mục tiêu 20000)
- Bằng cách thay đổi ô: Chọn ô nhập để điều chỉnh (ví dụ:B2 , Số lượng đã bán)
- Nhấp vào OK
- Excel tự động lặp lại để điều chỉnh giá trị bán hàng cho đến khi đạt được lợi nhuận mục tiêu
Thông tin chi tiết: Sử dụng Tìm kiếm mục tiêu khi công thức của bạn hoạt động ngược lại so với mục tiêu đã biết (như điểm hòa vốn hoặc doanh số mục tiêu).
- Ưu điểm: Nhanh chóng và đơn giản đối với các bài toán một biến
- Nhược điểm: Không xử lý nhiều biến; có thể không hội tụ nếu mô hình phức tạp
2. Bảng dữ liệu:Xem một hoặc hai biến ảnh hưởng đến kết quả như thế nào
Bảng dữ liệu cho phép bạn xem việc thay đổi một hoặc hai biến đầu vào ảnh hưởng như thế nào đến kết quả của bạn, hiển thị tất cả các khả năng trong một bảng. Điều này tạo ra một phân tích độ nhạy cho thấy nhiều kết quả tiềm năng.
- Bảng một biến: Thay đổi một đầu vào trên một hàng hoặc cột; kiểm tra các giá trị khác nhau cho một đầu vào
- Bảng hai biến: Thay đổi hai đầu vào (một hàng, một cột) để tạo ma trận; tính toán lại mô hình cho từng kết hợp và giúp trực quan hóa xu hướng
Bảng dữ liệu một biến
Hãy xem các đơn giá khác nhau ảnh hưởng đến lợi nhuận như thế nào. Lợi nhuận thay đổi như thế nào nếu Đơn giá tăng từ $40 lên $70?
Các bước:
- Thiết lập mô hình của bạn
- Nhập các mức giá này theo chiều dọc vào một cột
- Trong ô liền kề với giá đầu tiên (ví dụ:B5), hãy tham chiếu ô kết quả lợi nhuận chính (ví dụ:E2)
- Chọn phạm vi bảng đầy đủ, bao gồm tiêu đề (ví dụ:A5:B12)
- Đi tới Dữ liệu tab>> chọn Phân tích What-If>> chọn Bảng dữ liệu
- Rời khỏi Ô nhập hàng trống
- Trong Ô nhập cột , chọn ô Đơn giá ban đầu (A2)
- Nhấp vào OK
- Excel tự động điền lợi nhuận cho từng mức giá
Giải thích:
- Cột kết quả thể hiện độ nhạy cảm của lợi nhuận với giá
- Giá cao hơn thường làm tăng lợi nhuận cho đến khi cầu giảm xuống (nếu được lập mô hình)
Bảng dữ liệu hai biến
Hãy xem:“Điều gì sẽ xảy ra nếu cả Đơn giá và Đơn vị bán khác nhau?”
Các bước:
- Nhập giá theo chiều dọc (cột bên trái) và số lượng bán theo chiều ngang (hàng trên cùng)
- Trong ô trên cùng bên trái của bảng (trên 40 và bên trái 800), tham chiếu ô kết quả lợi nhuận chính (ví dụ:nhập =E2 ở D5)
- Chọn toàn bộ bảng, bao gồm các tiêu đề (ví dụ:D5:H9)
- Đi tới Dữ liệu tab>> chọn Phân tích What-If>> chọn Bảng dữ liệu
- Trong Ô nhập hàng , chọn Số lượng đã bán (B2)
- Trong Ô nhập cột , chọn Đơn giá (A2)
- Nhấp vào OK
- Excel tạo ma trận đầy đủ hiển thị lợi nhuận theo mọi kết hợp giá-doanh số
Lưu ý: Đối với Bảng dữ liệu, các ô nhập phải nằm trên cùng một trang tính với Bảng dữ liệu. Công thức dùng để điền vào bảng có thể nằm trên một trang tính khác bằng cách tham chiếu.
Thông tin chi tiết: Bảng dữ liệu là công cụ tốt nhất để kiểm tra độ nhạy nhanh, như độ co giãn của giá hoặc phân tích khối lượng-lợi nhuận.
- Ưu điểm: Trực quan hóa phạm vi; xử lý tối đa hai biến một cách hiệu quả
- Nhược điểm: Có thể tính toán chuyên sâu cho các bảng lớn; kết quả là tĩnh cho đến khi được làm mới
3. Trình quản lý kịch bản:So sánh nhiều mô hình “Nếu-thì”
Trình quản lý Kịch bản cho phép bạn lưu và so sánh các tập hợp giá trị đầu vào (kịch bản) khác nhau mà không cần ghi đè công thức. Bạn có thể nhanh chóng chuyển đổi giữa chúng hoặc tạo báo cáo tóm tắt so sánh tất cả các kịch bản cạnh nhau. Nó phù hợp với các tình huống phức tạp có nhiều biến số, chẳng hạn như dự báo trường hợp tốt nhất/xấu nhất.
Khi nào nên sử dụng:
- So sánh các dự đoán kinh doanh trong trường hợp tốt nhất, trường hợp xấu nhất và có khả năng xảy ra nhất
- Đánh giá các lựa chọn chiến lược khác nhau với nhiều giả định thay đổi
- Trình bày nhiều tình huống cho các bên liên quan theo một hình thức rõ ràng, có tổ chức
- Theo dõi các phiên bản khác nhau của mô hình theo thời gian
Hãy lập dự báo kinh doanh với ba kịch bản:Trường hợp tốt nhất, Trường hợp cơ bản và Trường hợp xấu nhất.
Các bước:
- Đi tới Dữ liệu tab>> chọn Phân tích What-If>> chọn Trình quản lý kịch bản
- Nhấp vào Thêm
- Tên kịch bản: Đặt tên cho kịch bản đầu tiên (ví dụ:Trường hợp tốt nhất)
- Trong Thay đổi ô , chọn Đơn giá (A2), Số lượng đã bán (B2), Chi phí cố định (C2) và Chi phí biến đổi (D2)
- Nhấp vào OK
- Nhập các giá trị cho Trường hợp tốt nhất kịch bản
- Đơn giá: 65
- Số lượng đã bán: 1600
- Chi phí cố định: 8500
- Chi phí biến đổi: 25
- Nhấp vào OK
- Nhấp vào Thêm một lần nữa và lặp lại cho “Trường hợp cơ bản”
- Nhấp vào Thêm một lần nữa và lặp lại cho “Trường hợp xấu nhất”
- Chọn kịch bản bất kỳ>> nhấp vào Hiển thị để áp dụng các giá trị ngay lập tức
- Nhấp vào Tóm tắt để hiển thị tất cả các trường hợp
- Chọn Tóm tắt kịch bản
- Chọn ô lợi nhuận làm “Ô kết quả” (ví dụ:E2)
- Nhấp vào OK
- Excel tạo bảng Tóm tắt tình huống mới so sánh tất cả kết quả của tình huống
Thông tin chi tiết: Bạn có thể sử dụng phân tích What-If của Trình quản lý Kịch bản để dự báo và lập mô hình các kịch bản trong Excel. Đây là một trong những công cụ ra quyết định tốt nhất (ví dụ:đầu tư, lập ngân sách hoặc đánh giá rủi ro), trong đó nhiều giả định phải được lưu và xem xét cùng nhau.
- Ưu điểm: Quản lý nhiều biến; dễ dàng so sánh với các báo cáo
- Nhược điểm: Thiết lập thủ công; giới hạn ở 32 ô thay đổi cho mỗi kịch bản
Tải xuống sổ tay thực hành
Kết luận
Hướng dẫn này cho thấy cách sử dụng ba kỹ thuật phân tích What-If để dự báo và lập mô hình các kịch bản trong Excel. Phân tích Điều gì xảy ra nếu biến sự không chắc chắn từ rào cản thành cơ hội bằng cách khám phá các khả năng khác nhau với Tìm kiếm mục tiêu, Bảng dữ liệu và Trình quản lý kịch bản. Cho dù bạn đang lập kế hoạch ngân sách kinh doanh, đánh giá các lựa chọn đầu tư hay lập mô hình kết quả dự án, những công cụ Excel này sẽ giúp bạn hiểu những thay đổi trong giả định ảnh hưởng đến kết quả của bạn như thế nào.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!