Будь умным!


У вас вопросы?
У нас ответы:) SamZan.ru

тематики И Н Ф О Р М А Т И К А УЧЕБНОМЕТОДИЧЕСКИЙ КОМПЛЕКС ИНФОРМАЦИОННЫЕ РЕСУРСЫ ДИСЦИПЛИНЫ

Работа добавлена на сайт samzan.ru: 2015-07-10


PAGE  2

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

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

«СЕВЕРО-ЗАПАДНЫЙ ГОСУДАРСТВЕННЫЙ ЗАОЧНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ»

Кафедра информатики и прикладной математики

И Н Ф О Р М А Т И К А

УЧЕБНО-МЕТОДИЧЕСКИЙ КОМПЛЕКС

ИНФОРМАЦИОННЫЕ РЕСУРСЫ ДИСЦИПЛИНЫ

Методические указания к выполнению лабораторных работ

«Применение электронных таблиц

в финансовых и экономических расчетах»

Институты:

Системного анализа, автоматики и управления.

Управления производственными и инновационными программами

Специальности:

080105.65 – финансы и кредит

080507.65 – менеджмент организации

220201.65 – управление и информатика в технических системах

220301.65 – автоматизация технологических процессов и производства в машиностроении

Направления подготовки бакалавра:

080100.62 – экономика

080500.62 – менеджмент

220200.62 – автоматизация и управление

Санкт-Петербург

Издательство СЗТУ

2009


Утверждено редакционно-издательским советом университета

УДК 881.3

Информатика: учебно-методический комплекс (информационные ресурсы дисциплины: методические указания к выполнению лабораторных работ «Применение электронных таблиц в финансовых и экономических расчетах»)/сост.: Л.В. Боброва, Е.А. Рыбакова, Н.А. Смирнова. – СПб.: Изд-во СЗТУ, 2009. –  с.

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

В методических указаниях приводятся тринадцать лабораторных работ. Каждая работа построена таким образом, что ее могут выполнять как студенты, работающие с пакетом Ms Office 2003, так и те студенты, на компьютерах которых установлен Ms Office 2007 (в последнем случае команды, отличающиеся от команд Excel 2003, выделены серым цветом).

Рассмотрено на заседании кафедры информатики 19.06.09 г, одобрено методической комиссией факультета общеопрофессиональной подготовки 26.06.09.

 

РЕЦЕНЗЕНТЫ:

кафедра информатики и прикладной математики СЗТУ (зав. каф. Г.Г. Ткаченко, канд. физ.-мат. наук, доц.);

М.И. Барабанова, канд. экон.. наук, доц. кафедры информатики СПбГУЭФ.

СОСТАВИТЕЛИ:

Л.В. Боброва, канд. техн. наук, проф.;

Е.А. Рыбакова, ст. преп.;

Н. А. Смирнова, канд. техн. наук, доц.

Северо-Западный государственный заочный технический университет, 2009


Введение

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

Каждая работа построена таким образом, что ее могут выполнять как студенты, работающие с пакетом Ms Office 2003, так и те студенты, на компьютерах которых установлен Ms Office 2007 (в последнем случае команды, отличающиеся от команд Excel 2003, выделены серым цветом).

Библиографический список

Основной:

1. Информатика. Базовый курс: учебник для вузов/ под ред. С.В. Симановича. – СПб.: Питер, 2007.

2. Гарнаев, А.Н. Excel, VBA, Internet в экономике и финансах/ А.Н. Гарнаев. – СПб.: BHV, 2003.

3. Боброва, Л.В. Информатика; учеб. пособие / Л.В. Боброва. – СПб.: Изд-во СЗТУ, 2007.

Дополнительный:

4. Пикуза, В.Н. Экономические и финансовые расчеты в Excel/ В.Н. Пикуза, А.С. Гаращенко. – СПб., BHV, 2004.

5. Долженов, В.А. Microsoft Excel 2000/ В.А. Долженов, Ю.В. Колесников. –СПб.: BHV, 1998.

6. Лавренов, С.М. Excel: Сборник примеров и задач/ С.М. Лавренов. – М.: Финансы и статистика, 2000.


Работа 1. МАТРИЧНЫЕ ОПЕРАЦИИ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

  1.  Цель работы

Освоение операций с матрицами.

2. Основные теоретические положения

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

3. Порядок выполнения работы

 Задание 1. Выполнить сложение матриц.

Задание 2. Выполнить умножение матрицы на число и вычитание матрицы.

 Задание 3. Вычислить определитель матрицы.

 Задание 4. Вычислить обратную матрицу.

 Задание 5. Перемножить матрицы.

3.1. Выполнение задания 1

Найдем сумму матриц С=А+В, где

            А =     и       В = .

 3.1.1. Ввод  матриц

Откройте новую книгу Excel (Пуск – Программы – Microsoft Office -Excel).

Введите матрицу А в блок ячеек А3:С5, а матрицу В в блок ячеек Е3:G5 (табл. 1 – показ вычислений, табл. 2 – показ формул).

3.1.2. Присвоение имени блокам

Выполните следующие действия:

  •  выделите блок А3:С5;
  •  выполните команды меню Вставка – Имя – Присвоить – А – ОК (В программе Еxcel 2007 выполнить команды Вставка – Определенные имена – Присвоить имя);
  •  выделите блок Е3:G5;
  •  выполните команды меню Вставка – Имя – Присвоить – В – ОК (В программе Еxcel 2007 выполнить команды Вставка – Определенные имена – Присвоить имя).

3.1.3. Сложение матриц

  •  выделите диапазон ячеек А7:С9, где будет размещена сумма (матрица С);
  •  введите с клавиатуры знак равенства =;
  •  выполните команды меню Вставка – Имя – Вставить – А – ОК (Вставка – Определенные имена – Использовать в формуле).
  •  введите с клавиатуры знак +;
  •  выполните команды Вставка – Имя – Вставить – В – ОК (Вставка – Определенные имена – Использовать в формуле);
  •  для того чтобы формула = А + В была введена во все выделенные ячейки диапазона, нажмите одновременно клавиши Ctrl+Shift+Enter.

3.2. Выполнение задания 2

3.2.1. Вычисление матрицы D = 3 * A - B

Используем матрицы А и В, которые были введены ранее (п. 3.1.1). Поскольку матрица D также будет иметь размерность 3х3, выделите диапазон ячеек А11:С13.

                       Таблица 1. Показ вычислений

A

B

C

D

E

F

G

  1.  

Задание 1. Вычисление суммы матриц C=A+B

  1.  

Матрица А

Матрица В

  1.  

1

4

-3

-3

0

4

  1.  

-2

6

5

5

-7

2

  1.  

7

-8

9

-4

6

-8

  1.  

Матрица С

  1.  

-2

4

7

  1.  

3

-1

7

  1.  

3

-2

1

  1.  

Задание 2. Вычисление матрицы D=3*A-B

  1.  

6

12

5

  1.  

-11

25

13

  1.  

25

-30

35

  1.  

Задание 3. Вычисление определителя матрицы А

  1.  

228

  1.  

Задание 4. Вычисление обратной матрицы А

  1.  

0,412

-0,263

0,009

  1.  

0,232

-0,053

-0,05

  1.  

-0,11

0,158

0,061

  1.  

Задание 5. Умножение матриц

  1.  

5

-10

-12

  1.  

16

-12

-36

  1.  

-97

110

-60

  •  активизируйте ячейку А15, затем щелкните мышью по пиктограмме Мастер функций (меню Формулы – Вставить функцию);
  •  в окне Категория выберите Математические, а в окне ФункцияМОПРЕД, затем ОК;

3.3.2. Ввод формулы

  •  В открывшемся окне Мастера функций в поле Массив введите диапазон ячеек исходной матрицы А3:С5, затем

Обратите внимание! При вводе диапазона А3:С5 в окне Мастера функций появилась формула =МОПРЕД(А), так как выделенному диапазону ранее было присвоено имя А.

3.4. Выполнение задания 4

3.4.1. Выбор диапазона

Выполните следующие действия:

  •  выделите диапазон ячеек А17:С19, в нем будем размещать обратную матрицу;

Таблица 2 . Показ формул в табличном процессоре Excel

A

B

C

D

E

F

G

1

Задание 1. Вычисление суммы матриц C=A+B

2

Матрица А

Матрица В

3

1

4

-3

-3

0

4

4

-2

6

5

5

-7

2

5

7

-8

9

-4

6

-8

6

Матрица С

7

=А+В

=А+В

=А+В

8

=А+В

=А+В

=А+В

9

=А+В

=А+В

=А+В

10

Задание 2. Вычисление матрицы D=3*A-B

11

=3*А-В

=3*А-В

=3*А-В

12

=3*А-В

=3*А-В

 =3*А-В

13

=3*А-В

=3*А-В

 =3*А-В

14

Задание 3. Вычисление определителя матрицы  А

15

=МОПРЕД(А)

16

Задание 4 . Вычисление обратной матрицы А

17

=МОБР(А3:С5)

=МОБР(А3:С5)

