Практическое занятие №16-17.

Практическое занятие №16-17.

Финансово-экономический анализ деятельности предприятия.

Цель занятия: – Отработать финансовые функции БЗ, КПЕР, ППЛАТ, ПЛПРОЦ. Создавать электронные таблицы для определения соответствующих экономических показателей с помощью заданных функций.

План занятия:

1. Специфика задания аргументов финансовых функций.

2. Функция ППЛАТ.

3. Функция НПЗ.

 

1. Специфика задания аргументов финансовых функций.

Для решения задач на определение будущего значения вклада в табличном процессоре существует функция БЗ из раздела "финансовые".

БЗ- Возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.

Синтаксис функции БЗ:

БЗ(ставка; кпер; плата; нз; тип), где

Ставка - это процентная ставка за период.

Кпер - это общее число периодов выплат годовой ренты.

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

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

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

Решение задач на определение количества периодов выплат.

Для решения задач на определение количества периодов выплат в табличном процессоре существует функция КПЕР из раздела "финансовые".

КПЕР - Возвращает общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки.

Синтаксис:

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

Ставка - это процентная ставка за период.

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

Нз - это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента.

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

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

 

Функция ППЛАТ.

Для решения задач на определение ежемесячных выплат в табличном процессоре существует функция ППЛАТ из раздела "финансовые".

ППЛАТ - Вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки.

Синтаксис:

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

Ставка - это процентная ставка по ссуде.

Кпер - это общее число выплат по ссуде.

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

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

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

Таблица данных для одной переменной

Предположим, что вы рассматриваете возможность покупки дома, для чего вам придется взять ссуду под закладную в $200 000 на 30 лет, и вы хотите вычислить месячные выплаты по этой ссуде для нескольких процентных ставок. Эту информацию может предоставить таблица данных для одной переменной.

Чтобы создать такую таблицу, выполните следующие действия:

• В новом рабочем листе введите в ячейку В2 процентную ставку – 6%, в ячейку В3 срок ссуды – 30 и в ячейку В4 размер ссуды – 200000.
• В ячейки В7:В12 введите интересующие вас процентные ставки 6%, 6,5%, 7%, 7,5%, 8% и 8,5%. Этот диапазон называется входным диапазоном, так как он содержит входные значения, которые необходимо проверить.
• Затем введите формулу, в которой используется входная переменная. В данном случае введите в ячейку С6 формулу

=ППЛАТ(В2/12;В3*12;В4)

где В2/12 — месячная процентная ставка, В3*12 — срок ссуды в месяцах и В4 — размер ссуды. Результат показан на рисунке

• Выделите диапазон таблицы данных — минимальный прямоугольный блок ячеек, включающий в себя формулу и все значения входного диапазона. В данном случае выделите диапазон В6:С12.
• 
  В меню Данные выберите команду Таблица подстановки. В окне диалога Таблица подстановки, показанном на рисунке,

 

задайте местонахождение входной ячейки в поле Подставлять значения по столбцам в или в поле Подставлять значения по строкам в. Входная ячейка — это ячейка-метка, на которую ссылается формула таблицы данных, в данном случае В2. Чтобы таблица данных заполнялась правильно, вы должны ввести ссылку на входную ячейку в нужное поле. Если входные значения расположены в строке, введите ссылку на входную ячейку в поле Подставлять значения по столбцам в. Если значения во входном диапазоне расположены в столбце, используйте поле Подставлять значения по строкам в. В данном примере входные значения расположены в столбце, поэтому введите $В$2 в поле Подставлять значения по строкам в или щелкните на этом поле и затем — на ячейке А2.

• Нажмите кнопку ОК. Excel поместит результаты вычисления формулы для каждого входного значения в свободных ячейках диапазона таблицы данных. В нашем примере Excel поместит шесть результатов в диапазоне С7:С12, как показано на рисунке

При создании этой таблицы данных Excel ввел формулу массива

{=ТАБЛИЦА(;В2)}

в каждую ячейку диапазона С7:С12, который мы будем называть диапазоном результатов. В нашей таблице эта формула вычисляет значения функции ППЛАТ для каждой процентной ставки из столбца В. Например, формула в ячейке С9 вычисляет размер выплаты при ставке, равной 7 процентам. Функция ТАБЛИЦА, используемая в формуле, имеет следующий синтаксис:

=ТАБЛИЦА(входная ячейка для строки; входная ячейка для столбца)

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

После построения таблицы можно изменить формулу таблицы данных или любые значения во входном диапазоне для создания другого множества результатов. Например, предположим, что для покупки дома вы решили занять только $185 000. Если вы измените данные в ячейке В4 на 185000 значения в выходном диапазоне изменятся, как показано на рисунке.

