Inntekts- og kostnadsdata i Excel-salgsprognoser

Hvis din økologiske nisje er i bedriftens salgsledelseskjede, er det sannsynlig at du fører en oversikt i Excel over selskapets salgsresultater, eller at du har noen som holder det for deg. Hvis produktene dine gjør det bra, liker du å fyre opp Excel, se på resultatene og sukke fornøyd.

Hvis produktene dine ikke fungerer som de skal, starter du refleksivt opp Excel, ser på disse resultatene og lurer på hvordan du kan fikse det som har gått galt. Uansett er dataene sannsynligvis allerede i en Excel-arbeidsbok, og det gjør det enkelt å gjøre prognoser.

Å bygge inntektsgrunnlag er ganske enkelt. Du bestemmer deg for prognoseperioden (lengden på tiden som er representert av hver observasjon i grunnlinjen) og om nødvendig bruker du Excel til å beregne den totale inntekten for hver periode i grunnlinjen. Nå er du klar til å forutsi hvor mye du vil få inn i løpet av den kommende perioden.

Det er vanskeligere å beregne kostnader, spesielt salgskostnader. Skal du inkludere kostnadene for solgte varer? Selvfølgelig vil du inkludere kostnadene til selgernes lønn og provisjoner, produktkampanjer, etterlatelser, søppel og pyntegjenstander, reiser og underholdning. Hva med indirekte kostnader?

En måte å fortsette på er å trekke disse kostnadene, for hver periode i grunnlinjen, fra inntektene for samme periode for å få et overskuddsestimat for perioden. Dette skaper en ny resultatbaselinje som du kan bruke til å forutsi neste periodes fortjeneste.

Men hva med mulighetskostnader? Når du bruker penger for å støtte salget av én produktlinje, avleder du ressurser fra en annen produktlinje. Det er en mulighetskostnad: Du hadde muligheten til å bruke de pengene til støtte for widgets i stedet for widgets, og du kan ha skapt mer inntekt og mer fortjeneste hvis du hadde brukt pengene til å selge widgets.

Ingen regnskapsregel forteller deg om du skal inkludere alternativkostnader i fortjenesteberegningene dine. Her driver du ikke med regnskap, du bruker det til å legge planene dine.

Figuren illustrerer hvordan prognoser kan hjelpe deg med å planlegge hvordan du støtter produktlinjer.

TREND-regnearkfunksjonen er basert på lineær regresjon – her ved å bruke det historiske forholdet mellom kostnader og inntekter.

Dataene om Widgets og Gidgets er ganske greie. Figuren viser den faktiske inntekten og de faktiske direkte kostnadene ved å støtte hver produktlinje i løpet av hver måned fra januar 2014 til og med juli 2016, i rad 3 til rad 33. Regnearket får resultattallene ganske enkelt ved å trekke kostnadene fra inntektene.

Rad 35 viser prognoser for august 2016. Slik får de dem:

Den anslår kostnadene for august 2016 ved bruk av eksponentiell utjevning; se kapittel 15 for mer informasjon, men for de av dere som spiller med hjemme, har utjevningskonstanten ennå ikke blitt optimert ved å minimere den gjennomsnittlige kvadratfeilen.
Kostnadsprognosene vises i celle C35 og G35.

For hvert produkt anslår den inntektene for august 2016 ved å bruke regresjonstilnærmingen (hvor du bruker et pålitelig forhold mellom salgsinntekter og en eller flere prediktorvariabler for å lage prognosen), i form av TREND-regnearkfunksjonen.
Ved å bruke informasjon om det historiske forholdet mellom kostnadene og inntektene for hvert produkt, forutsier den i celle B35 og F35 hva inntektene vil være, gitt kostnadsprognosene.

Den anslår fortjenesten for august 2016 ved å trekke prognosekostnaden fra prognoseinntekten.
Å legge til forventet fortjeneste for begge produktlinjene resulterer i en total fortjeneste for august 2016 på $11 330.