=МОБР(..

18

=МОБР(А3:С5)

=МОБР(А3:С5)

=МОБР(..

19

=МОБР(А3:С5)

=МОБР(А3:С5)

=МОБР(..

20

Задание 5. Умножение матриц

21

=МУМНОЖ(А3:С5;Е3:G5)

=МУМНОЖ(А3:С5;Е3:G5)

=МУМН..

22

=МУМНОЖ(А3:С5;Е3:G5)

=МУМНОЖ(А3:С5;Е3:G5)

=МУМН..

23

=МУМНОЖ(А3:С5;Е3:G5)

=МУМНОЖ(А3:С5;Е3:G5)

=МУМН..


3.4.2. Ввод формулы

  •  щелкните мышью по пиктограмме Мастер функций (меню Формулы – Вставить функцию);;
  •  в окне Категория выберите Математические, а в окне ФункцияМОБР, затем ОК;
  •  в открывшемся окне Мастера функций в поле Массив введите диапазон ячеек исходной матрицы А3:С5;
  •  нажмите одновременно клавиши Ctrl+Shift+Enter.

3.5. Выполнение задания 5

3.5.1. Работа с Мастером функций

Перемножим матрицы А и В. В результате получим матрицу М = А * В.

Для этого:

  •  выделите диапазон ячеек А21:С23, в нем будем размещать результат вычислений;
  •  щелкните мышью по пиктограмме Мастер функций (меню Формулы – Вставить функцию);
  •  в окне Категория выберите Математические, а в окне ФункцияМУМНОЖ, затем ОК;
  •  в открывшемся окне Мастера функций в поле Массив 1  введите адрес исходной матрицы А (А3:С5), а в поле Массив 2 введите адрес исходной матрицы В (Е3:G5), затем Ctrl+Shift+Enter.

Обратите внимание! Формула записалась в виде =МУМНОЖ(А3:С5;Е3:G5).

3.6. Самостоятельная работа

Вычислите матрицу, обратную матрице В. Результат вычислений продемонстрируйте преподавателю.

4. Отчет по работе

Распечатка таблицы (показ формул и показ вычислений).

Литература: [6], с. 166-171.

Работа 2. РЕШЕНИЕ СИСТЕМ УРАВНЕНИЙ

1. Цель работы

Научиться приемам решения систем уравнений.

2. Основные теоретические положения

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

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

3. Порядок выполнения работы

Задание 1. Решить систему уравнений

с использованием процедуры Поиск решения.

Задание 2. Решить систему уравнений из задания 1 матричным методом.

Задание 3. Решить систему уравнений согласно индивидуальному заданию.

3.1. Выполнение задания 1

3.1.1. Ввести исходные данные в строки 1-9 электронной таблицы (табл. 3 в режиме показа вычислений и табл. 4 в режиме показа формул).

3.1.2. В качестве начального приближения возьмем все значения переменных равными единице. Заполнить этими значениями ячейки А10:С10.

3.1.3. Ввести формулы для вычисления левых частей уравнений в ячейки А12:А14.

Для вычисления левых частей уравнений будем использовать функцию СУММПРОИЗВ.

3.1.4. В ячейки Е12:Е14 ввести формулы для вычисления отклонений значений левых частей уравнения при данном наборе переменных от правых частей исходных уравнений.

3.1.5. В ячейке F13 вычислить суммарные отклонения.

Теперь для нахождения корней обратимся к процедуре Поиск решения: Сервис – Поиск решения (В Excel 2007 следует выполнить команды: меню Данные – вкладка Анализ – Поиск решения).

.

3.1.6. В окне Поиск решения ввести:

Установить целевую ячейку  F13

Равной        Значению 0

    Изменяя ячейки                                А10:С10

и щелкнуть по кнопке Добавление ограничений

3.1.7. В окне Добавление ограничений ввести:

А12:А14=Е5:Е7, щелкнуть Ок.

3.1.8. Щелкнуть по кнопке Выполнить.

                    Таблица 3. Решение системы уравнений. Показ вычислений

 

A

B

C

D

E

F

1

Решение системы уравнений с использованием процедуры

Поиск Решения

2

Имена переменных

 

 

 

3

X

Y

Z

 

 

 

4

Матрица коэффициентов системы уравнений

 

Правая часть системы уравнений

 

5

3

4

2

 

8

 

6

2

-1

-3

 

-4

 

7

1

5

1

 

0

 

8

 

 

 

 

 

 

9

Приближенные значения неизвестных (начальное приближение)

 

 

 

10

1

1

1

 

 

 

11

Значения левой части системы уравнений для приближенных значений неизвестных

 

 

 

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

Суммарное отклонение (целевая функция)

12

9

 

 

 

1

 

13

-2

 

 

 

2

10

14

7

 

 

 

7

 

В результате получим следующие значения переменных (табл. 5).

x = 2; у = -1;         z = 3.

3.2. Выполнение задания 2

Для решения системы уравнений матричным способом следует:

а) найти матрицу коэффициентов, обратную исходной матрице;

б) умножить полученную обратную матрицу на столбец свободных членов.

3.2.1. Вычисление обратной матрицы:

  •  выделить ячейки А16:С18;
    •  выполнить команды ВставкаФункция (меню Формулы – Вставить функцию) МатематическиеМОБР - Ок;
    •  указать диапазон исходной матрицы А5:С7;
    •  одновременно нажать клавиши Ctrl+Shift+Enter.

  1.  Умножение матриц:
    •  выделить ячейки Е16:Е18;


Таблица 5. Результат решения системы уравнений

A

B

C

D

E

F

1

Решение системы уравнений с использованием процедуры

Поиск Решения

2

Имена переменных

 

 

 

3

X

Y

Z

 

 

 

4

Матрица коэффициентов системы уравнений

 

Правая часть системы уравнений

 

5

3

4

2

 

8

 

6

2

-1

-3

 

-4

 

7

1

5

1

 

0

 

8

Приближенные значения неизвестных (решение)

 

 

 

9

2

-1

3

 

 

 

10

Значения левой части системы уравнений для приближенных значений неизвестных

 

 

 

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

Суммарное отклонение (целевая функция)

11

8

 

 

 

0

 

12

-4

 

 

 

0

0

13

0

 

 

 

0

 

  •  Вставка – Функция (меню Формулы – Вставить функцию)– Математические – МУМНОЖ - Ок;
    •  ввести:     Массив 1    А16:С18

   Массив 2    Е5:E7 ;

  •  одновременно нажать клавиши Ctrl+Shift+Enter.

В результате в ячейках Е16:Е18 получим те же самые значения корней, что в п. 3.1.

3.3. Выполнение задания 3

3.3.1. Выбрать из табл. 6 индивидуальное задание по последней цифре шифра.

3.3.2. Решить систему уравнений с использованием процедуры Поиск решения и матричным методом.

4. Отчет по работе

Результаты выполнения заданий 13.

Литература: [1], с. 113-116, [6], с. 208-215.

Таблица 6. Индивидуальные задания

№ варианта

Система уравнений

№ варианта

Система уравнений

0

5

1

6

2

7

3

8

4

9

Работа 3. ПЛАНИРОВАНИЕ ВЫПУСКА ПРОДУКЦИИ

  1.  Цель работы

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

  1.  Основные теоретические положения

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

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

Таблица 7. Сведении о работе отраслей

Отрасли

Объемы производства

отраслей

Производственное потребление отраслей за предыдущий период

Прогнозируе-мый конечный

спрос

1

2

3

1

2

3

600

1000

800

250

150

0

100

500

300

160

0

400

2000

2000

3000

  1.  Математическая постановка задачи

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

Пусть Хi  – величина, равная суммарному выпуску продукции отрасли i;

     xij – количество продукции отрасли i, необходимое для того, чтобы отрасль j произвела Xj единиц своей продукции;

Yi – количество продукции отрасли i, оставшееся для внешнего потребления (конечная продукция).

Тогда взаимосвязь отраслей в процессе производства и потребления отдельного продукта Хi (i=1, 2, 3) может быть описана в виде следующих уравнений:

          (1)

Используем понятие коэффициентов прямых затрат (технологических коэффициентов) aij:

– количество продукции отрасли i, необходимое для того, чтобы отрасль j произвела одну единицу своей продукции.

Тогда xi j = aijXj и система уравнений (7) будет иметь следующий вид:

                                (2)

Или в матричной форме

                Х=АХ+Y,             (3)

где  – матрица прямых затрат,

 Х – вектор-столбец выпуска продукции в предыдущем периоде.

 Y – вектор-столбец конечного спроса в предыдущем периоде.

2.2. Решение задачи

2.2.1. Определение вектора конечной продукции за предыдущий период

По условию задачи известны объемы производства каждой из отраслей за предыдущий период (суммарный выпуск продукции отрасли i): X1 = 600, X2 = 1000, X3 = 800 и значения xij  (i, j = 1, 2, 3):

  

Отсюда, используя (1), можно определить значения Yi , i = 1, 2, 3, конечной продукции каждой из отраслей за предыдущий период.

            (4)

Таким образом, вектор конечной продукции за предыдущий период найден Y = (90, 350, 100).

Для определения вектора выпуска продукции Х при заданном конечном прогнозируемом векторе спроса Y = (2000, 2000, 3000) надо решить систему уравнений (3), из которого следует, что

   Х = (Е-А)-1Y,             (5)

где Е – единичная матрица.

     Матрица S=(E-A)-1 – называется матрицей полных затрат.

2.2.2. Определение коэффициентов прямых затрат

Учитывая, что технология производства не изменилась, определим коэффициенты прямых затрат aij:

Таким образом, матрица коэффициентов прямых затрат будет иметь вид

.       (6)

2.2.3. Проверка продуктивности матрицы

Все элементы матрицы А неотрицательные, А  0.

Для того чтобы система уравнений (5) имела единственное неотрицательное решение при любом неотрицательном векторе Y, необходимо, чтобы матрица А была продуктивной. Экономический смысл продуктивности состоит в том, что существует такой план выпуска продукции, при котором каждая отрасль сможет произвести необходимое количество конечной продукции. Можно показать, что для продуктивности матрицы А необходимо и достаточно, чтобы все главные миноры матрицы (Е-А) были положительными числами строго меньше единицы. Кроме того, известно: если сумма элементов каждого из столбцов неотрицательной квадратной матрицы А положительна и строго меньше единицы, то все главные миноры матрицы (Е-А) положительны и строго меньше единицы.

Суммы элементов каждого столбца матрицы А (6) соответственно равны:

Следовательно, в силу вышесказанного, матрица А продуктивна, выражение (5) имеет смысл и вектор Y неотрицателен. Следовательно, для нахождения плана выпуска продукции Х можно воспользоваться формулой (5).

2.2.4. Вычисление матрицы Е - А

Вычислим матрицу (Е - А):

(7)

2.2.5. Вычисление обратной матрицы (Е-А)-1

Известно, что матрица В-1 называется обратной по отношению к квадратной матрице В, если произведение В * В-1 = Е   (Е – единичная матрица).

Для вычисления обратной матрицы воспользуемся формулой:

    .     (8)

Здесь (Bij) – матрица, полученная из элементов Bij, а Bij  – алгебраические дополнения элементов матрицы.

   Bij=(-1)i+j Mij,                (9)

где Mij – минор элемента aij (минор – это такой определитель, который получается из матрицы вычеркиванием строки и столбца, на пересечении которых стоит данный элемент).

Вычислим значения алгебраических дополнений элементов матрицы (Е - А). Обозначим для простоты вычислений Е - А=В

;       ;

;      ;

 ;       ;

;    ;

.

 

Таким образом, .

2.2.6. Вычисление транспонированной матрицы

Поменяв в матрице [Е-А] строки и столбцы местами, получаем

2.2.7. Вычисление определителя матрицы [Е-А]

Вычислим определитель, применив разложение по первой строке

2.2.8. Вычисление матрицы прямых затрат S

По формуле S=(E-A)-1=B-1=

.

2.2.9. Определение вектора выпуска продукции Х

Зная S и Y, вычислим X по формуле:

 Х=SY.

Отсюда

Таким образом, вектор выпуска продукции в следующем периоде при заданном векторе конечной продукции Y = (2000, 2000, 3000) равен  

X = (8637, 8322, 10985).

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

3. Порядок выполнения работы

Задание. Реализовать балансовую модель в электронной таблице (ЭТ).

3.1. Выполнение задания

Компьютерная реализация балансовой модели в ЭТ показана в табл. 8 (режим показа формул) и   табл. 9 (режим показа вычислений).

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

3.1.1. Создать блок исходных данных. В ячейки А2:D5 ввести исходные данные из таблицы задания (табл. 7).

3.1.2. В ячейках B6:D8 разместить формулы для вычисления технологических коэффициентов:

  •  в ячейку В6 ввести формулу для вычисления первого коэффициента =B3/A$3 и скопировать ее в ячейки В7:B8;

Таблица  8. Балансовая модель. Показ формул

A

B

C

D

1

БАЛАНСОВАЯ МОДЕЛЬ

2

Объём про- изводства

Потребление отраслей

3

600

250

100

160

4

1000

150

500

0

5

800

0

300

400

6

Вычисление технологиче-ских коэф-фициентов

=В3/А$3

=С3/А$4

=D3/А$5

7

= В4/А$3

=С4/А$4

=D4/А$5

8

= В5/А$3

=С5/А$4

=D5/А$5

9

Проверка продуктивности матрицы А

10

=СУММ(B6:B8)

=СУММ(C6:C8)

=СУММ(D6:D8)

11

=ИЛИ(B10>=1;C10>=1;D10>=1)

=ЕСЛИ(A11=ИСТИНА;"Решения нет";"Матрица продуктивна")

12

Единичная матрица

1

0

0

13

0

1

0

14

0

0

1

15

Вычисление Е-А

=B12-B6

=C12-C6

=D12-D6

16

=B13-B7

=C13-C7

=D13-D7

17

=B14-B8

=C14-C8

=D14-D8

18

Вычисление обратной матрицы

=МОБР(B15:D17)

=МОБР(B15:D17)

=МОБР(B15:D17)

19

=МОБР(B15:D17)

=МОБР(B15:D17)

=МОБР(B15:D17)

20

=МОБР(B15:D17)

=МОБР(B15:D17)

=МОБР(B15:D17)

21

Спрос на будущий период

2000

План выпуска продукции

=МУМНОЖ(B18:D20;B21:B23)

22

2000

=МУМНОЖ(B18:D20;B21:B23)

23

3000

=МУМНОЖ(B18:D20;B21:B23)

  •  аналогично в ячейку С6 ввести формулу =C3/A$4 и скопировать ее в ячейки С7:С8;
  •  в ячейку D6  ввести формулу =D3/A$5 и скопировать ее в ячейки D7:D8.

3.1.3. В ячейках В10:D10 разместить формулы для подсчета суммы значений элементов по столбцам:

  •  в ячейку В10 ввести формулу =СУММ(В6:В8);
  •  скопировать формулу в ячейки С10:D10.

3.1.4. В строке 11 размещаем формулы для проверки продуктивности матрицы технологических коэффициентов:

Таблица 9. Балансовая модель. Показ вычислений

A

B

C

D

1

БАЛАНСОВАЯ МОДЕЛЬ

2

Объём производства

Потребление отраслей

3

600

250

100

160

4

1000

150

500

0

5

800

0

300

400

6

Вычисление технологических коэффициентов

0,417

0,1

0,2

7

0,25

0,5

0

8

0

0,3

0,5

9

Проверка продуктивности матрицы А

10

 

0,667

0,900

0,700

11

ЛОЖЬ

Матрица продуктивна

12

Единичная матрица

1

0

0

13

0

1

0

14

0

0

1

15

Вычисление Е-А

0,583

-0,1

-0,2

16

-0,25

0,5

0

17

0

-0,3

0,5

18

Вычисление обратной матрицы

2,113

0,930

0,845

19

1,056

2,465

0,423

20

0,634

1,479

2,254

21

Спрос на будущий период

2000

План выпуска продукции

8619,72

22

2000

8309,86

23

3000

10985,92

  •  в ячейку А11 ввести формулу =ИЛИ(В10>=1;C10>=1;D10>=1).

Эта формула проверяет содержимое ячеек В10:D10. Если хотя бы в одной из этих ячеек значение больше единицы (т.е. сумма значений элементов хотя бы в одном столбце превышает единицу), то в ячейке А11 будет записано значение ИСТИНА». В противном случае – значение «ЛОЖЬ»;

  •  в ячейку C11 ввести формулу

=ЕСЛИ(А11=”ИСТИНА”;”Нет решения”;”Матрица продуктивна”).

Эта формула проверяет содержимое ячейки А11 и если сумма элементов хотя бы одного столбца превысила единицу, выводит сообщение “Нет решения”, а в противном случае – “Матрица продуктивна”.

3.1.5. В строках 12 –14 разместить единичную матрицу Е.

3.1.6. В строках 15 – 17 произвести вычисление матрицы Е-А:

  •  в ячейку В15 поместить формулу =В12-В6;
  •  скопировать формулу в ячейки В16:D17.

3.1.7. В строках 18 – 20 разместим формулы для вычисления матрицы, обратной матрице Е-А:

  •  активизировать ячейку В18;
  •  зажав левую клавишу мыши, выделить диапазон ячеек В18:D20, где будет размещена обратная матрица;
  •  щелкнуть по пиктограмме Мастер функций fx (Формулы – Вставить функцию);
  •  в первом окне Мастера функций в поле Категория выбрать Математические);
  •  в поле Функция среди расположенных по алфавиту функций найти функцию МОБР;
  •  щелкнуть по кнопке Ок и перейти во второе окно Мастера функций;
  •  в поле Массив ввести адрес матрицы Е-А: диапазон ячеек В15:D17;
  •  чтобы формула была введена во все ячейки выделенного диапазона, следует одновременно нажать клавиши Ctrl+Shift+Enter; после этого в ячейки В18:D20 будет введена формула =МОБР(В15:D17).

