Решение задачи об учете ежемесячных платежей по кредиту в программной среде MS Excel 2010

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ

ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

ФИНАНСОВЫЙ УНИВЕРСИТЕТ

ПРИ ПРАВИТЕЛЬСТВЕ РОССИЙСКОЙ ФЕДЕРАЦИИ

БАРНАУЛЬСКИЙ ФИЛИАЛ

Кафедра «Математика и информатика»

Направление бакалавриата «Экономика»

КОНТРОЛЬНАЯ РАБОТА

Вариант №4

По дисциплине «Экономическая информатика»

Студент Симонова Евгения Андреевна

Группа 1Б-1 Номер личного дела 100.02/130184

Преподаватель Свердлов М.Ю

Барнаул 2014

СОДЕРЖАНИЕ

ВВЕДЕНИЕ

1. Постановка задачи

1.1 Условие задачи

1.2 Цель решения задачи

2. Математическая модель решения задачи

3. Технология решения задачи в MS Excel

ЗАКЛЮЧЕНИЕ

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ

ВВЕДЕНИЕ

В рамках написания контрольной работы предлагается решить задачу об учете ежемесячных платежей по кредиту. Клиент банка «Акцепт+» осуществляет ежемесячное погашение кредита равными платежами. Решение данной задачи является актуальным, так как кредитование на сегодняшний день является очень популярным как среди физических лиц, так и среди юридических. Как известно при погашении кредита необходимо строго учитывать все сроки во избежание штрафных санкций. Организацией было принято решение отслеживать ежемесячные выплаты по кредиту во избежание просрочек. Задача, которая будет решаться в программной среде MS Excel ежедневно, называется «Учет платежей по кредиту».

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

Данная задача будет решаться на ЭВМ в программной среде MS Excel 2010.

экономический задача кредит excel

1. ПОСТАНОВКА ЗАДАЧИ

1.1 Условие задачи

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

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

Структура результирующего документа «Платежи по кредиту клиента банка «Акцепт+»»

Платежи по кредиту клиента ________________________________ банка "Акцепт+" за 2012 г.
Годовая процентная ставка	12,00% (G)
Кредит выдан на	12 месяцев (M)
Сумма кредитов, руб.	250000 (K)
Номер платежа	Дата платежа	Текущий остаток по кредиту, руб.	Сумма процентов, руб.	Погашение основного долга, руб.	Платеж по кредиту, руб.
N	D	O	P	S	Q
1
…
12				S12
Итого:				So	Qo

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

В технологии необходимо использовать функции ЕСЛИ и ОКРУГЛ и др.

1.2 Цель решения задачи

Рассмотрим такую ситуацию. Клиент банка «Акцепт+» осуществляет ежемесячное погашение кредита равными платежами. Организацией было принято решение отслеживать ежемесячные выплаты по кредиту во избежание просрочек. Задача, которая будет решаться в программной среде MS Excel ежедневно, называется «Учет платежей по кредиту».

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

2. МАТЕМАТИЧЕСКАЯ МОДЕЛЬ РЕШЕНИЯ ЗАДАЧИ

Для получения ведомости о платежах по кредиту банка необходимо рассчитать следующие показатели:

ѕ Текущий остаток по кредиту, руб. (за первые 11 месяцйев);

ѕ Сумма процентов, руб.;

ѕ Погашение основного долга, руб.;

ѕ Платеж по кредиту, руб.;

ѕ Текущий остаток по кредиту за декабрь, руб.;

ѕ Общую сумму основного долга, руб.;

ѕ Общую сумму платежа по кредиту, руб.

Расчеты выполняются по следующим формулам:

O₁ = K;

O_n = O_n-1 -S_n; 2?n?11;

P = O*G/M;

S_n = K/M, 1?n?11;

O₁₂ =

S₁₂ = O_12;

Q_n = P_n + S_n ;

S_o = ?S_n , 1?n?12;

Q_o = ?Q_{n ,}1?n?12.

где O₁ - остаток по кредиту на 1 платеж, K - сумма кредита, O_n - остаток по кредину на n-ный платеж, n - номер платежа, S_n - сумма погашения основного долга на n-ный платеж, P - сумма процентов, G - годовая процентная ставка, M - количество месяцев, на которое выдан кредит, S₁₂ - погашение основного долга за последний месяц, S_o - общая сумма погашения основного долга, Q_о - общая сумма платежа по кредиту.

3. ТЕХНОЛОГИЯ РЕШЕНИЯ ЗАДАЧИ В MS EXCEL

Решение задачи средствами MS Excel

1. Вызовите Excel:

· нажмите кнопку «Пуск»;

· выберите в главном меню команду «Программы»;

