Пример 2. Конкурс проектов

Строительная фирма собирается участвовать в конкурсе на получение заказа по проведению работ по благоустройству города. Оценочная стоимость контракта 90–130 млн р. Вероятность получения заказа зависит от запрашиваемой суммы и числа участников конкурса. Количество участников точно не известно. Предполагается, что оно описывается треугольной функцией распределения с минимальным, максимальным и наиболее вероятным значениями 3, 8 и 5 соответственно. В табл. 2.1 приведена предполагаемая зависимость вероятности получения заказа от запрашиваемой суммы и количества участников. Необходимо определить вероятность получения того или иного объёма финансирования.

На рис. 2.12 представлена модель для расчета вероятности получения заказа. В ячейках А12:Е18 записаны данные табл. 2.1. Диапазоны запрашиваемых сумм представлены нижними значениями для удобства расчетов. В ячейках В21:В23 приведены параметры функции распределения числа участников конкурса. В ячейке В3 находится величина запрашиваемой суммы. Число в ячейке С3 является вспомогательным для нахождения решения, оно определяется по формуле

=1+ПОИСКПОЗ(B3;B12:E12). (2.13)

В ячейке В4 записано значение случайной переменной – количество участников конкурса, согласно формуле

=ЦЕЛОЕ(ЕСЛИ(СЛЧИС()<=2/5;3+2КОРЕНЬ(СЛЧИС());

9-4КОРЕНЬ(СЛЧИС()))). (2.14)

Рис. 2.12. Определение вероятности получения заказа

Решение, полученное для конкретного значения случайной переменной, выводится в ячейке В5. Оно рассчитывается по формуле

=ВПР(B4;A12:E18;C3). (2.15)

Здесь функция ВПР выдаёт значение из таблицы, записанной в ячейках А12:Е18, находящееся в столбце с номером, записанным в ячейке С3 и в строке, в которой находится число из первого столбца таблицы, записанное в ячейке В6.

Таблица 2.1

Вероятность получения заказа на проведение работ

Рис. 2.13. Двумерная таблица подстановки

После того, как модель построена, создадим таблицу подстановки (рис. 2.13). На этот раз сделаем таблицу подстановки двумерной, для того чтобы иметь возможность сразу наблюдать изменение среднего значения интересующей нас величины в зависимости от варьируемого параметра. Для этого надо выполнить следующее. 1) В ячейки G4:G23 занесём номера репликаций. В ячейки H3:K3 запишем значения варьируемого параметра (запрашиваемой суммы). В ячейку G3 скопируем содержимое ячейки В5, предварительно зафиксировав адреса ячеек в формуле (2.15) ($B$4;$A$12:$E$18;$C$3). 2) Выделим область G3:K23. 3) Выполним команду: Таблица подстановки из меню Данные. 4) В появившемся диалоговом окне в графе Подставлять значения по столбцам в: запишем адрес ячейки В3, а в графе Подставлять значения по строкам в: снова запишем адрес произвольной неиспользуемой ячейки (ОК).

Поясним смысл произведённых манипуляций. Чтобы записать значение в ячейку таблицы подстановки, Excel выполняет следующие действия. 1) Переписывает значение, содержащееся в первом столбце таблицы в той же строке, что и заполняемая ячейка, в ячейку, указанную в графе Подставлять значения по строкам в: диалогового окна. 2) Переписывает число, содержащееся в первой строке таблицы подстановки в том же столбце, что и заполняемая ячейка, в ячейку, указанную в графе Подставлять значения по столбцам в: диалогового окна. 3) Пересчитывает модель и записывает число, появившееся в ячейке, находящейся в первой строке первого столбца таблицы подстановки, в заполняемую ячейку. 4) Восстанавливает прежние значения всех ячеек, кроме вновь заполненной. 5) Повторяет действия 1) – 4) с каждой оставшейся незаполненной ячейкой таблицы подстановки. При решении предыдущей задачи мы заполняли так называемую одномерную таблицу подстановки, в которой действие 2) пропускается, а в действии 3) происходит запись в заполняемую ячейку числа, появившегося в ячейке, находящейся в первой строке второго столбца таблицы подстановки.

Вернёмся к конкурсу проектов. Для ответа на поставленный вопрос в ячейках H24:K24 подсчитаны средние значения вероятностей из соответствующих столбцов таблицы подстановки (рис. 2.13). Зная эти значения, управляющий строительной фирмой может принимать решение о том, какую сумму лучше запросить для осуществления проекта. Из результатов моделирования следует, что чем меньше запрашиваемая сумма, тем выше вероятность её получения. С другой стороны, при этом меньше и доход, получаемый фирмой. Теперь дело управляющего решать, за какой доход стоит побороться. Конечно, в реальной ситуации вероятность получения заказа определяется не только запрашиваемой суммой, но и качеством, и объёмом предлагаемой работы, а также некоторыми другими факторами. Тем не менее, наша упрощённая модель позволяет исследовать влияние одного из факторов, влияющих на решение о размещении заказа. Кроме того, применяя её, мы познакомились с возможностями, даваемыми программой MS Excel для обоснования управленческих решений.

В заключение стоит сказать несколько слов о стиле моделирования. Как мы видели, процесс проведения расчетов можно разбить на три части. Соответственно этому можно выделить три области таблицы: 1) область, где содержится сама модель; 2) область, где содержится набор решений, получающихся при прогонах модели; 3) область с результатами статистического анализа выходных данных. Модель легче понять и отладить, если эти три области действительно чётко разделены в таблице. Хорошим стилем является также включение названий, раскрывающих смысл модели, назначение параметров, промежуточных и окончательных результатов, а также функциональных областей таблицы. Среди представленных в таблице чисел особенно важны три типа величин: окончательные результаты, случайные переменные и варьируемые параметры. Ячейки, содержащие эти три типа величин, удобнее выделять разными цветами.