Brug af Problemløser til kapital budgettering

Hvordan kan en virksomhed bruge Problemløser til at afgøre, hvilke projekter det skal udføre?

Hvert år er et firma, der som Eli Lilly har brug for at afgøre, hvilke stoffer der skal udvikles et firma som Microsoft, som softwareprogrammer kan udvikle et firma som Proctor & Gamble, som nye forbrugerprodukter, der skal udvikles. Funktionen Problemløser i Excel kan hjælpe en virksomhed med at træffe disse beslutninger.

De fleste virksomheder ønsker at iværksætte projekter, der bidrager til den største nettonutidsværdi (NUTIDsværdi), forudsat at der er begrænset ressourcer (sædvanligvis kapital og arbejdskraft). Lad os sige, at et softwareudviklings firma forsøger at afgøre, hvilke 20 software projekter det skal udføre. NUTIDSVÆRDI (i millioner af kroner), som hvert projekt bidrager til, samt det store (i millioner af kroner), og det antal programmører, der kræves i hvert af de næste tre år, er angivet i det grundlæggende model regneark i filen Capbudget. xlsx, der vises i figur 30-1 på den næste side. For eksempel giver Project 2 udbytter $908.000.000. Det kræver $151.000.000 i år 1, $269.000.000 i år 2 og $248.000.000 i år 3. Project 2 kræver 139-programmører i år 1, 86-programmører i år 2 og 83 programmører i år 3. Celler E4: G4 viser den store (i millioner af kroner), der er tilgængelige i hvert af de tre år, og cellerne H4: J4 angiver, hvor mange programmører der er tilgængelige. For eksempel under år 1 op til $2.500.000.000 i store og 900-programmører er tilgængelige.

Virksomheden skal beslutte, om det skal udføre hvert projekt. Lad os antage, at vi ikke kan udføre en brøkdel af et software projekt. Hvis vi tildeler 0,5 af de nødvendige ressourcer, ville vi for eksempel have et ikke-arbejdsprogram, der $0 kan føre til en-indtægt!

Stikket i model situationer, hvor du enten gør eller ikke gør noget, er at bruge binære justerbare celler. En binær ændrings celle er altid lig med 0 eller 1. Når en binær ændring af en celle, der svarer til et projekt, er lig med 1, gør vi projektet. Hvis en binær ændrings celle, der svarer til et projekt, er lig med 0, gør vi ikke projektet. Du konfigurerer Problemløser til at bruge et område af binær redigering af celler ved at tilføje en begrænsning – Markér de ændrede celler, du vil bruge, og vælg derefter bin på listen i dialogboksen Tilføj betingelse.

Billede af bog

Med denne baggrund er vi klar til at løse problemet med valg af software projekt. Som altid med en Problemløser model starter vi med at identificere vores målcelle, de ændrede celler og begrænsningerne.

  • Målcelle. Vi maksimerer den nutidsværdi, der er oprettet af udvalgte projekter.

  • Ændre celler.Vi søger efter en binær ændring af en celle med 0 eller 1 for hvert projekt. Jeg har placeret disse celler i området A6: A25 (og navngivne område doit). For eksempel angiver en 1 i celle A6, at vi påtager sig projekt 1; et 0 i celle C6 angiver, at vi ikke udfører projekt 1.

  • Begr.Vi er nødt til at sikre, at det for hvert år t (t = 1, 2, 3), års store-kapital forbrug er mindre end eller lig med års t-kapital, og at forbrug er mindre end eller lig med år tarbejde tilgængelig .

