Бизнес-аналитика в Excel: формируем и визуализируем данные

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

С научной точки зрения бизнес-анализ — это процесс преобразования различных данных в управленческую информацию, позволяющую повысить конкурентоспособность компании на рынке и увеличить ее эффективность. Из этого определения понятно, что формирование качественной бизнес-аналитики требует не только консолидации данных из различных источников, но и высокой оперативности как поступления необходимой информации, так и ее обработки.

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

Именно поэтому для экономического (т. е. постфактного) анализа работы компании широко применяются специализированные программы, а вот для целей бизнес-анализа на практике предпочитают пользоваться всем известным табличным редактором Excel. И для этого есть веские основания, которые мы и рассмотрим.

ПРИМЕР ABC-АНАЛИЗА ПРОДАЖ В EXCEL ПО НЕСКОЛЬКИМ ФАКТОРАМ

Рассмотрим вариант ABC-анализа по двум факторам:

  • продажи в рублях;
  • доходность в рублях.

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

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

Исходя из сказанного, необходимо рассматривать продажи в рублях и доходность в рублях вместе.

На листе «Исходные данные» (см. «Сервис форм», код доступа — на с. 119) имеется выгрузка продаж и доходности позиций по магазинам за месяц. Построим из этой выгрузки сводную таблицу со средними показателями продаж и доходности позиций, отсортируем позиции по убыванию продаж (рис. 1). Среднее берется для того, чтобы учесть разную географию позиций. В реальном примере нам также следует разделить продажи позиций на количество дней с продажами или остатками, чтобы сравнить показатели с учетом количества дней их представленности в магазине.

Нас интересует вопрос, каким образом реализовать ABC-анализ для двух факторов.

Вариант 1: проводить раздельный ABC-анализ и присваивать каждой позиции две буквы по шкале продаж и доходности в рублях. Например, две АА будут говорить о высокодоходной позиции с большой долей в продажах, BC — о позиции со средней доходностью с низкими продажами.

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

Вариант 2: вывод интегрального показателя, в котором учитываем все рассматриваемые показатели.

Понятно, что мы не можем просто сложить продажи в рублях с доходностью в рублях и делать ABC-анализ по общей сумме, так как по умолчанию доходность — это только часть продаж, и при сложении мы будем учитывать продажи с большим весом.

На помощь в этом случае приходит нормирование показателя. Одним из способов нормирования является линейная нормировка. Ее формула выглядит так:

Нормированное значение = (Значение показателя – Минимум группы) / (Максимум группы – Минимум группы).

Чтобы нормировать показатель продаж конкретного товара, из значения продаж данного товара нужно вычесть минимум продаж группы и разделить полученное значение на разницу между максимумом и минимумом продаж группы.

П. М. Чеглаков, ведущий аналитик

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 11, 2018.

Собираем когортный анализ/анализ потоков на примере Excel

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

Легко рисовать картинки, но для того, чтобы они считались и отображались правильно “под капотом” нужно проделать немало работы. В этой статье мы поговорим о том, как реализовать когортный анализ. Я расскажу про реализацию при помощи Excel, а в другой статье при помощи R.

Хотим мы этого или нет, но по факту Excel это инструмент анализа данных. Более “высокомерные” аналитики будут считать, что это слабый и не удобный инструмент. С другой стороны по факту сотни тысяч людей делают анализ данных в Excel и в этом отношении он легко побьет R / python. Конечно, когда мы говорим о advances analytics и машинном обучении, мы будем работать на R / python. И я был бы за то, чтобы большая часть аналитики делалась именно этими инструментами. Но стоит признать факты, в Excel обрабатывают и представляют данные подавляющее большинство компаний и именно этим инструментом пользуются обычные аналитики, менеджеры и product owners. Вдобавок Excel трудно победить в части простоты и наглядности процесса, т.к. вы мастерите свои расчеты и модельки буквально руками.

И так, как же нам сделать когортный анализ в Excel? Для того, чтобы решать подобные задачи нужно определить 2 вещи:

Какие данные у нас в начале процесса

Как должны выглядеть наши данные в конце процесса.

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

Дата регистрации клиента

Объем продаж этого клиента в эту календарную дату

Первая сложность, которую предстоит преодолеть — это получить эти данные. Если у вас правильное хранилище, то они уже должны быть у вас. С другой стороны, если пока реализовали только запись данных о совокупных продажах по дням, то данные по клиентам у вас есть только на “проде”. Для когортного анализа вам придется реализовать ETL и сложить в ваше хранилище данные в разрезе клиентов, иначе у вас ничего не выйдет. И лучше всего если вы разделите “прод” и аналитику в разные базы, т.к. У аналитических задач и задач функционирования вашего продукта разные цели конкуренция за ресурсы. Аналитикам нужны быстрые агрегаты и расчеты на по многим пользователям, продукту нужно быстро обслужить конкретного пользователя. Об организации хранилища я напишу отдельную статью.

Итак, вы имеете стартовые данные:

Первое, что нам нужно сделать это преобразовать их в “лесенки”. Для этого нужно над этой таблицей построить сводную таблицу, по строкам — дата регистрации, по столбцам — календарная дата, в качестве значений — кол-во id клиентов. Если вы верно извлекли данные, то у вас должен получится вот такой треугольник/лесенка:

