Найти сумму продаж для указанных магазинов в Excel

Обновлено: 20.11.2024

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

​Сомер Дж. Андерсон — дипломированный бухгалтер, доктор бухгалтерского учета и профессор бухгалтерского учета и финансов, проработавший в сфере бухгалтерского учета и финансов более 20 лет. Ее опыт охватывает широкий спектр областей бухгалтерского учета, корпоративных финансов, налогов, кредитования и личных финансов.

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

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

Ключевые выводы

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

Понимание анализа безубыточности

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

Типы расходов

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

Стратегии ценообразования

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

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

Маржинальный вклад

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

Например, если каждый продукт продается по 200 долларов США, а общие переменные затраты составляют 80 долларов США за единицу, маржинальная прибыль составит 120 долларов США (200–80 долларов США). 120 долларов США — это доход, полученный после вычета переменных затрат, и его должно быть достаточно для покрытия постоянных расходов компании.

Формула анализа безубыточности

Точка безубыточности возникает, когда:

Общие постоянные затраты + общие переменные затраты = доход

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

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

Особые соображения

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

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

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

Типы анализа безубыточности

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

Безубыточный общий объем продаж

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

Приведенная ниже формула помогает рассчитать общий объем продаж, но измеряется в долларах ($), а не в единицах:

  • Безубыточные продажи = общие фиксированные затраты / (доходная маржа)
  • Маржа вклада = 1 – (переменные затраты/доходы)

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

Продано безубыточных единиц

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

В зависимости от имеющихся у вас данных вам может потребоваться преобразовать общую стоимость в долларах в стоимость на единицу:

  • Безубыточные единицы = общие фиксированные затраты / (цена за единицу — переменные затраты за единицу)

Чтобы рассчитать анализ безубыточности, мы делим общие фиксированные затраты на маржинальную прибыль для каждой проданной единицы. Предположим, что в предыдущем примере общие фиксированные затраты составляют 10 000 долларов США.

Мы уже знаем, что каждый продукт продается по 200 долларов США, а общие переменные затраты составляют 80 долларов США за единицу, в результате чего маржинальная прибыль составляет 120 долларов США (200–80 долларов США).

Используя приведенную выше формулу точки безубыточности, мы подставляем числа (10 000 долларов США постоянных затрат / 120 долларов США маржинальной прибыли).

Точка безубыточности для продаж составляет 83,33 или 84 единицы, которые необходимо продать до того, как компания покроет их постоянные затраты. С этого момента или после 85 единиц компания будет оплачивать свои постоянные затраты и фиксировать прибыль на единицу.

Безубыточная цена

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

  • Переменные затраты в процентах на единицу = общие переменные затраты / (общие переменные + общие постоянные затраты)
  • Общие фиксированные затраты на единицу = общие постоянные затраты / общее количество единиц.
  • Цена безубыточности = 1 / ((1 – общие переменные затраты в процентах на единицу)*(общие постоянные затраты на единицу))

Анализ безубыточности в Excel

Теперь, когда мы знаем, из чего состоит анализ безубыточности, мы можем приступить к его моделированию в Excel. Есть несколько способов сделать это. Наиболее полезными являются создание калькулятора безубыточности или использование Goal Seek, встроенного в Excel инструмента.

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

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

Или по цене:

Наконец, мы можем легко построить матрицу чувствительности, чтобы изучить, как взаимодействуют эти факторы. Учитывая различные структуры затрат, мы можем увидеть диапазон цен безубыточности от 28 до 133 долларов США.

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

Что вы хотите сделать?

Поиск значений в списке по вертикали с помощью точного совпадения

Для выполнения этой задачи можно использовать функцию ВПР или комбинацию функций ИНДЕКС и ПОИСКПОЗ.

Примеры ВПР

Дополнительную информацию см. в разделе Функция ВПР.

Примеры ИНДЕКС и ПОИСКПОЗ

На простом английском языке это означает:

