Лабораторная работа №2






НазваниеЛабораторная работа №2
страница1/6
Дата публикации04.12.2014
Размер0.8 Mb.
ТипЛабораторная работа
top-bal.ru > Информатика > Лабораторная работа
  1   2   3   4   5   6
Лабораторная работа № 2 

Работа со списками. Создание автоматических промежуточных отчетов и сводных таблиц 

Цель работы: Приобрести навыки использования возможностей MS Excel для анализа данных в списках.

Задание к лабораторной работе: 

1. Изучите материалы лекции 5 и решите приведенные примеры.

2. Выполните контрольное задание на новом листе или в отдельной книге MS Excel.

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

Создайте список из 25 записей, содержащий сведения о вкладах, например:

ФИО

Отделение банка

Тип вклада

Дата открытия счета

Сумма вклада

1) На примере созданного списка вычислите простые и сложные промежуточные итоги с использованием разных функций. Группы для подведения итогов создайте по произвольно выбранному полю (полям);

2) Создайте сводную таблицу по данным списка. Добавьте вычисляемое поле и постройте сводную диаграмму.

3. Добавьте в архив файлы с решениями и результатами.

ЛЕКЦИЯ 5

Прочитав эту лекцию, Вы узнаете:

  • о способах подведения автоматических итогов по группам показателей;

  • о возможностях сводных таблиц и диаграмм для обобщения и анализа данных в списках;

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

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

^ Использование итогов для анализа данных

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

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

Алгоритм создания простых промежуточных отчетов

Подготовка данных для создания промежуточных отчетов:

1. Создать исходный список.

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

Создание промежуточного отчета:

1. Установить курсор в список и выполнить команду Промежуточный итог вкладки Данные.

2. В окне Промежуточные итоги выполнить следующие действия:

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

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

- в поле Добавить итоги по выбрать метки столбца (столбцов), содержащих значения, которые будут участвовать в расчетах. Для расчетов итогов по нескольким полям базы данных в раскрывающемся списке поля Добавить итоги по диалогового окна Промежуточные итоги следует выбрать метки нужных столбцов. Можно применять различные функции к одному столбцу списка.  

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

Удалить итоговые строки из списка можно нажатием кнопки Убрать все в диалоговом окне Промежуточные итоги.

Рассмотрим примеры создания простых промежуточных отчетов.

Задание. На основании данных списка вычислить фонд оплаты труда в каждом цехе.

Порядок действий:

1. Создайте на первом рабочем листе список, содержащий не менее 25 строк с информацией о сотрудниках предприятия (рисунок 1).

 

^ Список сотрудников

 

 

 

 

 

 

 

 

 

^ Номер п/п

ФИО

Пол

Стаж

Подразделение

Должность

Оклад

1

Новиков П.Р.

м

6

Технический отдел

ст.инженер

 25 000р.

2

Петров К.Н.

м

2

Отдел маркетинга

специалист

 18 000р.

3

Иванова В.П.

ж

18

Отдел маркетинга

экономист

 25 000р.

4

Соболева М.И.

ж

15

Диспетчерский

техник

 13 000р.

Рисунок 1– Фрагмент исходного списка 

2. Подготовьте данные для создания промежуточных итогов. Для этого установите курсор в список и отсортируйте список по полю «Подразделение» (меню Сортировка вкладки Данные) (рисунок 2). Это необходимо для создания групп данных. Порядок сортировки может быть любым.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l4_12.gif
Рисунок 2 – Пример диалогового окна Мастера сортировки

3. Установите курсор в список и выполните команду Промежуточные итоги вкладки Данные.

Заполните  диалоговое окно Промежуточные итоги в соответствии с рисунком 3.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l4_13.gif
Рисунок 3 – Пример диалогового окна Мастера промежуточных итогов

После нажатия кнопки ^ ОК диалогового окна на рабочем листе будет создана структура отчета: слева от названия столбцов – строка номеров уровней. Уровень детализации изменяется с помощью кнопок, расположенных левее номеров строк списка (номера 1,2,3 уровней структуры). Выбор 1-го уровня  позволяет увидеть на экране только строку общего итога, выбор 2-го уровня – строки промежуточных итогов и общего итога, 3-й уровень отображает все строки списка, включая  итоговые.

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

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

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l4_14.gif
Рисунок 4 – Промежуточные итоги для каждого подразделения

