E19: Phân tích độ nhạy của dữ liệu với Scenario Manager

  • Post author:
  • Post category:Excel
  • Post comments:0 Comments
  • Reading time:5 mins read

Với data table chúng ta có thể phân tích được độ nhạy của dữ liệu dựa vào sự thay đổi của 1 tham số hoặc 2 tham số. Trong trường hợp có nhiều hơn 2 tham số thì chúng ta không thể sử dụng data table, khi đó công cụ Scenario Manager của What-If Analysis có thể sử dụng. Xét bài toán kinh doanh bán hàng dưới đây.

  • Mức thuế taxrate=0.4 (40%)
  • Số sản phẩm bán ra năm thứ 1 Year1Sales=12000
  • Tỉ lệ bán tăng trưởng hàng năm Sales growth=0.05 (5%)
  • Giá mỗi sản phẩm của năm thứ 1 Year1price=$7.50
  • Chi phí trên mỗi sản phẩm năm thứ 1 Year1cost=$6.00
  • Lãi suất hàng năm intrate=0.15 (15%)
  • Chi phí tăng mỗi năm costgrowth=0.05 (5%)
  • Giá sản phẩm tăng mỗi năm pricegrowth=0.03 (3%)

Bạn quan tâm tới giá trị hiện tại thuần NPV sau 5 năm. Bằng cách thiết lập bảng phân tích dựa vào số liệu kinh doanh của năm thứ 1 như bảng dưới. Trong đó

  • Year: từ 1 đến 5 thể hiện số liệu qua các năm
  • Unit Sales: doanh số bán được
  • Unit price: Giá bán cho mỗi sản phẩm
  • Unit cost: chi phí trên mỗi sản phẩm
  • Revenues: tổng doanh thu của năm
  • Costs: tổng chi phí của năm
  • Before tax profit: lợi nhuận trước thuế thu được
  • Tax: thuế phải trả trong năm
  • Aftertax profit: lợi nhuận sau thuế còn lại
  • NPV B19=$35,492.08 Giá trị hiện tại thuần sau 5 năm kinh doanh

Tuy nhiên giá trị NPV này dựa trên sự ổn định về doanh số bán, cũng như mức tăng trưởng về doanh số bán ra cũng như giá hàng năm. Do đó bạn muốn đặt giả thiết dựa trên các kịch bản bán hàng của năm thứ 1 như sau:

  • Trường hợp tốt nhất: bán được 20000 sản phẩm, với giá bán $10.00, tỉ lệ bán hàng năm tăng 20%
  • Trường hợp kỳ vọng: bán được 10000 sản phẩm, với giá bán $7.50, tỉ lệ bán hàng năm tăng 10%
  • Trường hợp xấu nhất: bán được 5000 sản phẩm, với giá bán $5.00, tỉ lệ bán hàng năm tăng 2%

Với bài toán này, có 3 tham số đầu vào biến đổi quyết định giá trị hiện tại thuần, đó là: doanh số bán, tỉ lệ tăng trưởng và giá bán, do đó chúng ta sử dụng công cụ What-if | Scenario Manager như sau

  1. Chọn Data | What-If | Scenario Manager
  2. Trong cửa sổ Scenario Manager chọn Add
  3. Trong cửa sổ Add Scenario. Cửa sổ này sẽ định nghĩa đầu vào cho từng kịch bản
    1. Scenario name: Best. Trường hợp này là trường hợp tốt nhất
    2. Changing cells: C2:C4. Các biến đầu vào mong muốn (giá trị sẽ nhập ở bước sau). Các biến tham số đầu vào ở đây là Year1sales, sales_growth, Year1price
    3. Bấm OK
  4. Trong cửa sổ Scenario Values. Cửa sổ này sẽ nhập giá trị đầu vào cho kịch bản được định nghĩa ở bước trước
    1. Year1sales: 20000
    2. Sales_growth: 0.2 (20%)
    3. Year1price: 10
  5. Lặp lại các bước từ 2 đến 4 cho các kịch bản với số liệu lần lượt như sau

    Most likely | C2:C4 | 10000 | 0.1 | 7.5

    Worst | C2:C4 | 5000 | 0.02 | 5

  6. Tại cửa sổ Scenario Manager lúc này trong danh sách Scenarios đã có 3 kịch bản: Best, Most likely và Worst. Chọn Summary
  7. Trong cửa sổ Scenario Summary
    1. Report type: Scenario Summary
    2. Result cells: =B$17:F$17,B$19. Bài toán chúng ta đang quan tâm tới lợi nhuận sau thuế qua các năm B$17:F$17 và giá trị hiện tại thuần NPV B$19
    3. Bấm OK
  8. Excel sẽ sinh ra một sheet với dữ liệu như dưới

Với số liệu phân tích này chúng ta thấy được lợi nhuận sau thuế cũng như giá trị hiện tại thuần được tính toán tương ứng cho mỗi kịch bản số liệu dựa trên sự thay đổi của các tham số: doanh số bán, tỉ lệ tăng trưởng hàng năm và giá bán.

Leave a Reply