визначення точки беззбитковості в Microsoft Excel

Зміст

• Точка беззбитковості
  • Розрахунок точки беззбитковості
  • Створення графіка
• Питання та відповіді

Одним з базових економічних і фінансових розрахунків діяльності будь-якого підприємства є визначення його точки беззбитковості. Даний показник вказує на те, при якому обсязі виробництва діяльність організації буде рентабельною і вона не зазнає збитків. Програма Excel надає користувачам інструменти, які значною мірою дозволяють полегшити визначення даного показника і відобразити отриманий результат графічно. Давайте з'ясуємо, як ними користуватися при знаходженні точки беззбитковості на конкретному прикладі.

Точка беззбитковості

Суть точки беззбитковості полягає в тому, щоб знайти величину обсягу виробництва, при якому розмір прибутку (збитків) буде дорівнює нулю. Тобто, при збільшенні обсягів випуску продукції підприємство почне показувати прибутковість діяльності, а при зниженні – збитковість.

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

Саме з співвідношенням постійних і змінних витрат пов'язано поняття точки беззбитковості. До досягнення певного обсягу виробництва постійні витрати становлять істотну величину в загальній собівартості продукції, але зі збільшенням обсягу їх питома вага падає, а значить і падає собівартість одиниці виробленого товару. На рівні точки беззбитковості витрати на виробництво і дохід від реалізації товарів або послуг рівні. При подальшому збільшенні обсягу виробництва підприємство починає отримувати прибуток. Ось тому так важливо визначити обсяги виробництва, при яких досягається точка беззбитковості.

Розрахунок точки беззбитковості

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

• Постійні витрати;
• Змінні витрати на одиницю продукції;
• Ціна реалізації одиниці продукції.

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

1. Будуємо нову таблицю, грунтуючись на таблиці-исходнике. Перша колонка нової таблиці-це кількість товарів (або партій), виготовлених підприємством. Тобто, номер рядка буде позначати кількість виготовлених товарів. У другому стовпці знаходиться величина постійних витрат. Вона у нас у всіх рядках буде дорівнює 25000 . У третьому стовпці-загальна сума змінних витрат. Ця величина для кожного рядка буде дорівнює добутку кількість товарів, тобто, вмісту відповідної комірки першого стовпця, на 2000 рублів .

   У четвертому стовпці розташована загальна сума витрат. Вона становить суму осередків відповідного рядка другого і третього стовпця. У п'ятому стовпці розташований загальний дохід. Він розраховується шляхом множення ціни одиниці товару ( 4500 р. ) на сукупну їх кількість, яка вказана у відповідному рядку першого стовпця. У шостому стовпці розміщено показник чистого прибутку. Він обчислюється шляхом віднімання від загального доходу ( стовпець 5 ) суми витрат ( стовпець 4 ).

   Тобто, в тих рядках, у яких у відповідних осередках останнього стовпця буде негативне значення, спостерігається збиток підприємства, в тих, де показник буде дорівнює 0 - досягнута точка беззбитковості, а в тих, де він буде позитивним – відзначається прибуток в діяльності організації.

   Для наочності заповнюємо 16 рядків. У першому стовпці буде число товарів (або партій) від 1 до 16 . Наступні стовпці заповнюються за тим алгоритмом, який був зазначений вище.


2. Як бачимо, точка беззбитковості досягається на 10 товар. Саме тоді загальний дохід (45000 рублів) дорівнює сукупним витратам, а чистий прибуток дорівнює 0 . Вже починаючи з випуску одинадцятого товару підприємство показує прибуткову діяльність. Отже, в нашому випадку точка беззбитковості в кількісному показнику становить 10 одиниць, а в грошовому – 45000 рублів .

Створення графіка

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

1. Переходимо у вкладку " Вставка» . Тиснемо на значок »Точка" , який розміщується на стрічці в блоці інструментів " діаграми» . Перед нами відкривається вибір декількох типів графіків. Для вирішення нашої задачі цілком підійде тип " точкова з гладкими кривими і маркерами» , тому клацаємо по даному елементу списку. Хоча, при бажанні, можна використовувати і деякі інші види діаграм.

2. Перед нами відкривається порожня область діаграми. Слід заповнити її даними. Для цього натискаємо правою кнопкою миші по області. В активованому меню вибираємо позицію " вибрати дані...» .

3. Відбувається запуск вікна вибору джерела даних. У його лівій частині є блок " елементи легенди (ряди)» . Тиснемо на кнопку »додати" , яка розміщена в зазначеному блоці.

4. Перед нами відкривається вікно під назвою " зміна ряду» . У ньому ми повинні вказати координати розміщення даних, на основі яких буде будуватися один з графіків. Для початку побудуємо графік, в якому відображалися б загальні витрати. Тому в полі " ім'я ряду» вводимо з клавіатури запис " загальні витрати» .

   У полі " Значення X» вказуємо координати даних, розташованих в стовпці " кількість товарів» . Для цього встановлюємо курсор в дане поле, а потім, зробивши затиск лівої кнопки миші, виділяємо відповідний стовпець таблиці на аркуші. Як бачимо, після зазначених дій його координати відобразяться у вікні зміни ряду.

   У наступному полі " Значення Y» слід відобразити адресу стовпця " загальна сума витрат» , в якому розташовані потрібні нам дані. Діємо за вищевказаним алгоритмом: ставимо курсор в поле і виділяємо з затиснутою лівою кнопкою миші осередки потрібного нам стовпця. Дані будуть відображені в полі.

   Після того, як зазначені маніпуляції були проведені, тиснемо на кнопку «OK» , розміщену в нижній частині вікна.


5. Після цього автоматично відбувається повернення до вікна вибору джерела даних. У ньому також потрібно натиснути на кнопку «OK» .

6. Як бачимо, слідом за цим на аркуші відобразиться графік загальних витрат підприємства.

7. Тепер нам належить побудувати лінію загального доходу підприємства. Для цих цілей натискаємо правою кнопкою миші по області діаграми, на якій вже розміщена лінія загальних витрат організації. У контекстному меню вибираємо позицію " вибрати дані...» .

8. Знову запускається вікно вибору джерела даних, в якому знову потрібно натиснути на кнопку»додати" .

9. Відкривається невелике віконце зміни ряду. У полі " ім'я ряду» на цей раз пишемо " загальний дохід» .

   У полі «Значення X» слід внести координати стовпця " кількість товарів» . Робимо це тим же способом, який ми розглядали при побудові лінії загальних витрат.

   У полі «Значення Y» , точно так же вказуємо координати стовпця " загальний дохід» .

   Після виконання цих дій тиснемо на кнопку «OK» .


10. Вікно вибору джерела даних закриваємо, натиснувши на кнопку «OK» .

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

Таким чином, ми досягли цілей створення даного графіка.

Урок: як зробити діаграму в Екселі

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