3.1.8. В строках 21 – 23 поместим формулы для вычисления плана выпуска продукции:

  •  в ячейках В21:В23 разместить значения спроса на будущий период согласно заданию (табл. 7);
  •  выделить ячейки D21:D23, в которых будет размещена формула перемножения элементов матрицы, обратной Е-А, и вектора-столбца спроса;
  •  выполнить команды ВставкаФункция (Формулы – Вставить функцию);
  •  в категории Математические Мастера функций выбрать функцию МУМНОЖ - Ок;
  •  во втором окне Мастера в поле Массив1 ввести адрес обратной матрицы: диапазон ячеек В18:D20;

  •  в поле Массив2 ввести адрес вектора-столбца спроса: диапазон ячеек В21:В23;
  •  чтобы формула была введена во все ячейки выделенного диапазона, следует одновременно нажать клавиши Ctrl+Shift+Enter. После этого в ячейки D21:D23 будет введена формула =МУМНОЖ(В18:D20;B21:B23).

3.2. Самостоятельная работа

Приведите расчеты балансовой модели для данных, выбранных по последней цифре шифра, по табл. 10.

Таблица 10. Балансовая модель. Индивидуальные задания

№ варианта

Объем производства

Потребление отраслей

Спрос

1

1000

200

250

120

1000

600

150

50

40

1000

800

0

250

700

1000

2

800

150

250

140

2000

1000

200

500

150

2000

600

100

0

250

2000

3

600

100

100

0

1500

800

150

200

250

1500

1000

200

200

500

1500

4

800

0

250

120

1500

1200

200

300

40

1500

1000

150

350

700

1500

5

1000

150

250

140

2000

1500

200

350

150

2000

800

100

100

250

2000

6

1500

250

140

150

2000

1000

200

250

150

2000

800

100

120

200

2000

7

1000

150

250

100

1500

900

200

150

150

1500

800

100

0

250

1500

8

800

50

250

120

1000

1100

200

400

40

1000

900

150

350

700

1000

9

1000

200

250

120

1000

1500

150

400

40

1000

1000

50

350

700

1000

10

1000

200

250

120

2000

800

250

150

40

2000

800

100

350

300

2000

4. Отчет по работе

Распечатки таблицы вычислений.

Литература: [2], c. 509-515.

Работа 4. ФИНАНСОВЫЕ ВЫЧИСЛЕНИЯ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

1. Цель работы

Ознакомление с финансовыми функциями.

2. Основные теоретические положения

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

Пусть на срок t предоставлена в кредит некоторая сумма Р. За использование кредита надо платить. Возврат кредита составит S=P+I. Плата I носит название “процент”. Чем больше время, на которое выдается кредит, тем больше процент. В простейшем случае полагают

I = P r  t,

где r – процентная ставка.

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

                                S = P + I = P + P r t = P ( 1 + r t ).                               (1)

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

S=P(1+r)t .      (2)

Большинство финансовых функций  используют эти формулы.

3. Порядок выполнения работы

Задание 1. Вычислить размер возвращаемой ссуды, используя формулу (1).

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

Задание 3. Вычислить размер возвращаемой ссуды по схеме сложных процентов.

Задание 4. Рассчитать постоянную ренту, используя функцию БС (будущая сумма).

Задание 5. Используя функцию ПС (приведенная или современная Сумма), рассчитать сумму денег, которую можно получить под вексель.

Задание 6. Рассчитать срок вклада, необходимый для наращения нужной суммы.

Задание 7. Рассчитать процентную ставку кредита.

3.1. Выполнение задания 1

Рассмотрим порядок вычисления размера возвращаемой ссуды на следующем примере. В примере 1 и далее приведены фрагменты табл. 11.

ПРИМЕР 1

Ссуда в размере 1 млн долларов выдана 5 января 2009 г. по 20 сентября 2009 г. включительно. Годовая процентная ставка – 15 %. Какую сумму придется заплатить должнику в конце срока?

 

РЕШЕНИЕ

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

3.1.1.Ввод исходных данных в таблицу

В ячейки А1:В8 введите текст и исходные данные задачи.

Внимание! При вводе в ячейку В8 суммы кредита $1000000, сначала следует, поставив курсор в ячейку В8 выполнить команды ФорматЯчейки (в Excel 2007 выполните команды: меню Главная – вкладка Ячейки – Формат). В появившемся окне выбрать вкладку Числа, Числовые форматы: Денежный, Обозначение  $ Английский (США), ОК. Затем в ячейку В8 ввести число 1000000. 

3.1.2. Расчет доли года. Ввод комментариев

Для пересчета процентной ставки нужно знать, какую долю года составит срок кредита. В ячейку В10 введите формулу =В7-В6. Установите для ячейки В10 числовой формат.

В ячейке В10 появится результат – 258 (период вклада в днях). Произведем пересчет в долях года, для этого в ячейку В11 введите формулу =В10/366. Итог расчета дает 0,71 года.

Для наглядности в столбец С введите комментарии – те же формулы из смежных ячеек столбца В, но перед знаком = вводите знак апостроф (одинарная кавычка). Например: ‘= В7 – В6. В столбце С вычисления производиться не будут.

3.1.3. Расчет процентной ставки

В ячейку В12 введите формулу для пересчета процентной ставки =В5*В11. Ставка за период составила 11, а не 15 %. Теперь при подстановке значений в формулу (1) нужно брать либо рассчитанную долю года, либо 11 % вместо 15.

3.1.4. Расчет суммы возврата

По формуле (1) наращенная сумма равна

S=P(1+rt)=1 106 027.

Здесь ; t = 1.

Введите в ячейку В13 формулу =В8*(1+B5*В11) для расчета возвращаемой суммы, используя формулу (1).  

А

В

С

  1.  

ФИНАНСОВЫЕ ВЫЧИСЛЕНИЯ  В ЭТ

  1.  

Задание 1. Расчет возвращаемой суммы при получении кредита

  1.  

Исходные данные

  1.  

Переменные

Вычисления

Формулы и комментарии

  1.  

Годовая ставка

15%

  1.  

Дата выдачи кредита

05.01.09

  1.  

Дата возврата кредита

20.09.09

  1.  

Сумма кредита

$1 000 000

  1.  

Расчеты

  1.  

Срок кредита в днях

258

‘=В7-В6

  1.  

Срок кредита в годах

0,71

‘=В10/366

  1.  

Ставка для периода

11%

‘=В5*В11

  1.  

Сумма возврата

$1 106 027,4

‘=В8*(1+В5*В11)

3.2. Выполнение задания 2

3.2.1. Описание функции для вычисления будущего значения

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

=БС(Ставка; Кпер; Плт; ПС; Тип),

где

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

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

Плт (Промежуточные выплаты) – вводится, если имеются промежуточные выплаты;

ПС (приведенное или начальное значение) – размер кредита;

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

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

  

 3.2.2. Вызов функции вычисления будущего значения

    Активизируйте ячейку В16, затем щелкните по пиктограмме Мастер функций (Формулы – Вставить функцию). В окне Категория выберите Финансовые, а в окне Функция выберите БС (FV) и щелкните ОК (Далее).

    3.2.3. Ввод аргументов

  •  в поле Ставка введите В12;
  •  в поле Кпер введите 1;
  •  поле Плт  пропустите;
  •  в поле Пс (приведенная сумма или начальное значение)  введите В8.

    В результате в ячейке В16 появится такая же сумма, что и в ячейке В13, но со знаком минус.

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

 Результат вычислений

А

В

С

15

Задание 2. Расчет возврата ссуды с использованием функции БС

16

- $1 106 027,4

‘=БС(В12;1;;В8)

                                                                              

3.3. Выполнение задания 3

ПРИМЕР 2

В банк на 5 лет помещен вклад в размере 50 000 руб. под 12 % годовых с ежеквартальным начислением. Какую сумму получит вкладчик через 5 лет?

РЕШЕНИЕ

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

.

Число периодов равно числу кварталов за 5 лет, т.е.  5*4=20.

Значит, по формуле (2) получим

90 305,56 руб.

3.3.1. Ввести в строку 17 комментарий к примеру:

A

B

C

17

Задание 3. Расчет по схеме сложных процентов

18

90 305,56р.

=БС(12%/4;5*4;;-50000)

3.3.2. В ячейку В18 ввести формулу для вычисления наращенной суммы (аналогично пп. 3.2.2): =БС(12%/4;5*4;;-50000).

3.3.3. В ячейку С18 ввести ту же формулу с апострофом (комментарий).

3.4. Выполнение задания 4

Финансовые схемы с многократными взносами или выплатами называются Постоянными рентами.

ПРИМЕР 3

На счет в банке вносят сумму $ 20 000 в течение 10 лет равными долями в конце каждого года. Годовая ставка равна 22 %. Какая сумма будет на счете по истечении 10 лет?

РЕШЕНИЕ

3.4.1. Ввод исходных данных в таблицу

Введите в ячейки А20:В25 текст и исходные данные из табл. 11.

3.4.2. Использование функции БС и ввод аргументов

В ячейку В27 введите формулу для расчета накопленной суммы при взносах в конце периода. Для этого:

  •  активизируйте ячейку В27;
  •  щелкните по пиктограмме Мастер функций;
  •  выберите в списке категорий функций Финансовые;
  •  выберите в списке финансовых функций – функцию БС;
  •  в поле Ставка введите В25;
  •  в поле Кпер введите 10;
  •  в поле Плт  введите –2000 (знак минус означает, что деньги отдают);
  •  остальные поля в данном случае пропускаются;
  •  щелкните ОК.

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

  •  активизируйте ячейку В28;
  •  щелкните по пиктограмме Мастер функций;
  •  выберите в списке категорий функций Финансовые;
  •  выберите в списке финансовых функций – функцию БС;
  •  щелкните ОК;
  •  в поле Ставка  введите В25;
  •  в поле Кпер введите 10;
  •  в поле Плт введите –2000 (знак минус означает, что деньги отдают);
  •  поле Пс пропустите;
  •  в поле Тип введите 1, так как выплаты производят в начале периода;
  •  щелкните ОК.

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

                                                                                            

А

B

C

20

Задание 4. Расчет постоянной ренты с использованием функции БС

21

Исходные данные

22

Переменные

Вычисления

Формулы и комментарии

23

Срок

10

24

Сумма

$20 000

25

Годовая ставка

22%

26

Расчеты

27

Плата в конце периода

$57 314,83

‘=БС(В25;10;-2000)

28

Плата в начале периода

$69 924,09

‘=БС(В25;10;-2000;;1)

3.5. Выполнение задания 5

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

ПРИМЕР 4

Вексель на 4 млн долларов с процентной ставкой 18 % и начислением процентов дважды в год выдан на три года. Найти исходную сумму, выданную под этот вексель.

РЕШЕНИЕ

Для решения этой задачи в Excel используют функцию ПС (приведенная или современная сумма). Синтаксис этой функции

=ПС(Ставка; Кпер; Плт; БС; Тип).

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

3.5.1. Ввод исходных данных

Введите в ячейки А30:В36 текст и исходные данные из табл. 13.

3.5.2. Вычисление процентной ставки за период

В задании приводится годовая учетная ставка, а начисление процентов ведется дважды в год. Поэтому в ячейку В38 введите формулу, вычисляющую  процентную ставку за полгода =В33/В34.

3.5.3. Вычисление исходной суммы, выданной по векселю

  •  активизируйте ячейку В39;
  •  щелкните по пиктограмме Мастер функций;
  •  выберите в списке категорий функций Финансовые;
  •  выберите в списке финансовых функций функцию ПС;
  •  щелкните ОК;
  •  в поле Ставка введите В38;
  •  в поле Кпер введите В34*В36;
  •  поле Плт пропустите (промежуточных выплат нет);
  •  в поле БС введите В35;
  •  поле Тип пропустите;
  •  щелкните ОК.

                                                                                     Расчеты в Excel

А

В

С

30

Задание 5. Расчет платы за вексель с использованием функции ПС

31

Исходные данные

32

Переменные

Вычисления

Формулы и комментарии

33

Процентная ставка

18%

34

Периодичность выплат

2

35

Будущее значение

-$4 000 000

36

Количество лет

3

37

Расчеты

38

Процент за период

9%

‘=В33/В34

39

Современное значение

$2 385 069,31

‘=ПС(В38;В34*В36;;В35)

                                                                      Расчеты в Calc

А

В

С

30

