Як зробити аналіз беззбитковості в Excel?
Аналіз беззбитковості може допомогти вам досягти точки, коли чистий прибуток дорівнює нулю, що означає, що загальний дохід дорівнює загальним витратам. Цілком корисно встановити ціну на новий продукт, коли ви можете прогнозувати свої витрати та продажі.
- Проведіть аналіз беззбитковості за допомогою функції Goal Seek
- Зробіть аналіз беззбитковості за формулою
- Зробіть аналіз беззбитковості за допомогою діаграми
Проведіть аналіз беззбитковості за допомогою функції Goal Seek
Припустимо, ви збираєтеся продати новий продукт, і ви знаєте змінну вартість одиниці та загальну фіксовану вартість. Тепер ви збираєтеся прогнозувати можливі обсяги продажів і оцінювати товар на основі них.
1. Складіть просту таблицю та заповніть елементи вказаними даними в таблиці. Дивіться знімок екрана нижче:
2. Введіть правильні формули для розрахунку доходу, змінної вартості та прибутку. Див. Скріншот вище:
Доходи = Ціна за одиницю x Продана одиниця
Змінні витрати = Вартість одиниці х Продана одиниця
Прибуток = Дохід - Змінна вартість - Фіксовані витрати
3. Натисніть дані > Що-якщо аналіз > Мета пошуку.
4. У діалоговому вікні, що відкриває Goal Seek, виконайте наступні дії (див. Скріншот вище):
(1) Вкажіть Встановити комірку як комірка прибутку, в нашому випадку це комірка В7;
(2) Вкажіть Оцінювати as 0;
(3) Вкажіть Змінюючи комірку як комірка одиниці ціни, в нашому випадку це комірка B1.
(4) Клацніть OK кнопки.
5. А потім спливає діалогове вікно "Стан пошуку мети". Клацніть на OK кнопку, щоб застосувати його.
Тепер вона змінює ціну одиниці з 40 до 31.579, а чистий прибуток - до 0. Отже, якщо ви прогнозуєте обсяг продажів 50, а ціна за одиницю не може бути менше 31.579, інакше настає збиток.
Завантажте складний шаблон беззбитковості
http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx
Демонстрація: Проведіть аналіз беззбитковості за допомогою функції Goal Seek у програмі Excel
Легко поєднуйте кілька робочих аркушів / книг / файлів CSV в один аркуш / книгу
Об’єднувати десятки аркушів із різних робочих зошитів в один аркуш може бути втомливим. Але з Kutools для Excel Об’єднати (робочі аркуші та робочі зошити) Утиліта, ви можете зробити це всього за кілька кліків!
Зробіть аналіз беззбитковості за формулою
Порівнюючи з функцією «Пошук цілей», ми також можемо застосувати формулу для простого аналізу беззбитковості в Excel.
1. Складіть просту таблицю та заповніть елементи вказаними даними в таблиці. У цьому методі ми припускаємо, що прибуток дорівнює 0, і ми вже прогнозували продану одиницю, вартість одиниці та постійні витрати. Дивіться знімок екрана нижче:
2. У таблиці введіть формулу = B6 / B2 + B4 в комірку B1 для розрахунку ціни за одиницю введіть формулу = B1 * B2 в комірку B3 для розрахунку доходу та введіть формулу = B2 * B4 у комірку В5 для змінних витрат. Дивіться знімок екрана нижче:
І тоді, коли ви зміните іне одне значення прогнозованої проданої одиниці, вартість одиниці або постійні витрати, величина ціни одиниці автоматично зміниться. Див. Скріншот вище:
Порада: Збережіть діапазон як запис автотексту (інші формати комірок та формули) для повторного використання в майбутньому
Пересилати комірки та застосовувати кілька формул для аналізу беззбитковості кожного разу, мабуть, дуже втомливо. Kutools для Excel пропонує гарний обхідний шлях Автотекст утиліта для збереження діапазону як запису автотексту, який може зберігати формати комірок та формули в діапазоні. І тоді ви зможете повторно використовувати цей діапазон лише одним клацанням миші в будь-якій книзі.
Зробіть аналіз беззбитковості за допомогою діаграми
Якщо ви вже записали дані про продажі, ви також можете зробити аналіз беззбитковості за допомогою діаграми в Excel. Цей метод допоможе вам легко створити діаграму беззбитковості.
1. Підготуйте таблицю продажів, як показано на знімку екрана. У нашому випадку ми припускаємо, що продані одиниці, вартість одиниці та постійні витрати є фіксованими, і нам потрібно зробити аналіз беззбитковості за ціною одиниці.
2. Заповніть таблицю, як показано нижче:
(1) У комірку Е2 введіть формулу = D2 * $ B $ 1та перетягніть його ручку автозаповнення до діапазону E2: E13;
(2) У комірку F2 введіть формулу = D2 * $ B $ 1 + $ B $ 3та перетягніть його ручку автозаповнення до діапазону F2: F13;
(3) У комірку G2 введіть формулу = E2-F2та перетягніть його ручку автозаповнення до діапазону G2: G13.
Наразі ми закінчили вихідні дані діаграми беззбитковості, які ми створимо пізніше. Дивіться знімок екрана нижче:
3. У таблиці виберіть одночасно стовпець Дохід, стовпець Витрати та Прибуток, а потім натисніть Insert > Вставити діаграму лінії або області > Лінія. Дивіться знімок екрана:
4. Тепер створена лінійна діаграма. Клацніть правою кнопкою миші на діаграмі та виберіть у контекстному меню пункт Вибрати дані. Дивіться знімок екрана нижче:
5. У діалоговому вікні Вибір джерела даних будь-ласка:
(1) В Записи про легенду (Серія) розділ, виберіть одну із серій, як вам потрібно. У своєму прикладі я вибираю Доходи серія;
(2) Клацніть на Редагувати кнопка в Горизонтальні (категорія) мітки осей розділ;
(3) У діалоговому вікні Етикетки осей, що вискакує, вкажіть стовпець Ціна одиниці (крім назви стовпця) як діапазон міток осі;
(4) Клацніть OK > OK зберегти зміни.
Тепер на графіку беззбитковості ви побачите, що точка беззбитковості виникає, коли ціна дорівнює 36, як показано на знімку екрана нижче:
Так само ви можете створити діаграму беззбитковості для аналізу точки беззбитковості за проданими одиницями, як показано на знімку екрана нижче:
Демонстрація: Проведіть аналіз беззбитковості за допомогою діаграми в Excel
Легко експортуйте / зберігайте виділення як окремі файли PDF / CSV / TEXT / HTML у програмі Excel
Наприклад, коли ви закінчите аналіз беззбитковості в Excel, ви можете зробити свою презентацію з цим аналізом беззбитковості. У цьому випадку ви можете застосувати Kutools для Excel Експортувати діапазон у файл утиліта для швидкого експорту виділення як окремий файл PDF, HTML, CSV, текстовий файл тощо.
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!