Обчисліть точку беззбитковості та беззбитковість у програмі Excel - ваш асистент

Підручник, зроблений в Excel 2016

У цьому підручнику ми створимо необхідні формули розрахунку для визначити точку беззбитковості і точка беззбитковості бізнесу завдяки Excel.

Підготовка файлу

Щоб розрахувати ці 2 параметри, нам потрібно знати деякі дані, такі як:

Кількість Фіксовані заряди (CF) року, що вивчається;
Кількість Змінні навантаження (CV) року, що вивчається;
Сума, яку ви повинні заплатити Оборот (Каліфорнія) прогноз на вивчений рік.

фіксовані збори відносяться до всіх витрат компанії, незалежно від досягнутого обороту. Незалежно від того, чи є оборот позитивним чи негативним, ці збори доведеться сплатити. Це оренда приміщень, зарплата працівникам, страхування ...

змінні витрати, тим часом, майте на увазі витрати, пов’язані з товарообігом, такі як придбання товарів, виробничі витрати ... Чим більше збільшується оборот, тим більше збільшуються ці витрати.

Внизу статті ви знайдете використаний файл, де вам потрібно буде лише додати формули розрахунку, які ми збираємось створити.

У нашому прикладі ми призначимо такі значення:

Фіксовані платежі: 10 000 євро (у грошовому форматі);
Змінні витрати: тут я почну з прикладу мікропідприємства, яке надає послуги, подібні до мого, яке, отже, не має змінних витрат, але я рахую частину, яка буде передана до RSI (соціальний режим для незалежних) або 25,68% продажів. Як правило, коли немає змінного заряду, ми встановлюємо цей параметр на 0 €, але це я зробив для власних розрахунків, і він залишається змінним зарядом, оскільки те, що я плачу RSI, пропорційне моєму обороту. Формула розрахунку = B3 * (25,68/100) (у валютному форматі);

Оборот: 18 000 євро (у грошовому форматі). Останні також можуть бути розраховані за фіксованими платежами + змінними комісіями, але потрібно було б знати величину змінних комісій. Однак мої змінні витрати розраховуються відповідно до обороту. Через це я не можу ввести цей розрахунок у комірку B3, оскільки для нього використовуються кругові посилання. Ви самі обираєте розрахунок, який хочете виконати.

Що таке точка беззбитковості (SR)

Точка беззбитковості - це оборот, якого повинна досягти компанія, щоб отримати нульовий результат. Саме тоді, коли компанія досягне цього обороту, вона почне отримувати прибуток, оскільки всі її витрати будуть оплачені. Приклад: компанія A має точку беззбитковості 25 000 євро, тому їй доведеться заробляти не менше 25 000 євро обороту, щоб не втратити гроші.

Обчисліть точку беззбитковості

Щоб розрахувати точку беззбитковості, нам потрібно знати:

Мінлива маржа витрат (MCV)що позначає різницю між товарообігом та змінними витратами;
Норма змінної норми витрат (TMCV).

Націнка на змінні витрати обчислюється за оборотом - змінними витратами (= B3-B2), що становить 18000 - 4622,40 = 13377,60 € (у грошовому форматі).

Норма маржі змінних витрат обчислюється за маржею змінних витрат/обороту/100 (= B4/B3/100), тобто 13 377,60/18 000/100 = 0,74% (у форматі відсотків із 2 знаками після коми).

Зверніть увагу, що в цьому обчисленні не потрібно додавати дужки, оскільки порядок переваги обчислень означає, що спочатку B4 буде поділено на B3, перш ніж ділити ціле на 100.

Нарешті, точка беззбитковості розраховується наступним чином: Фіксовані платежі/Норма націнки за змінними витратами/100 (= B1/B5/100), тобто 10000/0,74%/100 = 13 455,33 євро.

Ця компанія почне отримувати прибуток, коли досягне обороту в 13 455,33 євро.

У моєму прикладі мої змінні витрати корелюють з оборотом, лише якщо я модифікую свої постійні витрати, то точка беззбитковості зміниться, але якщо я не буду вказувати відсоток у змінних витратах, як це було б у класичній точці беззбитковості обчислення, то ці дані будуть змінюватися з кожною модифікацією значення CF, CV та/або CA.

Що таке мертва точка (ПМ)

