Контрольная Информационные технологии в экономике, 5 лабораторных работ, номер: 224188

"Лабораторная работа 1
ЛОГИЧЕСКИЕ ФУНКЦИИ. ФУНКЦИИ ПРОГНОЗИРОВАНИЯ
Цель работы: повторение некоторых простых функций программного продукта Excel фирмы Microsoft из курса «Информатика», значимых для решения некоторых экономических задач и анализа полученных данных.
Задание к работе
1) Спрогнозировать темп инфляции на апрель и май, если известна динамика среднего роста цен за три месяца: январь – 3%, февраль – 9%, март – 13%, предположив, что рост цен подчиняется линейной зависимости.
2) Составить таблицу значений площадей круга и длин окружностей в зависимости от радиуса. Значения радиуса взять в диапазоне от 2 до 4 с шагом 0,05. Точность значений – шесть знаков после запятой. Ячейке, хранящей Пи, присвоить имя и ссылаться на нее по имени.
3) Смоделировать ситуацию, описанную в былине про Илью Муромца, в которой перед богатырем ставилась задача выбрать одну из трех дорог. В одной из смежных ячеек вводится вариант пути (1, 2 или 3), а в другой в это время появляется описание последствий выбора.
4) Построить таблицу по образцу табл. 1.1. Организовать возможность перерасчета стоимости в рублях, если за условную единицу принят а) $, б) €. Выбор типа условной единицы выполнить с помощью функции «Если» (ячейки серого цвета в табл. 1.1).
При расчете значений в столбце «Всего, руб.» также использовать функцию ЕСЛИ. Ячейкам, в которые будут заноситься значения курсов валют присвоить имена, например, USD, EURO. В результате автоматический перерасчет стоимости товаров в рублевом эквиваленте должен осуществляться путем ввода типа валюты в (указанную вами) ячейку-параметр.

Наименование статьи расхода Модель Стоимость за ед Кол-во, шт Всего Всего
у.е. ($, €.) у.е. ($, €.) руб.
Компьютеры
Ноутбук ASUS S200N CENTRINO (PM 1G/512Mb/60G/8.9""TFT/DVD-CDRW/Win XP Home RUS), 0.88 кг 1928 3
Комплектующие и принадлежности
Мышь оптическая Мышь LOGITECH MX310 Optical Mouse (5 кнопок+ колесо прокрутки, USB+PS/2) (930928) 58,8 3
USB Flash Drive (128Mb) USB 2.0 Flash Hard Drive, 128 Mb, ext. 24 3
CD-RW + DVD-RW DVD±R/RW & CD-R/RW NEC ND-2500A, IDE (Write: 8xDVD±R/ 4xDVD±RW/ 16xCD-RW/ 32xCD-R, Read: 12xDVD/ 40xCD, буфер 2 Mb) OEM 75 5
Программное обеспечение
Microsoft Project 530
Консультант Плюс (верс. Проф) 300
Периферийные устройства
Принтер лазерный цветной А4 HP Color LaserJet 3500 (A4, 64 Mb, 600x600 dpi, Image Ret 2400, 12 с/мин, USB 2.0, Win'98/SE/NT4/Me/2000/XP, Mac OS, до 45000 стр/мес) 972 2
Сканер Epson Perfection 2580 Photo (A4, 2400x4800 dpi, CCD, 48 bit, USB 2.0, слайд-адаптер с автоподачей) 162 2
Оргтехника
Копировальный аппарат А4 SHARP AR-5012 (A4, цифровой копир, 12 коп./мин., SOPM, масшт. 25-400%, 600dpi ) 558 1
Средства связи
Факсимильный аппарат Телефакс Panasonic KX-FP143RU (обыч.бум., память на 28 стр., автодозвон, монитор, лоток на 30 листов, память на 100 номеров, идентиф. вызыв. абонента) 162 1
Телефонный аппарат Телефон Panasonic KX-TMC40RU (трехстрочный ЖК дисплей, евроАОН, 50 номеров ускор. набора, блокир. набора номера, возм. настен. уст-ки, спикерфон, автоответчик) 62 1
Итого
Скидка

Лабораторная работа 2
ПОСТРОЕНИЕ И РЕДАКТИРОВАНИЕ ДИАГРАММ В MS EXCEL
Цель работы: Освоение приемов создания и редактирования диаграмм.
Задание к работе
1) Построить таблицу по образцу табл. 2.1.
2) Построить объемную гистограмму, отражающую начисленную сумму каждому из сотрудников.
3) Изменить цвет ряда данных.
4) Изменить цвет области построения диаграммы.
5) Подписать столбец, соответствующий максимальной сумме.
6) Добавить на диаграмму ряд данных «Отработано часов».
7) Установить логарифмическую шкалу.
8) Увеличить величину отработанных часов Андреевой И.Т. до 200 часов.
9) Увеличить сумму, начисленную Сидорову В.И. до 30 000руб. и отобразить значение на столбце.
10) Построить таблицу о прибылях и убытках фирмы по образцу табл. 2.2 .Налоговая ставка фирмы – 34%, количество акций в обращении – 10 млн.
11) Построить гистограмму, показывающую динамику изменения амортизации и динамику изменения прибыли на одну акцию по годам.
12) Вставить метку значений около максимального значения амортизации.
13) Изменить цвет одного из рядов данных.


