Алгоритм выполнения задания

В табличном процессореExcelвыполнить следующие пункты задания:

1. Запустим программу Microsoft Office Excel 2003. (Пуск/ Все программы / Microsoft Office / Microsoft Office Excel 2003) и откроем рабочую книгу Книга1.xls.

2. Подготовим исходные данные на Листе1 в табличной форме следующего вида:

3. Листу 1 присвоим название «Исходные данные».

4. В столбец "Опт. Цена, у.е. " заносятся числа по формуле "Цена, у.е. *0.95". В столбце "Цена, р." значения вычисляются по формуле "Цена, у.е.*Курс у.е.". Для этого нужно предварительно занести в какую-нибудь ячейку, например в G2 "Курс 1 у.е. в р.". Значения столбца "Опт. Цена, у.е. " заполняются копированием вниз формулы из ячейки D2,используя относительную адресацию. Значения столбца "Цена, р." заполняются копированием вниз формулы из ячейки Е2, используя абсолютную адресацию ячейки, где помещено значение курса 1 у.е. в р.

5. На листе «Исходные данные» составить таблицу 2, используя статистические встроенные функции Excel - MИН, МАКС, СРЗНАЧ, СТАНДОТКЛОН.

6. По данным таблицы1 построим объемную диаграмму.

7. Перейдем на Лист 2. Листу 2 присвоим название «Линейная модель». Составим таблицу 3 статистических данных объемов продаж строительных материалов (значения берутся из варианта) и рассчитаем стоимость продаж силикатного кирпича, беря его цену из таблицы 1 листа «Исходные данные».

8. По данным таблицы 3 построим график с маркерами типа «График X-У», где X - месяц, У – объём. Для этого необходимо:

Ø выделить диапазон C4: C14;

Ø По данным таблицы 3 построим график с маркерами типа «График X-У», где X - месяц, У – объём. Для этого необходимо:

Ø выделить диапазон C4: C14;

Ø вызвать Мастер диаграмм;

Ø назначить тип диаграммы – График, вид – График с маркерами, помечающими точки данных;

Ø нажать кнопку Готово.

Ø выделить и отредактировать полученный график в меню Диаграмма / Исходные данные / Ряд.

9. Для аппроксимации полученного графика построить линию линейного тренда

Ø активизировать график одним щелчком мыши;

Ø через опции системного меню Диаграмма /Добавить линию тренда выбрать на закладке Тип окна Линия тренда тип линии тренда Линейная;

Ø в окне Линия тренда перейти на закладку Параметры;

Ø здесь установить: Прогноз вперёд на 1 период;

Ø Показать уравнение на диаграмме;

Ø - Поместить на диаграмму величину достоверности аппроксимации ( R²);

Ø Нажать кнопку ОК.

10. Значения коэффициентов А и В из уравнения занести в таблицу.

11. Вычислим значение теоретического объёма продаж товаров по формуле, показанной на линии тренда = $C$2*A7+$D$2. Скопируем его в диапазон D7:D14.

12. Вычислим абсолютное значение отклонения теоретического от фактического объёма продаж товаров в столбце «Отклонение»: =ABS(C7-D7). Скопируем его далее.

13. Определим максимальную погрешность в столбце «Отклонение» при помощи функции МАКС Мастера функций (клетка E16).

14. Произведем минимизацию величины погрешности, используя сервисное средство Excel «Поиск решения» (кнопка системного меню – Сервис). При этом в качестве целевой ячейки надо выбрать ту, в которой находится величина погрешности (E16). Изменять следует значение коэффициентов a и b (ячейки C2:D2).

15. Cделаем прогноз объёма продаж на девятый период, скопировав формулу из предыдущей строки.

16. Построим на диаграмме совмещенные графики объёма продаж силикатного кирпича теоретического и фактического.

17. Указанные действия выполним для степенной математи­ческой модели, используя функцию Y=A*X^B на листе 3, предварительно присвоя ему имя

18. «Степенная модель»). Определим какая математическая модель даёт минимальную погрешность решения.

Задание 3. Использование электронных таблиц для моделирования и прогнозирования экономических процессов.

Создайте таблицу в Excel и выполните необходимые расчеты. Имея данные объемов продаж на восемь недель, сделайте прогноз продаж на 9 неделю.

3.1

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Фасадная панель	«Мой Дом»	25,33
Фасадная плита	«Мой Дом»	28,96
Газобетонный блок	«Мой Дом»	0,67
Керамическая плитка	«Мой Дом»	0.41
Гипсокартонный лист	«Мой Дом»	8,16
Кирпич М-150	«Мой Дом»	0,26
Кирпич М-100	«Мой Дом»	0,2
Пеноблок 100 мм	«Стройград»	1,43
Пеноблок 150 мм	«Стройград»	1,89
Пергамин	«Стройград»	5,67
Пенофол	«Стройград»	6,38
Изовер	«Стройград»	25,66
Пенополистирол1.2*1,2*0,05	«Стройград»	8,48
Гидростеклоизол	«Стройград»	15,34

