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

 

Федеральное агентство по образованию

Тульский государственный университет

КАФЕДРА АВТОМАТИЗИРОВАННЫХ ИНФОРМАЦИОННЫХ И УПРАВЛЯЮЩИХ СИСТЕМ

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

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

К курсовой работе

по дисциплине

ИНФОРМАТИКА

Тула, 2010

Аннотация

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

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

Содержание

Введение

I. Задача №1

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

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

.1 Условие задачи

.2 Математическое описание

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

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

.2 Описание средств электронной таблицы MS Excel для проведения экономических расчетов

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

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

.5 Выводы

II. Задача №2

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

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

.1 Условие задачи

.2 Математическое описание

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

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

.2 Описание средств электронной таблицы MS Excel для проведения экономических расчетов

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

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

.5 Выводы

III. Задача №3

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

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

.1 Условие задачи

.2 Математическое описание

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

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

.2 Описание средств электронной таблицы MS Excel для проведения экономических расчетов

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

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

.5 Выводы

Заключение

Список литературы

Введение

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

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

В соответствии с целью работы были поставлены следующие задачи:

1.Изучить стандартные функции приложения МS Ехсе1;

2.Проанализировать предметные области финансовых задач;

3.Указать решаемые в курсовой работе задачи;

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

5.Проверить решения задач аналитическим способом;

6.Оценить сходимость результатов решений

7.Сформировать выводы.

I. Задача №1

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

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

Предполагается, что в течение определенного срока на счет откладывается непостоянная сумма денег под 13% годовых.

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

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

.1 Условие задачи

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

.2 Математическое описание

Математическая модель решения задачи представляется следующей зависимостью:

, (1)

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

ПС - начальное значение вклада;

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

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

Тип - коэффициент, определяющий время выплаты;пер - количество периодов вложений.

Данная зависимость используется в решении два раза, поскольку величина вклада нефиксированная и каждому из них соответствует своё количество вкладов. Следует обратить внимание на то, что в первом случае ПС равно 0, поскольку изначально на счете денежных средств не было, а во втором - аргумент ПС равен БС, вычисленном в первом случае.

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

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

Необходимо определить будущую стоимость вложений к концу заданного срока. Для этого можно воспользоваться финансовой функцией для анализа инвестиций, встроенной в приложение MS Excel: БС (ставка; кпер; плт; пс; тип) или решить задачу, используя аналитические зависимости.

Ниже представлено описание средств электронной таблицы MS Excel для проведения требуемых экономических расчетов.

.2 Описание средств электронной таблицы MS Excel для проведения экономических расчетов

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

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

Кпер - общее число периодов платежей по аннуитету (финансовая рента определяется как поток платежей, все члены которого положительны и поступают через одинаковые интервалы времени);

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

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

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

Для решения данной задачи аргумент Ставка для обоих случаев использования формулы (1) составляет 0,13, т.к. ставка фиксированная и проценты начисляются ежегодно.

Аргумент Кпер для первого случая равен 2 года, а для второго 3 по условию задачи.

Аргумент Плт равен -700 000 в первом случае и -850 000 во втором случае. Знак «минус» означает, что сумма вкладывается, т. е. вкладчик отдает свои деньги.

Аргумент Пс равен 0 для первого случая, так как первоначально на счете денег не было. А во втором случае Пс равен сумме вкладов за первые два года с учетом процентов, т.е. БС, полученному в первом случае.

Аргумент Тип равен 0 и в первом и во втором случае, так как в задаче указано то, что платежи производятся в конце периода.

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

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

. Создать таблицу в программе МS Ехсе1, где следует указать исходные данные задачи.

Необходимая для решения задачи таблица представлена на рисунке 1.

Рисунок 1 - Таблица с исходными данными для решения задачи 1

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

Подстановка данных для решения задачи представлена на рисунке 2.

Рисунок 2 - Подстановка значений для случая 1 задачи 1

. Для закрытия диалогового окна и получения результата следует нажать на кнопку ОК.

4. Те же операции необходимо проделать и для второго случая, учитывая особенности аргумента ПС (рисунок 3).

Рисунок 3 - Подстановка значений для случая 2 задачи 1

Процентная ставка по условию составляет 13%, количество периодов в соответствии со вторым случаем равно 3, периодические платежи составляют 850000 руб. Приведенная к настоящему моменту стоимость вложений равна 1 491 000 руб.

В условии задачи указано то, что проценты начисляются в конце каждого года, поэтому значение аргумента тип равно 0.

Результаты решения задачи в двух действиях представлены на рисунке 4.

Рисунок 4 - Результаты решения задачи 1

На рисунке 5 показана итоговая формула получения результата в соответствии с действием 1, на рисунке 6 - в соответствии с действием 2 условия поставленной задачи.

Рисунок 5 - Итоговая формула получения результата в соответствии с действием 1

Рисунок 6 - Итоговая формула получения результата в соответствии с действием 2

Также в условии указано задание: округлить результат до копеек. Его можно выполнить, указав в денежном формате ячейки два десятичных знака. (рисунок 7)

