Sử dụng bộ giải dành cho ngân sách vốn

Công ty có thể sử dụng bộ giải để xác định các dự án nào sẽ thực hiện như thế nào?

Mỗi năm, một công ty như các nhu cầu của Eli Lilly để xác định loại thuốc nào để phát triển; một công ty như Microsoft, các chương trình phần mềm nào để phát triển; một công ty như Proctor & Gamble, mà sản phẩm tiêu dùng mới để phát triển. Tính năng Solver trong Excel có thể giúp công ty thực hiện các quyết định này.

Hầu hết các tập đoàn muốn thực hiện các dự án đóng góp vào giá trị hiện đại nhất của net (NPV), tùy thuộc vào các nguồn tài nguyên hạn chế (thường là vốn và lao động). Hãy nói rằng một công ty phát triển phần mềm đang cố gắng xác định xem có 20 dự án phần mềm nào sẽ thực hiện. Hàm NPV (trong hàng triệu đô-la) đã đóng góp bởi từng dự án cũng như thủ đô (trong hàng triệu đô la) và số lượng lập trình cần thiết trong mỗi ba năm tiếp theo được cung cấp trên trang tính mô hình cơ bản trong tệp capbudget. xlsx, được hiển thị trong hình 30-1 trên trang tiếp theo. Ví dụ, Project 2 cho $908.000.000. Nó đòi hỏi $151.000.000 trong năm 1, $269.000.000 trong năm 2 và $248.000.000 trong năm 3. Project 2 yêu cầu lập trình 139 trong năm 1, 86 lập trình trong năm 2 và các lập trình viên 83 trong năm 3. Các ô E4: G4 cho thấy vốn (trong hàng triệu đô la) sẵn dùng trong suốt ba năm và các ô H4: J4 cho biết có bao nhiêu lập trình sẵn dùng. Ví dụ, trong năm 1 lên đến $2.500.000.000 trong các lập trình vốn và 900 có sẵn.

Công ty phải quyết định xem nó sẽ thực hiện từng dự án. Giả định rằng chúng tôi không thể thực hiện một phần của dự án phần mềm; Nếu chúng tôi phân bổ 0,5 của các tài nguyên cần thiết, ví dụ, chúng tôi sẽ có một chương trình không làm việc mà có thể mang lại cho chúng tôi doanh thu $0!

Thủ thuật trong các tình huống lập mô hình mà bạn có thể làm hoặc không làm điều gì đó là sử dụng các ô thay đổi nhị phân. Một ô thay đổi nhị phân luôn bằng 0 hoặc 1. Khi một ô thay đổi nhị phân tương ứng với một dự án bằng 1, chúng tôi thực hiện dự án. Nếu một ô thay đổi nhị phân tương ứng với một dự án bằng 0, chúng tôi không thực hiện dự án. Bạn thiết lập bộ giải để sử dụng một phạm vi các ô thay đổi nhị phân bằng cách thêm một ràng buộc — chọn các ô thay đổi bạn muốn sử dụng, rồi chọn bin từ danh sách trong hộp thoại thêm ràng buộc.

Ảnh quyển sách

Với nền này, chúng tôi đã sẵn sàng để giải quyết vấn đề lựa chọn dự án phần mềm. Như mọi khi với mô hình Solver, chúng tôi bắt đầu bằng cách xác định ô đích của chúng tôi, các ô thay đổi và các ràng buộc.

  • Ô đích. Chúng tôi tối đa hóa NPV được tạo bởi các dự án được chọn.

  • Thay đổi ô.Chúng tôi tìm kiếm một ô 0 hoặc 1 nhị phân thay đổi cho từng dự án. Tôi đã đặt các ô này trong phạm vi A6: A25 (và đặt tên cho phạm vi doit). Ví dụ: 1 trong ô A6 cho biết chúng tôi thực hiện dự án 1; 0 trong ô C6 chỉ ra rằng chúng tôi không thực hiện Project 1.

  • Khó.Chúng ta cần đảm bảo rằng đối với mỗi năm t (t = 1, 2, 3) , vốn đầu tư có được sử dụng nhỏ hơn hoặc bằng t % vốn có sẵn và lao động t năm được sử dụng nhỏ hơn hoặc bằng lao động năm có sẵn.