Som du kan se, skal regnearket udregne for enhver udvælgelse af projekter, den kapital, der blev brugt årligt, og de programmører, der blev brugt hvert år. I celle B2 bruger jeg formlen SUMPRODUKT (doit, nutidsværdi) til at beregne den samlede nutidsværdi, der er oprettet af udvalgte projekter. (Områdenavnet nutidsværdi henviser til området C6: C25.) For hvert projekt, der har 1 i kolonne A, vælger denne formel nutidens nutidsværdi for projektet, og for hvert projekt med 0 i kolonne A afhenter denne formel ikke nutidens nutidsværdi for projektet. Vi kan derfor beregne nutidsværdien af alle projekter, og vores målcelle er lineær, fordi den beregnes ved at summere ord, der følger efter formularen (ændring af celle) * (konstant). På samme måde beregner jeg den kapital, der blev brugt hvert år, og det arbejde, der blev brugt hvert år, ved at kopiere fra E2 til F2: J2 formel SUMPRODUKT (doit, E6: E25).

Jeg vil nu udfylde dialogboksen parametre i Problemløser, som vist i figur 30-2.

Billede af bog

Vores mål er at maksimere nutidsværdi for udvalgte projekter (celle B2). Vores ændrede celler (området med navnet doit) er de binære justerbare celler for hvert projekt. Begrænsningen E2: J2<= E4: J4 sikrer, at den anvendte kapital og arbejdstid er mindre end eller lig med den disponible kapital og det disponible arbejde. Hvis du vil tilføje den betingelse, der gør de ændrede celler binære, skal du klikke på Tilføj i dialogboksen parametre i Problemløser og derefter vælge placering på listen midt i dialogboksen. Dialogboksen Tilføj begrænsning vises, som vist i figur 30-3.

Billede af bog

Vores model er lineær, fordi målcellen beregnes som summen af ord, der har formularen (ændring af celle) * (konstant) , og fordi begrænsningerne for ressourceforbrug beregnes ved at sammenligne summen af (ændrede celler) * (konstant) med en konstant.

Når dialogboksen Problemløserparametre er udfyldt, skal du klikke på løs, og vi har de resultater, der vises tidligere i figur 30-1. Virksomheden kan få en maksimal nutidsværdi på $9.293.000.000 ($9.293.000.000) ved at vælge projekter 2, 3, 6 – 10, 14 – 16, 19 og 20.

Nogle gange har Project-udvælgelses modeller andre begrænsninger. Antag f. eks., at hvis vi vælger Project 3, skal vi også vælge Project 4. Da vores aktuelle optimale løsning vælger Project 3, men ikke Project 4, ved vi, at vores aktuelle løsning ikke kan være optimal. Hvis du vil løse dette problem, skal du blot tilføje den betingelse, at den binære ændring af cellen for projekt 3 er mindre end eller lig med den binære ændring af cellen for projekt 4.

Du kan finde dette eksempel på regnearket Hvis 3 og 4 i filen Capbudget. xlsx, som vises i figur 30-4. Celle L9 refererer til den binære værdi, der er relateret til Project 3, og celle L12 til den binære værdi, der er relateret til projekt 4. Ved at tilføje betingelsen L9<= L12, hvis vi vælger projekt 3, L9 er lig med 1, og vores betingelse tvinger L12 (det binære projekt-4) til at være lig med 1. Vores betingelse skal også lade den binære værdi i den ændrede celle i Project 4 være ubegrænset, hvis vi ikke vælger projekt 3. Hvis du ikke vælger projekt 3, L9 er lig med 0, og vores betingelse gør det muligt for Project 4-binært at være lig med 0 eller 1, hvilket vi har lyst til. Den nye optimale løsning vises i figur 30-4.

Billede af bog

Der beregnes en ny optimal løsning, hvis du vælger projekt 3, skal du også vælge projekt 4. Antag nu, at vi kun kan udføre fire projekter mellem projekterne fra 1 til og med 10. (Se det højst 4 af P1 – P10 -regneark, der vises i figur 30-5). I celle L8 beregner vi summen af de binære værdier, der er knyttet til projekterne fra 1 til og med 10 med formel summen (A6: A15). Derefter tilføjer vi begrænsningen L8<= L10, hvilket sikrer, at der højst er valgt 4 i de første 10 projekter. Den nye optimale løsning vises i figur 30-5. NUTIDSVÆRDI er faldet til $9.014.000.000.

Billede af bog

