Использование электронных таблиц MS EXCEL для решения экономических задач. Финансовый анализ в Excel (стр. 2 из 3)

- выберем пункт Данные – Сводная таблица. Появится окно Мастер сводных таблиц;

- укажем диапазон, содержащий исходные данные из таблицы (B4:D9);

- укажем «Поместить таблицу в новый лист» и нажмем кнопку Макет.

Макет сводной таблицы в первоначальном виде (рис.6):

Рисунок 6. – Макет сводной таблицы

3. Макет измененной сводной таблицы (рис.7):

Рисунок 7. – Макет измененной сводной таблицы

4. Структуру сводной таблицы изменим путем перетаскивания мышкой полей таблицы, которые располагаются справа, в нужные области диаграммы. Потом нажмем кнопку «Ок».

5. Измененная сводная таблица

Сумма по полю Стоимость перевозки	Поставляемое изделие	Расстояние
	Агрегат	Агрегат Всего	Зерно		Зерно Всего	Нефть		Нефть Всего	Общий итог
Тип транспорта	свыше 5000		1000-5000	до 1000		1000-5000	до 1000
воздух	18700	18700							18700
ж/д				7000	7000		10000	10000	17000
Морской			5400		5400	14000		14000	19400
Общий итог	18700	18700	5400	7000	12400	14000	10000	24000	55100

Задание № 3. Использование процедуры «Поиск решения»

1. Вариант 8.

Наименование	Расходы, гр/шт. (Р)	Кол-во, шт. (К)	Всего расходов, гр. (ВР)	Процент прибыли (ПП)	Прибыль (П)
Товар 1	780	6200		9%
Товар 2	3200	500		22%
Товар 3	160	3800		15%
Товар 4	1100	9100		13%
Товар 5	4500	800		33%
Товар 6	200	5600		23%
Итого	Х			Х
Предельные значения		36000	2700000	Х	Х

ВР=В*К П=ПП*ВР

2. Для расчета в таблице значений «Всего расходов» использовалась формула: =B4*C4 и далее аналогично по остальным видам товаров. Для расчета в таблице значений «Прибыль» использовалась формула: =E4*D4 и далее аналогично по остальным видам товаров. Для расчета суммарных значений количества товаров, расходов и прибыли использовалась функция СУММ(): Общее количество товаров: =СУММ(C4:C9).

3. Для определения оптимального плана производства выберем пункт Сервис – Поиск решения и в открывшемся диалоговом окне укажем необходимые ссылки (рис.8).

Рисунок 8. - Поиск решения

4. Таблица с результатами выполненной процедуры «Поиск решения»

Наименование	Расходы, гр/шт. (Р)	Кол-во, шт. (К)	Всего расходов, гр. (ВР)	Процент прибыли (ПП)	Прибыль (П)
Товар 1	780	0	0,00	9%	0,00
Товар 2	3200	0	0,00	22%	0,00
Товар 3	160	0	0,00	15%	0,00
Товар 4	1100	0	0,00	13%	0,00
Товар 5	4500	600	2700000,00	33%	891000,00
Товар 6	200	0	0,00	23%	0,00
Итого	Х	600	2700000	Х	891000
Предельные значения		36000	2700000	Х	Х

Задание № 4. Регрессионный анализ данных

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 грн.