· в меню Microsoft Office выберите MS Excel.

2. Переименуйте «Лист 1» в «Платежи по кредиту»:

· установите курсор мыши на ярлык «Лист 1» и нажмите правую кнопку мыши (ПКМ);

· В контекстном меню выберите команду «Переименовать» и нажмите левую кнопку мыши (ЛКМ);

· Наберите на клавиатуре «Платежи по кредиту» и подтвердите изменение нажатием клавиши «Enter».

3. В ячейку A1 введите заголовок таблицы «Платежи по кредиту клиента банка "Акцепт+"».

4. Введите в ячейки A2:F6 информацию, представленную на рисунке 1.

Рисунок 1 - Имена полей таблицы «Платежи по кредиту клиента банка "Акцепт+"»

5. В ячейки A7:F19 введите информацию, приведенную в таблице 1.

Таблица 1 - Платежи по кредиту клиента банка "Акцепт+"

Платежи по кредиту клиента банка "Акцепт+"
Платежи по кредиту клиента ________________________________ банка "Акцепт+" за 2012 г.
Годовая процентная ставка	12,00%
Кредит выдан на	12 месяцев
Сумма кредитов, руб.	250000
Номер платежа	Дата платежа	Текущий остаток по кредиту, руб.	Сумма процентов, руб.	Погашение основного долга, руб.	Платеж по кредиту, руб.
1	Январь
2	Февраль
3	Март
4	Апрель
5	Май
6	Июнь
7	Июль
8	Август
9	Сентябрь
10	Октябрь
11	Ноябрь
12	Декабрь
Итого:

6. Созданная таблица «Платежи по кредиту клиента банка "Акцепт+"» представлена на рисунке 2.

Рисунок 2 - Таблица «Платежи по кредиту клиента банка "Акцепт+"»

7. Вычислим остаток по кредиту для первого платежа (ячейка C7). Для этого в ячейку С7 введем формулу:

=C5,

где С5 - сумма по кредиту.

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

Рисунок 3 - Ввод формулы для вычисления остатка по кредиту для первого платежа

8. Заполним столбец «Погашение основного долга» таблицы.

Для этого:

· Сделайте ячейку E7 активной;

· Введем в ячейку «= »

· Воспользуйтесь командой «Формулы» - «Вставить функцию»;

· Выберите функцию «ОКРУГЛ» в разделе «Математические» (рисунок 4);

Рисунок 4 - Выбор функции «ОКРУГЛ»

· Нажмите кнопку «ОК»;

· Введите значение в поле «Число», щелкнув по ячейке С5, далее нажмите клавишу F4 для того, чтобы добавить абсолютную адресацию на ячейку, в которой расположена сумма кредита;

· Далее допишите «/12», означает, разделить на срок выдачи вредина в месяцах.

· В поле «Число разрядов» введите цифру 0, чтобы значения округлились до целого числа (рисунок 5);

· Нажмите кнопку «ОК» (результат на рисунке 6).

Рисунок 5 - Аргументы функции «ОКРУГЛ»

Рисунок 6 - Результат работы формулы для нахождения суммы погашения основного долга

С помощью маркера автозаполнения, заполним столбец полностью, для этого «растянем» формулу до ячейки E17 (рисунок 7).

Рисунок 7 - Заполненный столбец «Погашение основного долга»

9. Заполним столбец «Сумма процентов». Для этого в ячейку D7 введем формулу:

=C7*$C$3/12,

где C7 - ячейка, в которой находится сумма кредита, $C$3 - абсолютная ссылка на ячейку, в которой расположена годовая процентная ставка по кредиту (в процентном формате), 12 - количество месяцев, на которое выдан кредит.

· Результат работы формулы представлен на рисунке 8.

· Сделайте ячейку D7 активной, установите курсор на маркер автозаполнения в правом нижнем углу ячейки D7, щелкните ЛКМ и протяните его до ячейки D18;

· Результат заполнения столбца «Сумма процентов» представлен на рисунке 9.

Рисунок 8 - Результат работы формулы для нахождения суммы процентов по кредиту

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

На рисунке 9 видно, что сумма процентов для платежей с 2 по 12 равна нулю. Это происходит в связи с тем, что зависимые ячейки формулы, введенной в данном столбце, еще не заполнены (столбец «Текущий остаток по кредиту»). Как только в данном столбце появятся данные, сумма процентов по кредиту будет вычислена.

10. Вычислим общую текущий остаток по кредиту для платежей с 2 по 11. Для этого в ячейку С8 введем формулу:

=C7-E8,

где С7 - сумма предыдущего остатка по кредиту, E8 - текущая сумма погашения основного долга.

