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

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

Содержание

Введение

Задание 1

.1 Анализ предметной области

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

.3 Описание технологии и алгоритма решения задачи

Задание 2

.1 Анализ предметной области

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

.3 Описание технологии и алгоритма решения задачи

Задание 3

.1 Анализ предметной области

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

.3 Описание технологий и алгоритма решения задачи

Заключение

Список используемой литературы

Введение

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

Цель работы:

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

Задачи работы:

-  проанализировать общее описание предметной области (ПО);

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

-       выделить другие параметры ПО, необходимые для выполнения индивидуального задания;

-       на основе проведенного анализа осуществить постановку задачи;

-       разработать алгоритм решения поставленной задачи;

-       осуществить машинную реализацию решения задачи.

Предмет исследований:

электронные таблицы MS Excel.

Объект исследований:

индивидуальные финансовые задачи.

Актуальность работы:

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

Задание 1

.1 Анализ предметной области

Предмет области - задача определения будущей стоимости;

Функция предметной области - расчет будущей стоимости вклада.

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

Предположим, есть два варианта инвестирования средств в течение шести лет: в начале каждого месяца под 10% годовых или в конце каждого месяца под 12% годовых. Пусть общая сумма взносов за год составляет 12 000 руб. Определите, сколько денег окажется на счете в конце шестого года для каждого варианта. Ответ округлите до копеек. Какой вариант предпочтительнее?

.3 Описание технологии и алгоритма решения задачи

Обобщенный алгоритм решения задачи:

1.       изучение теоретических положений;

2.      определение типа задачи;

3.  определение метода ее решения;

4.      описание математического аппарата, используемого в задаче;

.        решение задачи с помощью электронных таблиц MC Excel;

.        решение задачи аналитическим способом;

.        сравнение полученных результатов;

8.       вывод.

9.      Описание средств электронных таблиц для проведения экономических расчетов:

1.      Формулы - финансовые формулы - БС

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

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

КПЕР - Общее число периодов платежей по аннуитету;

ПЛТ - Фиксированная выплата, производимая в каждый период;

ТИП - коэффициент, определяющий время выплаты: 0 - в конце периода (по умолчанию), 1 - в начале периода;

ПС - Приведенная к настоящему моменту стоимость инвестиции, начальное значение вклада

Входные параметры для начисления процентов в начале периода:

Ставка - 10%/12=0,00833%

КПЕР - 12*6=72

ПЛТ - 0

ТИП - 1

ПС - -12 000 р.

Выходные параметры для начисления процентов в начале периода:

БС - 21 811,13 р.

Входные параметры для начисления процентов в конце периода:

Ставка - 12%/12=0,01%

КПЕР - 12*6=72

ПЛТ - 0

ПС --12 000 р.

Выходные параметры для начисления процентов в конце периода:

БС - 24 565,19 р.

Реализация технологии решения задачи с использованием электронного табличного процессора:

1.      запустить программу MC Excel;

.        определение структуры задачи - заполнение надлежащих таблиц (см. рисунок 1.1).

Рисунок 1.1. Исходные данные

3.      Формулы - финансовые формулы - БС. Заполнение аргументов функции (см. рисунок 1.2 и рисунок 1.3):

Рисунок 1.2. Заполнение аргументов функции БС для случая начисления процентов в начале периода

Рисунок 1.3. Заполнение аргументов функции БС для случая начисления процентов в конце периода

4.      Получение результатов вычисления функции БС (см. рисунок 1.4):

Рисунок 1.4. Результаты вычисления функции БС для обоих вариантов

Проверка решения аналитическим способом:

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

где БС - будущая стоимость потока фиксированных периодических платежей;

Плт - фиксированная периодическая сумма платежа;

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

Ставка - постоянная процентная ставка;

ТИП - коэффициент, определяющий время выплаты: 0 - в конце периода (по умолчанию), 1 - в начале периода

Так как в нашем случае ПЛТ=0, то второй множитель обращается в 0. В итоге мы имеем следующую формулу для БС:

Подставим значения для начисления процентов в начале периода:

В ячейке R13C3 содержится следующая формула:

БС =-R[-9]C*(1+R[-5]C)^R[-3]C

Подставим значения для начисления процентов в конце периода:

В ячейке R13C7 содержится следующая формула:

БС =-R[-9]C*(1+R[-5]C)^R[-3]C

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

Рисунок 1.5. Результаты аналитического решения функции БС для обоих вариантов