Như bạn có thể thấy, trang tính của chúng tôi phải tính toán cho bất kỳ lựa chọn nào của dự án NPV, vốn được sử dụng hàng năm và các lập trình viên được dùng mỗi năm. Trong ô B2, tôi sử dụng công thức Sumproduct (doit, NPV) để tính tổng NPV được tạo bởi các dự án được chọn. (Tên phạm vi NPV tham chiếu đến phạm vi C6: C25.) Đối với mỗi dự án với 1 trong cột A, công thức này sẽ chọn lên NPV của dự án và đối với mỗi dự án với 0 trong cột A, công thức này sẽ không nhận được NPV của Project. Vì vậy, chúng tôi có thể tính toán cho NPV của tất cả các dự án và ô đích của chúng ta là tuyến tính vì nó được tính bằng cách tổng hợp các thuật ngữ theo biểu mẫu (thay đổi ô) * (hằngsố). Trong một thời trang tương tự, tôi tính toán vốn được sử dụng mỗi năm và lao động được sử dụng mỗi năm bằng cách sao chép từ E2 sang F2: J2 công thức Sumproduct (doit, E6: E25).

Bây giờ tôi đã điền vào hộp thoại tham số bộ giải như được hiển thị trong hình 30-2.

Ảnh quyển sách

Mục tiêu của chúng tôi là tối đa hóa NPV của các dự án đã chọn (ô B2). Các ô thay đổi của chúng tôi (phạm vi có tên là doit) là các ô thay đổi nhị phân cho từng dự án. Số khó khăn khó khăn : J2<= E4: J4 đảm bảo rằng trong mỗi năm vốn và lao động được sử dụng nhỏ hơn hoặc bằng vốn và lao động sẵn dùng. Để thêm hạn chế khiến cho các ô thay đổi nhị phân, tôi bấm vào thêm trong hộp thoại tham số bộ giải, rồi chọn bin từ danh sách ở giữa hộp thoại. Hộp thoại thêm hạn chế sẽ xuất hiện như minh họa trong hình 30-3.

Ảnh quyển sách

Mô hình của chúng tôi là tuyến tính vì ô đích được tính là tổng các thuật ngữ có biểu mẫu (thay đổi ô) * (hằng số) và vì các ràng buộc về mức sử dụng tài nguyên được tính bằng cách so sánh tổng của (thay đổi ô) * (hằng số) thành hằng số.

Với hộp thoại tham số bộ giải, hãy bấm giải quyết và chúng tôi có kết quả được hiển thị trước đó trong hình 30-1. Công ty có thể nhận được tối đa là NPV của $9.293.000.000 ($9.293.000.000) bằng cách chọn các dự án 2, 3, 6 – 10, 14 – 16, 19 và 20.

Đôi khi các mô hình lựa chọn dự án có các ràng buộc khác. Ví dụ, giả sử rằng nếu chúng tôi chọn Project 3, chúng tôi cũng phải chọn Project 4. Vì giải pháp tối ưu hiện tại của chúng tôi sẽ chọn Project 3 nhưng không dự án 4, chúng tôi biết rằng giải pháp hiện tại của chúng tôi không thể duy trì tối ưu. Để giải quyết vấn đề này, chỉ cần thêm ràng buộc rằng ô thay đổi nhị phân cho Project 3 nhỏ hơn hoặc bằng ô thay đổi nhị phân cho Project 4.

Bạn có thể tìm thấy ví dụ này trên trang tính if 3, sau đó 4 trong tệp capbudget. xlsx, được hiển thị trong hình 30-4. Ô L9 tham chiếu đến giá trị nhị phân liên quan đến Project 3 và ô L12 đến giá trị nhị phân liên quan đến Project 4. Bằng cách thêm ràng buộc L9<= L12, nếu chúng tôi chọn Project 3, L9 bằng 1 và các lực lượng hạn chế của chúng tôi L12 (phân bổ dự án 4) thành bằng 1. Ràng buộc của chúng tôi cũng phải rời khỏi giá trị nhị phân trong ô thay đổi của Project 4 nếu không hạn chế nếu chúng tôi không chọn dự án 3. Nếu chúng tôi không chọn Project 3, L9 Equals 0 và ràng buộc của chúng tôi cho phép nhị phân dự án 4 đến bằng 0 hoặc 1, vốn là những gì chúng tôi muốn. Giải pháp tối ưu mới được hiển thị trong hình 30-4.

Ảnh quyển sách

Một giải pháp tối ưu mới được tính nếu chọn Project 3 nghĩa là chúng tôi cũng phải chọn Project 4. Bây giờ, giả sử rằng chúng tôi chỉ có thể thực hiện bốn dự án từ các dự án từ 1 đến 10. (Xem gần nhất là 4 trang tính P1 – P10 , được hiển thị trong hình 30-5.) Trong ô L8, chúng ta tính toán tổng các giá trị nhị phân liên kết với các dự án từ 1 đến 10 với tổng công thức (A6: A15). Sau đó, chúng tôi thêm các ràng buộc L8<= L10, đảm bảo rằng, tại hầu hết, 4/10 dự án đầu tiên được chọn. Giải pháp tối ưu mới được hiển thị trong hình 30-5. NPV đã rơi vào $9.014.000.000.