Nå, hva om du tok alternativkostnadene ved å støtte Widgets i betraktning, og i stedet hellet dem inn i Gidgets? I den neste figuren kan du se effekten av å forlate widgeter og bruke kostnadene deres – ressursene bedriften din bruker på å støtte widgets – kun til å støtte widgets.

Hva skjer når du forlater widgeter og bruker kostnadene på å støtte widgets.

Her er kolonnene A til H identiske med de i den tidligere figuren. Kolonne J til K viser effekten av å ta ressursene bort fra Widgets og bruke dem til å støtte Gidgets. Følgende trinn viser hvordan du får disse anslagene:

I celle K3 skriver du inn =C3+G3 og kopierer og limer inn formelen i cellene K4 til K33 og inn i celle K35.
Kolonne K har nå summen av de faktiske kostnadene for de to produktlinjene fra januar 2014 til og med juli 2016, pluss summen av prognosekostnadene i K35.

I celle J3, skriv inn =(F3/G3)*K3 og kopier og lim inn formelen i cellene J4 til J33.
Denne formelen får forholdet mellom inntekt og kostnad for Gidgets i januar 2014, og multipliserer den med de totale kostnadene vist i celle K3. Effekten er å bruke ett mål på bruttomargin på et høyere mål på kostnader, og estimere hva inntektene for Gidgets i så fall ville vært.

I celle J35 skriver du inn denne formelen =TREND(J3:J33,K3:K33,K35) .
Dette anslår inntektene for Gidgets i august 2016, gitt forholdet mellom prognosene for inntekter og kostnader i J3 til K33, hvis du bestemte deg for kun å støtte Gidgets.

For å få en prognose for fortjeneste for Gidgets kun i august 2016, skriv inn =J35-K35 i celle L35.

Legg merke til at summen av overskuddet i august 2016 for Widgets og Gidgets er $4,128 + $7,201 = $11,330. Men hvis du forpliktet Widget-ressursene dine til Gidgets, ville fortjenesten for august 2016 være $14 004 - $2 674 mer. I rå dollar virker det ikke som mye, men det er en økning på 24 prosent. Generasjoner av europeiske kasinoeiere har vokst seg velstående på mye mindre fordeler.

Grunnen er selvfølgelig at bruttomarginen på Gidgets er større enn på Widgets, selv om inntekten din på Widgets er nesten 30 prosent større enn på Gidgets. Å oppsummere:

• I kolonne K opptrer du som om du bare hadde forpliktet alle ressursene dine til Gidgets, fra januar 2014 til og med juli 2016. Effekten er å fjerne all støtte fra Widgets og legge den til støtten gitt til Gidgets.
• I kolonne J anslår du inntektene du ville tjent hvis du bare støttet Gidgets, ved å bruke den historiske marginen for Gidgets.
• Ved å bruke TREND-funksjonen regreserer du inntektsestimatene i J3 til J33 til kostnadene i K3 til K33, og bruker resultatet på den estimerte kostnaden i K35. Trekk K35 fra J35 for å få en prognose for fortjeneste hvis du gjenkjente alternativkostnadene dine og støttet kun gidgets.

Ved å bruke to forskjellige scenarier - Widgets med Gidgets, og Gidgets alene - gjør dette eksemplet litt vanskeligere å følge. Men det er en realistisk illustrasjon av hvordan du kan bruke den grunnleggende prognosefunksjonen TREND for å ta en informert beslutning om ressursallokering.

Selvfølgelig vil andre hensyn ha betydning for en beslutning om å flytte ressurser fra en produktlinje til en annen - en analyse av arten av feilene i prognosene (ofte kalt residualene), ugjenkallelige kostnader, mulig ombygging for å støtte økt produksjonskapasitet i en produktlinje, nødvendigheten av løpende støtte for kunder som har investert i Widgets, og så videre. Men et av kriteriene er nesten alltid økonomiske estimater, og hvis du kan forutsi økonomien med selvtillit, er du i forkant.