Далее, используя маркер автозаполнения, вычислим текущий остаток по кредиту для остальных платежей, не включая 12-й (размножим формулу в ячейке С8 до ячейки С17, рисунки 10,11).



Рисунок 10 - Вычисление текущего остатка по кредиту

Рисунок 11 - Вычисленные текущие остатки по кредиту для платежей с 2 по 11

На рисунке 11 видно, что с появлением значений текущих остатков по кредиту, были вычислены суммы процентов по кредиту.

11. Далее вычислим последний текущий остаток по кредиту. Для этого:

· Сделайте ячейку С18 активной;

· Введем в ячейку «= »

· Воспользуйтесь командой «Формулы» - «Вставить функцию»;

· Выберите функцию «ЕСЛИ» в разделе «Логические» (рисунок 12);

Рисунок 12 - Выбор функции «ЕСЛИ»

· Нажмите кнопку «ОК»;

· Введите выражение в поле «Лог_выражение», как показано на рисунке 13;

· В поле «Значение_если_истина» введите формулу C5-СУММ(E7:E17);

· В поле «Значение_если_ложь» введите 0 (рисунок 14)

· Нажмите кнопку «ОК» (результат на рисунке 15).

Рисунок 13 - Заполнение поля «Лог_выражение»

Рисунок 14 - Заполнение полей функции ЕСЛИ



Рисунок 15 - Результат применения функции ЕСЛИ

12. Заполним ячейку «Погашение основного долга» за декабрь. Для этого в ячейку E18 введите формулу:

=C18,

где C18 - последний остаток по кредиту.

Результат заполнения ячейки E18 представлен на рисунке 16.

13. Вычислим суммы платежей по кредиту. Для этого в ячейку F7 введем формулу:

=D7+E7,

где D7 - сумма процентов по текущему платежу, E7 - сумма погашения основного долга по текущему платежу.

Далее, используя маркер автозаполнения, вычислим платежи по кредиту для остальных месяцев (размножим формулу в ячейке F7 до ячейки А18, рисунки 17,18).



Рисунок 16 - Вычисление последней суммы погашения основного долга

Рисунок 17 - Вычисление суммы платежа по кредиту

Рисунок 18 - Заполненный столбец «Платеж по кредиту»

14. Для проверки правильности вычислений подсчитаем итоговую общую сумму погашения основного долга. Для этого в ячейку E19 введем формулу:

=СУММ(E7:E18).

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

Рисунок 19 - Проверка правильности вычислений

15. Размножим формулу в ячейке E19 до ячейки F19 для нахождения общей суммы платежей по кредиту (рисунок 20).

Рисунок 20 - Вычисление общей суммы платежа по кредиту

16. Представим наглядно результаты работы, создав диаграмму по данным таблицы. Для этого:

· Выделите несмежные диапазоны ячеек, содержащие информацию о номере платежа и информацию об сумме платежа по кредиту. При выделении несмежных диапазонов удерживаем клавишу Ctrl (рисунок 21);

· Выберите команду «Гистограмма» в разделе «Диаграммы» меню «Вставка»;

· Переименуйте получившуюся гистограмму в «Платежи по кредиту» (рисунок 22).



Рисунок 21 - Выделение необходимых данных для построения диаграммы

Рисунок 22 - Гистограмма «Платежи по кредиту»

ЗАКЛЮЧЕНИЕ

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

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ

1. Информатика: учебное пособие / под ред. Б.Е. Одинцова, А.Н. Романова. - М.: Вузовский учебник : ИНФРА+М, 2012.

2. Информационные ресурсы и технологии в экономике: учебное пособие / под ред. Б.Е. Одинцова, А.Н. Романова. - М.: Вузовский учебник, 2012.

3. Информатика: Практикум для экономистов: учебное пособие /под ред. В.П. Косарева. - М.: Финансы и статистика : ИНФРА+М, 2009.

4. Компьютерная обучающая программа по дисциплине «Информатика» / А.Н. Романов, В.С. Торопцов, Д.Б. Григорович, Л.А. Галкина, А.Ю. Артемьев, Н.И. Лобова, К.Е. Михайлов, Г.А. Жуков, О.Е. Кричевская, С.В. Ясеновский, Л.А. Вдовенко, Б.Е. Одинцов, Г.А. Титоренко, Г.Д. Савичев, В.И. Гусев, С.Е. Смирнов, В.И. Суворова, Г.В. Федорова, Г.Б. Коняшина. - М.: ВЗФЭИ, 2000. Дата обновления: 24.11.2010. - URL: http://repository.vzfei.ru. Доступ по логину и паролю.

Размещено на Allbest.ru

...