Решение финансовых задач в MS EXCEL

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

Государственное образовательное учреждение высшего профессионального образования

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

Кафедра автоматизированных информационных и управляющих систем

КУРСОВАЯ РАБОТА

по дисциплине «Информатика»

на тему:

РЕШЕНИЕ ФИНАНСОВЫХ ЗАДАЧ В
MS
EXCEL

Выполнила: ________________________________ Чебыкина Е.С.

гр. 720782

Руководитель: _____________________________ Яблочкин Л.Б.

Тула 2009

Содержание

Введение…………………………………………………………………………...4

Глава 1. Назначение и основные возможности MSExcel……………………...5

Глава 2. Решение задач…………………………………………………………...9

Задача №1…………………………………………………………………...9

Задача №2………………………………………………………………….13

Задача №3………………………………………………………………….17

Заключение……………………………………………………………………….22

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

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

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

Кафедра "Автоматизированные информационные и управляющие системы"

Задание на курсовую работу

Студент __________________________________ Группа ________________

Фамилия, инициалы

1. Тема ____________________________________________________________

__________________________________________________________________

2. Срок предоставления работы к защите " ___ " _________ 200__ г.

3. Исходные данные для проектирования

Входные документы ________________________________________________

________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

Выходные документы _______________________________________________

______________________________________________________________________________________________________________________________________________________________________________________________________

Решаемая задача ___________________________________________________

______________________________________________________________________________________________________________________________________________________________________________________________________

Содержание пояснительной записки

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

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

3. Разработка информационного обеспечения задачи.

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

5. Разработка диалогового приложения пользователя.

6. Список использованной литературы

5. Задание выдал __________ "___"______ 200__ г ______________________

ПодписьФамилия и инициалы

6. Задание получил_________ "___"______ 200__ г ______________________

ПодписьФамилия и инициалы

Введение

Табличный процессор MS Excel (электронные таблицы) – одно из наиболее часто используемых приложений интегрированного пакета MS Office, мощнейший инструмент в умелых руках, значительно упрощающий рутинную повседневную работу. Основное назначение MS Excel – решение практически любых задач расчетного характера, входные данные которых можно представить в виде таблиц. Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без программирования расчётов. В сочетании же с языком программирования Visual Basic for Application (VBA), табличный процессор MS Excel приобретает универсальный характер и позволяет решить вообще любую задачу, независимо от ее характера.

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

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

Глава 1.
Назначение и основные возможности
MS
Excel

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

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

Рабочим полем табличного процессора является экран дисплея, на котором электронная таблица представляется в виде прямоугольника, разделенного на строки и столбцы. Строки нумеруются сверху вниз. Столбцы обозначаются слева направо. На экране виден не весь документ, а только часть его. Документ в полном объеме хранится в оперативной памяти, а экран можно считать окном, через которое пользователь имеет возможность просматривать таблицу. Для работы с таблицей используется табличный курсор, — выделенный прямоугольник, который можно поместить в ту или иную клетку. Минимальным элементом электронной таблицы, над которым можно выполнять те или иные операции, является такая клетка, которую чаще называют ячейкой. Каждая ячейка имеет уникальное имя (идентификатор), которое составляется из номеров столбца и строки, на пересечении которых располагается ячейка. Нумерация столбцов обычно осуществляется с помощью латинских букв (поскольку их всего 26, а столбцов значительно больше, то далее идёт такая нумерация — AA, AB, ..., AZ, BA, BB, BC, ...), а строк — с помощью десятичных чисел, начиная с единицы. Таким образом, возможны имена (или адреса) ячеек B2, C265, AD11 и т.д.

Следующий объект в таблице — диапазон ячеек. Его можно выделить из подряд идущих ячеек в строке, столбце или прямоугольнике. При задании диапазона указывают его начальную и конечную ячейки, в прямоугольном диапазоне — ячейки левого верхнего и правого нижнего углов. Наибольший диапазон представляет вся таблица, наименьший — ячейка. Примеры диапазонов — A1:A100; B12:AZ12; B2:K40.

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

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

Ячейки в электронных таблицах могут содержать числа (целые и действительные), символьные и строковые величины, логические величины, формулы (алгебраические, логические, содержащие условие).

