Данни за приходите и разходите в прогнозиране на продажбите в Excel

Ако вашата екологична ниша е във веригата за управление на продажбите на вашата компания, вероятно поддържате запис в Excel за резултатите от продажбите на компанията или имате някой да го съхранява вместо вас. Ако вашите продукти се справят добре, обичате да стартирате Excel, да гледате тези резултати и да въздишате доволно.

Ако вашите продукти не работят както трябва, вие рефлексивно стартирате Excel, разглеждате тези резултати и се чудите как да поправите това, което се е объркало. Така или иначе, данните вероятно вече са в работна книга на Excel и това прави прогнозирането ви лесно.

Изграждането на базови линии за приходи е доста лесно. Вие решавате вашия прогнозен период (продължителността от време, която е представена от всяко наблюдение във вашата базова линия) и ако е необходимо, използвайте Excel, за да изчислите общите приходи за всеки период във вашата базова линия. Сега сте готови да прогнозирате колко ще внесете през предстоящия период.

Изчисляването на разходите, особено на разходите за продажби, е по-трудно. Трябва ли да включите цената на продадените стоки? Разбира се, вие ще включите разходите за заплати и комисионни на търговски представители, за промоции на продукти, за изоставяне, за боклук и дрънкулки, за пътувания и развлечения. Какво ще кажете за непреките разходи?

Един от начините да продължите е да извадите тези разходи за всеки период във вашата базова линия от приходите за същия период, за да получите прогноза за печалбата за периода. Това създава нова базова линия за печалба, която можете да използвате, за да прогнозирате печалбата за следващия период.

Но какво да кажем за алтернативните разходи? Когато харчите пари, за да поддържате продажбите на една продуктова линия, вие отклонявате ресурси от друга продуктова линия. Това е алтернативен разход: имахте възможността да похарчите тези пари в подкрепа на Gidgets, а не на Widgets, и може да сте създали повече приходи и повече печалба, ако сте използвали парите, за да помогнете за продажбата на Widgets.

Никое счетоводно правило не ви казва дали да включите алтернативните разходи в изчисленията на печалбата си. Тук не се занимавате със счетоводство, вие го използвате , за да ви помогне да поставите плановете си.

Фигурата илюстрира как прогнозирането може да ви помогне да планирате как да поддържате продуктови линии.

Функцията на работния лист TREND се основава на линейна регресия - тук, използвайки историческата връзка между разходите и приходите.

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

Ред 35 показва прогнози за август 2016 г. Ето как ги получава:

Той прогнозира разходите за август 2016 г., използвайки експоненциално изглаждане; вижте Глава 15 за повече информация, но за тези от вас, които си играят у дома, константата на изглаждане все още не е оптимизирана чрез минимизиране на средноквадратичната грешка.
Прогнозите за разходите са показани в клетки C35 и G35.

За всеки продукт той прогнозира приходите за август 2016 г., като използва регресионния подход (при който използвате надеждна връзка между приходите от продажби и една или повече променливи за прогнозиране, за да направите прогнозата си), под прикритието на функцията на работен лист TREND.
Използвайки информация за историческата връзка между разходите и приходите за всеки продукт, той прогнозира в клетки B35 и F35 какви биха били приходите, като се имат предвид прогнозите за разходите.

Той прогнозира печалбите за август 2016 г., като извади прогнозните разходи от прогнозните приходи.
Добавянето на прогнозните печалби за двете продуктови линии води до обща печалба за август 2016 г. от $11,330.

Ами сега, ако вземете предвид алтернативните разходи за поддръжка на Widgets и вместо това ги излеете в Gidgets? На следващата фигура можете да видите ефекта от изоставянето на Widgets и влагането на неговите разходи - ресурсите, които вашата компания изразходва за поддръжка на Widgets - в поддръжка само на Gidgets.

Какво се случва, когато изоставите Widgets и вложите разходите му в поддръжка на Gidgets.

Тук колоните от A до H са идентични с тези на по-ранната фигура. Колони от J до K показват ефекта от отнемането на ресурсите от Widgets и използването им за поддръжка на Gidgets. Следните стъпки показват как да получите тези прогнози:

В клетка 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 и Gidgets е $4,128 + $7,201 = $11,330. Но ако сте ангажирали ресурсите си за Widget на Gidgets, печалбата ви за август 2016 г. ще бъде $14,004 - $2,674 повече. В необработени долари това не изглежда много, но е 24 процента увеличение. Поколения европейски собственици на казина забогатяват с много по-малки предимства.

Причината, разбира се, е, че брутната печалба на Gidgets е по-голяма от тази на Widgets, въпреки че приходите ви от Widgets са почти 30 процента по-големи, отколкото на Gidgets. Да обобщим:

• В колона K се държите така, сякаш сте заложили всичките си ресурси само на Gidgets от януари 2014 г. до юли 2016 г. Ефектът е премахването на цялата поддръжка от Widgets и добавянето й към поддръжката, предоставена на Gidgets.
• В колона J изчислявате приходите, които бихте спечелили, ако поддържате само Gidgets, като използвате историческия марж за Gidgets.
• Използвайки функцията TREND, вие регресирате прогнозите за приходите в J3 до J33 към разходите в K3 до K33 и прилагате резултата към прогнозните разходи в K35. Извадете K35 от J35, за да получите прогноза за печалба, ако сте разпознали вашите алтернативни разходи и поддържате само Gidgets.

Използването на два различни сценария - Widgets с Gidgets и Gidgets самостоятелно - прави този пример малко по-труден за следване. Но това е реалистична илюстрация за това как можете да използвате основната функция за прогнозиране TREND, за да помогнете да вземете информирано решение относно разпределението на ресурсите.

Разбира се, други съображения биха взели предвид решението за прехвърляне на ресурси от една продуктова линия към друга — анализ на естеството на грешките в прогнозите (често наричани остатъчни), невъзможни разходи, възможно преоборудване, за да се поддържа допълнителен производствен капацитет в продуктова линия, необходимостта от постоянна поддръжка за клиенти, които са инвестирали в Widgets, и т.н. Но един от критериите почти винаги са финансовите оценки и ако можете да прогнозирате финансовите показатели с увереност, вие сте по-напред.