Таблица 2.1
Заработная плата сотрудников
Таб. номер ФИО Тарифная ставка, руб. Отработано часов Начислено,
руб.
1 Сидоров В.И. 120,00 250 15 000
2 Андреева И.Т. 50,00 100 10 000
3 Ковалева О.А. 70,00 350 24 500
4 Лобанов А.О. 100,00 250 25 000
5 Евдокимов В.Х. 250,00 267 66 750
7 Морозова Н.С. 120,00 786 94 320
8 Пулит А.В. 243,76 155 37 783
9 Шанина Е.П. 120,00 467 56 040
Итого 2534 344 393
14) Отредактировать текст и формат легенды.
15) Изменить тип диаграммы для ряда, отражающего данные о прибыли на одну акцию, на тип ""график"".
16) Установить логарифмическую шкалу.
17) Добавить на диаграмму ряд данных ""Доход до уплаты налогов"".
18) Построить круговую, объемную диаграмму, отражающую данные об амортизации по годам. На диаграмме в качестве меток указать процентное соотношение долей по годам.
19) Изменить ориентацию диаграммы с помощью окна "" Объемный вид"".
20) Построить таблицу по образцу табл. 2.3 (файл exmpl.doc).
21) Построить гистограмму, показывающую динамику изменения бегства капиталов за границу по минимальным и максимальным экспертным оценкам (колонки Min и Max соответственно) ежемесячно.
22) Вычислить среднее значение бегства капиталов за месяц и добавить полученный ряд данных на диаграмму.
23) Построить для всех рядов данных на диаграмме полиномиальные линии тренда 3-го порядка с прогнозом на 2002 год.
24) Добавить к названиям по оси X имя прогнозируемого 2002 г.
25) Оформить отчет и подготовиться к защите работы.

Таблица 2.2
Отчет о прибылях и убытках, предоставленный акционерам
В млн. долларов Год
1996 1997 1998 1999 2000 2001 2002 2003 2004 2005
Объем продаж 100,00 100,00 100,00 100,00 100,00 100,00 100,00 100,00 100,00 100,00
Расходы 50,00 50,00 50,00 50,00 50,00 50,00 50,00 50,00 50,00 50,00
Амортизация 20,00 32,00 19,00 12,00 11,00 6,00 0,00 0,00 0,00 0,00
Доход до уплаты налогов 30,00 18,00 31,00 38,00 39,00 44,00 50,00 50,00 50,00 50,00
Налоги, подлежащие оплате по ставке 34% 10,2 6,12 10,54 12,92 13,26 14,96 17,00 17,00 17,00 17,00
Чистый доход после уплаты налогов 19,80 11,88 20,46 25,08 25,74 29,04 33,00 33,00 33,00 33,00
Прибыль на одну акцию, долл. 1,98 1,19 2,05 2,51 2,57 2,90 3,30 3,30 3,30 3,30

Лабораторная работа 3
СОРТИРОВКА, ОБРАБОТКА И ФИЛЬТРАЦИЯ ДАННЫХ В MS EXCEL
Цель работы: Освоение технологии структурирования и группировки данных средствами сортировки и фильтрации.

Задание к работе
1) Построить таблицу по образцу табл. 3.1 (файл exmpl.doc).
2) Отсортировать табл. 3.1 сначала в алфавитном порядке фамилий продавцов, а затем в порядке возрастания полученной выручки.
3) Отсортировать табл. 3.1 по районам в следующем порядке: Северный, Восточный, Западный, Южный, создав пользовательский список.
Таблица 3.1
Выручка фирмы за первый квартал 2004г
Месяц Продукция Продавец Район Объем, шт. Выручка, руб.
январь Напитки Марченко Северный 11111 2577752
январь Напитки Марченко Восточный 3214 745648
январь Напитки Марченко Южный 3200 742400
февраль Напитки Марченко Северный 567 131544
март Напитки Марченко Северный 567 131544
февраль Напитки Николаев Западный 45677 10597064
январь Напитки Николаев Западный 45670 10595440
январь Мясо Ивин Южный 543 125976
январь Мясо Ивин Восточный 5678 1317296
февраль Мясо Ивин Южный 4444 1031008
февраль Мясо Ивин Северный 6666 1546512
март Мясо Ивин Восточный 7777 1804264
март Мясо Ивин Южный 2323 538936
март Молоко Козлов Южный 353 81896
март Молоко Козлов Северный 36789 8535048
январь Молоко Козлов Южный 353 81896
февраль Молоко Козлов Южный 354 82128
март Молоко Марченко Северный 4567 1059544
февраль Бакалея Козлов Южный 5432 1260224
март Бакалея Козлов Восточный 2345 544040
март Бакалея Козлов Южный 567 131544


4) Выбрать из таблицы данные по продукции Молоко, проданной в Северном районе.
5) Выбрать из таблицы записи, содержащие данные по молоку с выручкой больше1000000 руб. и по напиткам, с выручкой, большей 200000руб.
6) Выбрать записи, со значениями выручки, больше средней.
7) Построить таблицу по образцу табл. 3.2 (файл exmpl.doc).
8) Отсортировать таблицу по графе ""Образование"", организовав пользовательский список: ""н/высшее"", ""высшее"", ""среднее"", а затем по соц. группе и возрасту.
9) Отсортировать таблицу по графе ""Соц. группа"", и подсчитать кол-во опрошенных в каждой соц. группе.
10) Выбрать записи, относящиеся к предпринимателям, которые могут тратить от 1000 до 3000 рублей.
11) Выбрать записи, относящиеся к данным о пенсионерах, готовых тратить больше 300 рублей, и студентах, готовых тратить больше 500 рублей.
12) Выбрать записи, относящиеся к мужчинам от 18 до 25 лет и к женщинам, от 26 до 35 лет.
13) Выбрать записи с информацией о лицах, имеющих покупательные возможности больше среднего значения.
14) Оформить отчет и подготовиться к защите работы.
Таблица 3.2
Информация о потребителях рынка одежды
Пол Возраст Социальная
группа Покупательные возможности Образование Ср. срок носки выходн. одежды Ср. срок носки повседн. одежды Покупка одежды в ""Сэконд хэнд""
м 18-25 студент 300р. н/высшее 1 1 нет
м 26-35 предприниматель 3 000р. высшее 1 менее года нет
ж 18-25 студент 500р. среднее 1 1 да
ж 26-35 безработный 100р. среднее 3 2 да
м 18-25 предприниматель 2 000р. среднее 1 1 нет
м 26-35 предприниматель 1 000р. высшее 1 1 нет
ж 36-45 безработный 0р. высшее 3 3 да
м 36-45 рабочий 500р. среднее 3 3 да
м 18-25 рабочий 300р. высшее 3 3 да

Окончание табл. 3.2
ж 26-35 служащий 200р. высшее 2 2 да
ж 26-35 служащий 500р. высшее 1 2 нет
м 36-45 гос. служащий 100р. высшее 2 2 да
ж 26-35 предприниматель 500р. н/высшее 1 3 нет
м 46-56 безработный 100р. н/высшее 3 более 3 лет да
ж 18-25 студент 1 000р. среднее 1 2 нет
м 26-35 безработный 200р. среднее 2 более 3 лет да
ж 36-45 предприниматель 4 000р. высшее 3 3 нет
ж 56-… пенсионер 200р. среднее 1 более 3 лет да
ж 46-56 пенсионер 300р. высшее 3 более 3 лет да
ж 56-… пенсионер 500р. высшее 2 более 3 лет нет
ж 26-35 предприниматель 1 000р. высшее 1 2 нет
ж 26-35 студент 200р. среднее 1 1 да
ж 26-35 служащий 400р. высшее 2 1 да
м 46-56 рабочий 400р. среднее 3 более 3 лет да
м 36-45 предприниматель 2 000р. н/высшее менее года менее года да

"
"Лабораторная работа 4
КОНСОЛИДАЦИЯ ДАННЫХ И СВОДНЫЕ ТАБЛИЦЫ В MS EXCEL
Цель работы: Получение навыков решения задач анализа средствами консолидации данных, связывания таблиц и сводных таблиц.

Задание к работе
1) Создать таблицы по образцу табл. 4.1 на двух разных листах одной книги путем простого связывания их данных, а затем получить тот же результат, расположив таблицы в разных рабочих книгах.
2) Создать отчеты о доходах воображаемой фирмы за три подряд идущих месяца, например, по шаблону вида табл. 4.2 на трех подряд идущих листах одной рабочей книги.
На четвертом листе этой же книги консолидировать данные всех трех таблиц, просуммировав доходы фирмы за все месяцы по каждой статье, применив трехмерные формулы.
На пятом листе книги просуммировать доходы фирмы за все месяцы по каждой статье, но с помощью консолидации по положению.
Таблица 4.1
Оборот продаж книг за 2005г.
Название
книги Цена,
руб. Количество (шт.) Итого,
руб.
WinWord 50 000,00 р. 4000 200 000 000,00 р.
Excel 55 000,00 р. 8500 467 500 000,00 р.
FoxPro 45 000,00 р. 3000 135 000 000,00 р.
MS DOS 34 000,00 р. 2000 68 000 000,00 р.
MS Windows 65 000,00 р. 10500 682 500 000,00 р.
3) Создать таблицу по образцу табл. 4.3.
4) Отсортировать таблицу по названиям отделов, внутри отдела по табельному номеру.
Настраиваем такой фильтр:

5) Отсортировать таблицу по названиям отделов, расположив их в следующей последовательности: ""Плановый"", ""Бухгалтерия"", Маркетинг"", ""Снабжения"". Внутри отдела отсортировать по должности.
Настраиваем такой фильтр

6) Сосчитать количество сотрудников в каждом отделе.
Таблица 4.2
Доход фирмы ""Орион"" за месяц _______
Город Розничная продажа Оптовая продажа Продажа в кредит Итого
Москва
Киев
Таллин
Минск

7) Определить величину среднего оклада сотрудников каждого отдела.
8) Составить список всех уволенных сотрудников, занимавших должность секретаря
9) Найти всех сотрудников 1971 года рождения.
10) Выбрать сотрудников, имеющих оклад меньше среднего.
11) Составить список всех сотрудников, занимающих должность секретаря старше 20 лет, и экономистов старше 30 лет.
12) Определить количество иждивенцев в каждом отделе.
13) Построить сводную таблицу, аналогичную приведенной на рис. 4.1.
14) Изменить данные исходной таблицы (например, изменив название любой должности) и обновить сводную таблицу.
15) Скрыть в сводной таблице данные по должности ""Менеджер"".
16) Построить новую сводную таблицу с данными о кол-ве мужчин и женщин в каждом отделе.
17) Создать и заполнить базу данных по образцу табл. 4.4. На другом листе создать сводную таблицу:
 поля год, продавец оформить полями страниц;
 поле объем сделать полем строк;
 поле район сделать полем столбцов;
 поле товар сделать полем данных.
18) Для каждого значения в списке год создать отдельный рабочий лист. Заменить функцию Сумма, примененную к данным сводной таблицы по умолчанию, на функцию Количество значений.

Таблица 4.3
Информация о сотрудниках фирмы
№пп Таб. номер Фамилия Имя Отчество Дата рождения Отдел Должность Дата приема на работу Дата увольне-ния Пол Кол. иждивен.
1 1 Иванов Иван Иванович 28.10.1952 Плановый экономист 10.01.1996 м 2
2 454 Иваненко Иван Петрович 21.01.1935 Бухгалтерия бухгалтер 10.04.1998 м 1
3 1234 Петров Петр Петрович 26.08.1970 Плановый секретарь 21.07.1998 м 2
4 12312 Петренко Петр Иванович 14.11.1970 Маркетинга менеджер 10.10.1998 м 1
5 12345 Сидоров Сидор Сидорович 02.02.1971 Снабжения менеджер 10.01.1999 10.10.2001 м 0
6
23456 Седов Кузьма Фомич 23.04.1971 Плановый экономист 12.04.1999 м 5
7 34567 Фомин Фома Фомич 12.07.1985 Плановый экономист 26.07.1995 м 1
8 45454 Фоменко Сидор Кузьмич 30.09.1971 Бухгалтерия бухгалтер 10.11.1999 м 1
9 45564 Кукина Юлия Петровна 19.12.1971 Бухгалтерия секретарь 10.01.2000 21.12.2001 ж 1
10 45678 Макова Алина Игоревна 08.03.1972 Снабжения менеджер 10.04.2000 ж 1
11 56565 Сушкина Алла Вадимовна 17.12.1956 Плановый экономист 10.07.2000 12.12.2000 ж 1
12 56786 Кротова Инна Павловна 21.01.1980 Снабжения секретарь 21.10.1997 ж 1
13 56789 Бойцов Семен Семенович 26.08.1970 Бухгалтерия начальник 10.01.2001 м 1
14 67890 Гайдай Иван Михайлович 14.11.1970 Бухгалтерия бухгалтер 30.04.2001 м 1
15 78787 Краснов Павел Павлович 02.02.1971 Плановый начальник 10.07.2001 м 5
16 78901 Рябов Олег Евгеньевич 23.04.1971 Снабжения начальник 13.10.2001 м 1
17 89012 Белова Софья Петровна 12.07.1971 Плановый экономист 10.01.2002 ж 2
18 90123 Чернова Зоя Богдановна 30.09.1971 Маркетинга начальник 10.04.2000 ж 2
19 98989 Родионов Андрей Вадимович 19.12.1971 Маркетинга секретарь 10.07.2002 30.01.2001 м 0
20 99999 Хрустов Юрий Юрьевич 08.03.1972 Маркетинга менеджер 10.10.2002 м 0


Рис. 4.1. Образец сводной таблицы
Таблица 4.4
Объем продаж фирмы за первый квартал 2004г.
Месяц Год Продавец Товар Район Объем Сбыт
Январь 1995 Иванов Детектив Северный 12 102
Февраль 1993 Петров Детектив Южный 13 105
Март 1994 Сидоров Детектив Западный 11 200
Январь 1994 Иванов Поэзия Западный 14 123
Апрель 1995 Иванов Поэзия Восточный 13 200
Январь 1993 Петров Детектив Северный 11 187
Июнь 1995 Сидоров Классика Южный 10 156
Март 1994 Иванов Поэзия Южный 16 186
Июнь 1993 Петров Классика Восточный 11 190
Июль 1993 Сидоров Детектив Западный 20 122
Август 1995 Иванов Классика Южный 15 122
Апрель 1993 Петров Поэзия Южный 11 186
Май 1994 Петров Детектив Западный 10 145
Июнь 1995 Иванов Детектив Северный 14 135
Июль 1995 Сидоров Детектив Северный 15 164
Август 1994 Иванов Детектив Восточный 18 172
Сентябрь 1993 Сидоров Классика Южный 19 155
Октябрь 1994 Иванов Классика Северный 13 162

Лабораторная работа 5
ПОСТРОЕНИЕ СЦЕНАРИЕВ В MS EXCEL
Цель работы: Изучение способов подбора параметров в таблицах и построения сценариев при различных параметрах данных.
Задание к работе
1) Создать таблицу по образцу табл. 5.1 (файл exmpl.doc).
2) Определить (используя инструмент Подбор параметра)
- при какой цене сумма со скидкой, полученная за товар 1 будет равна 25000 руб.;
- при каком количестве проданного товара 2 сумма без скидки будет равна 42000 руб.
Расчет производить при условии, что при сумме более 25000 руб. предоставляется скидка, равная 5%, в противном случае 3%.
Таблица 5.1
Расчет цен на товар с учетом скидок в рублях
Код
товара Наименование
товара Цена
товара
(без учета налога с продаж) Цена
товара (с учетом налога с продаж) Продано единиц Сумма Сумма со скидкой
2001 товар 1 120 200
2002 товар 2 243 150
3) Рассчитать варианты платежей по простому займу в 10000 руб., выданному на 12 месяцев под 8; 8,5; 9; 9,5; 10; 10,5 процентов, используя инструмент Таблица подстановки. Расчет платежа выполняется по формуле:
сумма займа * (100%+процент)/(срок).
4) Рассчитать варианты платежей по простому займу в 10000 руб., выданному на 10; 11; 12; 13; 14 месяцев под 8 процентов, применив инструмент Таблица подстановки.
5) Рассчитать варианты платежей по простому займу в 10000; 11000; 12000; 13000 рублей, выданному на 10; 11; 12; 13; 14 месяцев под 8 процентов, применив инструмент Таблица подстановки.
6) Составить сценарии поведения модели, отображенной в табл. 5.2 , считая изменяемыми затраты на рекламу, среднее количество покупателей. При расчете сценариев принять, что в году 52 недели. Рассмотреть 3 случая:
- наихудший (минимальное кол-во клиентов определяется командой Подбор параметра для точки безубыточности, затраты на рекламу 10000 руб/год);
- наилучший (500000 клиентов в неделю, затраты на рекламу 100000 руб/год.);
- средний (как среднее арифметическое клиентов и затрат на рекламу).
7) Создать итоговый отчет по трем сценарием в виде сводной таблицы по следующим ячейкам результатов: Среднее кол-во покупателей за неделю; Текущая прибыль за неделю.
8) Оформить отчет и подготовиться к защите работы.
Таблица 5.2
Пример модели для построения сценариев
Покупатель Накладные расходы Всего за
неделю Всего за год
Доход от одного покупателя 34,78
Расходы на одного покупателя 30,12
Прибыль от одного
покупателя вычислить
Среднее кол-во покупателей изменять!
Общая прибыль вычислить вычислить
Накладные расходы
Отчисления на
зарплату вычислить 3 494 046
Оборудование вычислить 1 635 511
Амортизация вычислить 453 305
Реклама вычислить изменять!
Снабжение вычислить 496 944
Прочее вычислить 1 295 828
Всего вычислить
Текущая прибыль вычислить
"