Формирование решений средствами таблиц MS Excel

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

Вычисленное отношение доходов к затратам показало, что вариант В₄ имеет наибольшую его величину (3,2), поэтому ему присваивается первый ранг, варианту В₁ присваивается второй ранг и т.д. Очевидно, согласно критерию, который требует выбора варианта с максимальным уровнем дохода на единицу затрат, лучшим будет вариант В₄.

Таблица 5.1 " Стоимость – эффективность "

Варианты решений	Общие затраты	Общие доходы	Отношение доходов к затратам	Ранг варианта
В1			1,7
В2			1,55
В3			1,27
В4			3,2

Таблица "Стоимость – эффективность" может быть использована лишь в том случае, если каждый из вариантов оценивается на основе одного критерия. Если же применяется больше одного критерия, то создается таблица "Стоимость - критерий". Пример таблицы приведен в табл. 5.2. В ней представляются варианты решений, оцениваемые с различных точек зрения. Допустим, те же четыре варианта капитальных вложений необходимо оценить с позиций трех критериев: близость расположения к железной дороге (транспортные затраты), близость расположения к водоемам (затраты на транспортировку воды), наличие в данной местности работоспособного населения (затраты на перевозку людей).

Таблица 5.2. "Стоимость – критерий"

Варианты решения	Критерий К1	Критерий К2	Критерий К3	Общая оценка по всем критериям	Ранг варианта
В1
В2
В3
В4
Коэффициент значимости критерия	0,6	0,3	0,1
Общие издержки

Элементами таблицы могут быть как абсолютные величины, указывающие на затраты или доходы, так и относительные, например ранг варианта, вычисленный на основе таблицы "Стоимость – эффективность". В последней строке таблицы указываются коэффициенты значимости каждого из критериев оценки. Это та качественная информация, которая собственно и отличает систему формирования решений от формальных оптимизационных методов. Здесь, лицо, принимающее решение вносит свой опыт и знание в процесс оценки вариантов. Сумма коэффициентов значимости всех критериев должна быть равна единице: 0,6 + 0,3 + 0,1 = 1.

Общая оценка каждого из вариантов рассчитывается по формуле:

,

где - общая оценка i -го варианта решения;

- вес j- го критерия;

-результат, который может быть получен при i -м варианте

согласно j- го критерия.

Тогда по варианту В₁ общая оценка равна:

.

Наилучшим вариантом, согласно данным таблицы, является вариант В₄. Однако абсолютные величины в большинстве случаев мало информативны. Например, затраты в суме 160, не соотнесенные с доходами, не устанавливают полностью объективной картины. Поэтому в большинстве случаев в качестве элементов Е_ij используют относительные величины (ранги, рентабельности, нормы прибыли и т.д.).

Средствами электронной таблицы Excel можно решить две задачи: Что будет, если? и Как сделать, чтобы? Первая задача решается достаточно просто: пользователь, изменяя исходные данные, может получить различные варианты решения.

Допустим, необходимо выяснить, какое финансовое состояние будет у предприятия через несколько лет, если известен рост выручки. Для решения задачи обратимся к табл. 5.3., представленной в программной системе MS Excel. Пусть расчет прибыли осуществляется по формуле: , где - прибыль, - выручка, - переменные затраты за период, - постоянные затраты за период.

Таблица 5.3. Расчетная динамика финансовых показателей предприятия

Номер строки	А	B	C	D
	Показатель
	Прибыль (убытки) от продаж	ВЗ-В4-В5	С3-С4-С5	D3-D4-D5 15.44
	Выручка (В)		В3*В6	C3*C6 125.44
	Переменные затраты (ПЕ)		В4+5	C4+5
	Постоянные затраты (ПО)		В5+5	C5+5
	Рост выручки	1,12	В6	В6

Для решения задачи "Что будет, если?" можно заменить коэффициент роста выручки, например, с 1,12 на 1,20. Тогда будет получен ответ на вопрос: "Какой объем прибыли будет получен в 2007 году, если выручка будет увеличиваться в соответствии с коэффициентом 1,20?". Для этого достаточно изменить значение ячейки В6, равное 1,12 на значение 1,20.

Вторая задача - "Как сделать, чтобы?" состоит в определении таких исходных данных, которые обеспечат необходимый результат. Она уже рассматривалась нами в 7.3. Для ее решения в среде MS Excel следует указать показатель, используемый в качестве цели, и показатель, который следует вычислить, чтобы добиться ее достижения. Например, если в качестве цели использовать показатель желаемой прибыли в 2007 году, равный 40 ед., а в качестве искомого значения показатель роста выручки, который обеспечит эту прибыль, то ответом будет значение показателя роста выручки в 2005 году 1,22. Для этого необходимо установить курсор в ячейку D2, где должна быть представлена желаемая прибыль в 2007 году, и выполнить следующие действия: Сервис/Подбор параметра. В поле "Установить в ячейке" будет находиться адрес D2 (см. рис. 8.4). В поле "Значение" следует указать 40. Так как нас интересует значение показателя "Рост выручки в 2005 году", поэтому в поле "Изменяя значение ячейки" следует указать адрес ячейки В6. В результате получим 1,22.

рис. 5.18. Технология решения задачи «Как сделать, чтобы?»

Аналогично, если требуется узнать какая должна быть выручка в 2005 году, то в поле "Изменяя значение ячейки" следует указать адрес В3. Ответ равен 119,57. В данном случае решена обратная задача с одной переменной – «Рост выручки». Если переменных больше, то следует обратиться к обратным вычислениям (см. раздел 8.4).