Рисунок 7 - Форматирование ячеек

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

Проверим решение, полученное при использовании технологии экономических расчетов средствами электронных табличных процессоров, в частности МS Excel, аналитическим способом. Для проверки правильности решения задачи в соответствии с действием 1 воспользуемся формулой (1), записав её в ячейку (рисунок 8)

Рисунок 8 - Проверка решения действия 1 аналитическим способом

Аналогичные операции производим со вторым действием (рисунок 9)

Рисунок 9 - Проверка решения действия 2 аналитическим способом

Полученные ответы изображены на рисунке 10 и полностью соответствуют ответам, полученным при решении задачи с использованием средств МS Excel, следовательно, решение является верным.

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

.5 Выводы

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

II. Задача №2

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

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

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

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

.1 Условие задачи

Ссуда размером 52 500 руб. погашается равномерными периодическими платежами по 755 руб. каждый месяц в течении семи лет. Определите годовую ставку процента. Определите платежи по процентам по займу за пятый год? Ответ округлите до копеек.

2.2 Математическое описание

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

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

. Для нахождения платежей по процентам по займу за определенный год можно использовать следующую формулу:

ПРПЛТ = ПЛТ - (ПС*((1+Ставка)период-(1+Ставка)период-1)-ПЛТ*((1+Ставка)период- -(1+ставк)период-1)/Ставка) (2)

Где ПРПЛТ - платеж по процентам по займу;

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

ПС - размер ссуды;

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

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

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

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

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

Для этого можно воспользоваться следующими финансовыми функциями приложения MS Excel:

для определения годовой процентной ставки СТАВКА (кпер;плт;пс;бс;тип;предположение)

для определения платежей по процентам по займу за пятый год: ПРПЛТ (Ставка; Период; Кпер; Пс; Бс);

Указанные вычисления можно получить и при помощи аналитических зависимостей, т.е. по формулам (1) и (2).

Ниже представлено описание средств электронной таблицы MS Excel для проведения требуемых экономических расчетов.

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

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

.Функция СТАВКА(кпер;плт;пс;бс;тип;предположение).

Данная функция возвращает процентную ставку по аннуитету за один период, вычисляется путем итерации.

Аргументами функции являются:

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

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

Пс - приведенная нынешняя стоимость, или общая сумма, равноценная на данный момент серии будущих выплат;

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

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

Предположение -прогнозная величина процентной ставки.

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

Аргумент Плт составит -9060 - ежегодный платеж. Знак «-» означает, что платежи для заемщика являются оттоком средств.

Аргумент Пс составит размер ссуды, т. е. 52 500.

Аргументы Б с, Тип и Предположение при решении данной задачи не указываются.

2. Функция ПРПЛТ (Ставка; Период; Кпер; Пс; Бс).

Данная функция определяет выплаты по процентам в определенный период.

Аргументами функции являются:

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

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

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

Пс - приведенная (нынешняя) стоимость или общая сумма, равноценная на данный момент серии будущих выплат;

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

Аргумент Ставка при решении второй части задачи принимается равным найденному значению.

Аргумент Период следует принять равным 5, т. к. определяется сумма выплат по процентам за пятый год.

Аргумент Кпер принимается равным 7 по условию.

Аргумент Пс принимается равным размеру ссуды, т. е. 52 500.

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

Для решения первой части поставленной задачи с помощью функции КПЕР необходимо:

. Ввести исходные данные в электронную таблицу и рассчитать ежегодный платеж (рисунок 11).

Рисунок 11 - Ввод исходных данных в электронную таблицу

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

Рисунок 12 - Процесс нахождения годовой процентной ставки

Для решения второй части поставленной задачи с помощью функции ПРПЛТ необходимо:

1. Ввести исходные данные задачи в электронную таблицу. Выбрать функцию ПРПЛТ и подставить в качестве аргументов исходные значения. (рисунок13)

Рисунок 13 - Подстановка значений

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

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

При использовании функции ПРПЛТ компьютер выдает отрицательное значение. Это объясняется тем, что платежи для заемщика являются оттоком денежных средств. В целях комфортного восприятия информации мы в строке задания функции перед функцией ПРПЛТ поставили знак «-», чтобы получить положительное значение.

Округление рассчитанных значений осуществляется также путем установления форматов ячеек.

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

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

Для определения годовой процентной ставки воспользуемся зависимостью (1), подставив в него исходные и полученные данные (рисунок 15).

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

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

Полученный ответ полностью соответствует ответу при решении задачи с использованием средств МS Excel, следовательно, решение является верным.

Для определения платежей по процентам по займу за пятый год воспользуемся формулой (2) (рисунок 16)

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

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

На рисунке 17 изображен результат проверок аналитическим способом.

Рисунок 17 - Результат проверок аналитическим способом

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

Таким образом, все части задачи, реализованные с помощью приложения MS Excel, решены верно.

3.5 Выводы

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

III. Задача №3

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

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

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