Задание 5. Расчет платы за вексель с использованием функции PV

31

Исходные данные

32

Переменные

Вычисления

Формулы и комментарии

33

Процентная ставка

18%

34

Периодичность выплат

2

35

Будущее значение

-$4 000 000

36

Количество лет

3

37

Расчеты

38

Процент за период

9%

‘=В33/В34

39

Современное значение

$2 385 069,31

‘=PV(В38;В34*В36;;В35)

В результате в ячейке В39 появилось значение 2 385 069,32. Итак, под вексель 4 млн долларов можно получить сумму $2 385 069.

3.6. Выполнение задания 6

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

ПРИМЕР 5

За какой срок сумма, равная 80 рублям, достигает 300 000 рублей при начислении процентов по ставке 15 % раз в году и поквартально?

РЕШЕНИЕ

    Воспользуемся функцией КПЕР (количество периодов). Ее синтаксис:

=КПЕР(Ставка;Плт ; ПС; БС; Тип).

    Все аргументы этой функции известны из предыдущих заданий.

3.6.1. Ввод заголовка примера 5 (см. табл. 11).

    3.6.2. Определение числа периодов в годах при начислении процентов раз в году

  •  активизировать ячейку В42;
  •  щелкните по пиктограмме Мастер функций;
  •  выберите в списке категорий функций Финансовые;
  •  выберите в списке финансовых функций функцию КПЕР;
  •  щелкните ОК;
  •  в поле Ставка введите 15 %;
  •  в поле Плт введите 0 (или пропустите);
  •  в поле ПС введите –80 (знак минус – так как отдаем деньги);
  •  в поле БС введите 300000;
  •  поле Тип пропустите;
  •  щелкните ОК.

В результате вычислений период накопления заданной суммы составит 59 лет:

                                                                                               

А

В

С

41

Задание 6. Расчет срока вклада с использованием функции КПЕР

42

Начисление раз в год

59

‘=КПЕР(15%;0;-80;300000)

43

По кварталам

56

‘=КПЕР(15%/4;0;-80;300000)/4

3.6.3. Определение числа периодов в годах при начислении процентов поквартально

  •  активизируйте ячейку В43;
  •  щелкните по пиктограмме Мастер функций;
  •  выберите в списке категорий функций Финансовые;
  •  выберите в списке финансовых функций функцию КПЕР;
  •  щелкните ОК;
  •  в поле Ставка введите 15%/4 (начисление процентов производят четыре раза в год и за каждый квартал ставка в четыре раза меньше);
  •  в поле Плт введите 0 (или пропустите);
  •  в поле ПC введите –80 (знак минус – отдаем);
  •  в поле БC введите 300000;
  •  поле Тип пропустите;
  •  щелкните ОК.

В ячейке В43 введена формула =КПЕР(15%/4;0;-80;300000), которая вычисляет рассчитываемое число периодов  в кварталах, а нас интересует срок накоплений в годах.

    3.6.4. Редактирование формулы расчета числа периодов

  •  активизируйте ячейку В43;
  •  установите курсор в строке формул в конец выражения и после скобки наберите с клавиатуры /4 (см. табл. 11);
  •   нажмите Enter.

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

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

3.7. Выполнение задания 7

ПРИМЕР 6

В долг на 4 года предоставлена ссуда $ 4000 с условием возврата $ 6000. Определить процентную ставку ссуды.

РЕШЕНИЕ

Для расчета процентной ставки используется функция

=СТАВКА (Кпер; Плт; ПС; БС; Тип; Предположение).

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

3.7.1. В строку 45 ввести заголовок примера (см. табл. 11).

3.7.2. В ячейку В46 ввести формулу для расчета процентной ставки:

  •  ВставкаФункцияФинансовыеСтавкаОк;
  •  ввести аргументы согласно табл. 11
  •  .

3.7.3. В ячейку С46 ввести ту же формулу с апострофом для комментария.

В результате вычислений у нас сформирована табл. 11.

 

3.8. Самостоятельная работа

Рассчитайте значение вклада 5 000 долларов через 4 года при годовой процентной ставке 28 % с начислением процентов раз в полгода.

4. Отчет по работе

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

Литература: [6], с. 261-271.

 Таблица 11. Финансовые вычисления в Excel

А

В

С

  1.  

ФИНАНСОВЫЕ ВЫЧИСЛЕНИЯ  В Excel

  1.  

Задание 1. Расчет возвращаемой суммы при получении кредита

  1.  

Исходные данные

  1.  

Переменные

Вычисления

Формулы и комментарии

  1.  

Годовая ставка

15%

  1.  

Дата выдачи кредита

05.01.09

  1.  

Дата  кредита

20.09.09

  1.  

Сумма возврата

$1 000 000

  1.  

Расчеты

  1.  

Срок кредита в днях

258

‘=В7-В6

  1.  

Срок кредита в годах

0,71

‘=В10/366

  1.  

Ставка для периода

11%

‘=В5*В11

  1.  

Сумма возврата

$1 106 024.4

‘=В8*(1+В5*В11)

  1.  

  1.  

Задание 2. Расчет возврата ссуды с использование функции БС

  1.  

$1 106 024.4

‘=БС(В12;1;;В8)

  1.  

Задание 3. Расчет по схеме сложных процентов

  1.  

90305,56р.

‘=БС(12%/4;5*4;;-50000)

  1.  

  1.  

Задание 4. Расчет постоянной ренты с использованием функции БС

  1.  

Исходные данные

  1.  

Переменные

Вычисления

Формулы и комментарии

  1.  

Срок

10

  1.  

Сумма

$20 000

  1.  

Годовая ставка

22%

  1.  

Расчеты

  1.  

Плата в конце периода

$57314,83

‘=БС(В25;10;-2000)

  1.  

Плата в начале периода

$69924,09

‘=БС(В25;10;-2000;;1)

  1.  

  1.  

Задание 5. Расчет платы за вексель с использованием функции ПС

  1.  

Исходные данные

  1.  

Переменные

Вычисления

Формулы и комментарии

  1.  

Процентная ставка

18%

  1.  

Периодичность выплат

2

  1.  

Будущее значение

-$4 000000

  1.  

Количество лет

3

  1.  

Расчеты

  1.  

Процент за период

9%

‘=В33/В34

  1.  

Современное значение

$2 385 069,31

‘=ПС(В36;В34*В36;;В35)

  1.  

 Окончание табл. 13

А

В

С

  1.  

Задание 6. Расчет срока вклада с использованием функции КПЕР

  1.  

Начисление раз в год

59

‘=КПЕР(15%;0;-80;300000)

  1.  

По кварталам

56

‘=КПЕР(15%/4;0;-80;300000)/4

44

45

Задание 7. Расчет процентной ставки

46

11%

=СТАВКА(4;;4000;-6000)

Работа 5. ОЦЕНКА ИНВЕСТИЦИОННЫХ ПРОЕКТОВ

  1.  Цель работы

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

2. Основные теоретические положения

2.1. Основные понятия

Инвестиции – это вложение капитала в какой-то проект. При этом возврат денежных средств происходит неравномерно по объемам и по срокам.

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

  1.  FV  будущая стоимость инвестиции.

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

FV представляет собой сумму, которую мы получим через определенный срок (n), вложив определенную сумму (PV) под данный процент (i). FV рассчитывается по формуле:

FV=РV*(1+i)n ,      (1)

где РV – текущая стоимость инвестиции;

      n – срок, на который инвестируются деньги;

      i – процентная ставка.

Очевидно, что в Excel в этом случае можно использовать функцию БС, а в программе  Calc – функцию FV (см. работу 4).

2) PV  текущая стоимость инвестиции.

Например, мы хотим через 3 года получить 1000 рублей, положив деньги в банк под 10 %. РV представляет собой сумму, которую нам нужно инвестировать сегодня, чтобы получить 1000 рублей (FV) через 3 года (n) на указанных условиях (i = 10 %).

PV рассчитывается по формуле:

 ,      (2)

где FV – будущая стоимость инвестиции;

     n – срок, на который инвестируются деньги;

     i – процентная ставка.

В Excel для расчета этого параметра можно использовать функцию ПС, а в программе Calc – функцию PV (см. работу 4).

3) СС  цена капитала.

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

Параметры PV и FV можно использовать только в случае равномерного поступления денежных средств (по годам и по суммам).

2.2. Оценка инвестиционных проектов (ИП) в общем случае

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

1) Чистый приведенный доход (NPV net present value) рассчитывается по формуле:

,     (3)

где Pk – сумма денежных поступлений за период К;

     IC – сумма первоначального вложения;

     r – процентная ставка (коэффициент дисконтирования);

n  количество лет, в течение которых будут приходить денежные поступления.

Экономический смысл NPV:

Если  NPV > 0, то проект прибыльный;

 NPV < 0, то проект убыточный;

 NPV = 0, то проект ни прибыльный, ни убыточный.

2) Индекс рентабельности (РI) рассчитывается по формуле:

.    (4)

Этот параметр показывает, насколько велика сумма дохода по отношению к вложенным средствам.

Экономический смысл РI:

Если PI > 1, то проект рентабельный;

        PI < 1, то проект нерентабельный;

3) Внутренняя норма прибыли инвестиции (IRRinternal rate return) представляет собой коэффициент дисконтирования, при котором NPV = 0. Таким образом, IRR находится из уравнения:

.     (5)

Экономический смысл IRR:

Если IRR > СС, то проект следует принять;

        IRR < СС, то проект следует отвергнуть;

        IRR = СС, то проект ни прибыльный, ни убыточный.

4) Срок окупаемости инвестиций (РР) обычно рассчитывается прямым подсчетом числа лет, в течение которых поступающие денежные потоки превысят сумму первоначальных вложений. Общая формула расчета РР имеет вид:

РР = n, при котором .   (6)

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

Проще говоря, наиболее выгодным является проект, у которого первые три показателя (NPV, PI, IRR) наибольшие, а последний (РР) наименьший.

3. Порядок выполнения работы

Задание 1. Рассчитать будущую стоимость инвестиционных проектов.

Задание 2. Рассчитать текущую стоимость инвестиционных проектов.

Задание 3. Провести оценку наиболее выгодного инвестиционного проекта по четырем критериям с использованием специальных функций Excel.

3.1. Выполнение задания 1.

3.1.1. Запуск Microsoft Excel:

  •  щелкните по кнопке Пуск ПрограммыMicrosoft Excel.

3.1.2. Заполнение исходной таблицы:

  •  введите таблицу (табл. 12) на рабочий Лист1.

3.1.3. Расчет будущей стоимости проектов А и В:

  •  активизируйте ячейку В5. Выберите в главном меню Вставка – Функция, в списке категорий функций выберите Математические  в списке Функция – выберите СТЕПЕНЬ – нажмите ОК;
  •  на экране появилось окно ввода аргументов данной функции. Щелкните по полю Число и введите 1 + В6. Затем щелкните по полю Степень и введите В7нажмите ОК. В строке формул появилась запись =СТЕПЕНЬ(1+В6;В7)

Таблица 12. Оценка инвестиционных проектов

А

В

С

D

E

1

ОЦЕНКА ИНВЕСТИЦИОННЫХ ПРОЕКТОВ

2

Инвестиционный проект

3

А

В

С

D

4

Текущая стоимость инвестиции

2000

2000

5

Будущая стоимость данного вложения

4000

4000

6

Процентная ставка

15%

23%

10%

17%

7

Срок инвестиции

4

3

4

3

  •  установив курсор на ячейку В5, щелкните по строке формул, переведите курсор на конец формулы и введите *В4. В строке формул появится запись =СТЕПЕНЬ(1+В6;В7)*В4. Здесь В4 – текущая стоимость инвестиции, В6 – процентная ставка, В7 – срок, на который инвестируются средства. Нажмите клавишу Enter. В ячейке В5 появится будущая стоимость данного вложения: 3498,01;
  •  установите курсор на ячейку В5, нажмите кнопку Копировать, затем установите курсор на ячейку С5 и нажмите кнопку Вставить. В ячейке С5 появится будущая стоимость инвестиции В, равная 3721,73.

3.2. Выполнение задания 2.

3.2.1. Расчет текущей стоимости проектов С и D:

  •  установите курсор на ячейку D4 и выполните действия, описанные в пункте 3.1.3. В поле Число введите 1+D6, а в поле Степень введите D7, нажмите ОК. В строке формул появится запись =CTEПEHЬ(1+D6;D7);
  •  установите курсор на ячейку D4 и щелкните по строке формул, переведите курсор на начало формулы (после знака =) и введите D5/. В строке формул появится запись =D5/CTEПEHЬ(1+D6;D7). Здесь D5 – будущая стоимость инвестиции, D6 – процентная ставка, а D7 – срок инвестиции. Перейдите на конец формулы и нажмите Enter;
  •  в ячейке D4 появится текущее значение стоимости инвестиции 2732,05;
  •  установите курсор на ячейку D4, нажмите кнопку Копировать, затем установите курсор на ячейку Е4 и нажмите кнопку Вставить. В ячейке Е4 появится текущая стоимость инвестиции D 2497,48.

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

