(дисциплина – «Информационные технологии в менеджменте»)
Цель лабораторной работы: приобретение умений и навыков выполнения расчетов в программе для работы с электронными таблицами MS Office Excel, анализ данных и использование диаграмм.
Задание на лабораторную работу:
Компания ООО «Шина-Маркет» розничной продажей автомобильных шин мировых концертов. Головной офис компании расположен в г. Новосибирск, имеет 6 филиалов в Барнауле, Бийске, Кемерово, Омске и Томске. На основе данных количестве и составе заказов за январь 2019г. по филиалам, необходимо:
1) Рассчитать финансовые результаты (выручка, затраты, прибыль от продаж) в разрезе филиалов, проранжировать филиалы по уровню доходности
2) Провести анализ ежедневных продаж компании и представить результаты в наглядном виде.
3) Провести анализ доходности представленных брендов-производителей (АВС анализ) и предложить корректировки по ассортименту.
Методические указания по выполнению лабораторной работы:
1. Откройте файл «Заказы за январь 2019.xlxs »
В данном файле перечислены все заказы компании за период с 01.01.2019 по 31.01.2019 (рис. 2.1)
Рисунок 2.1. Список заказов
Столбец «№» указывает уникальный номер заказа в учетной системе
Столбец «Дата» отражает дату продажи товара
Столбец «Стоимость товара, руб.» показывает сумму выручки от продажи товара
Столбец «Себестоимость, руб.» показывает за какую цену товар был приобретен у поставщика
Столбец «Покупатель» отражает ФИО покупателя товара
Столбец «Производитель» указывает на бренд-производитель данного товара
Столбец «Название товара» отражает полное наименование модели и типоразмера проданного товара
Столбец «Филиал» отражает филиал, в котором была осуществлена продажа товара
2. Расчет наценки на заказы. После столбца «Себестоимость, руб.» добавьте 2 новых столбца. Для этого наведите курсор на подпись столбца Е и нажмите левой кнопкой мыши (выделение столбца), затем нажмите правой кнопкой мыши и в выпадающем меню выберите «Вставить» (рис. 2.2). Проделайте процедуру повторно добавив второй новый столбец. Введите название столбцов «Наценка, руб.» и «Наценка, %» соответственно.
Рисунок 2.2. Добавление новых столбцов
 |
Используя формулы представленные выше, заполните 2 новых столбца значениями.
3. Условное форматирование.
Аналитики компании установили, что для обеспечения прибыльности компании минимальная наценка должна находиться на уровне 12% и выше, а рекомендуемая на уровне не менее 15%.
Для того, чтобы выделить товары, которые нуждаются в корректировке цен используем инструмент условное форматирование.
Выделите ячейки F3:F192 (Столбец «Наценка, %») и выберите вкладку «Главная» > «Условное форматирование» > «Создать правило». В диалоговом окне «Создание правила форматирования» выберите пункт «Форматировать только ячейки, которые содержат», задайте значение «больше или равно 0,12», выберите форматирование с зеленой заливкой (рис. 2.3).
Рис. 2.3. Условное форматирование
Произведите условное форматирование наценки в красную заливку, если значение меньше 12%, и в желтую, если значение находится в диапазоне между 12% и 15%.
Рис. 2.4. Итоговый вид условного форматирования
Используя форматирование специалист по товарной группе сможет оперативно произвести корректировку цен на товары, находящихся в красной зоне (ниже минимальной наценки).
4. Корректировка наценки
Добавьте 3 новых столбца после «Наценка, %»:
- Новая наценка, %
- Новая наценка, руб.
- Новая стоимость товара, руб.
В столбце «Новая наценка, % » проведите корректировку наценку, используя функцию ЕСЛИ (подробно использование функции было рассмотрено в лабораторной работе №1). Если значение наценки менее 12% - необходимо заменить на 12%, если значение наценки 12% и выше. Протяните формулу на заказы.
В столбце «Новая наценка, руб.» рассчитайте значения как произведение новой скорректированной наценки и себестоимости.
Проведите условное форматирование столбца «Новая наценка, руб.» по аналогии с пунктом 3. Убедитесь, что все знания находятся в желтой и зеленой зоне.
В столбце «Новая стоимость товара, руб. » рассчитайте значения как сумма новой наценки и себестоимости.
5. Расчет экономического эффекта от корректировки цен
В ячейках С193:D193 и H193:I193 найдите сумму по столбцам (рис. 2.5). Используя формулу наценки найдите среднюю наценку до корректировки и после.
Рис. 2.5. Расчет экономического эффекта от корректировки цен
В ячейке С194 найдите сумму, которая была бы дополнительно привлечена в компанию при новой наценки (Наценка, руб. – Новая наценка, руб.)
6. Создание сводных таблиц.
Выделите диапазон данных для анализа A2:J192 и перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица».
Должно появиться диалоговое окно «Создание сводной таблицы».
Настройте отображение данных, как обозначено на рис. 2.6.
Строки - Филиал
Значения – Сумма по полю Стоимость товара, руб.; Сумма по полю Себестоимость, руб.; Сумма по полю Наценка, руб.
Фильтры - Производитель
Рис. 2.6. Данные для сводной таблицы
Область фильтра отчета находится в верхней части сводной таблицы и является необязательной. Добавление полей в область фильтра отчета позволяет фильтровать данные, находящиеся в полях. Область фильтра отчета может пригодиться в случае динамической фильтрации выводимых результатов.
 |
