Data Analysis with Excel: What-If Analysis

Trong bài viết này chúng ta sẽ tìm hiểu tất tần tật chủ đề What-If Analysis trong Excel nhé!

Note: Bạn có thể kéo xuống dưới cùng để download tài liệu của bài viết này.

What-If Analysis

What-If Analysis trong Excel cho phép bạn thử các giá trị (scenarios) khác nhau cho các công thức. Ví dụ sau đây sẽ giúp bạn nắm vững What-If Analysis một cách nhanh chóng và dễ dàng.

Giả sử bạn sở hữu một cửa hàng sách và hiện đang có hơn 100 cuốn sách trong kho. Giả bạn bán một cuốn sách với certain % giá cao nhất là 50 đô và certain % giá thấp nhất là 20 đô.

Nếu bạn bán được 60% với giá cao nhất, ô D10 sẽ tính tổng lợi nhuận là 60 * $50 + 40 * $20 = $3800.

Tạo Scenarios (kịch bản) khác nhau

Nhưng nếu bạn bán 70% với giá cao nhất thì sao? Hay bạn bán 80% với giá cao nhất? Hoặc 90%, hoặc thậm chí 100%? Mỗi tỷ lệ phần trăm khác nhau sẽ cho ra một scenarios khác nhau. Bạn có thể sử dụng Scenario Manager để tạo các scenarios này.

Lưu ý: Bạn chỉ cần nhập một tỷ lệ phần trăm khác vào ô C4 để xem kết quả tương ứng với tình huống trong ô D10. Tuy nhiên, what-if analysis cho phép bạn dễ dàng so sánh kết quả của các scenarios khác nhau.

1. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

2. Click Scenario Manager.

Hộp thoại Scenario Manager xuất hiện.

3. Thêm một scenario bằng cách click vào Add.

4. Nhập tên (60% cao nhất), chọn ô C4 (% được bán với giá cao nhất) cho Changing cells và click OK.

5. Nhập giá trị tương ứng 0.6 và click OK một lần nữa.

6. Tiếp theo, thêm 4 scenarios khác (70%, 80%, 90% và 100%).

Cuối cùng, Scenario Manager của bạn phải nhất quán với hình bên dưới:

Lưu ý: để xem kết quả của một scenario,, hãy chọn scenario và click vào nút Show. Excel sẽ thay đổi giá trị của ô C4 tương ứng để bạn xem kết quả tương ứng trên sheet.

Scenario Summary

Để dễ dàng so sánh kết quả của các scenario này, bạn hãy thực hiện các bước sau.

1. Click vào nút Summary button trong Scenario Manager.

2. Tiếp theo, bạn chọn ô D10 (total profit) cho ô kết quả và click OK.

Kết quả:

Kết luận: nếu bạn bán 70% với giá cao nhất, bạn sẽ thu được tổng lợi nhuận là $4100, còn nếu bạn bán 80% với giá cao nhất, bạn sẽ thu được tổng lợi nhuận là $4400,...

Goal Seek

Lợi nhuận mong muốn mà bạn muốn thu được là $4700, vậy bạn cần phải bán bao nhiêu cuốn sách với giá cao nhất? Bạn có thể nhờ tính năng Goal Seek của Excel giúp tìm ra câu trả lời.

1. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

2. Click Goal Seek.

Hộp thoại Goal Seek xuất hiện.

3. Chọn ô D10.

4. Click vào hộp "To value" và nhập 4700.

5. Click vào "By changing cell" và chọn ô C4.

6. Click OK.

Kết quả: Bạn cần bán 90% số sách với giá cao nhất để thu được tổng lợi nhuận chính xác là $4700.

Data Tables

Thay vì tạo các scenario khác nhau, bạn có thể tạo data table để nhanh chóng thử các giá trị khác nhau cho các công thức. Bạn có thể tạo data table một biến hoặc data table hai biến.

Giả sử bạn sở hữu một cửa hàng sách và có 100 cuốn sách trong kho. Bạn bán certain % nhất định với giá cao nhất là 50 đô la và certain % nhất định cho giá thấp hơn là 20 đô la. Nếu bạn bán 60% với giá cao nhất, ô D10 bên dưới sẽ có công thức tính lợi nhuận là 60 * $50 + 40 * $20 = $3800.

One Variable Data Table

Để tạo data table một biến, bạn hãy thực hiện các bước sau.

1. Chọn ô B12 và nhập = D10 (tham khảo ô total profit).

2. Nhập các tỷ lệ phần trăm khác nhau vào column A.

3. Chọn phạm vi A12: B17.

