Sử dụng Bộ giải để lập ngân sách vốn

Hầu hết các tập đoàn muốn thực hiện các dự án đóng góp giá trị hiện tại ròng lớn nhất (NPV), tùy thuộc vào nguồn lực hạn chế (thường là vốn và lao động). Giả sử một công ty phát triển phần mềm đang cố gắng xác định 20 dự án phần mềm cần thực hiện. NPV (trong hàng triệu đô la) đóng góp bởi mỗi dự án cũng như vốn (trong hàng triệu đô la) và số lượng các lập trình viên cần thiết trong mỗi năm trong ba năm tiếp theo được đưa ra trên trang tính Mô hình Cơ bản trong tập tin Capbudget.xlsx, được hiển thị trong Hình 30-1 trên trang tiếp theo. Ví dụ, Project 2 cho lợi nhuận $908 triệu. Nó yêu cầu $151 triệu trong năm thứ 1, $ 269 triệu trong năm 2, và $ 248 triệu trong năm 3. Project 2 yêu cầu 139 lập trình viên trong năm thứ 1, 86 lập trình viên trong Năm 2 và 83 lập trình viên trong Năm 3. Các ô E4:G4 cho thấy vốn (trong hàng triệu đô la) có sẵn trong mỗi năm trong ba, và các ô H4:J4 cho biết có bao nhiêu lập trình viên sẵn dùng. Ví dụ, trong Năm 1, có tới 2,5 tỷ đô la vốn và 900 lập trình viên có sẵn.

Công ty phải quyết định có nên thực hiện từng dự án hay không. Chúng ta hãy giả định rằng chúng ta không thể thực hiện một phần nhỏ của một dự án phần mềm; nếu chúng tôi phân bổ 0,5 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 sẽ mang lại cho chúng tôi doanh thu $0!

Các mẹo trong các tình huống trong đó bạn hoặc làm hoặc không làm điều gì đó là để sử dụng các tế bào thay đổi nhị phân. Ô 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 ta thực hiện dự án. Nếu ô thay đổi nhị phân tương ứng với dự án bằng 0, thì chúng ta 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 ô thay đổi nhị phân bằng cách thêm ràng buộc—chọn ô 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.

Với nền tảng 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ư thường lệ với mô hình Trình giải quyết, chúng ta bắt đầu bằng cách xác định ô đích, các ô thay đổi và ràng buộc.

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

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

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

Như bạn có thể thấy, trang tính của chúng tôi phải tính toán bất kỳ lựa chọn nào của các dự án NPV, vốn được sử dụng hàng năm và các lập trình viên sử 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 đã chọn. (Tên dải Ô NPV tham chiếu đến phạm vi C6:C25.) Đối với mỗi dự án có cột A là 1, công thức này sẽ chọn NPV của dự án và đối với mỗi dự án có cột A bằng 0, công thức này không nhận NPV của dự án. Do đó, chúng tôi có thể tính NPV của tất cả các dự án và ô mục tiêu của chúng tôi là tuyến tính vì nó được tính bằng cách tính tổng các thuật ngữ theo sau biểu mẫu (ô thay đổi )*(hằng số). Theo cách 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 đến 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ư minh họa trong Hình 30-2.

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 ta (phạm vi có tên là doit) là các ô thay đổi nhị phân cho mỗi dự án. Ràng buộc E2:J2<=E4:J4 đảm bảo rằng trong mỗi năm vốn và lao động được sử dụng ít hơn hoặc bằng vốn và lao động sẵn có. Để thêm ràng buộc giúp ô thay đổi thành nhị phân, tôi bấm 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 Ràng buộc sẽ xuất hiện như minh họa trong Hình 30-3.

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

Với hộp thoại Tham số Bộ giải được điền vào, hãy bấm Giải quyết và chúng ta có kết quả hiển thị trước đó trong Hình 30-1. Công ty có thể nhận được NPV tối đa $9.293 triệu ($9,293 tỷ) 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ó những ràng buộc khác. Ví dụ: giả sử nếu chúng ta chọn Project 3, chúng ta 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 chọn Project 3 chứ không phải Project 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 và 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 ta chọn Project 3, L9 bằng 1 và lực ràng buộc L12 (nhị phân Project 4) để bằng 1. Ràng buộc của chúng tôi cũng phải để giá trị nhị phân trong ô thay đổi của Project 4 không bị hạn chế nếu chúng tôi không chọn Project 3. Nếu chúng ta không chọn Project 3, L9 bằng 0 và ràng buộc của chúng ta cho phép nhị phân Project 4 bằng 0 hoặc 1, đó là điều chúng ta muốn. Giải pháp tối ưu mới được hiển thị trong Hình 30-4.