Объем продаж керамической плитки
	недели
Фирма
«Мой Дом»

3.2

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Блоки из ячеистого бетона	«Восточное»	1,95
Гипсовая плита полнотелая	«Восточное»	6,169
Гипсовая плита	«Восточное»	7,17
Фанера ФК 8мм	«Восточное»	16,60
Труба вентиляционная	«Восточное»	59,2
Шифер плоский	«Восточное»
Сетка металлическая	«Восточное»	4,85
Кирпич полнотелый М-150	«Альта»	0,39
Панель фасад. DT-Stone	«Альта»	51,67
Пенополистирол	«Альта»	8,1
Керамогранит темно-серый	«Альта»	13,7
Рубероид	«Альта»	0,68
Гидростеклоизол	«Альта»	29,71
Пенофол 2000	«Альта»	3,71

Объем продаж пенополистирола
	недели
Фирма
«Альта»

3.3

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Фанера ФК 21мм	«ТехноСтрой»	40,69
Плитка глазиров, пол	«ТехноСтрой»	9.06
Плитка каф. стена	«ТехноСтрой»	9,11
Минеральная вата	«ТехноСтрой»	58,81
Бордюр из гранита	«ТехноСтрой»	68,43
Гипсокартон 12.5мм	«ТехноСтрой»	9,73
ДСП строит.	«ТехноСтрой»	27,83
Панель пластиковая	«СтройЦентр»	5,33
Кирпич щелевой М-150	«СтройЦентр»	0,55
Дверное полотно	«СтройЦентр»	113,33
Бирепласт	«СтройЦентр»	26,48
Рубемаст	«СтройЦентр»	1,90
Мин.плита ROCKWOOL	«СтройЦентр»	18,40
Сетка штукатурная	«СтройЦентр»	2,46

Объем продаж гипсокартона 12.5мм
	недели
Фирма
«ТехноСтрой»

3.4

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Фанера ФК 18мм	«ТехноРесурс»	34,25
Сетка стеклотканевая	«ТехноРесурс»	14,25
Пенофол 2000 А	«ТехноРесурс»	1,475
Айситекс-Моно	«ТехноРесурс»	60,37
Линолеум Фаворит	«ТехноРесурс»	16,23
Паркет ламинир.	«ТехноРесурс»	3,5
Панель потолочная	«ТехноРесурс»	10,5
Штукатурка	«ЭкоДом»	8,16
Краска водоэмульсионная	«ЭкоДом»	42,73
Полистиролбетонные блоки	«ЭкоДом»	4,03
Нордик терракота	«ЭкоДом»	15,04
Металлочерепица «Монтеррей»	«ЭкоДом»	35,83
Тепломат-100	«ЭкоДом»
Пергамин	«ЭкоДом»	0,53

Объем продаж пергамина
	недели
Фирма
«ЭкоДом»

3.5

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Фанера ФК 15мм	«СтройИнвест»	29,28
ПЕНОПЛЭКС К	«СтройИнвест»	55,91
Айситекс-Профи	«СтройИнвест»	51,52
Керамогранит Серо-рыжый	«СтройИнвест»	19,67
Линолеум 35-415F	«СтройИнвест»	9,03
Паркет лам. KRONOSTAR	«СтройИнвест»	2,85
Мозаика фиолетовая	«СтройИнвест»	4,67
Панель потолочная Албес	«СтройОптТорг»	2,65
Раствор для стяжки	«СтройОптТорг»	7,34
Краска водоэмульсионная	«СтройОптТорг»	42,75
Стеновые пеноблоки	«СтройОптТорг»	3,58
Кронштейн	«СтройОптТорг»	0,55
Техноэласт ЭПП	«СтройОптТорг»	5,76
Кирпич полнотелый М-125	«СтройОптТорг»	0,42

Объем продаж стеновых пеноблоков
	недели
Фирма
«СтройОптТорг»

3.6

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Пенофол 2000 С	«РосСтрой»	3,98
Унифлекс ЭПП	«РосСтрой»	4,45
Альпин синий с отливом	«РосСтрой»	18,6
Керамогранит зелёный	«РосСтрой»	11,34
Ондулин (зеленый)	«РосСтрой»	12,67
Покрывающий фартук	«РосСтрой»	10,35
Гипсокартон ламинир.	«РосСтрой»	23,95
Профиль направляющий	«МегаОпт»	2,17
Фанера березовая ФСФ 9 мм	«МегаОпт»	27,37
Стекло, 4мм	«МегаОпт»	4,92
ДСП мебельная	«МегаОпт»	28,85
Линолеум Tarkett	«МегаОпт»	12,57
Мозаика желтая	«МегаОпт»	22,39
Грунтовка P-PRIMER	«МегаОпт»	27,93