Выводы:

В ходе решения задачи мы получили следующие значения для будущей стоимости вклада:

При начислении процентов в начале периода: 21 811,13 р.

При начислении процентов в конце периода: 24 565,19 р.

.Очевидно, что выгоднее вложить деньги под 12% годовых с начислением процентов в конце каждого месяца.

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

Задание 2

.1 Анализ предметной области

Предмет области - задача определения будущей стоимости вклада;

Функция предметной области - определение процентной ставки

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

Сумма кредита размером 200 000 руб. за семь лет возросла до 960 000 руб. Проценты начисляются ежеквартально. Определите годовую ставку процента

2.3 Описание технологии и алгоритма решения задачи

Обобщенный алгоритм решения задачи:

1.      изучение теоретических положений;

.        определение типа задачи;

.        определение метода ее решения;

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

.        решение задачи с помощью электронных таблиц MC Excel;

.        решение задачи аналитическим способом;

.        сравнение полученных результатов;

.        вывод.

Описание средств электронных таблиц для проведения экономических расчетов:

1.       Формулы - финансовые формулы - СТАВКА

СТАВКА - Возвращает процентную ставку по аннуитету за один период, вычисляется путем итерации. Данная функция основывается на следующих аргументах:

БС - Будущая стоимость вклада;

КПЕР - Общее число периодов платежей по аннуитету;

ПЛТ - Фиксированная выплата, производимая в каждый период;

ТИП - коэффициент, определяющий время выплаты: 0 - в конце периода (по умолчанию), 1 - в начале периода;

ПС - Приведенная к настоящему моменту стоимость инвестиции, начальное значение вклада

Входные параметры при сроке вклада 5 лет:

БС - 960 000 р.

КПЕР - 4*7=28

ТИП - 0

ПС - -200 000 р.

Выходные параметры при сроке вклада 5 лет:

СТАВКА - 5,76%

Реализация технологии решения задачи с использованием электронного табличного процессора:

1.      запустить программу MC Excel;

.        определение структуры задачи - заполнение надлежащей таблицы (см. рисунок 2.1).

Рисунок 2.1. Исходные данные таблицы

3.     
Формулы - финансовые формулы - СТАВКА. Заполнение аргументов функции (см. рисунок 2.2):

Рисунок 2.2. Заполнение аргументов функции СТАВКА

.        Получение результатов вычисления функции СТАВКА (см. рисунок 2.3):

Рисунок 2.3. Результат вычисления функции СТАВКА

.        Найдем годовую процентную ставку по кредиту: для этого результат работы функции СТАВКА умножим на 4 (количество начислений процентов за год).

Итог вычислений представлен на рисунке 2.4:

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

Проверка решения аналитическим способом:

Рассчитаем годовую процентную ставку для срока вклада 7 лет по следующей формуле:

где- наращенная сумма,- периодический платеж,- срок вклада, лет,- число начислений процентов в год,- ставка процента, годовая

Подставим наши значения для срока вклада 5 лет

В ячейку R13C3 запишем следующую формулу

Ставка =((R[-6]C/-R[-10]C)^0,03571-1)*R[-8]C

Результаты аналитического вычисления представлены на рисунке 2.5.

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

программа excel финансовый табличный

Выводы:

В ходе решения задачи мы получили следующие значения:

·        Годовая процентная ставка по кредиту при сроке 7 лет, начальной суммой кредита 200 000р и будущей стоимостью 960 000р. должна составить 23,05%

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

Задание 3

.1 Анализ предметной области

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

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

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

Имеется таблица кредитных вложений коммерческого банка в зависимости от их обеспечения (см. таблица 1)

Таблица 1

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

3.3 Описание технологии и алгоритма решения задачи

Обобщенный алгоритм решения задачи:

1.      изучение теоретических положений;

.        определение типа задачи;

.        определение метода ее решения;

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

.        решение задачи с помощью электронных таблиц MC Excel;

.        решение задачи аналитическим способом;

.        сравнение полученных результатов;

.        вывод.

Описание средств электронных таблиц для проведения экономических расчетов:

1.     Данные - фильтр - Дополнительно - расширенный фильтр

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

.       Формулы - категория (статистические) - СРЗНАЧ

СРЗНАЧ - Возвращает среднее (арифметическое) своих аргументов.

Входные параметры - ячейки R4C3:R11C3

Выходные параметры:

Средний объем кредитный вложений: 4625,00 млн. руб.