Một giải pháp tối ưu mới sẽ được tính toán nếu chọn Project 3 nghĩa là chúng ta cũng phải chọn Project 4. Bây giờ giả sử chúng ta chỉ có thể thực hiện bốn dự án từ dự án từ 1 đến 10. (Hãy xem trang tính At Most 4 Of P1–P10 , được hiển thị trong Hình 30-5.) Trong ô L8, chúng tôi tính tổng các giá trị nhị phân gắn với Dự án từ 1 đến 10 bằng công thức SUM(A6:A15). Sau đó, chúng tôi thêm ràng buộc L8<=L10, đảm bảo rằng, hầu hết, 4 trong số 10 dự án đầu tiên được chọn. Các giải pháp tối ưu mới được hiển thị trong Hình 30-5. NPV giảm xuống còn 9,014 tỷ USD.

Các mô hình Bộ giải Tuyến tính trong đó một số hoặc tất cả các ô thay đổi bắt buộc phải là nhị phân hoặc số nguyên thường khó giải hơn các mô hình tuyến tính, trong đó tất cả các ô thay đổi được phép là phân số. Vì lý do này, chúng tôi thường hài lòng với một giải pháp gần tối ưu cho một vấn đề lập trình nhị phân hoặc số nguyên. Nếu mô hình Bộ giải của bạn chạy trong một thời gian dài, bạn có thể muốn cân nhắ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ụ, thiết đặt Dung sai là 0,5% có nghĩa là Bộ giải sẽ dừng khi lần đầu tiên nó tìm thấy một giải pháp khả thi, trong phạm vi 0,5 phần trăm 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 bỏ qua các ràng buộc nhị phân và số nguyên). Thông thường chúng ta phải đối mặt với sự lựa chọn giữa việc tìm câu trả lời trong vòng 10 phần trăm của tối ưu trong 10 phút hoặc tìm một giải pháp tối ưu trong hai tuần thời gian máy tính! Giá trị Sai số mặc định là 0,05%, nghĩa là Bộ giải ngừng khi tìm thấy giá trị ô Đích trong phạm vi 0,05% giá trị ô đích tối ưu lý thuyết.

1. Một công ty có chín dự án đang được xem xét. NPV được thêm vào mỗi dự án và vốn yêu cầu của mỗi dự án trong hai năm tiếp theo được thể hiện trong bảng sau đây. (Tất cả các số có giá trị hàng triệu.) Ví dụ, Project 1 sẽ thêm $14 triệu vào NPV và yêu cầu chi tiêu $12 triệu trong Năm 1 và $3 triệu trong Năm 2. Trong năm thứ 1, có sẵn $50 triệu cho các dự án và có sẵn $20 triệu trong Năm 2.

	NPV	Chi phí năm 1	Chi phí năm 2
Dự án 1	14	12	3
Dự án 2	17	54	7
Dự án 3	17	6	6
Dự án 4	15	6	2
Dự án 5	40	30	35
Dự án 6	12	6	6
Dự án 7	14	48	4
Dự án 8	10	36	3
Dự án 9	12	18	3

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

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

• Một công ty xuất bản đang cố gắng xác định 36 cuốn sách sẽ xuất bản trong năm nay. Tệp được Pressdata.xlsx cung cấp thông tin sau đây về từng cuốn sách:

  • Doanh thu và chi phí phát triển dự kiến (tính bằng hàng nghìn đô la)

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

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

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

Bài viết này được chuyển đổi từ Phân tích Dữ liệu Excel 2007 và Lập 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 loạt các bài thuyết trình của Wayne Winston, một nhà thống kê và giáo sư kinh doanh nổi tiếng chuyên về ứng dụng sáng tạo, thực tế của Excel.