=INDEX(Мне нужно возвращаемое значение из C2:C10, которое будет MATCH(Kale, которое находится где-то в массиве B2:B10, где возвращаемое значение является первым значением, соответствующим Kale))

Формула ищет первое значение в ячейках C2:C10, соответствующее капусте (в ячейке B7), и возвращает значение в ячейке C7 (100), которое является первым значением, соответствующим кале.

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

Для этого используйте функцию ВПР.

Важно! Убедитесь, что значения в первой строке отсортированы по возрастанию.

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

Дополнительную информацию см. в разделе Функция ВПР.

Поиск значений по вертикали в списке неизвестного размера с использованием точного совпадения

Для выполнения этой задачи используйте функции СМЕЩЕНИЕ и ПОИСКПОЗ.

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

C1 — верхние левые ячейки диапазона (также называемые начальной ячейкой).

MATCH("Oranges",C2:C7,0) ищет апельсины в диапазоне C2:C7. Вы не должны включать начальную ячейку в диапазон.

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

Поиск значений в списке по горизонтали с помощью точного совпадения

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

HLOOKUP ищет столбец Продажи и возвращает значение из строки 5 в указанном диапазоне.

Дополнительную информацию см. в разделе Функция ГПР.

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

Для выполнения этой задачи используйте функцию ГПР.

Важно! Убедитесь, что значения в первой строке отсортированы по возрастанию.

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11 000 и, следовательно, ищет следующее наибольшее значение, меньшее 1100, и возвращает 10 543.

Дополнительную информацию см. в разделе Функция ГПР.

Создание формулы поиска с помощью мастера поиска (только в Excel 2007)

Примечание. Надстройка «Мастер поиска» больше не поддерживается в Excel 2010. Эта функция была заменена мастером функций и доступными функциями «Поиск и ссылки» (ссылка).

В Excel 2007 мастер поиска создает формулу поиска на основе данных рабочего листа с метками строк и столбцов.Мастер поиска помогает найти другие значения в строке, когда известно значение в одном столбце, и наоборот. Мастер поиска использует ИНДЕКС и ПОИСКПОЗ в формулах, которые он создает.

Нажмите ячейку в диапазоне.

На вкладке "Формулы" в группе "Решения" нажмите "Поиск".

Если команда «Поиск» недоступна, необходимо загрузить программу-надстройку «Мастер поиска».

Как загрузить программу надстройки Lookup Wizard

Нажмите кнопку Microsoft Office , выберите «Параметры Excel» и выберите категорию «Надстройки».

В поле "Управление" нажмите "Надстройки Excel", а затем нажмите "Перейти".

В диалоговом окне «Доступные надстройки» установите флажок рядом с «Мастер поиска» и нажмите «ОК».

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

Один из способов поддерживать порядок в товарных запасах и экономить время при поиске, добавлении и редактировании данных — использовать формулы, позволяющие быстро находить и объединять данные, а также извлекать выводы из анализа.

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

И если вам нужен еще один секрет, вот номер 21: система PIM, в которую вы можете импортировать все свои документы Excel для автоматизации управления продуктами.

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

Одна из самых простых формул для управления продуктами и данными в Excel.

Формула СУММ позволяет суммировать значения в строке или столбце, просто выбрав последнюю ячейку в нужной строке или столбце и нажав клавиши ALT+.

СУММЕСЛИ

Вышеупомянутую базовую формулу можно немного усложнить: с помощью функции СУММЕСЛИ вы можете сложить все значения, соответствующие определенным критериям. Например, общая стоимость продуктов для конкретного клиента или общий объем продаж продукта в определенном канале.

СУММПРОИЗВ

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

ПОСТУПЛЕНИЕ

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

ИСХОДЯЩИЕ ЗАПАСЫ

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

УРОВЕНЬ ЗАПАСОВ

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

ПОИСК/ПОИСК

Это другие базовые формулы для просмотра вашего инвентаря в Excel, особенно полезные, если у вас много продуктов или артикулов.

