На этом шаге мы рассмотрим особенности определения этой точки безубыточности.
Таблица, показанная на рисунке 1, предназначена для получения только одного значения - значения точки безубыточности и не дает полного представления о том, каким образом она получена и из каких элементов состоит.
Рис.1. Таблица классификации издержек для отдельного продукта с числовыми данными
Существует другое, более полное представление точки безубыточности - в виде графика.
Для создания графика в Excel необходимо предварительно создать таблицу с данными, на основании которых будет построен этот график. Для создания графика определения точки безубыточности и для отражения данных, которые оказывают влияние на ее величину, необходимо следующее:
- объем реализации в количественном выражении (ось Х);
- сумма переменных издержек, как зависимость произведения величины удельной переменной составляющей стоимости единицы продукции на объем реализации и является величиной пропорционально возрастающей в зависимости от объема реализации;
- сумма постоянных издержек - величина постоянная;
- суммарные издержки - сумма переменных и постоянных затрат;
- сумма реализации - пропорционально возрастающая величина в зависимости от объема реализации и равна произведению цены реализации единицы продукции на объем реализации.
Таблица с данными для создаваемого графика показана на рисунке 2 и расположена в области Н1:L30 рабочего листа Анализ.
Рис.2. Таблица с данными для построения графика графического определения точки безубыточности
Для создания таблицы возможны три метода ввода в нее данных:
- изменяя содержимое ячейки D11, полученные результаты вычислений из ячеек D11, Е12, Е13, Е17 и Е19 вводить в соответствующие ячейки создаваемой таблицы с клавиатуры;
- использовать для создания формулы;
- воспользоваться инструментом Excel - Таблица подстановки.
Первый метод довольно распространен на практике, но при большом объеме данных займет довольно много времени и мало чем отличается от механических расчетов.
Применение формул при создании таблицы
Метод создания формул для создания таблицы также довольно распространен и создание таблицы занимает относительно мало времени. Основной элемент в формулах - эффективное использование в адресах абсолютной, относительной и смешанной ссылок на ячейки (рисунок 3).
Рис.3. Формулы для создания таблицы определения точки безубыточности
В связи с тем, что объем изготавливаемой продукции колеблется в довольно широком диапазоне: от сотен тысяч единиц (фигурные бутылки - 800 тысяч) до миллионов (4 млн. - стандартные бутылки), а в таблице всего 29 строк, то шаг изменения объема реализации для первого вида продукции, не подойдет для второго. Поэтому в ячейке М1 задается шаг изменения объема реализации для каждой следующей строки таблицы. Кроме того, наличие такого количества формул увеличивает физические размеры файла и уменьшает быстродействие проведения вычислений.
Использование инструмента Excel Таблица подстановки
Третий метод создания таблицы - использование инструмента Excel Таблица подстановки.
Предварительный этап применения таблицы подстановки заключается во вводе в диапазон ячеек Н2:L2 ссылок на ячейки исходной таблицы (рисунок 1) и указания диапазона изменения объема реализации продукции.
Для вызова диалогового окна Таблица подстановки выделите диапазон Н2:L30 и выполните команду Данные | Таблица подстановки. В этом диалоговом окне Таблица подстановки (рисунок 4) имеются два поля:
Рис.4. Диалоговое окно Таблица подстановки
- Подставлять значения по столбцам в: - при расположении заполняемой таблицы горизонтально;
- Подставлять значения по строкам в: - при вертикальном расположении таблицы.
В поле Подставлять значения по строкам в укажите ссылку на ячейку D11 и нажмите кнопку ОК.
После заполнения таблицы в диапазоне ячеек I3:L30 будет находиться формула:
{=ТАБЛИЦА(;D11)}
Использование инструмента Таблица подстановки - следующий шаг повышения уровня пользователя, но без применения дополнительных сервисных возможностей не дает этому методу преимуществ. Созданная таблица позволяет производить изменение данных в исходной таблице с отражением этих изменений в таблице для создания графика определения точки безубыточности.
На рисунке 5 в столбце Н с данными объема реализации для изменения шага применена другая формула.
Рис.5. Формулы, полученные при использовании Таблицы подстановки
Она основана на том, что по значению числа, введенного в ячейку А2, производит поиск шага изменения объема реализации в области Z1:АА30 (рисунок 6) и добавляет его к ячейке, находящейся выше.
Рис.6. Таблица ввода шага изменения объема реализации
Это нововведение мало чем отличается от второго случая создания таблицы. Для изменения шага объема реализации придется вводить его значение в ячейку А2, но с тем отличием, что с клавиатуры вводится не само значения шага, а порядковый номер, который присвоен значению этого шага в дополнительно созданной таблице (рисунок 6). Но этот метод имеет преимущество - возможность автоматизации процесса задания шага.
Автоматизация создания таблицы определения точки безубыточности
В качестве инструмента для изменения содержимого ячейки А2 используйте элемент управления Поле со списком.
Но просто внедрение элемента Поле со списком не позволит до конца завершить автоматизацию процесса создания таблицы, потому что при создании таблицы применение только элемента управления Переключатель предполагает перенос фактических данных из таблицы на листе РаспределениеЗатрат. И при этом переносе данных в ячейке D11 находится значение предполагаемого объема реализации. Это значение, попадая в область I2:L30, нарушит гармоничность таблицы определения точки безубыточности, а соответственно и график.
Поэтому элементу управления Поле со списком назначьте макрос Обнуление, производящий обнуление значение объема реализации в таблице (рисунок 1), состоящий из одной строки:
Range("D11").ClearContents
При вводе шага изменения объема реализации, для того, чтобы увидеть на графике найдена точка безубыточности или нет, нужно перейти в режим просмотра этого графика и убедиться в том, что данный шаг изменения объема реализации не позволяет увидеть на графике точку безубыточности, или она будет находиться на графике очень низко. Тогда необходимо вернуться на лист Анализ, изменить шаг и опять перейти в режим просмотра графика. Это довольно утомительный процесс. Для того, чтобы не делать при работе "лишних" движений, в ячейке С24 создайте формулу:
=ЕСЛИ(K30>L30;"Точка безубыточности не определена!";
ЕСЛИ(K16<L16;"Точка безубыточности НИЗКО!";""))
Задача этой формулы - определение шага изменения объема реализации в зависимости от:
- значения, когда точка безубыточности не определена на графике, о чем говорит сравнение крайнего значения суммарных затрат в ячейке К30, по которой создается график, с такой точкой объема выручки со значением в ячейке L30 - полученная выручка от реализации этого объема. Если значения суммарных затрат выше, то пересечения этих значений на графике нет и поэтому формула в ячейке С24 формирует текст: Точка безубыточности не определена!;
- значения, когда точка безубыточности находится очень низко. В этом случае возвращается текст: Точка безубыточности НИЗКО!. Для этого пользователь определяет самостоятельно масштабируемость графика, и, в зависимости от этого, определяет номер строки сравнения во второй функции ЕСЛИ.
Применение формулы с этим предупреждающим текстом позволит уменьшить потери времени на создание оптимального, визуально воспринимаемого графика поиска точки безубыточности.
Создание графика
Для создания графика выделите область Н1:L30 на листе Анализ и нажмите кнопку Мастер диаграмм на Стандартной панели инструментов. Мастер диаграмм поможет создании графика (рисунок 7).
Рис.7. Определение точки безубыточности графическим способом
Анализ таблицы распределения накладных расходов по видам продукции
Таблица распределения накладных расходов на листе РаспределениеЗатрат (рисунок 8) по видам продукции, создание которой было подробно описано в прошлых шагах, довольно сложна для анализа.
Рис.8. Таблица распределения накладных расходов предприятия по видам продукции на рабочем листе РаспределениеЗатрат
Причина - любое изменение данных в рабочих листах Нормы, Цех или Администрация при различных методиках разнесения косвенных затрат повлечет за собой изменение всех параметров таблицы в целом. Например, увеличение в два раза норм оплаты труда основных производственных рабочих линии стандартных бутылок повлечет за собой:
- увеличение постоянных общецеховых издержек по линии стандартных бутылок на 21,6% при уменьшении издержек по второй линии на 39%;
- увеличение постоянных общезаводских издержек по линии стандартных бутылок на 54% при уменьшении на 33% по остальным линиям;
- как следствие - точка безубыточности возрастет на 47%, в то время как по всем остальным производственным линиям она снизится от 25 до 18%.
Увеличение же объема реализации по той же линии на 10% влечет за собой увеличение точки безубыточности этой продукции на 5,3% и снижение ее про другим видам продукции на 1,0% - 2%.
Насколько взаимозависимы все параметры таблицы? Попытайтесь определить нулевую рентабельность производства продукции по изготовлению стандартных бутылок. С исходными данными в ячейке F35 - точка безубыточности по линии стандартных бутылок (рисунок 8) возвращено значение 4622083. Применение инструмента Подбор параметра для поиска точки безубыточности реализации стандартных бутылок в общей таблице вычислит значение равное 5430282. Для этого, вызвав диалоговое окно Подбор параметра, найдите значение 0 в ячейке F33 (Рентабельность), изменяя объем реализации. Обратите внимание: изменились и все параметры таблицы.
В таблице распределения затрат достаточно больщое количество неизвестных, которое возрастает, с увеличением видов продукции, статей затрат переменных и постоянных издержек, статей общезаводских и общецеховых издержек. В зависимости от применяемых видов распределения косвенных затрат финансовые показатели могут иметь самое различное значение. Причем точка безубыточности для отдельного продукта в составе общей таблицы распределения затрат может изменять свои значения, в зависимости от изменения параметров других видов продукции. Определение точек безубыточности для различных продуктов математическим путем просто не представляется возможным.
На следующем шаге мы рассмотрим определение точки безубыточности одного вида продукции в составе всей продукции выпускаемой заводом.
