- по формулам рассчитать необходимые показатели, при необходимости использовать абсолютную адресацию ячеек;
- при помощи копирования заполнить последующие ячейки таблицы;
- при помощи Мастера функций рассчитать требуемые статистические показатели представленной таблицы (минимум, максимум, среднее значение)
5. Построить диаграммы:
- по результатам расчетов построить гистограмму с указанием ее названия, наименования строк и столбцов
- круговую по данным одного столбца (или одной строки)
В пояснительной записке к Заданию №1 контрольной работы указать:
1. Таблицу с исходными данными (вариант задания)
2. Формулы excel со ссылками на ячейки, по которым производится расчет
3. Таблицу с результатами расчетов
4. Этапы построения диаграмм
5. Построенные диаграммы
Задание № 2. Подведение динамических итогов с использованием сводных таблиц
Порядок выполнения
1. Согласно варианту создать на рабочем листе исходную таблицу
2. Построить сводную таблицу (выделить исходную таблицу и выбрать пункт меню «данные-сводная таблица»)
3. Изменить структуру сводной таблицы (выбрав пункт «макет» мастера создания сводных таблиц)
В пояснительной записке к Заданию №2 контрольной работы указать:
1. Исходную таблицу (вариант задания)
2. Этапы построения сводной таблицы (в обязательном порядке макет сводной таблицы)
3. Полученную сводную таблицу (в обязательном порядке макет измененной сводной таблицы)
4. Порядок изменения структуры сводной таблицы
5. Измененную сводную таблицу
Задание № 3. Использование процедуры «Поиск решения»
Ставится задача определить оптимальный план производства (найти такое количество товаров каждого вида) таким образом, чтобы суммарная прибыль была максимальной и выполнялись ограничения: общее количество товаров и суммарные расходы не должны превышать предельных значений.
Порядок выполнения:
1. На рабочем листе создать таблицу с данными о товарах, согласно варианту, и провести расчет по приведенным формулам
3. В открывшемся диалоговом окне указать необходимые ссылки:
- целевая ячейка - суммарная прибыль (максимальное значение). Эта ячейка должна содержать формулу
- изменяя ячейки – кол-во товаров каждого вида; нужно указать диапазон ячеек, в которых после выполнения процедуры «Поиск решения » будет получено значения оптимальных выпусков.
- ПРИМЕЧАНИЕ: этот диапазон используется для получения формул в целевой ячейке и ячейках ограничений;
- ограничения – кол-во товаров целые и положительные, суммарные расходы и общее кол-во товаров не должны превышать предельных значений.
4. Выполнить процедуру «Поиск решения»
В пояснительной записке к Заданию №3 контрольной работы указать:
таблицу с результатами выполненной процедуры «Поиск решения»
Задание № 4. Регрессионный анализ данных
Найти уравнение эмпирической зависимости в виде: Y = a X + b и построить ее график по данным таблицы
Порядок выполнения:
1. На рабочем листе создать таблицу согласно варианту
2. Построить точечную диаграмму
3. Добавить на диаграмму линию тренда, указывая тип зависимости линейная и отображая уравнение на диаграмме.
В пояснительной записке к Заданию №4 контрольной работы указать:
1. Исходную таблицу (вариант задания)
2. Диаграмму и этапы ее построения
3. Добавление линии тренда
4. Уравнение линии тренда
Часть ІІ. Финансовый анализ в Excel
Задание №1
Известен размер вклада, который помещен на определенный срок под заданный процент. Вычислить коэффициент наращения и сумму выплат в конце периода.
Задание № 2
Используя функцию “Подбор параметра” создать систему для начисления сложных процентов при заданной сумме выплаты.
Задание №3. Таблицы подстановки с одной переменной
Используя функцию “Таблицы подстановки” создать систему для прогнозирования суммы выплаты при вариации процентной ставки.
Задание № 4. Таблицы данных с двумя переменными
Используя функцию “Таблицы подстановки” создать систему для прогнозирования суммы выплаты при вариации процентной ставки и суммы вклада.
Пояснительная записка
Часть І. Использование электронных таблиц MS EXCEL для решения экономических задач
Задание №1. Создание таблиц, расчет по формулам, построение диаграмм
1. Вариант 8. Учет затрат на вспомогательные материалы
Наименование материала
Цена (Ц)
Количество
Сумма на конец месяца (С)
Доля в общем объеме (Д)
На начало месяца (НМ)
На конец месяца (КМ)
Израсходовано (И)
Краска
12,50
90
50
Лак
28,30
60
10
Бензин
2,00
140
60
Керосин
1,80
90
30
Эмаль
16,30
75
80
Ацетон
11,80
60
15
Итого
Х
Х
Х
Х
Средняя сумма расхода
Х
Минимальная доля в общем объеме
И=КМ-НМ С=Ц*КМ Д=С/(Итого С) * 100
2. Расчеты в таблице производились по следующим формулам Еxcel со ссылками на ячейки:
- Израсходовано (И): Е6 =D6-C6;
- Сумма на конец месяца (С): F6 =B6*D6;
- Доля в общем объеме (Д): G6 =(B6/F6)*100;
Для расчета средней суммы расхода используем функцию Еxcel, которая возвращает среднее значение списка значений. Для этого выполним следующие действия:
- из списка функций выберем СРЗНАЧ и нажмем кнопку «Ок». В поле «Число 1» укажем диапазон ячеек, среди значений которых нужно найти среднее. Формула будет иметь вид: G13=СРЗНАЧ(F6:F11)
Аналогично рассчитаем минимальную долю в общем объеме, воспользуясь функцией МИН. Формула будет иметь вид: G14= =МИН(G6:G11)
3. Таблица с результатами расчетов:
Наименование материала
Цена (Ц)
Количество
Сумма на конец месяца (С)
Доля в общем объеме (Д)
На начало месяца (НМ)
На конец месяца (КМ)
Израсходовано (И)
Краска
12,5
90
50
-40
625
2,00
Лак
28,3
60
10
-50
283
10,00
Бензин
2
140
60
-80
120
1,67
Керосин
1,8
90
30
-60
54
3,33
Эмаль
16,3
75
80
5
1304
1,25
Ацетон
11,8
60
15
-45
177
6,67
Итого
Х
Х
Х
Х
2563
Средняя сумма расхода
427,17
Минимальная доля в общем объеме
1,25
4. Этапы построения диаграмм
Для построения диаграммы выполним следующие действия:
- укажем диапазон ячеек (F5:G10), по которым нужно построить диаграмму, выберем пункт Вставка – Диаграмма Откроется окно «Мастер диаграмм» (рис.2);
Рисунок 2. - Мастер диаграмм
- выберем тип Гистограмма и нажмем кнопку Далее. В закладке Ряд в поле Ряд напишем названия рядов 1 и 2 и нажмем кнопку Готово;
- из пунктов меню выберем Параметры диаграммы, откроется окно (рис.3);
- выберем закладку Заголовки и укажем название, наименование строк и столбцов диаграммы.
Рисунок 3. - Параметры диаграммы
5. Построенная гистограмма будет иметь вид:
Рисунок 4. - Гистограмма
Для построения круговой гистограммы выберем пункт меню Вставка – Диаграмма и тип Круговая, после чего нажмем кнопку Готово. Аналогичным образом укажем название круговой диаграммы (рис.5).
Рисунок 5. – Круговая диаграмма
Задание №2. Подведение динамических итогов с использованием сводных таблиц
1. Вариант 8.
Поставляемое изделие
Тип транспорта
Расстояние
Стоимость перевозки
Нефть
Морской
1000-5000
14000
Зерно
ж/д
до 1000
7000
Агрегат
воздух
свыше 5000
3100
Нефть
ж/д
до 1000
10000
Зерно
морской
1000-5000
5400
агрегат
воздух
свыше 5000
15600
2. Этапы построения сводной таблицы:
- выберем пункт Данные – Сводная таблица. Появится окно Мастер сводных таблиц;
- укажем диапазон, содержащий исходные данные из таблицы (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 грн.
Для решения данной задачи можно воспользоваться финансовой функцией ППЛАТ (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 и щелкаем на пункте меню Данные → таблица подстановки.