Чтобы переместить задачу, подведите курсор к задаче (курсор примет вид ) и, нажав левую кнопку мыши, перемещайте задачу. Если появится окно Мастер планирования с предупреждением о конфликте планирования, то установите переключатель Продолжить. Конфликт планирования допускается и нажмите кнопку ОК.

Чтобы перераспределить ресурсы, назначенные задаче, используйте кнопку Назначить ресурсы .

Откройте копию проекта, которая была сохранена перед корректировкой. В MS Project имеется средство автоматического выравнивания использования ресурсов для устранения превышения доступности. Выполните команду СервисВыравнивание загрузки ресурсов… Установите переключатель Выравнивать автоматически и нажмите кнопку Выровнять.

С помощью команды ВидТаблицыСуммарные данные убедитесь, что пиковая загрузка уменьшилась, но превышение доступности сохранилось. Чтобы просмотреть результаты выравнивания, выберите представление Диаграмма Ганта с выравниванием и посмотрите дату окончания проекта после автоматического выравнивания. Сравните результаты автоматического выравнивания и выравнивания вручную, выполненного вами ранее. Завершите работу с копией проекта и вернитесь в исходный проект.

8. Отслеживание хода выполнения проекта

Перед началом процесса отслеживания рекомендуется сохранить базовый план. Базовый план (до 11 на проект) представляет снимок календарного плана на момент сохранения и содержит всю информацию о задачах, ресурсах и назначениях. С помощью базового плана можно отслеживать ход выполнения, просматривать отклонения и вносить необходимые исправления. Чтобы сохранить базовый план выполните команду СервисОтслеживаниеСохранить базовый план... Нажмите кнопку ОК.

Мастер отслеживания помогает создать таблицу, в которой удобно обновлять сведения о ходе выполнения задач. На панели инструментов Консультант нажмите кнопку Отслеживание. В боковой области выберите ссылку Подготовка к отслеживанию хода работы над проектом. На шаге 1 выберите режим отслеживания: вручную (укажите Нет). На шаге 2 укажите Всегда отслеживать путем указания процента завершения по трудозатратам. В результате в текущем представлении MS Project появился дополнительный столбец %завершения по трудозатратам, а на диаграмме Ганта появились соответствующие данные. Задайте для задачи Предварительное экономическое обоснование проекта процент завершения 70%.

Для сравнение фактических и плановых трудозатрат для задач выполните команды ВидДиаграмма Ганта и ВидТаблицаТрудозатраты. Сравните значения в полях Трудозатраты, Базовые, Фактические и .Оставшиеся.

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

9. Печать и публикация проекта

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

В MS Project предлагается более 20 встроенных отчетов. Выполните команду ВидОтчеты. Выберите категорию Обзорные… и обзорный отчет Сводка по проекту. Также просмотрите отчет Дела по исполнителям (категория Назначения…).

В категории Настраиваемые… представлены все стандартные отчеты. В окне Настраиваемые отчеты можно создать новые отчеты (кнопка Создать…), изменить существующие (кнопка Изменить…), скопировать в шаблон (из шаблона) Global.MPT и т.д. В окне Настраиваемые отчеты выберите отчет Использование трудовых ресурсов и нажмите кнопку Изменить…На вкладке Определение замените Недели на Месяцы .На вкладке Подробности укажите Формат даты Январь 2002. Нажмите кнопку ОК. Просмотрите отчет (кнопка Просмотр).

Для публикации сведений о проекте в Internet можно сохранить их в формате HTML. Выполните команду ФайлСохранить как веб-страницу… Введите имя экспортируемого файла в поле Имя файла и нажмите кнопку Сохранить. В первом окне Мастера экспорта нажмите кнопку Далее. Во втором окне установите переключатель Использовать существующую схему и нажмите кнопку Далее. В следующем окне Выберите схему для данных Сводная таблица задач и ресурсов и нажмите кнопку Далее. В следующем окне установите флажок Экспорт на основе шаблона HTML. Для выбора шаблона HTML нажмите кнопку Обзор. В окне Обзор выберите любой шаблон и нажмите кнопку ОК. В окне Мастер экспорта-параметры схемы нажмите кнопку Готово. Просмотрите созданный файл в формате HTML.

Лабораторная работа №5 Планирование работ средствами Microsoft Excel

бизнес информационный excel expert

Цель. Изучить некоторые возможности Microsoft Excel (условное форматирование, проверка ввода, работа со ссылками и массивами и т.д.), приобрести навыки использования этих инструментов для решения задач планирования работ в проектной организации.

Задание

1. Ввести данные на рабочие листы Исходные данные, Распределение, Диаграмма Ганта и Зарплата согласно заданию.

2. Осуществить распределение проектировщиков по проектам.

3. Составить ведомость на выплату заработной платы.

Основные сведения

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

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

Рассмотрим следующую ситуацию. Проектной организации, где работает 6 конструкторов и 4 технолога, поручили выполнить 6 проектов (Проект А, Проект Б и т.д.). Работа над каждым проектом включает два этапа: 1) этап конструкторской подготовки производства (КПП) и 2) этап технологической подготовки производства (ТПП). Необходимо распределить проектировщиков по проектам, назначить даты начала этапов, рассчитать даты завершения этапов. Для простоты планирование осуществляется только на один месяц - май 2005 года..

Накладываемые ограничения.

1. Этап ТПП может начаться только после завершения предыдущего этапа КПП.

2. Над одним проектом может работать не более 4 конструкторов и не более 3 технологов.

3. Все проекты должны завершиться не позднее заданных сроков.

4. Один проектировщик может участвовать в нескольких проектах, но одновременно может работать только над одним проектом.

Технология работы

1. Создание рабочего листа Исходные данные

Запустите на выполнение программу Microsoft Excel, создайте рабочую книгу с именем Планирование работ(<ФИО студента>).xls. Переименуйте лист Лист1 с помощью команды меню ФорматЛистПереименовать лист. Задайте новое имя Исходные данные.

Другой способ переименовать лист - двойной щелчок левой кнопкой мыши по имени листа.

Введите данные на лист Исходные данные согласно рис. 5.1 и приведенным ниже указаниям.



Рис. 5.1. Рабочий лист Исходные данные

Для обеспечения проверки вводимых значений в ячейку C1 выполните команду ДанныеПроверка… В окне Проверка вводимых значений на вкладке Параметры задайте Тип данных Список. В поле Источник введите текст

Январь;Февраль;Март;Апрель;Май;Июнь;Июль;Август;Сентябрь;Октябрь;Ноябрь;Декабрь

На вкладке Сообщение для ввода задайте Заголовок Месяц и Сообщение Выберите месяц, для которого создается план работ. Нажмите кнопку ОК.

Для ячейки D1 самостоятельно задайте проверку ввода, указав в качестве Источника текст 2004;2005;2006;2007;2008;2009;2010

