Вариант 8. Вид функции: z1=f(x1)

1.         Вариант 8. Вид функции: z1=f(x1)

Исходная таблица

X1	70	72	75	68	68	71	69	71	69	68	68	69	75	83	73	71	82	69	73	73	72
Z1	471	492	506	464	457	478	475	490	480	457	470	468	515	578	508	493	556	463	497	502	498

Уравнение эмпирической зависимости вида y = ax + b для функции z1 = f(x1) решим методом наименьших квадратов.

Формулы для оценок параметров имеют следующий вид:

;  де ; ;

,

Заполним таблицу

i	X1	Z1	2
1	70	471	221841	32970
2	72	492	242064	35424
3	75	506	256036	37950
4	68	464	215296	31552
5	68	457	208849	31076
6	71	478	228484	33938
7	69	475	225625	32775
8	71	490	240100	34790
9	69	480	230400	33120
10	68	457	208849	31076
11	68	470	220900	31960
12	69	468	219024	32292
13	75	515	265225	38625
14	83	578	334084	47974
15	73	508	258064	37084
16	71	493	243049	35003
17	82	556	309136	45592
18	69	463	214369	31947
19	73	497	247009	36281
20	73	502	252004	36646
21	72	498	248004	35856
S	1509	10318	5088412	743931

;

;

Таким образом, искомая эмпирическая формула имеет вид z = 7,99x + 0,13.

2.         Построим диаграмму для функции z = 7,99x + 0,13:

-   выделим диапазон значений функции (G4:H24) выберем пункт меню Вставка – Диаграмма;

-   выберем тип Точечная и нажмем кнопку Готово

3.         Добавим линию тренда:

-   выберем курсором мыши линию точек функции и нажмем правую кнопку и выберем пункт Добавить линию тренда (рис. 9);

Рисунок 9. – Добавление линии тренда

-   выберем закладку Параметры и установим флажок на поле «Показать уравнение на диаграмме».

4.         Диаграмма имеет вид:

Рисунок 10. – Добавление линии тренда

Часть ІІ. Финансовый анализ в Excel

Задание №1.

Вариант 8

№	Размер вклада	Срок вклада	Процентная ставка
8	212600	6	6,5

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

Записываем заголовки столбцов в ячейки А1, А2 и А3. В ячейку В1 записываем размер суммы вклада, в ячейку В2 - срок вклада, в ячейку В3 - процентная ставка, в ячейку В4 - формулу для расчета коэффициента наращения, в ячейку В5 формулу для расчета суммы выплат через 6 лет: =БЗ(B3;B2;0;-B1;0).

Коэффициент наращения можно рассчитать так: = В5/В1, где в ячейке В1 - исходная сумма, в ячейке В5 - формула =БЗ(B3;B2;0;-B1;0).

Таблица с данными и с формулами:

Значения:			Вид формул:
Размер вклада	212600		212600
Срок вклада	6		6
Процентная ставка	6,5%		0,065
Коэффициент наращения	1,459142		=B5/B1
Сумма выплаты	310 213,65 грн.		=БЗ(B3;B2;0;-B1;0)

Задание № 2. Вариант 8

№	Размер вклада	Сумма вклада	Процентная ставка
8	21500	368	9,8%

Для построения системы можно использовать функцию ППЛАТ (PMT).

Требуется накопить 21500 грн., накапливая постоянную сумму каждый месяц, с помощью этой функции можно определить размер откладываемых сумм. Изменяемая ячейка - ячейка с количеством лет, используем функцию ППЛАТ, чтобы определить при процентной ставке 9,8% при определенной сумме выплат - в конце какого периода будет итоговая сумма - 21500. За ежемесячные отчисления - возьмем 368 грн.

Для решения данной задачи можно воспользоваться финансовой функцией ППЛАТ (PMT). Создаем таблицу со следующей структурой:

Размер вклада	21500		Размер вклада	21500
срок вклада	3		срок вклада	3
Процентная ставка	9,80%		процентная ставка	0,098
Сумма выплаты	516,14 грн.		Сумма выплаты	=ППЛАТ(B3/12;B2*12;0;-B1)

Запускаем программу Подбор параметра через меню Сервис. Изменяемая ячейка - срок вклада, т.е. В2, в ячейке В4 должны получить результат - 200.

Появляется сообщение:

Искомое значение срока вклада - 6 лет - при ежемесячном отчислении 200 грн через 6 лет на счете будет 21500 грн.

Таблица после выполнения программы Подбор параметра:

Размер вклада	21500
срок вклада	6,4564557
Процентная ставка	9,8%
Сумма выплаты	200,00 грн.

Задание № 3. Таблицы подстановки с одной переменной. Вариант 8

№	Размер вклада	Срок вклада	Процентная ставка
8	180800	6	6,0%

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

“Таблица данных” оперирует одной или двумя величинами одновременно.

Запишем исходные данные и формулу для расчета суммы выплат, как и в первом задании. Затем создаем таблицу с данными. Для этого пишем заголовки столбцов, затем - в ячейке С2 записываем формулу =В3, в ячейку В8 -формулу =В4, в ячейку С8 - формулу =В5. Для расчета процента от 6% до 11% с шагом 0,5% записываем формулу =A8+0,005 и копируем ее вниз по столбцу, пока не получим значение 11%. Затем выделяем диапазон ячеек A8:С10 щелкаем на пункте меню Данные → Таблица подстановки и в окошке Подставлять значения по строкам в записываем адрес ячейки с процентной ставкой:

После нажатия на ОК получим следующую таблицу:

Размер	180 800,00 грн.	180800
Срок вклада	6	6%
процент. Ставка	6%	0,06
коэффициент наращения	1,418519112	=B5/B1
Сумма выплаты	256 468,26 грн.	=БЗ(B3;B2;0;-B1;0)
процент. Ставка	Коэфф.	Сумма выплаты
6,0%	1,42	256 468,26 грн.
6,5%	1,46	263 812,93 грн.
7,0%	1,50	271 332,05 грн.
7,5%	1,54	279 028,92 грн.
8,0%	1,59	286 906,88 грн.
8,5%	1,63	294 969,33 грн.
9,0%	1,68	303 219,70 грн.
9,5%	1,72	311 661,49 грн.
10,0%	1,77	320 298,23 грн.
10,5%	1,82	329 133,50 грн.
11,0%	1,87	338 170,95 грн.

Задание № 4. Таблицы данных с двумя переменными

Вариант 8

№	Размер вклада	Срок вклада	Процентная ставка
8	152567	6	6,0%

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

Выделяем прямоугольную область начиная с формулы расчета суммы выплат – А7:Е18 и щелкаем на пункте меню Данные → таблица подстановки.

Проставляем адреса ячеек и нажимаем ОК.

Получим результат в виде таблицы данных:

Размер	152567	152567
Срок	6	6
Процент	6%	0,06
Коэффициент наращения	1,42 грн.	=B5/B1
Сумма выплаты	216 419,21 грн.	=БЗ(B3;B2;0;-B1;0)
216 419,21 грн.	2	3	5	7
6,0%	171424,3	181709,7	204169,1	229404,4
6,1%	171747,9	182224,5	205133,9	230923,6
6,2%	172071,8	182740,2	206102,5	232451,4
6,3%	172396,0	183256,9	207074,6	233987,9
6,4%	172720,5	183774,6	208050,5	235533,1
6,5%	173045,3	184293,3	209030,0	237087,1
6,6%	173370,4	184812,9	210013,2	238649,8
6,7%	173695,9	185333,5	211000,1	240221,3
6,8%	174021,6	185855,0	211990,7	241801,7
6,9%	174347,6	186377,6	212985,1	243391,0