Ảnh quyển sách

Các mô hình bộ giải mã tuyến tính, trong đó một số hoặc tất cả các ô thay đổi đều được yêu cầu là nhị phân hoặc số nguyên thường là khó xử lý so với các mô hình tuyến tính, trong đó tất cả các ô thay đổi đều được phép phân số. Vì lý do này, chúng ta thường hài lòng với một giải pháp tối ưu gần đến một vấn đề lập trình nhị phân hoặc số nguyên. Nếu mô hình Solver của bạn chạy trong một thời gian dài, bạn có thể muốn cân nhắc việc điều chỉnh thiết đặt dung sai trong hộp thoại tùy chọn bộ giải. (Xem hình 30-6.) Ví dụ, một thiết đặt dung sai của 0,5% có nghĩa là người giải sẽ ngừng lần đầu tiên nó tìm thấy một giải pháp khả thi là trong 0,5 phần trăm của giá trị ô đích tối ưu lý thuyết (giá trị ô đích tối ưu lý thuyết là giá trị đích tối ưu được tìm thấy khi các ràng buộc nhị phân và số nguyên sẽ được bỏ qua). Thường thì chúng tôi đang phải đối mặt với một lựa chọn giữa việc tìm kiếm câu trả lời trong vòng 10 phần trăm tối ưu trong 10 phút hoặc tìm giải pháp tối ưu trong hai tuần của thời gian máy tính! Giá trị dung lượng mặc định là 0,05%, điều này có nghĩa là người giải điểm dừng khi tìm thấy giá trị ô đích trong vòng 0,05 phần trăm của giá trị ô đích tối ưu lý thuyết.

Ảnh quyển sách

  1. 1. công ty có chín dự án dưới xem xét. Các NPV được thêm vào bởi từng dự án và vốn theo yêu cầu của từng dự án trong hai năm tiếp theo được hiển thị trong bảng sau đây. (Tất cả các số đều nằm trong hàng triệu.) Ví dụ, Project 1 sẽ thêm $14.000.000 vào NPV và yêu cầu chi phí $12.000.000 trong năm 1 và $3.000.000 trong năm 2. Trong năm 1, $50.000.000 ở vốn có sẵn cho các dự án và $20.000.000 sẵn dùng trong năm 2.

NPV

Chi tiêu năm 1

Chi tiêu của năm 2

Dự án 1

14

12

3

Dự án 2

1

54

7

Dự án 3

1

6

6

Dự án 4

15

6

2

Dự án 5

40

25

35

Dự án 6

12

6

6

Project 7

14

48

4

Project 8

10

36

3

Project 9

12

7

3

  • Nếu chúng tôi không thể thực hiện phân số của một dự án nhưng phải thực hiện tất cả hoặc không dự án nào, chúng tôi có thể tối đa hóa NPV bằng cách nào?

  • Giả sử là nếu Project 4 được thực hiện, dự án 5 phải được thực hiện. Chúng tôi có thể tối đa hóa NPV bằng cách nào?

  • Một công ty phát hành đang tìm cách xác định những sách 36 cần phát hành năm nay. Tệp Pressdata. xlsx cung cấp thông tin sau đây về mỗi cuốn sách:

    • Chi phí phát triển và doanh thu dự kiến (trong hàng ngàn đô-la)

    • Các trang trong mỗi cuốn sách

    • Liệu cuốn sách có hướng tới người xem các nhà phát triển phần mềm (được chỉ báo bằng 1 trong cột E) hay không

      Công ty phát hành có thể phát hành các quyển sách tổng cộng lên đến 8500 trang trong năm nay và phải phát hành ít nhất bốn quyển sách hướng đến các nhà phát triển phần mềm. Công ty có thể tối đa hóa lợi nhuận của nó bằng cách nào?

Bài viết này được điều chỉnh từ phân tích dữ liệu của Microsoft Office Excel 2007 và mô hình kinh doanh của Wayne L. Winston.

Cuốn sách kiểu lớp học này được phát triển từ một chuỗi các bản trình bày của Wayne Winston, một số chuyên ngành và giáo viên kinh doanh nổi tiếng chuyên về các ứng dụng sáng tạo, thực tế của Excel.

Lưu ý:  Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Cho chúng tôi biết thông tin này có hữu ích hay không? Dưới đây là bài viết bằng tiếng Anh để bạn tham khảo..​

Bạn cần thêm trợ giúp?

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×