3.2.2. Установите курсор на ячейку В6 и введите с клавиатуры 20 %. Обратите внимание на то, как изменится значение будущей стоимости инвестиции (ячейка В5).

3.2.3. Ввод новых исходных данных в таблицу. Используйте рассмотренные функции для решения следующей задачи. Имеется сумма 1000 рублей (PV), которую можно вложить в банк А на 4 года под 15 % или в банк В на 3 года под 23 %. Выбрать наиболее оптимальный способ инвестирования.

  •  В ячейки В4 и С4 введите текущую стоимость инвестиции -1000.

Обратите внимание на полученные значения FV. Очевидно, что вложение в банк В более выгодно, так как через меньшее количество лет мы получаем большую сумму FVA (1749,01) < FVB ( 1860,87).

3.3. Выполнение задания 3.

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

В Excel Функция ЧПС (чистая приведенная стоимость) используется для оценки чистого приведенного дохода NPV и имеет синтаксис:

=ЧПС(Ставка; Значение),

В этой функции аргументы:

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

значение – адрес диапазона ячеек, в котором размещены значения поступающих денежных средств.

Обратите внимание! Функция ЧПС не учитывает размер начальных инвестиций.

Функция ВСД (внутренняя ставка доходности) используется в Excel для расчета внутренней доходности и имеет синтаксис:

=ВСД(Значение; Предположение).

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

Для расчета критериев оценки каждого из проектов:

3.3.1. Щелкните по ярлычку Лист2 и введите данные согласно табл. 13.

3.3.2. Произведите форматирование текстовых полей.

3.3.3. Расчет чистого приведенного дохода (NPV):

  •  установите курсор в ячейку В13. Щелкните кнопку Вставка функции – выберите Финансовые, в списке категорий выберите функцию ЧПС. Нажмите ОК; 
  •  в появившемся окне установите курсор в поле Ставка и щелкните по ячейке В11. Адрес этой ячейки появится в поле Ставка. Установите курсор в поле Значение 1 и выделите диапазон ячеек В6:В10. В строке формул появится запись =ЧПС(В11;В6:В10),  
  •  щелкните по строке формул, установите курсор на конец формулы и добавьте +В5. Формула примет вид: =ЧПС(В11;В6:В10)+В5. Нажмите Enter. В ячейке В13 появится значение NPV для данного денежного потока;

Таблица 13. Оценка инвестиционных проектов с использованием специальных функций

А

B

С

D

E

1

Денежный поток

2

3

Год

Инвестиционный проект

4

A

B

C

D

5

0

-1000

-1000

-1000

-1000

6

1

100

0

100

300

7

2

900

0

200

300

8

3

100

300

300

400

9

4

150

700

400

500

10

5

200

1300

1250

500

11

Норма (10 %)

0,1

12

Критерий оценки

13

Чистый приведенный доход (NPV)

14

Индекс рентабельности (PI)

15

Внутренняя норма прибыли (IRR)

16

Срок окупаемости (PP)

  •  установление абсолютной адресации для ячейки В11 (курсор в ячейке В13). Щелкните по строке формул и выделите В11. Нажмите один раз клавишу F4. В Excel формула примет вид: =ЧПС($В11;В6:В10)+В5;
  •  копирование формулы в остальные ячейки строки. Скопируйте формулу из ячейки В13 в диапазон ячеек С13:Е13.

3.3.4. Расчет индекса рентабельности (РI):

  •  установите курсор в ячейку В14 и выполните ввод функции ЧПС (см. пп. 3.3.3);
  •  для редактирования формулы установите курсор в ячейку В14, щелкните по строке формул, передвиньте курсор в ее конец и введите /-В5. В строке формул появится запись =ЧПС(В11;В6:В10)/-В5 в табличном процессоре Excel и =NPV(В11;В6:В10)/-В5 в программе Calc. Перед В5 ставится минус, чтобы конечное значение PI было положительным.

 Нажмите Enter. В ячейке В14 появится значение индекса рентабельности для данного денежного потока;

  •  установление абсолютной адресации для В11 (см. пп. 3.3). В строке формул появится запись: =ЧПС($В11;В6:В10)/-В5. Скопируйте формулу из ячейки В14 в диапазон ячеек С14:Е14.

3.3.5. Расчет внутренней нормы доходности (IRR):

  •  установите курсор в ячейку В15. Выберите в главном меню пункт Вставка – Функция – Финансовые – в списке Функция – найдите функцию ВСД – ОК.  
  •  установите курсор в поле Значения и выделите или введите диапазон ячеек В5:В10. Нажмите ОК. В строке формул появится запись =ВСД(В5:В10). В ячейке В15 появится значение внутренней нормы прибыли в процентах для данного денежного потока;
  •  скопируйте формулу из ячейки В15 в диапазон ячеек С15:Е15.

3.3.6. Расчет срока окупаемости (РР):

  •  в ячейку В16 введите номер года, в котором сумма денежных поступлений будет больше или равна сумме первоначальной инвестиции (IC). Для инвестиции А складывайте в уме значения в ячейках от В6 до В10 до тех пор, пока полученная сумма не превысит 1000. Таким образом, для инвестиции А в ячейку В16 вводим 2, для инвестиции В в ячейку С16 – число 4, для инвестиции С в ячейку D16 также 4, для инвестиции D в ячейку Е16 – число 3.

3.3.7. Выбор наиболее выгодного инвестиционного проекта.

На основе информации об экономическом значении каждого из рассчитанных критериев определите наиболее выгодный инвестиционный проект (в данном случае, с наибольшими значениями NPV, PI и IRR) и выделите ячейку с его названием (проект D, ячейка Е4) красным цветом, щелкнув мышью по стрелке справа от кнопки Цвет заливки и выбрав квадрат с соответствующим цветом.

3.4. Самостоятельная работа

3.4.1. Создайте на новом рабочем листе  таблицу с исходными данными (табл. 14).

3.4.2. Рассчитайте NPV, IRR, PI и РР для исходных данных (по каждому из трех проектов).

3.4.3. Продемонстрируйте работу преподавателю.

3.4.4. Завершите работу программы,  сохранив документ в свою папку.

                        Таблица 14. Задание для самостоятельной работы

Годы

Инвестиционный проект

А

В

С

0

-100

-100

-100

1

80

90

20

2

20

0

30

3

30

5

50

4

10

10

20

4. Отчет по работе

Распечатки таблиц

Литература: [4], с. 278-284.

Работа 6. СОЗДАНИЕ ПРОСТЕЙШИХ МАКРОСОВ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

1. Цель работы

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

  1.  Основные теоретические положения

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

3. Порядок выполнения работы

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

Задание 2. Создать макрокоманду, набирающую и форматирующую заголовки.

Задание 3. Использовать созданные макросы для вставки заголовка в таблицу.

Задание 4. Провести закрепление макросов за различными элементами.

3.1. Выполнение задания 1

Создание первого макроса – Вставка строк.

  1.  Создайте новый документ: Пуск – Программы – Microsoft Excel. Открылась рабочая книга с чистым рабочим листом.

3.1.2. Выберите команды меню Сервис – Макрос – Начать запись.

3.1.3. В диалоговом окне Запись макроса введите имя первого макроса Вставка_строк, в поле Сочетание клавиш – введите N (латинский алфавит), в поле Сохранить в введите Эта книга, в поле Описание введите Макрос вставляет строки. Нажмите ОК.

Далее приступим к написанию макроса:

3.1.4. Выделите первую строку. Для этого щелкните мышью по номеру строки 1.

3.1.5. Выполните команды меню Вставка Строки, снимите выделение.

3.1.6. Еще раз выделите первую строку и повторите команды меню Вставка – Строки, снимите выделение.

3.1.7. Повторите действия еще два раза (вставьте еще две строки – всего четыре).

3.1.8. Выполните команды СервисМакросОстановить запись. Макрос записан.

3.1.9. Проверьте его работу. Для этого:

  •  введите в ячейку А1 слово «Проверка»;
    •  оставьте ячейку А1 активной;
    •  выполните команды СервисМакросМакросыВставка_СтрокВыполнить.

3.2. Выполнение задания 2

Создание второго макроса – Создание_заголовка.

3.2.1. Повторите все операции по созданию макроса (см. пп. 3.1.2). Введите имя второго макроса – Создание_заголовка, Сочетание клавишМ (латинский алфавит), в поле Описание введите Макрос вставляет заголовок.

Далее приступим к написанию макроса.

  1.  Выделите диапазон ячеек А1:G1 и щелкните по кнопке Объединить и поместить по центру, установите начертание – Полужирный, размер – 12. Введите текст: Докладная записка. Снимите выделение.

Выделите диапазон ячеек А2:G2 и повторите действия форматирования. Введите текст: Учет квартальных продаж.

Выделите диапазон ячеек А3:G3 и повторите действия форматирования. Введите текст: ООО “Здоровье”.

Аналогично произведите форматирование четвертой строки. Введите текст: Коммерческий директор Иванов И.И.

  1.  Щелкните по кнопке Остановить запись. Макрос записан.

3.3. Использование макросов

3.3.1. Создать таблицу Продажи (табл. 18).

Таблица 18. Исходная таблица для работы с макросами

№ п/п

Товар

Цена

1-й кварт.

2-й кварт.

3-й кварт.

4-й

кварт.

1

Бальзам "Абу-Симбел"

120

20

15

95

66

2

Бальзам "Демидов"

153

45

150

32

50

3

Бальзам "Русский"

200

82

65

45

47

4

Бальзам "Сто трав"

95

23

45

66

82

5

Витамин В12

45

100

89

23

45

6

Витамин С

67

123

160

95

66

7

Гель "Для ванны"

90

200

26

98

69

8

Гель "Здоровье"

80

45

47

450

230

9

Крем "Бальзам"

25

32

50

82

65

10

Крем "Бальзам"

25

32

26

45

150

11

Крем "Елена"

29

87

160

32

50

12

Поливитамины

25

450

230

123

160

13

Рыбий жир

40

98

69

200

26

14

Соль "Беби"

100

65

80

450

230

15

Соль "Хвойная"

70

95

66

82

65

3.3.2. Активизируйте ячейку А1. Выполните команды меню Сервис –Макрос – Макросы. Из диалогового окна выберите имя первого макроса Вставка_строк, нажмите кнопку Выполнить. На листе появились четыре новые строки

3.3.3. Активизируйте ячейку А1 и запустите второй макрос Создание_заголовка другим способом: нажмите клавиши <Ctrl>+<М>. Заголовок вставлен.

3.4. Закрепление макроса за различными элементами

  1.  Скопируйте и вставьте табл. 18 без заголовка на новый лист.

3.4.2. Выполните команды меню Сервис – Настройка. На вкладке Команды выберите категорию Макросы. Из списка Команды перетащите мышью Настраиваемую кнопку на панель инструментов. В диалоговом окне Назначить макрос выберите имя первого макроса, нажмите ОК. Закройте окно. Выполните макрос, щелчком по новой пиктограмме предварительно активизировав ячейку А1.

3.4.3. Создайте графический объект в виде прямоугольника с помощью панели инструментов Рисование (Вид Панель инструментов Рисование) для управления вторым макросом. Для этого:

  •  выберите место на листе для кнопки и щелкните по инструменту Прямоугольник;
    •  перемещая мышь, нарисуйте прямоугольник;
    •  щелкните правой кнопкой мыши по контуру прямоугольника и выберите команду контекстного меню Назначить макрос;
    •  введите в поле Имя макроса имя второго макроса и нажмите ОК
    •  выполните макрос, активизировав предварительно ячейку А1.

3.4.4. Создайте кнопочную форму запуска макроса. Для этого:

  •  выведите на экран панель инструментов Формы (команды Вид Панели инструментов Формы;
  •  перенесите с помощью мыши инструмент Кнопка на рабочее поле листа;
  •  в диалоговом окне Назначить макрос объекту введите в поле Имя макроса имя первого или второго макроса. Если окно не открылось, щелкните по кнопке правой клавишей мыши и выберите эту команду в контекстном меню;
  •  закройте Формы. Выполните макрос, активизировав ячейку А1.

3.2. Выполнение задания 2

Создание второго макроса – Создание_заголовка.

  1.  Выполните команды Сервис – Макрос – Записать макрос.

Появится окно Записать макрос. Приступим к записи макроса.

  1.  Выделите диапазон ячеек А1:G1 и щелкните по кнопке Объединить и

поместить по центру, установите начертание – Полужирный, размер – 12. Введите текст: Докладная записка. Снимите выделение.

Выделите диапазон ячеек А2:G2 и повторите действия форматирования. Введите текст: Учет квартальных продаж.

Выделите диапазон ячеек А3:G3 и повторите действия форматирования. Введите текст: ООО “Здоровье”.

Аналогично произведите форматирование четвертой строки. Введите текст: Коммерческий директор Иванов И.И.

  1.  Щелкните по кнопке Завершить запись в окне Запись макроса. Появится окно Макрос OpenOffice.org Basic.

    3.2.4. Чтобы сохранить макрос, сначала в списке Сохранить макрос в поставьте курсор на название файла, в котором вы работаете, далее в текстовом окне Название макроса введите имя sozdanie_zagolovka и нажмите на кнопку Записать.

  1.  Для закрепления за макросом «горячих клавиш» выполним команды

п. 3.1.9, только при сочетании клавиш Ctrl+М, в окне Функция вводим имя макроса sozdanie_zagolovka.

3.3. Использование макросов

3.3.1. Создайте таблицу Продажи (табл. 18).

    3.3.2. Выполним команды Сервис – Макрос – Выполнить макрос. В появившемся окне щелкнем по + напротив имени файла, в котором вы работаете, далее щелкнем по + напротив Standart и поставим курсор на Module 1. В правом окне Имя макроса поставим курсор на vstavka_stroc, нажмем на кнопку Выполнить.).

3.3.3. Активизируйте ячейку А1 и запустите второй макрос sozdanie_zagolovka другим способом: нажмите клавиши <Ctrl>+<М>. Заголовок вставлен.

3.4. Закрепление макроса за различными элементами

  1.  Скопируйте и вставьте табл. 18 без заголовка на новый лист.

3.4.2. Для закрепления макроса за пиктограммой:

  •  выполним команды Сервис – Настройка – вкладка Панели

инструментов;

  •  из списка в окне Панель инструментов выберем Стандартная (стоит

по умолчанию), начать на кнопку Добавить;

  •  в появившемся окне Добавить команды в окне списка Категория

выбрать Макрос OpenOffice.org, и, как в п. 3.1.9, раскрыв соответствующие вложенные папки (<Имя файла> - Standart),ставим курсор на имя папки Module 1;

  •   в окне Команды ставим курсор на имя нужного макроса, щелкнем по

кнопке Добавить, Закрыть;

  •  в окне Настройка в поле Содержимое панели инструментов 

появился заголовок макроса. В окне Настройка (курсор стоит на названии вашего макроса), щелкнем по кнопке Изменить;

  •   в открывшемся меню выберем команду Выбрать значок, в окне

Выбор значка выберем любой значок, Ок;

  •   Еще раз щелкнем по Изменить, выберем команду Переименовать;
  •   В появившемся окне Переименовать панель в текстовом окне Новое

название удалим имя макроса, Ок;

  •   Нажмем кнопку Ок в окне Настройка. На панели инструментов

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

3.4.3. Создадим графический объект в виде прямоугольника с помощью панели инструментов Рисование (Вид Панель инструментов Рисование) для управления вторым макросом. Для этого:

  •  выберем место на листе для кнопки и щелкнем по инструменту Прямоугольник;
  •  перемещая мышь, нарисуем прямоугольник;
  •  в поле Макрос раскроем папку Module 1, находящуюся в папке вашего файла;
  •   в поле Существующие макросы в: курсором мыши выберем имя макроса, далее нажмем кнопку Назначить, Ок;
  •  выполним макрос, активизировав предварительно ячейку А1.

4. Отчет по работе

Опишите порядок создания макроса, закрепления за макросом кнопки, создания графического объекта.

Литература: [2], c. 49-54.

Работа 7. РЕШЕНИЕ ЗАДАЧ ПРОГНОЗИРОВАНИЯ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

1. Цель работы

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

2. Основные теоретические положения

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

3. Порядок выполнения работы

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

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

 Задание 2. Создать таблицы данных при изменении одной и двух величин, пересчитать таблицу данных при новых условиях.

Задание 3. Создать таблицу данных при одновременном изменении двух величин.

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

 3.1. Выполнения задания 1

3.1.1. Создание таблицы:

  •  откройте новую таблицу;
  •  в ячейку А1 введите заголовок: ЗАКУПКА, в ячейку D1 введите заголовок: РЕАЛИЗАЦИЯ, установите в первой строке Размер16, Полужирный;
  •  в ячейки А2, А3 и А4 введите соответственно наименования: Количество, Цена, Стоимость;
  •  в ячейку В2 введите число 30, в Е2 – 0,15;
  •  активизируйте ячейку В3. В Строке формул щелкните мышью на знак равенства – строка готова к вводу формулы. Введите формулы согласно табл. 19 и убедитесь, что в этих ячейках появились результаты вычислений;
  •  в ячейки D2, D3, D4, D5 и D7 соответственно введите наименования: Наценка, Доход, Расход, Зарплата, Прибыль (см. табл. 20);
  •  установите обрамление двойной линией нижней границы первой строки и осуществите автоподбор ширины столбцов А и D (выделить А и D; Формат – Столбцы – Автоподбор ширины).

       

    Таблица 19. Исходные данные

Ячейка

Формула

Результат

В3

=10000*В2/(В2-5)

12000

В4

=В2*В3

360000

Е3

=В4*Е2

54000

Е4

=В4/(150-В2)

3000

Е5

=1000*В2

30000

Затем щелкните кнопку ОК. В поле имен адрес заменится именем ячейки.

  •  аналогичным образом ячейкам Е4 и Е5 присвойте имена Расход, Зарплата соответственно.

3.1.3. Ввод формул с использованием имен ячеек:

  •  активизируйте ячейку Е7 и наберите в ней знак равенства;
  •  щелкните кнопкой мыши по ячейке Е3 с именем Доход, и название этой ячейки скопируется в ячейку Е7.  наберите знак минус;
  •  щелкните кнопкой мыши по ячейке Е4 с именем Расход. Имя скопируется в ячейку Е7наберите знак минус;
  •  щелкните кнопкой мыши по ячейке Е5 с именем Зарплата. Имя скопируется в ячейку Е7. нажмите кнопку клавиатуры Enter. В ячейке Е7 появится результат 21000.

        Щелкните в ячейке Е7 и убедитесь, что в Строке формул формула содержит не адреса, а имена ячеек (=Доход–РасходЗарплата). Этим обеспечивается использование в формуле абсолютной адресации ячеек (при копировании адреса ячеек не меняются). В результате получим табл. 20 (режим показа формул) или табл. 21 (режим показа вычислений);

  •  для проверки правильности решения задачи введите в ячейку В2 число 50. Программа произведет пересчет по формулам, и в ячейке Е7 появится результат 27777,78… . Таким образом, можно прогнозировать результат при изменении любого параметра, входящего в расчеты.

3.1.4. Форматирование таблицы:

  •  в ячейках В3, В4, Е3, Е4, Е5, Е7 установите формат числа Денежный, а в ячейке Е2 – Процентный (Формат – Ячейки – Число);
  •  установите ширину столбцов В и Е – 12.

Таблица 20. Таблица расходов и реализации в режиме показа формул

 

A

B

C

D

E

1

ЗАКУПКА

 

РЕАЛИЗАЦИЯ

2

Количество

30

 

Наценка

0,15

3

Цена

=10000*B2/(B2-5)

 

Доход

=B4*E2

4

Стоимость

=B2*B3

 

Расход

=B4/(150-B2)

5

 

 

 

Зарплата

=1000*B2

6

7

 

 

 

Прибыль

=Доход-Расход-Зарплата

         Таблица 21.Таблица расходов и реализации в режиме показа вычислений

 

A

B

C

D

E

1

ЗАКУПКА

 

 

РЕАЛИЗАЦИЯ

2

Количество

30

 

Наценка

15%

3

Цена

     12 000р.

 

Доход

 54 000,00р.

4

Стоимость

   360 000р.

 

Расход

 3 000,00р.

5

 

 

 

Зарплата

 30 000,00р.

6

 

 

 

 

 

7

 

 

 

Прибыль

 21 000,00р.

3.2. Выполнение задания 2

3.2.1. В ячейки А10 и А11 введите числа 10 и 20 соответственно.

3.2.2. При помощи автозаполнения введите в ячейки А12:А19 последовательность чисел от 30 до 100 с шагом 10. Для этого:

  •  выделите диапазон ячеек А10:А11;
  •   установите курсор мыши в правом нижнем углу выделенного блока (курсор примет вид черного крестика);
  •   нажмите левую кнопку мыши и, не отпуская ее, протяните вниз до ячейки А19.

3.2.3. В ячейку В9 скопируйте формулу из ячейки Е7.

  1.  При работе в Excel выделите диапазон ячеек А9:В19 и исполните

команды Данные – Таблица подстановки (Данные – Таблица данных).  В окне Таблицы подстановки ввести: Подставить значения по строкам $B$2, щелкните ОК;

   В ячейках В10:В19 появятся значения величины прибыли в зависимости от количества проданных изделий (табл. 22). Таким образом, можно прогнозировать результат в наглядном табличном виде при изменении одного параметра.

3.2.5. Проверьте правильность ваших действий: для 30 изделий прибыль (табл. 21) должна составлять 21 000,00… (сравните с табл. 21). При ошибке всю таблицу данных необходимо очистить, выделив ее и выполнив команду Очистить содержимое контекстного меню (щелкнуть правой кнопкой мыши), и повторить действия заново.

3.2.6. Оформите таблицу. Активизируйте ячейку А8, наберите =А2 и нажмите <Enter> (текст из А2 запишется в А8). Аналогичным образом запишите в В8 текст из D7. Сделайте обрамление более толстой линией, а внутренние линии тонкими. Измените цвет шрифта.

3.3. Выполнение задания 3

3.3.1. В ячейку D9 скопируйте формулу из Е7. В программе OpenOffice.org Calc формулу из Е7 скопируйте в ячейку D8, ячейка D9 должна быть пустой!!.

3.3.2. В ячейки D10:D19 скопируйте данные из А10:А19.

3.3.3. В ячейки Е9 и F9 введите числа 0,1 и 0,15 соответственно, установите в них формат числа – Процентный и при помощи автозаполнения заполните ячейки G9:K9 значениями наценки до 40 % с шагом 5 % (аналогично п 3.2.2).

Таблица 22. Результат работы таблицы подстановки

 

A

B

C

D

E

1

Закупка

 

 

Реализация

 

2

Количество

50

 

Наценка

15%

3

Цена

     11 111,11р.

 

Доход

   83 333,33р.

4

Стоимость

   555 555,56р.

 

Расход

     5 555,56р.

5

 

 

 

Зарплата

   50 000,00р.

6

 

 

 

 

 

7

 

 

 

Прибыль

   27 777,78р.

8

 

 

 

 

 

9

 

27 777,78р.

 

 

 

10

10

18 571,43р.

 

 

 

11

20

17 948,72р.

 

 

 

12

30

21 000,00р.

 

 

 

13

40

24 415,58р.

 

 

 

14

50

27 777,78р.

 

 

 

15

60

30 909,09р.

 

 

 

16

70

33 653,85р.

 

 

 

17

80

35 809,52р.

 

 

 

18

90

37 058,82р.

 

 

 

19

100

36 842,11р.

 

 

 

3.3.4. Выделите диапазон ячеек D9:К19 и исполните команды Данные - Таблица подстановки.

3.3.5. В диалоговом окне ввести

В программе Ехсе1:

Подставить значения по столбцам  $E$2;

Подставить значения по строкам   $B$2;

     Нажмите ОК.

3.3.6. Установите денежный формат в ячейках Е10:К19.  

Таким образом, результат можно прогнозировать в наглядном табличном виде при изменении двух параметров. В ячейках Е10:К19 появятся значения величины прибыли в зависимости от количества проданных изделий и величины наценки (табл. 23). Проверьте правильность ваших действий: для 50 изделий при наценке 30 % прибыль должна составлять 111 111,1… (табл. 23). При ошибке всю таблицу данных необходимо очистить и повторить действия заново.

3.3.7. Установите обрамление в ячейках последней созданной таблицы.

Таблица 23. Результат подбора двух параметров одновременно

D

E

F

G

H

I

J

K

9

30 909,09р

10%

15%

20%

25%

30%

35%

40%

10

10

8 571р.

18 571р.

28 571р.

38 571р.

48 571р.

58 571р.

68 571р.

11

20

4 615р.

17 949р.

31 282р.

44 615р.

57 949р.

71 282р.

84 615р.

12

30

3 000р.

21 000р.

39 000р.

57 000р.

75 000р.

93 000р.

111 000р.

13

40

1 558р.

24 416р.

47 273р.

70 130р.

92 987р.

115 844р.

138 701р.

14

50

0р.

27 778р.

55 556р.

83 333р.

111 111р.

138 889р.

166 667р.

15

60

-1 818р.

30 909р.

63 636р.

96 364р.

129 091р.

161 818р.

194 545р.

16

70

-4 038р.

33 654р.

71 346р.

109 038р.

146 731р.

184 423р.

222 115р.

17

80

-6 857р.

35 810р.

78 476р.

121 143р.

163 810р.

206 476р.

249 143р.

18

90

-10 588р.

37 059р.

84 706р.

132 353р.

180 000р.

227 647р.

275 294р.

19

100

-15 789р.

36 842р.

89 474р.

142 105р.

194 737р.

247 368р.

300 000р.

3.3.8. Пересчет таблицы данных:

а) активизируйте ячейку D9 (В программе OpenOffice.org Calc активизируйте ячейку D8), щелкните в Строке формул и наберите в конце формулы +1. Завершите ввод формулы и убедитесь, что произошел автоматический пересчет таблицы (например, данные в строке 12 увеличились на 1);

б) в ячейке К9 измените 40 % на 50 %. После ввода нового значения произойдет автоматический пересчет  таблицы данных в столбце К.

3.4. Выполнение задания 4

3.4.1. В ячейку Е2 введите величину наценки 20 %.

3.4.2. Для данной величины наценки необходимо определить количество изделий, обеспечивающее прибыль 50 000 р. Для этого активизируйте ячейку Е7 и выполните команды СервисПодбор параметра. При этом в диалоговом окне Подбор параметра в поле  Установить в ячейке (в программе Calc – в поле Яч. с формулой) будет введен абсолютный адрес ячейки $Е$7.

3.4.3. В поле Значение (в программе Calc – в поле Целевое значение) – введите число 50 000. В поле  Изменяя значение в ячейке (в программе Calc – в поле Изменяемая яч.)  щелкните по ячейке В2,будет введен ее абсолютный адрес – $B$2. Щелкните кнопкой ОК.

3.4.4. В окне Результат подбора параметра щелкните ОК. Проверьте правильность решения: в ячейке В2 количество изделий равно 43.

3.5. Самостоятельная работа

3.5.1. Введите дополнения в табл. 22, показанные в табл. 24:

Таблица 24.Ввод дополнений в табл. 22

Ячейка

Содержимое ячейки

Формат ячейки

А6

Ставка налога

Текст

В6

1000

Денежный

D6

Налог

Текст

Е6

=В6*(1+В4/100000)

Дать имя Налог, Денежный

Е7

Дополните в формулу вычитание  Налог

Денежный

В результате получим табл. 25.

                               Таблица 25. Таблица закупок и реализации с учетом налога

A

B

C

D

E

1

Закупка

 

 

Реализация

 

2

Количество

50

 

Наценка

20%

3

Цена

     11 111р.

 

Доход

 111 111,11р.

4

Стоимость

   555 556р.

 

Расход

     5 555,56р.

5

 

 

 

Зарплата

   50 000,00р.

6

Ставка
налога

1000

 

Налог

         6 556р.

7

 

 

 

Прибыль

   49 000,00р.

3.5.2. При количестве изделий, равном 50, и наценке 20 % определите ставку налога, обеспечивающую прибыль 30 000 р. (В ячейке В6 появится результат 3898).

3.5.3. Продемонстрируйте работу преподавателю. Сохраните документ в своей папке, завершите работу приложения.

4. Отчет по работе

      Распечатка полученных результатов в режиме счета и формул.

Литература: [3], c. 56-62.

Работа 8. АНАЛИЗ ФИНАНСОВОЙ ДЕЯТЕЛЬНОСТИ ПРИ ПОЛУЧЕНИИ КРЕДИТА

1. Цель работы

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

  1.  Основные теоретические положения

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

3. Порядок выполнения работы

Задание. Создать таблицу для проведения анализа возможности получения прибыли в зависимости от срока начала выплаты процентов. Рассматривается случай займа 1 000 $ США у частного лица под 10 % в месяц на два года для создания собственного дела.

Выполнение задания

3.1. Создание таблицы расчета прибыли при ежемесячной выплате процентов

        3.1.1. Откройте новую рабочую книгу и введите текстовые заголовки из табл. 26.

                  Таблица 26. Заголовки таблицы

Ячейка

Содержимое ячейки

А1

Сумма кредита

А2

Проценты по кредиту в месяц

А3

Прибыль в месяц

А5

Начало выплаты

А6

Число выплат

А7

Сумма

А9

Месяцы

В9

Сумма в конце месяца

С9

Ежемесячные выплаты

D9

Сумма в обороте

 

3.1.5. В ячейки С2 и С3 введите 0,1 и 0,2 соответственно и установите в них формат Процентный.

3.1.6. Щелкните мышью в ячейке С1, затем по инструменту Формат по образцу на панели Стандартная (метелка), выделите диапазон ячеек В10:D13. На все выделенные ячейки скопируется формат $0,00.

3.1.7. При работе с Excel введите в ячейки формулы и числа в соответствии с табл. 27. При работе с программой Calc формулы вводятся в соответствии с табл. 28. 

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

 Как видно из сравнения табл. 27 и табл. 28, в программе OpenOffice.org Calc формула вычисления адреса ссылки в ячейке D7 имеет вид:  =ADDRESS(9+$C$5;2), формула двойной ссылки в ячейке С7 имеет вид:   =INDIRECT(D7), а для расчета ежемесячных выплат используем функцию IF, тое есть формула в ячейке С11 имеет вид  =IF($C$5>A11;0;$C$7*$C$2).

                                                 Таблица 27. Ввод формул при работе с Excel

Ячейка

Содержимое ячейки

Формат ячейки

С1

1000

Денежный $ 0,00

C2

0,1

Процентный

С3

0,2

Процентный

C5

2

Числовой

C6

=24C5+1

Числовой

D7

=АДРЕС(9+$C$5;2)

Денежный $ 0,00

C7

=ДВССЫЛ(D7)

Денежный $ 0,00

A10

0

Числовой

A11

1

Числовой

A12

2

Числовой

B10

=C1

Денежный $ 0,00

B11

=C1+C1*C2+C11

Денежный $ 0,00

C11

=ЕСЛИ($C$5>A11;0;$C$7*$C$2)

Денежный $ 0,00

D11

=$C$1+$C$1*$C$3+C11

Денежный $ 0,00

B12

=B11+B11*$C$2+C12

Денежный $ 0,00

C12

=ЕСЛИ($C$5>A12;0;$C$7*$C$2)

Денежный $ 0,00

D12

=D11+D11*$C$3+C12

Денежный $ 0,00

А13

3

Числовой

B13

=B12+B12*$C$2+C13

Денежный $ 0,00

C13

=ЕСЛИ($C$5>A13;0;$C$7*$C$2)

Денежный $ 0,00

D13

=D12+D12*$C$3+C13

Денежный $ 0,00

3.1.8. Выделите диапазон ячеек А12:А13. Подведите указатель мыши в правый угол рамки, обрамляющей выделение. Указатель примет вид черного крестика. Нажмите левую кнопку мыши и растяните рамку до 34-й строки включительно. Ячейки автоматически заполнятся значениями формул для 24 месяцев.

3.1.9. В ячейку С36 введите слово «Прибыль». В ячейку D36 введите формулу =D34-В34. Должен получиться результат $ 42 510,79. Такова прибыль, если начнем выплачивать проценты со второго месяца и вернем кредит в конце срока. Таблица в режиме показа формул для табличного процессора Excel представлена в табл. 29, для программы Calc – в табл. 30. Результат в режиме показа вычислений приведен в табл. 31.

3.1.10. Измените значение в ячейке С5 на 5, получится $ 50 699,76 прибыли, если начнем выплачивать проценты с пятого месяца и вернем кредит в конце срока.

3.1.11. Измените значение в ячейке С5 на 24, получится $ 69 647,11 прибыли, если выплатим проценты и вернем кредит в конце срока.

                               Таблица 28. Ввод формул при работе с программой Calc

Ячейка

Содержимое ячейки

Формат ячейки

С1

1000

Денежный $ 0,00

C2

0,1

Процентный

С3

0,2

Процентный

C5

2

Числовой

C6

=24C5+1

Числовой

D7

=ADDRESS(9+$C$5;2)

Денежный $ 0,00

C7

=INDIRECT(D7)

Денежный $ 0,00

A10

0

Числовой

A11

1

Числовой

A12

2

Числовой

B10

=C1

Денежный $ 0,00

B11

=C1+C1*C2+C11

Денежный $ 0,00

C11

=IF($C$5>A11;0;$C$7*$C$2)

Денежный $ 0,00

D11

=$C$1+$C$1*$C$3+C11

Денежный $ 0,00

B12

=B11+B11*$C$2+C12

Денежный $ 0,00

C12

=IF($C$5>A12;0;$C$7*$C$2)

Денежный $ 0,00

D12

=D11+D11*$C$3+C12

Денежный $ 0,00

А13

3

Числовой

B13

=B12+B12*$C$2+C13

Денежный $ 0,00

C13

=IF($C$5>A13;0;$C$7*$C$2)

Денежный $ 0,00

D13

=D12+D12*$C$3+C13

Денежный $ 0,00

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

3.2.1. Скопируйте всю таблицу на Лист2.

3.2.2. Для расчета ежемесячных выплат процентов и ежемесячного погашения долга используется функция ПЛТ  (в программе OpenOffice.org Calc  она называется  PMT). Ее синтаксис:

=ПЛТ(Ставка; Кпер; Пс; Бс; Тип).

В данном случае ставка равна 10%,  Кпер соответствует числу выплат, Пс – сумма кредита ($1 000). Поскольку долг должен быть погашен к концу срока кредита, Бс = о, мы ее опускаем; ежемесячные выплаты производятся в конце месяцы, поэтому Тип = 0, его тоже можно опустить.  

Отредактируйте в ячейке С11 формулу. В Excel она должна иметь вид

=ЕСЛИ($C$5>A11;0;ПЛТ($C$2;$C$6;$C$7)).

В программе OpenOffice.org Calc  в ячейку С11 вводим формулу

= IF($C$5>A11;0;PMT($C$2;$C$6;$C$7)).



  •  
  •  
  •  
  •  
  •  



Таблица 31. Итоговая таблица. Показ вычислений

A

B

C

D

1

Сумма кредита

$1 000,00

2

Проценты по кредиту в месяц

10%

3

Прибыль в месяц

20%

4

5

Начало выплаты

2

6

Число выплат

23

7

Сумма

1100

$B$11

8

9

Месяцы

Сумма в конце месяца

Ежемесячные выплаты

Сумма в обороте

10

0

$1 000,00

11

1

$1 100,00

$0,00

$1 200,00

12

2

$1 100,00

$110,00

$1 330,00

13

3

$1 100,00

$110,00

$1 486,00

14

4

$1 100,00

$110,00

$1 673,20

15

5

$1 100,00

$110,00

$1 897,84

16

6

$1 100,00

$110,00

$2 167,41

17

7

$1 100,00

$110,00

$2 490,89

18

8

$1 100,00

$110,00

$2 879,07

19

9

$1 100,00

$110,00

$3 344,88

20

10

$1 100,00

$110,00

$3 903,86

21

11

$1 100,00

$110,00

$4 574,63

22

12

$1 100,00

$110,00

$5 379,55

23

13

$1 100,00

$110,00

$6 345,47

24

14

$1 100,00

$110,00

$7 504,56

25

15

$1 100,00

$110,00

$8 895,47

26

16

$1 100,00

$110,00

$10 564,56

27

17

$1 100,00

$110,00

$12 567,48

28

18

$1 100,00

$110,00

$14 970,97

29

19

$1 100,00

$110,00

$17 855,17

30

20

$1 100,00

$110,00

$21 316,20

31

21

$1 100,00

$110,00

$25 469,44

32

22

$1 100,00

$110,00

$30 453,33

33

23

$1 100,00

$110,00

$36 433,99

34

24

$1 100,00

$110,00

$43 610,79

35

36

Прибыль

$42 510,79

3.2.3. Скопируйте отредактированную формулу из ячейки С11 на диапазон ячеек С12:С34. Получится прибыль $ 69 67,11, так как в ячейке С5 осталось значение 24.

3.2.4. Измените значение в ячейке С5 на 5. Получится, что при начале погашения кредита на пятый месяц прибыль составит $ 47 391,62. Очевидно, что чем позже вы возвращаете деньги, тем большую выгоду вы получаете от кредита.

3.3. Определение суммы, которую надо положить на депозит для получения через пять лет суммы в 100 000 р.

ПРИМЕР

Семья собирается накопить за 5 лет 100 000 руб., поместив в банк некоторую сумму под 10 % годовых. Известно, что каждый год нужно вносить 12 000 руб. Какую сумму следует вложить вначале?

Используйте функцию Подбор параметра.

3.3.1. Активизируйте Лист3 и переименуйте его в Сумма вклада.

3.3.2. Введите текст из табл. 32.

               Таблица 32.Исходные данные

Ячейка

Содержимое

А1

Сумма вклада

А2

Годовая ставка, %

А3

Ежегодные выплаты

А4

Срок вклада

А5

Будущее значение

3.3.5. В ячейку С1 введите число 10 000 р. Знак минус означает, что эти деньги надо отдавать, а не получать (т. е. мы попробуем вложить 10000, чтобы получить 100 000 руб). Эта сумма и будет подбираться.

3.3.6. Установите в ячейке С2 Процентный формат и введите в нее число 10. Это годовая процентная ставка, которая не меняется в течение 5 лет.

3.3.7. Введите в ячейку С3 число -12 000. Здесь находится сумма, которую вы вкладываете (т.е. отдаете, поэтому со знаком минус) по истечении каждого года.

3.3.8. Введите в ячейку С4 число 5. Это число лет, на которое размещается депозит.

3.3.9. С помощью мастера Вставка функций введите в ячейку С5 финансовую формулу =БС(С2;С4;С3;С1;0). В программе OpenOffice.org Calc функция имеет вид  =FV(С2;С4;С3;С1;0). Эта финансовая формула вычисляет будущее значение вклада в зависимости от процентной ставки, количества лет, суммы ежегодных выплат и суммы вклада. Последний аргумент, равный нулю означает, что выплаты производятся по окончании года. Полученный итог (89 366 руб) говорит о том, что суммы вклада недостаточно для накопления  100 000 руб.

  •  3.3.10. Автоматизируйте подбор требуемой суммы вклада. Для этого: выполните команды меню СервисПодбор параметра;
  •   В диалоговом окне в поле  Установить в ячейке (в программе Сalc поле называется Яч. с формулой)   щелкните мышью в ячейке $С$5;
  •   в поле Значение (в программе Сalc в поле Целевое значение) введите 100 000;
  •   в поле Изменяя значение ячейки (в программе Сalc в поле Изменяя яч.)  щелкните мышью в ячейке $С$1.
  •   Нажмите ОК в окне Подбора параметра.

3.3.11. Нажмите ОК в окне Результат подбора параметра. В ячейке С1 появится результат -16 603 р. (табл. 33). Задача решена. Итак, для получения желаемой суммы следует положить на депозит сумму    16 603 рублей.

                             Таблица 33. Результат подбора параметра

А

В

С

1

Сумма вклада

-16 603р.

2

Годовая ставка в %

10%

3

Ежегодные выплаты

-12 000р.

4

Срок вклада

5

5

Будущее значение

100 000р.

4. Отчет о проделанной работе

Распечатки решения задания.

Литература: [6], c. 276-284.

Работа 9. ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ ЭЛЕКТРОННЫХ ТАБЛИЦ ПРИ КРЕДИТНЫХ РАСЧЕТАХ

  1.  Цель работы

Научиться использовать функции Excel для расчетов по кредитным схемам.

2. Основные теоретические положения

Кредитные расчеты основаны на использовании понятия ренты.

Рента – это финансовая схема с постоянными взносами или выплатами. Если выплаты в разные периоды R1 = R2 = …= Rn = R (равномерная рента).

Наращенная сумма для ренты определяется по формуле

     .    (1)

В Excel для вычисления по формуле (1) используется функция БС, в которой аргумент выплаты – это значение R  (в программе OpenOffice.org Calc - FV).

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

    .     (2)

В Excel для расчета R используется функция ПЛТ (промежуточные платежи) с аргументами

=ПЛТ(Ставка; Кпер;ПС;БС;Тип).

Здесь аргумент будущее значение – величина S (в программе OpenOffice.org Calc  используется функция PMT с аналогичными аргументами).

Кроме того, так как выплаты включают не только плату за взятый кредит, но и платежи по процентам, есть функции ПРПЛТ (плата по процентам) и ОСПЛТ (основные платежи). Синтаксис функции ПРПЛТ:

=ПРПЛТ(Ставка; Период; Кпер; Пс; Бс; тип)

У функции ОСПЛТ аргументы те же самые.

В программе OpenOffice.org Calc функция для расчета платы по процентам за данный период называется IPMT, функция для расчета основных платежей за данный период – PPMT.

ПРИМЕР 1

Для покупки квартиры стоимостью 201 900 руб. взята 30-летняя ипотечная ссуда со ставкой 8 % годовых при начальном взносе 20 % суммы. Сделать расчет для ежемесячных и ежегодных выплат по ссуде.

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

РЕШЕНИЕ

n = 30 лет, r = 8 %, P = 201 900 руб.

Начальный взнос А = 0,2201900 = 40380 руб.

1). Вычисление ежемесячных выплат

Ставка за период , число периодов

n = 30 (лет)*12 (месяцев) =360 (месяцев).

По формуле (6) размер платежа R равен

.

Для расчета по этой формуле нужно знать размер ссуды S. Он вычисляется как разность между стоимостью квартиры и начальным взносом

S=P-A=201 900-0,2201 900=201 900 (1-0,2)=201 9000,8=161 520 (руб).

Тогда

  (руб).

2) Вычисление ежегодных выплат

Ставка за период i = 8 %, число периодов n = 30 лет

 (руб).

3. Порядок выполнения работы

Задание 1. Рассчитать ежемесячные и ежеквартальные выплаты для примера 1.

Задание 2. Рассчитать отдельно выплаты по процентам и основным платежам.

3.1. Вычисление ежемесячных и ежегодных  выплат

Для решения этой задачи создадим  электронную таблицу. В режиме показа формул она представлена в табл. 34, а в режиме показа вычислений в табл. 35. При работе с программой Calc в ячейку В10 табл. 34 следует ввести формулу   =PMT(В5/12;В8;В6), а в ячейку D10 - формулу  =PMT(B5;D8;B6).

Таблица 34. Расчет ипотечной ссуды. Показ формул

А

В

С

D

E

1

Расчет ипотечной ссуды

2

Исходные данные

 

 

 

3

Цена квартиры

201900

 

 

 

4

Первый взнос (%)

0,2

 

 

 

5

Годовая процентная

ставка

0,08

 

 

 

6

Размер ссуды

=B3*(1-B4)

 

 

 

7

Ежемесячные платежи

Ежегодные платежи

8

Срок погашения ссуды

=D8*12

месяцев

30

лет

9

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

10

Периодические выплаты

=ПЛТ(B5/12;B8;B6)

 

=ПЛТ(B5;D8;B6)

11

Общая сумма выплат

 =B8*B10

 

=D8*D10

 

12

Общая сумма комиссионных

=B11+B6

 

=D11+В6

 

3.2. Вычисление ежегодных платежей по процентам, основных платежей и остатка долга

При расчете используются функции: в Excel  ПРПЛТ и ОСПЛТ, а в программе Calc IPMT и PPMT. Для этого дополним табл. 34 (строки 13 45). Формулы вводятся в ячейки B16:D16 и копируются в ячейки B17:D45.Введенные формулы показаны в табл. 36.  При работе с программой Calc в ячейку В16 вводится формула =IPMT(B$5;A16;D$8;B$6)  и копируется в ячейки В17:В45. В ячейку С16 вводится формула                                            = PPMT(B$5;A16;D$8;B$6)  и копируется в ячейки С17:С45. Результаты вычислений показаны в табл. 37.

                                    Таблица 35. Расчет ипотечной ссуды. Показ вычислений

А

В

С

D

E

1

Расчет ипотечной ссуды

2

Исходные данные

 

 

 

3

Цена квартиры

    201 900,00р.

 

 

 

4

Первый взнос (%)

20%

 

 

 

5

Годовая процентная ставка

8%

 

 

 

6

Размер ссуды

   161 520,00р.

 

 

 

7

Ежемесячные платежи

Ежегодные платежи 

8

Срок погашения ссуды

360

месяцев

30

лет

9

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

10

Периодические выплаты

-1 185,18р.

 

-14 347,41р.

 

11

Общая сумма выплат

-426 663,55р.

 

-430 422,21р.

 

12

Общая сумма комиссионных

-265 143,55р.

 

-268 902,21р.

 

 4. Отчет о проделанной работе

                                                 Распечатки решения задания.

Литература: [6], с. 231-252.


Таблица 36. Показ формул в
Excel для расчета платежей по процентам

Таблица 37. Показ вычислений при расчете платежей по процентам

 

А

В

C

D

 

1

Расчет ипотечной ссуды

2

Исходные данные

 

 

 

3

Цена квартиры

   201 900,00р.

 

 

 

4

Первый взнос %

20%

 

 

 

5

Годовая процентная ставка

8%

 

 

 

6

Размер ссуды

   161 520,00р.

 

 

 

7

 

Ежемесячные платежи

 

Ежегодные платежи

 

8

Срок погашения ссуды

360

месяцев

30

лет

9

 

 

 

 

 

10

Периодические выплаты

-1 185,18р.

 

-14 347,41р.

 

11

Общая сумма выплат

-426 663,55р.

 

-430 422,21р.

 

12

Общая сумма комиссионных

-265 143,55р.

 

-268 902,21р.

 

13

Ежегодные платежи по процентам и остаток долга

14

Год

Плата по процентам

Основная плата

Остаток долга

 

15

0

 

 

161 520,00

 

16

1

-12 921,60р.

-1 425,81р.

160 094,19р.

 

17

2

-12 807,54р.

-1 539,87р.

158 554,32р.

 

18

3

-12 684,35р.

-1 663,06р.

156 891,26р.

 

19

4

-12 551,30р.

-1 796,11р.

155 095,15р.

 

20

5

-12 407,61р.

-1 939,79р.

153 155,36р.

 

21

6

-12 252,43р.

-2 094,98р.

151 060,38р.

 

22

7

-12 084,83р.

-2 262,58р.

148 797,80р.

 

23

8

-11 903,82р.

-2 443,58р.

146 354,22р.

 

24

9

-11 708,34р.

-2 639,07р.

143 715,15р.

 

25

10

-11 497,21р.

-2 850,19р.

140 864,96р.

 

26

11

-11 269,20р.

-3 078,21р.

137 786,75р.

 

27

12

-11 022,94р.

-3 324,47р.

134 462,28р.

 

28

13

-10 756,98р.

-3 590,42р.

130 871,85р.

 

29

14

-10 469,75р.

-3 877,66р.

126 994,20р.

 

30

15

-10 159,54р.

-4 187,87р.

122 806,32р.

 

31

16

-9 824,51р.

-4 522,90р.

118 283,42р.

 

Окончание табл. 37. Показ вычислений при расчете платежей по процентам

 

А

В

C

D

32

17

-9 462,67р.

-4 884,73р.

113 398,69р.

 

33

18

-9 071,90р.

-5 275,51р.

108 123,18р.

 

34

19

-8 649,85р.

-5 697,55р.

102 425,63р.

 

35

20

-8 194,05р.

-6 153,36р.

96 272,27р.

 

36

21

-7 701,78р.

-6 645,63р.

89 626,64р.

 

37

22

-7 170,13р.

-7 177,28р.

82 449,37р.

 

38

23

-6 595,95р.

-7 751,46р.

74 697,91р.

 

39

24

-5 975,83р.

-8 371,57р.

66 326,34р.

 

40

25

-5 306,11р.

-9 041,30р.

57 285,04р.

 

41

26

-4 582,80р.

-9 764,60р.

47 520,43р.

 

42

27

-3 801,63р.

-10 545,77р.

36 974,66р.

 

43

28

-2 957,97р.

-11 389,43р.

25 585,23р.

 

44

29

-2 046,82р.

-12 300,59р.

13 284,64р.

 

45

30

-1 062,77р.

-13 284,64р.

0,00р.

 

Работа 10. АНАЛИЗ РЫНКА ОБЛИГАЦИЙ

1. Цель работы

Научить пользователя основным принципам и приемам работы с электронной таблицей  на примере анализа рынка облигаций.

2. Основные теоретические положения

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

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

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

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

Срок погашения – оговоренный срок, по истечении которого облигация погашается, то есть выплачивается ее номинальная стоимость.

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

Сумма купона = Номинал * (Ставка процента /100%).

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

Для этого используется следующая формула:

Рt =(Pt+1 +C)/(1+r),

где     Pt   стоимость облигации в момент времени (год) t;

Pt+1   стоимость облигации в срок (год) t +1;

С сумма купонного платежа;

r  ставка дисконтирования, которая рассчитывается по формуле: если, например, ставка дисконтирования 20 %, то r = 20/100 = 0,2. При этом считается, что в момент погашения Р = F (номинальной стоимости облигации).

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

Доходность до погашения  это доходность в расчете на год, которую обеспечит себе инвестор (кредитор), если, купив облигацию, продержит ее до погашения.

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

Y = (F + C*N - P)/(N*P),

где Y  доходность до погашения;

F  номинальная стоимость облигации;

С  сумма купонной выплаты;

N  количество лет до погашения;

Р  текущая стоимость облигации.

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

3. Порядок выполнения работы

Задание. Создать таблицу для анализа доходности облигаций.

3.1. Запуск приложения Microsoft Excel (или OpenOffice.org Calc).

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

3.2. Cоздание первого блока таблицы

  •  введите на рабочий лист данные из представленной табл. 38.

3.3. Форматирование, обрамление и цветовое оформление таблицы

  •  выделите диапазон ячеек A3:D3. В меню выберите пункт Формат – Ячейки  выберите вкладку Выравнивание и щелчком мыши установите "х" в окне Переносить по словам. Нажмите кнопку ОК;
  •  выделите диапазон ячеек A3:D3, щелкните по кнопке Полужирный;
  •  выделите диапазон ячеек B3:D6. Щелкните по кнопке По центру;
  •  выделите диапазон ячеек А4:А6. Щелкните по кнопке Полужирный;
  •  выделите всю таблицу (диапазон ячеек A3:D6), щелкните по кнопке Границы. В появившемся окне щелкните по варианту рамки;

                                                                               Таблица 38. Исходные данные

A

B

C

D

3

Облигация

Номинал

Процентная

ставка купона, %

Срок обращения, лет

4

А

100

0

5

5

В

100

15

5

6

С

100

20

5

  •  выделите диапазон ячеек А3:А6 (первый столбец), щелкните по кнопке Цвет заливки. В появившемся окне выберите цвет для закраски первого столбца и щелкните мышью по квадрату с этим цветом. Проделайте аналогичные действия для следующих диапазонов ячеек B3:D3 и B4:</