7. Форматирование сводной таблицы.
Повторите форматирование сводной по аналогии, представленной на рис. 2.7, используя навыки, полученные на лабораторной №1.
Рис. 2.7. Форматирование сводной таблицы
8. Использование круговой диаграммы
Для анализа вклада каждого филиала в общий объем выручки компании используем круговую диаграмму.
 |
Выделите фрагмент таблицы, для которого создается диаграмма (А3:D11). На вкладке «Вставка» в группе «Диаграммы» щелкните по кнопке с типом диаграмм и в галерее выберите вид диаграммы «Круговая диаграмма» (рис. 2.8.)
Рис. 2.8. Круговая диаграмма
Чтобы добавить числовые подписи данных, нажмите на зеленый знак 
в верхнем правом углу диаграммы по отметьте пункт «Подписи данных».
Измените заголовок диаграммы на следующий «Выручка от продажи товаров в филиалах».
9. Использование гистограммы
Для анализа финансовых показателей каждого филиала в комплексе используйте гистограмму.
Выделите фрагмент таблицы, для которого создается диаграмма (А3:D11). На вкладке «Вставка» в группе «Диаграммы» щелкните по кнопке с типом диаграмм и в галерее выберите вид диаграммы «Гистограмма»
 |
Рис. 2.9. Гистограмма
При оформлении осей диаграммы может потребоваться настройка параметров оси. Это можно сделать с помощью диалогового окна Формат оси.
Выделите ось диаграммы. На вкладке Работа с диаграммами/Формат нажмите кнопку Формат выделенного элемента. В разделе Параметры оси окна Формат оси выберите и установите в поле «Основные деления » 50000. Для оси значений можно установить максимальное и минимальное значение оси, цену основных и промежуточных делений, установить другие параметры.
Рис. 2.10. Параметры оси диаграммы
Проанализируйте полученные данные и сделайте вывод о том подразделении компании, которое генерирует наибольшую выручку и том, которое является для компании наименее прибыльным.
10. Использование графика
Перейдите на лист «Заказы клиентов», выделите диапазон A2:J192 и перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица»
В качестве строк используйте поле «Дата»; в качестве значений – поля «Сумма по полю Стоимость товара, руб.»; «Сумма по полю Себестоимость, руб.»; «Сумма по полю Наценка, руб.»
Добавьте заголовок таблицы «Анализ ежедневных продаж компании» и примените форматирование по аналогии с рис. 2.11.
Рис. 2.11. Сводная таблица ежедневных продаж
Выделите область данных и добавьте диаграмму «График». Добавьте название диаграммы «Анализ ежедневных продаж»
Рис. 2.12. График
Проанализируйте график и определите день в который, продажи были максимальными / минимальными.
11. ABC-анализ поставщиков продукции.
Создайте новый лист под названием «АВС-анализ»
 |
На основе данных на листе «Заказы клиентов» создайте новую сводную таблицу (в качестве строк задайте «Производитель», в качестве значение столбец «Наценка, руб.».)
Т.к. сводные таблицы не позволяют использовать данные для фильтрации и дальнейшего расчета, скопируйте значения в сводной таблицы на свободное поле. Для этого выделите все ячейки таблицы, нажмите сочетание клавиш CTRL+V (данные скопированы в буфер). Выделите ячейку D3 и нажмите правую кнопку мыши. В появившемся меню выберите «Параметры вставка» - «Значения» (рис. 2.13)
Рис. 2.13. Вставка значений сводной таблицы
Добавьте 3 столбца справа в новую таблицу: «Доля», «Доля накопительным итогом» и «Группа».
Выделите ячейки и отфильтруйте значения по убыванию суммы наценки («Данные» – «Фильтр» – «По убыванию»)
«Доля» - доля продаж шин данного бренда в общем итогу.
«Доля нарастающим итогом» - равна столбцу «Доля» для первого бренда, для второго и последующих рассчитывается как сумма значения по предыдущей строке и доля текущего бренда (рис. 2.14)
Рис. 2.14 Доля нарастающим итогом
Столбец «Группа» - задайте формулу =ЕСЛИ(G4>0,8;ЕСЛИ(G4>0,95;"C";"B");"A") определяющую группу, в которую попадает товар.
Проведя группировку товаров, компания получает аналитический отчет о том, какие товары обеспечивают основные денежные поступления. Дальнейшей целью является увеличение продаж целевых товаров из группы «А» и снижение доли не эффективных товаров из группы «С».