3.       Формулы - категория (математические) - СУММ

СУММ - суммирует выделенный диапазон ячеек.

Входные данные: ячейки R4C3:R11C3

Выходные данные: 37 000 млн. руб.

Реализация технологии решения задачи с использованием электронного табличного процессора:

1.      запустить программу MC Excel;

.        определение структуры задачи - заполнение надлежащей таблицы (см. рисунок 3.1).

Рисунок 3.1. Исходные данные таблицы

.        Найдем общую сумму по полю Сумма (см. рисунок 3.2).

Для этого нажимаем: "Формулы" - "Автосумма"

Рисунок 3.2 Общая сумма по полю "Сумма"

.        Найдем удельный вес каждой ссуды (см. рисунок 3.3 и рисунок 3.4).

Для этого введем в ячейку R4C5 следующую формулу:

=RC[-1]/R[9]C[-1] и растянем ее вниз за уголок. Первый аргумент является относительной ссылкой (меняется при изменении строки), второй - абсолютной (не изменяет своего значения).

Установим формат всего этого столбца процентный.

Рисунок 3.3 Расчет удельного веса каждой ссуды.

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

.        Найдем средний объем привлеченных средств по всем кредитным вложениям (см. рисунок 3.5).

Для этого: Формулы - категория (статистические) - СРЗНАЧ

Запишем в ячейку R3C8 формулу: =СРЗНАЧ(R[2]C[-5]:R[9]C[-5])

Рисунок 3.5 Результат вычисления среднего объема всех кредитных вложений

.        Выберем из списка только те сроки, сумма по кредитным вложениям которых меньше среднего объема кредитных вложений (44625 млн. руб.) (см. рисунок 3.6 и рисунок 3.7).

Для этого воспользуемся расширенным фильтром.

Для этого: "Данные" - "Сортировка и фильтр" - "Дополнительно" - "Расширенный фильтр"

Запишем условие: кредитные вложения<44625 млн. руб.

Рисунок 3.6. Установка условий фильтрации расширенного фильтра.

Рисунок 3.7. Итог работы расширенного фильтра. Определение кредитных вложений, которые меньше 4625 млн. руб.

         Для нахождения количества видов ссуд под залог расширенным фильтром (см. рисунок 3.8 и рисунок 3.9).

Для этого: "Данные" - "Сортировка и фильтр" - "Дополнительно" - "Расширенный фильтр"

Запишем условие: кредитные вложения - ссуды под залог.

Рисунок 3.8. Установка условий фильтрации расширенного фильтра.

Рисунок 3.9. Итог работы расширенного фильтра. Определение количества типов ссуд под залог

. После работы расширенного фильтра по условию "ссуд под залог" количество видов ссуд под залог равно 5.

В ходе решения задачи мы получили следующие значения:

·        Удельный вес по каждому привлеченному кредиту (см.рисунок 3.3).

·        Среднее значение по всем кредитным вложениям (см. рисунок 3.5.)

·        Выборка кредитных вложений меньше среднего значения по всем кредитным вложениям (см. рисунок 3.7)

·        Выборка кредитных вложений под залог (см. рисунок 3.9)

Решение данной задачи очень удобно производить в электронных таблицах MC Excel. Расширенный фильтр позволяет итоги фильтрации записать вне списка фильтрации, Функция СРЗНАЧ позволяет быстро рассчитать среднее значение диапазона ячеек.

Заключение

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

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

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

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

В ходе работы с предметом исследований, мною были сделаны следующие выводы:

·        табличный процессор Excel достаточно прост в освоении и понимании;

·        Функционален и обладает мощными вычислительными возможностями;

·        Необходим для работы со списками и базами данных.

Список используемой литературы

1.      Лавренов С.М. Excel: Сборник примеров и задач. - М.: Финансы и статистика, 2010. - 440 с.

2.      Овчаренко Е.К., Ильина О.П. Балыбердин Е.В. Финансово-экономические расчеты в Excel. 2-е изд. - М.: Информационно-издательский дом "Филин". 1998. - 320 с.

.        Попов А.А. Excel: практическое руководство. - М.:ДЕСС КОМ, 2000. - 340 с.

.        Симонович С.В. Информатика для юристов и экономистов. - СПб: Питер, 2012. - 512 с.

.        Четыркин Е.М. Методы финансовых и коммерческих расчетов. 2-е изд. - М.: Дело Лтп, 1995. - 426 с.