Hur du använder Problemlösaren för versalt budgetering

Obs!:  Vi vill kunna erbjuda dig bästa möjliga supportinnehåll så fort som möjligt och på ditt språk. Den här sidan har översatts med hjälp av automatiserad översättning och kan innehålla grammatiska fel eller andra felaktigheter. Vår avsikt är att den här informationen ska vara användbar för dig. Vill du berätta för oss om informationen är till hjälp längst ned på sidan? Här är artikeln på engelska som referens.

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

Varje år måste ett företag som Eli Lilly bestämma vilket narkotika för att utvecklas. ett företag som Microsoft, vilken programvara program för att utvecklas. ett företag som Proctor & lotteri, vilka nya consumer produkter det gäller att utveckla. Funktionen Problemlösaren i Excel kan hjälpa ett företag fatta dessa beslut.

De flesta företag vill inleda projekt som bidrar största nuvärdet (NVLF) skyldig begränsade resurser (vanligtvis versalt och arbete). Anta att ett software development företag försöker avgöra vilken 20 programvaruprojekt ska utföra. Netnuvärde (i miljoner kronor) infört av varje projekt samt kapital (i miljoner kronor) och antalet programmerare som behövs under var och en av de kommande tre åren anges på kalkylbladet Grundläggande modellen i filen Capbudget.xlsx, vilket är visas i bild 30-1 på nästa sida. Till exempel ger Project 2 908 miljoner dollar. Det krävs 151 miljoner dollar under år 1, 269 miljoner dollar under året 2 och 248 miljoner dollar under året 3. Projekt 2 kräver 139 programmerare under år 1, 86 programmerare under året 2 och 83 programmerare under året 3. Celler E4:G4 visa kapital (i miljoner kronor) tillgängliga under var och en av de tre åren och celler H4:J4 anger hur många programmerare som är tillgängliga. Till exempel är under året 1 upp till 2,5 miljarder dollar i kapital och 900 programmerare tillgängliga.

Företaget måste bestämma om den ska utföra varje projekt. Låt oss anta att vi inte kan utföra en del av ett projekt för programvara. Om vi fördelar 0,5 nödvändiga resurser, till exempel skulle vi har ett ledig program som tar oss 0 kr intäkt!

Lura i modeling situationer där du antingen eller inte göra något är att använda binära justerbara celler. En binär ändra cell alltid är lika med 0 eller 1. Vi gör projektet när ett binärt tal ändras cell som hör till ett projekt är lika med 1. Om ett binärt tal ändras cell som hör till ett projekt är lika med 0, gör inte vi projektet. Konfigurera Problemlösaren att använda ett cellområde binärt justerbara celler genom att lägga till ett villkor, markera justerbara celler som du vill använda och välj sedan lagerplats i listan i dialogrutan Lägg till begränsning.

Bild av bok
Bild-30-1 Data vi använder med Problemlösaren för att bestämma vilka projekt att genomföra

Med den här bakgrunden är vi redo att hjälper programvara project markeringen. Som alltid med en problemlösarmodell börjar vi med att identifiera vår Målcell, justerbara celler och begränsningar.

  • Målcellen. Vi maximera Netnuvärde som genereras av markerade projekt.

  • Justerbara celler. Vi leta efter en 0 eller 1 binära justerbara celler för varje projekt. Jag har dessa celler och finns i området A6:A25 (namngivna område doit). Till exempel anger 1 i cell A6 Vi åtar sig projekt 1. värdet 0 visas i cell C6 anger att vi inte genomföra projekt 1.

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

Som du kan se måste våra kalkylblad beräkna för ett urval av projekt i Netnuvärde, kapital används årligen och programmerare används varje år. I cell B2 använda jag formeln SUMPRODUCT(doit,NPV) för att beräkna totala Netnuvärde genereras av markerade projekt. (Intervallnamnet Netnuvärde refererar till området C6:C25.) Den här formeln tar upp Netnuvärde försenas för varje projekt med 1 i kolumn A och för varje projekt med 0 i kolumn A med den här formeln hämtar inte upp Netnuvärde försenas. Därför vi kunna beräkna Netnuvärde över alla projekt och vår Målcell är linjär eftersom den beräknas genom att summera termer som följer formuläret (ändra cell)*(konstant). På liknande sätt beräkna jag kapital 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).

Jag nu Fyll i dialogrutan Parametrar för Problemlösaren enligt följande bild 30-2.

Bild av bok
Bild-30-2 parametrar för Problemlösaren dialogrutan rutan Konfigurera för project markeringen modell

Vårt mål är att maximera Netnuvärde med markerade projekt (cell B2). Vår justerbara celler (område med namnet doit) är binärt justerbara celler för varje projekt. Villkoret E2:J2 < = E4:J4 säkerställer att under varje år kapital och arbete som används är mindre än eller lika med kapital och arbete som är tillgängliga. Om du vill lägga till den begränsning som gör justerbara celler binära jag klicka på Lägg till i dialogrutan Parametrar för Problemlösaren och välj lagerplats i listan mitt i dialogrutan. Dialogrutan Lägg till begränsning ska se ut som i bild 30-3.

Bild av bok
Bild-30-3 Använd intervallet alternativ i dialogrutan Lägg till begränsning för att konfigurera binärt justerbara celler, celler som visas antingen 0 eller 1.

Vår modellen är linjära eftersom målcellen beräknas som summan av termer som kan formuläret (ändra cell)*(konstant) och eftersom begränsningar för Resursanvändning beräknas genom att jämföra summan av (ändra cells)*(constants) med en konstant.

