استفاده از ابزار Solver برای بودجه بندی سرمایه ای

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل، در فصل گذشته به استفاده از ابزار Solver برای حل مشکلات مربوط به نقل‌ و انتقال و توزیع پرداختیم، در این مقاله “استفاده از ابزار Solver برای بودجه بندی سرمایه ای” را مورد بررسی قرار می‌دهیم.

هرسال شرکتی مثل Eli Lilly نیاز دارد که تعیین کند کدام دارو را توسعه دهد، شرکتی چون Microsoft نیز می‌خواهد بداند کدام برنامه نرم‌افزاری را توسعه دهد، شرکتی مثل Proctor&Gamble نیاز دارد بداند کدام کالای مصرفی جدید را توسعه دهد. ابزار Solver در مایکروسافت اکسل 2019 می‌تواند در تصمیم‌گیری‌های ذکر شده به شرکت‌ها کمک کند.

استفاده از ابزار Solver برای مشخص کردن انجام شدن پروژه ها

یک شرکت داروسازی چگونه می‌تواند از ابزار Solver برای مشخص‌کردن اینکه کدام پروژه را به انجام برساند استفاده کند؟

بیشتر شرکت‌ها تمایل دارند پروژه‌هایی را در دست بگیرند که دارای بیشترین ارزش فعلی خالص (NPV) و مشروط به منابع محدودی (معمولاً از لحاظ سرمایه و نیروی کار) باشند. بیاید فرض کنیم شرکت توسعه نرم‌افزاری می‌خواهد مشخص کند که از 20 پروژه نرم‌افزاری کدام‌یک را می‌بایست در دست بگیرد. ارزش فعلی خالص (به میلیون دلار) هر پروژه به همراه سرمایه (به میلیون دلار) و تعداد برنامه‌نویسان موردنیاز در طول هریک از سه سال آینده در کاربرگ Basic Model در فایلی به نام Capbudget.xlsx به شما داده شده و در تصویر 1-33 نشان‌داده‌شده است.

مثلاً پروژه 2 میزان 908 میلیون دلار کسب می‌کند. این پروژه 151 میلیون دلار در طول سال اول،269 میلیون دلار در طول سال دوم و 248 میلیون دلار در طول سال سوم نیازد دارد. پروژه 2 نیازمند 139 برنامه‌نویس در سال اول، 86 برنامه‌نویس در سال دوم و 83 برنامه‌نویس در سال سوم می‌باشد. سلول‌های E4:G4 سرمایه در دسترس در طول هر یک از سه سال را (به میلیون دلار) نشان می‌دهند و سلول‌های H4:J4 مشخص می‌کنند که چه تعداد برنامه‌نویس نیازمندیم. مثلاً در سال 1 بیش از 2.5 میلیارد سرمایه و 900 برنامه‌نویس در دسترس هستند.

شرکت باید تصمیم بگیرد که آیا می‌بایست هر پروژه را در دست بگیرد یا نه. بیایید فرض کنیم که شرکت نمی‌تواند بخشی از پروژه‌های نرم‌افزاری را انجام دهد؛ مثلاً اگر 0.5 مقدار از منابع موجود اختصاص داده شود، شرکت برنامه‌ای ناکارآمد خواهد داشت که درآمدی برابر با صفر دلار خواهد داشت.

ترفند لازم در مدل‌سازی موقعیت‌هایی که در آنها یا کاری را انجام می‌دهید یا انجام نمی‌دهید استفاده از سلول‌های متغیر باینری[1] است. یک سلول باینری متغیر همیشه برابر صفر و یا یک است. وقتی که سلول باینری متغیر که مرتبط با پروژه باشد برابر با عدد یک شود، پروژه را انجام می‌دهید. اگر سلول متغیر باینری مرتبط با پروژه برابر با صفر شود پروژه را انجام نمی‌دهید. در اینجا ابزار Solver را به‌گونه‌ای تنظیم می‌کنیم که محدوده‌ای از سلول‌های متغیر باینری را با اضافه‌کردن یک محدودیت بکار گیرد: ابتدا سلول‌هایی را که می‌خواهیم استفاده کنیم را انتخاب می‌کنیم و سپس از لیست موجود در کادر محاوره‌ای Add Constraint گزینه Bin را انتخاب می‌کنیم.

