Перейти к основному контенту
Office

Использование надстройки "Поиск решения" для бюджетирования "капитал"

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

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

Каждый год компания, например ели Лилли, должна определить, какой наркотиков для разработки. Компания (например, Microsoft), для которой требуется разработать программное обеспечение; Компания, например Проктор _Амп_ Гамбле, которая позволяет создавать новые потребительские продукты. Функция "Поиск решения" в Excel помогает компании принимать такие решения.

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

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

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

Изображение книги

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

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

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

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

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

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

Изображение книги

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

Изображение книги

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

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

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

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

Изображение книги

Если выбрать проект 3, будет вычислено новое оптимальное решение. Кроме того, необходимо выбрать проект 4. Предположим, что мы можем сделать только четыре проекта из одного из проектов в проект 1 – 10. (На рисунке 30-5 показано, что вы видите не более 4 из P1 –-листа P10 .) В ячейке 8. вычисляется сумма двоичных значений, связанных с проектами 1 – 10, с формулой Sum (A6: A15). Затем мы добавим ограничение L8< = L10, которое гарантирует, что в большинстве случаев выделено 4 из первых 10 проектов. Новое оптимальное решение показано на рисунке 30-5. Функция ЧПС была перенесена в $9 014 000 000.

Изображение книги

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

Изображение книги

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

СВЯЗЬ

Годовой 1 расход

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

Проект 1

14

12

3

Проект 2

18

54

7

Проект 3

18

6

6

Проект 4

15

6

2

Проект 5

40

30

35

Project 6

12

6

6

Проект 7

14

48

4

Проект 8

10

36

3

Проект 9

12

18

3

  • Если вы не можете использовать долю проекта, но не обязаны использовать проект, как это можно продлить?

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

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

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

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

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

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

Эта статья была адаптирована от анализа данных Microsoft Office Excel 2007 и бизнес-моделирования с помощью Уэйн L. Уинстон.

Эта книга в стиле аудиторов была разработана на основе серии презентаций, Уэйн Уинстон, хорошо известных статистиЦиан и бизнес-профессором, которые специализируется на творческих и практичных приложениях Excel.

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

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

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

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

×