Отчет по выполнению плана продаж товаров в excel

Разработка форм отчетности для руководства в Excel с расчетными формулами

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

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

Например, руководителю финансово-экономической службы компании «Альфа» нужно предоставить руководителю и собственнику компании отчеты о результатах работы компании за I полугодие 2019 г. исходя из типовых форм регламентированной отчетности — баланса и формы 2 к нему.

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

Чтобы преобразовать это огромное количество цифр в нужный нам формат, создадим дополнительно в книге Excel несколько листов:

• «Структура баланса» — для расчета динамики структуры агрегированных показателей имущества компании и источников его финансирования;
• «Оборотный капитал» — для определения размера чистого оборотного капитала компании;
• «Безубыточность» — для расчета точки безубыточности и запаса финансовой прочности на начало и конец отчетного периода;
• «ФинПоказатели» — для расчета комплекса финансовых показателей и коэффициентов, характеризующих состояние компании;
• «Сводные показатели» — для визуализации обобщенной динамики финансового положения компании по итогам рассматриваемого периода.

Следующий этап построения управленческой отчетности — заполнение созданных на этих листах отчетных форм данными из представленных выше финансовых отчетов — баланса и формы 2 к нему.

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.

С помощью следующего алгоритма мы детально рассмотрим пример, как построить сводную таблицу в Excel. На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица».

Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.

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

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

Во время работы вы можете столкнуться с подобным сообщением «недопустимое имя сводной таблицы Excel». Это означает, что первая строка диапазона, откуда пытаются извлечь информацию, осталась с незаполненными ячейками. Чтобы решить эту проблему, вы должны заполнить пустоты колонки.

Анализ данных с помощью 3D-карт

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

Как работать с 3D-картами в Excel

Последовательность действий, которую вам необходимо выполнить, чтобы работать с 3Д-картами, следующая:

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

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

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

Объединяем несколько условий.

Для описания условия в функции ЕСЛИ Excel позволяет использовать более сложные конструкции. Также можно использовать несколько условий. В этом случае мы также воспользуемся тем, что функции могут быть «вложены» друг в друга.

Чтобы объединить несколько условий в одно, мы используем логические функции ИЛИ и И. Рассмотрим простые примеры.

Пример 1

Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из нескольких перечисленных в ней условий истинно.

= SE (OR (C2 = «Восток», C2 = «Юг»), «Экспорт», «Местный»)

Мы вставляем функцию ИЛИ как условие в функцию ЕСЛИ. В нашем случае, если регион покупателя — Восток или Юг, отгрузка считается экспортной.

Пример 2.

Давайте использовать несколько более сложных условий в функции ЕСЛИ.

Если регион продаж — Запад или Юг и количество больше 100, предоставляется скидка 10%.

= SE (И (ИЛИ (C2 = Запад, C2 = Юг), E2> 100), F2 * 0,1,0)

Функция И возвращает ИСТИНА, если все перечисленные в ней условия выполнены. Внутри функции И мы устанавливаем два условия:

1. Регион — Запад или Юг
2. Число больше 100.

Первый из них реализован так же, как и первый пример: ИЛИ (C2 = «Запад», C2 = «Юг»)

Во-вторых, здесь все очень просто: E2> 100

В строках 2, 3 и 5 выполняются оба условия. Эти покупатели получат скидку.

В строке 4 ни один из них не был выполнен. А в строке 6,7,8 выполняется только первая, но ее слишком мало. Следовательно, скидка будет нулевая.

Пример 3.

Конечно, эти несколько условий могут быть более сложными. Ведь логические функции можно «вкладывать» друг в друга.

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

Все наши ранее написанные условия, в свою очередь, становятся первым аргументом в новой функции И:

1. Регион — Запад или Юг и число больше 100 (рассмотрено в примере 2)
2. В названии шоколада присутствует слово «черный».

В результате мы получаем формулу ЕСЛИ с несколькими условиями:

= ЕСЛИ (И (ЕЧИСЛО (НАЙТИ («Черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)

Функция НАЙТИ ищет точное совпадение. Если для нас не важен регистр символов в тексте, то вместо НАЙТИ можно использовать аналогичную функцию ТОЧНЫЙ.

= ЕСЛИ (И (ЕЧИСЛО (МАТЧ («черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)

В результате количество вложенных условий в Excel может быть очень большим

Важно лишь строго соблюдать логическую последовательность их выполнения

Обновление данных в сводной таблице Excel

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

Обновить данные в нашей сводной таблице Excel можно двумя способами:

• кликнув правой кнопкой мыши на любой ячейке и выбрав в контекстном меню пункт «Обновить»;
• используя вкладку «Данные» и кнопку «Обновить всё».

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

Области сводной таблицы в Excel

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

Ниже вы узнаете подробней об областях:

• Кэш
• Область “Значения”
• Область “Строки”
• Область “Столбцы”
• Область “Фильтры”

Что такое кэш сводной таблицы

При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.

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

Область “Значения”

Область “Значения” включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области “Значения”.

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

Область”Столбцы”

Заголовки вверху значений таблицы называются “Столбцы”.

На примере ниже красным выделены поля “Столбцы”, в нашем случае это значения месяцев.

Область “Фильтры”

Область “Фильтры” используется опционально и позволяет задать уровень детализации данных. Например, мы можем в качестве фильтра указать данные “Тип клиента” – “Продуктовый магазин” и Excel отобразит данные в таблице касающиеся только продуктовых магазинов.

Функции для сводных таблиц в Экселе — Виды

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

• Получение данных по запросу пользователя. Редактор документа может сослаться на отдельную ячейку таблички и получить из нее необходимые данные;
• Фильтрация исходной информации. Такая возможность позволяет быстро просмотреть искомую информацию для нескольких сущностей таблицы одновременно;
• Сводка по нескольким диапазонам сразу. Пользователь может выбрать как уже имеющиеся типы сводок, так и создать свою личную с помощь макросов;
• Группировка данных с определенным шагом. То есть редактор документа может объединять в отдельные группы информацию, которая соответствует определенному временному промежутку (данные за месяц, год и прочее);
• Создание полноценных отчетов с возможностью их распечатки. Данная функция позволяет сэкономить время на составление текстового отчета после создания таблицы.

Пример отчета по сводной таблице

Видеоролики:

Создание сводной таблицы в Excel

Как создать аналитический отчет сводной таблицы в Microsoft Excel. Понятно, пошагово и с подробностями.

Анализ данных с помощью сводных таблиц в Excel

Что такое сводные таблицы и как они работают. Быстрый анализ с помощью сводных таблиц. Как подготовить данные для сводных таблиц. Построение сводных таблиц и их настройка

Как создать таблицу в Word

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

Для представленного выше списка из 2-х учеников потребуется создать в редакторе MS Word таблицу из 3-х строк (учеников у нас в списке пока двое, а самую первую строку таблицы мы используем для заголовков столбцов), и из 5-и столбцов для ввода в них следующих данных:

• фамилия ученика
• имя ученика
• отчество ученика
• дата рождения ученика
• телефон ученика.

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

• Затем поставим курсор на вторую строку,
• откроем вкладку «Вставка» (1 на рис. 1),
• кликнем по иконке «Таблица» (2 на рис. 1) и
• выберем строку меню «Вставить таблицу» (3 на рис. 1).

Рис. 1 (кликните по рисунку для увеличения). Создание новой таблицы в редакторе Word.

В открывшемся служебном окне «Вставка таблицы»

• указываем «Число столбцов» 5 (1 на рис. 2) и
• «Число строк» 3 (2 на рис. 2) ,
• далее жмем на кнопку «ОК» (3 на рис. 2).

Рис. 2. Описание вставляемой таблицы (указание числа столбцов и строк) в редакторе Ворд.

Таблица успешно вставляется и имеет вид, как показано на рис. 3.

Рис. 3 (кликните по рисунку для увеличения). Вставленная пока еще пустая таблица из 3-х строк и 5-и столбцов в редакторе Microsoft Word.

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

В оставшиеся 2 строки таблицы заносим данные ученика по фамилии Иванов и ученицы по фамилии Петрова (рис. 4).

Рис. 4 (кликните по рисунку для увеличения). Заполненная таблица, сделанная в редакторе Ворд.

Теперь данные размещены в таблице. Это не только красивее, чем просто список, но еще и удобно. В первой колонке таблицы теперь стоят только фамилии учеников, во второй – имена, в третьей – отчества и так далее.

Как выделить и перенести часть таблицы в Ворде

Данные в таблице у нас теперь, как говорят, структурированы. А значит, их легко найти, выделить, куда-то перенести отдельно.

Скажем, нам нужны только фамилии учеников. Выделяем данные из первого столбца таблицы. Для этого помещаем курсор мышки в клетку, которая будет второй по вертикали в первой колонке таблицы – на фамилию «Иванов». Нажимаем на левую кнопку мышки и, не отпуская ее, перемещаем курсор мышки в третью строку таблицы. То есть как бы «проводим» мышкой по клеткам таблицы, где занесены фамилии учеников. Результат такого выделения показан на рис. 5.

Рис. 5 (кликните по рисунку для увеличения). Выделение одной колонки таблицы в редакторе MS Word.

Затем копируем эти данные в буфер обмена (клик мышкой на кнопку «Копировать» — 1 на рис. 6).

Рис. 6 (кликните по рисунку для увеличения). Копирование выделенной части таблицы в буфер обмена в редакторе Microsoft Word.

После копирования части таблицы в буфер обмена давайте установим курсор под таблицей не прямо на следующей строке, а через одну строку (выделено синим цветом – 1 на рис. 7), и кликнем по кнопке «Вставить» (2 на рис. 7).

Рис. 7 (кликните по рисунку для увеличения). Подготовка к вставке части таблицы в другое место текста в редакторе Word.

Часть таблицы, а именно – фамилии учеников – вставится ниже под основной таблицей (рис. 8).

Рис. 8 (кликните по рисунку для увеличения). Часть таблицы (фамилии учеников) воспроизведена в другом месте текста в редакторе Ворд.

Отчет №1 Суммарные продажи Товаров

Найдем суммарные продажи каждого Товара. Задача решается достаточно просто с помощью функции СУММЕСЛИ() , однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.

Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только уникальные значения. Это можно сделать несколькими способами: формулами (см. статью Отбор уникальных значений ), через меню Данные/ Работа с данными/ Удалить дубликаты или с помощью Расширенного фильтра . Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:

• Перейдите на лист с исходной таблицей;
• Вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
• Заполните поля как показано на рисунке ниже: переключатель установите в позицию Скопировать результат в другое место ; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок Только уникальные записи .

• Скопируйте полученный список на лист, в котором будет размещен отчет;
• Отсортируйте перечень товаров ( Данные/ Сортировка и фильтр/ Сортировка от А до Я ).

Должен получиться следующий список.

В ячейке B6 введем нижеследующую формулу, затем скопируем ее Маркером заполнения вниз до конца списка:

Для того, чтобы понять сруктурированные ссылки на поля в таблицах в формате EXCEL 2007 можно почитать Справку EXCEL (клавиша F1 ) в разделе Основные сведения о листах и таблицах Excel > Использование таблиц Excel .

Также можно легко подсчитать количество партий каждого Товара:

Для чего в Excel нужны сводные таблицы

В оригинале этот термин звучит как Pivot Table, а перевод фразы несколько туманен: «таблица, которую можно менять, крутить, демонстрировать в разных проекциях». Что это означает? Интерпретация табличных данных – задача непростая. Обычно это делается с помощью формул, получая итоговые результаты в конце таблицы.

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

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

Допустим, вы менеджер компании и отвечаете за ведение документации по продажам. Все сделки заносятся в таблицу с указанием всех деталей (продавец, покупатель, количество, сумма, дата продажи, адрес покупателя и т. д.). Руководитель просит отчёт о продажах по регионам.

Как мы поступаем? Создаём макет новой таблицы, в шапке которой указываем весь ассортимент реализуемых товаров, а в строках – наименования регионов. Для этого копируем из исходной таблицы столбец с товарами, удалив дубликаты. Используя режим специальной вставки, транспонируем этот столбец в шапку сводной страницы. Аналогичным образом поступаем с регионами, но, поскольку они располагаются в сводной таблице по вертикали, просто копируем без транспонирования.

В каждую ячейку суммируем выручку, используя функцию СУММЕСЛИМН. Наконец, внизу добавляем итоговые результаты и отправляем отчёт руководителю. Руководитель просит регионы расположить вверху, а товары – по строкам. Вы делаете новый отчёт за 5 минут, и получаете новое задание: предоставить данные по прибыли, а не выручке, то есть с учётом затрат. Что ж, немного изменяем формулу, и опять результат готов за считанные минуты. Наконец, поступает просьба подготовить такие отчёты по каждому продавцу, чтобы оценить их эффективность. И это для сводной таблицы не проблема, если знать, где располагаются нужные исходные данные!

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

Процесс анализа продаж

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

Анализ выполнения плана продаж

Если в компании ведется планирование и установлен план продаж, то первым шагом рекомендуем оценить выполнение плана продаж по товарным группам и проанализировать качество роста продаж (динамику отгрузок по отношению к аналогичному периоду прошлого года).

Рис.3 Пример анализа выполнения плана продаж по товарным группам

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

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

Анализ динамики продаж по направлениям

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

Рис.4 Пример анализа продаж по направлениям

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

Анализ структуры продаж

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

Рис.5 Пример анализа структуры продаж ассортимента компании

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

Рис.6 Пример анализа себестоимости и рентабельности продаж

АВС анализ

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

Рис.7 Пример АВС анализа ассортимента

АВС анализ проводится в разрезе продаж и прибыли 1 раз в квартал.

Контроль остатков

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

Рис.8 Пример анализа остатков продукции

От автофильтра к сводному отчету

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

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

Теперь посмотрим какие возможности дает интерфейс сводных таблиц. На листе СводАнализ
построено несколько отчетов на основе диапазона ячеек с данными листа ПродажиАнализ
.

Первая таблица анализа построена через интерфейс Excel 2007 Лента \ Вставка \ Сводная таблица
(в Excel 2000-2003 меню Данные \ Сводная таблица
).

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

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