Chúng ta sẽ tính được tổng lợi nhuận nếu bạn bán 60% cho giá cao nhất hoặc 70% cho giá cao nhất,...

4. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

5. Click Data Table.

6. Click vào hộp "Column input cell" (tỷ lệ phần trăm nằm trong một cột) và chọn ô C4.

Chúng ta chọn ô C4 vì tỷ lệ phần trăm tham chiếu đến ô C4 (% được bán với giá cao nhất) cùng với công thức trong ô B12. Excel hiện biết rằng nó nên thay thế ô C4 bằng 60% hoặc 70% để tính tổng lợi nhuận.

Lưu ý: đây là data table một biến nên chúng ta để trống ô nhập Row.

7. Click OK.

Kết quả.

Kết luận: nếu bạn bán 60% với giá cao nhất, bạn thu được tổng lợi nhuận là $3800, nếu bạn bán 70% với giá cao nhất, bạn sẽ thu được tổng lợi nhuận là $4100,...

Lưu ý: formula bar chỉ ra rằng các ô chứa công thức array. Do đó, bạn không thể xóa một kết quả duy nhất. Để xóa kết quả, hãy chọn phạm vi B13: B17 và nhấn Delete.

Two Variable Data Table

Để tạo một Data Table hai biến, bạn hãy thực hiện các bước sau.

1. Chọn ô A12 và nhập = D10 (tham khảo ô total profit).

2. Nhập các đơn vị lợi nhuận khác nhau (giá cao nhất) vào row 12.

3. Nhập các tỷ lệ phần trăm khác nhau vào column A.

4. Chọn phạm vi A12: D17.

Chúng ta sẽ tính toán tổng lợi nhuận cho các kết hợp khác nhau của "unit profit (highest price)" và "% sold for the highest price".

5. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

6. Click Data Table.

7. Click vào "Row input cell" (đơn vị lợi nhuận nằm trong một hàng) và chọn ô D7.

8. Click vào "Column input cell" (phần trăm nằm trong một cột) và chọn ô C4.

Chúng ta chọn ô D7 vì lợi nhuận đơn vị tham chiếu đến ô D7. Tương tự vậy, chúng ta chọn ô C4 vì tỷ lệ phần trăm tham chiếu đến ô C4. Cùng với công thức trong ô A12, Excel hiện biết rằng nó nên thay thế ô D7 bằng $50 và ô C4 bằng 60% để tính tổng lợi nhuận, thay ô D7 bằng $50 và ô C4 bằng 70% để tính tổng lợi nhuận,...

9. Click OK.

Kết quả:

Kết luận: nếu bạn bán 60% với giá cao nhất, với lợi nhuận đơn vị là $50, bạn thu được tổng lợi nhuận là $3800, nếu bạn bán 80% với giá cao nhất, với lợi nhuận đơn vị là $60, bạn thu được tổng lợi nhuận là $5200,...

Lưu ý: formula bar chỉ ra rằng các ô chứa công thức array. Do đó, bạn không thể xóa một kết quả duy nhất. Để xóa kết quả, hãy chọn phạm vi B13: D17 và nhấn Delete.

Goal Seek

Nếu bạn biết kết quả bạn muốn từ một công thức, hãy sử dụng Goal Seek trong Excel để tìm giá trị đầu vào và tạo ra kết quả với công thức này.

Ví dụ 1 về Goal Seek

Sử dụng Goal Seek trong Excel để tìm điểm trong bài kiểm tra thứ tư có final grade. là 70.

1. Công thức trong ô B7 tính final grade.

2. Điểm của bài kiểm tra thứ tư trong ô B5 là ô input.

3. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

4. Click Goal Seek.

Hộp thoại Goal Seek xuất hiện.

5. Chọn ô B7.

6. Click vào "To value" và nhập 70.

7. Click vào "By changing cell'' và chọn ô B5.

8. Click OK.

Kết quả: Điểm 90 trong kỳ thi thứ tư tạo ra điểm cuối cùng là 70.

Ví dụ 2 về Goal Seek

Sử dụng Goal Seek trong Excel để tìm số tiền cho vay tạo ra monthly payment là $1500.

1. Công thức trong ô B5 tính toán monthly payment.

Giải thích: chức năng PMT tính toán khoản thanh toán cho một khoản vay. Nếu bạn chưa bao giờ nghe nói về chức năng này trước đây thì không sao cả. Số tiền vay càng cao thì số tiền phải trả hàng tháng càng cao. Giả sử, bạn chỉ có thể chi trả $1500 một tháng vậy thì số tiền vay tối đa của bạn là bao nhiêu?

