Intäkts- och kostnadsdata i Excel-försäljningsprognoser

Om din ekologiska nisch finns i ditt företags försäljningsledningskedja är det troligt att du för ett register i Excel över företagets försäljningsresultat, eller så har du någon som håller det åt dig. Om dina produkter fungerar bra, gillar du att starta Excel, titta på dessa resultat och suckar belåtet.

Om dina produkter inte fungerar som de ska, startar du reflexmässigt upp Excel, tittar på resultaten och undrar hur du åtgärdar det som har gått fel. Hur som helst, data finns förmodligen redan i en Excel-arbetsbok, och det gör dina prognoser enkelt.

Att bygga intäktsbaslinjer är ganska enkelt. Du bestämmer din prognosperiod (längden på tiden som representeras av varje observation i din baslinje) och vid behov använder du Excel för att räkna ut den totala intäkten för varje period i din baslinje. Nu är du redo att förutse hur mycket du kommer att få in under den kommande perioden.

Att räkna ut kostnader, särskilt försäljningskostnader, är svårare. Ska du inkludera kostnaden för sålda varor? Naturligtvis kommer du att inkludera kostnaderna för säljarnas löner och provisioner, för produktkampanjer, för lämna-behinds, för skräp och prydnadssaker, för resor och underhållning. Hur är det med indirekta kostnader?

Ett sätt att gå vidare är att subtrahera dessa kostnader, för varje period i din baslinje, från intäkterna för samma period för att få en vinstuppskattning för perioden. Detta skapar en ny vinstbaslinje som du kan använda för att prognostisera nästa periods vinst.

Men hur är det med alternativkostnader? När du spenderar pengar för att stödja försäljningen av en produktlinje, avleder du resurser från en annan produktlinje. Det är en alternativkostnad: Du hade möjligheten att spendera de pengarna till stöd för widgets snarare än widgets, och du kanske hade skapat mer intäkter och mer vinst om du hade använt pengarna för att hjälpa till att sälja widgets.

Ingen redovisningsregel talar om för dig om du ska inkludera alternativkostnader i dina vinstberäkningar. Här, du är inte gör bokföring, du använder den för att hjälpa lägga dina planer.

Figuren illustrerar hur prognoser kan hjälpa dig att planera hur du ska stödja produktlinjer.

TREND-kalkylbladsfunktionen är baserad på linjär regression – här med hjälp av det historiska förhållandet mellan kostnader och intäkter.

Uppgifterna om widgets och gidgets är ganska okomplicerad. Figuren visar de faktiska intäkterna och de faktiska direkta kostnaderna för att stödja varje produktlinje under varje månad från januari 2014 till juli 2016, i rad 3 till rad 33. Arbetsbladet får vinstsiffrorna helt enkelt genom att subtrahera kostnaderna från intäkterna.

Rad 35 visar prognoser för augusti 2016. Så här får de dem:

Den prognostiserar kostnaderna för augusti 2016 med hjälp av exponentiell utjämning; se kapitel 15 för mer information, men för er som spelar med hemma har utjämningskonstanten ännu inte optimerats genom att minimera medelkvadratfelet.
Kostnadsprognoserna visas i cellerna C35 och G35.

För varje produkt prognostiserar den intäkterna för augusti 2016 genom att använda regressionsmetoden (där du använder ett pålitligt förhållande mellan försäljningsintäkter och en eller flera prediktorvariabler för att göra din prognos), i skepnad av TREND-kalkylbladsfunktionen.
Med hjälp av information om det historiska sambandet mellan kostnaderna och intäkterna för varje produkt prognostiserar den i cellerna B35 och F35 vad intäkterna skulle bli, givet kostnadsprognoserna.

Den prognostiserar vinsten för augusti 2016 genom att subtrahera den prognostiserade kostnaden från den prognostiserade intäkten.
Att lägga till prognostiserade vinster för båda produktlinjerna resulterar i en total vinst för augusti 2016 på 11 330 USD.

