Создание отчета по сценарию в виде сводной таблицы

 

· На вкладке Данные выберите команду Анализ «что-если»/ Диспетчер сценариев …

· Нажмите кнопку Отчет.

· Установите переключатель в положение Сводная таблица.

 

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

 

Постройте на основе сценариев итоговые отчеты.