Двухпараметрический анализ чувствительности
Пусть одновременно будут изменяться значение цены в диапазоне от 55 руб. до 35 руб. с шагом 5 руб. и значение объёма выпуска изделий со 150 шт. до 300 шт. с шагом 50 шт. (см. табл. 2.3.1). Требуется определить функцию NPV при любых комбинациях этих двух параметров:
- формулу для NPV переместите из ячейки D10 в ячейку С10 (Команды Копировать и Вставить выполняются для выделенной формулы в строке формул соответствующих ячеек);
- ячейки D10:Н10 заполните значениями объёма выпуска изделия 300, 250, 200, 150, 100 соответственно;
- выделите диапазон ячеек С10:H15;
- вызовите команду Данные ® Таблица подстановки. В открывшемся окне укажите, что по строкам выделенного диапазона располагаются значения цены изделия B3, а по столбцам – значения объёма выпуска B2, после чего нажмите кнопку ОК.
Построение диаграммы
По результатам анализа чувствительности функции NPV к значению параметров Цена изделия и Объём выпуска изделий строится объёмная гистограмма:
- выделите диапазон ячеек D11:H15;
- вызовите команду Вставка ® Диаграмма. В открывшемся окне последовательно выберите Объёмную гистограмму: во вкладке Ряд окна Исходные данные в поле «Подписи оси Х» внесите диапазон ячеек D10:H10 (выделите диапазон мышью); в окне Размещение диаграммы желательно установить переключатель в режим «На имеющемся листе», остальные настройки диаграммы выполните произвольно.
Изучение функции MS Excel «Диспетчер сценариев»
|
|
|
При разработке реальных проектов достаточно часто возникает задача расчёта нескольких вариантов развития (так называемых оптимистичных и пессимистичных сценариев) в зависимости от набора входных параметров. Как правило, таких вариантов несколько, они отличаются целым набором входных переменных, причём расчёт выходных величин для любого сочётания входных переменных не требуется.
Рассчитайте значение функции NPV для трёх наборов входных переменных (сценариев), отличающихся значениями переменных Q, P, V, R, N, S:
- выделите диапазон ячеек В2:В6;
- вызовите команду Сервис ® Сценарии;
- в открывшемся окне Диспетчер сценариев после нажатия кнопки Добавить дайте название первому сценарию «Вероятный»; укажите диапазон изменяемых ячеек B2:B6; в следующем окне Значение ячеек сценария введите наиболее вероятные значения этих параметров из табл. 2.3.1;
- аналогично предыдущему пункту последовательно добавьте сценарии «Наилучший» и «Наихудший», значения параметров проекта для которых находятся в соответствующих столбцах табл. 2.3.1;
- не закрывая окна Диспетчер сценариев, нажмите кнопку Отчёт, выберите настройку Структура, а ячейка результата - С10. Полученный лист «Структура сценария» будет отображать значения величины NPV из ячейки С10 для трёх наборов исходных параметров проекта – вероятного, наилучшего, наихудшего.
|
|
|
- откройте окно Диспетчер сценариев на листе рабочей книги MS Excel «Анализ чувствительности NPV», убедитесь, что Ваш набор сценариев сохранен, и выведите отчёт в формате сводной таблицы для диапазона ячеек D11:H15. Оцените полученный лист отчёта.
Задание № 1 для самостоятельного выполнения
По образцу п. 2 на листе рабочей книги MS Excel «Анализ чувствительности NPV» (в свободном месте) выполните однопараметрический анализ чувствительности NPV. Варианты заданий приведены в табл. 2.3.3. Диапазоны изменения параметров следует взять из табл. 2.3.1. Шаг изменяемого параметра выбирается самостоятельно так, чтобы количество значений варьируемого параметра в таблице подстановки было в пределах 4..8.
Таблица 2.3.3
Варианты заданий
для однопараметрического анализа чувствительности.
| Номер варианта | Название варьируемого параметра | Расположение варьируемого параметра |
| 1 | Объём выпуска изделий, шт. | по строкам |
| 2 | Норма дисконта, % | по строкам |
| 3 | Переменные затраты, руб. | по строкам |
| 4 | Постоянные затраты, руб. | по строкам |
| 5 | Остаточная стоимость, руб. | по строкам |
| 6 | Налог на прибыль, % | по строкам |
| 7 | Норма дисконта, % | по столбцам |
| 8 | Переменные затраты, руб. | по столбцам |
| 9 | Постоянные затраты, руб | по столбцам |
| 10 | Остаточная стоимость, руб. | по столбцам |
| 11 | Налог на прибыль, % | по столбцам |
|
|
|
Задание № 2 для самостоятельного выполнения
По образцу п. 3 на листе Вашей рабочей книги MS Excel «Анализ чувствительности NPV» выполните анализ чувствительности NPV к изменению одновременно двух входных параметров проекта. Варианты заданий приведены в табл. 2.3.4. Диапазоны изменения параметров следует взять из табл. 2.3.1. Шаг изменяемого параметра выбирается самостоятельно так, чтобы количество значений каждого варьируемого параметра в таблице подстановки было в пределах 3..6.
Таблица 2.3.4
Варианты заданий
для двухпараметрического анализа чувствительности
| Номер варианта | Название первого варьируемого параметра | Название второго варьируемого параметра |
| 1 | Объём выпуска изделий, шт. | Налог на прибыль, % |
| 2 | Норма дисконта, % | Переменные затраты, руб. |
| 3 | Переменные затраты, руб. | Объём выпуска изделий,шт. |
| 4 | Постоянные затраты, руб. | Переменные затраты, руб. |
| 5 | Остаточная стоимость, руб. | Норма дисконта, % |
| 6 | Налог на прибыль, % | Остаточная стоимость, руб. |
| 7 | Норма дисконта, % | Налог на прибыль, % |
| 8 | Переменные затраты, руб. | Остаточная стоимость, руб. |
| 9 | Постоянные затраты, руб. | Объём выпуска изделий,шт. |
| 10 | Остаточная стоимость, руб. | Объём выпуска изделий,шт. |
| 11 | Налог на прибыль, % | Переменные затраты, руб. |
|
|
|
Результаты выполнения работы представьте преподавателю.
Дата добавления: 2018-10-27; просмотров: 285; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!