Nu, tänk om du tog hänsyn till alternativkostnaderna för att stödja Widgets och istället hällde in dem i Gidgets? I nästa figur kan du se effekten av att överge widgetar och lägga dess kostnader – de resurser som ditt företag spenderar på att stödja widgets – endast på att stödja widgets.

Vad händer när du överger Widgets och lägger dess kostnader på att stödja Gidgets.

Här är kolumnerna A till H identiska med de i den tidigare figuren. Kolumnerna J till K visar effekten av att ta resurserna från Widgets och använda dem för att stödja Gidgets. Följande steg visar hur man får dessa projektioner:

I cell K3 anger du =C3+G3 och kopierar och klistra in formeln i cellerna K4 till K33 och i cell K35.
Kolumn K har nu summan av de faktiska kostnaderna för de två produktlinjerna från januari 2014 till och med juli 2016, plus summan av prognostiserade kostnader i K35.

I cell J3 anger du =(F3/G3)*K3 och kopierar och klistra in formeln i cellerna J4 till J33.
Denna formel får förhållandet mellan intäkter och kostnader för Gidgets i januari 2014 och multiplicerar det med de totala kostnaderna som visas i cell K3. Effekten är att tillämpa ett mått på bruttomarginal på ett högre kostnadsmått, och uppskatta vad intäkterna för Gidgets i så fall skulle bli.

I cell J35 anger du den här formeln =TREND(J3:J33,K3:K33,K35) .
Detta prognostiserar intäkterna för Gidgets i augusti 2016, med tanke på förhållandet mellan prognoserna för intäkter och kostnader i J3 till K33, om du bestämde dig för att endast stödja Gidgets.

För att få en vinstprognos för Gidgets först i augusti 2016, skriv in =J35-K35 i cell L35.

Lägg märke till att summan av vinsten i augusti 2016 för Widgets och Gidgets är $4,128 + $7,201 = $11,330. Men om du anvisade dina Widget-resurser till Gidgets, skulle din vinst för augusti 2016 vara $14 004 — $2 674 mer. I råa dollar verkar det inte vara så mycket, men det är en ökning med 24 procent. Generationer av europeiska kasinoägare har blivit rika på mycket mindre fördelar.

Anledningen är förstås att bruttomarginalen på Gidgets är större än den på Widgets, även om dina intäkter på Widgets är nästan 30 procent större än på Gidgets. För att sammanfatta:

• I kolumn K agerar du som om du endast hade anvisat alla dina resurser till Gidgets, från januari 2014 till och med juli 2016. Effekten är att ta bort allt stöd från Widgets och lägga till det till stödet som ges till Gidgets.
• I kolumn J uppskattar du intäkterna du skulle tjäna om du endast stödde Gidgets, med hjälp av den historiska marginalen för Gidgets.
• Med hjälp av TREND-funktionen regresserar du intäktsuppskattningarna i J3 till J33 till kostnaderna i K3 till K33 och tillämpar resultatet på den beräknade kostnaden i K35. Subtrahera K35 från J35 för att få en prognos om vinst om du kände igen dina alternativkostnader och endast stödde gidgets.

Att använda två olika scenarier - Widgets med Gidgets och Gidgets enbart - gör det här exemplet lite svårare att följa. Men det är en realistisk illustration av hur du kan använda den grundläggande prognosfunktionen TREND för att ta ett välgrundat beslut om resursallokering.

Naturligtvis skulle andra överväganden inkludera ett beslut att flytta resurser från en produktlinje till en annan — en analys av arten av felen i prognoserna (ofta kallade restprodukterna), sänkta kostnader, eventuell omställning för att stödja ökad tillverkningskapacitet i en produktlinje, nödvändigheten av löpande support för kunder som har investerat i Widgets och så vidare. Men ett av kriterierna är nästan alltid ekonomiska uppskattningar, och om du kan förutsäga ekonomin med tillförsikt ligger du före.