Экономические расчеты в Excel

Технология экономических расчетов средствами MS Excel

Отчет по учебной практике

МДК 02.02 «Технология разработки и защиты баз данных»

Технология экономических расчетов средствами MS Excel

Вариант 23

Обучающейся Тулупова Максима Вячеславовича

Руководитель:

Игнатенко Екатерина Сергеевна

Оценка

Теоретическая часть________________

Практическая часть_________________

Сургут 2015

Тема теоретической части: Решение транспортных задач в MS Excel 2010

Тема практической части:Технология экономических расчетов средствами MS Excel

План теоретической части стр.
Решение транспортных задач в MS Excel 2010
План практической части стр.
1. Технология решения экономических задач с применением финансовых функций MS Excel. Анализ операций по кредитам и займам. Определение будущей стоимости на основе постоянной и переменной процентной ставки. Определение текущей стоимости.Оформление отчетной документации и защита проектов по MS Excel
2. Технология решения экономических задач с применением финансовых функций MS Excel. Определение срока платежа. Расчет периодических платежей, связанных с погашением займов.Оформление отчетной документации и защита проектов по MS Excel
Вывод
Список литературы

MS Excel 2010, его функции и возможности

Текст Текст Текст Текст Текст Текст Текст

Технология экономических расчетов средствами MS Excel

Технология решения экономических задач с применением финансовых функций MS Excel.

Определение будущей стоимости на основе постоянной и переменной процентной ставки. Определение текущей стоимости.Оформление отчетной документации и защита проектов по MS Excel

Текст Текст Текст Текст Текст Текст Текст

2. Технология решения экономических задач с применением финансовых функций MS Excel. Определение срока платежа. Расчет периодических платежей, связанных с погашением займов.Оформление отчетной документации и защита проектов по MS Excel

Текст Текст Текст Текст Текст Текст Текст

Вывод

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

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

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

Количественный финансовый анализ предполагает использование моделей и методов расчета финансовых показателей. Условно методы финансово-экономических расчетов можно разделить на две части: базовые и прикладные.

К базовым методам относятся:

1) простые и сложные проценты как основа операций, связанных с наращением или дисконтированием платежей;

2) расчет потоков платежей применительно к различным видам финансовых рент.

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

1) планирование и оценка эффективности финансово-кредитных операций;

2) расчет страховых аннуитетов;

3) планирование погашения долгосрочной задолженности;

4) планирование погашения ипотечных ссуд и потребительских кредитов;

5) финансовые расчеты по ценным бумагам;

6) лизинговые, факторинговые и форфейтинговые банковские операции;

7) планирование и анализ инвестиционных проектов и др.

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

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

— покупательная способность денег даже при небольшой инфляции со временем снижается.

Среди полного перечня финансовых функций Microsoft Excel, непосредственно предназначенных для финансовых расчетов, выделяется группа функций, используемая для анализа инвестиций и расчета операций по кредитам, ссудам и займам

Самостоятельная работа состоит из двух частей.

В первой части были рассмотрены современные компьютерные технологии, которые позволяют значительно упростить и автоматизировать процесс проектирования и создания. Microsoft Office — офисный пакет приложений, созданных корпорацией Microsoft для операционных систем Microsoft Windows и Apple Mac OS X. В состав этого пакета входит программное обеспечение для работы с различными типами документов: текстами, электронными таблицами, базами данных и др.

Во второй части были решены экономические задачи в программе Excel (2007) 2010.

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

1. Гобарева Я.Л., Городецкая О.Ю., Кочанова Е.Р. Сборник практических заданий по курсу «Автоматизированные информационные системы в экономике». –М.: Финансовая академия, 2001. -124 с.

2. Карлберг К. Бизнес-анализ с помощью Microsoft Excel. /Пер. с англ. –М.: Вильямс, 2005. -464 с.

3. Коцюбинский А.Ю., Грошев С.В. Excel для менеджера и экономиста в примерах. –М.: Гросс-Медиа, 2004. -300с.

4. Мур Дж., Уэдерфорд Л. Экономическое моделирование в Microsoft Excel. –М.: Вильямс, 2004. -1018 с.

5. Никольская Ю., Спиридонов А. Excel в помощь бухгалтеру и экономисту. –М.: Вершина, 2006. -256 с.

6. Пикуза В., Геращенко А. Экономические и финансовые расчеты в Excel. –СПб.: Питер, 2004. -396 с.

7. Проктор С.К. Построение финансовых моделей с помощью Microsoft Excel. /Пер. с англ. –М.: Интернет-Трейдинг, 2005. -430 с.