В целом лесенка это наш когортный график, в котором каждая строка отображает динамику отдельной когорты. Клиенты во времени в этой отображении двигаются только внутри одной строки. Таким образом динамика когорты отображает развитие отношений с группой клиентов пришедший в один период времени. Часто для удобства и без потери качества, можно объединить когорты в “блоки” строк. Например, вы можете сгруппировать их по неделям и месяцам. Точно так же вы можете сгруппировать и колонку, т.к. Возможно ваш темп развития продукта не требует детализации до дней.

На основе этой лесенки вы можете влоб построить график из моей статьи (я правда указывал, что сгруппировал несколько строк в одну, чтобы когорт было поменьше):

Это график с накопительными областями, где каждый ряд — это строка, по горизонтали даты.

Чуть сложнее логика для реализации графика “потоков”. Для потоков мы должны сделать некоторые дополнительные вычисления. В логике потоков каждый клиент прибывает в различных состояниях:

  1. Новый — любой клиент, у кого разница между датой регистрации и календарной дате <7 дней
  2. Реактивированный — любой клиент, кто уже не новый, но в прошлом календарном месяце не генерировал выручку
  3. Действующий — любой клиент, кто не новый, но в в календарном месяце генерировал выручку
  4. Ушедший — любой клиент, кто не генерирует выручку 2 месяца подряд

Во-первых вам стоит в компании закрепить эти определения, чтобы вы могли корректно реализовать эту логику и автоматически рассчитывать состояния. Эти 4 определения имеют далеко идущие последствия в целом и для маркетинга. Ваши стратегии по привлечению, удержанию и возвращению будут базироваться на том, в каком состоянии вы считаете находится клиент. А если вы начнете внедрять модели машинного обучения в прогнозировании ухода клиентов, то определения станут вашим краеугольным камнем успешности этих моделей. Вообще про организацию работы и важность аналитической методологии я напишу отдельную статью. Выше я привел просто пример того, какими могут быть эти определения.

В Excel вам нужно создать дополнительную колонку, куда вписать описанную выше логику. В нашей случае нам придется “попотеть”. У нас есть 2 типа критериев:

  • Разница между датой регистрацией и календарной датой — эти данные есть у каждой строки и тут просто нужно ее посчитать (вычитание дат в Excel просто дает разницу в днях)
  • Данные о выручке в текущем и прошлом месяце. Эти данные нам не доступны в строке. Более того, с учетом того, что в нашей таблице не гарантирован порядок, то вы не можете точно сказать, где у вас данные по другим дням месяца для этого клиента.