Объем продаж линолеума Tarkett
	недели
Фирма
«МегаОпт»

3.7

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Бирепласт-Норма-Л	«СтройТемп»	26,48
Панель потолочная TAURUS	«СтройТемп»	2,5
Шлакоблок стеновой	«СтройТемп»	0,9
Биполь ЭПП	«СтройТемп»	3,76
Шифер волнистый	«СтройТемп»
Ондуфлеш терракота	«СтройТемп»
Битум БН 90/30	«СтройТемп»	21,33
Сетка кладочная	«Райтер»	2,94
Керамогранит бежевый	«Райтер»	19,63
Тепломат-80	«Райтер»	23,21
Мастика БПХ Техномаст	«Райтер»	16,53
Панель облицовочная	«Райтер»	21,38
Коньковый элемент	«Райтер»	6,94
Панель сайдинга	«Райтер»	6,52

Объем продаж панелей сайдинга
	недели
Фирма
«Райтер»

3.8

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Сэндвич-панели стеновые	«ЦентрСтрой»	42,5
Грунтовка Vosko	«ЦентрСтрой»	28,23
Клей для кафеля Terraco	«ЦентрСтрой»	6,17
Бикрост ХПП	«ЦентрСтрой»	2,44
Щипцовый профиль	«ЦентрСтрой»	7,97
Профнастил Н-60 оцинкованный	«ЦентрСтрой»	67,75
Линолеум Moda	«ЦентрСтрой»	14,86
Мозаика голубая	«СтройИнфо»	7,17
Тепловит-20	«СтройИнфо»	66,94
Клей FA-500	«СтройИнфо»	11,56
Техноэластмост Б ЭМП	«СтройИнфо»	8,28
Конек коричневый 90см	«СтройИнфо»	8,32
Лист гладкий оцинкованный	«СтройИнфо»	27,36
Затирка Terraco grout	«СтройИнфо»	2,35

Объем продаж мозаики голубой
	недели
Фирма
«ИнфоСтрой»

3.9

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Фанера БЕРЕЗА 12 мм	«РесурсСтрой»	25,32
Сотовый поликарбонат STPLAST	«РесурсСтрой»	54,56
ДСП ламиниров.	«РесурсСтрой»	19,45
Коробка дверная 90 мм	«РесурсСтрой»	23,93
Линолеум Магия	«РесурсСтрой» «РесурсСтрой»	15,13
Краска Krastone	«РесурсСтрой»	44,82
Ендова зеленая 100см	«Стройкомплект»	8,58
Тепловит-12	«Стройкомплект»	15,25
Мозаика JA-24	«Стройкомплект»	26,37
Грунтовка ALC-PRIMER	«Стройкомплект»	29,77
Гранит GG17 ступень	«Стройкомплект»	45,23
Мозаика оранжевая	«Стройкомплект»	26,25
Айситекс-Стандарт	«Стройкомплект»	43,64
Клей для плитки ПХВ	«Стройкомплект»	19,38

Объем продаж краски Krastone
	недели
Фирма
«РесурсСтрой»

3.10

Строительные материалы	Фирма	Цена,y.е.	Опт.цена, у.е.	Цена,р.
Линолеум Астория	«ГрадСтрой»	10,21
Айситекс-Мастер ТКП	«ГрадСтрой»	35,62
Клей Хомакол	«ГрадСтрой»	17,53
Гипсокартон влагостойкий	«ГрадСтрой»	12,26
Фанера березовая ФСФ 15 мм	«ГрадСтрой»	43,13
Сотовый поликарбонат NOVOGLASS	«ГрадСтрой»	131,57
Оргстекло 1,2х1,8 3мм прозрачное	«ГрадСтрой»	31,37
Заполнитель карнизов универсальный 2 м	«СтройтТорг»	1,13
Плитка Керамогранит полиров.	«СтройтТорг»	18,45
Ламнированная панель HILUC	«СтройтТорг»	30,31
Дверное полотно ВОСТОК ДГШ	«СтройтТорг»	106,67
Плёнка пароизоляционная универсальная	«СтройтТорг»	0,67
Фанера хвойная 18 мм	«СтройтТорг»	37,25
Сетка стеклотканевая 5х5	«СтройтТорг»	19,42

Объем продаж хвойной фанеры18 мм
	недели
Фирма
«СтройтТорг»