Använda Problemlösaren för budgetering i versaler

Hur kan ett företag använda Problemlösaren för att avgöra vilka projekt det ska utföra?

Varje år måste ett företag som Erna Lilly avgöra vilken verksamhet som utvecklas. ett företag som Microsoft, vilka program som ska utvecklas; Ett företag som Proctor & Köpan, som nya konsumentprodukter som ska utvecklas. Problemlösaren i Excel hjälpa ett företag att fatta dessa beslut.

De flesta företag vill utföra projekt som bidrar med störst nettonuvärde (NETNUVÄRDE), med begränsade resurser (oftast huvudstad och arbete). Anta att ett företag för programvaruutveckling försöker avgöra vilka av 20 programvaruprojekt som det ska utföra. NETNUVÄRDE (i miljoner dollar) som bidragit med varje projekt, liksom i huvudstad (i miljoner dollar) och antalet programmerare som behövs under vart och ett av de kommande tre åren anges i kalkylbladet Basic Model i filen Capbudget.xlsx, som visas i Bild 30-1 på nästa sida. Exempel: Project 2 ger 908 miljoner kronor. Det kräver 151 miljoner dollar under år 1, 269 miljoner under år 2 och 248 miljoner under år 3. Project 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. Under år 1 finns till exempel upp till 2,5 miljoner i versaler och 900 programmerare tillgängliga.

Företaget måste bestämma om det ska utföra varje projekt. Anta att vi inte kan utföra en del av ett programvaruprojekt. om vi tilldelar 0,5 av de nödvändiga resurserna, skulle vi till exempel ha ett fritt program som skulle ge oss intäkter på 0 KR!

Tricket i modelleringssituationer där du antingen gör eller inte gör något är att använda binära celler som ändrar . En binär ändringscell är alltid lika med 0 eller 1. Om en binär ändringscell som motsvarar ett projekt är lika med 1 gör vi projektet. Om en binär ändringscell som motsvarar ett projekt är lika med 0 gör vi inte projektet. Du kan konfigurera Problemlösaren att använda ett område med binära ändringsceller genom att lägga till ett villkor. Markera de celler som du vill använda och välj sedan Fack i listan i dialogrutan Lägg till begränsning.

Bild av bok

Med den här bakgrunden är vi redo att lösa problemet med att välja programvara. Som alltid med en problemlösarmodell börjar vi med att identifiera målcellen, cellerna som ändras och begränsningarna.

  • Målcell. Vi maximerar DE NETNUVÄRDE som genererats av valda projekt.

  • Ändra celler. Vi letar efter en binär ändringscell med 0 eller 1 för varje projekt. Jag har hittat de här cellerna i området A6:A25 (och namngett området doit). Till exempel anger en 1 i cell A6 att vi utför Project 1; en 0:a i cell C6 anger att vi inte utför Project 1.

  • Begränsningar. Vi måste se till att för varje År t(t=1, 2, 3), År t versaler som används är mindre än eller lika med År t tillgängligt, och År t arbete som används är mindre än eller lika med År t arbete tillgängligt.

Som du kan se beräknar kalkylbladet för val av projekt NETNUVÄRDE, vilket kapital som används årligen och vilka programmerare som används varje år. I cell B2 använder jag formeln PRODUKTSUMMA(doit,NETNUVÄRDE) för att beräkna det totala NETNUVÄRDE som genererats av valda projekt. (Områdesnamnet NETNUVÄRDE refererar till intervallet C6:C25.) För varje projekt med 1 i kolumn A hämtar den här formeln NETNUVÄRDE för projektet, och för varje projekt med 0 i kolumn A hämtar den här formeln inte 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 formuläret (ändrar cell)*(konstant). På liknande sätt beräknar jag vilket versaler som används varje år och arbetet som används varje år genom att kopiera från E2 till F2:J2 formeln PRODUKTSUMMA(doit,E6:E25).

Nu fyller jag i dialogrutan Parametrar för Problemlösaren enligt bild 30-2.

Bild av bok

Vårt mål är att maximera NETNUVÄRDE för valda projekt (cell B2). Våra föränderliga celler (området doit)är de binära ändringscellerna för varje projekt. Villkoret E2:J2<=E4:J4 säkerställer att under varje år är det versaler och arbete som används mindre än eller lika med versaler och arbete tillgängligt. Om jag vill lägga till villkoret som gör de ändrade cellerna binära klickar jag på Lägg till i dialogrutan Parametrar för Problemlösaren och väljer sedan Fack i listan i mitten av dialogrutan. Dialogrutan Lägg till begränsning ska visas enligt bild 30–3.

Bild av bok

Modellen är linjär eftersom målcellen beräknas som summan av termer som har formen (ändrar cell)*(konstant) och eftersom resursanvändningsbegränsningarna beräknas genom att summan av (ändra celler)*(konstanter) jämförs 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å maximalt NETNUVÄRDE på 92 930 miljoner (9,293 miljoner kr) genom att välja Projekt 2, 3, 6–10, 14–16, 19 och 20.