Решить проблему 2 типа критериев можно 2 способами:

  1. Попросите сделать это в базе данных. SQL позволяет при помощи аналитической функции вычислить для каждого клиента сумму выручки за текущий и прошлый месяц (для текущего месяца SUM(revenue) OVER (PARTITION BY client_id, calendar_month, а потом LAG, чтобы получить смещение по прошлому месяцу):
  2. В экселе вам придется реализовать это так:
    • Для текущего месяца: СУММЕСЛИ(), критериями будет id клиента и месяц ячейки календарного дня
    • Для прошлого месяца: СУММЕСЛИ(), критериями будет id клиента и месяц ячейки календарного дня минус ровно 1 календарный месяц. При этом обращу внимание, что вы должны вычесть именно календарный месяц, а не 30 дней. Иначе вы рискуете получить смазанную картину из-за неодинакового числа дней в месяцах. Также используйте функцию ЕСЛИОШИБКА, чтобы заменить ошибочные значения для клиентов у кого не было прошлого месяца.
Читайте также:  Копирование формул без изменения адресов относительных ссылок

Добавив колонки выручки текущего месяца, прошлого месяца вы можете построить вложенное условие ЕСЛИ, учитывающие все факторы (разницу дат и суммы выручки в текущем/прошлом месяце):
ЕСЛИ( разница дат <7; “новый”;
ЕСЛИ( И (выручка прошлого месяца = 0; выручка текущего месяца > 0); “реактивация”;
ЕСЛИ( И (выручка прошлого месяца > 0; выручка текущего месяца > 0); “действующий”
ЕСЛИ( И (выручка прошлого месяца = 0; выручка текущего месяца = 0); “ушедший”; “ошибка”))))

“Ошибка” нужна тут только для контроля, что вы не ошиблись в записи. Логика критериев состояний MECE (https://en.wikipedia.org/wiki/MECE_principle), т.е. Если все сделано правильно, то каждому будет проставлено одно состояние из 4-х

У вас должно получится вот так:

Теперь эту таблицу можно пересобрать при помощи сводной таблицы в таблицу для построения графика. Вам нужно трансформировать ее в таблицу:

Календарная дата (колонки)
Состояние (строки)
Кол-во id клиентов (значения в ячейках)

Далее мы просто должны на основе данных построить диаграмму столбчатую диаграмма с накоплениями, по оси Х календарная дата, ряды это состояния, кол-во клиентов это высота столбцов. Вы можете поменять порядок состояний на графике, изменив порядок рядов в меню “выбрать данные”. В итоге мы получим такую картину:

план график продвижения в рамках года

Из данной статьи вы узнаете, как эффективно распределить активность по продвижению товаров из разных товарных групп в рамках года.

Ситуация – у нас более 10 товарных групп с разной сезонностью. Вопрос, в какие месяцы эффективнее всего запланировать и провести мероприятия по стимулированию сбыта, для того, чтобы затраты дали максимальную отдачу?

Для расчета значений тренда:

функция Excel линейн

  1. Определим коэффициенты уравнения линейного тренда y=bx+a с помощью функции Excel =Линейн(). Для этого в ячейки Excel вводим функцию =линейн(объёмы продаж за 5 лет; номера периодов; 1;0). Выделяем 2 ячейки, в левой – формула =линейн(), нажимаем комбинацию клавиш в следующей последовательности (F2 + Ctrl+Shift+Enter). Excel рассчитает для нас значение коэффициентов a и b.
  2. Рассчитываем значения тренда. Для этого в уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде. Получаем y-значения линейного тренда для каждого периода (см. вложенный файл).

Финансовый анализ в Excel с примером

​с клавиатуры. Далее​Второй результат работы:​ больше в сравнении​ клавиш Ctrl+Alt+F5 или​ в Power Query​

​ – отсутствуют в​ и на вкладке​ по новой формуле.​условного форматирования в​ Ссылка на статью​ экран протягиваем функцию​ по счету наименьшего​. В строке формул​«СЧЁТЕСЛИ»​ условие синхронизации или​

Пример финансового анализа предприятия в Excel

​ из содержимого строки​ Далее обворачиваем полученное​ кликаем по первой​

  • ​Построить таблицу статистических характеристик​
  • ​ с начальным периодом.​
  • ​ кнопкой​

​ их необходимо преобразовать​ старом списке, т.е.​ «Заливка» укажите зеленый​ Например, так.​

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

​ поможет команда «Описательная​Внеоборотные активы приращиваются с​

​Обновить все (Refresh All)​ сначала в умные​ были добавлены. Изменения​ цвет.​В ячейке А6 мы​

Баланс.

​можно выделять различные​Получится так.​ заполнения до конца​В поле​ выражение​

  1. ​ щелкаем по кнопке​ будет являться обязательным,​ активной одну из​ которыми ставим два​ мы сравниваем, во​ статистика» (пакет «Анализ​ более высокими темпами,​на вкладке​ таблицы. Для этого​ цены также хорошо​На всех окнах жмем​ нажали функцию «Среднее»,​ данные – одинаковые,​Теперь можно изменить любую​ столбца вниз. Как​Значения на начало и на конец года.
  2. ​«Массив»​«ЕСЛИ»​«OK»​ что выгодно отличает​ ячеек, находящуюся в​ знака​ второй таблице. Получилось​Значения на начало и на конец года.
  3. ​ данных»). Диалоговое окно​ чем оборотные.​Данные (Data)​ выделим диапазон с​ видны.​ ОК.​Изменения.
  4. ​ посчиталось среднее значение​ разные, выделять заполненные​ часть диаграммы, нажав​ видим, обе фамилии,​следует указать координаты​без кавычек и​.​ данный вариант от​Динамика в процентах.
  5. ​ указанных не совпавших​«-»​ выражение следующего типа:​ заполним следующим образом:​

​Собственный капитал предприятия больше,​.​ данными и нажмем​Плюсы​После ввода всех условий​ всех чисел в​

​ или пустые ячейки,​ на нее два​

  1. ​ которые присутствуют во​ диапазона дополнительного столбца​ открываем скобку. Далее,​Происходит запуск окна аргументов​
  2. ​ ранее описанных.​ строках.​. В нашем случае​
  3. ​=A2=D2​После нажатия ОК отображаются​ чем заемный. Причем​Плюсы​
  4. ​ на клавиатуре сочетание​этого способа: просто​ для форматирования Excel​

Статистический анализ данных в Excel

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

​Хотя, конечно, в каждом​

  1. ​ основные статистические параметры​ темпы роста собственного​: Пожалуй, самый красивый​ Ctrl+T или выберем​ и понятно, “классика​ автоматически выделил цветом​ ячейке В6 выбрали​Функция СРЗНАЧ.
  2. ​ по нашему условию​ В появившемся окне​ отсутствуют в первой,​, который мы ранее​ было работать, выделяем​СЧЁТЕСЛИ​ нужно сравнить.​ метод условного форматирования.​–(A2:A7<>D2:D7)​ конкретном случае координаты​ по данному ряду.​Функция ДИСП.
  3. ​ превышают динамику заемного.​ и удобный способ​ на ленте вкладку​ жанра”, что называется.​ тех сотрудников зарплаты​ функцию «Количество чисел»​Функция СТАНДОТКЛОН.
  4. ​ (больше, меньше, «Да»,​ менять параметры, цвет,​ выведены в отдельный​ преобразовали с помощью​ в строке формул​. Как видим, наименования​Функция МЕДИАНА.
  5. ​Выполняем переход во вкладку​ Как и в​Щелкаем по кнопке​ будут отличаться, но​Скачать пример финансового анализа​Кредиторская и дебиторская задолженность​Размах варьирования.
  6. ​ из всех. Шустро​Главная – Форматировать как​ Работает в любой​ которых изменились по​ – посчиталось количество​ «Нет»), т.д. Смотрите​ расположение, т.д.​ диапазон.​

​ функции​ значение​ полей в этом​ под названием​

​ предыдущем способе, сравниваемые​«OK»​ суть останется одинаковой.​

Функция ЭКСЦЕСС.

​ в Excel​ приращиваются примерно в​ работает с большими​

​ таблицу (Home -​ версии Excel.​ сравнению с предыдущим​

​ заполненных ячеек в​ об этом в​Эти же функции​При сравнении диапазонов в​ЕСЛИ​

  1. ​«ЕСЛИ»​ окне соответствуют названиям​«Главная»​ области должны находиться​.​Щелкаем по клавише​Это третий окончательный результат​ одинаковом темпе.​Анализ данных.
  2. ​ таблицами. Не требует​ Format as Table)​Генерация случайных чисел.
  3. ​Минусы​Результат генерации.
  4. ​ месяцем.​ столбце В.​ статье «Условное форматирование​ можно найти на​ разных книгах можно​. Делаем все ссылки​и жмем по​ аргументов.​. Делаем щелчок по​ на одном рабочем​Оператор производит расчет и​Enter​ работы в данном​​Настройка панели БД.
  5. ​ ручных правок при​. Имена созданных таблиц​тоже есть. Для​​Как посчитать количество заполненных​ в Excel» здесь.​ закладке «Работа с​Прогрессия.
  6. ​ использовать перечисленные выше​Первый результат.
  7. ​ абсолютными.​ иконке​Устанавливаем курсор в поле​ кнопке​Гистограмма.
  8. ​ листе Excel и​Второй результат.
  9. ​ выводит результат. Как​, чтобы получить результаты​ примере.​Для реализации статистических методов​ изменении размеров таблиц.​

​ можно подкорректировать на​ поиска добавленных в​В определенном условии существенное​

ABC и XYZ анализ в Excel с примером расчета товарного ассортимента

​ результата. Преобразованный и​ режиму методом касательных.​ данных.​ формулами и стандартными​ а также построить​

​ вклад лишь примерно​ настроив соответствующим образом​ Всем товарам присвоены​ наименованию функции​«Вставить функцию»​С​ товара. Но величина​ инструментов​ период (за месяц,​

ABC-анализ в Excel

​ успешной организации труда​Excel – это лучший​ в первую ячейку​ детализированный, данный закон​ В этом случае​

​По умолчанию, надстройка определяет​ средствами MS EXCEL.​

  • ​ диаграмму Парето (по​ в 20% выручки.​ стандартную диаграмму типа​
  • ​ те же самые​ПОИСКПОЗ​
  • ​. Открывается список недавно​– оставшиеся​ удельного веса отображается​

​«Сортировка и фильтр»​ как правило).​ всех структурных подразделений.​ в мире универсальный​

  1. ​ формулу: =B2/$B$17 (ссылку​ нашел применение в​ границы групп определены​ границы классов (групп​
  2. ​ Это нам позволит,​ оси Х указывается​ И лишь отдельные​
  3. ​ Гистограмма с группировкой.​ категории, только при​

​в строке формул.​ используемых операторов. Нам​5%​ в числовом формате,​на ленте.​Наша примерная таблица элементарна.​ Плановые показатели определяются​ аналитический инструмент, который​

​ на «сумму» обязательно​

  • ​ разработке рассматриваемых нами​ из соотношения 90,08%;​
  • ​ товаров) методом касательных,​ во-первых сравнить трудозатраты​ количество проданного товара,​ клиенты-звезды вносят вклад​Предложенная классификация клиентов основана​ этом строки не​Затем мы возвращаемся в​
  • ​ нужна функция​.​ а нам нужно​Можно также поступить по-другому.​

​ Но на предприятии​ по каждой линейке​

  • ​ позволяет не только​
  • ​ делаем абсолютной). «Протягиваем»​ методов.​
  • ​ 7,87% и 2,05%.​ который является наиболее​
  • ​ на выполнение расчетов​

​ по оси Y​ в выручку, существенно​ на некоторых допущениях,​ изменили своего начального​ окно аргументов функции​ПОИСКПОЗ​

​Таким образом, всем товарам,​

  1. ​ трансформировать его в​ Выделяем указанный выше​ имеет смысл продукты​ продукции, по каждому​ обрабатывать статистические данные,​
  2. ​ до последней ячейки​Метод ABC позволяет рассортировать​
  3. ​ В группу А​ гибким среди десятков​ и построение диаграммы,​ – % выручки​
  4. ​ перекрывая суммарный вклад​ связанных с распределением​
  5. ​ положения.​ПОИСКПОЗ​. Так как в​
  6. ​ накопленная доля удельного​ процентный. Для этого​ диапазон таблицы, затем​ распределить по товарным​ филиалу, магазину и​ но и составлять​ столбца.​
  7. ​ список значений на​ теперь включено значительно​ других. Суть метода​ а во-вторых проверить​ накопительным итогом).​ остальных клиентов.​
  8. ​ выручки по клиентам.​Урок:​
  9. ​. Как видим, в​ списке её нет,​ веса которых входит​ выделяем содержимое столбца​
  10. ​ перемещаемся во вкладку​ позициям, привести артикулы,​

АВС-анализ товарного ассортимента в Excel

​ прогнозы с высокой​Посчитаем долю нарастающим итогом.​ три группы, которые​ больше товаров 617​ заключается в определении​ корректность работы самой​Примечание​Вывод​ Прежде чем их​Мастер функций в Экселе​ поле​

  1. ​ то жмем по​ в границу до​«Удельный вес»​«Главная»​ продажи в штуках.​ менеджеру (если есть​ точностью. Для того​ Добавим в таблицу​ оказывают разное влияние​ против 343. Группы​Сортировка.
  2. ​ границ групп по​ надстройки.​: Границы классов выделены​: Перед применением метода​ озвучить отметим следующие​Итоговая строка.
  3. ​Программа Excel способна значительно​«Искомое значение»​ надписи​80%​. Затем перемещаемся во​и выполняем щелчок​ Для более детального​ такая необходимость). Рассмотрим,​ чтобы оценить некоторые​ 4 столбец «Накопленная​ на конечный результат.​ А и В​Доля.
  4. ​ точкам изгиба кривой​Вызовем диалоговое окно надстройки,​ на диаграмме бордовыми​ ABC-анализа исследуйте распределение​ моменты:​ облегчить проведение ABC-анализа​появились данные заданные​«Другие функции…»​, присваиваем категорию​ вкладку​ по кнопке​ анализа – указать​ как составить план​ возможности Excel в​ доля». Для первой​Благодаря анализу ABC пользователь​Накопленная доля.
  5. ​ значительно расширились за​ Парето. Этот метод​ чтобы ввести необходимые​ линиями (технически это​ исследуемого показателя (в​Произвольность установления границ классов​Парето.
  6. ​ для пользователя. Это​ оператором​.​A​

​«Главная»​«Сортировка и фильтр»​ себестоимость, рассчитать рентабельность​ продаж на месяц​ области прогнозирования продаж,​ позиции она будет​

XYZ-анализ: пример расчета в Excel

​ сможет:​ счет товаров группы​ позволяет точно определить​ параметры. Для этого​ сделано с помощью​

​ данном случае выручки)​. Например, в​ достигается использованием такого​СУММЕСЛИ​Снова производится запуск окна​. Товарам с накопленным​. На ленте в​

​, расположенной в блоке​ и прибыль.​ в Excel.​ разберем практический пример.​ равна индивидуальной доле.​выделить позиции, имеющие наибольший​ С.​ границы группы на​ на вкладке fincontrollex.com​ горизонтальных и вертикальных​ по объектам (клиентам).​класс А​ инструмента, как сортировка.​. Но это ещё​

Читайте также:  Примеры использования функции МОБР в Excel матрицах

​Мастера функций​ удельным весом от​ группе настроек​ инструментов​Анализ выполнения плана по​У нас есть развивающееся​Рассчитаем прогноз по продажам​ Для второй позиции​

​ «вес» в суммарном​

  1. ​Примечание​ основании изменения скорости​ в группе ABC​планок погрешностей​Теперь перейдем к вычислениям.​попадают клиенты, обеспечивающие​
  2. ​ После этого производится​ не все. Переходим​
  3. ​. Опять переходим в​80%​«Число»​

​«Редактирование»​ позициям позволяет сравнить​

  1. ​ предприятие, которое систематически​ с учетом роста​ – индивидуальная доля​ результате;​
  2. ​: К сожалению, при​ роста суммы и​ Analysis Tool нажмите​
  3. ​).​ Сначала проведем АВС​ 80% выручки. Почему​

​ подсчет индивидуального удельного​ в это поле​

  1. ​ категорию​до​имеется поле отображающее​на ленте. Активируется​Результат функций СТАНДОТКЛОНП и СРЗНАЧ.
  2. ​ текущие показатели с​ ведет финансовую отчетность.​ и сезонности. Проанализируем​ + доля нарастающим​анализировать группы позиций вместо​

​ повторном вызове окна​ количества показателей.​ кнопку «Анализ». Появится​Можно также рассчитать сколько​ – анализ стандартными​ не 70% или​ веса, накопленной доли​ и уже к​«Ссылки и массивы»​95%​ формат данных. По​

​ список, в котором​ предшествующими и с​

​ На реализацию влияет​ продажи за 12​ итогом для предыдущей​ огромного списка;​ надстройки поля​

ИНСТРУМЕНТАРИЙ EXCEL ДЛЯ СОЗДАНИЯ АНАЛИТИЧЕСКИХ ОТЧЕТОВ

В табличном редакторе Excel предусмотрен широкий выбор инструментов, с помощью которых можно создать аналитические отчеты на основе данных о реализации продукции. Для успешной работы с этими инструментами от пользователя требуется определенный уровень подготовки. Представим перечень инструментария для создания аналитических отчетов:

  • продвинутый уровень — макросы, Power BI;
  • хороший уровень — OLAP-кубы, Power Query/Pivot;
  • средний уровень — сводные таблицы, формулы.

Рассмотрим особенности применения каждого из указанных инструментов, а также знания и навыки пользователя, которые нужны для их качественного применения.

Макросы

Работа с макросами основана на применении языка программирования VBA, который можно использовать для расширения возможностей MS Excel и других приложений MS Office. С помощью прописанных в макросе команд можно:

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

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

Очевидно, что работать с макросами может незначительная часть сотрудников, которые создают отчетность в Excel.

Power BI

Power BI по своей сути является отдельным программным продуктом, в который можно загрузить файлы Excel и произвести дальнейшую обработку с целью анализа и визуализации данных.

Power BI включает в себя весь функционал надстроек Excel (Power Query и Power Pivot плюс улучшенные механизмы визуализации из Power View и Power Map). Преимущества данного инструмента: с отчетами может работать сразу несколько пользователей плюс широкий диапазон визуализации показателей отчетов.

Идет тренд к интеграции Excel c Power BI. Например, в Excel 2019 появилась возможность напрямую загружать данные в функционал Power BI. Для этого в меню выбираем:

Файл > Опубликовать > Опубликовать в Power BI.

Передав файл, нажимаем кнопку «Перейти к Power BI», чтобы просмотреть загруженные данные.

Главные сложности использования Power BI: загруженные таблицы Excel нужно дополнительно обрабатывать для корректного включения их данных в отчеты, а формулы для создания отчетов в этой программе отличаются от формул Excel.

Power BI постоянно развивается, однако на сегодняшний момент использовать его для формирования аналитических отчетов достаточно трудоемко.

OLAP-кубы

OLAP (online analytical processing) — аналитическая технология обработки данных в реальном времени, при которой данные из учетной базы выгружаются в файлы Excel, а затем обрабатываются с помощью другого инструмента Excel (сводных таблиц).

Для начала работы нужно создать подключение файла Excel к данным OLAP-куба (Данные → Получение внешних данных), а затем из открывшегося окна перетащить курсором в табличную часть Excel показатели, которые требуются.

В результате будет получена сводная таблица с отчетными данными. Главное ее преимущество — возможность автоматической актуализации данных при каждом подключении к OLAP-кубу.

Power Query/Pivot

Данные инструменты являются надстройками Excel, поэтому работа с ними происходит непосредственно из меню табличного редактора.

Power Query появился в версии Excel 2013 как отдельная надстройка, требующая подключения, а с версии 2016 г. весь функционал Power Query уже встроен по умолчанию и находится на вкладке «Данные → Получить и преобразовать».

Power Query обладает значительными возможностями для целей создания отчетов. С помощью этой надстройки можно:

  • загружать данные в Excel из почти 40 различных источников, среди которых базы данных (SQL, Oracle, Access, Teradata), корпоративные ERP-системы (SAP, Microsoft Dynamics, 1C), интернет-сервисы;
  • собирать данные из файлов всех основных типов данных (XLSX, TXT, HTML, XM) — поодиночке и сразу из всех файлов указанной папки;
  • зачищать полученные данные от лишних пробелов, столбцов или строк, повторов, служебной информации в заголовках, непечатаемых символов и т. д;
  • трансформировать таблицы Excel, приводя их в желаемый вид (фильтровать, сортировать, менять порядок столбцов, транспонировать, добавлять итоги, разворачивать кросс-таблицы в плоские и сворачивать обратно);
  • подставлять данные из одной таблицы в другую по совпадению одного или нескольких параметров (полностью заменяет формулу ВПР и ее аналоги).

Главная особенность Power Query: все действия по импорту и трансформации данных запоминаются в виде запроса — последовательности шагов на внутреннем языке программирования Power Query, который лаконично называется «М».

Шаги можно отредактировать, воспроизвести любое количество раз (обновить запрос). Поэтому данный инструмент может служить хорошей альтернативой создания макросов или прописания очень сложных формул при построении отчетов.

Power Pivot — надстройка Excel, предназначенная для разнопланового анализа больших объемов данных. Поэтому результат работы с Power Pivot похож на усложненные сводные таблицы.

Общие принципы работы в Power Pivot:

  • внешние данные загружают в Power Pivot, который поддерживает 15 различных источников: распространенные базы данных (SQL, Oracle, Access), файлы Excel, текстовые файлы, веб-каналы данных. Если Power Query использовать как источник данных, то возможности загрузки увеличиваются многократно;
  • между загруженными таблицами настраиваются связи, то есть создается Модель Данных. Это позволит строить отчеты по любым полям из имеющихся таблиц так, будто это одна таблица;
  • при необходимости в Модель Данных добавляют дополнительные вычисления с помощью вычисляемых столбцов (аналог столбца с формулами в «умной» таблице) и мер (аналог вычисляемого поля в сводной таблице). Нужные вычисления записываются на специальном внутреннем языке Power Pivot, который называется DAX (Data Analysis Expressions);
  • на листе Excel по Модели Данных строят интересующие отчеты в виде сводных таблиц и диаграмм.

Сводные таблицы

Первый интерфейс сводных таблиц (сводных отчетов) был включен в состав Excel в 1993 г. (в версии Excel 5.0). Этот инструмент изначально создавался для построения отчетов на основе многомерных данных. Он имеет достаточно широкие функциональные возможности.

Реализованный в Excel инструмент сводных таблиц позволяет расположить измерения многомерных данных в области рабочего листа. Упрощенно можно представлять себе сводную таблицу как отчет, лежащий сверху диапазона ячеек (хотя есть определенная привязка форматов ячеек к полям сводной таблицы).

Сводная таблица Excel имеет четыре области отображения информации: фильтр, столбцы, строки и данные. Измерения данных именуются полями сводной таблицы. Эти поля имеют собственные свойства и формат отображения.

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

Обновление отчета производится простыми средствами пользовательского интерфейса. Данные автоматически агрегируются по заданным правилам. Не требуется дополнительный или повторный ввод какой-либо информации.

Сводные таблицы Excel являются самым востребованным инструментом при работе с многомерными данными в больших объемах информации. Этот инструмент поддерживает в качестве источника данных как внешние источники данных, так и внутренние диапазоны электронных таблиц.

Для работы со сводными таблицами не нужны знания в области программирования VBA или внутренних языков программирования надстроек Excel.

Формулы Excel

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

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

В редакторе Excel есть встроенный справочник по формулам, что облегчает работу пользователям со средним уровнем владения Excel. Поэтому я предлагаю рассмотреть возможности использования функционала формул при разработке аналитических отчетов из одного источника данных.

Шаблон Excel при аналитике продаж

При учете продукта подобает подбирать удобный для вас стандарт, в который вы будете вносить какие-либо сведения.

Так, шаблон продаж может принять следующий вид.

  • Наименование.
  • Его артикул.
  • Ед.изм.
  • Количество.
  • Стоимость.
  • Цена.

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

Зачем нужны инструменты анализа показателей продаж

В качестве исходной для всесторонней оценки принимается информация о проданных экземплярах продукции. Обязательно смотрят на ее динамику, объемы, ассортиментность. Из этого легко вычленить наиболее популярные модели, отсеять то, что не продается. Поэтому следует вести регулярный мониторинг, это жизненно важно для всех фирм.

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

Кроме того, своевременный анализ поможет решить разные задачи:

  • дает информацию, на основании которой у руководителя получится принимать эффективные решения – как тактические, так и стратегические;
  • определяет прибыльную и убыточную продукцию, способствует принятию правильного выбора– оставить вещь в ассортименте или убрать ее;
  • настраивается и меняется, в случае необходимости, сбытовая политика;
  • снижаются ненужные запасы и увеличивается объем требуемых;
  • сегментируется рынок;
  • выявляется сезонность;
  • планируются графики и ассортиментная матрица;
  • своевременное понимание изменений рынка, учет спадов и подъемов.

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

XYZ анализ ассортимента продукции. Формула расчета

XYZ-анализ ассортимента заключается в группировке товаров по трем группам на основе изменчивости их продаж за прошлые периоды. Для проведения анализа необходимо осуществить следующий последовательный алгоритм.

Читайте также:  Пример построения диаграммы Парето в Excel скачать шаблон

На первом этапе осуществляется расчет коэффициента вариации объема продаж (уровня спроса) по каждой товарной группе. Изменчивость продаж оценивается с помощью коэффициента вариации. Формула расчета коэффициента вариации представляет собой отношение стандартного отклонения к математическому ожиданию объема продаж. Формула имеет следующий вид:

XYZ анализ продаж. Формула расчета

где:

σ – стандартное отклонение объема продаж;

xi – объем продаж в период в i-й период;

n – количество рассматриваемых периодов продаж товаров;

х * – среднеарифметическое значение продаж товаров.

Смысл коэффициента вариации заключается в оценке процентного отклонения объема продаж от среднего значения. Чем больше показатель вариации, тем менее устойчив объем продаж данного вида товара.

На втором этапе производится сортировка ассортимента продукции по значению коэффициента вариации и классификация товаров по трем группам – XYZ на основе значений коэффициента вариации. В таблице ниже показан критерий оценки и экономическое описание для различных групп товаров.

Группа Коэффициент вариации Характеристика класса
Группа «X» 0<V<10% Товары с наиболее устойчивыми объемами продаж
Группа «Y» 10%<V<25% Товары с прогнозируемыми, но изменчивыми объемами продаж
Группа «Z» V>25% Товары, обладающие случайным спросом

Преимущества ABC-анализа

Данный метод хорошо себя зарекомендовал на практике, и имеет следующие преимущества:

  1. Простота использованияи скорость проведения анализа для повышения эффективности продаж. Методику ABC-анализа можно использовать на любом предприятии, так как она не требует больших вычислительных мощностей и баз данных. Все расчеты по номенклатуре товара могут быть произведены в таблице в Excel.
  2. Надежность результатов. Полученные результаты устойчивы во времени и позволяют предприятию сфокусировать свои ресурсы и капитал в развитие наиболее перспективных продуктов. Управление номенклатурой наиболее ценных товаров позволяет создать финансовую устойчивость предприятия.
  3. Оптимизация ресурсов и времени. Использование методики позволяет высвободить дополнительные ресурсы как финансовые, так и временные.
  4. Универсальность анализа. Возможность применения методики ABC-анализа для других сфер деятельности предприятия.

Другие направления использования ABC-анализа в компании

Спектр использования данного метода повышения эффективности в хозяйственных системах крайне широк:

  • Оптимизация номенклатуры товаров.
  • Выделение ключевых поставщиков, подрядчиков, клиентов.
  • Повышение эффективности организации складских запасов.
  • Оптимизация производственного процесса.
  • Бюджетирование и управление маркетинговыми затратами.

Понимание прогноза продаж

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

Прогнозирование заключается в управлении данными, насколько неправильным является ваш прогноз, а не насколько он верный. Расстояние между фактическими результатами и ожидаемыми результатами является риском для вашего бизнеса, и этот риск может быть значительным, даже разрушительным.

Если вы ожидаете высоких результатов, но не дотягиваете до них, вы застрянете с дополнительными запасами на руках, и это будет стоить вашей компании больших денег, которые могли бы быть использованы для лучших проектов. С другой стороны, представьте, что ваши реальные результаты намного выше, чем ваш прогноз. Теперь у вас может возникнуть ситуация, когда у вас закончится продукция. У вас возникают задержки, и вы начинаете терять клиентов.

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

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

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

Прогнозирование продаж является одним из наиболее важных процессов в компании, потому что с ним связано множество отделов бизнеса. Давайте посмотрим, почему прогнозирование продаж так важно:

  1. Во-первых, влияние прогноза продаж на финансовое планирование бизнеса. Например, прогнозы влияют на цены акций и возможность привлечения новых инвестиций .
  2. Во-вторых, прогнозирование продаж влияет на то, как компания создает и управляет своим отделом продаж . Прогнозы продаж будут использоваться для определения территории продаж, установления квот для каждого торгового представителя и для измерения производительности.
  3. В-третьих, другие отделы компании полагаются на точные прогнозы продаж. Например, операционный отдел будет принимать важные решения на основе прогнозов продаж. Если вы производитель, вы решаете, сколько продукции производить каждый день, основываясь на тех же прогнозах.
  4. В-четвертых, сфера услуг, такие как розничная торговля, банковское обслуживание и общественное питание, решают, сколько сотрудников им нужно каждый день для обслуживания прогнозируемого объема клиентов.
  5. В-пятых, прогнозы продаж могут повлиять даже на управление людьми. Они влияют на то, как и когда вы нанимаете новых людей, и на любое увеличение или уменьшение заработной платы.
  6. В-шестых, если компания хочет развиваться за счет приобретения другой компании, ей потребуется точный прогноз продаж целевой компании, чтобы решить, сколько за нее заплатить.

Теперь вам должно быть очевидно, что руководители уделяют так много внимания прогнозированию продаж из-за его далекоидущих последствий. Прогнозирование продаж – это систематический процесс, включающий различные бизнес-функции. Вот почему первым шагом в этом процессе является создание правильной команды .

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

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

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

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

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

Теперь рассмотрим основные шаги прогнозирования:

  1. Сначала проанализируйте рынок. В какой вы категории? Насколько велик рынок, какие тенденции, а также как динамика рынка влияют на будущие результаты?
  2. Соберите данные. Вам нужно собирать только те данные, которые имеют отношение к прогнозу. Вы также должны учитывать данные, которые хотели бы иметь, но у вас их нет. В таком случае вы делаете предположения.
  3. Определитесь с техникой, которую вы будете использовать. Качественная, количественная или комбинация двух?
  4. Проверьте ваш прогноз. Проведите свою модель прогнозирования через прошлые циклы продаж и посмотрите, как она выполняется. Есть ли какие-то корректировки, которые необходимо внести для точной настройки прогноза?

Этапы прогнозирования продаж

Хорошие прогнозы не являются просто предположением. Они являются результатом дисциплинированного и многофункционального подхода.

Сравнительный анализ продаж по дням недели в Excel

Есть данные за месяц с 1 по 30 июня с продажам за каждый день. При чём каждая продажа отображается отдельно. Для отчёта нужно суммировать продажи по дням недели (ПН, ВТ и т.д.) и по времени: 8-9 ч., 9-10, 10-11, 11-12, 12-13, 13-14, 14-15, 15-16, 16-17, 17-18, 18-19. С помощью какой формулы это можно сделать? Пробовал с помощью СУММЕСЛИ, СУММЕСЛИМН, СУММПРОИЗВ, но выдавало только первые данные продаж за 1 число или 0.
[moder]Приложите файл с примером в соответствии с Правилами форума[/moder]

Есть данные за месяц с 1 по 30 июня с продажам за каждый день. При чём каждая продажа отображается отдельно. Для отчёта нужно суммировать продажи по дням недели (ПН, ВТ и т.д.) и по времени: 8-9 ч., 9-10, 10-11, 11-12, 12-13, 13-14, 14-15, 15-16, 16-17, 17-18, 18-19. С помощью какой формулы это можно сделать? Пробовал с помощью СУММЕСЛИ, СУММЕСЛИМН, СУММПРОИЗВ, но выдавало только первые данные продаж за 1 число или 0.
[moder]Приложите файл с примером в соответствии с Правилами форума[/moder] nechehov

Сообщение Добрый день!

Есть данные за месяц с 1 по 30 июня с продажам за каждый день. При чём каждая продажа отображается отдельно. Для отчёта нужно суммировать продажи по дням недели (ПН, ВТ и т.д.) и по времени: 8-9 ч., 9-10, 10-11, 11-12, 12-13, 13-14, 14-15, 15-16, 16-17, 17-18, 18-19. С помощью какой формулы это можно сделать? Пробовал с помощью СУММЕСЛИ, СУММЕСЛИМН, СУММПРОИЗВ, но выдавало только первые данные продаж за 1 число или 0.
[moder]Приложите файл с примером в соответствии с Правилами форума[/moder] Автор – nechehov
Дата добавления – 02.07.2015 в 08:44

Сумма по дням недели.
Сделайте таблицу по часам – накидаем варианты.

П.С. После 1000 строки данные удалил, чтобы файл поместился.

Сумма по дням недели.
Сделайте таблицу по часам – накидаем варианты.

П.С. После 1000 строки данные удалил, чтобы файл поместился. ArkaIIIa

Сообщение Сумма по дням недели.
Сделайте таблицу по часам – накидаем варианты.

П.С. После 1000 строки данные удалил, чтобы файл поместился. Автор – ArkaIIIa
Дата добавления – 02.07.2015 в 09:24

Обработка позволяет распечатать пакет отгрузочных документов для конфигураций УТ и БП, должна выдерживать обновления конфигураций. Что она позволяет распечатать 2 торг12, 2 счет фактуры , 2 ттн за два нажатия кнопки. + Встроен универсальный отбор по документам реализации товаров и услуг (н-р можно распечатать все документы за опред период по опред. контрагенту) + Возможность отсылки сразу на принтер и распределения по копиям + все общие модули вынесены в модуль обработки) + Печатает пакетно также и внешние печатные формы для документов реализация товаров и услуг и счетов фактур + при печати использует типовые модули печати документов в конфигурации (как будто вы сами отправляете на печать из формы документов) проверялась УТ 10.3.3.3 и БП 1.6.10.6

02.09.2009 26478 883 y22-k 33

Ссылка на основную публикацию
Adblock
detector