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

Работа добавлена на сайт samzan.net:

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. МАТРИЧНЫЕ ОПЕРАЦИИ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Цель работы

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

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. Вычисление суммы матриц C=A+B
Матрица А		Матрица В
1	4	-3		-3	0	4
-2	6	5		5	-7	2
7	-8	9		-4	6	-8
Матрица С
-2	4	7
3	-1	7
3	-2	1
Задание 2. Вычисление матрицы D=3*A-B
6	12	5
-11	25	13
25	-30	35
Задание 3. Вычисление определителя матрицы А
228
Задание 4. Вычисление обратной матрицы А
0,412	-0,263	0,009
0,232	-0,053	-0,05
-0,11	0,158	0,061
Задание 5. Умножение матриц
5	-10	-12
16	-12	-36
-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.

Умножение матриц:
- выделить ячейки Е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. Отчет по работе

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

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

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

№ варианта	Система уравнений	№ варианта	Система уравнений
0		5
1		6
2		7
3		8
4		9

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

Цель работы

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

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

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

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

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

Отрасли

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

отраслей

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

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

спрос

600

1000

800

250

150

100

500

300

160

400

2000

3000

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

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

Пусть Х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 по формуле:

Х=SY.

Отсюда

Таким образом, вектор выпуска продукции в следующем периоде при заданном векторе конечной продукции 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. Расчет возвращаемой суммы при получении кредита
Исходные данные
Переменные	Вычисления	Формулы и комментарии
Годовая ставка	15%
Дата выдачи кредита	05.01.09
Дата возврата кредита	20.09.09
Сумма кредита	$1 000 000
Расчеты
Срок кредита в днях	258	‘=В7-В6
Срок кредита в годах	0,71	‘=В10/366
Ставка для периода	11%	‘=В5*В11
Сумма возврата	$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

А	В	С
ФИНАНСОВЫЕ ВЫЧИСЛЕНИЯ В Excel
Задание 1. Расчет возвращаемой суммы при получении кредита
Исходные данные
Переменные	Вычисления	Формулы и комментарии
Годовая ставка	15%
Дата выдачи кредита	05.01.09
Дата кредита	20.09.09
Сумма возврата	$1 000 000
Расчеты
Срок кредита в днях	258	‘=В7-В6
Срок кредита в годах	0,71	‘=В10/366
Ставка для периода	11%	‘=В5*В11
Сумма возврата	$1 106 024.4	‘=В8(1+В5В11)

Задание 2. Расчет возврата ссуды с использование функции БС
	$1 106 024.4	‘=БС(В12;1;;В8)
Задание 3. Расчет по схеме сложных процентов
	90305,56р.	‘=БС(12%/4;5*4;;-50000)

Задание 4. Расчет постоянной ренты с использованием функции БС
Исходные данные
Переменные	Вычисления	Формулы и комментарии
Срок	10
Сумма	$20 000
Годовая ставка	22%
Расчеты
Плата в конце периода	$57314,83	‘=БС(В25;10;-2000)
Плата в начале периода	$69924,09	‘=БС(В25;10;-2000;;1)

Задание 5. Расчет платы за вексель с использованием функции ПС
Исходные данные
Переменные	Вычисления	Формулы и комментарии
Процентная ставка	18%
Периодичность выплат	2
Будущее значение	-$4 000000
Количество лет	3
Расчеты
Процент за период	9%	‘=В33/В34
Современное значение	$2 385 069,31	‘=ПС(В36;В34*В36;;В35)

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

	А	В	С
	Задание 6. Расчет срока вклада с использованием функции КПЕР
	Начисление раз в год	59	‘=КПЕР(15%;0;-80;300000)
	По кварталам	56	‘=КПЕР(15%/4;0;-80;300000)/4
44
45	Задание 7. Расчет процентной ставки
46		11%	‘=СТАВКА(4;;4000;-6000)

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

Цель работы

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

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

2.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) Внутренняя норма прибыли инвестиции (IRR – internal 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. Задание для самостоятельной работы

Годы

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

-100

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

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

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

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

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

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

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

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

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

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

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

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

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

3.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:G1 и щелкните по кнопке Объединить и поместить по центру, установите начертание – Полужирный, размер – 12. Введите текст: Докладная записка. Снимите выделение.

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

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

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

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

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. Закрепление макроса за различными элементами

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

п. 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. Закрепление макроса за различными элементами

Скопируйте и вставьте табл. 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.

При работе в 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. Цель работы

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

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

В экономической и финансовой деятельности часто встречаются задачи расчета прибыли в зависимости от срока вклада и процента. В работе 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	=24C5+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	=24C5+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. ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ ЭЛЕКТРОННЫХ ТАБЛИЦ ПРИ КРЕДИТНЫХ РАСЧЕТАХ

Цель работы

Научиться использовать функции 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,2201900 = 40380 руб.

1). Вычисление ежемесячных выплат

Ставка за период , число периодов

n = 30 (лет)*12 (месяцев) =360 (месяцев).

По формуле (6) размер платежа R равен

Для расчета по этой формуле нужно знать размер ссуды S. Он вычисляется как разность между стоимостью квартиры и начальным взносом

S=P-A=201 900-0,2201 900=201 900 (1-0,2)=201 9000,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:D6.

3.4. Создание второго блока таблицы

введите данные на тот же рабочий лист из табл. 39.

Таблица 39. Данные для второго блока таблицы

Ячейка	Содержимое ячейки	Формат ячейки
А8	Ставка дисконтирования, %	Переносить по словам, по центру
С8	20
А10	Потоки платежей	Полужирный
А11	Процентная ставка купона, %
В11	Момент времени (года)	По центру
А13:А15	Скопировать из С4:С6
В12	0	Числовой
С12	1	Числовой
D12	2	Числовой
E12	3	Числовой
F12	4	Числовой
G12	5	Числовой
B13	=B4*C4/100
B14:B15	Скопировать из В13
G13	=B4+B4*C4/100
G14:G15	Скопировать из G13

создайте обрамление для диапазона ячеек A11:G15;
выделите диапазон ячеек А11:А12. Нажмите пункт Формат – Ячейки  выберите вкладку Выравнивание и установите "х" в окошке Объединение ячеек и в окошке Переносить по словам. Нажмите ОК;
выделите диапазон ячеек B11:G11. Нажмите кнопку По центру;
установите курсор на ячейке В13. Нажмите кнопку Копировать, выделите диапазон ячеек C13:F13. В меню выберите пункт Правка  Специальная вставка. В диалоговом окне щелкните по пункту Значения и нажмите ОК;
проделайте аналогичные действия для копирования значения ячейки В14 в диапазон C14:F14 и ячейки В15 в диапазон C15:F15;
измените цветовое и шрифтовое исполнение таблицы.

3.5. Создание третьего блока таблицы

введите следующие данные на тот же рабочий лист из табл. 40.

Таблица 40. Третий блок таблицы

Ячейка	Содержимое ячейки	Формат ячейки
А18	Скопировать из А11: G15
А17	Теоретическая цена облигаций	Переносить по словам
В20:G22	Выделить и очистить <Delete>
В20:G22		Числовой, число десят. знаков - 3
G20:G22	Скопировать из В4:В6
F20	=(G20+F13)/(1+$C$8/100)
F21-F22	Скопировать из F20
B20:E22	Скопировать из F20

В результате вашей работы на экране появится новая таблицы (табл.41 – режим показа формул, табл. 42 – режим вычислений).

3.6. Расчет доходности облигаций А, В и С при их приобретении по текущим ценам (в нулевой год)

в ячейку А25 введите заголовок четвертой таблицы Доходность облигаций. Выделите ячейку А25, нажмите кнопку Полужирный и затем кнопку Курсив, выберите цвет шрифта заголовка (инструмент Цвет шрифта);
выделите интервал ячеек А3:А6, нажмите кнопку Копировать, затем установите курсор на ячейке А26 и нажмите кнопку Вставить;
в ячейке В27 рассчитаем доходность для облигации А при ее приобретении по текущей цене:

(Номинал + Сумма купонных выплат * Количество лет до погашения  Цена облигации)/(Цена облигации* Количество лет до погашения).

Для этого ввести в ячейку В27 формулу: =(B4+C13*D4 - B20)/(B20*D4);

скопируйте формулу из ячейки В27 в ячейки В28 и В29;
выделите ячейки В27:В29, выберите пункт в главном меню Формат – Ячейки  выберите вкладку Число  Числовые форматы  Процентный. В окно Число десятичных знаков  введите цифру 3. Нажмите ОК;

Таблица 42. Анализ рынка облигаций. Показ вычислений

	А	В	С	D	E	F	G
1
2
3	Облигация	Номинал	Процентная ставка купона, %	Срок обращения, лет
4	А	100	0	5
5	В	100	15	5
6	С	100	20	5
7
8	Ставка дисконтирования, %		20
9
10	Потоки платежей
11	Процентная ставка купона, %	Момент времени (года)
12		0	1	2	3	4	5
13	0	0	0	0	0	0	100
14	15	15	15	15	15	15	115
15	20	20	20	20	20	20	120
16
17	Теоретическая цена облигаций
18	Процентная ставка купона, %	Момент времени (года)
19		0	1	2	3	4	5
20	0	40,188	48,225	57,870	69,444	83,333	100
21	15	85,047	87,056	89,468	92,361	95,833	100
22	20	100,000	100,000	100,000	100,000	100,000	100

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

	А	В
25	Доходность облигаций
26	Облигация
27	А	29,7660%
28	В	21,1537%
29	С	19,1464%

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

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

создайте на новом (Лист2) рабочем листе новую таблицу с исходными данными табл. 43;
создайте таблицу потока платежей по данным облигациям (см. п. 3.4);
создайте таблицу теоретических цен по данным облигациям (см. п. 3.5);

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

Облигация	Номинал	Процентная ставка купона, %	Срок обращения, лет
А	100	0	3
В	100	15	3

рассчитайте доходность по каждой из облигаций при ее покупке в нулевой год (см. пп. 3.6);
продемонстрируйте работу преподавателю;
сохраните в своей папке книгу с именем ОБЛИГАЦИЯ. Завершите работу программы.

4. Отчет о проделанной работе

Распечатка полученных таблиц.

Литература: [6], c. 284-290.

Работа 11. ПРОГНОЗИРОВАНИЕ КУРСА ВАЛЮТЫ

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

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

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

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

Временной ряд  это набор чисел, “привязанный” к последовательным, обычно равноотстоящим моментам времени. Данный ряд чисел получается как результат наблюдения за ходом некоторого процесса. Временным рядом, например, будет являться таблица значений изменения курса доллара с 19 марта по 23 апреля 2008 года.

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

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

В Excel для построения уравнения тренда используется режим Линия тренда.

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

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

3.1. Ввод исходной таблицы

Выполните команды: Пуск – Программы – Microsoft Excel (OpenOffice.org Calc). На экране появится чистый рабочий лист. Введите данные из исходной табл. 44.

Таблица 44. Таблица исходных данных

	A	B	C	D	E	F	G
1	Изменение курса доллара
2	№	1	2	3	4	5	6
3	Дата	19.03.08	26.03.08	02.04.08	09.04.08	16.04.08	23.04.08
4	Курс	24,25	25,65	26,33	25,87	25,33	24,85

3.2. Построение графика Изменение курса доллара

В главном меню выберите пункт Вставка, а в появившемся меню нажмите на пункт Диаграмма.

3.2.1. Шаг 1

При рпботе с Excel в списке типов диаграмм Тип щелкните по пункту График. Среди видов графиков щелкните по картинке в левом верхнем углу. В правой нижней части окна появится запись: График отражает развитие процесса по времени или по категориям. Нажмите кнопку Далее.

В программе OpenOffice.org Calc в списке типов диаграмм Тип диаграммы щелкните по пункту Линии. Выберите второй тип графика (Линии и точки), Далее.

3.2.2. Шаг 2

При работе с Excel введите диапазон ячеек в поле Диапазон. Для этого в таблице Изменение курса доллара выделите диапазон ячеек B4:G4. В верхней части окна появится изображение графика. В нижней части окна справа от надписи Ряды в установите флажок рядом с пунктом Строках. Перейдите на вкладку Ряд и в поле Подписи оси Х введите диапазон В2:G2. Нажмите кнопку Далее.

В программе OpenOffice.org Calc введите Диапазон данных, для этого в таблице Изменение курса доллара выделите диапазон ячеек B4:G4. Установите флажок Ряды данных в строках, галочку Первая строка как надпись (Первый столбец как надпись галочки не должно быть), Далее.

3.2.3. Шаг 3

При работе с Excel в появившемся окне выберите закладку Заголовки. В поле Название диаграммы введите Изменение курса доллара. В поле Ось X введите Дата. В поле Ось Y введите Курс. Нажмите кнопку Далее.

В программе OpenOffice.org Calc (в окне Диапазон данных курсор стоит на Название) щелкнуть курсором мыши по слева от поля Диапазон для: Название, затем по ячейке А4 в таблице Изменение курса доллара, Далее.

3.2.4. Шаг 4

При работе с Excel в списке Выберите диаграмму на листе щелкните по указателю Отдельном. Нажмите кнопку Готово. На новом листе с названием Диаграмма1 появится график изменения курса доллара за период с 19 марта по 23 апреля.

В программе OpenOffice.org Calc в окне Заголовок введите Изменение курса доллара, в окне Ось Х введите Дата, в окне Ось Y введите Курс, Готово.

Построение линии тренда

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

Установите указатель мыши точно на линию графика и нажмите правую клавишу мыши. В появившемся меню выберите команду Добавить линию тренда. Откроется окно Линия тренда, в котором выберите тип Линейная;
Перейдите на вкладку Параметры. В появившемся окне установите флажок (v) в окне с названием Показывать уравнение на диаграмме. Нажмите ОК;
На том же графике появится прямая линия, представляющая собой тренд, то есть тенденцию изменения курса доллара, и ее уравнение (рис. 1).

В программе OpenOffice.org Calc:

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

3.4. Создание массива значений тренда (прогнозирование).

Щелкните по вкладке Лист1 в нижней части экрана. На экране появится таблица с исходными данными;
В ячейку В6 введите название таблицы Значения тренда. Выделите диапазон ячеек B6:G6, нажмите кнопку Полужирный, затем нажмите кнопку Объединить и поместить в центре;

Рис. 1

Выделите диапазон ячеек B2:G2, то есть ряд чисел, соответствующих номерам периодов, для которых указаны исходные значения курса доллара. Нажмите кнопку Копировать, затем установите курсор на ячейке В7 и нажмите кнопку Вставить;
В ячейку А8 введите уравнение тренда (см. лист Диаграмма1) =0,0451*А7 + 25,222. В ячейке А8 появится число 25,222. При вводе адресов ячеек с клавиатуры не забудьте переключиться на латинский алфавит!!!
В программе Есхе1:выделите диапазон ячеек A7:G8. В главном меню выберите пункт Данные, а в появившемся подменю щелкните по пункту Таблица подстановки. В появившемся окне в поле Подставлять значения по столбцам в введите А7. Нажмите ОК. В ячейках B8:G8 появятся значения тренда на соответствующие даты (можно вместо этого пункта просто скопировать формулу из А8 в В8:G8);

В программе OpenOffice.org Calc: выделите диапазон ячеек В7:G8. В главном меню выберите пункт Данные, а в появившемся подменю щелкните по пункту Совмещенные операции. В появившемся окне в поле Формулы ввести $A$8, в поле Строка ввода $А$7. Нажмите ОК. В ячейках B8:G8 появятся значения тренда на соответствующие даты (можно вместо этого пункта просто скопировать формулу из А8 в В8:G8).

Выделите диапазон ячеек B7:G8, затем щелкните по кнопке Границы. В появившемся окне выберите вариант рамки.

Таким образом, составлен прогноз курса доллара на 30.04.08 (табл. 45).

Таблица 45. Прогноз курса доллара

	A	B	C	D	E	F	G
1	Изменение курса доллара
2	№	1	2	3	4	5	6
3	Дата	19.03.05	26.03.05	02.04.05	09.04.05	16.04.05	23.04.05
4	Курс	24,25	25,65	26,33	25,87	25,33	24,85
5
6		Значение тренда
7		1	2	3	4	5	6
8	25,222	25,2671	25,3122	25,3573	25,4024	25,4475	25,4926

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

3.5.1. Создайте на этом же рабочем листе новую таблицу с исходными данными по движению курса марки (табл. 46).

3.5.2. Постройте график изменения курса марки.

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

Движение курса марки

№

Дата

10.03.08

17.03.08

24.03.08

31.03.08

07.04.08

14.04.08

Курс

12,83

12,95

13,29

13,26

13,77

13,78

3.5.3. Постройте линию тренда и рассчитайте по уравнению тренда значение курса марки на 21.04.08 (на одну неделю вперед).

3.5.4. Продемонстрируйте работу преподавателю. Завершите работу программы, сохранив информацию в своей именной папке.

4. Отчет о проделанной работе

Распечатки графиков прогноза курса валют.

Литература: [4], с. 378-380.

Работа 12. ОБРАБОТКА МАССОВЫХ ОТПРАВЛЕНИЙ

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

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

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

Операционная система Windows дает возможность совмещать работы основных приложений. Например, в пакете программ Microsoft Office из информации табличного процессора Excel можно сформировать базу данных в Access. Можно также осуществить слияние документов табличного процессора Excel и текстового редактора Word. Аналогичным образом в пакете программ OpenOffice.org можно осуществить слияние документов текстового редактора Writer и электронной таблицы Calc.

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

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

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

3.1. Создание файла с данными об абонентах:

3.1.1. Выполните команды меню Файл – Создать (откроется новая книга с чистым рабочим листом).

Создать таблицу (табл. 47), которая представляет собой ведомость оплаты за телефонные услуги.

Таблица 47. Ведомость оплаты за телефонные услуги

	A	B	C	D
1	№ пп	Фамилия И.О.	Телефон	Сумма долга
2	1	Баболина А.И.	213-89-51	75р. 50к.
3	2	Быковская Г.В.	135-67-97	175р.00к.
4	3	Васильева Э.С.	312-77-57
5	4	Воеводина Е.Г.	213-89-52
6	5	Громин Л.С.	432-90-94
7	6	Дмитриев Н.А.	213-89-54	46р. 93к.
8	7	Евдокимова Е.В.	312-11-10
9	8	Евсеева И.А.	312-87-66	25р. 50к.
10	9	Жилина Н.В.	312-11-11
11	10	Земелина Н.И.	432-90-98	0р. 33к.
12	11	Зубарев П.С.	311-45-16
13	12	Ильина К.В.	210-36-87
14	13	Истомина Н.В.	315-45-45
15	14	Итков В.М.	213-77-12
16	15	Карелин М.Ю.	312-89-57	75р. 50к.
17	16	Кузнецова А.Д.	210-85-89
18	17	Ларина В.Г.	213-65-98
19	18	Лавров К.И.	315-78-78
20	19	Лещенков О.Ф.	213-44-81

Окончание таблицы 47

	A	B	C	D
21	20	Ломова Р.И.	110-15-87
22	21	Макарова Н.Н.	319-11-13	225р. 75к.
23	22	Мошкина О.В.	320-33-54
24	23	Мякишев Л.Д.	312-57-11
25	24	Налина О.М.	432-90-96	18р. 96к.
26	25	Огольцава Е.В.	312-11-19	75р. 50к.
27	26	Одоевская В.Г.	432-89-74
28	27	Оросьева О.В.	110-11-12
29	28	Панин П.П.	112-56-98
30	29	Петрова Н.Р.	186-34-32
31	30	Пташкина О.В.	186-45-67
32	31	Романина Н.Ю.	312-11-14	33р. 50к.
33	32	Рокотов С.В.	315-96-31
34	33	Руковица А.Д.	312-45-73
35	34	Рябов А.Р.	310-12-45
36	35	Санин О.К.	311-60-07
37	36	Стегалина З.С.	135-67-93	885р. 00к.
38	37	Тенина Г.В.	564-05-60
39	38	Тупалев В.П.	552-23-18	75р. 50к.
40	39	Усатов В.В.	212-04-98
41	40	Ушеров Р.И.	213-89-62	23р. 70к.

В ячейку Е1 ввести заголовок: 1 – должник, 0 – нет.
1. В ячейку Е2 ввести формулу, работающую по правилу: если значение ячейки D2 не равно нулю (то есть имеется задолжность), то ввести 1, иначе – 0. Формула в Excel имеет вид =ЕСЛИ(D2=0;0;1) (В программе OpenOffice.org Calc формула =IF(D2=0;0;1)).
  1. Скопировать формулу на весь список. В графе 1 – должник, 0 – нет стоят единицы, если абонент не заплатил за услуги.

3.2. Подготовка данных об абонентах–задолжниках

3.2.1. Поставьте курсор в поле заголовков таблицы и выполните команды меню Данные – Фильтр – Автофильтр. Щелкните по кнопке фильтра в графе 1 –должник, 0 – нет и выберите в списке условие 1. На экране появится новая таблица (табл. 48), содержащая только те записи, в которых стоит условие 1.

3.2.2. Протяните курсор мыши с нажатой левой кнопкой по новой таблице и выполните команды меню Правка – Копировать, затем вставьте на рабочий Лист2 данные из буфера обмена (Правка – Вставить). Получилась новая табл. 48 без кнопок автофильтра.

3.2.3. Для сохранения документа выполнить команды Файл – Сохранить как… – Мои документы – папка Факультет – ваша Именная папка – в окне Имя – введите имя Абонент – Сохранить. Закройте документ.

3.3. Подготовка основного документа (бланка письма)

3.3.1. При работе в Microsoft Officе не закрывая файла Excel, выполните команды: Пуск – Программы – Microsоft Word.

При работе в OpenOffice.org для создания бланка письма используем программу Writer, для запуска которой выполнить команды Пуск – Программы - OpenOffice.org - OpenOffice. Org Writer

Таблица 48. Данные об абонентах-задолжниках

	A	B	C	D	E
1	№ пп	Фамилия	Телефон	Сумма долга	1-должник, 0-нет
2	1	Баболина А.И.	213-89-51	75р. 50к.	1
3	2	Быковская Г.В.	135-67-97	175р.00к.	1
4	6	Дмитриев Н.А.	213-89-54	46р. 93к.	1
5	8	Евсеева И.А.	312-87-66	25р. 50к.	1
6	10	Земелина Н.И.	432-90-98	985р 36к	1
7	15	Карелин М.Ю.	312-89-57	75р. 50к.	1
8	16	Кузнецова А.Д.	210-85-89	75р. 50к.	1
9	17	Ларина В.Г.	213-65-98	885р. 00к.	1
10	19	Лещенков О.Ф.	213-44-81	275р. 50к.	1
11	21	Макарова Н.Н.	319-11-13	123р. 70к.	1
12	22	Мошкина О.В.	320-33-54	33р. 50к.	1
13	24	Налина О.М.	432-90-96	18р. 96к.	1

3.3.2. Создайте бланк письма по образцу рис. 2. В редакторе Word окна для ввода информации формируйте при помощи команды меню Вставка – Надпись. Курсор превращается в черный крестик, растяните рамку до нужных размеров.

В программе OpenOffice Writer для создания двух верхних окон бланка можно воспользоваться панелью рисования. Для ввода текущей даты выполните команды Вставка – Поля - Дата. Для создания окна ввода фамилии абонента выполните команды Вставка – Врезка. В появившемся окне во вкладке Тип в поле Ширина введите 5 см, в поле высота – 1см, в поле Привязка поставьте флажок К абзацу, Ок. Появившееся поле легко передвинуть. Для этого необходимо навести на поле курсор мыши и зажав левую клавишу перемещать. Окно ввода суммы задолженности создается подобным образом, только в поле Ширина следует ввести 4 см.

Образец бланка

(Вставьте текущую дату Вставка -Дата)

Уважаемый абонент

Ваша задолженность по оплате телефонных услуг

составила р.

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

Рис. 2

3.4. Слияние документов

Слияние документов Word и Excel

Последовательность действий следующая:

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

2) В первом окне Мастера:

выбрать опцию

 Текущий документ;

щелкнуть по кнопке Далее.

3) Во втором окне Мастера:

выбрать источник данных – опция  Список;
щелкнуть по кнопке Обзор.

4) В диалоговом окне Открыть источник данных:

в списке Тип файла установите Все файлы;
откройте папку Мои документы – факультет – именная;
щелкните по файлу Абонент – Открыть;
выберите номер листа таблицы Лист 2 – ОК.

5) Объединение документов Word и Excel

щелкните мышью в поле бланка Уважаемый абонент;
щелкните в окне Мастера слияния по кнопке Другие элементы;
в появившемся списке выберите Фамилия – Добавить – Закрыть;
щелкните мышью в поле бланка Сумма задолжности;
в окне Мастера щелкните по кнопке Другие элементы и выберите Сумма долга – Добавить – Закрыть.

6) Просмотр писем:

на панели Мастера слияний щелкните Просмотр писем;
для просмотра других писем используйте кнопку >>.

Слияние документов в OpenOffice.org

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

2) В первом окне Мастера:

выбрать опцию

 Использовать текущий документ;

щелкнуть по кнопке Далее.

3) Во втором окне Мастера:

выбрать тип документа – опция  Письмо;
щелкнуть по кнопке Далее.

4) В третьем окне Мастера:

а) Щелкнуть по кнопке Выбрать список адресатов.

В появившемся окне Выбор списка адресатов нажать на кнопку Добавить

В окне Открыть:

в списке Тип файла установите Все файлы;
откройте папку Мои документы – факультет – именная;
щелкните по файлу Абонент – Открыть;
выберите номер листа таблицы Лист 2 – ОК, ОК.

б) в п.2 данного окна поставить галочку Документ должен содержать блок с адресом и нажать кнопку Дополнительно. В появившемся окне Выбрать блок с адресом нажать на кнопку Создать.

В следующем окне в поле списка элементов адреса поставьте курсор на Фамилия, щелкните по стрелке, направленной вправо, поставьте курсор на Домашний телефон и нажмите на кнопку стрелки.

В окне Перетащите элемент с адресом сюда отобразятся <Фамилия> <Домашний телефон>. Нажмите два раза ОК.

в) в п.3 нажать на кнопку Поля. В появившемся окне Сопоставление полей в поле Фамилия, раскрыв список, выбрать Фамилия, в поле Домашний телефон – Сумма долга. (В данной программе команда Рассылка писем предусматривает только вставку в каждое письма соответствующий адрес. Нам же нужно вставить еще и сумму долга. Такого поля в перечне возможных полей адреса нет, поэтому мы временно воспользовались подходящим полем Домашний телефон). Нажать на ОК.

г) в окне п.4 отобразились фамилия и долг первого по списку абонента-должника. Нажмите Далее.

5) Следующие два шага Мастера (4 и 5) мы пропускаем, поэтому два раза нажмите Далее. Мы остановились на шестом шаге Мастера. Нажмите кнопку Правка документа. Окно Мастера рассылки писем уменьшится. В документе появилось окно с фамилией и суммой задолженности абонента.

Выделите фамилию абонента курсором мыши, выполните команду Вырезать, поставьте курсор в окно для ввода фамилии абонента, выполните команду Вставить. Таким же образом переместите сумму задолженности абонента в окно ввода суммы. Щелкните курсором мыши по оставшемуся пустому окну и удалите его. Далее в окне Мастера щелкните Вернуться в рассылку писем, Далее.

6) Седьмой шаг пропускаем, поэтому еще раз Далее. На восьмом шаге Мастера следует выбрать опцию  Сохранить исходный документ и нажать кнопку Сохранить исходный документ, Готово. В итоге документ будет содержать письма для всех абонентов-задолжников.

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

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

Литература: [2], с. 67-71.

Работа 13. ИСПОЛЬЗОВАНИЕ СВОДНЫХ ТАБЛИЦ ДЛЯ ФИНАНСОВОГО И ЭКОНОМИЧЕСКОГО АНАЛИЗА

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

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

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

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

Порядок выполнения задания

Внимание! Данную работу выполняют только студенты, работающие с пакетом программ Microsoft Office. СВОДНАЯ ТАБЛИЦА в программе OpenOffice.org Calc дает возможность осуществления сводных операций только для одной таблицы – практически работает аналогично режиму ПРОМЕЖУТОЧНЫЕ ИТОГИ, с которым мы познакомились при выполнении первой части лабораторных работ с табличным процессором.

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

Задание 2. Провести модификацию сводной таблицы для удобства проведения анализа.

Задание 3. Построить диаграмму по данным сводной таблицы.

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

3.1.1. Запуск программы. Откройте новую рабочую книгу

3.1.2. Ввод исходных данных по доходам:

активизируйте ячейку А2 и введите текст Парфюмерия. В ячейку A3 введите текст Бытовые товары, в ячейку А4 введите текст Продовольствие. В ячейку В1 введите текст Альфа, в ячейку С1 введите текст Бета, в ячейку D1 введите текст Гамма, в ячейку Е1 введите текст Дельта (см. табл. 49);
выделите ячейки с А1 по Е4 и выполните команду меню Формат – Автоформат. На экране появится диалоговое окно Автоформат;
в поле Список форматов выберите Объемный 2 и нажмите кнопку ОК. В таблице установится выбранный формат;
щелкните правой кнопкой мыши по ярлыку первого листа Лист1. Появится контекстное меню, в котором выберите команду Переместить/ Скопировать. Появится диалог Переместить или скопировать;
установите флажок Создавать копию и нажмите кнопку ОК. В рабочей книге появится лист с названием Лист1(2);

Примечание: все тексты, форматы, размеры ячеек на нем точно такие же, как и на листе с названием Лист1.

Аналогичным способом создайте еще одну копию листа.

щелкните правой кнопкой мыши по ярлычку Лист1(3). В появившемся диалоговом окне выберите команду Переименовать;
в поле Имя листа введите новое имя Июнь и нажмите клавишу <Enter>. Название листа на ярлычке изменится. Аналогично поменяйте названия двух других листов на Июль и Август соответственно;
введите данные в ячейки с В2 по Е4 из табл. 50 – 51, по доходам в тыс. руб. для четырех магазинов фирмы на конкретный рабочий лист (по месяцам). После ввода данных таблицы готовы для работы.

Таблица 49. Данные за июнь

Данные за июнь
	Альфа	Бета	Гамма	Дельта
Парфюмерия	345	465	364	456
Бытовые товары	1200	500	487	456
Продовольствие	970	457	354	645

Таблица 50. Данные за июль

Данные за июль
	Альфа	Бета	Гамма	Дельта
Парфюмерия	784	462	587	254
Бытовые товары	367	865	548	452
Продовольствие	385	245	524	798

Таблица 51. Данные за август

Данные за август
	Альфа	Бета	Гамма	Дельта
Парфюмерия	584	462	175	285
Бытовые товары	862	542	845	468
Продовольствие	426	452	462	138

Примечание: Обрабатывать таблицы в том виде, в котором они получены, довольно трудный процесс. Значительно облегчают эту работу сводные таблицы.

3.1.3. Построение сводной таблицы для проведения анализа доходов:

выполните команду меню Данные – Сводная таблица. Мастер сводных таблиц начнет работать с первого шага;
установите переключатель в положение В нескольких диапазонах консолидации, так как данные расположены на различных листах рабочей книги. Нажмите кнопку Далее;
установите переключатель в положение Создать одно поле страницы, так как все листы идентичны и отличаются только одним параметром - месяцем получения дохода. Нажмите кнопку Далее;
щелкните мышью в поле Диапазон, затем щелкните мышью по ярлычку листа Июнь и выделите ячейки с А1 по Е4. Если окно Мастер шаблонов закрывает нужные ячейки, переместите окно за заголовок мышью и сдвиньте его вниз;
нажмите кнопку Добавить и диапазон Июнь!$А$1:$Е$4 будет добавлен в поле Список диапазонов;
щелкните мышью по ярлычку листа Июль и нажмите кнопку Добавить;
щелкните мышью по ярлычку листа Август и нажмите кнопку Добавить. В поле Диапазон добавятся адреса трех диапазонов. Нажмите кнопку Далее;
в появившемся окне Мастер сводных таблиц (шаг 3) щелкните по кнопке Макет;
кнопку Строка перетащите мышью в поле Страница, которое находится в левой части окна, а кнопку Страница из правого столбца в поле Строка в левой части окна и нажмите кнопку Ок;
в появившемся диалоговом окне установите флажок Новый лист и нажмите кнопку Готово. Сводная таблица построится на новом листе;
дважды щелкните по ярлычку этого листа и введите новое имя листа Сводная таблица.

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

3.2.1. Модификация сводной таблицы для удобства проведения анализа:

щелкните дважды по ячейке А1, откроется диалог Вычисление поля сводной таблицы. Вместо слова Строка введите в поле Имя слова Тип товара, нажмите кнопку ОК.

3.2.2. Аналогично в ячейке В3 поменяйте название Столбец на Магазин, в ячейке А4 название Страница на Месяц.

3.2.3. Щелкните в ячейке А5 и введите в строке формул название месяца Август. В ячейку А6 введите название Июль, а в ячейку А7 введите название Июнь. Названия месяцев даны правильно, но расположены в неверном порядке. Не применяя сортировку, изменим порядок следующим способом. В ячейку А5 введите – Июнь, в ячейку А6 – Июль, а в ячейку А7 – Август. Обратите внимание, что все строки списка поменялись местами. Список отсортирован, сводная таблица готова.

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

3.2.5. Щелкните дважды мышью по ячейке В3 (по полю Магазин). Откроется диалог Вычисление поля сводной таблицы. В поле Скрыть элементы щелкните по названиям магазинов, которые в данный момент вас не интересуют, например Альфа и Гамма. Нажмите кнопку ОК. В сводной таблице останутся результаты только по магазинам, названия которых не убраны.

3.2.6. Восстановите первоначальный вид сводной таблицы, сняв отметки в поле Скрыть элементы.

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

3.3.1. Нажмите кнопку Мастер диаграмм на панели инструментов Стандартная.

3.3.2. В окне Мастера диаграмм щелкните мышью по типу круговой диаграммы.

3.3.3. В следующем окне Мастера диаграмм выберите второй вид диаграмм (объемная целая) и нажмите кнопку Далее.

3.3.4. В открывшемся окне в поле Диапазон введите, выделяя мышью, диапазоны ячеек $А$5:$А$7 и через точку с запятой $F$5:$F$7. Установите переключатель Ряды в в положение В столбцах и нажмите кнопку Далее.

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

3.3.6. Перейдите на лист Июнь. Измените число 1 200 на 12 000. Перейдите на лист Сводная таблица. Обратите внимание, что в сводной таблице изменений не произошло.

3.3.7. Выделите на сводной таблице ячейки с В5 по Е7 и щелкните правой кнопкой мыши по выделенным ячейкам. В появившемся контекстном меню выполните команду Обновить данные. Данные в ячейках сводной таблицы обновятся. Вид диаграммы также изменится.

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

3.4.1. Измените данные, введенные за август месяц, следующим образом: замените название магазина Дельта на Сигма, а данные по продажам: 285 на 200, 468 на 300, 138 на 400.

3.4.2. Внесите эти изменения в сводную таблицу.

3.4.3. Постройте диаграмму с объемами продаж по магазинам.

3.4.4. Продемонстрируйте результаты работы преподавателю.

3.4.5. Завершите работу табличного процессора.

4. Отчет о проделанной работе

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

Литература: [4], с.388-389.

СОДЕРЖАНИЕ

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

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

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

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

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

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

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

[8] Работа 8. АНАЛИЗ ФИНАНСОВОЙ ДЕЯТЕЛЬНОСТИ ПРИ ПОЛУЧЕНИИ КРЕДИТА

[9] Работа 9. ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ ЭЛЕКТРОННЫХ ТАБЛИЦ ПРИ КРЕДИТНЫХ РАСЧЕТАХ

[10] Работа 10. АНАЛИЗ РЫНКА ОБЛИГАЦИЙ

[11] Работа 11. ПРОГНОЗИРОВАНИЕ КУРСА ВАЛЮТЫ

[12] Работа 12. ОБРАБОТКА МАССОВЫХ ОТПРАВЛЕНИЙ

[13] Работа 13. ИСПОЛЬЗОВАНИЕ СВОДНЫХ ТАБЛИЦ ДЛЯ ФИНАНСОВОГО И ЭКОНОМИЧЕСКОГО АНАЛИЗА

Работа 13. Использование сводных таблиц для финансового

и экономического анализа …………………………………………….86

ИНФОРМАТИКА

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

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

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

Применение электронных таблиц Excel и OpenOffice.org Calc в финансовых и экономических расчетах

Сводный темплан 2008 г.

Лицензия ЛР № 020308 от 14.02.97

Санитарно-эпидемиологическое заключение № 78.01.07.953.П.005641.11.03

от 21.11.2003 г.

Подписано в печать Формат 6084 1/16

Б.кн.-журн. П.л. 5,75 Б.л. 2,875 Изд-во СЗТУ

Тираж 71 Заказ

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

Издательство СЗТУ, член Издательско-полиграфической ассоциации

университетов России

191186, Санкт-Петербург, ул. Миллионная, д.5

1. вольных хлебопашцах был принят в 1801 г
2. Экономика предприятия
3. Роль СМИ в современном мире (The mass media in the life of Society)
4. тема компьютера представляет собой комплекс взаимосвязанных программ который действует как интерфейс межд
5. далекая и таинственная
6. Статья 21 Дееспособность гражданина 1
7. Введение23
8. за их редакторскую работу и помощь; моей жене Сильвии за ее любовь и поддержку; а также бесчисленному множес
9. Тематический план по дисциплине Д13-1 ~ФИЛОСОФИЯ Направление подготовки- для всех направлен
10. Typing the text Wp phone Wht is Wp Wp stnds for Wireless ppliction protocol which llows users to send emils nd ccess informtion from the Internet on mobile phone
11. Теория и практика перевода
12. Система обработки жалоб (service recovery system) в сфере банковских услуг
13. лист для КА ЗАО ldquo;
14. гуманным советским государством
15. Лїтература суспільно економічних і кольонїзаційних обставин східноукраїнського житя XVXVI вв.html
16. Исследование работы судовой системы в ЧС.html
17. His mother Jne Frncesc Elgee ws celebrted writer who went by the pennme of ~Spernz~
18. Продаж цих цінних паперів дає банку кошти для надання іпотечних позик
19. Лабораторная работа 9 Конструирование многотабличной базы данных Цель работы- познакомиться с пон
20. Лабораторна робота 26 Наближене розв~язування рівнянь комбінованим методом

Материалы собраны группой SamZan и находятся в свободном доступе