Чтобы разместить текст в нескольких ячейках (например, в ячейках D3:F3) необходимо выделить эти ячейки и нажать кнопку Объединить и поместить в центре .

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

Чтобы разместить текст в ячейке (ячейках) по центру с переносом слов (например, в ячейке D15 или в ячейках В4:В5), выполните команду Формат Ячейки… и на вкладке Выравнивание задайте по горизонтали по центру, по вертикали по центру. Установите флажок переносить по словам. Нажмите кнопку ОК.

Для диапазона ячеек В6:В11 укажите Отступ 1 (команда меню Формат Ячейки…, вкладка Выравнивание).

Чтобы автоматизировать ввод числовых рядов (1, 2, 3,…), введите числа 1 и 2 в соседние ячейки, затем выделите эти две ячейки, и с помощью мыши протяните в нужном направлении.

Для удобства дальнейшей работы рекомендуется создавать имена для ячеек и диапазонов ячеек. Чтобы быстро создать имя для диапазона ячеек Н5:Н13, выделите эти ячейки и щелкните левой кнопкой мыши по полю Имя (слева от строки формул), введите имя Праздники и нажмите клавишу Enter.

ВНИМАНИЕ! Имена вводятся БЕЗ пробелов!

ВНИМАНИЕ! Ввод имени завершается нажатием клавиши ENTER!

Самостоятельно создайте имена: СпецКонструктор для ячейки В16, СпецТехнолог: для ячейки В17, ЧислоКонструкторов для ячейки D16, ЧислоТехнологов для ячейки D17, ВсегоПроектировщиков для ячейки D18 и Специальность для ячеек С22:С31.

Для ячеек С22:С31 задайте проверку вводимых значений (Тип данных Список, Источник =$В$16:$В$17). Введите данные в таблицу Список сотрудников-проектировщиков.

Для автоматизации подсчета числа конструкторов в ячейку D16 введите формулу =СЧЁТЕСЛИ(Специальность;СпецКонструктор)

Для ввода имен удобно использовать клавишу F3.

В ячейку D17 формулу введите самостоятельно.

В ячейке D18 подсчитайте сумму.

Создайте лист Распределение и введите данные на этот лист согласно рис. 5.2 и приведенным ниже указаниям.

Рис. 5.2. Рабочий лист Распределение

Чтобы не копировать данные с рабочего листа Исходные данные в диапазон ячеек А3:С12 лист Распределение, введите в ячейку А3 формулу

='Исходные данные'!A22

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

Скопируйте эту формулу в ячейки диапазона А3:С12.

Чтобы скопировать формулу из ячейки А3 в ячейки А3:С12, подведите курсор к черному квадратику в правом нижнем углу ячейки А3, чтобы курсор превратился в черный крестик. Нажав левую кнопку мыши, «протащите» курсор по ячейкам А3:С12.

Чтобы заполнить ячейки D2:I2, можно применить два способа (заполните ячейки D2:I2 двумя способами):

Способ 1. На листе Исходные данные выделите ячейки В6:В11 и скопируйте их в буфер обмена. Затем щелкните правой кнопкой мыши по ячейке D2 на листе Распределение и в контекстном меню выберите команду Специальная вставка… В окне Специальная вставка установите флажок транспонировать и нажмите ОК.

Способ 2. Выделите ячейки D2:I2 на листе Распределение. В строке формул введите формулу

=ТРАНСП('Исходные данные'!B6:B11)

Функция ТРАНСП(массив) находится в категории Ссылки и массивы.

Функция ТРАНСП() должна быть введена как формула массива. Для этого необходимо одновременно нажать клавиши Ctrl, Shift и Enter. В результате в строке формул введенная формула будет заключена в фигурные скобки.

Для проверки ввода в диапазон D3:I12 задайте проверку данных с параметрами Тип данных Список, Источник 0;1

Для ячейки J2 создадим примечание. Щелкните правой кнопкой мыши по ячейке J2 и выберите команду Добавить примечание. Введите примечание Количество проектов, в которых участвует работник.

В ячейках J3:J12 подсчитайте сумму по соответствующей строке.

В ячейку D13 введите формулу

=СУММЕСЛИ($C3:$C12;СпецКонструктор;D3:D12)

В остальные ячейки диапазона D13:I14 формулы введите самостоятельно.

Чтобы облегчить ввод данных в диапазон ячеек D3:I12, необходимо конструкторов и технологов сгруппировать отдельно. Применим сортировку таблицы на листе Распределение. Выделите диапазон ячеек А2:К12 и выполните команду меню ДанныеСортировка. В окне Сортировка диапазона в поле Сортировать по задайте Специальность. Нажмите кнопку ОК.

Заполните диапазон ячеек D3:I12 согласно рис. 5.2 (с учетом накладываемых ограничений).

Формулы для ячеек К3:К12 введем позднее. Самостоятельно отформатируйте лист Распределение, чтобы он соответствовал рис. 5.2.

Создайте рабочий лист Диаграмма Ганта. Введите данные на этот лист согласно рис. 5.3 и приведенным ниже указаниям.

Рис. 5.3. Рабочий лист Диаграмма Ганта

Чтобы автоматизировать заполнение ячеек В3:В14, ни один из ранее рассмотренных способов не подходит. Введите в ячейку В3 формулу

=СМЕЩ('Исходные данные'!B$6;$A3-1;0)

Размножьте эту формулу в диапазоне ячеек В3:В14.

Найдите и прочитайте описание функции СМЕЩ() (категория Ссылки и массивы).

Самостоятельно введите формулы в ячейки С3:С14.

Не забудьте задать для ячеек С3:С14 Числовые форматы Дата, Тип 14.03.99.

В ячейку Е3 введите формулу =СМЕЩ('Исходные данные'!$D$6;A3-1;0)

В ячейку Е4 введите формулу =СМЕЩ('Исходные данные'!$D$6;A3-1;1)

Растяните эти формулы по столбцу Е.

В ячейку F3 введите формулу =СМЕЩ(Распределение!$D$13;0;A3-1)

В ячейку F4 введите формулу =СМЕЩ(Распределение!$D$13;1;A3-1)

Растяните эти формулы по столбцу F.

В ячейку G3 введите формулу =ОКРУГЛВВЕРХ(E3/F3;0)

Растяните эту формулу по столбцу G.

В диапазон Н3:Н14 введите даты начала работ.

Чтобы рассчитать день завершения этапа, используем функцию РАБДЕНЬ(). Она возвращает дату, отстоящую на заданное количество рабочих дней вперед или назад от даты Нач_дата. Рабочими днями не считаются выходные дни и дни, определенные как праздничные. Функция РАБДЕНЬ() используется, чтобы исключить выходные дни или праздники при вычислении даты завершения этапа.

Синтаксис функции РАБДЕНЬ(Нач_дата;Количество_дней;Праздники)

Нач_дата - это начальная дата.

Количество_дней - это количество рабочих дней до или после Нач_дата. Положительное значение аргумента Количество_дней означает будущую дату; отрицательное значение - прошедшую дату.

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

Чтобы найти день завершения этапа в ячейку I3 введите формулу =РАБДЕНЬ(H3;G3-1;Праздники). Растяните формулу по столбцу I.

В ячейку J2 введите формулу

=ДАТАЗНАЧ("1"&'Исходные данные'!C1&'Исходные данные'!D1)

Функция ДАТАЗНАЧ() возвращает числовой формат даты, представленной в виде текста.

Синтаксис функции ДАТАЗНАЧ(Дата_как_текст)

Дата_как_текст - это текст, представляющий дату (например, 30.01.1998).

Оператор & позволяет объединить две текстовые строки в одну строку.

В ячейку К2 введите формулу =J2+1 и размножьте ее по строке.

Отформатируем ячейку J2, чтобы кроме даты, был виден день недели. Подходящего встроенного формата не существует. Чтобы создать его, выполните команду ФорматЯчейки… На вкладке Число выберите Числовые форматы (все форматы), в поле Тип задайте ДД.ММ.ГГ ДДД

Шаблон ДДД отображает день недели в виде Пн, Вт, …, Вс.

Чтобы отформатировать диапазон J2:AN2, скопируйте формат из ячейки J2 в остальные ячейки диапазона

Чтобы скопировать формат из ячейки J2 в диапазон J2:AN2, выделите ячейку J2, нажмите кнопку Формат по образцу . Рядом с курсором появится знак кисти. Выделите диапазон J2:AN2.

Чтобы выделить цветом выходные и праздничные дни, воспользуемся условным форматированием. Выделите ячейку J2 и выполните команду ФорматУсловное форматирование… Задайте данные согласно рис. 5.4.

Условие 1 задает формат для выходных дней (с помощью кнопки Формат… задайте желтый цвет заливки ячеек). Условие 2 задает формат для праздничных дней (задайте красный цвет заливки ячеек).

При вводе формул в окне Условное форматирование удобнее не вводить формулы, а вставлять их из буфера обмена, предварительно набрав и отладив в какой-либо ячейке. Для копирования формулы выделите ячейку, затем В СТРОКЕ ФОРМУЛ выделите формулу и скопируйте ее в буфер обмена (кнопка Копировать ). В окне Условное форматирование в нужном месте выполните команду Вставить (кнопка Вставить ).

Чтобы добавить еще одно условие, служит кнопка .

Скопируйте созданный формат из ячейки J2 в остальные ячейки строки.

Рис. 5.4. Окно Условное форматирование для диапазона J2:AN2

Чтобы на диаграмме Ганта были представлено число проектировщиков, участвующих в проекте на данном этапе, в ячейку J3 введите формулу

=ЕСЛИ(И(J$2>=$H3;J$2<=$I3);$F3;"")

Найдите и прочитайте описание функции И() (категория Логические).

Размножьте формулу на диапазон J3:AN14.

Чтобы выделить цветом дни, когда ведется работа над проектом, а также выделить требуемый день завершения проекта, воспользуемся условным форматированием. Для ячейки J3 задайте условное форматирование согласно рис. 5.5.



Рис. 5.5. Окно Условное форматирование для диапазона J3:AN14

Условие 1 задает формат для дней работы над проектом и для последнего допустимого срока (задайте красную границу ячейки и желтый цвет заливки). Условие 2 задает формат для дней работы над проектом (задайте серый цвет заливки для этапов КПП, зеленый для этапов ТПП). Условие 3 задает формат для последнего допустимого срока (повторите формат для Условие 1).

Скопируйте созданный формат из ячейки J3 в диапазон J3:AN3, а также в диапазоны J5:AN5, J7:АN7, J9:АN9, J11:AN11, J13:AN13.

Задайте условное форматирование для ячейки J4 и скопируйте созданный формат в диапазон J4:AN4, а также в диапазоны J6:AN6, J8:АN8, J10:АN10, J12:AN12, J14:AN14.

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

Для активизации мастера суммирования выполните команду меню СервисНадстройки… В окне Надстройки установите флажок напротив строки Мастера суммирования. Нажмите кнопку ОК.

Выполните команду СервисМастерЧастичная сумма… На шаге 1 укажите, где находится таблица для суммирования 'Диаграмма Ганта'!$D$2:$AN$14. Нажмите кнопку Далее. На шаге 2 задайте Суммировать 01.05.05 Вс , Столбец Этап, Оператор =, Значение КПП и затем нажмите кнопку Добавить условие. Нажмите кнопку Далее. На шаге 3 нажмите кнопку Далее. На шаге 4 выберите ячейку J15 и нажмите кнопку Готово. В результате в ячейке J15 находится формула массива

{=СУММ(ЕСЛИ($D$3:$D$14="КПП";1;0))}

К сожалению, она выдает неправильный результат. Отредактируйте формулу, чтобы она приняла вид {=СУММ(ЕСЛИ($D$3:$D$14="КПП";J$3:J$14;0))}

Чтобы отредактировать формулу массива, после редактирования нажмите одновременно клавиши Ctrl, Shift и Enter.

Для ячейки J15 задайте условное форматирование согласно рис. 5.6.

Рис. 5.6. Окно Условное форматирование для диапазона J15:AN15

Условие 1 задает красный цвет заливки, Условие 2 - желтый и Условие 3 - зеленый.

Самостоятельно задайте формулы и форматирование для остальных ячеек диапазона J15:AN16.

В ячейке J17 найдите сумму ячеек J15 и J16. Задайте условия форматирования.

Для построения план-графика работы каждого сотрудника введите данные в диапазон D19:AN26 согласно следующим указаниям.

Создайте имя Сотрудники для диапазона 'Исходные данные'!B22:B31.

Для ячейки F20 задайте проверку вводимых значений (Тип данных Список, Источник =Сотрудники. В ячейку F21 введите формулу

=ВПР(F20;'Исходные данные'!B22:C31;2;0)

Функция ВПР() позволит по заданной ФИО проектировщика (ячейка F20) установить его специальность, просмотрев таблицу 'Исходные данные'!B22:C31.

.В ячейку I20 введите формулу

=ВПР($F$20;Распределение!$B$3:$I$12;G20+2;0)

Она позволяет извлечь информацию об участии проектировщика в конкретном проекте (0 - не участвует, 1 - участвует).

В ячейку J20 введите формулу

=ЕСЛИ($I20=1;СМЕЩ(J$3;ЕСЛИ($F$21=СпецКонструктор;2*($G20-1);2*$G20-1);0);"")

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

.В ячейку J26 введите формулу =СЧЁТ(J20:J25), подсчитывающую число проектов, в которых участвует сотрудник в этот день. Задайте условное форматирование, сигнализирующее красным цветом ячеек, что число проектов больше 1.

Размножьте введенные формулы по соответствующим диапазонам.



Рис. 5.7. Окно Условное форматирование для диапазона J20:AN25

Вернемся к формуле в ячейке I3. Если дата начала работ равна 01.05.05, то на диаграмме Ганта возникает ошибка - при длительности работы в пять дней, на диаграмме работа занимает четыре рабочих дня. Ошибка связана с особенностями работы функции РАБДЕНЬ(). Введите в ячейку I3 «подправленную» формулу

=РАБДЕНЬ(H3-1;G3;Праздники) и размножьте ее по столбцу.

При работе с MS Excel пользователи довольно часто по ошибке портят созданные ранее формулы. Чтобы избежать несанкционированного изменения формул можно защитить ячейки, листы или всю книгу в целом. Чтобы защитить лист Исходные данные перейдите на него и выполните команду СервисЗащитаЗащитить лист... В окне Защитить лист введите пароль (можно не вводить).

ВНИМАНИЕ! Если вводите пароль - обязательно сохраните его!

Чтобы защитить лист Распределение за исключением ячеек D3:I12, в которые будут вводиться данные, выделите диапазон ячеек D3:I12 и выполните команду ФорматЯчейки… На вкладке Защита сбросьте флажок Защищаемая ячейка. Нажмите кнопку ОК. Затем защитите лист Распределение.

Самостоятельно защитите лист Диаграмма Ганта за исключением ячеек H3:H14.

На основе полученного плана работ рассчитаем заработную плату каждого работника согласно формуле

Зарплата работника = Объем работ в днях * Дневная тарифная ставка

Чтобы рассчитать объем работ в днях, перейдите на лист Распределение и создайте вспомогательную таблицу согласно рис. 5.8.

Не забудьте снять защиту листа командой СервисЗащитаСнять защиту листа...

Рис. 5.8. Таблица длительностей этапов проектов

В ячейку О3 введите формулу

=СМЕЩ('Диаграмма Ганта'!$G$3;2*(M3-1);0)

В ячейку Р3 введите формулу =СМЕЩ('Диаграмма Ганта'!$G$3;2*M3-1;0)

Размножьте формулы по таблице.

Создадим имя для диапазона О3:О8. Выделите ячейки О2:О8. Выполните команду ВставкаИмяСоздать…, и в окне Создать имена укажите переключатель в строке выше. Нажмите кнопку ОК.

В результате автоматически будет создано имя Этап_КПП.

Самостоятельно создайте имя Этап_ТПП для диапазона Р3:Р8.

В ячейку К3 введите формулу

=МУМНОЖ(D3:I3;ЕСЛИ(C3=СпецКонструктор;Этап_КПП;Этап_ТПП))

Размножьте формулу по столбцу.

Для расчета зарплаты введите данные на лист Исходные данные согласно рис. 5.9.



Рис. 5.9. Тарифная сетка

Для ячейки Е33 создайте имя ДневнаяТарифнаяСтавка.

В ячейку D36 введите формулу =C36*ДневнаяТарифнаяСтавка и размножьте ее по столбцу.

Создайте лист Зарплата. Введите данные согласно рис. 5.10.

В ячейку А1 введите формулу

="Ведомость на выдачу зарплаты за "&'Исходные данные'!C1&" "&'Исходные данные'!D1

В ячейку Е3 введите формулу =ВПР(B3;Распределение!$B$3:$K$12;10;0)

В ячейку F3 введите формулу

=E3*ВПР(D3;'Исходные данные'!$B$36:$D$53;3;1)

Размножьте формулы по столбцам.



Рис. 5.10. Ведомость на выдачу зарплаты за май 2005 года

Полученное решение не удовлетворяет условиям задачи на странице 50. Например, Петров С.И. одновременно участвует в проектах Г, Д и Е; в отдельные дни (6 мая и с 12 по 16 мая) будет не хватать конструкторов. Поэтому необходимо скорректировать разработанный план работ.

Пользуясь созданными таблицами, перераспределите сотрудников по проектам, назначьте новые даты начала работ по этапам,

Изменяйте данные только в диапазонах ячеек Распределение!D3:I12 и 'Диаграмма Ганта'!H3:H14. Для проверки того, что план-график работы сотрудника удовлетворяет заданным ограничением, используйте ячейку J20.

Для упрощения распределения сотрудников разбейте их на группы по 2-4 человека и переводите эту группу с одного проекта на другой.

Лабораторная работа №6 Бизнес-планирование средствами Microsoft Excel

Цель. Изучить финансовые функции Microsoft Excel, связанные с принятием инвестиционных решений, а также провести анализ чувствительности бизнес-плана с помощью сценариев.

Задание

1. Сформировать основные документы бизнес-плана - Отчет о прибылях и убытках и Отчет о движении денежных средств.

2. Рассчитать показатели эффективности бизнес-плана.

3. Выполнить анализ чувствительности бизнес-плана с помощью сценариев.

Основные сведения

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

Технология работы

1. Общие сведения и начало работы

Скопируйте файл Бизнес-план.xls (уточните у преподавателя местонахождение файла) в свою папку. Откройте этот файл.

На рабочих листах Общие данные, Инв. план, Осн. фонды, Сбыт и Издержки введены основные сведения об инвестиционном проекте Производство фильтроэлементов для автомобилей. Горизонт планирования составляет 5 лет. В сентябре 2006 заканчивается инвестиционный этап проекта и с октября начинается выпуск продукции. На листе Сбыт приведены прогноз цены и объемов продаж.

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

Для удобства работы с MS Excel рекомендуется присваивать имена ячейкам и диапазонам ячеек, так как это повышает наглядность и облегчает понимание используемых формул. На листе Имена приведены все используемые в данной рабочей книге имена.

2. Создание Отчета о прибылях и убытках

Откройте лист Отчет о приб. На рис. 6.1 приведен результирующий вид Отчета о прибылях и убытках.



Рис. 6.1. Результирующий вид Отчета о прибылях и убытках

В ячейку С2 введите формулу =ГОД(ДатаНачала). В ячейку D2 введите формулу =C2+1 и размножьте ее далее по строке.

Для ввода имени ДатаНачала можно щелкнуть по соответствующей ячейке ('Общие данные'!С2) или воспользоваться клавишей F3.

Рассмотрим 1-й способ расчета валового объема продаж - в ячейку С3 введите формулу =Сбыт!B6*Цена/(1+НДС) и размножьте формулу по строке.

2-й способ. Выделите диапазон C4:G4 и введите формулу =ОбъемСбыта*Цена/(1+НДС) Щелкните левой кнопкой мыши в строке формул и одновременно нажмите клавиши Ctrl, Shift и Enter, чтобы ввести формулу массива. Данный стиль ввода формул считается более предпочтительным.

В ячейку С5 введите формулу =C4*Потери и размножьте ее по строке.

В ячейку С6 введите формулу =C3-C5 и размножьте ее по строке.

В диапазон C7:G7 введите формулу массива

{=ОбъемСбыта*(Материалы+Электроэнергия)/(1+НДС)}

В диапазон C8:G8 самостоятельно введите формулу массива.

В диапазон C9:G9 введите формулу массива

{=ОбъемСбыта*СдельнаяЗарплата}

В ячейку С10 введите формулу =C9*СУММ('Общие данные'!$D$8:$D$10) и размножьте ее по строке.

Чтобы ввести в ссылке на ячейку знаки $ удобно пользоваться клавишей F4. Вспомните, для чего служат знаки $ в ссылке на ячейку.

В ячейку С11 введите формулу =СУММ(C7:C10), в ячейку С12 введите формулу =C6-C11, а в ячейку С13 введите формулу

=СУММПРОИЗВ(СтоимостьОПФ;НормыНаРемонт)

Размножьте эти формулы по соответствующим строкам.

В диапазон C14:G14 введите формулу массива {=Топливо/(1+НДС)}

В ячейку С15 введите формулу =C13+C14 и размножьте ее по строке.

В диапазон C16:G16 введите формулу массива

{=Менеджмент*(1+СУММ('Общие данные'!D8:D10))+ПрочиеИздержки}

В ячейку С17 введите формулу =C15+C16 и размножьте ее по строке.

В дальнейшем нам потребуется использовать некоторые финансовые функции Excel. Перед началом работы с финансовыми функциями рекомендуется установить надстройку Пакет анализа. Для этого выполните команду меню СервисНадстройки… В диалоговом окне Надстройки установите флажок напротив строки Пакет анализа и нажмите кнопку OK/ В результате вам будут доступны все финансовые функции Excel, которые ориентированы на решение задач, связанных с расчетами различных аннуитетов, амортизации, цены, доходности и других параметров ценных бумаг (облигаций, акций и т.п.), а также задач оценки эффективности инвестиционных проектов.

В диапазоне C18:G18 для расчета величины амортизационных отчислений за год воспользуемся финансовой функцией АПЛ().

Синтаксис функции АПЛ(Стоимость;Остаток;Период).

Стоимость - это начальная стоимость имущества.

Остаток - это стоимость имущества в конце периода амортизации.

Период - это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации).

В нашем случае Остаток равен 0. Зная годовую норму амортизации, можно рассчитать аргумент функции Период=1/Годовая норма амортизации. Тогда в ячейку D18 нужно ввести формулу

=АПЛ(ЗданияСооружения;0;1/'Осн. фонды'!$D$3)+АПЛ(Оборудование;0;1/'Осн. фонды'!$D$4)

. Для ввода функции АПЛ() выделите ячейку D18, нажмите кнопку Вставка функции , выберите категорию Финансовые, функция АПЛ.

Так как в 2006 году основные фонды будут эксплуатироваться только три месяца, то в ячейку С18 введите формулу =3/12*D18

В диапазон C19:G19 введите значение 0.

Чтобы ввести в один или несколько диапазонов ячеек одинаковое значение, можно выделить эти диапазоны ячеек, затем ввести нужное значение и нажать одновременно клавиши Ctrl и Enter.

Рис. 6.2. Результирующий вид Отчета о движении денежных средств

В ячейку С20 введите формулу =СУММ(C18:C19), в ячейку С21 введите =C12-C17-C20, в С22 введите =ЕСЛИ(C21<0;0;C21*НалогНаПрибыль), в С23 введите =C21-C22. Размножьте эти формулы по соответствующим строкам.

3. Создание Отчета о движении денежных средств

Откройте лист Отчет о движ. На рис 6.2 представлен результирующий вид Отчета о движении денежных средств. В таблице 6.1 приведены формулы, которые необходимо ввести на данный рабочий лист.

Таблица 6.1

№	Наименование статьи	=ГОД(ДатаНачала)	=C2+1
1	Чистая прибыль	{=ЧистПрибыль}	{=ЧистПрибыль}
2	Амортизация	{=Амортизация}	{=Амортизация}
3	КЭШ-ФЛО ОТ ОПЕРАЦИОННОЙ ДЕЯТЕЛЬНОСТИ	=C3+C4	=D3+D4
4	Затраты на приобретение активов	=СУММ('Инв. план'!F4:F6)	0
5	Другие издержки подготовительного периода	='Инв. план'!F3	0
6	КЭШ-ФЛО ОТ ИНВЕСТИЦИОННОЙ ДЕЯТЕЛЬНОСТИ	=-(C6+C7)	=-(D6+D7)
7	Кредиты	0	0
8	Выплаты в погашение кредитов	0	0
9	КЭШ-ФЛО ОТ ФИНАНСОВОЙ ДЕЯТЕЛЬНОСТИ	0	0
10	САЛЬДО НАЛИЧНОСТИ НА НАЧАЛО ПЕРИОДА	=СтартКапитал+C11	=C13+D11
11	САЛЬДО НАЛИЧНОСТИ НА КОНЕЦ ПЕРИОДА	=C12+C5+C8	=D12+D5+D8

4. Расчет показателей эффективности проекта

Откройте лист Эффект. На рис 6.3 представлен результирующий вид этого листа.

В ячейку В4 введите формулу ='Отчет о движ.'!C5+'Отчет о движ.'!C8, в В5 =СУММ($B4:B4), а в В6 =ЕСЛИ(B5<0;1;0). «Растяните» формулы по строкам.

Чтобы рассчитать срок окупаемости (Payback Period, PP), нужно рассчитать момент времени, когда кумулятивный чистый поток денежных средств изменит знак с минуса на плюс. В нашем случае это произойдет между вторым и третьим годом. В ячейке А6 получена грубая оценка срока окупаемости с помощью формулы =СУММ(B6:F6). Для уточнения срока окупаемости в ячейку В7 введите

=A6-0,5-ИНДЕКС(КумЧистПотокДенСредств;;A6)/

(ИНДЕКС(КумЧистПотокДенСредств;;A6+1)-ИНДЕКС(КумЧистПотокДенСредств;;A6))

Рис. 6.3. Результирующий вид рабочего листа Эффект

В данной работе для простоты предполагается, что все доходы и расходы распределены равномерно в течение года (на практике это зачастую не так). В этом случае рекомендуется считать, что все элементы потока денежных средств относятся к середине соответствующего года. Поэтому, когда кумулятивный поток изменил знак с «-» (-959921) на «+» (+719562), мы считаем, что срок окупаемости лежит между серединой второго года и серединой третьего года. Последняя формула выведена с учетом этого предположения.

Чтобы ввести формулу в диапазон B10:F10, выделите этот диапазон и введите формулу =ЧистПотокДенСредств/(1+СтавкаДисконта)^(Год-0,5), а затем одновременно нажмите клавиши Ctrl и Enter.

Нажав одновременно клавиши Ctrl и Enter, мы определяем, что каждое значение в диапазоне ЧистПотокДенСредств будет разделено на соответствующее ему значение массива (1+СтавкаДисконта)^(Год-0,5). Здесь используется показатель степени Год-0,5, так как мы условились относить все доходы и расходы к середине года.

В остальные ячейки диапазона А11:F13 необходимые формулы введите самостоятельно.

Чтобы ввести формулу в диапазон B15:F15, выделите этот диапазон и введите формулу

=ЧистПотокДенСредств/((1+Инфляция)*(1+СтавкаДисконта))^(Год-0,5)

Затем нажмите клавиши Ctrl и Enter. В диапазоне А16:F18 остальные формулы введите самостоятельно.

Чтобы убедиться, что все сроки окупаемости рассчитаны правильно, построим графики всех трех кумулятивных потоков денежных средств. Выделите диапазоны А5:F5, А11:F11 и А16:F16. Нажмите кнопку Мастер диаграмм и выберите тип диаграммы График. Нажмите кнопку Готово.

Чтобы выделить несмежные диапазоны ячеек, нужно выделить первый диапазон ячеек обычным способом, а затем, нажав и удерживая клавишу Ctrl, выделить следующие диапазоны ячеек.

Для расчета чистого приведенного дохода NPV существует две финансовые функции - ЧПС() и ЧИСТНЗ() (см. Приложение). Функция ЧПС() используется, если платежи (поступления) происходят через равные промежутки времени. С учетом того, что все платежи (поступления) мы относили к середине года, в ячейку В20 введите формулу

=ЧПС(СтавкаДисконта;ЧистПотокДенСредств)*(1+СтавкаДисконта)^0,5

Обратите внимание, что полученное значение 2068348 совпадает со значением в ячейке F11.

Для расчета чистого приведенного дохода NPV с учетом инфляции нужной функции не существует, поэтому в ячейку В21 введите формулу =F16.

В ячейку В22 введите формулу

=-ЧПС(СтавкаДисконта;'Отчет о движ.'!C8:G8) *(1+СтавкаДисконта)^0,5

Эта формула предполагает, что все инвестиции осуществлялись в середине 2006 года. Однако данные инвестиционного плана (лист Инв. план) позволяют уточнить эту оценку. Перейдите на лист Инв. план и добавьте в таблицу этап №0 со стоимостью - р., а также добавьте столбец Момент инвестирования согласно рис. 6.4. Попытайтесь самостоятельно ввести формулы в диапазон Н3:Н7.

Теперь на рабочем листе Эффект в ячейку В23 введите формулу

=ЧИСТНЗ(СтавкаДисконта;'Инв. план'!F3:F7;'Инв. план'!H3:H7)

Функция ЧИСТНЗ() используется, если платежи (поступления) происходят через неравные промежутки времени (как в нашем случае). Очевидно, что уточненная оценка не существенно отличается от упрощенной оценки.

В ячейку В24 введите формулу =1+B20/B23, а в В25 формулу =1+B21/B23.

Рис. 6.4. Результирующий вид рабочего листа Инв. план

Для расчета внутренней нормы рентабельности (доходности) проекта IRR (Internal Rate of Return) служат финансовые функции ВСД() и ЧИСТВНДОХ(). Функция ВСД() используется, если платежи (поступления) происходят через равные промежутки времени. В ячейку В26 введите формулу

=ВСД(ЧистПотокДенСредств)

Функции для расчета IRR с учетом инфляции не существует. Поэтому в ячейку В27 необходимо ввести формулу

=(ВСД(ЧистПотокДенСредств)-Инфляция)/(1+Инфляция)

Рассчитанные показатели эффективности инвестиционного проекта свидетельствуют о его эффективности при заданной инвестором ставке дисконтирования e=15% , так как выполняются условия NPV>0, IRR>e и PI>1.

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

Для исследования чувствительности проекта к изменениям различных условий можно использовать такое средство MS Excel как сценарии. Будем исследовать влияние изменения цены, объема сбыта и уровня инфляции на эффективность проекта (в частности, на NPV, IRR и срок окупаемости с учетом инфляции).

К сожалению, на листе Сбыт не представлены значения прогноза инфляции, NPV, IRR и срока окупаемости, а все изменяемые ячейки сценария должны находиться на активном листе (т.е. на листе Сбыт). Чтобы значение прогноза инфляции присутствовало на листе, перейдите на лист Общие данные, вырежьте диапазон А11:С11 (кнопка Вырезать ) и вставьте в любом месте на листе Сбыт (теперь ячейка с именем Инфляция располагается на листе Сбыт). Чтобы не перемещать ячейки результатов NPV, IRR и срок окупаемости РР с листа Эффект на лист Сбыт, создадим копии этих ячеек на листе Сбыт. В ячейку В10 введите формулу =Эффект!B21, в В11 введите =Эффект!B27, в В12 введите =Эффект!B18. Ячейке В10 присвойте имя NPV, В11 - IRR, В12 - PP.

Перейдите на лист Сбыт и выполните команду СервисСценарии… В окне Диспетчер сценариев нажмите кнопку Добавить… В окне Добавление сценария задайте Название сценария Базовый сценарий, Изменяемые ячейки C3;B6:F6;Инфляция и нажмите кнопку ОК. В окне Значения ячеек сценария указаны текущие значения изменяемых ячеек. Нажмите кнопку ОК.

Добавьте сценарий Рост цен на 10% (Изменяемые ячейки С3, Цена =130*1,1 или 143). Самостоятельно создайте сценарий Падение цен на 10%.

Добавьте сценарий Рост продаж на 10% (Изменяемые ячейки B6:F6, $B$6 =50000*1,1 или 55000, $С$6 =300000*1,1 или 330000 и т.д.). Самостоятельно создайте сценарии Падение продаж на 10%, Инфляция 15% и Инфляция 20%.

Для создания отчета по сценариям нажмите кнопку Отчет… в окне Диспетчер сценариев. В окне Отчет по сценарию укажите Тип отчета структура, Ячейки результата =$B$10:$B$12. Нажмите кнопку ОК. Проанализируйте созданный рабочий лист Структура сценария. Убедитесь, что наибольшее влияние на эффективность проекта имеет цена.

Самостоятельно создайте отчет по сценариям с использованием сводной таблицы.

Чтобы получить удовлетворительный отчет с использованием сводной таблицы, необходимо предварительно удалить Базовый сценарий. После создания листа Сводная таблица по сценарию отредактируйте сводную таблицу. Для полей IRR и РР измените параметры поля - щелкните правой кнопкой мыши в нужном поле, выберите команду Параметры поля… и в окне Вычисление поля сводной таблицы задайте Операция Сумма, после чего нажмите кнопку ОК. Отформатируйте сводную таблицу так, чтобы она приняла следующий вид.

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

Чтобы определить минимальную цену, при которой проект будет оставаться рентабельным, перейдите на лист Сбыт и выполните команду СервисПодбор параметра… В окне Подбор параметра задайте значения согласно рис. 6.6. Нажмите кнопку ОК.



Рис. 6.5. Результирующий вид листа Сводная таблица по сценарию

Убедитесь, что при цене 127,53 руб. NPV=0 руб., IRR=15% (т.е. IRR равен ставке дисконта), а РР больше 5 лет.

Рис. 6.6. Окно Подбор параметра

Лабораторная работа №7 Финансовые функции Microsoft Excel

Цель. Изучить некоторые финансовые функции Microsoft Excel и научиться использовать их для расчета различных экономических показателей, связанных с амортизацией основных фондов, анализом аннуитетов и т.д.

Задание

1. Активизировать все финансовые функции Excel.

2. Рассчитать величины амортизационных отчислений и остаточной стоимости основных фондов (задачи 1-4).

3. Рассчитать параметры аннуитетов (задачи 5-8).

4. Рассчитать схемы погашения кредитов (задачи 9-12).

Основные сведения

В данной работе демонстрируются возможности встроенных функций MS Excel, используемых в финансовых вычислениях. Кроме того, в некоторых случаях полезным может оказаться такое средство MS Excel как Подбор параметра и/или Поиск решения.

Технология работы

Задача 1. Первоначальная стоимость объекта 60000 руб. Срок полезного использования - 2 года. Объект вводится в эксплуатацию 1 мая 2004 года. Рассчитать норму амортизации, суммы амортизационных отчислений линейным методом, накопленный износ и остаточную стоимость по месяцам.

Запустите на выполнение программу Microsoft Excel, создайте рабочую книгу с именем Финансовые функции.xls. Переименуйте лист Лист1 с помощью команды меню ФорматЛистПереименовать лист. Задайте новое имя Задача 1

Введите данные на лист Задача 1 согласно рис. 7.1 (в ячейки Е3:Е4, С7:Е18 и G7:I18 данные пока вводить не надо).

Чтобы ввести названия месяцев, в ячейку В7 введите Январь, а затем, нажав левую кнопку мыши, «протащите» курсор по ячейкам В7:В18.

Рис. 7.1

При форматировании ячеек В6 и F6 воспользуйтесь командой меню ФорматЯчейки…Граница. Выравнивание текста в этих ячейках можно произвести с помощью пробелов.

В ячейку Е3 самостоятельно введите формулу для расчета нормы амортизации за один месяц. Норма амортизации рассчитывается по формуле , где n - срок полезного использования в месяцах.

В ячейке Е4 для расчета величины амортизационных отчислений за месяц используйте функцию АПЛ() (см. Приложение). Задайте аргументы Стоимость $Е$1, Остаток 0, Период $Е$2.

В ячейку С12 введите формулу =$E$4, а в ячейку С13 введите формулу =C12+$E$4. Скопируйте формулу из ячейки С13 в ячейки С14:С18.

В ячейку D7 введите формулу =C18+$E$4, а в ячейку D8 введите =D7+$E$4. Скопируйте формулу из ячейки D8 в ячейки D9:D18.

В ячейки Е7:Е11 скопируйте формулы из ячеек D7:D11.

В ячейки С7:С11 и Е12:Е18 введите 0.

Выделите диапазон ячеек С7:Е18 и задайте денежный формат данных (кнопка Денежный формат ).

В ячейку G11 введите формулу =$E$1-C11, а затем скопируйте эту формулу в соответствующие ячейки.

Задача 2. Решить задачу 1 при условии, что используется нелинейный метод начисления амортизации.

Создайте копию листа Задача 1 и переименуйте его в лист Задача 2.

В ячейку Е3 введите формулу для расчета нормы амортизации. Норма амортизации при нелинейном методе рассчитывается по формуле .

Строку 4 можно удалить.

Для расчета сумм амортизации при нелинейном методе используйте функцию ПУО(). Функция ПУО возвращает величину амортизации за один или несколько периодов, используя метод двойного процента (или иного явно указанного процента) со снижающегося остатка.

Синтаксис функции

ПУО(Стоимость;Остаток;Период;Нач_период;Кон_период;Коэф;Без_перекл)

Аргументы Стоимость, Остаток и Период имеют тот же смысл, что и для функции АПЛ.

Нач_период - это начальный период, для которого вычисляется амортизация.

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

Коэф - это коэффициент, используемый при вычислении нормы амортизации. Если Коэф опущен, то он полагается равным 2.

Без_перекл - это логическое значение. Если аргумент Без_перекл имеет значение ЛОЖЬ или опущен, то Microsoft Excel переключается на линейный метод начисления амортизации, если амортизация, исчисленная по линейному методу, больше амортизации, исчисленной по нелинейному методу. Если аргумент Без_перекл имеет значение ИСТИНА, то Microsoft Excel никогда не переключается на линейный метод начисления амортизации.

Введите в ячейку С11 формулу =ПУО($E$1;0;$E$2;0;A11-5) и скопируйте ее в ячейки С12:С17.

Обратите внимание, что пятый аргумент A11-5 в формуле =ПУО($E$1;0;$E$2;0;A11-5) позволяет задать порядковый номер месяца, для которого рассчитывается накопленный износ.

В ячейку D6 введите формулу =ПУО($E$1;0;$E$2;0;A6+7) и скопируйте ее в ячейки D7:D17.

Самостоятельно задайте формулу для ячейки Е6 и скопируйте ее в ячейки Е7:Е10.

На рис. 7.2 представлена полученная таблица.

Рис. 7.2

В результате можно убедиться, что, начиная с июня 2005 года, амортизация начисляется по линейному методу и составляет 1760 руб. ежемесячно. Однако, согласно Налоговому Кодексу линейный метод применяется, если остаточная стоимость достигнет 20% от первоначальной стоимости основных фондов, т.е. в нашем случае линейный метод можно применять, только начиная с декабря 2006 года.

Создайте копию листа Задача 2. На новом листе необходимо запретить переключаться на линейный метод амортизации в период с июня 2005 по ноябрь 2005. Для этого необходимо исправить соответствующие формулы в ячейках D6:D17, добавив два аргумента: Коэф равный 2 и Без_перекл равный ИСТИНА.

Вместо значения ИСТИНА можно использовать значение 1.

В результате в ячейке Н17 должно получиться значение 11 486 р., представляющее собой остаточную стоимость на 1 декабря 2005 года. Начиная с этого момента нужно применять линейный метод.

В ячейку Е6 введите формулу =D17+$H$17/5, а ячейку Е7 формулу =E6+$H$17/5. Скопируйте последнюю формулу в ячейки Е8:Е10. Полученный результат представлен на рис. 7.3.

Рис. 7.3

Задача 3. Решить задачу 1 при условии, что используется метод учета целых периодов службы основных фондов.

По данному методу суммируется число периодов службы основных фонд. В нашем случае 1+2+…++24=24*(24+1)/2=300. Тогда в первом периоде амортизация равна 60000*24/300=4800 руб., во втором - 60000*23/300=4600 руб. и т.д. Для вычисления амортизации за один период служит функция АСЧ().

Синтаксис функции

АСЧ(Стоимость;Остаток;Период;Текущий_период).

Аргументы Стоимость, Остаток и Период имеют тот же смысл, что и для функций АПЛ и ПУО.

Текущий_период - это период, для которого рассчитывается амортизация.

Создайте лист Задача 3. В итоге он должен иметь вид, представленный на рис. 7.4.



Рис. 7.4

В ячейку С10 введите формулу =АСЧ($E$1;0;$E$2;A10-5), а в ячейку С11 введите формулу =C10+АСЧ($E$1;0;$E$2;A11-5). Скопируйте формулу из ячейки С11 в ячейки С12:С16.

В ячейку D5 введите формулу =C16+АСЧ($E$1;0;$E$2;A5+7), а в ячейку D6 введите формулу =D5+АСЧ($E$1;0;$E$2;A6+7). Скопируйте формулу из ячейки D6 в ячейки D7:D16.

В ячейки Е5:Е9 формулы введите самостоятельно.

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

Нажмите кнопку Мастер диаграмм и выберите тип диаграммы График. Нажмите кнопку Далее. В следующем окне щелкните по вкладке Ряд. Щелкните по кнопке Добавить и введите Имя Линейный метод. В поле Значения укажите диапазон данных

='Задача 1'!$G$11:$G$18;'Задача 1'!$H$7:$H$18;'Задача 1'!$I$7:$I$10;'Задача 1'!$I$11

Чтобы добавить на диаграмму следующий ряд, соответствующий нелинейному методу, используйте кнопку Добавить. Задайте Имя Нелинейный метод. В поле Значения укажите диапазон данных

='Задача 2'!$G$10:$G$17;'Задача 2'!$H$6:$H$17;'Задача 2'!$I$6:$I$10

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

Нажмите кнопку Далее. Задайте Название диаграммы Остаточная стоимость по периодам. Завершите создание диаграммы. В результате должна получиться диаграмма, представленная на рис. 7.5.

Рис.7.5

Задача 5. Рассчитать современную и будущую стоимости аннуитета за 10 лет, если величина каждого отдельного платежа 5000 руб., годовая процентная ставка 15%, платежи осуществляются в конце каждого года.

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

Различают будущую и современную стоимость аннуитета.

Будущая стоимость аннуитета

,

где n - общее число платежей (периодов); Pt - платеж, произведенный в начале или конце t-ого периода (зачастую рассматривают одинаковые размеры платежей, т.е. Рt=Р); ic - доходность платежей (ставка дисконта); t - коэффициент наращивания.

Современная стоимость аннуитета , где t - коэффициент дисконтирования.

Если все платежи производятся в начале периода, то такой аннуитет называют пренумерандо. Если все платежи производятся в конце периода, то такой аннуитет называют постнумерандо. В данной задаче имеет место аннуитет постнумерандо.

Способ 1. Введите данные согласно рис. 7.6.

Рис.7.6

Чтобы ввести значения от 0 до 10 в ячейки В2:L2, введите 0 в ячейку В2, подведите курсор к черному квадратику в правом нижнем углу ячейки, чтобы курсор превратился в черный крестик. Нажмите и удерживайте клавишу Ctrl и, нажав левую кнопку мыши, «протащите» курсор по ячейкам C2:L2.

В ячейку B4 введите формулу =1/(1+$B$1)^B2, в ячейку L5 введите формулу =(1+$B$1)^(10-L2). Размножьте формулы по строке.

В ячейку В7 введите формулу =СУММПРОИЗВ(B3:L3;B4:L4). В ячейку В8 введите формулу =СУММПРОИЗВ(B3:L3;B5:L5).

Недостаток данного способа - необходимо вводить все платежи.

Достоинство - можно задавать различные величины платежей и ставки дисконта для каждого платежа..

Способ 2. Воспользуемся формулами для стоимости аннуитета.

Современная стоимость аннуитета постнумерандо , где Р - размер платежа. В ячейку С7 введите формулу =C3*(1-1/(1+B1)^10)/B1.

Будущая стоимость аннуитета постнумерандо . Самостоятельно введите соответствующую формулу в ячейку С8.

Способ 3. Воспользуемся встроенными функциями Excel ПС() и БС().

В ячейку D7 вставьте финансовую функцию ПС(). В открывшемся диалоговом окне задайте аргументы: Норма В1 Кпер 10 Выплата С3 Остальные аргументы можно не задавать. Нажмите клавишу ОК. В результате в ячейке D7 окажется формула =ПС(B1;10;C3)

Синтаксис функции ПС(Норма;Кпер;Выплата;Бз;Тип)

Норма - это процентная ставка дисконта (норма прибыли) за период. В случае, если, например, задана годовая ставка дисконта 18% и в течение года производятся ежемесячные платежи, то в качестве значения аргумента Норма нужно ввести 18%/12 или 1,5% или 0,015.

Кпер - это общее число периодов выплат аннуитета. В случае, если, например, аннуитет выплачивается в течение 4 лет, платежи делаются ежемесячно, то в качестве значения аргумента Кпер нужно ввести 4*12 или 48.

Выплата - это выплата, производимая в каждый период и не меняющаяся за все время аннуитета.

Бз - величина дополнительного платежа, производимого в последнем периоде. Если аргумент опущен, то он полагается равным 0.

Тип - это число 0 или 1. Если аргумент Тип равен 0 или опущен, то платежи осуществляются постнумерандо (в конце периода). Если аргумент Тип равен 1, то платежи осуществляются постнумерандо (в начале периода).

В финансовых функциях выплачиваемые деньги, такие как взносы в банк на накопление, представляются отрицательным числом, а полученные деньги, такие как дивиденды, представляются положительным числом. Например, взнос в банк на сумму 1 000 руб. представляется аргументом _1 000 руб. для вкладчика и представляется аргументом +1 000 руб. для банка.

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

Задача 6. Инвестор предполагает накопить в течение 2 лет на счете в банке 150 тыс. руб. Платежи осуществляются в начале каждого месяца при годовой процентной ставке 10%. Рассчитать величину каждого платежа, если первоначальный взнос 30 тыс. руб.

Способ 1. Финансовые функции БС(), ПС(), КПЕР(), СТАВКА(), ПЛТ() взаимосвязаны. Excel выражает каждый финансовый аргумент через другие, используя формулу

...