Студопедия — Анализ данных в Excel (4 часа)
Студопедия Главная Случайная страница Обратная связь

Разделы: Автомобили Астрономия Биология География Дом и сад Другие языки Другое Информатика История Культура Литература Логика Математика Медицина Металлургия Механика Образование Охрана труда Педагогика Политика Право Психология Религия Риторика Социология Спорт Строительство Технология Туризм Физика Философия Финансы Химия Черчение Экология Экономика Электроника

Анализ данных в Excel (4 часа)






Цель работы: приобретение навыков анализа данных в Excel

 

Задача.

Произвести анализ данных в трех торговых книгах.

Произвести подбор оптимального показателя роста объема продаж.

 

Методические рекомендации к выполнению работы:

1. Создать Торговую книгу №1.

На первом листе создать таблицу, содержащею информацию об агентах, филиалах, моделях, цене продаваемого товара, количестве продаж, выручке и способе доставки. Например:

 
 

 


Колонка «Выручка» заполняется с помощью формулы произведения цены товара на количество проданного товара соответственно каждым торговым агентом.

На этом же листе ниже создать таблицы, отображающую количество товара на складе соответственно каждой модели. Например:

 
 

 

 


Колонка «Продано» заполняется с помощью формулы суммирующей количество товара одной модели разными торговыми агентами. Дать листу название «Торг, книга 1»

2. Создать Торговые книги №2 и № 3

Использую команду переместить/скопировать, находящуюся в контекстном меню первого листа, поставив галочку напротив «Создать копию» и в окошке «Перед листом: выделить «(Переместить в конец)», создать две копии «Торг, книга 1» и переименовать их соответственно в «Торг, книга 2» и «Торг, книга 3».

На получившихся листах в основной таблице поменять значения цены товара и количества проданного товара разными торговыми агентами.

Во второй торговой книге в нижней таблице использовав формулы связи заполнить колонку «Склад» соответственно значениями из нижней таблицы первой торговой книги колонки «Осталось». Аналогично повторить операцию между второй и третьей торговыми книгами.

 

3.Отсортировать данные каждой книги по фамилиям и отфильтровать по филиалам и способу доставки.

На первом листе, выделив верхнею таблицу выбрать в верхнем меню «Данные», затем «Сортировка» и в верхнем окошке «Сортировать по» выбрать столбец (слово) «Фамилия», кликнуть на «ОК». Повторить операцию на втором и третьем листах.

На втором листе скопировать шапку верхней таблицы вниз (на свободную строчку ниже таблицы «Количество товара на складе»). Под новой шапкой в колонке «Филиал» на следующей строчке написать один из филиалов, к примеру, Алматы. В верхнем меню выбрать «Данные», затем «Фильтр», затем «Расширенный фильтр». Поставить флажок напротив «Скопировать результат в другое место», заполнить ячейки: «Исходный диапазон» - выделить всю верхнею таблицу; «Диапазон критериев» - выделить новую таблицу (шапку и строчку ниже со словом «Алматы»); «Поместить результат в диапазон» - выделить некоторое достаточное пространство ниже новой таблицы по ширине оной. Кликнуть на «ОК». Получившийся результат должен выглядеть так:

 
 

 


На третьем листе проделать аналогичную операцию, за исключением того, что под новой шапкой в колонке «Доставка» на следующей строчек написать один из возможных вариантов доставки.

 

4. Создать сводную ведомость.

Соединить формулами связи, а именно абсолютной адресацией, ячейки чистого листа с соответственными ячейками «Торг, книга 1», так чтобы новая таблица содержала столбцы «№», «Фамилия», «Филиал», «Кол-во проданного», «Выручка», а также информацию обо всех торговых агентах.. Увеличить количество строк отведенных под одного торгового агента от одного до трех, так чтобы все строки содержали одинаковую информацию. Затем во второй и третьей строках соответственно каждого торгового агента изменить формулы связи в столбцах «Кол-во проданного» и «Выручка», так чтобы данные ячейки ссылались на соответственно «Торг, книга 2» и «Торг, книга 3». Результат должен соответствовать:

 
 

 

 


Скопировать полученный лист, используя команду переместить/скопировать, поставив флажок на «Создать копию».

На одном из полученных листов в верхнем меню выбрать «Данные», затем «Итоги». Проверить наличие флажков на «Итоги под данными», в окне «Добавить итоги по» - на «Кол-во проданного» и «Выручка», в окне «Операция» выбрать «Сумма», кликнуть на «ОК». Дать название листу - «Сводная ведомость».

Повторить аналогичную операцию со вторым листом, только в данном случае в верхнем меню выбрать «Данные», а затем «Сводная таблица». Дать название листу - «Сводная таблица».

5. Использую формулы связи между листами создать таблицу для проведения финансового анализа.

Новая таблица на листе «Фин. анализ» должна содержать столбцы «№», «Фамилия», «Выручка1», «Выручка2» и «Выручка3». Последние три столбца связываются соответственно стремя торговыми книгами. Подбить итоги по выручкам трех периодов.

На этом же листе, ниже подсчитать СПОП (средний показатель объема продаж) для итогов «Выручка1» и «Выручка2», «Выручка2» и «Выручка3» по формуле:

СПОП = (х2 - xl)/xl. Найти среднее значение.

 

В еще одной таблице ниже вписать значения среднего СПОП, полученного согласно вычислениям, а также произвольно выбрать значения максимального и минимального значений. Значение текущего СПОП равносильно среднему. В основную таблицу добавить столбец «План» и заполнить его с помощью формул: План = Выручка3 + Выручка3* *Ср. СПОП. (Последний параметр берется из самой нижней таблицы.) Итог посчитать с помощью формулы суммы.

6. Подобрать оптимальный параметр среднего СПОП.

В верхнем меню выбрать «Сервис», затем «Подбор параметра». Заполнить окна: «Установить в ячейке» - Итог Плана, «Значение» - размер предполагаемой желательной прибыли в следующем периоде, «Изменяя значение ячейки» - Средний СПОП из нижней таблицы. Кликнуть на «ОК».

По завершении операции значение среднего СПОП измениться на оптимальный вариант при данной предполагаемой желательной прибыли, а столбец «План» будет показывать размер прибыли, который должен будет принести каждый торговый агент, чтобы в сумме им вместе выполнить план.

Повторив операцию, изменяя размер будущей прибыли можно подобрать не только желательный, но и реальный параметр среднего СПОП, ориентируясь на текущее, максимальное и минимальное значения.

Контрольные вопросы для самостоятельного выполнения:

1. Как создать сводную таблицу?

2. Для чего используются сценарии в Excel?

3. Как произвести подбор параметра?

4. Как осуществить прогноз продаж на следующий год? Перечислите этапы выполнения задачи.

 

Рекомендуемая литература: [7], [8], [25] – д.

 

 

Тренинг №5







Дата добавления: 2014-11-10; просмотров: 775. Нарушение авторских прав; Мы поможем в написании вашей работы!



Картограммы и картодиаграммы Картограммы и картодиаграммы применяются для изображения географической характеристики изучаемых явлений...

Практические расчеты на срез и смятие При изучении темы обратите внимание на основные расчетные предпосылки и условности расчета...

Функция спроса населения на данный товар Функция спроса населения на данный товар: Qd=7-Р. Функция предложения: Qs= -5+2Р,где...

Аальтернативная стоимость. Кривая производственных возможностей В экономике Буридании есть 100 ед. труда с производительностью 4 м ткани или 2 кг мяса...

Упражнение Джеффа. Это список вопросов или утверждений, отвечая на которые участник может раскрыть свой внутренний мир перед другими участниками и узнать о других участниках больше...

Влияние первой русской революции 1905-1907 гг. на Казахстан. Революция в России (1905-1907 гг.), дала первый толчок политическому пробуждению трудящихся Казахстана, развитию национально-освободительного рабочего движения против гнета. В Казахстане, находившемся далеко от политических центров Российской империи...

Виды сухожильных швов После выделения культи сухожилия и эвакуации гематомы приступают к восстановлению целостности сухожилия...

Условия приобретения статуса индивидуального предпринимателя. В соответствии с п. 1 ст. 23 ГК РФ гражданин вправе заниматься предпринимательской деятельностью без образования юридического лица с момента государственной регистрации в качестве индивидуального предпринимателя. Каковы же условия такой регистрации и...

Седалищно-прямокишечная ямка Седалищно-прямокишечная (анальная) ямка, fossa ischiorectalis (ischioanalis) – это парное углубление в области промежности, находящееся по бокам от конечного отдела прямой кишки и седалищных бугров, заполненное жировой клетчаткой, сосудами, нервами и...

Основные структурные физиотерапевтические подразделения Физиотерапевтическое подразделение является одним из структурных подразделений лечебно-профилактического учреждения, которое предназначено для оказания физиотерапевтической помощи...

Studopedia.info - Студопедия - 2014-2024 год . (0.01 сек.) русская версия | украинская версия