605

первый столбец дипазона — столбец со значениями входного параметра, последний — содержит выходные данные о годовых переменных издержках.

Рис. 4.13. Математическая модель таблицы подстановки к заданию 18

г) Вызываем команду Данные, Таблица подстановки; в диалоговом окне команды в текстовом поле Подставлять значения по строкам в: указываем мышью клетку D1 и щелкаем кнопку ОК (поскольку входные параметры — это цены, то клетка D1 выбрана в качестве ячейки подстановки для столбцов).

д) В результате MS Excel создаст таблицу подстановки с одним параметром. В диапазоне D15:F15 этой таблицы прибыль, доход и переменные издержки вычисляются для цены $1,00. В диапазоне D16:F16 этой таблицы прибыль, доход и переменные издержки вычисляются для цены $1,25 и так далее для всего диапазона цен. Цена из всех перечисленных, при которой достигается максимальная прибыль, равна $3,75. Эта цена даст годовую прибыль в размере $58125, годовой доход — $117187,50 и годовые переменные издержки — $14062,50 (рис. 4.14).

Рис. 4.14. Таблица подстановки с двумя изменяемыми параметрами к заданию 18

На листе рабочей книги MS Excel готовятся два массива значений изменяемых параметров, один из которых — вектор-строка, другой — смежный вектор-столбец. Вводится формула для вычисления одной функции, зависящей от этих двух параметров. Для вычисления этой функции создается массив формул.

Задание 19

Предположим, предприниматель хочет определить, как будет варьироваться ежегодная прибыль при изменении цены стакана лимонада в диапазоне от $1,50 до $5,00 (с шагом $0,25) и изменении себестоимости стакана лимонада в диапазоне от $0,30 до $0,60 (с шагом $0,05).

61

Порядок выполнения задания

Поскольку мы изменяем два входных параметра, нам потребуется таблица подстановки с двумя параметрами, которую будем создавать на листе «Таблица_подстановки2» (рис. 4.15).

Рис. 4.15. Математическая модель таблицы подстановки к заданию 19

1.В диапазоне C15:C29 листа «Задание 19_Таблица_подстановки» рабочей книги MS Excel перечислите интересующие нас цены (от $1,00 до $5,00 с шагом $0,25), наложив на их диапазон формат денежный $ Английский (США) — число десятичных знаков 2.

2.В диапазоне D14:J14 этого же листа перечислим интересующие нас значения себестоимости стакана лимонада (от $0,30 до $0,60 с шагом $0,05).

3.Затем в клетку C14 запишите формулу для вычисления годовой прибыли и наложите на диапазон С14:J14 формат денежный $ Английский (США) — число десятичных знаков 2.

4.Далее выделите диапазон таблицы (C14:J29) и вызовите команду Данные, Таблица подстановки. Ячейку D1 (Цена) укажите в качестве ячейки для подстановки строк, а ячейку D2 (Себестоимость стакана лимонада) — для подстановки столбцов и щелкните кнопку ОК. В результате получим таблицу подстановки с двумя параметрами (рис. 4.16).

5.Сохраните полученные результаты.

62

Рис. 4.16. Таблица подстановки к заданию 19

Контрольные задания

1. Вы хотите построить новый дом. Сумма кредита для строительства нового дома (с выплатой в течение 15 лет) зависит от продажной цены за дом, в котором вы живете сейчас. Кроме того, нет уверенности в том, какой будет годовая процентная ставка при заключении договора кредита. Определите, как ежемесячные выплаты будут зависеть от суммы, взятой в кредит, и годовой процентной ставки. Выполните расчеты в предположении, что сумма кредита может меняться от $300 000 до $650 000 с шагом $50 000, а ставка процента от 5 до 8 % с шагом 0,5 [13].

2. В течение трех лет в конце каждого месяца вы планируете пополнять свой пенсионный счет на $100 под 20 % годовых. Первоначальные вложения на пенсионный счет составили $2000. Какова будет сумма на пенсионном счете через 3 года? Используя таблицу подстановки (рис. 4.17), проанализируйте влияние параметров Ставка (Параметр 1) и Выплата (Параметр 2) на функцию БС (будущую стоимость простого аннуитета) [3].

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

4.3.Информационная технология «Диспетчер сценариев»,

еевозможности, использование, анализ чувствительности решения

Кчислу инструментов электронной таблицы MS Excel, позволяющих исследовать влияние входных параметров модели на ее результатные показатели, относится и Диспетчер сценариев. В Диспетчере сценариев можно сохранять наборы значений входных параметров и зави-

63

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

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

Сценарный подход практически не ограничивает число одновременно изменяемых входных параметров модели. Для каждого сценария можно проводить анализ чувствительности решения, изменяя в модели одновременно до 32 входных параметров. Нет ограничений и на число результатных показателей в сценарии. Напомним, что в Подборе параметра используется один входной параметр, зависящих от него результатных показателей может быть несколько, а в Таблицах подстановки — один или два входных параметра, результатный показатель — один.

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

Рассмотрим порядок использования сценарного подхода при оценке эффективности инвестиционного проекта продаж автомобилей (задание 20).

Задание 20

Разработан инвестиционный проект продаж автомобиля новой модели в течение 5 лет на внутреннем автомобильном рынке [13]. Математическая модель, список ее входных параметров и предположений, а также результатные показатели проекта представлены на рис. 4.18.

Используя Диспетчер сценариев, создайте лучший, худший и наиболее вероятный сценарии для результатных показателей этого инвестиционного проекта в зависимости от числа проданных автомобилей в первом году, цены автомобиля в первом году, ежегодного роста продаж в процентах. Значения изменяемых входных параметров модели для каждого сценария приведены в табл. 4.1. Подготовьте отчет по заданным сценариям в форме структурной таблицы.

				Таблица 4.1
		Значения изменяемых входных параметров
	Сценарий	Продажи автомобилей	Ежегодный рост продаж,	Цена автомобиля в первый
		в первый год	%	год, тыс. $
	Лучший	20000	20	10
	Наиболее вероятный	10000	10	7,5
	Худший	5000	2	5

Порядок выполнения задания

1.Запустите на выполнение табличный процессор MS Excel. В новой рабочей книге присвойте текущему листу имя «Сценарии». На листе «Сценарии» создайте математическую модель инвестиционного проекта продаж автомобиля (см. рис. 4.18). Входные параметры модели расположите на листе в диапазоне B1 : C8. Результатных показателей модели, определяющих эффективность инвестиционного проекта, всего шесть. Это чистая прибыль по годам за 5 лет, получаемая в конце каждого года, соответствующие значения которой расположены на листе «Сценарии» в диапазоне B18 : F18, а также чистая приведенная стоимость (ЧПС) инвестиционного проекта — это сумма значений чистой прибыли по годам, дисконтированных к началу первого года. Значение ЧПС расположено на листе «Сценарии» в клетке B19. Для вычисления значения чистой приведенной стоимости используется стандартная функция ЧПС из категории «Финансовые». Назначение и синтаксис функции см. Справку MS Excel 2003.

2.Используя полученную модель и логические предположения проекта, выполните расчеты и сравните полученные результаты с приведенными на рис. 4.19.

64

Рис. 4.18. Математическая модель оценки проекта продаж автомобиля

Рис. 4.19. Расчетные показатели оценки инвестиционного проекта продаж автомобиля

65

3.Установите курсор в любое место на листе «Сценарии» и выполните команду меню Сервис, Сценарии. На экран выводится диалоговое окно команды с заголовком «Диспетчер сценариев» (рис. 4.20), щелкните в нем кнопку «Добавить…». На экран выводится еще одно окно с заголовком «Добавление сценария» (рис. 4.21).

4.В окне «Добавление сценария» в текстовом поле Название сценария: задайте название сценария (Лучший), а в поле Изменяемые ячейки: укажите мышью диапазон C2:C4 и нажмите кнопку ОК. На экран выводится еще одно окно с заголовком «Значения ячеек сценария» (рис. 4. 22).

Рис. 4.20. Диалоговое окно «Диспетчер сценариев» Рис. 4.21. Диалоговое окно «Добавление сценария»

5.В окне «Значения ячеек сценария» в соответствующие поля введите значения изменяемых ячеек (20000 20 10) для сценария «Лучший» и нажмите кнопку «Добавить».

6.Повторите пп. 4 и 5 для сценариев «Наиболее вероятный» — значения изменяемых параметров (10000 10 7,5) и «Худший» — значения изменяемых параметров (5000 2 5). После ввода данных по последнему сценарию нажмите кнопку ОК.

7.Появится диалоговое окно с заголовком «Диспетчер сценариев» (рис. 4.23). В окне «Диспетчер сценариев» нажмите кнопку Отчет, выберите тип отчета — Структура, а в поле Ячейки результата покажите мышью диапазон B18:F18; B19.

Рис. 4.22. Диалоговое окно «Значения ячеек сценария» Рис. 4.23. Диалоговое окно со списком сценариев

66

8. Просмотрите результаты отчета на листе «Структура сценария», сравните их с приведенными на рис. 4.24. Проанализируйте результаты в отчете и сделайте выводы.

Рис. 4.24. Структура и результаты созданных сценариев

С целью закрепления практических навыков по использованию «Диспетчера сценариев» выполните контрольные задания и предъявите работу к защите преподавателю.

Контрольные задания

