Задача №1. Работа с финансовыми функциями
В пакете EXCEL встроены специальныефункции для проведения различных финансово-экономических расчетов.Осуществляется выбор функции с помощью кнопки «Вставка функции» напанели инструментов, категория — «Финансовые».
Определение будущей стоимости
Будущая стоимость текущего значения вклада при постояннойпроцентной ставке рассчитывается с помощью функции:
Б3 (норма; число_периодов; выплата; нз; тип),
где:
норма — процентная ставка за один период;
число _периодов — общее число периодов выплат;
выплата — это выплата, производимая в каждый период,это значение не может меняться в течение всего периода выплат, обычно выплатасостоит из основного платежа и платежа по процентам, но не включает другихналогов и сборов;
нз — текущая стоимость вклада (настоящее значение),если аргумент нз опущен, то он полагается равным 0;
тип — это число 0 или 1, обозначающее, когда производитсявыплата (1 — в начале периода, 0-в конце периода). Если аргумент тип опущен,то он полагается равным 0. Параметр тип нужно указывать толькотогда, когда выплата не равна 0, т.е. делаются взносы по периодам.
Задание 1.1. На сберегательный счет в конце каждого месяцавносятся обязательные платежи по 100 тыс. грн. Рассчитайте, какая суммаокажется на счете через восемь лет при ставке процента 9.5% годовых.
Решение:
Для расчета применяется формула БЗ, т.к требуется найтибудущее значение выплаченной суммы. В данной задаче при ежемесячном начислениипроцентов общее число периодов начисления равно 8*12 (аргумент число_периодов),а процент за период начисления равен 9,5%/12 (аргумент норма). Поусловию аргумент нз = — 100000. Это отрицательна сумма, т.к деньги быливложены. Выплаты отсутствую, поэтому аргумент выплата отсутствует. Используяфункцию БЗ, получим
Б3 (9,5%/12; 8*12;; — 1000000) = 14297518,58 грн.
Результаты решения задачи представлены в таблице 1.
Динамика роста стоимости показана в рисунке 2.
Таблица 3 содержит расчетные формулы к решению задачи впакете Microsoft Excel.
Таблица 1
Расчет будущей стоимости A B C D E F G 1 ЗАДАНИЕ №1 2 год ставка число периодов выплата вклад тип величина вклада 3 1 0,007917 12 -100000 1 253 653,69р. 4 2 0,007917 24 -100000 2 631 729,49р. 5 3 0,007917 36 -100000 4 146 575,97р. 6 4 0,007917 48 -100000 5 811 767,32р. 7 5 0,007917 60 -100000 7 642 224,88р. 8 6 0,007917 72 -100000 9 654 350,92р. 9 7 0,007917 84 -100000 11 866 175,62р. 10 8 0,007917 96 -100000 14 297 518,58р.
Рисунок 2
/>
Таблица 3. Расчет будущей стоимости A B C D E F G 1 ЗАДАНИЕ №1 2 год ставка число периодов выплата вклад тип величина вклада 3 1 =0,095/12 =12*A3 -100000 =БЗ (B3; C3; D3; 0; F3) 4 2 =0,095/12 =12*A4 -100000 =БЗ (B4; C4; D4; 0; F4) 5 3 =0,095/12 =12*A5 -100000 =БЗ (B5; C5; D5; 0; F5) 6 4 =0,095/12 =12*A6 -100000 =БЗ (B6; C6; D6; 0; F6) 7 5 =0,095/12 =12*A7 -100000 =БЗ (B7; C7; D7; 0; F7) 8 6 =0,095/12 =12*A8 -100000 =БЗ (B8; C8; D8; 0; F8) 9 7 =0,095/12 =12*A9 -100000 =БЗ (B9; C9; D9; 0; F9) 10 8 =0,095/12 =12*A10 -100000 =БЗ (B10; C10; D10; 0; F10)
Определение текущей стоимости.
Для расчета текущей стоимости (начальное значение) вклада (займа)используется функция
П3 (норма; Кпер; выплата; бс; тип),
где:
норма — процентная ставка за один период;
Кпе — общее число периодов выплат;
выплата — это выплата, производимая в каждый периода
бс — будущая стоимость вклада, которую нужно достичьпосле последней выплаты, если аргумент бс опущен, то он полагаетсяравным 0;...
тшп — это число 0 или 1, обозначающее, когдапроизводится выплата (1 — в начале периода, 0 — в конце периода), если аргументпшп опущен, то он полагается равным 0. Параметр mипнужно указывать, только если выплата не равна 0, т.е. делаются взносы попериодам.
Задание 1.2 Какую сумму необходимо положить на депозит под16% годовых, чтобы получить через четыре года 25 млн. грн. при ежеквартальномначислении процентов?
Решение
Для расчета используем функцию ПЗ.
При этом норма = 16%, Кпер =4, выплата = 2500000 грн., бс =0.
П3 (16; 4; 2500000;) = — 13 347 704,39р. грн.
Результаты решения задачи представлены в таблице 4. Динамикароста стоимости показана в рисунке 5. Таблица 6 содержит расчетные формулы крешению задачи в пакете Microsoft Excel.
Таблица 4
Текущая стоимость A B C D E F G 31 ЗАДАНИЕ №2 32 год ставка число периодов выплата вклад тип величина вклада 33 1 16% 4 25000000 -21370104,78р. 34 2 16% 8 25000000 -18267255,13р. 35 3 16% 12 25000000 -15614926,24р. 36 4 16% 16 25000000 -13347704,39р.
Рисунок 5
/>
Таблица 6
Текущая стоимость A B C D E F G 28 ЗАДАНИЕ №2 29 год ставка число периодов выплата вклад тип величина вклада 30 1 16% =4*A30 25000000 =ПЗ (B30/4; C30; D30; E30; F30) 31 2 16% =4*A31 25000000 =ПЗ (B31/4; C31; D31; E31; F31) 32 3 16% =4*A32 25000000 =ПЗ (B32/4; C32; D32; E32; F32) 33 4 16% =4*A33 25000000 =ПЗ (B33/4; C33; D33; E33; F33) Задача № 2. Построение экономической модели вида y=f (x)
Цель экономического регрессионного анализа — на основесобранных статистических данных, которые представлены в виде таблицы, найтиэкономическую модель и произвести на основе этой модели соответствующиеэкономические расчеты и прогнозы.
Для построения корреляционного поля необходимо выполнитьследующие действия:
Открыть рабочее окно EXCEL и ввестизначения данных х и у.
Построить точечную диаграмму.
Выполнить пункты меню Диаграмма — Добавить линию тренда.На вкладке Тип выбрать тип диаграммы, (линейная, логарифмическая, полиноминальная,степенная, экспоненциальная).
Обратить внимание на то, что в различных вариантахзависимость может быть любого из перечисленных видов. Далее выбрать вкладкуПараметры и поставить " ٧ " в окне Показать уравнение надиаграмме.
Сделать вывод о виде принятой гипотезы.
Задание. Произвести экономический анализ для заданныхстатистических данных. Сделать выбор.X 5,21 5,61 6,12 6,61 7,01 7,59 7,98 8,48 8,99 10,49 Y 13,4 14,12 15,34 16,52 17,02 17,78 19,06 19,96 20,78 23,98
Решение
Выполняемпостроение точечной диаграммы и добавляем линию трейда с различными типамидиаграммы:
/> - линейная – логарифмическая
- полиноминальная – степенная, экспоненциальная
/>
/>
Вывод: проанализировав величину коэффициента достоверностиаппроксимации R2 для каждого типазависимости можно сделать вывод, что исходные экономические данные можноаппроксимировать с наибольшей точностью линейной зависимостью y = 1,9844x +3,0873 и полиномиальной зависимостью у = 0,0029x2 + +1,9396x + 3,2537, так какR2 = 0,99966.Задача №3. Модель Леонтьева многоотраслевой экономики (балансовый анализ)
Одной из основных задач, возникающих в макроэкономике,является задача, связанная с эффективностью ведения многоотраслевого хозяйства;каким должен быть объем производства каждой из nотраслей, чтобы удовлетворить все потребности в продукции этой отрасли. Приэтом каждая отрасль выступает, с одной стороны, как производитель некоторойпродукции, а с другой — как потребитель продукции и своей, и произведеннойдругими отраслями.
Введем следующие обозначения:
/>-вектор валовоговыпуска;
хy — объем продукции i-йотрасли, потребляемой j-й отраслью в процессе производства;
/>-вектор конечногопродукта;
/> - матрица прямыхзатрат, коэффициенты прямых затрат вычисляются по формуле />.
Основная задача межотраслевого баланса — отыскание такоговектора валового выпуска />,который при известной матрице прямых затрат А обеспечивает заданный векторконечного продукта />.
Матричное решение данной задачи:
/>
Работа с матрицами s пакете Excel
В пакете Excel существует несколькофункций для работы с матрицами:
ТРАНСП — транспонирование матрицы;
МОПРЕД — нахождение определителя матрицы;
МУМНОЖ — умножение матриц;
МОБР — нахождение обратной матрицы.
Все эти функции (кроме ТРАНСП) находятся в категории «Математические»,функция ТРАНСП — в категории «Ссылки и массивы».
Для работы с матрицами необходимо сделать следующее:
1 Выделить блок ячеек, в который нужно поместить результат.
2 Выбрать Вставка функции, найти нужную функцию.
3 Ввести адрес (или адреса) исходной матрицы (непосредственноили курсором). Нажать кнопку «ОК».
Для того, чтобы получить на экране все значения результата,нажать клавиши F2 и одновременно Ctrl+Shift+Enter.
Задание
Связь между тремя отраслями представлена матрицей прямыхзатрат А. Спрос (конечный продукт) задан вектором Y.Найти валовой выпуск продукции отраслей Х. Описать используемые формулы, представитьраспечатку со значениями и формулами.
Решение:
1. Вводим исходные данные в ячейки пакета Excel.Матрицу прямых затрат А вводим в ячейки (B2: D4), матрицу спроса /> вячейки (G2: G4).
/>/>
2. Определим матрицу прямых затрат />. Вначале найдем матрицу (Е-А).
Где Е — единичная матрица,
/>.
/>/>.
Вводим в ячейки (B6: D8) единичную матрицу. Матрицу (Е-А) посчитаем в ячейках (B13: D15) по формуле
/>.
3. Для вычисления обратной матрицы, сначала вычислим определитель.
Для этого выставляем курсор в ячейку, где будет определитель(G14), вызываем Вставку функции, в категории «Математические»выбираем функцию нахождения определителя матрицы МОПРЕД, вводим адрес матрицыМОПРЕД (В13: D15) и нажимаем «ОК». В ячейке G14 появляется значение определителя матрицы.
/>.
4. Для нахождения обратной матрицы используем математическуюфункцию МОБР. Обратную матрицу /> находимфункцией МОБР:
/>.
Для этого выделяем блок ячеек, где должна находится обратнаяматрица (B17: D19), вызываемВставку функции, в категории «Математические» выбираем функциюнахождения обратной матрицы МОБР, вводим адрес матрицы MOBP(B13: D15), нажимаем «ОК».Для получения на экране значения коэффициентов обратной матрицы, нажимаемклавиша F2 и Ctrl+Shift+Enter одновременно.
5. Вектор валового выпуска определяется по формуле />, Находим вектор решенийсистемы уравнений умножением обратной матрицы на вектор-столбец />, используя встроеннуюматематическую функцию МУМНОЖ:
/>.
Для этого выделяем блок, где будет находится вектор /> - (G17:G19). Вызываем Вставку функции в категории«Математические», выбираем функцию МУМНОЖ, вводим адрес обратнойматрицы (B17: D19) и вектора Y(G2: G4):
МУМНОЖ (B17: D19;G2: G4), нажимаем «ОК»Для получения на экране значения решения, нажимаем клавиша F2 и Ctri+Shift+Enter одновременно.
В результате решения было определено, что для удовлетворенияспроса необходимо произвести продукции в1-й, 2-й и 3-й отраслях на 100, 100 и90 д. е. соответственно. Затраты (отрасли) Выпуск (потребление) Конечный продукт Валовой выпуск 1 2 3 1 0,05 0.15 0,4 44 100 2 0,1 0.1 0,3 53 100 3 0,3 0,15 0,2 27 90 A B C D E F G 1 РАСЧЕТ ВАЛОВОГО ВЫПУСКА ПРОДУКЦИИ 2 0,05 0,15 0,4 44 3 А= 0,1 0,1 0,3 Y= 53 4 0,3 0,15 0,2 27 5 6 1 7 Е= 1 8 1 9 10 11 Решение задачи 12 13 0,95 -0,15 -0,4 14 E-A= -0,1 0,9 -0,3 D= 0,50175 15 -0,3 -0,15 0,8 16 17 1,34529148 0,358744 0,807175 100 18 E-A (-1) = 0,33881415 1,275536 0,647733 (E-A) (-1) *Y= 100 19 0,56801196 0,373692 1,674141 90 /> /> /> /> /> /> /> /> /> /> /> A B C D E F G 1 РАСЧЕТ ВАЛОВОГО ВЫПУСКА ПРОДУКЦИИ 2 0,05 0,15 0,4 44 3 А= 0,1 0,1 0,3 Y= 53 4 0,3 0,15 0,2 27 5 6 1 7 Е= 1 8 1 9 10 11 Решение задачи 12 13 =B6-B2 =C6-C2 =D6-D2 14 E-A= =B7-B3 =C7-C3 =D7-D3 D= =МОПРЕД (B13: D15) 15 =B8-B4 =C8-C4 =D8-D4 16 17 =МОБР (B13: D15) =МОБР (B13: D15) =МОБР (B13: D15) =МУМНОЖ (B17: D19; G2: G4) 18 E-A (-1) = =МОБР (B13: D15) =МОБР (B13: D15) =МОБР (B13: D15) (E-A) (-1) *Y= =МУМНОЖ (B17: D19; G2: G4) 19 =МОБР (B13: D15) =МОБР (B13: D15) =МОБР (B13: D15) =МУМНОЖ (B17: D19; G2: G4) Задача № 4
В опытном хозяйстве установили, что откорм животных возможентогда, когда животное будет получать вещества А не менее 10 ед., вещества В — не менее 12 ед. и вещества С — не менее 4 ед. Для кормления животногоиспользуются два вида корма. В 1 кг корма первого вида содержится 2, 2 и 0единиц питательных веществ соответственно. В 1 кг корма второго вида содержится1, 3, 2 единицы питательных веществ соответственно. Цена 1 кг корма первоговида равна 50 д. е., корма второго вида — 60 д. е. Сколько корма каждого виданужно расходовать ежедневно, чтобы затраты на него были минимальными?
Решение:
1. Формализация задачи.
Обозначим:
количество корма 1-го вида через x1;
количество корма 2-го вида через x2;
Тогда целевая функция — затраты на корм — равна:
z=50x1+60x2
Соотношение количества вещества А в дневном рационе недолжно быть меньше 10 д. е., т.е.
2x1+1x2≥10
Соответственно для вещества В и вещества С
2x1+3x2≥12
0x1+2x2≥4
Так как x1 и x2 — количество продукта, то справедливо
x1≥0
x2≥0
Полученная математическая модель задачи о смесях:
{ z=50x1+60x2 (min)
2x1+1x2≥10
2x1+3x2≥12
0x1+2x2≥4
x1≥0
x2≥0
2. Точное (алгебраическое) решение формализованной задачи.
Поскольку граничные условия, содержащие оба аргумента,представлены тремя уравнениями, решаются две системы, каждая из которых состоитиз двух уравнений с двумя неизвестными.
Система уравнений I: {
2x1+1x2≥10 [1]
0x1+2x2≥4 [2]
из [2] x2=2; тогда из [1] x1=4, Системауравнений II: {
2x1+3x2≥12 [3]
0x1+2x2≥4 [4]
из [4] x2=2; тогда из [3] x1=3, Принимаем x1=4, x2=2, поскольку значение x1=3 не удовлетворяет неравенство 2x1+1x2≥10
3. Графическоерешение формализованной задачи.
Строим область, являющуюсяпересечением всех плоскостей математической модели полученной при формализациизадачи (см. черт.1).
Находим градиент функции z: gradz= {50; 60}. Строим вектор с началом в т. (0; 0) и концом в точке (50; 60). Определяемзону допустимых решений. Для этого строим линии ограничений, приравнивая междусобой левые и правые части уравнений и определяя значения точек пересечениялиний ограничения с осями Х1 и Х2, присваивая значения равные 0:
2x1+1x2=10; x1=0, x2=10/x1=5, x2=0, 2x1+3x2=12; x1=0, x2=4/x1=6, x2=0
0x1+2x2=4;x2=2, x1=0, x2=0
Строим прямую, перпендикулярнуювектору градиента. Передвигаем эту прямую в направлении, указанном вектором.Самая последняя точка, которую пересекает прямая, и есть точка максимума.
/>
Рисунок 1 — Графическое решениеформализованной задачи
4. Решениезадачи с помощью пакета Excel.
Для решения данной задачилинейного программирования в пакете Excel воспользуемся помощью пункта меню Сервис, пункт Поискрешения.
Прежде, чем воспользоваться этойпрограммой, введем исходные данные:
1. Вячейки C3 и D3 вводим значения точки максимума соответственно.
2. Вводимкоэффициенты целевой функции 50 и 60 в ячейки C6 и D6соответственно.
3. Вячейку F6 вводим формулу для вычисления целевой функции. Для этоговызываем Вставка функции — «Математические» — СУММПРОИЗВ и вводимячейки C$3: D$3 и C6: D6. Формат функции; =СУММПРОИЗВ (С$3: 0$3; С6: D6).
4. Вячейки C4: D4 вводим нижние границы равные 0. Нижняя границапоказывает, что переменные не отрицательные.
5. Вводимкоэффициенты системы ограничений в ячейки C10: D12.
6. Вводимправые части системы ограничений в ячейки Н10: Н12.
7. Вячейку F10 вводим формулу расчета выполнения ограничений=СУММПРОИЗВ (С$3: D$3; C10: DО). Копируем эту формулу в ячейки F11, F12.
8. Вячейку I10 вводим формулу расчета неиспользованных ресурсов =H10-F10. Копиру емэту формулу в ячейки I11, I12
После ввода исходных данныхвызываем программу Поиск решения из пункта меню Сервис.
В окно Поиска решения вводимзначения в ячейках:
1. Вводим$F$6в окно «Установить целевую ячейку», выставляем ее «Равнойминимальному значению».
2. В окошко «Изменяя ячейки»вводим $C$3: $D$3.
3. Вокошке «Ограничения» выбираем пункт «Добавить»
«Ссылка на ячейку» — СЗ,знак — >=, «Ограничение» — С4. Появляется ограничение:
$С$3>=$С$4. Аналогично вводим:
$D$3>=$D$4;
$F$10>=$H$10;
$F$11>=$H$11;
$F$12>=$H$12
4. После этого нажимаем «Выполнить»,далее Тип отчета — «Результаты».
Получаем решение в ячейках СЗ и D3- значения переменных, в ячейках F6 — значение целевой функции, в ячейках F10: F12 — значенияограничений к в ячейках I10: I12 — разницу между исходными ресурсами и использованными. A B C D E F G H I 1 Переменные 2 X1 X2 3 Значения 4 2 4 Ниж. граница 5 Верхн. граница 6 F 50 60 320 max 7 Коэффициенты целевой ф-ции 8 9 Коэф-ты Значение Факт. ресурсы Неисп. ресурсы 10 Сис-ма ограничений 2 1 10 >= 10 11 2 3 14 >= 12 -2 12 2 4 >= 4 /> /> /> /> /> /> /> /> /> /> /> A B C D E F G H I 1 Переменные 2 X1 X2 3 Значения 4 2 4 Ниж. граница 5 Верхн. граница 6 F 50 60 =СУММПРОИЗВ (C3: D3; C6: D6) max 7 Коэффициенты целевой ф-ции 8 9 Коэф-ты Значение Факт. ресурсы Неисп. ресурсы 10 Сис-ма огранич 2 1 =СУММПРОИЗВ (C3: D3; C10: D10) >= 10 =H10-F10 11 2 3 =СУММПРОИЗВ (C3: D3; C11: D11) >= 12 =H11-F11 12 2 =СУММПРОИЗВ (C3: D3; C12: D12) >= 4 =H12-F12 Экономический вывод
Для минимизации затрат при ежедневном расходе необходимовключат в рацион 4 кг первого вида и 2 кг второго вида кормов. при этом врацион необходимо вносить:
Вещества А — 10 ед. при фактическом 10 ед.
Вещества В — 14 ед. при фактическом 12 ед.
Вещества С — 4 ед. при фактическом 4 ед.
Вещество В является недостаточным