Effektivisera din avtalsuppföljning med excel

Jag har tidigare skrivit om att det är viktigt med avtalsuppföljning, både extern (där du kontrollerar att leverantörer uppfyller villkor i avtal) och intern (där du till exempel kontrollerar att rätt produkter beställs från avtalet). I det här inlägget tänkte jag visa ett exempel på hur vi jobbar med intern avtalsuppföljning.

Avtalsuppföljning är ju så viktigt, men det är också viktigt att kunna prioritera. Du hinner inte följa upp alla avtal i detalj, men samtidigt så vill du försöka ha en ganska bra bild över vilka avtal som du faktiskt behöver följa upp i detalj. För att kunna prioritera mellan vilka avtal som är värda att lägga tid på kan det vara bra att snabbt få en bild över hur situationen i ett avtalsområde ser ut. Detta skulle man kunna få genom ett dedikerat inköpssystem, men jag skulle vilja säga att du med ganska enkla medel också kan få det genom att använda Excel.

Jag har tidigare begärt ut statistik från en leverantör avseende våra inköp fördelade på bland annat artikel- och kundnummernivå. Statistik från olika leverantörer kan se lite olika ut, så i det här inlägget tänkte jag visa lite tips för hur man kan bearbeta generisk statistik från leverantörer till att bli mer användbar i syfte att följa upp hur verksamheterna internt faktiskt följer avtalsvillkoren. (Alla siffror som syns i bilderna är framslumpade värden mellan 1 – 10 000 kronor, så det går inte att från det här inlägget utläsa något om vad vår faktiska avtalstrohet är inom avtalsområdet som avses.)


I bilden ovan ser ni ett exempel på hur statistik från en leverantör skulle kunna se ut när du erhåller den. Du ser vilken avdelning som beställt, du ser hur mycket de beställt av enskilda artiklar, det finns en summering per avdelning (gult fält) och det framgår vad styckpriset är för olika produkter.

 När jag bearbetar statistik brukar jag använda pivottabeller, och för att kunna använda det är det inte bra att det finns summeringsrader inne i databasen – det kommer bli fel. Jag brukar därför börja med att plocka bort alla summeringsrader. I det här fallet var det lätt då dessa var gulmarkerade. Genom att markera cell E5 där det står ”Artikel”, klicka på ”Data -> Filtrering” och välja ”filtrera efter färg” så kan du få fram endast de rader som är gulmarkerade. Ta sedan bort dessa och plocka bort filtret. Se nedan:

 Du har nu ett korrekt underlag som går att använda i en Pivottabell (se bara till att sätta rubriker på varje kolumn, som du ser i sista bilden ovan så finns det inga rubriker i kolumnen med kommunnamn till exempel och då kommer Pivottabellen inte att fungera.)

Nästa steg är att försöka göra statistiken lite mer användbar. Med dataunderlaget ovan kan vi följa upp vilka artiklar vi har köpt mest i kommunen och vilka avdelningar som har köpt det. Det som också är av intresse är vilka artiklar som ingår i det upphandlade sortimentet (”utvärderade artiklar”) och vilka artiklar som ingår i leverantörens övriga sortiment. Helst vill man försöka styra verksamheten så långt det är möjligt till de utvärderade artiklarna, även om det ibland behöver köpas även andra produkter.

Vilka artiklar som är utvärderade framgår vanligtvis av avtalet, men det kan vara lite tidskrävande att lägga in detta manuellt i en databas i excel (databasen ovan består av cirka 2 000 rader). För att lösa det brukar jag göra en lista med vilka artiklar som är utvärderade artiklar (artikelnummer) och sen använda funktionen ”LETARAD”. Se nedan:

Jag har i exemplet skapat en ny flik med en lista på alla utvärderade artiklar och deras artikelnummer. Denna lista kan jag nu jämföra med artikelnumren i statistiken för att kunna kategorisera varje köp utifrån om det är en ”Utvärderad artikel” som har köpts eller en ”Övrig artikel” (i bilden ovan är ”Övriga artiklar” de som det står ”#SAKNAS” på, då dessa saknas i listan med utvärderade artiklar.

Jag skapade en ny kolumn som jag döpte till ”Utvärderad artikel?”. I denna skrev jag sen ” =LETARAD(E6;’Utvärderade artiklar’!$A$1:$B$281;2;FALSKT)”. Jag ska inte i detalj gå igenom hur man skriver en LETARAD-funktion, men det funktionen gör är i alla fall att kontrollera det artikelnummer som står i kolumnen bredvid (”E6”) mot matrisen som innehåller artikelnummer och som anger om det är en utvärderad artikel eller inte. Om artikeln finns med skriver formeln ”Utvärderad artikel”, om artikeln inte finns med anges ”#SAKNAS” eftersom den gör just det – saknas i matrisen. Framöver har jag ändrat benämningen på alla ”#SAKNAS” till ”Övrigt sortiment” för att det ska bli lättare att följa.

Nästa steg är att skapa en pivottabell för att kunna använda ditt datamaterial till något nyttigt. Det görs genom att ställa sig i översta vänstra cellen i datamaterialet och klicka på ”Infoga -> Pivottabell”, se bild nedan:

I pivottabellen kan du snabbt filtrera datan. Det första du gör är nästan alltid att lägga in den kolumn som innehåller värdena (kostnaden för inköpta artiklar, till exempel) i fältet ”Värden” nere till höger. Sen kan du leka runt lite genom att filtrera på olika sätt och se om du kommer fram till något användbart. Filtreringen görs genom att du flyttar kolumnnamn till antingen fältet ”Filter” (om du vill kunna filtrera på de värden som kolumnen innehåller), ”Rader” eller ”Kolumner” (om du vill få informationen visualiserad direkt i tabellen). Jag ger några exempel nedan på rapporter som kan vara bra att ta fram för att få en snabb bild av hur ett avtal fungerar.

Här kan man snabbt se hur stor andel av de inköpta artiklarna som är från avtalssortimentet och hur stor del som inte är det. Nu är detta förstås inte våra riktiga siffror eftersom jag slumpade inköpsvolymerna tidigare, men om man inte är nöjd med en ”produkttrohet” på 42 % så kan man gå vidare och göra fler kontroller.

Genom att lägga in ett filter på ”Avdelning” så kan jag kontrollera produkttroheten avdelning för avdelning istället. I det här exemplet verkar ”Lagunen” ha en högre avtalstrohet än genomsnittet. Vad beror det på? Vad har de lyckats med? Vad kan andra avdelningar lära sig av dem? På samma sätt kan man filtrera fram avdelningar som inte har lika hög produkttrohet. Varför handlar dessa avdelningar mycket utanför det upphandlade sortimentet? Vet man inte att det finns ett upphandlat sortiment, eller kan det upphandlade sortimentet inte tillgodose verksamhetens behov? Genom att informera dessa avdelningar löpande om vikten av att handla från det upphandlade sortimentet så märker vi att de inte bara blir mer produkttrogna i det avtalet du informerar om för tillfället, de blir också mer benägna att handla på rätt sätt på andra avtalsområden. Man behöver alltså inte lägga mycket tid på att följa upp och informera om alla avtal, utan det räcker med att förklara principen för hur man ska tänka inom ett avtalsområde för att verksamheterna sen ska börja tillämpa samma tankesätt även på andra avtalsområde.

 

En sista uppföljningsmetod jag tänkte visa är när du sorterar ut bara de artiklar som är från det övriga sortimentet, fördelat på artikelnamn och avdelning. Då kan man sortera fram vilka artiklar från det övriga sortimentet vi köpt mest (Varför köper verksamheterna mycket av den produkten? Finns det ett alternativ bland det utvärderade sortimentet? Möter alternativet verksamhetens behov?) och vilka avdelningar som står för dessa inköp. Då kan man dels fråga de avdelningar som köper mest av produkten varför de väljer den framför ett utvärderat alternativ, eller (om det inte finns ett utvärderat alternativ, som i fallet ovan) om det är en artikel de vanligtvis köper mycket och som vi därför behöver försöka få med på prislistan i nästkommande upphandling.

Genom att använda excel kan du snabbt identifiera vilka avtal du behöver lägga lite extra tid på att följa upp, och genom att göra det förbättrar du hela tiden kunskapen i verksamheterna om viktiga saker som varför man ska hålla sig till det upphandlade sortimentet. Du lär dig också mycket (som du förstås dokumenterar) som kan vara till stöd när du ska få till ett ännu bättre avtal i samband med nästa upphandling inom samma avtalsområde.

Att göra en översiktlig granskning av ett avtalsområde tar mig runt 30 minuter om jag bara har lätt tillgång till statistiken, och ska man sen gå in djupare på ett avtal så tar det kanske ett par timmar till att informera och diskutera med verksamhetsföreträdarna. Men det är tid som jag har mångdubbelt tillbaka i samband med våra upphandlingsprocesser, och det är också tid som jag vet är väl investerad när det gäller att utveckla kommunens inköpsarbete.

Avslutningsvis tänkte jag dela med mig av en lathund jag gjorde i samband med att jag höll en internutbildning i excel för ett tag sedan. Hoppas den kan vara till nytta för någon!

lathund-excel

Lämna ett svar

E-postadressen publiceras inte. Obligatoriska fält är märkta *