.1 Условие задачи

Имеется следующий список

КредиторСумма кредита (руб.)Годовая ставкаСрок (лет)1120 0001812350 0002853640 0002334150 0002025780 0001546271 0002327450 0002668631 0002559550 00022310500 000224

Найти кредиторов, у которых годовая ставка меньше 20%, а срок больше трех лет.

Задачу решить тремя способами:

·С помощью автофильтра;

·С помощью расширенного фильтра;

С помощью функции БИЗВЛЕЧЬ

2.2 Математическое описание

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

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

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

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

В соответствии с условием задачи решение должно содержать 3 способа: с помощью автофильтра, расширенного фильтра и функции БИЗВЛЕЧЬ.

Указанные команды представляют собой универсальный метод отбора данных, удовлетворяющих каким-либо условиям.

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

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

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

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

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

. Записать условие задачи в лист Excel (рисунок 18)

Рисунок 18 - Условие поставленной задачи, записанное в листе Excel

. Выбрать пункт меню Данные - Фильтр - Автофильтр. В результате в заголовке списка появятся флажки с выпадающими меню автофильтра Необходимо выбрать вариант отбора «условие» (рисунок 19).

Рисунок 19 - Применение команды Автофильтр

3. Нам необходимо задать два критерия, указанные в условии задачи, относительно годовой ставки и срока. Установка критерия для годовой ставки изображена на рисунке 20.

Рисунок 20 - Установка критерия для годовой ставки

После применения критерия «годовая ставка меньше 20» список выглядит следующим образом (рисунок 21):

Рисунок 21- Список после применения первого условия

Синим цветом на рисунке 21 обозначены номера строк, удовлетворяющих заданному условию.

. Далее необходимо задать второе условие для срока (рисунок 22)

Рисунок 22 - Задание критерия для поля «Срок»

После выполнения второго условия «срок погашения кредита больше 3» список, являющийся ответом, выглядит так (рисунок 23):

Рисунок 23 - Результат решения задачи первым способом

Таким образом, заданным условиям удовлетворяет единственный кредитор под номером 5.

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

. Записать условие задачи в лист Excel (рисунок 18)

. Далее под соответствующими полями записываются критерии (рисунок 24)

Рисунок 24 - Установка критериев

. Далее нужно применить команду Данные - Фильтр - Расширенный фильтр, после чего всплывает диалоговое окно, в котором следует указать исходный диапазон, диапазон условий и диапазон, куда будет записан результат, так, как показано на рисунке 25

экономический формула табличный excel

Рисунок 25 - Установка диапазонов

. После нажатия кнопки ОК в указанном диапазоне для результата появляется ответ (рисунок 26)

Рисунок 26 - Ответ задачи, полученный с помощью расширенного фильтра

Для решения задачи третьим способом, с помощью функции БИЗВЛЕЧЬ, необходимо выполнить следующие действия:

. Записать условие задачи в лист Excel.

. Далее под соответствующими полями указать критерии (рисунок 24)

. Выбрать пустую ячейку и вызвать функцию БИЗВЛЕЧЬ.

. В появившемся диалоговом окне указать все обязательные данные (рисунок27)

Рисунок 27 - Ввод значений аргументов

. После нажатия кнопки ОК в ячейке, в которую была вставлена функция, отобразился номер кредитора 5, что и является ответом (рисунок 28)

Рисунок 28 - Ответ, полученный с помощью функции БИЗВЛЕЧЬ

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

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

. Каждое значение поля «Годовая ставка» сравнить со значением 20

18<20

28>20

23>20

20=20

15<20

23>20

26>20

25>20

22>20

22 >20

. Из этих значений выбрать те, которые меньше 20. Это номера 1 и 5

. Сравнить срок выплаты первого и пятого кредиторов с 3

1<3

4>3

. Первый кредитор не удовлетворяет второму условию в отличие от пятого. Кредитор под номерам 5 удовлетворяет поставленным условиям. Ответ, полученный аналитическим методом, совпал с ответами, полученными с помощью автофильтра, расширенного и фильтра и функции БИЗВЛЕЧЬ, а значит, результат получен верно.

3.5 Выводы

Команды Автофильтр, Расширенный фильтр и БИЗВЛЕЧЬ позволяют быстро отсортировать данные и выбрать те, которые удовлетворяют определенному условию. Значительно облегчают работу при работе с большими базами данных.

Заключение

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

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

Список литературы

1. Андрианова М.А., Скорикова О.В. Методические указания к КР по Информатике.
2. Гобарева Я.Л. Технология экономических расчетов средствами MS EXCEL: учебное пособие / Я.Л. Гобарева, О.Ю. Городецкая, А.В. Золотарюк. - М.: КНОРУС, 2006. - 344 с.

Федеральное агентство по образованию Тульский государственный университет КАФЕДРА АВТОМАТИЗИРОВАННЫХ ИНФОРМАЦИОННЫХ И УПРАВЛЯЮЩИХ СИСТЕМ Решение финанс

Больше работ по теме: