Дані про доходи та витрати в Excel Sales Forecasting

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

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

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

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

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

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

Жодне правило бухгалтерського обліку не говорить вам, чи включати альтернативні витрати до розрахунку прибутку. Тут, ви не робите облік, ви використовуєте його , щоб допомогти закласти свої плани.

На малюнку показано, як прогнозування може допомогти вам спланувати, як підтримувати продуктові лінії.

Функція робочого аркуша TREND заснована на лінійній регресії — тут використовується історичний зв’язок між витратами та доходами.

Дані про Widgets і Gidgets досить прості. На малюнку показано фактичний дохід і фактичні прямі витрати на підтримку кожної лінії продуктів протягом кожного місяця з січня 2014 року по липень 2016 року, у рядках 3-33. Робочий лист отримує показники прибутку, просто віднімаючи витрати від доходів.

Рядок 35 показує прогнози на серпень 2016 року. Ось як він отримує їх:

Він прогнозує витрати на серпень 2016 року з використанням експоненційного згладжування; Для отримання додаткової інформації див. Розділ 15, але для тих із вас, хто підігрує вдома, константа згладжування ще не оптимізована шляхом мінімізації середньоквадратичної помилки.
Прогнози вартості відображаються в клітинках C35 і G35.

Для кожного продукту він прогнозує доходи на серпень 2016 року за допомогою регресійного підходу (де ви використовуєте надійний зв’язок між доходами від продажів і однією або кількома змінними-прогнозами, щоб зробити свій прогноз), під виглядом функції робочого аркуша TREND.
Використовуючи інформацію про історичний зв’язок між витратами та доходами для кожного продукту, він прогнозує в клітинках B35 і F35, якими будуть доходи, враховуючи прогнози витрат.

Він прогнозує прибуток на серпень 2016 року шляхом віднімання прогнозної вартості від прогнозованого доходу.
Додавання прогнозних прибутків для обох продуктових ліній дає загальний прибуток за серпень 2016 року в розмірі 11 330 доларів США.

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

Що відбувається, коли ви відмовляєтеся від Widgets і вкладаєте його витрати на підтримку Gidgets.

Тут стовпці від A до H ідентичні стовпцям на попередньому малюнку. Стовпці від J до K показують ефект відбирання ресурсів у віджетів і використання їх для підтримки гіджетів. Наступні кроки показують, як отримати ці прогнози:

У клітинку K3 введіть =C3+G3, скопіюйте та вставте формулу в клітинки K4–K33 та в клітинку K35.
Стовпець K тепер містить суму фактичних витрат для двох ліній продуктів із січня 2014 року по липень 2016 року плюс суму прогнозованих витрат у K35.

У клітинку J3 введіть =(F3/G3)*K3, скопіюйте та вставте формулу в клітинки J4–J33.
Ця формула отримує відношення доходу до вартості для Gidgets у січні 2014 року та множить його на загальні витрати, показані в клітинці K3. Ефект полягає в тому, щоб застосувати один показник валової маржі до більш високого показника витрат і оцінити, яким буде дохід від Gidgets в цьому випадку.

У клітинку J35 введіть цю формулу =TREND(J3:J33,K3:K33,K35) .
Це прогнозує доходи для Gidgets у серпні 2016 року, враховуючи співвідношення між прогнозами доходів і витрат у J3–K33, якщо ви вирішили підтримувати лише Gidgets.

Щоб отримати прогноз прибутку для Gidgets тільки в серпні 2016 року, введіть =J35-K35 в клітинку L35.

Зверніть увагу, що сума прибутку в серпні 2016 року для Widgets and Gidgets становить 4 128 доларів США + 7 201 доларів США = 11 330 доларів США. Але якщо ви передасте свої ресурси Widget для Gidgets, ваш прибуток за серпень 2016 року становитиме 14 004 доларів США — на 2 674 доларів США більше. У сирих доларах це не так багато, але це на 24 відсотки збільшення. Покоління європейських власників казино розбагатіли на набагато менших перевагах.

Причина, звісно, ​​полягає в тому, що валовий прибуток на Gidgets вищий, ніж на Widgets, хоча ваш дохід від Widgets майже на 30 відсотків більше, ніж на Gidgets. Узагальнити:

• У стовпці K ви поводитеся так, ніби ви передавали всі свої ресурси лише для Gidgets із січня 2014 року по липень 2016 року. Результатом цього є видалення всієї підтримки з віджетів і додавання її до підтримки, наданої для Gidgets.
• У стовпці J ви оцінюєте дохід, який ви отримали б, якби підтримували лише Gidgets, використовуючи історичну маржу для Gidgets.
• Використовуючи функцію TREND, ви регресуєте оцінки доходу в J3–J33 на витрати в K3–K33 і застосовуєте результат до оціночної вартості в K35. Відніміть K35 від J35, щоб отримати прогноз прибутку, якщо ви визнали свої альтернативні витрати та підтримували лише Gidgets.

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

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