В формулах при обращении к ячейкам используется два способа адресации — абсолютная и относительная адресации. При использовании относительной адресации копирование, перемещение формулы, вставка или удаление строки (столбца) с изменением местоположения формулы приводят к перестраиванию формулы относительно её нового местоположения. В силу этого сохраняется правильность расчётов при любых указанных выше действиями над ячейками с формулами. В некоторых же случаях необходимо, чтобы при изменении местоположения формулы адрес ячейки (или ячеек), используемой в формуле, не изменялся. В таких случаях используется абсолютная адресация. В приведенных выше примерах адресов ячеек и диапазонов ячеек адресация является относительной. Примеры абсолютной адресации (в Microsoft Excel): $A$10; $B$5:$D$12; $M10; K$12 (в предпоследнем примере фиксирован только столбец, а строка может изменяться, в последнем — фиксирована строка, столбец может изменяться).

Управление работой электронной таблицы осуществляется посредством меню команд.

Можно выделить следующие режимы работы табличного процессора:

- формирование электронной таблицы;

- управление вычислениями;

- режим отображения формул;

- графический режим;

- работа электронной таблицы как базы данных.

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

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

Режим отображения формул задает индикацию содержимого клеток на экране. Обычно этот режим выключен, и на экране отображаются значения, вычисленные на основании содержимого клеток.

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

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

- столбцы списков становятся полями базы данных;

- заголовки столбцов становятся именами полей базы данных;

- каждая строка списка преобразуется в запись данных.

Глава 2. Решение задач

Задача №1.

Рассчитайте, какую сумму необходимо положить на депозит, чтобы через пять лет она выросла до 500 000 руб., если ставка процента – 15% годовых и проценты начисляются ежеквартально. Ответ округлите до копеек. А если первоначально положить 250 000 руб., то какую сумму следует ожидать через пять лет? Ответ округлите до копеек.

Алгоритм решения задачи

При решении задачи аналитическим способом используем формулу:

,где

ПС – текущая стоимость вклада

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

Кпер – общее число периодов начисления процентов

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

Данная формула не учитывает знак «минус» для денежных потоков от клиента. Подставив в формулу числовые данные, получим:

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

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

В связи с тем, что проценты начисляются каждый квартал, аргумент ставка
равен 15%/4. общее число периодов начисления равно 5*4 (аргумент кпер
). Аргумент плт
отсутствует, так как вклад не пополняется. Аргумент тип равен 0, так как в подобных операциях проценты начисляются в конце каждого периода (задается по умолчанию). Если решать данную задачу с точки зрения вкладчика, то аргумент пс
(начальная стоимость) збудет равен отрицательному числу, поскольку для вкладчика это отток его денежных средств (вложение средств). На рисунке 1 показан ввод заданных параметров.

Рисунок 1 - Фрагмент листа Excel с решением задачи об определении текущей стоимости

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

Рисунок 2 - Фрагмент листа Excel с аналитическим решением задачи об определении текущей стоимости

Далее решаем вторую часть задачи.

Аналитический способ решения:

Подставив в формулу числовые значения, получаем:

БС = 250000 – (1+ 0,0375)²⁰
= 522037,999 руб.

Поскольку необходимо рассчитать единую сумму вклада на основе постоянной процентной ставки, то используем БС(ставка;кпер;плт;пс;тип).

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

Рисунок 3 - Фрагмент листа Excel с решением задачи об определении будущей стоимости

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

Рисунок 4 - Фрагмент листа Excel с аналитическим решением задачи об определении будущей стоимости

Задача №2.

Определите, через сколько лет обычные ежеквартальные платежи размером 3 150 руб. принесут доход в 450 000 руб. при ставке 14% годовых. Рассчитайте сумму ежеквартальных платежей, исходя из десятилетнего срока. Ответ округлите до копеек.

Алгоритм решения задачи

Решим первую часть задачи.

Аналитический способ решения задачи.

У нас есть формула:

Поскольку в данной задаче ПС = 0, выразим из данной формулы КПЕР:

Найдем количество лет, через которые данные платежи принесут заданный доход. Для этого 52/4 = 13 лет.

Решим задачу в MS Excel.

Для нахождения количества лет, через которые платежи размером 3150 рублей принесут доход в 450000 рублей, для начала найдемобщее количество периодов выплаты на основе периодических постоянных выплат и постоянной процентной ставки: КПЕР
(ставка
;плт
;пс
;бс;тип), а затем общее число периодов выплат разделим на количество начислений процентов за год. Таким образом, мы ответим на вопрос задачи.