1. Оставьте на экране только итоговые значения и постройте круговую диаграмму по полученным результатам. Для этого

- выделите столбец «Подразделение» (вместе с заголовком). Нажав и удерживая клавишу , выделите диапазон итоговых значений (без общего итога);

- в группе элементов Диаграммы вкладки Вставка выберите тип диаграммы (рисунок 5).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l4_15.gif
Рисунок 5 – Диаграмма ФОТ (суммы окладов) для каждого подразделения 

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

На рисунках 6 и 7 приведен пример вычисления среднего стажа работников в каждом подразделении.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\4-16.jpg
Рисунок 6 –Диалоговое окно Мастера при добавлении новых промежуточных итогов 

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l4_17.gif
Рисунок 7 –Промежуточные итоги для двух функций 

^ Создание сложных промежуточных отчетов

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

Например, нужно определить ФОТ для каждого подразделения и средний стаж по каждой должности в каждом подразделении.

Порядок действий 

1. Список сортируют по нескольким ключам. В нашем примере в качестве первого (внешнего) ключа сортировки используем поле «Подразделение», а в качестве второго ключа – «Должность» (рисунок 8).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l4_18.gif
Рисунок 8 – Пример диалогового окна Мастера сортировки по двум ключам

2. Создают промежуточные итоги по внешнему ключу (рисунок 9).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l4_19.gif
Рисунок 9 – Создание промежуточных итогов для подразделений

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

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l4_20.gif
Рисунок 10 – Диалоговое окно создания промежуточных итогов для поля Должность

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l4_21.gif
Рисунок 11 – Фрагмент результирующего списка со сложными промежуточными итогами

 

^ Лабораторная работа № 4 

Использование инструментария MS Excel для анализа бизнес - ситуации 

Цель работы: Приобрести навыки использования технологий Подбора параметра и сценариев для решения финансовых задач.

Задание к лабораторной работе: 

1. Изучите материалы лекций 9 и 10 решите приведенные примеры.

2. Выполните контрольные задания на новом листе или в отдельной книге MS Excel.

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

Задание 1. Хозяйственный субъект сдает в аренду помещение сроком на 5 лет. Арендная плата составляет 10 000 $ в год. Процентная ставка составляет 15% годовых. Затраты на ремонт и оборудование помещения составляют 30000 $ за весь период.

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

Задание 2. Сколько денег будет на вашем расчетном счете через 3 года при постоянной 3-х процентной ставке и постоянных ежегодных банковских взносах. Начальная сумма вклада составляла 20 тыс. рублей. Ежемесячно на вклад поступает 2 тыс. рублей.

Как должны измениться ежемесячные выплаты, чтобы  на счете накопилось 150000 руб.?

Задание 3. Определите, какая первоначальная сумма обеспечит за 2 года величину вклада 60000 при постоянных ежемесячных дополнениях вложений в размере 1000 руб. и годовой процентной ставке 12%.

Задание 4. Рассчитайте график платежей по ссуде

^ Сумма кредита

Ставка

Срок

Общая сумма выплат

 

100 000,00    

12,50%

10

 

 

^ Ежегодная сумма выплат

Период (год)

Денежный поток (расходы) получателя

^ Основной платеж

Проценты

Остаток задолженности по ссуде

1

 

 

 

 

2

 

 

 

 

3

 

 

 

 

4

 

 

 

 

5

 

 

 

 

6

 

 

 

 

7

 

 

 

 

8

 

 

 

 

9

 

 

 

 

10

 

 

 

 

Итого:

 

 

 

 

^ Ежемесячная сумма выплат

Период (месяц)

Денежный поток (расходы) получателя

^ Основной платеж

Проценты

Остаток задолженности по ссуде

1

 

 

 

 

2

 

 

 

 



 

 

 

 

Задание 5. Рассчитайте ЧПС доходов оператора связи, если клиент, использующий безлимитный тариф, будет платить в течение пяти лет в середине каждого месяца по 1000 руб. Ставка дисконтирования составит 15%.

3. Добавьте в архив файлы с решениями и результатами.

 

ЛЕКЦИЯ 9

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

^ Инструменты финансового анализа

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

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

^ Использование финансовых функций в решении задач финансового анализа

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

К наиболее часто используемым функциям из категории финансовых относятся:

^ БС() – расчет будущей стоимости капитала при постоянной процентной ставке и равномерных периодических выплатах;

БЗРАСПИС() - возвращает будущее значение основного капитала после начисления сложных процентов. Функция используется для вычисления будущего значения инвестиции с переменной процентной ставкой;

ПС() – расчет приведенной к текущему моменту стоимости инвестиций при серии равномерных периодических платежей и постоянной процентной ставке;

КПЕР() – количество периодов, за которые исходная сумма достигнет требуемой величины при постоянной процентной ставке и равномерных периодических выплатах;

СТАВКА() – величина процентной ставки, при которой исходная сумма достигнет требуемой величины при равномерных периодических выплатах;

ПЛТ() – величина периодических выплат, которые позволят достичь заданной величины вклада за определенный период при начальной сумме вложений и постоянной процентной ставке. Периодические выплаты включают основной платеж и плату по процентам, возвращаемые функциями ОСПЛТ() и ПРПЛТ();

ПРОЦПЛАТ() - вычисляет проценты, начисленные за указанный инвестиционный период.

Финансовые функции Excel позволяют учитывать момент времени, когда производятся периодические платежи, посредством аргумента ^ Тип. По умолчанию Тип выплат равен нулю, что означает конец периода. Если выплаты производятся в начале учетного периода, то данный параметр задается равным 1.

Замечания.

1. В формулах входящие денежные потоки учитываются как положительные величины, а исходящие – как отрицательные. Таким образом, вкладываемые денежные суммы (например, депозитные вклады) обозначаются отрицательными числами, а полученные денежные суммы – положительными (например, дивиденды).

^ 2. Следует обращать внимание на выбор единиц измерения аргументов СТАВКА (НОРМА) [1] и КПЕР. Для аргументов ставка и кпер должны использоваться согласованные единицы измерения (один и тот же период, например месяц). Если по двухгодичному займу при ставке 18% годовых производятся ежемесячные выплаты, то в формуле нужно использовать значение 18%/12 для аргумента СТАВКА и значение 2*12 – для аргумента КПЕР.

Рассмотрим форматы и  примеры использования некоторых финансовых функций.

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

Формат функции

=БС(ставка; кпер; плт; пс; тип), где

ставка (норма) – это фиксированная процентная ставка за период;

кпер   – это общее число периодов выплат годовой ренты;

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

пс  – это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент пс пропущен, то он полагается равным 0;

тип – это число 0 или 1, определяющая, когда должна производиться выплата:

0 – в конце периода;

1 – в начале периода.

Если аргумент тип пропущен, то он полагается равным 0.

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

Пример. Вычислить сумму накоплений на депозитном вкладе в сумме 20000 руб., размещенном под 14% годовых сроком на 3 года с обязательством дополнительных вложений в сумме 1000 р. в конце каждого месяца.

Исходные данные представим в виде таблицы на рабочем листе. В ячейку B8 введем формулу =БС(B3/12;B4*12;-B5;-B6;B7). Во избежание ошибок воспользуемся Мастером функций (п. меню Вставка/ Функция). В окне Мастера функций из категории Финансовые выберем – функцию БС.

Заполним диалоговое окно функции ссылками на соответствующие ячейки (рисунок 1).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l9_1.gif
Рисунок 1 – Пример диалогового окна функции БС()

После нажатия кнопки ОК в ячейке B8 будет выведен результат вычислений (рисунок 2).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l9_2.gif
Рисунок 2 – Результат расчета суммы депозитного вклада 

^ Функция КПЕР() – возвращает общее количество периодов выплат для данного вклада, за которые исходная сумма достигнет требуемой величины, на основе постоянных периодических вложений и постоянной процентной ставки.  

Формат функции

=КПЕР(ставка;плт;пс;бс;тип), где

ставка – процентная ставка за период;

плт – выплата, производимая в каждый период. Платеж состоит из основного платежа и платежа по процентам, никакие другие сборы или налоги при этом не учитываются;

пс – текущая стоимость, или общая сумма всех будущих платежей с настоящего момента;

бс – будущая стоимость, или баланс наличности, который должен быть достигнут после последней выплаты. Если аргумент бс пропущен, то предполагается, что он равен 0 (например, будущая стоимость займа равна 0);

тип – это число 0 или 1, обозначающее, когда должна производиться выплата:

0 (по умолчанию) – в конце периода;

1 в начале периода.

Например, мы берем ссуду размером 100000 руб. при годовой ставке по кредиту 20% и собираемся выплачивать по 20000 руб. в конце каждого года. Срок ссуды при заданных условиях можно рассчитать по формуле:

=КПЕР(20%; -20000;100000).

^ Функция ПЛТ() – вычисляет величину выплат по ссуде на основе постоянных периодических платежей и постоянной процентной ставки.

Формат функции

=ПЛТ(ставка;кпер;пс;бс;тип), где

  • ставка   – процентная ставка по ссуде;

  • кпер   – общее число выплат по ссуде;

  • пс   – общая сумма, которую составят будущие платежи;

  • бс   – будущая сумма или баланс наличности, которой нужно достичь после последней выплаты. Если бс пропущено, оно полагается равным нулю, т.е. сумма долга после последней выплаты равна 0.

  • тип – число 0 (ноль) или 1, обозначающее, когда должна производиться выплата:

0  – (по умолчанию) – в конце периода;

1  –   в начале периода.

Замечание. Выплаты, возвращаемые функцией ПЛТ(), включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или гонораров, иногда связанных со ссудой.

Следующая формула возвращает сумму, которую необходимо выплачивать ежемесячно при размере займа 50000 руб. сроком шесть месяцев и годовой ставке 15%:

=ПЛТ(15%/12; 6; 50000)

Функцию ПЛТ() можно использовать для расчета платежей не только в случае ссуды. С помощью этой функции можно определить размер ежемесячно откладываемых сумм. Например, если требуется накопить 50 000 руб. за 5 лет при годовой ставке 8%,  формула будет выглядеть следующим образом:

=ПЛТ(8%/12; 5*12; 0; 50000)

^ Функция ОСПЛТ()– сумма, составляющая накопление (погашение долга) за учетный период на основе постоянных периодических выплат и постоянной процентной ставке.

Формат функции

=ОСПЛТ(ставка; период; кпер; пс; бс; тип), где

  • период - период, за который требуется найти прибыль;

  • ставка – процентная ставка за период;

  • кпер – общее число периодов выплат;

  • пс – текущее значение, т.е. общая сумма, которую составят будущие платежи;

  • бс – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты;

  • тип – число 0 или 1.

Функция ПРПЛТ()– проценты (доход или к уплате) за учетный период. При этом величина периодических платежей и процентной ставки остаются неизменными в течение всего срока.

Формат функции

=ПРПЛТ (ставка; период; кпер; пс; бс; тип) 

Аргументы те же, что и у функции ОСПЛТ().

Замечание. За один и тот же период верно равенство

ПЛТ=ОСПЛТ+ПРПЛТ

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

Функция ПС() –возвращает текущий объем вклада при постоянной процентной ставке и постоянных периодических выплатах.

Формат функции

=ПС (ставка; кпер; плт; бс; тип), где

  • ставка – постоянная процентная ставка за период;

  • кпер – общее число периодов выплат;

  • плт – величина постоянных периодических платежей;

  • бс  – будущая стоимость или баланс наличности, которые нужно достичь после последней выплаты;

  • тип   - это число 0 (ноль) или 1, обозначающее, когда должна производиться выплата:

0  – (по умолчанию) – в конце периода;

1  –   в начале периода.

^ Функция СТАВКА() – вычисляет процентную ставку за один период, необходимую для накопления определенной суммы в течение заданного срока путем постоянных периодических выплат.

Формат функции

