В табличном процессоре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 период;
Ø Показать уравнение на диаграмме;
Ø - Поместить на диаграмму величину достоверности аппроксимации ( R2);
Ø Нажать кнопку ОК.
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 мм | ||||||||
недели | ||||||||
Фирма | ||||||||
«СтройтТорг» |