8. Саймон Дж, Анализ данных в Excel. /Пер. с англ. –М.: Диалектика, 2004. -516 с.

9. Сингаевская Г.И. Функции в Excel. Решение практических задач. –М.: Вильямс, 2005. -879 с.

10. Соломенчук В., Романович А. Практическая бухгалтерия на Excel для малого бизнеса. –СПб.: Питер, 2005. -250 с.

11. Уокенбах Дж. Подробное руководство по созданию формул в Excel 2003. /Пер. с англ. –М.: Диалектика, 2005. -637 с.

12. Уэйн Л. Винстон. Microsoft Excel: анализ данных и построение бизнес-моделей / Пер. с англ. –М.: ИТД «Русская редакция», 2005. -576 с.

Приложение 9

Функция ПОЛУЧЕНО в Excel и описание ее аргументов

Данная функция относится к категории финансовых функций Excel и имеет следующий синтаксис:

=ПОЛУЧЕНО(дата_согл;дата_вступл_в_силу;инвестиция;скидка;)

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

Описание аргументов функции:

  1. Дата_согл – величина в формате Даты в Excel, которая характеризует дату расчета за ценные бумаги (должна быть более поздняя, чем момент их выпуска).
  2. Дата_вступ_в_силу – величина в формате Даты в Excel, которая характеризует срок погашения ценных бумаг. То есть, она соответствует моменту окончания их срока действия.
  3. Инвестиция – данные Числового, Денежного или Финансового типа, которые характеризуют сумму, выплаченную за ценные бумаги.
  4. Скидка – данные Процентного типа, которые характеризуют ставку дисконтирования.
  5. – данные Числового типа в виде числа, находящегося в диапазоне от 0 до 4, характеризующие один из доступных способов расчета количества дней:
  • 0 – NASD – Американский 30/360;
  • 1 – Фактический;
  • 2 – Фактический/360;
  • 3 – Фактический/365;
  • 4 – Европейский 30/360.

Функция ПОЛУЧЕНО упрощает финансовые расчеты в Excel и фактически реализует следующую запись:

=A2/(1-(B2*(C2/D2), где:

  • A2 – инвестиция;
  • B2 – скидка (дисконт);
  • С2 – число дней, прошедших между датами выпуска и погашения;
  • D2 – количество дней в году в зависимости от выбранного типа базиса.

Примечания:

  1. Дата соглашения (дата_согл) эквивалентна дате реализации купона либо облигации. Срок платежа соответствует моменту истечения срока годности этого купона. Например, 1 января 2018 года была выпущена облигация, срок действия которой составляет 20 лет. Покупатель приобрел данную облигацию ровно спустя один год после ее выпуска (1 января 2019 года). Тогда дата выпуска – это 1 января 2018 года, а соглашение – 1 января 2019 года и погашение – 1 января 2038 года (спустя 30 лет после выпуска).
  2. Числовые данные полей дата_согл, дата_вступл_в_силу и базис округляются до ближайших целых чисел (Excel выполняет преобразование значений типа Дата в код времени Excel, полученные значения усекаются до целых чисел).
  3. Результатом работы функции ПОЛУЧЕНО будет код ошибки #ЗНАЧ!, если в качестве аргументов дата_согл и дата_вступл_в_силу были переданы значения, не являющиеся допустимыми датами в Excel.
  4. Если в качестве аргументов инвестиция и скидка были переданы отрицательные числа или 0 (нуль), функция ПОЛУЧЕНО вернет код ошибки #ЧИСЛО!.
  5. Если в качестве аргумента базис было передано значение, выходящее за пределы диапазона от 0 до 4 включительно, функция ПОЛУЧЕНО вернет код ошибки #ЧИСЛО!.
  6. Недопустимым случаем является тот, в котором параметр дата_согл принимает значение, равное или превышающее значение, принятое параметром дата_вступл_в_силу. В этом случае также будет возвращен код ошибки #ЧИСЛО!.



Расчет фактической стоимости облигаций в Excel по функции ПОЛУЧЕНО

Пример 2. Были выпущены облигации 30.06.2018 сроком на 1 год. Сумма инвестиций в облигации – 2 млн. рублей. Прогнозируемая стоимость полностью обеспеченных бумаг составляет 2 млн. 100 тыс.

рублей. Соответствует ли фактическая стоимость облигаций на момент их погашения прогнозируемой, если дисконт составляет 8%? Если нет, определить дисконт для прогнозируемой стоимости.

Исходная таблица имеет следующий вид:

Для решения используем следующую формулу:

Функция ЕСЛИ выполняет проверку равенства прогнозируемой и фактической стоимости ценных бумаг при дисконте 8%. В случае неравенства величин будет выведен результат вычислений 1-(B5/B7))*100 – записи, полученной исходя из математического смысла функции ПОЛУЧЕНО:

Средства_за_полностью_обеспеченную_облигацию = инвестиции/(1-(дисконт*(срок_годности_облигации/число_дней_в_году)))

Примечание: значение срок_годности_облигации/число_дней_в_году было опущено в данном примере, поскольку срок годности облигаций составляет 1 год (365/365 = 1).

То есть, фактический дисконт составил 9,09%.

ВПР в Excel и примеры по экономике

Составим формулу для подбора стоимости в зависимости от даты реализации продукта.

Изменения стоимостного показателя во времени представлены в таблице вида:

Нужно найти, сколько стоил продукт в следующие даты.

Назовем исходную таблицу с данными «Стоимость». В первую ячейку колонки «Цена» введем формулу: =ВПР(B8;Стоимость;2). Размножим на весь столбец.

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

Для дат между 01.01.2015 и 01.04.2015 формула останавливает поиск на 01.01.2015 и возвращает значение из второго столбца той же строки. То есть 87. И так прорабатывается каждая дата.

Составим формулу для нахождения имени должника с максимальной задолженностью.

В таблице – список должников с данными о задолженности и дате окончания договора займа:

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

  1. Для нахождения максимальной задолженности используем функцию МАКС (=МАКС(B2:B10)). Аргумент – столбец с суммой долга.
  2. Так как функция вертикально просматривает крайний левый столбец диапазона (а суммы находятся во втором столбце), добавим в исходную таблицу столбец с нумерацией.
  3. Чтобы найти номер предприятия с максимальной задолженностью, применим функцию ПОИСКПОЗ (=ПОИСКПОЗ(C12;C2:C10;0)). Тип сопоставления – 0, т.к. к столбцу с долгами не применялась сортировка.
  4. Чтобы вывести имя должника, применим функцию: =ВПР(D12;Должники;2).

Сделаем из трех формул одну: =ВПР (ПОИСКПОЗ (МАКС (C2:C10); C2:C10;0); Должники;2). Она нам выдаст тот же результат.

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

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

ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MS EXCEL

⇐ ПредыдущаяСтр 8 из 18Следующая ⇒

Задание №1.

Оценка рентабельности рекламной компании фирмы.

Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу. Создайте таблицу оценки рекламной компании по образцу (рис.8.1). Введите исходные данные: Месяц, Расходы на рекламу А(0) (р.), Сумма покрытия В(0) (р.), Рыночная процентная ставка ( j ) = 13,7%. Выделите для рыночной процентной ставки, являющейся константой, отдельную ячейку — С3, и дайте этой ячейке имя «ставка».

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

· выделите ячейку (группу ячеек), которой необходимо присвоить имя;

· щелкните поле Имя, которое расположено в строке формул слева;

· введите имя ячейки;

· нажмите клавишу Enter.

Помните, что по умолчанию имена ячеек являются абсолютными ссылками.

Рис. 8.1. Исходные данные

Произведите расчеты во всех столбцах таблицы.

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

Формула для расчета:

А(n) = А(0) Н (1 + j/12)(1-n) , в ячейке C6 наберите формулу =B6*(1+ставка/12)^(1-$A6) .

Адрес ячейки А6 в формуле имеет комбинированную адресацию: абсолютную адресацию по столбцу и относительную по строке, и записывается в виде $A6.

При расчете расходов на рекламу нарастающим итогом надо учесть, что первый платеж равен значению текущей стоимости расходов на рекламу, значит в ячейку D6 введем значение = С6, но в ячейке D7 формула примет вид =D6+C7. Далее формулу ячейки D7 скопируйте в ячейки D8:D17. Обратите внимание, что в ячейках нарастающего итога с мая по декабрь будет находиться одно и то же значение, поскольку после месяца мая расходов на рекламу не было.

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

Для расчета текущей стоимости покрытия скопируйте формулу из ячейки С6 в ячейку F6. В ячейке F6 должна быть формула: =E6*(1+ставка/12)^(1-$A6). Далее с помощью маркера автозаполнения скопируйте формулу в ячейки F7:F17.

Сумма покрытия нарастающим итогом рассчитывается аналогично расходам на рекламу нарастающим итогом, поэтому в ячейку G6 поместим содержимое ячейки F6 (=F6), а в G7 введем формулу = G6 + F7.
Далее формулу из ячейки G7 скопируем в ячейки G8:G17.
В последних трех ячейках столбца будет представлено одно и то же значение, ведь результаты рекламной компании за последние три месяца на сбыте продукции уже не сказывались.

Сравнив значения в столбцах D и G, уже можно сделать вывод о рентабельности рекламной компании, однако расчет денежных потоков в течении года (столбец H), вычисляемый как разница колонок G и D, показывает, в каком месяце была пройдена точка окупаемости инвестиций.
В ячейке H6 введите формулу = G6 – D6, и скопируйте ее вниз на всю колонку.

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

В ячейке Е19 произведите расчет количества месяцев, в которых сумма покрытия имеется. Используйте функцию «Счет» (Вставка-Функция-Статистические), указав в качестве диапазона «Значение 1» интервал ячеек E7:E14.

После расчета формула в ячейке Е19 будет иметь вид
= СЧЕТ(E7:E14).

В ячейке Е20 произведите расчет количества месяцев, в которых сумма покрытия больше 100 000 р. (используйте функцию СЧЕТЕСЛИ, указав в качестве диапазона «Значение» интервал ячеек E7:E14, а в качестве условия >100000).

После расчета формула в ячейке Е20 будет иметь вид
=СЧЁТЕСЛИ(E7:E14;»>100000″).

Результаты расчетов представлены на рис. 8.2.

Рис. 8.2. Результаты расчетов

Постройте графики по результатам расчетов:

· «Сальдо дисконтированных денежных потоков нарастающим итогом» по результатом расчетов колонки Н; (рис. 8.3)

Рис. 8.3.

Графики дают наглядное представление об эффективности расходов на рекламу.

Задание 2.

Фирма поместила в коммерческий банк 45 000 р. на 6 лет под 10,5% годовых. Какая сумма окажется на счете, если проценты начисляются ежегодно? Рассчитать, какую сумму надо поместить в банк на тех же условиях, чтобы через шесть лет накопить 250 000 р.

Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу. Создайте таблицу констант и таблицу для расчета наращенной суммы вклада по образцу (рис. 8.4).

Рис. 8.4. Исходные данные

Произведите расчеты. А(n) двумя способами:

· с помощью формулы А(n)= А(0) Н (1+j)n (В ячейку D10 ввести формулу =$B$3*(1+$B$4)^A10 или использовать функцию СТЕПЕНЬ);

· с помощью функции БС (рис.8.5).

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

Синтаксис функции БС: БС ( ставка ;кпер; плт; пс; тип), где ставка – это процентная ставка за период; кпер – это общее число периодов платежей по аннуитету; плт (плата) – это выплата, производимая в каждый период, вводится со знаком «–», это значение не может меняться в течении всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов;

пс – это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

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

Тип — это число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент тип опущен, то он полагается равным 0 (0 – платеж в конце периода, 1 –платеж в начале периода).

Все аргументы, означающие деньги, которые платятся (например, депозитные вклады), представляются отрицательными числами.

Деньги, которые получены (например, дивиденды), представляются положительными числами.

Для ячейки С10 задание параметров расчета функции БС имеет вид, как на рис.8.5.

Рис. 8.5. Аргументы функции БС

Конечный вид расчетной таблицы приведен на рис.8.6.

Рис. 8.6. Расчетная таблица

Используя режим Подбор параметра рассчитайте, какую сумму надо поместить в банк на тех же условиях, чтобы через шесть лет накопить 250 000 р. (рис. 8.7).

В результате подбора выясняется, что для первоначальная сумма для накопления 137 330,29 р. позволит накопить заданную сумму 250000 р.

Рис. 8.7. Подбор параметра

Задание 3.

Сравнить доходность размещения средств предприятия, положенных в банк на один год, если проценты начисляются m раз в год, исходя из процентной ставки j=9,5% годовых. По результатам расчетов построить график изменения доходности инвестиционной операции от количества раз начисления процентов в году (капитализации).
Выясните, при каком значении j доходность составит 15% (при капитализации m=12).

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

Рис. 8.8. Исходные данные

Формула для расчета доходности: Доходность = (1 + j/m)m – 1.
Установите формат значений доходности – процентный. Для проверки правильности ваших расчетов сравните полученный результат с правильным ответом: для m=12 доходность =9,92%. Произведите обратный расчет (используя режим Подбор параметра, рис. 8.9) для выяснения, при каком значении j доходность составит 15% (при капитализации m=12).

Добавить комментарий

Закрыть меню