=СТАВКА (кпер; плт; пс; бс; тип; нач_прибл), где

  • кпер – общее число периодов выплат;

  • плт – величина постоянных периодических платежей;

  • пс – текущее значение, т.е. общая сумма, которую составят будущие платежи;

  • бс – будущая стоимость или баланс наличности, которые нужно достичь после последней выплаты;

  • тип – 0 или 1;

  • нач_прибл – предполагаемая величина ставки. Если нач_прибл пропущено, то предполагается равным 10%. Если функция СТАВКА() не сходится, следует пытаться использовать различные начальные приближения.

Пример. Определим процентную ставку для 4-х летнего займа размером в 8000 руб. с ежемесячными выплатами – 200 руб. Используем в формуле аргументы, приведенные к одному периоду – месяц.

^ =СТАВКА(4*12; -200;8000)

В результате получим ставку за месяц 0,77% и ставку за год соответственно 0,77%*12.

Функция ПРОЦПЛАТ – возвращает проценты платежа по прямому займу за инвестиционный период.

Формат функции

=ПРОЦПЛАТ (ставка; период; кпер; пс), где

  • ставка – процентная ставка для вклада;

  • период – период, для которого требуется найти прибыль;

  • кпер – общее число периодов выплат для данного вклада;

  • пс – текущее значение вклада. В случае займа пс – это сумма кредита.

Пример. Рассчитать график дифференцированных платежей по ссуде 50000 руб., сроком на один год при годовой ставке 21%. Решение с расчетными формулами и результатами приведено на рисунках 3 и 4.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l9_3.gif
Рисунок 3– Вид рабочего листа с формулами

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l9_4.gif
Рисунок 4 – Пример расчета дифференцированных выплат

Следующий пример демонстрирует использования финансовой функции ЧПС() для оценки инвестиционных вложений.

Пример 2. Предприятие, предоставляющее телекоммуникационные услуги рассматривает два проекта развития, которые требуют инвестиционных вложений. Первый проект требует текущих вложений в размере 7 млн. руб. и 2 млн. руб. через два года. Через год ожидаемый доход от реализации первого проекта составит 10 млн. руб. Второй проект требует  4 млн. руб. инвестиций сейчас и 3,5 млн. руб. через два года. Ожидаемый доход в первый год составляет 8,5 млн. руб. Определим, какой из вариантов более выгоден предприятию при ставке дисконтирования 20%.

Рассмотрим варианты, когда денежные потоки  поступают в начале года, в конце и в середине года.

1. Диапазон A1:D4 заполним исходными данными (рисунок 5). Присвоим ячейке В1 имя Ставка.

2. В диапазоне B5:D6 вычислим текущую стоимость инвестиций в соответствии с формулой (текущие инвестиции на начало года)/(1+ставка дисконта)период.

В ячейки В5:В6 скопируем величину начальных инвестиций. В ячейки С5 и С6 – введем формулы для расчета текущей стоимости по окончании первого года: =C3/(1+Ставка)^$C$2 и =C4/(1+Ставка)^$C$2 соответственно.

В ячейки D5 и D6 – введем формулы для расчета текущей стоимости по окончании второго года: =D3/(1+Ставка)^$D$2 и =D4/(1+Ставка)^$D$2 соответственно.

3. В ячейках Е3и Е4 рассчитаем совокупный денежный поток за два года по формулам  =СУММ(B3:D3) и   =СУММ(B4:D4) соответственно. Результат расчета (рисунок 7) говорит об одинаковой эффективности  проектов. Однако сделать окончательный вывод можно только после учета чистой приведенной стоимости проектов.

4. Для определения ЧПС нужно сложить суммы потоков денежных средств в сегодняшних денежных единицах. Для расчета текущей стоимости денежных потоков, поступающих в начале года, отделим поток первого года и применим функцию ЧПС к остальным периодам.

В ячейки В9 и В10 введем формулы для расчета текущей стоимости проектов на начало года: =B5+ЧПС(Ставка;C3:D3) и =B6+ЧПС(Ставка;C4:D4) соответственно. Пример заполнения окна функции ЧПС приведен на рисунке 8.

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

5. Аналогично можно рассчитать текущую стоимость денежных потоков, поступающих в конце года. Введем в ячейки В12, В13 формулы для расчета текущей стоимости инвестиций на конец года: =ЧПС(Ставка;B3:D3) и =ЧПС(Ставка;B4:D4) соответственно. Превратим ЧПС денежных потоков, полученный в конце года, в ЧПС денежных потоков в начале года. В ячейках С9 и С10 приведен результат такого расчета. Как видите, оба варианта возвращают один и тот же результат.

Предположим, что денежные средства поступают в середине года. В ячейках В15, В16 рассчитаны текущие стоимости инвестиций в середине года по формулам: = КОРЕНЬ(1+Ставка)*B12 и   =КОРЕНЬ(1+Ставка)*B13.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l9_5.gif
Рисунок 5 – Вид рабочего листа с исходными данными и расчетными формулами

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l9_6.gif
Рисунок 6 – Окно функции ЧПС

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l9_7.gif
Рисунок 7 – Результаты оценки инвестиционных проектов с учетом критерия ЧПС

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

Рассмотрим пример поступления денег в произвольные моменты времени. Заполним исходными данными диапазон A4:B9. В ячейку А2 введем значение ставки дисконтирования (рисунок 8).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l9_8.gif
Рисунок 8 – Лист с исходными данными и результатами

В диапазоне С6:С9 рассчитаем длительности периодов (количество лет) поступления денежных потоков, которые будем использовать в формулах для расчета коэффициентов дисконтирования.

В диапазоне D5:D9 рассчитаем коэффициенты дисконтирования. Для начального периода коэффициент дисконтирования равен 1. Вид рабочего листа с формулами представлен на рисунке 9. В ячейке В12 введена формула для расчета ЧПС без использования встроенной функции ЧИСТНЗ(): =СУММПРОИЗВ(B5:B9;D5:D9).

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

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l9_9.gif
Рисунок 9 - Вид рабочего листа с расчетными формулами

Для расчёта ЧПС в сегодняшних денежных единицах нужно просто добавить строку с текущей датой и  нулевым денежным потоком. Попробуйте самостоятельно рассчитать ЧПС в текущих денежных единицах.

 

ЛЕКЦИЯ 10

  1   2   3   4   5   6

Добавить документ в свой блог или на сайт

Похожие:

Лабораторная работа №2 iconЛабораторная работа «Ремонт торцевых уплотнений» Лабораторная работа...
Машины и аппараты химических производств и предприятий строительных материалов, в 2004 г защитил кандидатскую диссертацию по специальности...

Лабораторная работа №2 iconЛабораторная работа 1
Цель работы: ознакомиться с топографической картой и научиться решать следующие задачи

Лабораторная работа №2 iconЛабораторная работа 8
Машковский М. Д. Лекарственные средства Медицина, 1984. ч. I, с. 325, 403, ч. II, с. 320, 322

Лабораторная работа №2 iconМежрегиональный центр переподготовки специалистов Лабораторная работа №1
В методических указаниях к лабораторным работам дополнительно рассматривается необходимый для их выполнения теоретический материал....

Лабораторная работа №2 iconЛабораторная работа №4 Тема 1
Написать и выполнить безымянный блок pl/sql, который выводит значение символьной переменной

Лабораторная работа №2 iconЛабораторная работа №1
...

Лабораторная работа №2 iconЛабораторная работа №6 Тема 1
Создать хранимую процедуру pl/sql, которая выводит наибольшее из двух чисел, заданных в качестве аргументов

Лабораторная работа №2 iconЛабораторная работа №8
Цель работы – определение радиуса кривизны плоско-выпуклой линзы с помощью интерференционной картины колец Ньютона

Лабораторная работа №2 iconРасписание лекций и практических работ по биологии 9 января, воскресенье
Лабораторная работа «Сравнительный анализ хромосом млекопитающих». Профессор О. В. Саблина

Лабораторная работа №2 iconЛабораторная работа №5 Тема 1
Составить и выполнить программу pl/sql, которая считывает из базы данных количество продавцов в Лондоне и выводит результат



Школьные материалы


При копировании материала укажите ссылку © 2018
контакты
top-bal.ru

Поиск