تصویر 1-33 داده‌هایی که با ابزار Solver بکار می‌بریم تا مشخص کنیم کدام پروژه را می‌بایست در دست گرفت.

با این موارد گفته شده حالا آماده حل مسئله انتخاب پروژه برنامه‌نویسی شده‌اید. شما باید مثل همیشه در کار با مدل Solver کار را با شناسایی سلول هدف، سلول متغیر و محدودیت‌ها آغاز کنید.

• سلول هدف مقدار ارزش خالص فعلی ارائه شده توسط پروژه منتخب را افزایش می‌دهد.
• سلول‌های متغیر در هر پروژه به دنبال سلول‌های متغیر باینری صفر و یک می‌گردند. ما این سلول‌ها را در محدوده A6: A25 قرار داده‌ایم (و این محدوده را doit نام‌گذاری کرده‌ایم) مثلاً عدد 1 در سلول A6 مشخص می‌کند که پروژه 1 را در دست می‌گیرید، عدد 0 در سلول C6 مشخص می‌کند که پروژه 1 را قبول نمی‌کنید.
• محدودیت‌هایی که نیاز دارید توسط آنها اطمینان حاصل کنید که در هرسال t(t= 1,2,3) سرمایه استفاده شده در سال t کمتر یا برابر با سرمایه در دسترس سال t نبوده و نیروی کار استفاده شده در سال t نیز کمتر یا برابر نیروی کار موجود سال t نباشد.

همان‌طور که می‌بینید، کاربرگ می‌بایست برای هر پروژه انتخابی میزان ارزش خالص فعلی، سرمایه استفاده شده سالیانه و برنامه‌نویسان استفاده شده در هرسال را محاسبه کند. در سلول B2 از فرمول =SUMPRODUCT(doit,NPV) استفاده می‌کنیم تا میزان کل ارزش خالص فعلی تولید شده در پروژه منتخب را پیدا کنیم. (نام محدوده NPV ارجاع به محدوده C6:C25 دارد) این فرمول ارزش خالص فعلی، هر پروژه دارای عدد یک در ستون A را انتخاب می‌کند و ارزش خالص فعلی هر پروژه که در ستون A رقم صفر داشته باشد را انتخاب نمی‌کند؛ بنابراین ما می‌توانیم ارزش خالص فعلی تمامی پروژه‌ها را محاسبه کنیم و سلول هدف دارای مدل خطی خواهد بود چرا که با جمع زدن تمامی شرایط ناشی از شکل (سلول متغیر)*(محدودیت) محاسبه می‌شود. ما به روشی مشابه میزان سرمایه و نیروی کار استفاده شده در هرسال را با کپی فرمول =SUMPRODUCT(doit,E6:E25) از سلول E2 به محدوده F2:J2 محاسبه می‌کنیم.

اکنون کادر محاوره‌ای Solver Parameters را همان‌طور که در تصویر 2-33 نشان‌داده‌شده پر می‌کنیم.

تصویر 2-33 کادر محاوره‌ای Solver Parameters که برای مدل انتخاب پروژه تنظیم شده است.

هدف آن است که میزان ارزش خالص فعلی پروژه‌های انتخاب شده (سلول B2) را زیاد کنیم. سلول‌های متغیر (محدوده‌ای به نام doit) برای هر پروژه سلول‌های متغیر باینری محسوب می‌شوند. محدودیت E2:J2<=E4:J4 به ما اطمینان می‌دهد که در طول هرسال، میزان سرمایه و نیروی کار استفاده شده کمتر و یا مساوی سرمایه و نیروی کار در دسترس خواهد بود. برای اضافه‌کردن محدودیتی که سلول‌های متغیر باینری به وجود آورد، بر روی گزینه Add در کادر محاوره‌ای Solver Parameters کلیک می‌کنیم و از لیست میانه کادر محاوره‌ای Add Constraint گزینه Bin را انتخاب می‌کنیم. حالا کادر محاوره‌ای Add Constraint می‌بایست همچون تصویر 3-33 به نظر برسد.