2. Số tiền cho vay trong ô B3 là ô input.

3. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

4. Click Goal Seek.

Hộp thoại Goal Seek xuất hiện.

5. Chọn ô B5.

6. Click vào hộp "To value" và nhấn -1500 (negative, bạn đang thanh toán tiền).

7. Click vào hộp 'By changing cell' và chọn ô B3.

8. Click OK.

Kết quả: Khoản vay $250,187 tạo ra khoản thanh toán hàng tháng là $1500.

Goal Seek Precision

Goal seek trả về các giải pháp gần đúng. Bạn có thể thay đổi cài đặt lặp lại trong Excel để tìm ra giải pháp chính xác hơn.

1. Công thức trong ô B1 tính bình phương giá trị trong ô A1.

2. Sử dụng goal seek để tìm giá trị đầu vào tạo ra kết quả công thức là 25.

Kết quả: Excel trả về một giải pháp gần đúng.

3. Trên tab File, click Options, Formulas.

4. Trong tùy chọn Calculation, hãy giảm giá trị Maximum Change bằng cách insert một số zeros. Giá trị mặc định là 0,001.

5. Click OK.

6. Sử dụng Goal Seek một lần nữa. Excel sẽ trả về một giải pháp chính xác hơn.

Tìm hiểu thêm về Goal Seek

Có nhiều vấn đề mà Goal Seek không thể giải quyết. Goal Seek yêu cầu một ô đầu vào và một ô đầu ra (công thức). Sử dụng Solver trong Excel để giải quyết các vấn đề với nhiều ô input. Đôi khi bạn cần bắt đầu với một giá trị input khác để tìm ra giải pháp.

1. Công thức trong ô B1 dưới đây cho kết quả là -0,25.

2. Sử dụng Goal Seek để tìm giá trị input tạo ra kết quả công thức là +0,25.

Kết quả: Excel không tìm ra được giải pháp.

3. Click Cancel.

4. Bắt đầu với giá trị input lớn hơn 8.

5. Sử dụng lại Goal Seek lần nữa để Excel tìm ra giải pháp.

Giải thích: y = 1 / (x - 8) không liên tục tại x = 8 (không chia hết cho 0). Trong ví dụ này, Goal seek không thể tiếp cận một phía của trục x (x> 8) nếu nó bắt đầu ở phía bên kia của trục x (x <8) hoặc ngược lại.

Quadratic Equation

Quadratic Equation (Phương trình bậc hai) có dạng ax² + bx + c = 0 trong đó a ≠ 0. Một phương trình bậc hai có thể được giải bằng cách sử dụng công thức bậc hai. Bạn cũng có thể sử dụng tính năng Goal Seek của Excel để giải phương trình bậc hai.

1. Ví dụ, chúng ta có công thức y = 3x² - 12x + 9.5 Thật dễ dàng để tính y cho bất kỳ x nào cho trước. Với x = 1, y = 0,5

2. Với x = 2, y = -2.5

3. Nhưng nếu chúng ta muốn biết x với bất kỳ y nào cho trước thì sao? Ví dụ: y = 24,5. Ta cần giải phương trình 3x² - 12x + 9.5 = 24.5. Ta có thể giải phương trình bậc hai này bằng cách sử dụng công thức bậc hai.

3x² - 12x -15 = 0
a = 3, b = -12, c = -15
D = b²- 4ac = (-12)² - 4 * 3 * -15 = 144 + 180 = 324

4. Bạn có thể sử dụng tính năng Goal Seek của Excel để có được kết quả chính xác giống nhau. Trên tab Data, trong nhóm Forecast, click What-If Analysis.

5. Click Goal Seek.

Hộp thoại Goal Seek xuất hiện.

6. Chọn ô B2.

7. Click vào hộp "To value" và nhập 24.5

8. Click vào hộp "By changing cell" và chọn ô A2.

9. Click OK.

Kết quả:

Lưu ý: Excel trả về giải pháp x = 5. Excel sẽ tìm ra giải pháp khác nếu bạn bắt đầu với giá trị x gần với x = -1. Ví dụ: nhập giá trị 0 vào ô A2 và lặp lại các bước từ 5 đến 9. Để tìm nghiệm nguyên, đặt y = 0 và giải phương trình bậc hai 33x² - 12x + 9.5 = 0. Trong trường hợp này, đặt "To value" thành 0.

Tài liệu của series

Để chuẩn bị cho việc thực hành, bạn tải tài liệu của series bằng cách điền vào form bên dưới nhé!