Lineære Problemløser modeller, hvor nogle eller alle ændrede celler skal være binære eller heltal, er normalt sværere at løse end lineære modeller, hvor alle ændrede celler må være brøker. Derfor er vi ofte tilfredse med en næsten-optimal løsning til et programmerings problem med binær eller heltal. Hvis din Problemløser model kører i lang tid, kan det være en god ide at overveje at justere indstillingen for tolerance i dialogboksen Problemløserindstillinger. Se fig. 30-6. For eksempel betyder en tolerance for 0,5%, at Problemløser stopper første gang, den finder en gennemførlig løsning, der er inden for 0,5 procent af den teoretiske optimale destinationscelle værdi (den teoretiske, optimale destinations værdi er den optimale destinations værdi, der blev fundet, når binære og heltalsbegrænsninger udelades). Vi har ofte en mulighed for at finde et svar inden for 10% af optimalt i 10 minutter eller at finde en optimal løsning på to ugers tid! Standard tolerance værdien er 0,05%, hvilket betyder, at Problemløser stopper, når den finder en målcelle værdi i 0,05 procent af den teoretiske optimale målcelle værdi.

Billede af bog

  1. 1. et firma har ni projekter under overvejelse. NUTIDSVÆRDI, der er tilføjet af hvert projekt, og den kapital, der kræves af hvert projekt i løbet af de næste to år, vises i den følgende tabel. (Alle tal er i millioner). For eksempel vil projekt 1 tilføje $14.000.000 i nutidsværdi og kræve udgifter til $12.000.000 i år 1 og $3.000.000 i år 2. I år 1 er $50.000.000 i en kapital tilgængelig for projekter, og $20.000.000 er tilgængelig i år 2.

NUTIDSVÆRDI

År 1-udgifter

År med 2 udgifter

Projekt 1

14

12

3

Project 2

17

54

7

Projekt 3

17

6

6

Projekt 4

15

6

2

Projekt 5

40

mindst

35

Project 6

12

6

6

Projekt 7

14

48

4

Projekt 8

10

36

3

Projekt 9

12

18

3

  • Hvis vi ikke kan udføre en brøkdel af et projekt, men skal udføre enten alle eller ingen af et projekt, hvordan kan vi maksimere nutidsværdien?

  • Antag, at hvis Project 4 er gennemført, skal Project 5 udføres. Hvordan kan vi maksimere nutidsværdien?

  • Et udgivelses firma forsøger at afgøre, hvilke 36-bøger det skal udgive i år. Filen pressData. xlsx indeholder følgende oplysninger om hver enkelt bog:

    • Forventede udgifter til omsætning og udvikling (i tusind kroner)

    • Sider i hver bog

    • Om bogen er rettet mod en målgruppe af softwareudviklere (angivet med 1 i kolonne E)

      En publicerings virksomhed kan publicere bøger, der er samlet op til 8500 sider i år og skal publicere mindst fire bøger, der er rettet mod softwareudviklere. Hvordan kan virksomheden maksimere sin indtjening?

Denne artikel er baseret på Microsoft Office Excel 2007-data analyse og virksomheds modellering af Wayne L. Winston.

Denne klasse af klasseværelset blev udviklet af en række præsentationer af Wayne Winston, som er en velkendt statistician-og Business professor, der specializes i kreative, praktiske programmer i Excel.

Bemærk!:  Denne side er oversat ved hjælp af automatisering og kan indeholde grammatiske fejl og unøjagtigheder. Det er vores hensigt, at dette indhold skal være nyttigt for dig. Var disse oplysninger nyttige? Her er artiklen på engelsk, så du kan sammenligne.

Udvid dine Office-færdigheder
Gå på opdagelse i kurser
Få nye funktioner først
Bliv Office Insider

Var disse oplysninger nyttige?

Tak for din feedback!

Tak for din feedback! Det lyder, som om det vil kunne hjælpe, hvis du bliver sat i forbindelse med en af vores Office-supportteknikere.

×