В данном случае ставка = 14%/4, тип = 0 (по умолчанию), пс отсутствует, плт по условию задачи = -3150 руб, т.к. данная сумма для вкладчика является оттоком средств.

На рисунке 5 мы видим нахождение общего количества периодов выплат с помощью MS Excel.

Рисунок 5. Фрагмент листа Excel с нахождение общего количества периодов выплат

На рисунке 6 изображено второе действие задачи (Мы поделили кпер на количество начислений процентов за год).

Рисунок 6. Фрагмент листа Excel с нахождение количества лет

Таким образом, при обычных ежеквартальных платежах размером 3 150 руб. и ставке 14% годовых потребуется 13 лет для получения дохода в 450000 рублей.

Теперь решим вторую часть задачи.

Решение аналитическим способом:

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

Для определения ежемесячных выплат применяется функция ПЛТ с аргументами: Ставка = 14%/4 (ставка процента за квартал); Кпер = 10*4 = 40 (общее число кварталов начисления процентов); Бс = 450000 (будущая стоимость вклада); Тип = 0,так как в подобных операциях проценты начисляются в конце каждого периода (задается по умолчанию). Иллюстрация решения данной задачи в Excel приведена на рисунке 5.

Рисунок 5 - Иллюстрация применения функции ПЛТ

Результат со знаком «минус», так как 5322,28 руб. клиент ежеквартально вносит в банк.

Задача № 3

Имеется следующая таблица.

Определить сумму налога на наследование при условии, что действует налоговая шкала, представленная в таблице.

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

Алгоритм решения

Решение аналитическим способом.

Для решения этой задачи нам потребуется рассмотреть каждого наследника.

Первый из них – Лушников является наследником первой очереди и унаследованная им сумма составляет 1560 руб. Исходя из второй таблицы, он облагается налогом, равным 5% от суммы наследования (1560*0,05). Сумма налога = 78 руб.

Федоров – наследник второй очереди и его сумма наследования составляет 3500 руб., следовательно, его сумма налога составляет 3500*0,3+255 = 1275 руб.

Семенов является наследником первой очереди, его сумма наследования равна 2200 руб., а сумма налога, которую он должен выплатить составляет 2200*0,1+42,5 = 262,2 руб.

Бобров – наследник первой очереди, сумма наследования равна 760 руб., сумма налога равна 0 руб.

И наконец, Колесников – наследник второй очереди, он имеет сумму наследования 1800 руб., а сумма налога равна 1800*0,2+85 = 445 руб.

Теперь нам нужно решить эту задачу в MSExcel. Для этого создаем 2 данные таблички, как показано на рисунке 6. При этом во второй табличке изменим формат ячеек (Для этого выделяем 2 и 3 столбцы таблицы, нажимаем правой кнопкой мыши: формат ячеек – числовой, с количеством чисел после запятой = 2). Также при рассмотрении 2 таблицы мы не будем учитывать числа 42,2; 85,0; 127,5 и 225,0 из 2 и 3 столбца для более удобных расчетов. Данные числа мы приплюсуем в конце. Данные преобразования показаны на рисунке 7.

Рисунок 6. Фрагмент листа Excel с условиями задачи 3.

Рисунок 7. Фрагмент листа Excel с установлением формата ячеек.

На рисунке 8 показано первое действие задачи.

Рисунок 8. Фрагмент листа Excel с началом решения задачи

Аналогичным способом находим остальные суммы налога. Результаты вычисления показаны на рисунке 9.

Рисунок 9. Фрагмент листа Excel с решением задачи

И в последнем действии мы находим окончательную сумму налога для каждого наследника. На рисунке 10 показано, как найти сумму налога в конечном счете. Ответы, полученные в результате вычисления, показаны на рисунке 11.

Рисунок 10. Фрагмент листа Excel с выполнением действий

Рисунок 11. Фрагмент листа Excel с результатом выполненных действий

Заключение

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

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

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

Поскольку таблицы содержат динамические формулы, их итоговые значения всегда будут актуальны. А это очень важно в современных условиях.

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

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

2. Информатика и информационные технологии. Учебник для 10-11 классов / Угринович Н.Д. – М.: БИНОМ. – 511 с.

3. Комягин В.Б. Компьютер для студентов. Самоучитель. Быстрый старт. Учебное пособие // М.: Триумф, 2003. с. – 400.

5. Электронная энциклопедия Windows (http://www.winpedia.ru)