Лист-задание
Часть І. Использование электронных таблиц MSEXCEL для решения экономических задач
Задание №1. Созданиетаблиц, расчет по формулам, построение диаграмм
Порядок выполнения:
1. Согласно вариантусоздать на рабочем листе таблицу
2. Ввести требуемыев задании данные для расчета
3. Отформатироватьтаблицу (выделить полужирным шрифтом заголовок таблицы, итоговые показатели ит.д.)
4. Провести расчеты:
ü по формулам рассчитать необходимыепоказатели, при необходимости использовать абсолютную адресацию ячеек;
ü при помощи копирования заполнитьпоследующие ячейки таблицы;
ü при помощи Мастера функций рассчитатьтребуемые статистические показатели представленной таблицы (минимум, максимум,среднее значение)
5. Построитьдиаграммы:
ü по результатам расчетов построитьгистограмму с указанием ее названия, наименования строк и столбцов
ü круговую по данным одного столбца(или одной строки)
В пояснительной записке кЗаданию №1 контрольной работы указать:
1. Таблицу сисходными данными (вариант задания)
2. Формулы excel со ссылками на ячейки, по которымпроизводится расчет
3. Таблицу срезультатами расчетов
4. Этапы построения диаграмм
5. Построенныедиаграммы
Задание № 2. Подведениединамических итогов с использованием сводных таблиц
Порядок выполнения
1. Согласно вариантусоздать на рабочем листе исходную таблицу
2. Построить своднуютаблицу (выделить исходную таблицу и выбрать пункт меню «данныеàсводная таблица»)
3. Изменитьструктуру сводной таблицы (выбрав пункт «макет» мастера создания сводныхтаблиц)
В пояснительной записке кЗаданию №2 контрольной работы указать:
1. Исходную таблицу(вариант задания)
2. Этапы построениясводной таблицы (в обязательном порядке макет сводной таблицы)
3. Полученнуюсводную таблицу (в обязательном порядке макет измененной сводной таблицы)
4. Порядок измененияструктуры сводной таблицы
5. Измененнуюсводную таблицу
Задание № 3.Использование процедуры «Поиск решения»
Ставится задачаопределить оптимальный план производства (найти такое количество товаровкаждого вида) таким образом, чтобы суммарная прибыль была максимальной ивыполнялись ограничения: общее количество товаров и суммарные расходы не должныпревышать предельных значений.
Порядок выполнения:
1. На рабочем листесоздать таблицу с данными о товарах, согласно варианту, и провести расчет поприведенным формулам
2. Запустить накомпьютере процедуру «Поиск решения» (меню сервисà Поиск решения)
3. В открывшемсядиалоговом окне указать необходимые ссылки:
ü целевая ячейка — суммарная прибыль(максимальное значение). Эта ячейка должна содержать формулу
/>
ü изменяя ячейки – кол-во товаровкаждого вида; нужно указать диапазон ячеек, в которых после выполненияпроцедуры «Поиск решения » будет получено значения оптимальных выпусков.
ü ПРИМЕЧАНИЕ: этот диапазониспользуется для получения формул в целевой ячейке и ячейках ограничений;
ü ограничения – кол-во товаров целые иположительные, суммарные расходы и общее кол-во товаров не должны превышатьпредельных значений.
4. Выполнитьпроцедуру «Поиск решения»
В пояснительной записке кЗаданию №3 контрольной работы указать:
· таблицу с данными о товарах
· провести расчет по формулам
· указать параметры процедуры «Поискрешения» с адресами ячеек (диалоговое окно процедуры Поиск решения)
таблицу с результатамивыполненной процедуры «Поиск решения»
Задание № 4.Регрессионный анализ данных
Найти уравнениеэмпирической зависимости в виде: Y = a X + b ипостроить ее график по данным таблицы
Порядок выполнения:
1. На рабочем листесоздать таблицу согласно варианту
2. Построитьточечную диаграмму
3. Добавить надиаграмму линию тренда, указывая тип зависимости линейная и отображая уравнениена диаграмме.
В пояснительной записке кЗаданию №4 контрольной работы указать:
1. Исходную таблицу(вариант задания)
2. Диаграмму и этапыее построения
3. Добавление линиитренда
4. Уравнение линиитренда
ЧастьІІ. Финансовый анализ вExcel
Задание №1
Известен размер вклада,который помещен на определенный срок под заданный процент. Вычислитькоэффициент наращения и сумму выплат в конце периода.
Задание № 2
Используя функцию “Подборпараметра” создать систему для начисления сложных процентов при заданной суммевыплаты.
Задание №3. Таблицыподстановки с одной переменной
Используя функцию“Таблицы подстановки” создать систему для прогнозирования суммы выплаты привариации процентной ставки.
Задание № 4. Таблицыданных с двумя переменными
Используя функцию“Таблицы подстановки” создать систему для прогнозирования суммы выплаты привариации процентной ставки и суммы вклада.
Пояснительнаязаписка
Часть І. Использование электронных таблиц MSEXCEL для решения экономических задач
Задание №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).
/>
Рисунок 1. –Мастер функций
– из списка функций выберем СРЗНАЧ инажмем кнопку «Ок». В поле «Число 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,00 9% 0,00 Товар 2 3200 0,00 22% 0,00 Товар 3 160 0,00 15% 0,00 Товар 4 1100 0,00 13% 0,00 Товар 5 4500 600 2700000,00 33% 891000,00 Товар 6 200 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 и щелкаем на пункте менюДанные → таблица подстановки.
/>
Проставляем адреса ячееки нажимаем ОК.
Получим результат в видетаблицы данных:Размер 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