· На вкладке Данные выберите команду Анализ «что-если»/ Диспетчер сценариев …
· Нажмите кнопку Отчет.
· Установите переключатель в положение Сводная таблица.
5.1 В поле Ячейки результата введите ссылки на ячейки, значения которых были изменены с помощью сценариев H12.
Нажмите ОК. Excel вставляет новый лист со сводной таблицей:
Самостоятельная работа
Задача 1. По облигации номиналом 100 тыс. руб, выпущенной на 6 лет, предусмотрен следующий порядок начисления процентов: в первый год - 10%, в два последующих – 20%, в оставшиеся три года – 25%. Рассчитаем будущую (наращенную) стоимость облигации по сложной процентной ставке.
6.1 Подготовьте исходные данные на рабочем листе книги Сценарии2:
Функция БЗРАСПИС возвращает будущее значение основного капитала после начисления сложных процентов. Функция БЗРАСПИС используется для вычисления будущего значения инвестиции с переменной процентной ставкой.
Синтаксис: БЗРАСПИС(первичное;план)
Первичное – стоимость инвестиции на текущий момент.
План – это массив применяемых процентных ставок.
Пример: БЗРАСПИС(1;{0,09;0,11;0,1}) = 1,33089
В отдельный столбец (A2:A7) введите список значений процентов. Формат ячеек A2:A7 – процентный. В ячейку H4 введите функцию БЗРАСПИС.
Сравните результат:
Создайте три сценария:
· Исходный вариант (в первый год – 10%, в два последующих – 20%, в оставшиеся три года – 25%);
· Лучший вариант (в первый год – 10%, в два последующих – 25%, в оставшиеся три года – 30%);
· Худший вариант (в первый год – 10%, в два последующих – 12%, в оставшиеся три года – 15%).
Выведите все отчеты для сценариев.
Задача 2. Рассчитать стоимость разработки программных продуктов в некоторой фирме, занимающейся созданием программного обеспечения (ПО). Рабочий лист для выполнения таких расчетов показан ниже на рисунке.
6.2 Всем изменяемым ячейкам присвоены соответствующие имена. Например, ячейка В10 имеет имя Аттестация_1.
Таблица для расчета стоимости состоит из двух разделов.
· В диапазоне A1:D3 записаны количества необходимых для выполнения программного проекта разработчиков ПО, руководителей групп разработчиков и менеджеров программных проектов, а также их почасовые ставки. Количество руководителей групп вычисляется в ячейке В2 таким образом, чтобы на четырех разработчиков приходилосьб не менее одного руководителя (=ОКРВВЕРХ(К_разр/4;1)).
Функция ОКРВВЕРХ округляет число до ближайшего целого или до ближайшего кратного указанному значению.
Синтаксис: ОКРВВЕРХ (число;точность)
Число – округляемое значение.
Точность – кратное, до которого требуется округлить.
· В диапазоне A5:D16 приведен список этапов программного проекта, для каждого этапа определены количество часов (на одного исполнителя) и исполнители, необходимые для выполнения этого этапа, а также вычисляется стоимость каждого этапа.
Создайте сценарии самого худшего случая разработки ПО, когда разработчиков не хватает, у них высокая почасовая ставка, и вследствие этого сокращено время выполнения отдельных этапов проекта:
К_разр: 3
К_мен: 1
Ст_разр: 100
Ст_рук: 120
Ст_мен: 150
Разр_спец: 2
Утв_спец: 1
Код_первое: 30
Код_второе: 20
Аттестация_1: 1
Код_третье: 10
Отладка: 10
Аттестация_2: 1
Подг_версии: 2
Утв_версии: 1
В наилучшем случае разработки программного продукта участвует достаточное число специалистов, они имеют относительно низкие почасовые ставки, а на выполнение этапов проекта отводится достаточное количество времени:
К_разр: 15
К_мен: 2
Ст_разр: 75
Ст_рук: 90
Ст_мен: 110
Разр_спец: 8
Утв_спец: 2
Код_первое: 100
Код_второе: 80
Аттестация_1: 10
Код_третье: 60
Отладка: 50
Аттестация_2: 10
Подг_версии: 15
Утв_версии: 5
Постройте на основе сценариев итоговые отчеты.