Med dialogrutan Parametrar för Problemlösaren fyllt i, klicka på Lös och vi har resultaten visas tidigare i bild 30-1. Företaget kan få en maximalt Netnuvärde av 9,293 miljoner dollar ($9.293 miljarder) genom att välja projekt 2, 3, 6 – 10, 14 – 16, 19 och 20.

Val av projekt modeller har ibland andra villkor. Anta till exempel att om vi väljer Project 3 vi också välja Project 4. Eftersom våra aktuella optimal lösning väljer Project 3 men inte Project 4, vet vi att våra aktuella lösning inte kan vara optimala. Om du vill lösa det här problemet, lägger du till den begränsning som binära justerbara celler för Project 3 är mindre än eller lika med binära justerbara celler för Project 4.

Du hittar det här exemplet i kalkylbladet om 3 sedan 4 i filen Capbudget.xlsx som visas i bild 30-4. Cell L9 refererar till ett binärt värde avseende Project 3 och cell L12 till det binära värdet avseende Project 4. Genom att lägga till villkoret L9 < = L12, om vi väljer Project 3 L9 lika med 1 och vår begränsning tvingar fram L12 (Project 4-binär) lika med 1. Vår villkor måste också lämna det binära värdet i Project 4 obegränsad om vi inte väljer Project 3 justerbara celler. Om vi inte väljer Project 3 L9 är lika med 0 och vår villkor kan Project 4 binära lika med 0 eller 1, vilket är vad vi vill. Den nya optimala lösningen visas i bild 30-4.

Bild av bok
Bild-30-4 nya optimal lösning för annars Project 3 sedan Project 4

En ny optimal lösning beräknas om väljer Project 3 innebär vi måste också markera Project 4. Anta att vi kan göra endast fyra projekt bland projekt 1 till 10. (Se At flest 4 av P1 – P10 kalkylbladet visas i bild 30-5). I cell L8 beräkna vi summan av binära värden som är associerad med projekt 1 till 10 med formeln SUM(A6:A15). Sedan vi lägga till villkoret L8 < = L10, som säkerställer att högst 4 av 10 första projekt markeras. Den nya optimala lösningen visas i bild 30-5. Netnuvärde har släppts till 9.014 miljarder dollar.

Bild av bok
Bild-30-5 Optimal lösning när vi kan välja endast 4 av 10 projekt

Linjär problemlösarmodeller i vilka vissa eller alla justerbara celler krävs för att vara binär eller heltal är vanligtvis svårare att lösa än linjära modeller där alla justerbara celler ska kunna vara bråktal. Därför kan är vi ofta nöjd med en nära optimal lösning till ett binärt tal eller heltal programming problem. Om Problemlösaren modellen körs för lång tid kan du vill du justera inställningarna i dialogrutan Alternativ för Problemlösaren. (Se bild 30-6). Till exempel 0,5% avvikelse inställningen innebär att Problemlösaren stoppar första gången den hittar en möjlig lösning som är inom 0,5 procent av en teoretiska optimala Målcell (cellvärdet teoretiska optimala målet är optimala målvärde hittades när den binärt och heltal villkor utelämnas). Vi är ofta inför ett val mellan söka efter ett svar inom 10 procent av optimala i 10 minuter eller hitta en optimal lösning i två veckor tidsperiod dator! Feltolerans standardvärdet är 0,05%, vilket betyder att Problemlösaren stannar när den hittar ett målvärde cell inom 0,05 procent av en teoretiska optimala Målcell.

Bild av bok
Bild-30-6 justera alternativet feltolerans

  1. 1. ett företag har nio projekt i fråga. Netnuvärde har lagts till av varje projekt och kapital som krävs för varje projekt under de kommande två åren visas i följande tabell. (Alla tal finns i flera miljoner.) Till exempel Project 1 läggs 14 miljoner dollar i Netnuvärde och Kräv utgifter över 12 miljoner dollar under år 1 och 3 miljoner dollar under året 2. Under år 1 50 miljoner dollar i kapital är tillgängligt för projekt och 20 miljoner dollar är tillgänglig under året 2.

NETNUVÄRDE

År 1 utgifter

År 2 utgifter

Projekt 1

14

12

3

Projekt 2

17

54

7

Project 3

17

6

6

Project 4

15

6

2

Project 5

40

30

35

Project 6

12

6

6

Project 7

14

48

4

Project 8

10

36

3

Project 9

12

18

3

  • Om vi kan inte utföra en del av ett projekt men måste utföra alla eller inga av ett projekt, hur kan vi maximera Netnuvärde?

  • Anta att om Project 4 påbörjas, Project 5 måste göras. Hur kan vi maximera Netnuvärde?

  • Publicering företag försöker avgöra vilket av 36 böcker som den ska publicera år. Filen Pressdata.xlsx ger följande information om varje bok:

    • Planerade intäkt och utveckling kostnader (i tusentals kronor)

    • Sidor i varje bok

    • Om boken riktar mot en målgrupp av programvaruutvecklare (indikeras av värdet 1 i kolumn E)

      Publicering företag kan publicera böcker summering upp till 8500 sidor år och måste publicera minst fyra böcker som är anpassat programvaruutvecklare. Hur kan företaget maximera sin vinst?

I den här artikeln bygger på Microsoft Office Excel 2007 dataanalys och Business Modeling av Wayne L. Winston.

Den här boken utvecklats från en serie Föreläsningar av Wayne Winston, en välkänd statistiker och företag lärare som specialiserat på kreativa, praktiska tillämpningar av Excel.

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

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×