Загрузка...
Учебная работа. Контрольная работа: Использование Excel для решения статистических задач
Кафедра прикладной арифметики
КОНТРОЛЬНАЯ РАБОТА
по дисциплине «Информатика»
2007
15.1
Вклад размером 500 тыс. грн. положен под 12% годичных. Высчитайте, какая сумма будет на сберо счете через 6 лет, если проценты начисляются любые полгода
15.2
Найти текущую стоимость обыденных каждогодних платежей размером 20 тыс. грн. в течение 3-х лет при начислении 16% годичных.
Таблица 1 – Статистические данные
X
1,01
1,51
2,02
2,51
3,01
3,49
3,98
4,48
4,99
5,49
Y
5,02
5,92
7,14
8,32
9,02
9,58
11,06
11,96
12,78
13,98
Вариант 15
Коммерческий магазин желает закупить овощи А и В. Количество овощей, закупочные цены и цены, по которым магазин реализует овощи, приведены в таблице 8.
Таблица 8
Овощи
Цены
количество овощей
Закупка
Реализация
А
1,6
2,4
60
В
1,7
2,2
70
Как прибыльнее вложить средства, если общая сумма, которой размещается магазин в данное время, составляет 180 д.е., при этом овощей А необходимо приобрести не наименее 10 тонн.
задачка №1
15.1
Вклад размером 500 тыс.грн. положен под 12% годичных. Высчитайте, какая сумма будет на сберо счете через 6 лет, если проценты начисляются любые полгода
Для расчета текущей цены вклада будем употреблять функцию
БЗ (норма; число_периодов; выплата; нз; тип),
где норма
– процентная ставка за один период. В нашем случае
величина нормы составляет 13% годичных.
число периодов
– общее число периодов выплат. В нашем случае
данная величина составляет 6 лет.
выплата
– выплата, производимая в любой период. В нашем
случае данная величина полагается равной -100000.
нз
– текущая стоимость вклада. Равна 0.
тип
– данный аргумент можно опустить (равен 0).
Получим последующее выражение БЗ (12/2; 12; 0; – 500; 0) = 1006.10 тыс. грн.
Расчет будущей цены вклада по годам приведен в таблице 3.
Таблица 3 – Расчет грядущего вклада
РАСЧЕТ ТЕКУЩЕГО ВКЛАДА
ГОД
СТАВКА
ЧИСЛО
ВЫПЛАТА
ВКЛАД, тыс. грн
ТИП
ВЕЛИЧИНА
(ГОД)
ПЕРИОДОВ
ВКЛАДА, тыс. грн
1
12%
2
0
-500
0
561.80
2
12%
4
0
-500
0
631.24
3
12%
6
0
-500
0
709.26
4
12%
8
0
-500
0
796.92
5
12%
10
0
-500
0
895.42
6
12%
12
0
-500
0
1006.10
гистограмма, отражающая динамику роста вклада по годам представлена ниже.
Набросок 1 – Динамика роста вклада по годам
Вывод:
Расчеты демонстрируют, что на счете через 6 лет будет 1006.10 тыс. грн.
15.2
Найти текущую стоимость обыденных каждогодних платежей размером 20 тыс. грн. в течение 3-х лет при начислении 16% годичных.
Для расчета используем функцию
ПЗ (норма; Кпер; выплата; бс; тип),
где норма
= 16% – процентная ставка за один период;
Кпер
= 3 – общее число периодов выплат;
выплата
= 20 тыс. грн. – Каждогодние платежи;
При всем этом:
ПЗ (16%; 3; 20) = – 44,92 тыс. грн.
Итог вышел отрицательный, так как это сумма, которую нужно вложить.
Вывод:
Таковым образом при данных критериях текущая стоимость вклада составляет 44,92 тыс. грн.
1.2.
Произвести экономический анализ для данных статистических данных и прийти к выводу.
Таблица 4 – Данные статистические данные
X
1,01
1,51
2,02
2,51
3,01
3,49
3,98
4,48
4,99
5,49
Y
5,02
5,92
7,14
8,32
9,02
9,58
11,06
11,96
12,78
13,98
1. Вводим значения X и Y, оформляя таблицу;
2. По данным таблицы строим точечную диаграмму (см. набросок 2);
3. Выполнив пункты меню Диаграмма – Добавить линию тренда, получаем линию тренда (см. набросок 2);
Из вероятных вариантов типа диаграммы (линейная, логарифмическая, полиномиальная, степенная, экспоненциальная), избираем линейную зависимость, т. к. она обеспечивает меньшее отклонение от данных значений параметра Y.
y = 1.9733
x + 3.0667
– уравнение зависимости;
R2
= 0.9962
– величина достоверности аппроксимации;
4. Для обоснования изготовленного выбора оформим таблицу 5 – сравнительный анализ принятых и данных значений параметра Y.
В данной для нас таблице:
Y1
–
Y–значение параметра Y, согласно данным данным.
ε – величина арифметического отличия ε = Y- Y1
;
Набросок 2 – график зависимости у=f(x)
Таблица 5 – Сравнительный анализ данных и принятых значений Y
X
1.01
1.51
2.02
2.51
3.01
3.49
3.98
4.48
4.99
5.49
Y
5.02
5.92
7.14
8.32
9.02
9.58
11.06
11.96
12.78
13.98
Y1
5.06
6.05
7.05
8.02
9.01
9.95
10.92
11.91
12.91
13.90
E
-0.04
-0.13
0.09
0.30
0.01
-0.37
0.14
0.05
-0.13
0.08
Вывод:
На базе собранных статистических данных, представленных в таблице находим экономическую модель – принятая догадка имеет степенную зависимость и выражается уравнением
y = 1.9733
x + 3.0667
Экономическое прогнозирование на базе уравнения данной зависимости различается достоверностью в области исходных значений параметра X– величина ε воспринимает малые значения и некорректностью в длительном периоде – в области конечных значений параметра X.
7.
Связь меж 3-мя отраслями представлена матрицей прямых издержек А. Спрос (конечный продукт) задан вектором X. Отыскать валовой выпуск продукции отраслей Х. Обрисовать применяемые формулы, представить распечатку со значениями и с формулами.
Данная задачка связана с определением размера производства каждой из N отраслей, чтоб удовлетворить все потребности в продукции данной отрасли. При всем этом любая ветвь выступает и как производитель некой продукции и как пользователь собственной и произведенной иными отраслями продукции. задачка межотраслевого баланса – отыскание такового вектора валового выпуска X, который при известной матрице прямых издержек обеспечивает данный вектор конечного продукта Y.
Матричное решение данной задачки:
X
= (
E
—
A
)-1
Y
. [2]
Из имеющихся в пакете Excel функций для работы с матрицами при решении данной задачки будем употреблять последующие:
1. МОБР – нахождение оборотной матрицы;
2. МУМНОЖ – умножение матриц;
3. МОПРЕД – нахождение определителя матрицы;
Также при решении данной задачки употребляли сочетание кнопок:
F2 CTRL + SHIFT + ENTER – для получения на дисплее всех значений результата.
Расчетные формулы для решения данной задачки показаны в таблице 7.
Итог решения показан в таблице 6.
Таблица 6 – Расчетные формулы
Издержки
Выпуск (потребление)
Конечный
Валовый
(отрасли)
ветвь А
ветвь B
ветвь C
продукт
выпуск
ветвь А
0.05
0.1
0.4
47
=МУМНОЖ (F12:H14; E3:E5)
ветвь B
0.1
0.1
0.3
58
=МУМНОЖ (F12:H14; E3:E5)
ветвь C
0.3
0.15
0.2
81
=МУМНОЖ (F12:H14; E3:E5)
Решение
Е =
1
0
0
0
1
0
0
0
1
Е-А =
=B8‑B3
=C8‑C3
=D8‑D3
(Е-А)-1
=
=МОБР (B12:D14)
=МОБР (B12:D14)
=МОБР (B12:D14)
=B9‑B4
=C9‑C4
=D9‑D4
=МОБР (B12:D14)
=МОБР (B12:D14)
=МОБР (B12:D14)
=B10‑B5
=C10‑C5
=D10‑D5
=МОБР (B12:D14)
=МОБР (B12:D14)
=МОБР (B12:D14)
Det (E-A)=
=МОПРЕД (B12:D14)
Таблица 7 – Итог решения
Издержки
Выпуск (потребление)
Конечный
Валовый
(отрасли)
ветвь А
ветвь B
ветвь C
продукт
выпуск
ветвь А
0.1
0.1
0.4
47
140
ветвь B
0.1
0.1
0.3
58
140
ветвь C
0.3
0.15
0.2
81
180
Решение
Е =
1
0
0
0
1
0
0
0
1
Е-А =
1
-0.1
-0.4
(Е-А)-1
=
1.322880941
0.27438
0.76433
-0.1
0.9
-0.3
0.333170015
1.25429
0.63694
-0.3
-0.2
0.8
0.558549731
0.33807
1.65605
Det (E-A)=
0.51025
Вывод:
Для ублажения спроса на продукцию отрасли А величиной 47 д.е., отрасли В – 58 д.е. и отрасли С – 81 д.е. нужно произвести продукции отрасли А на сумму 140 д.е., отрасли В на сумму 140 д.е., отрасли С – на сумму 180 д.е.
Вариант 15
Коммерческий магазин желает закупить овощи А и В. количество овощей, закупочные цены и цены, по которым магазин реализует овощи, приведены в таблице 8.
Таблица 8
Овощи
Цены
количество овощей
Закупка
Реализация
А
1,6
2,4
60
В
1,7
2,2
70
Как прибыльнее вложить средства, если общая сумма, которой размещается магазин в данное время, составляет 180 д.е., при этом овощей А необходимо приобрести не наименее 10 тонн.
Решение данной задачки состоит из 3-х главных шагов:
1. составление математической модели (формализация задачки);
Обозначим величину прибыли от овоща Как А, а величину прибыли от обоща В как В, тогда получим, что Прибыль от реализации овоща А составляет (2,4–1,6) А, соответственно овоща В – (2,2–1,7) В. Суммарная Прибыль магазина от реализации овощей составит (2,4–1,6) А+(2,2–1,7) В=0,8А+0,5В.
Тогда мотивированная функция имеет вид Z
=0,8А
– 0,5В
суммарная Прибыль обязана быть большей (наибольшей).
Данная задачка содержит две неведомых переменных, т.е. ее можно именовать плоской и она быть может решена графически.
Составим систему ограничений, исходя из условия задачки:
— ограничение на покупку овощей по деньгам:
На покупку овоща А расходуется 1,6 д.е на 1 тонн. На все количество овоща А расходуется 1,6 А д.е. На овощ В расходуется 1,7 д.е. на 1 тонну на закупку овоща В растрачивают 1,7 В. означает, исходя из условия задачки, суммарная сумма на которую затариваются овоща не обязана превосходить 180 д.е. Получим 1-ое неравенство системы:
1,6 А + 1,7 В ≤ 180;
– доп условия:
В условии задачки содержится доп условие – закупка овоща А не наименее 10 тонн и не наиболее 60 тонн. т.е. имеем доп неравенства для овоща А:
А ≥ 10;
А ≤ 60;
Для овоща В наложено верхнее ограничение не наиболее 70 тонн, из условия задачки понятно что нижним ограничение является 0. Получаем доп неравенства для овоща В:
В ≥ 0;
В ≤ 70;
Получили математическую модель задачки:
1,6А + 1,7В ≤ 180;
А
≥ 10; А
≤ 60;
В
≥ 0; В
≤ 70;
2. решение формализованной задачки;
Решив задачку графически и с внедрением пакета Excel, получим однообразное решение:
А = 60 тонн.
В = 49,412 тонн.
Ход решения – см. таблица 9 и набросок 3
Вывод: Для получения наибольшей прибыли в размере 72,7 ден. ед. нужно последующим образом издержать имеющиеся средства:
— овощ А закупить в количестве 60 тонн.
— овощ В закупить в количестве 49,412 м.
При всем этом нужно издержит все средства: 180 д.е.
Графическое решение задачки 4
нужно отыскать значения (А, В), при которых функция Z
=0,8 А
– 0,5 В
добивается максимума. При всем этом А и В должны удовлетворять системе ограничений, приведенной ранее:
1,6А + 1,7В ≤ 180;
А
≥ 10; А
≤ 60;
В
≥ 0; В
≤ 70;
1. Строим область, являющуюся пересечением всех полуплоскостей, уравнения которых приведены в системе ограничений. к примеру, полуплоскость 1,6А + 1,7В ≤ 180;
представляет собой совокупа точек, лежащих ниже прямой, соединяющей точки с координатами (65; 44,705) и (32,813; 75). Аналогично – другие. Построение – набросок 3.
2. Находим градиент функции Z.
gradz = {0,8; 0,5}
Строим вектор с началом в точке (0; 0) и концом в точке (0,8; 0,5).
Построение – набросок 3.
3. Строим прямую, перпендикулярную вектору градиента. Потому что по условию мы отыскиваем максимум функции Z, то передвигаем прямую в направлении обозначенном вектором. Точка максимума – крайняя точка области, которую пересечет эта ровная. В нашем случае, разыскиваемая точка лежит на пересечении прямых А=60 и 1,6 А + 1,7 В = 180;
Построение – набросок 3
4. Решаем систему уравнений
А=60;
1,6А + 1,7В = 180; В = 49,412;
Т.е графическое построение отдало итог (60; 49,412).
Наибольшее
Набросок 3 – Графическое решение задачки 4
Решение задачки 4 с внедрением пакета
Excel
В пакете Excel решение задачки линейного программирования осуществляется при помощи пт меню Сервис – Поиск решения.
Распечатка решения задачки в Excel приведена в таблице 9.
Формулы, по которым был произведен расчет, приведены в таб. 10.
Таблица 9 – Решение задачки в Excel
Переменные
A
B
значения
60
49.412
Нижняя граница
10
0
Верхняя граница
60
70
Z=(2.4–1.6) A+(2.2–1.7) B
0.8
0.5
72.706
max
Коэффициенты мотивированной функции
Коэффициенты
Таблица 10 – Формулы для расчета в Excel
Переменные
A
B
значения
60
49.412
Нижняя граница
10
0
Верхняя граница
60
70
Z=(2.4–1.6) A+(2.2–1.7) B
0.8
0.5
=СУММПРОИЗВ
(B3:C3; B6:C6)
max
Коэффициенты мотивированной функции
Коэффициенты
Неиспользо-
ванные ресурсы
Система ограничений
1.6
1.7
=СУММПРОИЗВ
(B3:C3; B10:C10)
<=
180
=F10‑D10
Перечень применяемой литературы
1.Финансово-экономические расчеты в Excel. – 2-е изд., доп. – М: Информационно-издательский дом «Филинъ», 2005. – 184 с.
2.Методический указания и контрольные задания по дисциплине «Информатика» для студентов заочного факультета экономического направления обучения. Ч. 3/ Сост. В.Н. Черномаз, Т.В. Шевцова, О.А. Медведева. – ДГМА, 2006 – 40 стр.
]]>