Учебная работа. Контрольная работа: Использование Excel для решения статистических задач

1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (Пока оценок нет)
Загрузка...
Контрольные рефераты

Учебная работа. Контрольная работа: Использование 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 стр.

]]>