Hur kan ett företag använda Problemlösaren till att avgöra vilka projekt det ska genomföra?
Varje år måste ett företag som Eli Lilly bestämma vilka läkemedel som ska utvecklas; ett företag som Microsoft, vilka program som ska utvecklas; ett företag som Proctor & Gamble, vilka nya konsumentprodukter som ska utvecklas. Problemlösaren i Excel kan hjälpa företag att fatta de här besluten.
Hur kan ett företag använda Problemlösaren till att avgöra vilka projekt det ska genomföra?
De flesta företag vill åta sig projekt som bidrar med det största nettonuvärdet (NPV), med begränsade resurser (vanligtvis kapital och arbetskraft). Anta att ett programvaruutvecklingsföretag försöker avgöra vilket av 20 programvaruprojekt det ska genomföra. Nettonuvärdet (i miljoner dollar) som varje projekt bidrar med, liksom kapitalet (i miljoner dollar) och antalet programmerare som behövs under vart och ett av de kommande tre åren anges på kalkylbladet Grundläggande modell i filen Capbudget.xlsx, som visas i figur 30-1 på nästa sida. Projekt 2 genererar t.ex. 908 miljoner dollar. Det kräver 151 miljoner USD under år 1, 269 miljoner USD under år 2 och 248 miljoner USD under år 3. Projekt 2 kräver 139 programmerare under år 1, 86 programmerare under år 2 och 83 programmerare under år 3. Cellerna E4:G4 visar det kapital (i miljoner dollar) som är tillgängligt under vart och ett av de tre åren, och cellerna H4:J4 anger hur många programmerare som är tillgängliga. Till exempel, under år 1 finns upp till 2.5 miljarder dollar i kapital och 900 programmerare tillgängliga.
Företaget måste bestämma om det ska genomföra varje projekt. Låt oss anta att vi inte kan genomföra en bråkdel av ett mjukvaruprojekt; Om vi allokerar 0,5 av de nödvändiga resurserna, till exempel, skulle vi ha ett icke-fungerande program som skulle ge oss $0 intäkter!
Tricket i modelleringssituationer där du antingen gör eller inte gör något är att använda binära föränderliga celler. En binär justerbar cell är alltid lika med 0 eller 1. När en binär föränderlig cell som motsvarar ett projekt är lika med 1, gör vi projektet. Om en binär justerbar cell som motsvarar ett projekt är lika med 0, gör vi inte projektet. Du konfigurerar Problemlösaren till att använda ett område med binära föränderliga celler genom att lägga till en begränsning – markera de justerbara celler du vill använda och välj sedan Lagerplats i listan i dialogrutan Lägg till villkor.
Med denna bakgrund är vi redo att lösa problemet med val av programvaruprojekt. Som alltid med en Problemlösaren-modell börjar vi med att identifiera målcellen, de justerbara cellerna och begränsningarna.
- Målcell. Vi maximerar det NPV som genereras av utvalda projekt.
- Celler som ändras. Vi letar efter en binär föränderlig cell från 0 eller 1 för varje projekt. Jag har lokaliserat de här cellerna i området A6:A25 (och döpt området till doit). En 1:a i cell A6 anger till exempel att vi åtar oss projekt 1. en 0:a i cell C6 anger att vi inte genomför projekt 1.
- Begränsningar. Vi måste se till att för varje år t (t = 1, 2, 3) är det år t-kapital som används mindre än eller lika med det år t-kapital som är tillgängligt, och det år t-arbete som används är mindre än eller lika med det år t-arbete som är tillgängligt.
Som du kan se måste vårt kalkylblad beräkna för varje urval av projekt nettonuvärdet, det kapital som används årligen och de programmerare som används varje år. I cell B2 använder jag formeln SUMPRODUCT(doit,NPV) för att beräkna den totala NPV som genereras av valda projekt. (Områdesnamnet NPV refererar till området C6:C25.) För varje projekt med 1 i kolumn A hämtar den här formeln upp projektets NETNUVÄRDE, och för varje projekt med 0 i kolumn A hämtar den här formeln inte upp projektets NETNUVÄRDE. Därför kan vi beräkna NETNUVÄRDE för alla projekt, och vår målcell är linjär eftersom den beräknas genom att summera termer som följer formen (föränderlig cell)*(konstant). På liknande sätt beräknar jag det kapital som används varje år och det arbete som används varje år genom att kopiera från E2 till F2:J2 formeln SUMPRODUCT(doit,E6:E25).
Nu fyller jag i dialogrutan Parametrar för Problemlösaren som visas i bild 30-2.
Vårt mål är att maximera nettonuvärdet för valda projekt (cell B2). Våra justerbara celler (området doit) är de binära justerbara cellerna för varje projekt. Begränsningen E2:J2<=E4:J4 säkerställer att det kapital och den arbetskraft som används under varje år är mindre än eller lika med det kapital och den tillgängliga arbetskraften. Om jag vill lägga till begränsningen som gör att de justerbara cellerna blir binära klickar jag på Lägg till i dialogrutan Parametrar för Problemlösaren och väljer sedan Lagerplats i listan i mitten av dialogrutan. Dialogrutan Lägg till begränsning bör se ut som visas i figur 30-3.
Modellen är linjär eftersom målcellen beräknas som summan av termerna som har formen (föränderlig cell)*(konstant) och eftersom resursanvändningsbegränsningarna beräknas genom att jämföra summan av (justerbara celler)*(konstanter) med en konstant.
Med dialogrutan Parametrar för Problemlösaren ifylld klickar du på Lös så visas resultatet tidigare i bild 30-1. Företaget kan få ett maximalt nettonuvärde på 9 293 miljoner dollar (9,293 miljarder dollar) genom att välja projekt 2, 3, 6–10, 14–16, 19 och 20.
Hantera andra villkor
Ibland kan modeller för projekturval ha andra begränsningar. Anta till exempel att om vi väljer projekt 3 måste vi också välja projekt 4. Eftersom vår nuvarande optimala lösning väljer projekt 3 men inte projekt 4 vet vi att vår nuvarande lösning inte kan förbli optimal. Lös problemet genom att helt enkelt lägga till begränsningen att den binära föränderliga cellen för projekt 3 är mindre än eller lika med den binära föränderliga cellen för projekt 4.
Du hittar det här exemplet i kalkylbladet Om 3 och 4 i filen Capbudget.xlsx, som visas i bild 30-4. Cell L9 refererar till det binära värdet som är relaterat till projekt 3 och cell L12 till det binära värdet som är relaterat till projekt 4. Genom att lägga till begränsningen L9<=L12, om vi väljer projekt 3, är L9 lika med 1 och vår begränsning tvingar L12 (binärfilen för projekt 4) att vara lika med 1. Vårt villkor måste också lämna det binära värdet i den föränderliga cellen i projekt 4 obegränsat om vi inte markerar projekt 3. Om vi inte markerar projekt 3 är L9 lika med 0 och vår begränsning tillåter att binärfilen för projekt 4 är lika med 0 eller 1, vilket är vad vi vill ha. Den nya optimala lösningen visas i figur 30-4.
En ny optimal lösning beräknas om valet av Projekt 3 innebär att vi också måste välja Projekt 4. Anta nu att vi bara kan göra fyra projekt bland projekt 1 till 10. (Se arbetsbladet Högst 4 av P1–P10, visas i figur 30-5.) I cell L8 beräknar vi summan av de binära värdena som är kopplade till projekt 1 till 10 med formeln SUMMA(A6:A15). Sedan lägger vi till villkoret L8<=L10, vilket säkerställer att högst 4 av de första 10 projekten är markerade. Den nya optimala lösningen visas i figur 30-5. NPV har sjunkit till 9,014 miljarder dollar.
Lösa problem med binär- och heltalsprogrammering
Linjära problemlösarmodeller där vissa eller alla föränderliga celler måste vara binära eller heltal är vanligtvis svårare att lösa än linjära modeller där alla föränderliga celler tillåts vara bråk. Av denna anledning är vi ofta nöjda med en nästan optimal lösning på ett binärt eller heltalsprogrammeringsproblem. Om din modell i Problemlösaren körs under en längre tid bör du överväga att justera toleransinställningen i dialogrutan Alternativ för Problemlösaren. (Se figur 30-6.) En toleransinställning på 0,5 % innebär till exempel att Problemlösaren stannar första gången en möjlig lösning hittas som ligger inom 0,5 procent från det teoretiskt optimala målcellsvärdet (det teoretiskt optimala målcellsvärdet är det optimala målvärdet som hittas när binär- och heltalsbegränsningarna utelämnas). Ofta står vi inför ett val mellan att hitta ett svar inom 10 procent av det optimala på 10 minuter eller att hitta en optimal lösning på två veckors datortid! Standardtoleransvärdet är 0,05 %, vilket innebär att Problemlösaren stannar när ett målcellsvärde upptäcks som ligger inom 0,05 procent från det teoretiskt optimala målcellsvärdet.
Problem
- Ett företag har nio projekt under övervägande. Nettonuvärdet som tillförs varje projekt och det kapital som krävs för varje projekt under de kommande två åren visas i följande tabell. (Alla siffror anges i miljoner.) Projekt 1 lägger t.ex. till 14 miljoner USD i NPV och kräver utgifter på 12 miljoner USD under år 1 och 3 miljoner USD under år 2. Under år 1 är 50 miljoner USD tillgängligt för projekt och 20 miljoner USD är tillgängligt under år 2.
| NETNUVÄRDE | Utgifter för år 1 | Utgifter för år 2 | |
|---|---|---|---|
| Projekt 1 | 14 | 1,2 | 3 |
| Projekt 2 | 17 | 54 | 7 |
| Projekt 3 | 17 | 6 | 6 |
| Projekt 4 | 15 | 6 | 2 |
| Projekt 5 | 40 | 30 | 35 |
| Projekt 6 | 1,2 | 6 | 6 |
| Projekt 7 | 14 | 48 | 4 |
| Projekt 8 | 10 | 36 | 3 |
| Projekt 9 | 1,2 | 18 | 3 |
- Om vi inte kan åta oss en del av ett projekt utan måste genomföra antingen hela eller inget av ett projekt, hur kan vi då maximera nettonuvärdet?
- Antag att om projekt 4 genomförs, måste projekt 5 genomföras. Hur kan vi maximera NPV?
Ett förlag försöker bestämma sig för vilken av 36 böcker de ska ge ut i år. Filen Pressdata.xlsx ger följande information om varje bok:
- Beräknade intäkter och utvecklingskostnader (i tusentals dollar)
- Sidor i varje bok
- Om boken riktar sig till en publik av mjukvaruutvecklare (anges av en 1 i kolumn E)
Ett förlag kan ge ut böcker på totalt upp till 8500 sidor i år och måste ge ut minst fyra böcker som riktar sig till mjukvaruutvecklare. Hur kan företaget maximera sin vinst?
Om artikeln
Den här artikeln har anpassats från Microsoft Office Excel 2007 Dataanalys och affärsmodellering av Wayne L. Winston.
Den här boken i klassrumsstil har utvecklats utifrån en serie presentationer av Wayne Winston, en välkänd statistiker och professor i företagsekonomi som specialiserat sig på kreativa, praktiska tillämpningar av Excel.