تصویر 3-33 استفاده از گزینه Bin در کادر محاوره‌ای Add Constraint برای مشخص‌کردن سلول‌های متغیر باینری. سلول‌هایی که یا عدد صفر و یا عدد یک را نشان می‌دهند.

این مدل خطی است چرا که سلول هدف با جمع شرایطی که شکل (سلول متغیر)*(محدودیت) دارند محاسبه می‌شود و همچنین به‌خاطر اینکه محدودیت‌های استفاده از منابع توسط مقایسه مجموع (سلول‌های متغیر)* (محدودیت‌ها) با یک محدودیت محاسبه می‌شوند؛ بنابراین موتور Simplex LP را انتخاب می‌کنیم.

اکنون که کادر محاوره‌ای Solver Parameters را پرکرده‌ایم روی گزینه Solve کلیک می‌کنیم و نتایجی را که پیش‌ازاین در تصویر 1-33 دیدیم را به دست می‌آوریم. این شرکت می‌تواند ارزش خالص فعلی ای برابر با 9293 میلیون ( 9.293 میلیارد دلار) را با انتخاب پروژه‌های 2،3،6،10،14،16،19 و 20 به دست آورد.

اداره کردن محدودیت‌ها

برخی اوقات مدل‌های انتخاب پروژه دارای محدودیت‌های دیگری هستند. مثلاً فرض کنید که اگر پروژه شماره 3 را انتخاب کنید می‌بایست پروژه شماره 4 را نیز انتخاب کنید. ازآنجاکه راه‌حل بهینه حاضر پروژه شماره 3 را انتخاب ولی پروژه شماره 4 را انتخاب نکرده است متوجه می‌شویم که راه‌حل حاضر راه‌حل بهینه‌ای نیست. برای حل این مشکل به‌سادگی محدودیتی را اضافه کنید که مشخص کند سلول متغیر باینری پروژه 3 کمتر یا مساوی با سلول متغیر باینری پروژه 4 باشد.

می‌توانید این مثال را در کاربرگ If 3 then 4 در فایلی به نام Capbudget.xlsx که در تصویر 33-4 نشان داده پیدا کنید. سلول L9 به ارزش باینری (صفر و یکی) مرتبط با پروژه 3 و سلول L12 به ارزش باینری (صفر و یک) مرتبط به پروژه 4 ارجاع دارند. با اضافه کردند محدودیت L9<=L12، چنانچه پروژه 3 را انتخاب کنید سلول L9 برابر با یک می‌شود، و این محدودیت سلول L12(سلول باینری پروژه 4) را وادار می‌کند تا برابر با 1 شود. این محدودیت همچنین چنانچه سلول شماره 3 را انتخاب نکنید، می‌بایست مقدار باینری در سلول متغیر پروژه شماره 4 را به شکل نامحدود رها کند. چنانچه پروژه شماره 3 را انتخاب نکنید، سلول L9 برابر با صفر می‌شود و محدودیت اجازه می‌دهد که مقدار باینری پروژه شماره 4 صفر یا یک باشد که این همان چیزی است که شما می‌خواهید. راه‌حل بهینه جدید در تصویر 4-33 نشان‌داده‌شده است.

تصویر 4-33 راه‌حل بهینه جدید برای وضعیتی که انتخاب پروژه شماره 3 نیاز به انجام پروژه شماره 4 داشته باشد.

حالا فرش کنید که از میان پروژه‌های شماره 1 تا 10 می‌توانید تنها چهار پروژه انجام دهید (کاربرگی به نام At Most 4of p1-p10 که در تصویر شماره 5-33 نشان‌داده‌شده را ببینید) در سلول L8 شما مجموع ارزش‌های باینری (صفر و یک) مرتبط با پروژه‌های 1 تا 10 را با فرمول =SUM(A6:A15)محاسبه می‌کنیم. سپس محدودیت L8<=L10 را اضافه می‌کنیم که به ما اطمینان می‌دهد که حداکثر 4 عدد از 10 پروژه انتخاب شده‌اند. راه‌حل بهینه جدید در تصویر 5-35 نشان‌داده‌شده است. می‌بینید که میزان ارزش خالص فعلی به 9.014 میلیارد دلار کاهش پیدا کرده است.

تصویر 5-33 راه‌حل بهینه وقتی که از پروژه‌های 1 تا 10 تنها چهار پروژه می‌توان انتخاب کرد.

حل کردن مسئله های برنامه نویسی دارای اعداد باینری و عدد صحیح

مدل‌های Solver خطی که در آنها برخی یا تمامی سلول‌های متغیر می‌بایست باینری (صفر و یک) و یا عدد صحیح باشند معمولاً دشوارتر از مدل‌های خطی است که تمامی سلول‌های متغیر می‌توانند عدد کسری باشند. به همین دلیل، تحلیل گران اغلب در مسائل برنامه‌نویسی دارای اعداد باینری یا صحیح راضی به راه حلی تقریباً بهینه هستند.

اگر مدل Solver شما قرار است برای مدتی طولانی کار کند، شاید بهتر باشد که به فکر تنظیم بهینگی عدد صحیح (Adjusting the Integer Optimality) (که قبلاً میزان تحمل یا Tolerance نامیده می‌شد) در کادر محاوره‌ای Solver Options باشید (تصویر 6-33 را مشاهده کنید) مثلاً مقدار تنظیم بهینگی عدد صحیح برابر با 0.05 به آن معنی است که ابزار Solver اولین باری که راه حلی عملی پیدا کند که در 0.05 درصد از ارزش بهینه سلول هدف نظری (Theoretical Optimal Target Cell Value) باشد کار را متوقف می‌کند. (ارزش بهینگی سلول هدف نظری ارزش هدف سلول هدف بهینه‌ای است که وقتی که محدودیت‌های باینری و عدد صحیح حذف شوند آن را پیدا می‌کنید) اغلب با انتخاب میان یافتن جوابی با ده درصد بهینگی در ده دقیقه و یا یافتن یک راه‌حل کاملاً بهینه در دوهفته زمان محاسبه روبرو می‌شویم.

میزان بهینگی پیش‌فرض عدد صحیح 5 درصد است که این به آن معناست که ابزار Solver هنگامی که ارزش سلول هدفی را در 5 درصد از ارزش بهینگی سلول هدف نظری پیدا کند، کار را متوقف می‌کند. هنگامی که برای اولین‌بار مسئله توسعه نرم‌افزاری را حل کردیم بهینگی عدد صحیح را برابر با 5% قرار دادیم و ارزش بهینگی سلول هدفی برابر با 9269 پیدا کردیم. وقتی که ارزش بهینگی عدد صحیح را به مقدار %0.05 تغییر دهیم ارزش سلول هدفی بهتر پیدا می‌کنیم (9293).

تصویر 33.6 تنظیم گزینه Integer Optimality (بهینگی عدد صحیح)

مسئله‌ های این فصل

شرکتی 9 پروژه در حال بررسی دارد. میزان ارزش خالص فعلی که هر پروژه به همراه می‌آورد و سرمایه لازم برای هر پروژه در طول دوساله آینده در جدول زیر نشان‌داده‌شده است (تمام عددها به میلیون هستند) مثلاً پروژه 1 میزان 14 میلیون ارزش خالص فعلی اضافه می‌کند و نیازمند مخارجی 12 میلیون دلاری در طول سال 1 و 3 میلیون دلار در سل دوم می‌باشد. در طول سال 1 میزان 50 میلیون دلار سرمایه برای پروژه‌ها موجود و در سال دوم این میزان به 20 میلیون دلار می‌رسد.

1. چنانچه نتوانید بخشی از یک پروژه را در دست بگیرید و می‌بایست یا تمامی آن و یا هیچ پروژه‌ای را تقبل کنید، چگونه می‌توانید میزان سود خالص فعلی را افزایش دهید؟
2. فرض کنید اگر پروژه شماره 4 تقبل شود، پروژه شماره 5 نیز می‌بایستی انجام شود. در این صورت چگونه می‌توانید سود خالص فعلی را افزایش دهید؟

شرکت انتشاراتی سعی بر آن دارد تا مشخص کند امسال کدام‌یک از 36 کتاب را می‌بایست منتشر کند. فایلی به نام Pressdata.xlsx اطلاعات زیر را در باره هریک از کتاب‌ها به شما می‌دهد:

1. درآمد پیش‌بینی‌شده و هزینه‌های توسعه (به هزار دلار)
2. تعداد صفحه‌های هر کتاب
3. اینکه آیا کتاب برای مخاطبان توسعه نرم‌افزاری طراحی شده است (در ستون E با عدد 1 مشخص شده است)

امسال شرکت می‌تواند کتاب‌هایی با حداکثر 8500 صفحه منتشر کند و می‌بایستی حداقل 4 کتاب مخصوص توسعه دهندگان نرم‌افزار منتشر کند. این شرکت چگونه می‌تواند سود خود را افزایش دهد؟

در معادله SEND+MORE=MONEY هر حرف نمایانگر رقمی متفاوت از صفر تا 9 می‌باشد. کدام رقم‌ها با هریک از حروف مرتبط است؟

جیل سعی دارد برنامه کلاسی‌اش برای ترم بعدی را مشخص کند. هر ترم شامل دونیم ترم هفت‌هفته‌ای می‌باشد. جیل می‌بایست در هر نیم ترم چهار درس را انتخاب کند. در هر ترم پنج ساعت کلاسی وجود دارد. البته جیل نمی‌تواند یک درس را دو بار انتخاب کند. جیل با مقادیری مرتبط با درس و زمان کلاس سروکار دارد. این داده‌ها در فایلی به نام Classdata.xlsx به شما داده شده‌اند. مثلاً دوره درسی شماره 1 در زمان شماره 5 در ترم 1 ارزشی برابر با 5 دارد. جیل کدام یک از این دوره‌های درسی را در هر ترم می‌بایست انتخاب کند تا مقدار ارزش مربوط به درس آن ترم را افزایش دهد؟

در مثال اول بودجه‌بندی سرمایه‌ای از قالب‌بندی شرطی استفاده کنید تا هر ردیف را که مرتبط با پروژه انتخاب شده است را به رنگ زرد مشخص نماید.

از ابزار Solver استفاده نمایید تا حداقل سکه‌های موردنیاز برای 92 سنت پول خرد را مشخص کنید.

شرکتی نفتی در حال بررسی 12 پروژه حفاری است. سود خالص فعلی و سرمایه موردنیاز برای تکمیل هر پروژه در فایلی به نام Problem7.xlsx داده شده است. پنجاه میلیون دلار را می‌توان برای تکمیل هر پروژه خرج کرد. کدام یک از پروژه‌ها می‌بایست انتخاب شوند؟

می‌خواهم کوله‌پشتی را با اقلامی که بیشترین استفاده را در سفری پیاده دارند پر کنم. وزن اقلام به پوند و میزان استفاده هریک در فایلی به نام Problem8data.xlsx داده شده است. کوله‌پشتی می‌تواند حداکثر 26 پوند وزن در خود جای دهد. حداقل یک نوشیدنی (آب یا نوشیدنی‌های ورزشی Gatorade) و حداقل یک‌قلم خوراک پروتئینی (پنیر یا ورقه‌های گوشت beef jerkey) می‌بایست در کوله‌پشتی قرار گیرند. چگونه می‌توانم اقلام درون کوله‌پشتی را به‌گونه‌ای انتخاب کنم که بیشترین بهره را از آنها ببرم؟

شما گردنبندهای بسیار گران‌قیمت برای افراد خانواده سلطنتی در سراسر دنیا تولید می‌کنید. به‌اندازه کافی کارگر دارید که سه نوع گردنبند متفاوت تولید کنید. قیمت فروش هر گردنبند، جواهرات موردنیاز و تعداد قطعات جواهری که برای ساخت یک گردنبند لازم است در فایلی به نام Problem9data.xlsx داده شده است. کدام یک از گردنبندها درآمد شما را افزایش می‌دهند؟