Excel

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

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

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

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

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

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

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

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

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

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

Таблиця показників діяльності підприємства в Microsoft Excel

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

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

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

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

    2. Таблиця розрахунку точки беззбитковості в Microsoft Excel

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

Точка беззбитковості на підприємстві в Microsoft Excel

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

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

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

    2. Вибір типу діаграми в Microsoft Excel

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

    3. Перехід до вибору даних в Microsoft Excel

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

    4. Вікно вибору джерела ряду в Microsoft Excel

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

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

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

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

    5. Вікно зміни ряду Загальні витрати в Microsoft Excel

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

    6. Закриття вікна вибору джерела даних в Microsoft Excel

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

    7. Графік загальних витрат в Microsoft Excel

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

    8. Перехід до вибору даних в програмі Microsoft Excel

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

    9. Вікно вибору джерела ряду в програмі Microsoft Excel

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

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

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

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

    10. Вікно зміни ряду Загальний дохід в Microsoft Excel

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

    11. Закриття вікна вибору джерела даних в програмі Microsoft Excel

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

Точка беззбитковості на графіку в Microsoft Excel

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

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

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