1.В окне «Диспетчер сценариев» (см. рис. 4.21) удалите сценарий с названием «Лучший» и создайте новый сценарный отчет. Проанализируйте результаты сценарного отчета и сделайте выводы.

2.Добавьте в новый сценарный отчет сценарий с названием «Высокая цена», в котором цена продажи автомобиля равна 15 тыс. $, а два других входных параметра (продажи в первый год и ежегодный рост продаж) остаются такими же, как в «Наиболее вероятном сценарии». Проанализируйте результаты отчета и сделайте выводы.

4.4. Информационная технология «Счетчики» как средство быстрого генерирования вариантов деловых решений,

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

Кчислу инструментов электронной таблицы MS Excel, посредством которых исследуется влияние многих входных параметров модели на ее результатные показатели, относится также информационная технология «Счетчики». Она позволяет быстро изменять значения входных параметров в пределах заданных диапазонов, генерируя очередное решение, и наблюдать влияние изменений на все вычисляемые показатели, в том числе результатные [13].

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

Рассмотрим порядок использования информационной технологии «Счетчики» для быстрого генерирования решений к проекту продаж автомобилей (задание 21).

Задание 21

Провести анализ чувствительности решения проекта продаж автомобиля новой модели на внутреннем автомобильном рынке в течение 5 лет в зависимости от изменения значений ключевых входных параметров модели в заданных диапазонах (табл. 4.2). В частности, быстро изменяя значения ключевых входных параметров, хотелось бы наблюдать влияние этих изменений на чистую приведенную стоимость (ЧПС) проекта продаж.

Математическая модель проекта обсуждалась в предыдущем подразделе и приведена здесь на рис. 4.25 с обрамлением списка ключевых входных параметров. При заданных исходных предположениях чистая приведенная стоимость проекта продаж автомобиля равна 70054,34 тыс. $ (рис. 4.26).

67

		Таблица 4.2
Значения ключевых входных параметров модели
Наименование ключевого параметра	Минимальное значение	Максимальное значение
Продажи автомобиля в первом году, ед.	5000	30000
Ежегодный рост продаж, %	0	50
Цена автомобиля в первый год, тыс. $	6	20
Себестоимость автомобиля в первый год, тыс. $	2	15

Рис. 4.25. Математическая модель оценки проекта продаж автомобиля (без счетчиков)

Счетчик — это кнопка , расположенная на панели инструментов «Формы». Если эту кнопку поместить на лист рабочей книги Excel, связать ее с какой-либо ячейкой этого листа и использовать как элемент управления этой ячейкой, то, щелкая мышью счетчик, можно увеличивать или уменьшать числовое значение в этой ячейке. С помощью Счетчиков пользователю удастся сгенерировать совокупность сценариев, для которых каждое вводимое значение ключевого входного параметра варьируется в диапазоне от его минимальной до максимальной величины. При этом пользователь видит, как изменяются интересующие его выходные результаты в ответ на изменения ключевого входного (ЧПС) параметра. В задании рассматриваются четыре ключевых входных параметра: продажи автомобилей в первом году, ежегодный рост продаж в %, цена автомобиля в первый год, себестоимость автомобиля в первый год. Для каждого из них должен быть создан свой Счетчик.

Порядок выполнения заданий

1.На листе рабочей книги MS Excel в соответствии с рис. 4.25 создайте математическую модель решаемой задачи.

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

68

ние 5 лет и сверьте полученные результаты с приведенными на рис. 4.26. Обратите внимание на значение результатного выходного параметра «Чистая приведенная стоимость (ЧПС) проекта».

3. Выделите строки, в которые хотите вставить кнопки Счетчик (на рис. 4.26 это строки 2–5), и затем измените высоту строк, выполнив в меню команду Формат, Строка, Высота — 25, OK.

Рис. 4.26. Расчетные показатели оценки проекта продаж автомобиля (без счетчиков)

4. Выведите на экран панель инструментов «Формы», выполнив в меню команду Вид,

Панели инструментов, Формы (рис. 4.27).

Рис. 4.27. Панель инструментов «Формы»

5.Щелкните на панели инструментов «Формы» кнопку Счетчик и затем нарисуйте прямоугольник для счетчика от верхней до нижней границы ячейки D2. В результате в ячейке D2 появится счетчик (рис. 4.28).

6.Щелкните счетчик правой клавишей мыши и выберите в контекстном меню команду Копировать. Далее установите табличный курсор в клетку D3, нажмите правую клавишу мыши и выберите в контекстном меню команду Вставить. Таким же образом скопируйте счетчики в ячейки D4 и D5. Теперь у Вас четыре счетчика (рис. 4.29).

69

Рис. 4.28. Технология создания счетчика для ключевого входного параметра «Продажи в первый год, единиц»

Рис. 4.29. Созданные счетчики для ключевых входных параметров

70