Анализ безубыточности - это инструмент для оценки возможностей получения прибыли от бизнес-модели и различных ценовых стратегий. В Excel вы можете найти фиксированные затраты, переменные затраты и варианты ценообразования, чтобы определить точку безубыточности продукта. Точка безубыточности - это количество единиц, которые необходимо продать по установленной цене для обеспечения безубыточности (покрытия производственных затрат).
Шаг
Часть 1 из 5: Создание таблицы переменных затрат
Шаг 1. Откройте Excel и создайте книгу
Вам понадобятся рабочие листы в книге, чтобы отслеживать все ваши расходы.
Шаг 2. Нажмите кнопку «+» рядом с «Sheet1» внизу экрана
Это откроет новый рабочий документ.
Шаг 3. Назовите новый рабочий лист «Переменная стоимость»
Этот рабочий лист будет содержать все таблицы, в которых отслеживаются все переменные затраты, такие как стоимость доставки, комиссионные и т. Д.
Шаг 4. Создайте заголовок для нового рабочего листа
Чтобы создать стандартную таблицу переменных затрат, введите «Описание» в ячейку A1 и «Сумму» в ячейку B1.
Шаг 5. Введите название переменной стоимости вашего бизнеса в столбец A
Под заголовком «Описание» введите тип обрабатываемых переменных затрат на продукт.
Шаг 6. Оставьте пока столбец B («Сумма») пустым
Позже этот столбец будет заполнен затратами, которые действительно произошли во время процесса.
Шаг 7. Создайте таблицу из введенных данных
Превратите данные в таблицы, чтобы их можно было легко вводить в формулы:
- Выделите все данные, включая строку заголовка и пустые ячейки, щелкнув и перетащив курсор мыши по всем ячейкам.
- Нажмите кнопку «Форматировать как таблицу». Вы найдете его под ярлыком «Домой». Если вы используете Excel для Mac, щелкните ярлык «Таблицы», нажмите кнопку «Создать» и выберите «Вставить таблицу с заголовками».
- Установите флажок «В моей таблице есть заголовки». Таким образом, метка первой строки будет установлена как метка заголовка.
- Щелкните поле «Имя таблицы» в правом верхнем углу и измените имя на «Переменная стоимость».
Часть 2 из 5: Создание таблицы фиксированных затрат
Шаг 1. Нажмите кнопку «+» рядом с «Переменной стоимостью» внизу экрана
Это откроет новый рабочий документ.
Шаг 2. Переименуйте новый рабочий лист в «Фиксированные затраты»
Этот рабочий лист будет содержать все фиксированные затраты на продукт, такие как аренда, страхование и другие расходы, которые не меняются.
Шаг 3. Создайте заголовок
Как и в случае с листом переменных затрат, создайте метку «Описание» в ячейке A1 и метку «Сумма» в ячейке B1.
Шаг 4. Введите названия постоянных затрат вашего бизнеса в столбец A
Заполните первый столбец описанием постоянных затрат, например «Аренда».
Шаг 5. Оставьте пока столбец B («Сумма») пустым
Вы заполните эти расходы после заполнения всех документов.
Шаг 6. Создайте таблицу из введенных данных
Выберите все, что вы создали на этом листе, включая заголовок:
- Нажмите кнопку «Форматировать как таблицу» на ярлыке «Главная».
- Установите флажок «У моей таблицы есть заголовки», чтобы изменить строку 1 на заголовок таблицы.
- Щелкните поле «Имя таблицы» и введите имя таблицы «Фиксированные затраты».
Часть 3 из 5: Создание рабочего листа безубыточности
Шаг 1. Переименуйте Sheet1 в «BEP» и выберите его
Этот рабочий лист будет содержать ваш основной график BEP (точки безубыточности). Вам не нужно менять имя на «BEP», но это облегчит вашу работу, если вы это сделаете.
Шаг 2. Установите макет для рабочего листа безубыточности
Для этого примера создайте рабочий лист, используя следующий макет:
- A1: Продажи - это метка раздела «Продажи» на листе.
- B2: Цена за единицу - это цена, взимаемая за каждый проданный товар.
- B3: Продано единиц - это количество единиц, проданных по установленной цене за определенный промежуток времени.
- A4: Затраты - это метка для раздела «Затраты» на листе.
- B5: Переменные затраты - это затраты на продукт, которые вы можете контролировать (доставка, комиссия и т. Д.)
- B6: Фиксированные затраты - это затраты на продукт, которые вы не можете контролировать (аренда помещения, страхование и т. Д.)
- A7: Доход - это сумма денег, полученная от продажи продукта до вычета затрат.
- B8: Маржа за единицу - это сумма денег, заработанная за единицу после вычета затрат.
- B9: Маржа валовой прибыли - это общая сумма денег, полученная от продажи единиц продукта после вычета затрат.
- A10: BEP - это метка для раздела рабочего листа, посвященного точке безубыточности.
- B11: Единицы - это количество единиц, которые необходимо продать, чтобы покрыть производственные затраты.
Шаг 3. Измените числовой формат для ячеек вывода и ввода
Вам нужно изменить числовой формат для определенных ячеек, чтобы данные отображались правильно:
- Выделите C2, C5, C6, C8 и C9. Щелкните раскрывающееся меню в разделе «Число» на ярлыке «Домой» и выберите «Валюта».
- Выделите C3 и C11. Щелкните раскрывающееся меню и выберите «Другие числовые форматы». Выберите «Число» и установите «Десятичные разряды» на «0».
Шаг 4. Создайте диапазон для использования в формулах
Выберите и создайте следующие диапазоны, чтобы формула работала. Таким образом, можно создавать переменные для ввода в формулы, чтобы вы могли легко отслеживать и обновлять их значения.
- Выберите B2: C3, затем щелкните метку «Формулы». Нажмите «Создать из выделенного» и нажмите «ОК».
- Выберите B5: C6, затем щелкните метку «Формулы». Нажмите «Создать из выделенного» и нажмите «ОК».
- Выберите B8: C9, затем щелкните метку «Формулы». Нажмите «Создать из выделенного» и нажмите «ОК».
- Выберите B11: C11, затем щелкните метку «Формулы». Нажмите «Создать из выделенного» и нажмите «ОК».
Часть 4 из 5: Ввод формул
Шаг 1. Введите формулу переменных затрат
Эта формула рассчитает общую переменную стоимость проданных товаров. Щелкните C5 и введите следующую формулу:
= СУММ (переменная стоимость) * Продано единиц
Шаг 2. Подставьте фиксированные затраты в формулу
Excel рассчитает общую фиксированную стоимость вашего продукта. Щелкните C6 и введите следующую формулу:
= СУММ (фиксированная стоимость)
Шаг 3. Введите формулу единичной маржи
Эта формула рассчитает итоговую маржу после включения переменных затрат. Щелкните C8 и введите следующую формулу:
= Price_Per_Unit-SUM (переменная стоимости)
Шаг 4. Введите формулу валовой прибыли
Эта формула определяет общую сумму прибыли для всех единиц после вычета переменных затрат. Щелкните C9 и введите следующую формулу:
= Margin_Unit * Unit_Sold
Шаг 5. Введите формулу BEP
Эта формула берет фиксированные затраты и сравнивает их с маржой, чтобы вы знали, сколько единиц вам нужно продать, чтобы выйти на уровень безубыточности. Щелкните C11 и введите следующую формулу:
= ЕСЛИОШИБКА (Фиксированная_Стоимость / маржа_ единицы, 0)
Часть 5 из 5: Определение точки безубыточности
Шаг 1. Введите переменные затраты вашего бизнеса
Вернитесь к таблице переменных затрат и укажите все затраты, связанные с продуктом. Чем точнее будут зарегистрированы затраты, тем точнее будут расчеты.
Каждая стоимость в таблице переменных затрат должна быть указана на проданную единицу
Шаг 2. Введите фиксированные затраты вашего бизнеса
Введите эти затраты в таблицу постоянных затрат. Это затраты на ведение бизнеса, все они устанавливаются через равные промежутки времени (например, ежемесячная плата).
Шаг 3. Введите цену за единицу
В листе BEP введите оценочную цену за единицу. Вы сможете корректировать его по мере выполнения расчетов.
Шаг 4. Введите количество единиц, которые вы хотите продать
Это желаемое количество продаж за тот же период времени, что и фиксированные затраты. Например, если ваши постоянные расходы включают ежемесячную арендную плату и страховку, Продано единиц - это количество единиц, проданных за тот же период времени.
Шаг 5. Прочтите вывод «Unit»
В ячейке вывода единиц (C11) будет отображаться количество единиц, которые необходимо будет продать, чтобы выйти на уровень безубыточности. Эта цифра будет меняться в зависимости от цены за единицу, а также от ваших таблиц переменных и фиксированных затрат.
Шаг 6. Внесите корректировки в цены и затраты
Изменение цены за единицу также изменит количество единиц, необходимое для безубыточности. Попробуйте изменить продажную цену единицы и посмотрите, как это повлияет на значение BEP.
