Анализ данных в 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