Однопараметрический анализ чувствительности

Цель работы: изучение технологий автоматизации процедур анализа многовариантных решений на примере анализа чувствительности экономических показателей проекта.

В лабораторной работе рассматривается инвестиционный проект, связанный с выпуском продукции. Основные показатели проекта приведены в табл. 2.3.1.

Основные показатели проекта и диапазон их изменения

Наименование показателя	Обозначение	Ожидаемое наихудшее значение	Наиболее вероятное значение	Ожидаемое наилучшее значение
1	2	3	4	5
Объём выпуска изделий, шт.	Q	150	200	300
Цена одного изделия, руб.	P	35	50	55
Переменные затраты, руб.	V	40	30	25

1	2	3	4	5
Постоянные затраты, руб.	F	400	500	600
Амортизация, руб.	A	100	100	100
Налог на прибыль, %	T	60%	60%	30%
Норма дисконта, %	R	8%	10%	15%
Срок проекта, лет	N	5	5	7
Остаточная стоимость, руб.	S	50	200	500
Начальные инвестиции, руб.	I	2000	2000	2000

Необходимо выполнить анализ чувствительности чистой современной стоимости проекта (NPV) к изменению ключевых показателей. Чистая современная стоимость проекта NPV определяется следующим аналитическим выражением:

.

(2.3.1)

Для автоматизации процедуры оценки чувствительности NPV создайте шаблон в MS Excel (рис. 2.3.1). Присвойте листу имя «Анализ чувствительности NPV».

Значения параметров в шаблоне заполните наиболее вероятными значениями, взятыми из табл. 2.3.1.

Адрес ячейки	Имя переменной (ячейки)	Адрес ячейки	Имя переменной (ячейки)
В2	Количество	D2	Начальные_инвестиции
В3	Цена	D3	Постоянные_расходы
В4	Переменные_расходы	D4	Амортизация
В5	Норма_дисконта	D5	Остаточная_стоимость
В6	Срок_Реализации	D6	Налог_на_прибыль
B9	Чистые_платежи	D10	NPV

	(2.3.2)
	(2.3.3)

Аргументы в формуле следует указывать выделением соответствующих ячеек мышью, а не набирать с клавиатуры, так как ошибка в любом символе приведёт в ошибке в формуле.

После ввода формул (2.3.2) и (2.3.3) ячейки B9 и D10 должны принять значения 1460 и 3658 руб. соответственно. Формат ячеек Денежный при желании можно задать соответствующей командой (Формат ячейки ® Число).

Однопараметрический анализ чувствительности

Пусть значение цены изделия будет меняться в диапазоне от 55 руб. до 35 руб. с шагом 5 руб. (см. табл. 2.3.1). Требуется определить функцию NPV при данных значениях цены:

— ячейки С11:С15 заполните значениями цены изделия 55, 50, 45, 40, 35 соответственно;

— вызовите команду Данные ® Таблица подстановки. В появившемся окне (рис. 2.3.2) укажите, что по строкам выделенного диапазона располагаются значения цены изделия B3;

— после нажатия на кнопку ОК ячейки D11:D15 заполните значениями функции NPV, формула которой взята из ячейки D10, при различных значениях цены изделия, размещённой в ячейках С11:С15.

© 2014-2022 — Студопедия.Нет — Информационный студенческий ресурс. Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав (0.017)

Примечание 3.1. При выходе за указанные в отчете по устойчивости пределы измения цен оптимальное решение может меняться как по номенклатуре выпускаемой продукции, так и по объемам выпуска (без изменения номенклатуры).

Анализ оптимального решения на чувствительность в Excel

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

1	2	3	4	5
Постоянные затраты, руб.	F	400	500	600
Амортизация, руб.	A	100	100	100
Налог на прибыль, %	T	60%	60%	30%
Норма дисконта, %	R	8%	10%	15%
Срок проекта, лет	N	5	5	7
Остаточная стоимость, руб.	S	50	200	500
Начальные инвестиции, руб.	I	2000	2000	2000

Анализ оптимального решения на чувствительность в Excel

Проведем анализ чувствительности задачи о мебельном комбинате из лабораторной работы №2 (часть I). Для этого необходимо после запуска в Excel задачи на решениев окне «Результаты поиска решения» выделить с помощью мыши два типа отчетов: «Результаты»и«Устойчивость» (рис.3.5).

Рис.3.5. Выделение типов отчетов требуемых для анализа чувствительности

2) таблица 2 содержит информацию о значениях переменных, полученных в результате решения задачи;

3) таблица 3 показывает результаты оптимального решения для ограничений и для граничных условий.

Если ресурс используется полностью (то есть ресурс дефицитный), то в графе «Статус» («Состояние») соответствующее ограничение указывается как «связанное»; при неполном использовании ресурса (то есть ресурс недефицитный) в этой графе указывается «не связан». В графе «Значение» приведены величины использованного ресурса.

Для граничных условий (строки 24, 25, 26 на рис. 3.6) в графе «Разница» показана разность между значением переменной в найденном оптимальном решении и заданным для нее граничным условием.

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

Таблица 1 содержит информацию, относящуюся к переменным.

Примечание 3.1. При выходе за указанные в отчете по устойчивости пределы измения цен оптимальное решение может меняться как по номенклатуре выпускаемой продукции, так и по объемам выпуска (без изменения номенклатуры).

Рис.3.7. Отчет по устойчивости для задачи о мебельном комбинате

Таблица 2 (см. рис.3.7) содержит информацию, относящуюся к ограничениям.

1. Величина использованных ресурсовв колонке «Результ. значение».

1. Что такое связывающие, несвязывающие, избыточные ограничения; дефицитные и недефицитные ресурсы?

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

3. Как графически проводится анализ изменения запаса дефицитных ресурсов?

4*. Каким образом, опираясь на результаты графического анализа, можно численно рассчитать новый (улучшенный) запас дефицитного ресурса?

5. Как графически проводится анализ изменения запаса недефицитных ресурсов?

6*. Каким образом, опираясь на результаты графического анализа, можно численно рассчитать новый запас недефицитного ресурса?

7. Что такое ценность дополнительной единицы i-го ресурса?

8. Как проводится графический анализ изменения коэффициентов ЦФ?

9*. Как численно определить диапазон изменения коэффициентов ЦФ, не изменяющий оптимального решения?

10. Какую информацию о чувствительности оптимального решения задачи ЛП можно получить из отчета по результатам и отчета по устойчивости?

11. Проанализируйте на чувствительность задачу о производстве полок (согласно своему варианту)?

4. ЛАБОРАТОРНАЯ РАБОТА №3 “ДВУХИНДЕКСНЫЕ ЗАДАЧИ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ. СТАНДАРТНАЯ ТРАНСПОРТНАЯ ЗАДАЧА”

Приобретение навыков построения математических моделей стандартных транспортных задач ЛП и решения их в Microsoft Excel.

Из таблицы видно, что при увеличении цены всего на 20%, чистый дисконтированный доход проекта увеличивается на 378%. Аналогично снижение цены на 20% вызывает падение NPV на 378%. Таким образом, проект очень чувствителен к изменению цены. И инвестору необходимо уделять большое внимание этой проблеме.

Анализ чувствительности инвестиционного проекта скачать в Excel

Таким образом, анализ чувствительности заключается в оценке влияния изменения исходных параметров проекта на его конечные характеристики, в качестве которых, обычно, используется внутренняя норма прибыли или NPV.