Эта функция позволяет выделить определенные данные (НАЙТИ) или выполнить более широкий поиск (ПРОСМОТР). Например, с помощью НАЙТИ вы можете найти ячейки, содержащие точное слово «носок», а если вы введете «носок» с помощью ПРОСМОТР, он также покажет вам результаты ячеек, содержащих «носок».

ВПР

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

Новая версия XLOOKUP,

ИНДЕКС и ПОИСКПОЗ

Ограничение формулы ВПР заключается в том, что она считывает данные только слева направо.

Если вам нужно найти данные в любом месте электронной таблицы и из разных столбцов, ИНДЕКС и ПОИСКПОЗ могут оказаться более полезными. Например, вы можете использовать его, чтобы узнать годовую чистую прибыль от продукта, используя его кодовую ячейку.

ВЛЕВО/ВПРАВО

Эта функция позволяет извлекать данные из начала и конца ячейки в зависимости от типа содержащихся в ней данных.

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

СРЕДНЕЕСЛИ

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

ОБЪЕДИНИТЬ

=CONCATENATE позволяет объединять данные разных типов (числа, текст, даты) в одной ячейке. Это функция, которая обычно используется в Excel для создания SKU.

Формула =ДЛСТР — это функция Excel, позволяющая узнать количество текстовых символов в ячейке. Этот ярлык полезен для идентификации различных типов кодов продуктов в зависимости от их длины.

СЧЁТ

Пропуск данных — одна из основных проблем при управлении каталогами товаров и запасами. Система PIM (управление информацией о продуктах) позволяет автоматически находить отсутствующие или неверные данные в ассортименте вашей продукции.

Но если вы хотите использовать ручную функцию в Excel, формула =СЧЁТЗ определит все пустые ячейки.

СЧЁТЕСЛИ

Это дополнительная формула к предыдущей функции, которая подсчитывает количество ячеек, соответствующих определенным критериям.

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

ЗНАЧЕНИЕ

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

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

Существует также дополнительная формула, если вы просто хотите получить количество рабочих дней между двумя датами:

MINIF и MAXIF

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

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

Дополнительные функции для управления данными о товарах с помощью Sales Layer

В дополнение к формулам, которые вы можете использовать в своих электронных таблицах Excel, при их импорте в систему PIM Sales Layer вы получите доступ к автоматической форме управления, которая позволяет избежать всех распространенных ошибок, выполняемых вручную, с более чем 50 формулами, которые ускорьте поиск, редактирование и извлечение данных:

  • Какие формулы можно использовать в Sales Layer?
  • Объединение цепочек, полей и функций
  • Фильтровать данные по дате изменения.
  • Форматировать текст
  • Проверьте или замените значения
  • Добавить ярлыки в список
  • Извлечение данных из полей таблицы
  • Вставка или изменение данных в таблице
  • Получить URL-адреса и имена изображений для экспорта
  • Выполнение математических формул
  • Удалить лишние пробелы
  • Изменить валюту
  • Найти ссылки

И многое другое, что вы можете попробовать сами, если запросите пробную версию Sales Layer прямо сейчас.

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

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

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

Формула

См. процентный столбец на изображении. Формула для расчета процента продаж: (продано / количество) * 100 . То есть сначала значение будет разделено, а затем умножено на 100.

Чтобы применить формулу,

• выберите ячейку D2
• вставьте формулу, набрав =(c1 / b2) * 100
• нажмите клавишу ввода

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

Чтобы округлить результат в процентах, вы можете использовать функцию ОКРУГЛ().

Вы можете еще больше упростить приведенную выше формулу. Не умножайте результат на 100. Просто отформатируйте ячейку (ячейки) как Percentage . Смотрите изображение.

Нет необходимости округлять результат и, кроме того, он автоматически добавляет к результату символ %.

👉 Знаете ли вы формулу для умножения двух буквенно-цифровых значений ячеек в Excel? Это просто. См. этот пример.

Читайте также: