С помощью поиска решения для прописная составление бюджета

Примечание:  Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

Компания использование "Поиск решения" для определения его следует предпринять какие проекты?

Каждый год компании, например Eli Lilly требуется определить которого препаратов для разработки; компании, например Майкрософт, какие программное обеспечение для разработки; Компания, например Proctor & Gamble, какие новые продукты получателя для разработки. Функция "Поиск решения" в Excel может помочь компании эти решения необходимо принять.

Большинство организаций нужно предпринять проекты, которые влияют наибольшему чистой приведенной стоимости (ЧПС) взыскана ограниченных ресурсов (обычно прописная и трудовые). Допустим, что компании разработки программного обеспечения пытается определить, какие 20 проектов программного обеспечения, его следует предпринять. ЧПС (в миллионы долларов) вклад каждого проекта, а также капитала (в миллионы долларов) и количество программистов необходимости при каждой из следующих трех лет предоставляется на листе в файле Capbudget.xlsx, который является Базовой модели на следующей странице показан на рисунке 30-1. Например 2 проекта дает 908 млн. Обработка сообщения требует 151 млн во время 1 года, 269 млн во время 2 года и 248 млн во время 3 года. Во время 1 года, 86 программистов во время 2 года и 83 программистов во время 3 года проекта 2 требует 139 программистов. Отображение капитала (в миллионы долларов) доступны при каждой из трех лет E4:G4 ячеек и ячейки H4:J4 указать, сколько программистов доступны. Например во время 1 года до 2,5 миллиардов долларов в прописные и 900 программистов доступны.

Компания необходимо решить, ли его следует предпринять каждого проекта. Предположим, что мы не удается предпринять доля проекта программного обеспечения; Если выделить 0,5 необходимые ресурсы, например, поэтому у нас есть нерабочего программы, которая приведет нам выручка $0!

В ситуации, в которых можно сделать либо не выполнить определенные моделирования достаточно использовать двоичный изменяемых ячеек. Двоичный, изменяя ячейки всегда равно 0 и 1. Изменение ячейку, которая соответствует проекта двоичного файла равно 1, мы выполните проекта. Если изменение ячейку, которая соответствует проекта двоичного файла равно 0, мы не проекта. Настроить поиск решения для использования диапазона двоичное изменяемых ячеек путем добавления ограничения — Выбор изменяемых ячеек, которые вы хотите использовать, а затем выберите ячейки из списка в диалоговом окне Добавить ограничение.

Изображение книги
Рисунок-30-1 данных мы используем с "Поиск решения" Чтобы определить, какие проекты следует предпринять

Фоне мы готовы решить проблему выбора проектов программного обеспечения. Как всегда с моделью "Поиск решения" начнем с определение нашей целевой ячейки, изменяемых ячеек и ограничения.

  • Целевой ячейки. Мы развертывание ЧПС, создаваемое выбранные проекты.

  • Изменяемых ячеек. Мы найдите 0 и 1 двоичный изменяемой ячейки для каждого проекта. Я находится в диапазоне A6:A25 эти ячейки (и вместо демонстрациидиапазон с именем). Например 1 в ячейке A6 указывает на то, что мы предпринять 1 проекта; 0 в ячейке C6 означает, что мы не предпринять 1 проекта.

  • Ограничений. Необходимо убедиться, что для каждого года t (t = 1, 2, 3), прописная год t используется — меньше или равно доступных прописная год t , а год t используемые трудовые ресурсы — меньше или равно год t труда доступны.

Как видите, нашей листа вычисляйте для любого фрагмента проектов ЧПС, используется ежегодную капитала и программистов использовать каждый год. В ячейке B2 использовать формулы SUMPRODUCT(doit,NPV) для вычисления общее ЧПС создаваемое выбранные проекты. (Имя диапазона ЧПС на диапазон C6:C25.) Для каждого проекта с 1 в столбце A эта формула забирает ЧПС проекта и для каждого проекта с 0 в столбце A, эта формула не обнаружить ЧПС проекта. Таким образом, мы можем для вычисления ЧПС все проекты и нашей целевой ячейки линейный так, как вычисляется по суммирование термины, которые следуют за формы (изменяемая ячейка)*(константа). Аналогичным образом вычисления, что капитала применен каждый год и используемые каждый год путем копирования с E2 F2:J2 формула SUMPRODUCT(doit,E6:E25)трудовые ресурсы.

Я теперь заполните диалоговое окно "Параметры поиска решения" как показано на рисунке 30-2.

Изображение книги
Установите диалоговое окно Параметры поиска решения рис 30 для выбора модели проекта

Наша цель — максимально использовать ЧПС выбранные проекты (ячейка B2). Изменяемые ячейки (диапазон с именем вместо демонстрации) являются двоичное изменяемых ячеек для каждого проекта. Ограничение E2:J2 < = E4:J4 гарантирует, что во время каждый год прописные и используемые трудовые ресурсы являются меньше или равно прописные и трудовые ресурсы доступны. Чтобы добавить ограничение, которое делает двоичный изменяемых ячеек, в диалоговом окне Параметры поиска решения нажмите кнопку Добавить и выберите ячейки из списка в центре диалоговое окно. Диалоговое окно "Добавление ограничения" отобразятся, как показано на рисунке 30-3.

Изображение книги
Используйте рис 30 ячейки параметр в диалоговом окне Добавить ограничение настроить двоичное изменяемых ячеек — ячеек, которые будут отображаться 0 и 1.

Нашей модели линейной так как значение целевой ячейки вычисляется как sum термины, которые имеют форму (изменяемая ячейка)*(константа) и поскольку вычисляются ограничениями на использование ресурсов, сравнив сумму (изменение cells)*(constants) константы.

В диалоговом окне Параметры поиска решения заполнены в, нажмите кнопку найти решение, и у нас есть результаты, показанные ранее в рисунке 30-1. Компания можно получить максимальное ЧПС из 9,293 млн (миллиардов 9.293 $), выбрав проекты 2, 3, 6 — 10, 14 — 16 19 и 20.

Иногда выбора проекта модели имеют другими ограничениями. Предположим, что при нажмем кнопку 3 проекта, мы также необходимо выбрать 4 проекта. Поскольку наш текущей оптимальное решение выбирает проекта 3, но не 4 проекта, мы знаем, что наш текущего решения невозможно остаются оптимизации. Чтобы решить эту проблему, просто добавьте ограничение, двоичный изменяемой ячейки для проекта 3 меньше или равно к ячейке двоичный изменение 4 проекта.

В этом примере на листе при 3 нажмите 4 можно найти в файле Capbudget.xlsx, как показано на рисунке 30-4. Ячейка L9 ссылается на двоичное значение, связанных с 3 проекта и ячейку L12 в двоичное значение, связанные с 4 проекта. Добавив ограничение L9 < = L12, если мы решим 3 проекта, L9 равняется 1 и наш ограничение начинает L12 (4 проекта двоичный) равным 1. Наш ограничение также необходимо оставить двоичное значение в изменяемой ячейки неограниченный Если мы не устанавливайте проекта 3 4 проекта. Если не устанавливайте 3 проекта, L9 равно 0 и наш ограничение позволяет двоичный равным 0 и 1, являющееся нам нужно 4 проекта. Новый оптимальное решение показан на рисунке 30-4.

Изображение книги
Новый рисунок-30-4 оптимальное решение для, если не 3 проекта, а затем 4 проекта

Если Выбор проекта 3 означает, что мы также необходимо выбрать 4 проекта вычисляется новый оптимальное решение. Предположим, что мы можем только четыре проектов один из проектов 1 до 10. ( Большая At 4 из P1 — P10 листа, показанный на рисунке 30-5 см). В ячейке уровне 8 мы вычисляет сумму двоичные значения, связанные с проектами 1 до 10 с формулой SUM(A6:A15). Затем добавим ограничение уровне 8 < = L10, гарантирует, что, не более 4 первые 10 проектов выбраны. Новый оптимальное решение показан на рисунке 30-5. ЧПС удалил на миллиардов 9.014 $.

Изображение книги
30 рис оптимальное решение, если мы можем выбрать только 4 из 10 проектов

Линейная моделей "Поиск решения" в какой некоторых или всех изменяемых ячеек должны быть двоичный или целого числа труднее обычно хранятся задачей, чем линейных задач, в которых могут быть дробей всех изменяемых ячеек. По этой причине мы часто устраивает почти оптимальное решение проблемы программирования двоичное или целого числа. Если ваша модель "Поиск решения" выполняется в течение длительного времени, вы можете попробуйте настроить параметр «погрешность» в диалоговом окне Параметры поиска решения. (Читайте в статье рисунке 30-6). Например, настройки погрешности 0,5% означает остановку "Поиск решения" в первый раз находит подходящим решением, которое входит в состав 0,5 процента от значения ячейки точная оптимальной цели (точная оптимального ячейки целевой является при найти оптимальное целевой двоичный и целое число со знаком ограничения пропускаются). Часто мы столкнулись с выбором между Поиск ответов в пределах 10% от оптимального через 10 минут или найти оптимальное решение в двух недель время на компьютере! Отклонение по умолчанию — 0,05%, которая означает, что поиск решения останавливается при нахождении целевой ячейки внутри 0,05 процента от значения ячейки точная оптимальной цели.

Изображение книги
Настройка рис 30 параметр отклонения

  1. 1. в компании имеется девять проектов в разделе ряд моментов. В следующей таблице показан ЧПС, добавленные в каждом проекте и капитала затребовал каждого проекта в течение следующего двух лет. (Все числа являются миллионов). Например 1 проекта надстройки 14 миллионов ЧПС и требуют расходы на 12 млн во время 1 года и 3 млн во время 2 года. Во время 1 года 50 миллионов долларов США в прописные доступна для проектов и 20 млн доступна в течение года 2.

ЧПС

1 года расходов

Год 2 расходов

Проект 1

14

12

3

Проекта 2

17

54

7

Проект 3

17

6

6

Проект 4

15

6

2

Проект 5

40

30

35

Проект 6

12

6

6

Проект 7

14

48

4

Проект 8

10

36

3

Проект 9

12

18

3

  • Если мы не удается предпринять доля проекта, но должны пройти все или никто проекта, как можно мы развертывание ЧПС?

  • Предположим, что если производится проекта 4, 5 проект должен выполняться. Как развернуть ЧПС?

  • Публикация компании пытается определить, какие 36 книг, он должен опубликовать в этом году. Файл Pressdata.xlsx дает следующие сведения о каждой книге:

    • Планируемая Выручка и разработки затрат на ресурсы (в тысячах доллары)

    • Страницы в каждой книге

    • Будет ли книга направленную аудитории разработчиков программного обеспечения (заданную 1 в столбце E)

      Публикации компании можно публиковать Группировка до 8500 страниц в этом году книг и необходимо опубликовать менее четырех книг, направленную разработчикам программного обеспечения. Как можно компании максимально увеличить прибыль его?

В этой статье было адаптировать из Microsoft Office Excel 2007 анализа и моделирования бизнес по Wayne Winston l..

В этой книге аудитории стиля из презентаций разработанный Wayne Winston известного статистика и бизнес-тренер кто занимается creative, практических приложения Excel.

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×