Работа № 2: Создание и редактирование диаграмм в Excel
Перечень заданий
Цель работы: освоение приемов создания и редактирования диаграмм
Состав работы (быстрый переход по ссылкам):
1. Задание 1: построение графика зарплаты.
2. Задание 2: прибыль по акциям.
3. Задание 3: анализ заемного капитала.
Задание 1
Исходные данные
| Таб. номер | ФИО | Тарифная ставка (руб.) | Отработано часов | Начислено |
| 0001 | Сидоров В.И. | 120,00р. | 125 | 15000р. |
| 0002 | Андреева И.Т. | 50,00р. | 134 | 32664р. |
| 0003 | Ковалева О.А. | 70,00р. | 350 | 85316р. |
| 0004 | Лобанов А.О. | 100,00р. | 250 | 30000р. |
| 0005 | Евдокимов В.Х. | 250,00. | 267 | 61650р. |
| 0007 | Морозова Н.С. | 120,00р. | 786 | 94320р. |
| 0008 | Пулит А.В. | 243,76р. | 155 | 37783р. |
| 0009 | Шанина Е.П. | 120,00р. | 467 | 56040р. |
|
| Итого |
| 2534 | 412773р. |
Порядок выполнения
На основании исходных данных, приведенных в таблице, выполнить следующее.
1. Построить диаграмму, отражающую начисленную сумму каждому из сотрудников.
2. Изменить цвет ряда данных.
3. Изменить цвет области построения диаграммы.
4. Подписать столбец, соответствующий максимальной сумме.
5. Добавить на диаграмму ряд данных «Отработано часов».
6. Настроить его на вспомогательную ось.
7. С помощью диаграммы увеличить величину отработанных часов Андреевой И.Т. до 200 часов.
8. С помощью диаграммы увеличить сумму, начисленную Сидорову В.И. до 30 000р.
|
|
|
Задание 2
Исходные данные
Отчет о прибылях и убытках, предоставленный акционерам
| Налоговая ставка фирмы: | 34% |
| Количество акций в обращении (в млн.): | 10 |
| В млн. долларов | Год | |||||||||
| 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 |
Порядок выполнения
На основании исходных данных, приведенных в таблице, выполнить следующее.
1. Построить гистограмму, показывающую динамику изменения амортизации и динамику изменения прибыли на одну акцию по годам.
|
|
|
2. Вставить метку значений около максимального значения амортизации.
3. Изменить цвет одного из рядов данных.
4. Отредактировать текст и формат легенды.
5. Изменить тип диаграммы для ряда, отражающего данные о прибыли на одну акцию, на тип "график".
6. Настроить его на вспомогательную ось.
7. Добавить на диаграмму ряд данных "Доход до уплаты налогов".
8. Построить круговую диаграмму, отражающую данные об амортизации по годам.
9. Изменить тип всей диаграммы на объемный.
10. Изменить ориентацию диаграммы с помощью окна " Объемный вид".
Задание 3
Исходные данные
Таблица для построения графиков, отражающих зависимость ожидаемого значения EPS и цены акции от величины заемного капитала
| Заемный капитал, млн.дол. | Ожидаемый EPS, дол. | Среднее квадратическое отклонение EPS , дол. | Коэффициент вариации | Цена акции, дол. | ||
| 0 | 2,4 | 1,52 | 0,63 | 20 | ||
| 2 | 2,55 | 1,68 | 0,66 | 20,89 | ||
| 4 | 2,7 | 1,87 | 0,69 | 21,47 | ||
| 6 | 2,87 | 2,09 | 0,73 | 21,73 | ||
| 8 | 3,04 | 2,4 | 0,8 | 21,71 | ||
| 10 | 3,2 | 2,9 | 0,91 | 21,05 | ||
| 12 | 3,34 | 3,83 | 1,15 | 19,86 | ||
| 14 | 3,26 | 5,2 | 1,6
| 17,16 | ||
| 16 | ? |
|
| ? | ||
| 18 | ? |
|
| ? |
Порядок выполнения
На основании исходных данных, приведенных в таблице, выполнить следующее.
1. Построить график, отражающий зависимость ожидаемого значения EPS от величины заемного капитала.
2. Подобрать линию тренда для построенного ряда данных.
3. Осуществить прогноз для значений заемного капитала 16 и 18 млн.дол.
4. Построить график, отражающий зависимость цены акции от величины заемного капитала.
5. Подобрать линию тренда для ряда данных "Цена акции".
6. Осуществить прогноз для значений заемного капитала 16 и 18 млн.дол.
7. Вывести на графике значение максимальной цены акции.
8. Вывести уравнение для подобранной линии тренда и подсчитать в таблице недостающие значения цены акций.
Работа № 3: Анализ данных в таблицах
Перечень заданий
Цель работы: освоить технологии анализа и расчетов на основе списков (баз данных) EXCEL: структурирование и группировка данных; формирование Итогов; консолидация таблиц; сортировка данных; фильтры; решение задач бизнес-анализа средствами Сводных таблиц.
Состав работы (быстрый переход по ссылкам):
1. Задание 1: Сортировка и выборка данных из таблиц.
|
|
|
2. Задание 2: Выборка из таблиц с помощью функций работы со списками.
3. Задание 3: Фильтрация и обработка данных, построение сводных таблиц.
Задание 1
Исходные данные
| Месяц | Продукция | Продавец | Район | Объем (шт.) | Выручка в руб. |
| янв | Напитки | Марченко | Северный | 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 |
Порядок выполнения
На основании исходных данных, приведенных в таблице, выполнить следующее.
1. Отсортировать Табл. 1 сначала в алфавитном порядке фамилий продавцов, а затем в порядке возрастания полученной выручки.
2. Отсортировать Табл. 1 по районам в следующем порядке: «Северный», «Восточный», «Западный», «Южный».
3. Выбрать из таблицы данные по продукции «Молоко», проданной в Северном районе.
4. Выбрать из таблицы записи, содержащие данные по молоку с выручкой больше 1 000 000 р. и по напиткам, с выручкой, большей 200 000р.
5. Выбрать записи, со значениями выручки, больше средней.
Задание 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 | да |
| ж | 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р. | н/высшее | менее года | менее года | да |
Порядок выполнения
На основании исходных данных, приведенных в таблице, выполнить следующее.
1. Отсортировать таблицу по графе "Образование", организовав пользовательский список: "н/высшее", "высшее", "среднее", а затем по соц. группе и возрасту.
2. Отсортировать таблицу по графе "Соц. группа".
3. Подсчитать кол-во опрошенных в каждой соц. группе (двумя способами).
4. Выбрать записи, относящиеся к предпринимателям, которые могут тратить от 1000 до 3000 рублей.
5. Выбрать записи, относящиеся к данным о пенсионерах, готовых тратить больше 300 рублей, и студентах, готовых тратить больше 500 рублей.
6. Выбрать записи, относящиеся к мужчинам от 18 до 35 лет и к женщинам, от 26 до 35 лет.
7. Выбрать записи с информацией об опрошенных, имеющих покупательные возможности больше среднего значения.
Задание 3
Исходные данные
| № пп | аб. номер | Фамилия | Имя | Отчество | Дата рождения | Отдел | Должность | Дата приема на работу | Дата увольне-ния | Пол | Кол-во иждивенцев | Оклад |
| 1 | 00001 | Иванов | Иван | Иванович | 28.10. 1952 | Плановый | экономист | 10.01. 1996 |
| м | 2 | 1500 |
| 2 | 00454 | Иваненко | Иван | Петрович | 21.01. 1935 | Бухгалтерия | бухгалтер | 10.04. 1998 |
| м | 1 | 3000 |
| 3 | 01234 | Петров | Петр | Петрович | 26.08. 1970 | Плановый | секретарь | 21.07. 1998 |
| м | 2 | 2250 |
| 4 | 12312 | Петренко | Петр | Иванович | 14.11. 1970 | Маркетинга | менеджер | 10.10. 1998 |
| м | 1 | 5250 |
| 5 | 12345 | Сидоров | Сидор | Сидорович | 02.02. 1971 | Снабжения | менеджер | 10.01. 1999 | 10.10. 2001 | м | 0 | 3750 |
| 6 | 23456 | Седов | Кузьма | Фомич | 23.04. 1971 | Плановый | экономист | 12.04. 1999 |
| м | 5 | 1500 |
| 7 | 34567 | Фомин | Фома | Фомич | 12.07. 1985 | Плановый | экономист | 26.07. 1995 |
| м | 1 | 1650 |
| 8 | 45454 | Фоменко | Сидор | Кузьмич | 30.09. 1971 | Бухгалтерия | бухгалтер | 10.11. 1999 |
| м | 1 | 4500 |
| 9 | 45564 | Кукина | Юлия | Петровна | 19.12. 1971 | Бухгалтерия | секретарь | 10.01. 2000 | 21.12. 2001 | ж | 1 | 2250 |
| 10 | 45678 | Макова | Алина | Игоревна | 08.03. 1972 | Снабжения | менеджер | 10.04. 2000 |
| ж | 1 | 3750 |
| 11 | 56565 | Сушкина | Алла | Вадимовна | 17.12. 1956 | Плановый | экономист | 10.07. 2000 | 12.12. 2000 | ж | 1 | 2055 |
| 12 | 56786 | Кротова | Инна | Павловна | 21.01. 1980 | Снабжения | секретарь | 21.10. 1997 |
| ж | 1 | 2250 |
| 13 | 56789 | Бойцов | Семен | Семенович | 26.08. 1970 | Бухгалтерия | начальник | 10.01. 2001 |
| м | 1 | 4500 |
| 14 | 67890 | Гайдай | Иван | Михайлович | 14.11. 1970 | Бухгалтерия | бухгалтер | 30.04. 2001 |
| м | 1 | 3000 |
| 15 | 78787 | Краснов | Павел | Павлович | 02.02. 1971 | Плановый | начальник | 10.07. 2001 |
| м | 5 | 6000 |
| 16 | 78901 | Рябов | Олег | Евгеньевич | 23.04. 1971 | Снабжения | начальник | 13.10. 2001 |
| м | 1 | 4500 |
| 17 | 89012 | Белова | Софья | Петровна | 12.07. 1971 | Плановый | экономист | 10.01. 2002 |
| ж | 2 | 2550 |
| 18 | 90123 | Чернова | Зоя | Богдановна | 30.09. 1971 | Маркетинга | начальник | 10.04. 2000 |
| ж | 2 | 7500 |
| 19 | 98989 | Родионов | Андрей | Вадимович | 19.12. 1971 | Маркетинга | секретарь | 10.07. 2002 | 30.01. 2001 | м | 0 | 2250 |
| 20 | 99999 | Хрустов | Юрий | Юрьевич | 08.03. 1972 | Маркетинга | менеджер | 10.10. 2002 |
| м | 0 | 3750 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Таблица на листе "образец" (см. ссылку ниже)
Порядок выполнения
На основании исходных данных, приведенных в таблице, выполнить следующее.
1. Отсортировать таблицу по названиям отделов, внутри отдела по табельному номеру.
2. Отсортировать таблицу по названиям отделов, расположив их в следующей последовательности:
"Плановый", "Бухгалтерия", Маркетинг", "Снабжения". Внутри отдела отсортировать по должности.
3. Сосчитать количество сотрудников в каждом отделе.
4. Определить величину среднего оклада сотрудников каждого отдела.
5.Составить список всех уволенных сотрудников, занимавших должность секретаря
6. Найти всех сотрудников 1971 года рождения.
7. Выбрать сотрудников, имеющих оклад меньше среднего.
8.Составить список всех сотрудников, занимающих должность секретаря старше 20 лет, и экономистов старше 30 лет.
9. Определить количество иждивенцев в каждом отделе.
10. Построить сводную таблицу, аналогичную приведенной на листе "Образец".
11.Подсчитать общее кол-во сотрудников в каждом отделе.
12.Рассчитать средний оклад сотрудников каждого отдела.
13.Изменить данные исходной таблицы (например, изменив название любого отдела) и обновить сводную таблицу.
14.Скрыть данные по отделу "Маркетинга".
15.Построить новую сводную таблицу с данными о кол-ве мужчин и женщин в каждом отделе.
16.Рассчитать средний оклад мужчин и женщин на предприятии.
17.Сформировать приказ о начислении премий.
18.Сформировать приказ о начислении премий сотрудникам отдела Снабжения.
Работа № 4: Подбор параметра, таблица подстановки, сценарии
Перечень заданий
Цель работы: освоение инструментов Excel (подбор параметра, таблица подстановки, сценарии)
Состав работы (быстрый переход по ссылкам):
1. Задание 1: Подбор параметра.
2. Задание 2: Таблица подстановки.
3. Задание 3: Сценарии.
Задание 1
Исходные данные
| № п/п | Код товара | Наименование товара | Цена товара (без учета налога с продаж) | Цена товара (с учетом налога с продаж) | Продано единиц | Сумма (в руб.) | Сумма со скидкой (в руб.) |
| 1 | 2001 | Товар 1 | 120,00р | 200 | |||
| 2 | 2002 | Товар 2 | 243,70р. | 150 |
Используя инструмент «Подбор параметра» определить:
1. При какой цене сумма со скидкой, полученная за Товар 1 будет равна 25000р.?
2. При каком количестве проданного товара 2 сумма без скидки будет равна 42000р.?
Принять, что при сумме более 25000 руб. предоставляется скидка, равная 5%, в противном случае 3%.
Задание 2
1. Используя инструмент «Таблица подстановки» рассчитать варианты платежей по простому займу в 10000р., выданному на 12 месяцев под 8%, 8,5%, 9%, 9,5%, 10%, 10,5%.
2. Используя инструмент «Таблица подстановки» рассчитать варианты платежей по простому займу в 10000р., выданному на 10, 11, 12, 13, 14 месяцев под 8%.
3. Используя инструмент «Таблица подстановки» рассчитать варианты платежей по простому займу в 10000р, 11000р., 12000р., 13000р., выданному на 10, 11, 12, 13, 14 месяцев под 8%.
Примечание. Расчет платежа выполняется по формуле: сумма займа * (100%+процент)/срок
Задание 3
Исходные данные
Составить сценарии поведения следующей модели, считая изменяемыми затраты на рекламу, среднее количество покупателей. Принять, что в году - 52 недели. При этом рассмотреть 3 случая:
· наихудший (минимальное кол-во клиентов определяется командой "Подбор параметра" для точке безубыточности, затраты на рекламу - 10000 руб/год);
· наилучший (100000 клиентов в неделю, затраты на рекламу 500000 руб/год.);
· средний (как среднее арифметическое клиентов и затрат на рекламу).
| Всего за неделю | Всего за год | ||
| Доход от одного покупателя | 34,78 | ||
| Расходы на одного покупателя | 30,12 | ||
| Прибыль от одного покупателя | вычислить! | ||
| Среднее кол-во покупателей | изменять! | ||
| Общая прибыль | вычислить! | вычислить! | |
| Накладные расходы | |||
| Отчисления на зарплату | расчитать! | 3 494 046 | |
| Оборудование | расчитать! | 1 635 511 | |
| Амортизация | расчитать! | 453 305 | |
| Реклама | расчитать! | изменять! | |
| Снабжение | расчитать! | 496 944 | |
| Прочее | расчитать! | 1 295 828 | |
| Всего | вычислить! | ||
| Текущая прибыль | вычислить! |
Дата добавления: 2019-01-14; просмотров: 1097; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!