Точка беззбитковості використовується для визначення дати досягнення точки беззбитковості. Приклад: Компанія А досягне межі беззбитковості на 200-й день року, тобто 20 липня 2017 року, вона почне отримувати прибуток з цього дня. Точку беззбитковості можна також розрахувати за кількістю, тобто ми можемо знати, скільки одиниць продукту потрібно продати, щоб почати отримувати прибуток.

Обчисліть точку беззбитковості за кількістю днів

Щоб розрахувати точку беззбитковості, нам потрібні дані, отримані раніше: оборот та точка беззбитковості.

Він обчислюється наступним чином: точка беззбитковості/(Оборот/360) (= В6/(В3/360)) або 13455,33/(18000/360) = 269,11. Як правило, для спрощення бухгалтерського обліку ми починаємо з місяців 30 днів і, отже, року 360 днів, отже, 360 у цьому розрахунку. Однак, оскільки нам потрібна кількість днів, ми не збираємося зберігати 269,11, але округлимо його до наступного вищого значення за формулою: = ROUNDUP (B6/(B3/360); 0) .

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

Для цього, розмістившись у комірці B7, клацніть на випадаючому списку групи Число на вкладці Додому, потім Інші цифрові формати ... або на маленькій стрілці в правому нижньому куті тієї ж групи.

Виберіть спеціальну категорію та введіть:

Щоб дізнатись більше про цей синтаксис, я запрошую вас прочитати цю статтю про користувацькі формати Excel.

Точка беззбитковості буде досягнута на 270-й день року.

Щоб отримати точну дату, беручи до уваги справжню кількість днів у кожному місяці та кількість днів у лютому, залежно від року, я вводжу в B8: = DATE (YEAR (TODAY ()); 1; 1) + B7 -1 (у короткому форматі дати). У цій формулі я беру перший день поточного року і додаю 270 днів, отриманих раніше. Точка беззбитковості буде досягнута 27.09.2017, саме з цієї дати компанія отримає прибуток.

Щоб перевірити номер дня зі своїми розрахунками, ви можете скористатися цим посиланням.

Обчисліть нейтральну точку за кількістю

Щоб знати кількість одиниць товару, яку потрібно продати, щоб бути вигідною, необхідно відштовхуватися від середньої ціни або погодинної ставки.

У цьому прикладі ми починаємо з погодинної ставки 50 євро в B9.

Таким чином, обчисленням цієї точки беззбитковості є точка беззбитковості/50, яка округляється (= ROUNDUP (B6/B9; 0)) або 13 455,33/50 = 270.

Ви також можете застосувати власний формат:

[1] 0 "одиниць на продаж"

Ця компанія повинна продати 270 товарів по 50 €, щоб досягти нульового результату.

Ми також можемо розрахувати в B10 кількість одиниць, що продаються за місяць, за формулою = ROUNDUP (B10/12; 0) або 270/12 = 23 одиниці для продажу щомісяця.

Призначте власний формат:

[1] 0 "одиниць, що продаються на місяць"

Нарешті, ми можемо розрахувати кількість одиниць, що продаються за відпрацьований день (ми округляємо до 20 днів). Формула = ROUNDUP (B11/20; 0) або 22/20 = 2 одиниці для продажу на день.

Призначте власний формат:

[1] 0 "одиниць для продажу на день"

Ваша матриця розрахунку заповнена.

Якщо ви хочете, ви можете заблокувати комірки, що містять формули обчислення.

Щоб керувати користувачем, застосуйте колір фону до нередагованих комірок (або навпаки на зручність).

Далі виділіть усі клітинки вашого аркуша за допомогою Ctrl + A, а потім клацніть правою кнопкою миші> Формат комірки... На вкладці Захист, зніміть прапорець біля пункту Заблоковано, а потім клацніть Гаразд.

Потім виділіть комірки, які потрібно заблокувати (використовуйте клавішу Ctrl, щоб виділити кілька несуміжних комірок). Поверніться до вкладки Захист щоб встановити прапорець Заблоковано та клацнути Гаразд.

Нарешті, клацніть правою кнопкою миші на назві вкладки внизу аркуша> Захистити аркуш ....

У вікні введіть пароль, якщо хочете (запам'ятайте його). Потім зніміть прапорець Вибрати заблоковані комірки перед натисканням Гаразд.

Щоб дізнатися більше про захист, див. Статтю про захист книги Excel.