Таблица данных для двух переменных

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

Предположим, что вы хотите создать таблицу данных, которая вычисляет месячные выплаты по ссуде под закладную в $200 000, но на этот раз нужно изменять не только процентную ставку, а также и срок ссуды. Требуется узнать месячные выплаты для процентной ставки 6, 6,5, 7, 7,5, 8 и 8,5% при сроках 15, 20, 25 и 30 лет.

Чтобы создать такую таблицу, выполните следующие действия:

• В новом рабочем листе введите в ячейку В2 процентную ставку – 6%, в ячейку В3 срок ссуды – 30 и в ячейку В4 размер ссуды – 200000.
• Введите первое множество входных значений в столбец. Как и раньше, введите шесть процентных ставок в ячейки В7:В12.
• Введите второе множество входных значений в строке, начинающейся выше и правее на одну ячейку от начала первого диапазона. В данном случае введите в ячейки С6:F6 сроки ссуды: 15, 20, 25 и 30.
• Теперь можно создать формулу для таблицы. Поскольку это таблица с двумя переменными, формула должна быть введена в ячейку на пересечении строки и столбца, содержащих два множества входных значений, то есть в ячейку В6. Хотя в таблицу данных для одной переменной можно включить любое число формул, в таблице с двумя переменными допускается использование только одной формулы. Формула для таблицы в нашем примере будет иметь следующий вид:

=ППЛАТ(В2/12;В3*12;В4)

В ячейку С5 введите надпись Кол-во месяцев :, а в ячейку А7 — Ставка :.

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

 

• Выделите диапазон таблицы данных — минимальный прямоугольный блок, включающий в себя все входные значения и формулу. В данном случае выделите диапазон В6:F12.
• Выберите в меню Данные команду Таблица подстановки и задайте входные ячейки. Поскольку это таблица с двумя переменными, вы должны задать две входные ячейки: одну для первого множества входных значений, другую — для второго. В этом примере введите ссылку для первой входной ячейки $В$3 в поле Подставлять значения по столбцам в и затем введите ссылку для второй входной ячейки $В$2 в поле Подставлять значения по строкам в.
• 
  Нажмите Enter или кнопку ОК для вычисления таблицы. Результат представлен на рисунке.

 

Как и в предыдущих примерах, Ехсеl вводит формулы массива ТАБЛИЦА в диапазоне результатов С7:F12. Поскольку эта таблица имеет две переменных, формула ТАБЛИЦА содержит две ссылки:

{=ТАБЛИЦА(В3;В2)}

Значения в диапазоне результатов — это месячные выплаты по ссуде для каждой комбинации процентных ставок и сроков. Например, значение в ячейке D10 (-1611,19) — это выплата, необходимая для погашения ссуды в $200 000 за 20 лет при процентной ставке 7,5%.

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

Самостоятельная работа

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

Задача. В конце года капиталовложения по проекту составят около 150 тыс.гр. Ожидается, что за последующие 4 года проект принесет доходы: 62,69,75,79 тыс.гр. Рассчитайте чистую текущую стоимость проекта для различных норм дисконтирования (от 4% до 16% с шагом 4%) и объемов капиталовложений (от 200 тыс.гр. до 400 тыс гр. с шагом 50 тыс.гр.).

Контрольные вопросы:

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

2. Использование таблиц данных для двух переменных.

Вывод: данная практическая работа позволяет использовать Таблицы подстановок при решении задач обработки экономической информации.

Практическое занятие №20.

Имя

Отчество

Должность

Оклад

Коэффициент надбавки к основному окладу

· Табель учета рабочего времени

Второй лист содержит информацию о рабочем времени сотрудников. Порядковый номер

Фамилия

Имя

Отчество

Количество отработанных дней

Количество больничных дней

Количество дней отпуска

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

· Справочная информация

Третий лист содержит справочную информацию:

Таблица шкалы подоходного налога

Вспомогательные таблицы

· Издержки из зарплаты

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

Аванс

Магазин (товары, купленные в магазине в счет зарплаты)

· Расчет заработной платы

Пятый лист – расчет заработной платы.

Итоговая отчетность:

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

Контрольные вопросы:

1. Как осуществляется связь между листами книги?

2. Как вычисляется подоходный налог?

3. Синтаксис функции ПРОСМОТР.

4. Что такое смешанная адресация?

5. Как осуществляется построение графиков и диаграмм?

 

Практическое занятие №25.

Практическое занятие №16-17.