- •Раздел 1. Работа со списками (таблицами) как с базами данных 2
- •Раздел 2. Финансовые вычисления с использованием Excel 35
- •Раздел 1. Работа со списками (таблицами) как с базами данных Задание 1. Обеспечение контроля за вводимыми данными
- •Методические указания для выполнения задания 1
- •Задание 2. Обеспечение контроля правильности введенных ранее значений
- •Методические указания для выполнения задания 2
- •1. Удаление условий.
- •2. Задание условия.
- •3. Обнаружение значений, не отвечающих поставленным условиям.
- •Задание 3. Обеспечение контроля правильности введенных ранее значений с помощью условного форматирования
- •Методические указания для выполнения задания 3
- •Задание 4. Подведение итогов по каждой группе записей
- •Методические указания для выполнения задания 4
- •Задание 5. Технология выбора данных с помощью "Расширенного списка"
- •Методические указания для выполнения задания 5
- •Задание 6. Организация поиска информации с помощью Расширенного фильтра
- •Методические указания для выполнения задания 6
- •Функция суммесли.
- •Функция датазнач.
- •Функция левсимв.
- •Задание 7. Обработка данных с помощью Сводных таблиц
- •Методические указания для выполнения задания 7
- •1. Создание сводной таблицы. Общие положения.
- •2. Модификация сводной таблицы
- •3. Дополнительные вычисления в сводной таблице
- •4. Изменение структуры сводной таблицы.
- •5. Сводные таблицы и диаграммы. Построение диаграмм.
- •Задание 8. Консолидация данных
- •2. Использование сводных таблиц для консолидации.
- •Задание 9. Контрольное задание Задача 1.
- •Задача 2.
- •Методические указания для выполнения контрольного задания
- •Раздел 2. Финансовые вычисления с использованием Excel Задание 1. Решение задачи с помощью функции бз
- •Методические указания для решения задания 1
- •Задание 2. Решение задачи с помощью функции пз
- •Методические указания для решения задания 2
- •Задание 3. Решение задачи с помощью функции норма
- •Методические указания для решения задания 3
- •Задание 4. Решение задачи с помощью функции кпер
- •Методические указания для решения задания 4
- •Задание 5. Решение задачи с помощью функции пплат
- •Методические указания для решения задания 5
- •Задание 6. Решение задачи с помощью функций пплат, плпроц и оснплат
- •Методические указания для решения задания 6
- •Задание 8. Контрольное задание
Задание 6. Решение задачи с помощью функций пплат, плпроц и оснплат
1. В табличном процессоре MS Excel сформировать расчетную таблицу для следующего условия:
Банк выдал кредит в сумме 40 000 ден.ед. на 5 лет под 6% годовых. Погашение кредита должно производиться равными ежегодными выплатами в конце каждого года, включающими погашение основного долга и процентные платежи. Начисление процентов производится раз в году. Составьте план погашения займа.
2. С помощью финансовых функций ППЛАТ, ПЛПРОЦ и ОСПЛАТ решить задачу.
3. Сохраните выполненное задание.
4. Представьте результат решения на проверку преподавателю.
Методические указания для решения задания 6
1. Откройте табличный процессор MS Excel. Используя известные Вам процедуры оформите таблицу
и занесите в таблицу исходные данные
Произведем расчеты.
1. Величина периодического платежа
Выделим ячейку С10 и на панели инструментов нажмите кнопку «Вставка функции». В окне «Мастер функций выберите категорию «Финансовые» и в окне «Функция» - ППЛАТ.
Выполните команду ОК.
В открывшемся окне ППЛАТ необходимо заполнить все ячейки в соответствии методикой задания 5.
Выполните команду ОК.
2. Баланс на начало периода
Произведите дополнительное построение таблицы.
В первом периоде баланс на начало периода равен сумме кредита (В13 = С3).
Для остальных периодов баланс на начало периода равен балансу на конец предшествующего периода, т.е. В14 = Е13 и скопировать формулу из ячейки В14 на остальной диапазон.
3. Баланс на конец периода
Баланс на конец периода вычисляется как сумма баланса на начало текущего периода и величины периодического платежа, минус размер части выплаты, идущей на оплату процентов. Формула имеет вид:
Е13 = В13 + $С$10 – D13.
Скопируем формулу на весь диапазон
4. Выплата по основному долгу
Выделим ячейку С13 и на панели инструментов нажмите кнопку «Вставка функции». В окне «Мастер функций» выберите категорию «Финансовые» и в окне «Функция» - ОСНПЛАТ.
Выполним команду ОК.
В открывшемся окне ОСНПЛАТ необходимо заполнить все ячейки по образцу.
Выполним команду ОК. Аналогично рассчитываются значения диапазона С14:С17, меняя в окне ОСНПЛАТ номер периода.
5. Выплата по процентам
Выделим ячейку D13 и на панели инструментов нажмем кнопку «Вставка функции». В окне «Мастер функций» выберем категорию «Финансовые» и в окне «Функция» - ПЛПРОЦ.
Выполним команду ОК.
В открывшемся окне ПЛПРОЦ необходимо заполнить все ячейки по образцу.
Выполним команду ОК.
Скопируем расчетную формулу на диапазон D14 : D17.
План составлен. В результате реализации данного плана клиент в конце 5-го периода рассчитается с банком.
Задание 7. Решение задачи с помощью известных функций
1. В табличном процессоре MS Excel сформировать расчетную таблицу для следующего условия:
1. Автоматизировать расчет по срочному вкладу, ставка 18,5% годовых, срок – 1 год и 1 месяц, ежеквартальная капитализация процентов.
2. Ссуда в размере 1000000 рублей выдана 20 января текущего года на срок до 5 октября текущего года включительно под 18% годовых. Какую сумму должен заплатить должник в конце срока?
3. Автоматизируйте расчет налога на материальную выгоду по беспроцентной ссуде. Налог взимается ежемесячно в размере 35% от суммы материальной выгоды, которая составляет ¾ ставки рефинансирования ЦБ РФ (13% годовых).
2. С помощью изученных Вами финансовых функций решить задачи.
3. Сохраните выполненное задание.
4. Представьте результат решения на проверку преподавателю.