Ibland finns det andra begränsningar för modeller för projekturval. Anta till exempel att om vi väljer Project 3 måste vi också välja Project 4. Eftersom vår nuvarande optimala lösning Project 3 men inte Project 4 vet vi att vår nuvarande lösning inte kan vara optimal. Lös problemet genom att bara lägga till villkoret att den binära ändringscellen för Project 3 är mindre än eller lika med den binära ändringscellen för Project 4.

Du hittar det här exemplet på kalkylbladet Om 3 sedan 4 i Capbudget.xlsx filen, som visas i Bild 30-4. Cell L9 refererar till det binära värde som är relaterat till Project 3 och cell L12 till det binära värde som hör till Project 4. Om vi väljer Project 3 är L9 lika med 1 och vårt villkor tvingar L12 (den Project 4 binära) att vara lika med 1 genom att lägga till villkoret L9<=L12. Vårt villkor måste också lämna det binära värdet i den föränderliga cellen i Project 4 obegränsad om vi inte markerar Project 3. Om vi inte markerar Project 3 motsvarar L9 0 och vårt villkor tillåter att den binära Project 4 är lika med 0 eller 1, vilket är vad vi vill ha. Den nya optimala lösningen visas i Bild 30–4.

Bild av bok

En ny optimal lösning beräknas om du väljer Project 3 innebär att vi också måste välja Project 4. Anta att vi bara kan göra fyra projekt mellan projekt 1 och 10. (Se kalkylbladet Som mest 4 av P1–P10, som visas i Bild 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). Därefter lägger vi till villkoret L8<=L10, vilket gör att som mest 4 av de första 10 projekten markeras. Den nya optimala lösningen visas i Bild 30–5. NETNUVÄRDE har minskat till 9,014 miljoner kr.

Bild av bok

Linjär problemlösaren modeller där vissa eller alla ändrade celler måste vara binära eller heltal är oftast svårare att lösa än linjära modeller där alla ändringsceller tillåts vara bråktal. Därför är vi ofta nöjda med en nästan optimal lösning på ett problem med binär eller heltalsprogrammering. Om problemlösarens modell körs länge kanske du vill justera inställningen Avvikelse i dialogrutan Problemlösarens alternativ. (Se Bild 30–6.) Inställningen Avvikelse på 0,5 % innebär till exempel att Problemlösaren slutar första gången den hittar en möjlig lösning som ligger inom 0,5 procent av det teoretiska optimala målcellvärdet (det teoretiska optimala målcellvärdet är det optimala målvärdet som hittas när de binära begränsningarna och heltalsbegränsningarna utelämnas). Ofta står vi inför ett val mellan att hitta ett svar inom 10 procent av optimal på 10 minuter eller att hitta en optimal lösning på två veckor från datorn! Standardvärdet för avvikelse är 0,05 %, vilket innebär att Problemlösaren stannar när den hittar ett målcellvärde inom 0,05 procent av det teoretiska optimala målcellvärdet.

Bild av bok

  1. 1. Ett företag har nio projekt att ta hänsyn till. NetNUVÄRDE som lagts till i varje projekt och det kapital som krävs för varje projekt under de kommande två åren visas i följande tabell. (Alla tal är i miljoner.) Med 1 Project till exempel 14 miljoner i NETNUVÄRDE och kostnader på 12 miljoner dollar under år 1 och 3 miljoner under år 2. Under år 1 är 50 miljoner dollar i versaler tillgängligt för projekt och 20 miljoner dollar är tillgängligt under år 2.

NETNUVÄRDE

Utgifter för år 1

Utgifter för år 2

Project 1

14

1,2

3

Project 2

17

54

7

Project 3

17

6

6

Project 4

15

6

2

Project 5

40

30

35

Project 6

1,2

6

6

Project 7

14

48

4

Project 8

10

36

3

Project 9

1,2

18

3

  • Om vi inte kan utföra en del av ett projekt men måste utföra hela eller inget av projektet, hur kan vi då maximera NETNUVÄRDE?

  • Tänk dig att om Project 4 inte kan genomföras måste Project 5 vara genomföras. Hur kan vi maximera NETNUVÄRDE?

  • Ett publiceringsföretag försöker ta reda på vilka av 36 böcker som ska publiceras i år. Filen Pressdata.xlsx följande information om varje bok:

    • Projekterade intäkter och utvecklingskostnader (i tusentals dollar)

    • Sidor i varje bok

    • Om boken är riktad mot en målgrupp med programvaruutvecklare (anges i kolumn 1 i kolumn E)

      Ett publiceringsföretag kan publicera böcker på upp till 8 500 sidor det här året och måste publicera minst fyra böcker för programvaruutvecklare. Hur kan företaget maximera vinsten?

Den här artikeln är en Microsoft Office Excel från 2007 Data Analysis och Business Modeling av Bruce L. Bruce.

Den här klassrumsstilsboken har utvecklats av en serie presentationer av Den välkända statistikern och affärsprofessorn som nu arbetar med kreativa, praktiska program av Excel.

Behöver du mer hjälp?

Utöka dina Office-kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

×