Hvordan kan en virksomhed bruge Problemløser til at afgøre, hvilke projekter den skal udføre?
Hvert år skal et firma som Eli Lilly bestemme, hvilke lægemidler der skal udvikles; et firma som Microsoft, hvilke softwareprogrammer der skal udvikles; en virksomhed som Proctor & Gamble, hvilke nye forbrugerprodukter der skal udvikles. Funktionen Problemløser i Excel kan hjælpe en virksomhed med at træffe disse beslutninger.
Hvordan kan en virksomhed bruge Problemløser til at afgøre, hvilke projekter den skal udføre?
De fleste virksomheder ønsker at gennemføre projekter, der bidrager med den største nettonutidsværdi (NUTIDSVÆRDI), underlagt begrænsede ressourcer (normalt kapital og arbejdskraft). Lad os sige, at et softwareudviklingsfirma forsøger at finde ud af, hvilket af 20 softwareprojekter det skal påtage sig. NUTIDSVÆRDIEN (i millioner af dollars), som hvert projekt har bidraget med, samt kapitalen (i millioner af dollars) og antallet af programmører, der er nødvendige i hvert af de næste tre år, er angivet på basismodelregnearket i filen Capbudget.xlsx, som er vist i figur 30-1 på næste side. Projekt 2 giver f.eks. $ 908 millioner. Det kræver 151 millioner USD i år 1, 269 millioner USD i år 2 og 248 millioner USD i år 3. Projekt 2 kræver 139 programmører i år 1, 86 programmører i år 2 og 83 programmører i år 3. Celle E4:G4 viser den kapital, der er til rådighed (i millioner af dollars) i hvert af de tre år, og cellerne H4:J4 angiver, hvor mange programmører der er tilgængelige. For eksempel er der i løbet af år 1 op til 2,5 milliarder dollars i kapital og 900 programmører til rådighed.
Virksomheden skal beslutte, om den skal påtage sig hvert enkelt projekt. Lad os antage, at vi ikke kan påtage os en brøkdel af et softwareprojekt; Hvis vi f.eks. allokerer 0,5 af de nødvendige ressourcer, ville vi have et ikke-fungerende program, der ville indbringe os $0 i omsætning!
Tricket til at modellere situationer, hvor du enten gør eller ikke gør noget, er at bruge binære ændringsceller. En binær justerbar celle er altid lig med 0 eller 1. Når en binær justerbar celle, der svarer til et projekt, er lig med 1, udfører vi projektet. Hvis en binær justerbar celle, der svarer til et projekt, er lig med 0, udfører vi ikke projektet. Du konfigurerer Problemløser til at bruge et område af binære justerbare celler ved at tilføje en begrænsning – vælg de justerbare celler, du vil bruge, og vælg derefter Bin på listen i dialogboksen Tilføj begrænsning.
Med denne baggrund er vi klar til at løse problemet med valg af softwareprojekter. Som altid med en Problemløsermodel begynder vi med at identificere vores destinationscelle, de skiftende celler og begrænsningerne.
- Målcelle. Vi maksimerer NUTIDSVÆRDIEN, der genereres af udvalgte projekter.
- Justerbare celler. Vi leder efter en 0 eller 1 binær ændringscelle for hvert projekt. Jeg har fundet disse celler i området A6:A25 (og navngivet området doit). Eksempelvis angiver et 1 i celle A6, at vi påtager os Projekt 1. Et 0 i celle C6 angiver, at vi ikke gennemfører Projekt 1.
- Begrænsninger. Vi er nødt til at sikre, at for hvert år t (t=1, 2, 3) er år t brugt kapital mindre end eller lig med år t kapital til rådighed, og år t brugt arbejdskraft er mindre end eller lig med år t arbejdskraft til rådighed.
Som du kan se, skal vores regneark for ethvert udvalg af projekter beregne NUTIDSVÆRDIEN, den kapital, der bruges årligt, og de programmører, der bruges hvert år. I celle B2 bruger jeg formlen SUMPRODUKT(gør,NUTIDSVÆRDI) til at beregne den samlede NUTIDSVÆRDI, der er genereret af markerede projekter. (Områdenavnet NUTIDSVÆRDI henviser til området C6:C25). For hvert projekt med 1 i kolonne A opfanger denne formel projektets NUTIDSVÆRDI, og for hvert projekt med et 0 i kolonne A henter denne formel ikke projektets NUTIDSVÆRDI. Vi kan derfor beregne NUTIDSVÆRDI for alle projekter, og destinationscellen er lineær, fordi den beregnes ved at opsummere udtryk, der følger formen (skiftende celle)*(konstant). På samme måde beregner jeg den kapital, der bruges hvert år, og den arbejdskraft, der bruges hvert år, ved at kopiere fra E2 til F2:J2 formlen SUMPRODUCT(doit,E6:E25).
Jeg udfylder nu dialogboksen Parametre til Problemløser som vist i figur 30-2.
Vores mål er at maksimere NUTIDSVÆRDI for udvalgte projekter (celle B2). Vores justerbare celler (området med navnet doit) er de binære ændringsceller for hvert projekt. Betingelsen E2:J2<=E4:J4 sikrer, at den anvendte kapital og arbejdskraft i løbet af hvert år er mindre end eller lig med den kapital og arbejdskraft, der er til rådighed. Hvis jeg vil tilføje den betingelse, der gør de justerbare celler binære, klikker jeg på Tilføj i dialogboksen Parametre til Problemløser og vælger derefter Papirkurv på listen midt i dialogboksen. Dialogboksen Tilføj begrænsning bør blive vist som vist i figur 30-3.
Vores model er lineær, fordi destinationscellen beregnes som summen af udtryk, der har formen (skiftende celle)*(konstant), og fordi begrænsningerne i ressourceforbruget beregnes ved at sammenligne summen af (skiftende celler)*(konstanter) med en konstant.
Klik på Problemløs, mens dialogboksen Problemløserparametre er udfyldt, og så har vi de resultater, der er vist tidligere i figur 30-1. Virksomheden kan opnå en maksimal NPV på $9,293 millioner ($9,293 milliarder) ved at vælge projekt 2, 3, 6–10, 14–16, 19 og 20.
Håndtering af andre begrænsninger
Nogle gange har projektudvælgelsesmodeller andre begrænsninger. Antag f.eks., at hvis vi vælger Projekt 3, skal vi også vælge Projekt 4. Da vores nuværende optimale løsning vælger projekt 3, men ikke projekt 4, ved vi, at vores nuværende løsning ikke kan forblive optimal. Hvis du vil løse dette problem, skal du blot tilføje betingelsen om, at den binære ændringscelle for Projekt 3 er mindre end eller lig med den binære ændringscelle for Projekt 4.
Du kan finde dette eksempel på regnearket Hvis 3 så 4 i filen Capbudget.xlsx, som er vist i figur 30-4. Celle L9 refererer til den binære værdi, der er relateret til Projekt 3, og celle L12 refererer til den binære værdi, der er relateret til Projekt 4. Hvis vi vælger Project 3, ved at tilføje betingelsen L9<=L12, er L9 lig med 1, og vores begrænsning tvinger L12 (det binære projekt 4) til at være lig med 1. Vores begrænsning skal også lade den binære værdi i den justerbare celle i Projekt 4 være ubegrænset, hvis vi ikke vælger Projekt 3. Hvis vi ikke vælger Project 3, er L9 lig med 0, og vores betingelse tillader, at den binære Project 4-fil er lig med 0 eller 1, hvilket er det, vi ønsker. Den nye optimale løsning er vist i figur 30-4.
Der beregnes en ny optimal løsning, hvis valg af Projekt 3 betyder, at vi også skal vælge Projekt 4. Antag nu, at vi kun kan udføre fire projekter blandt projekt 1 til 10. (Se Højst 4 af P1-P10 regnearket, vist i figur 30-5.) I celle L8 beregner vi summen af de binære værdier, der er knyttet til projekterne 1-10 med formlen SUM(A6:A15). Derefter tilføjer vi betingelsen L8<=L10, hvilket sikrer, at højst 4 af de første 10 projekter markeres. Den nye optimale løsning er vist i figur 30-5. NPV er faldet til $9.014 milliarder.
Løsning af binære og heltalsprogrammeringsproblemer
Lineære problemløsermodeller, hvor nogle eller alle skiftende celler skal være binære eller heltal, er normalt sværere at løse end lineære modeller, hvor alle skiftende celler må være brøker. Af denne grund er vi ofte tilfredse med en næsten optimal løsning på et binært eller heltalsprogrammeringsproblem. Hvis din Problemløser-model kører i lang tid, kan du overveje at justere toleranceindstillingen i dialogboksen Indstillinger for Problemløser. (Se figur 30-6.) En toleranceindstilling på 0,5 % betyder f.eks., at Problemløser stopper, første gang den finder en realistisk løsning, der er inden for 0,5 % af den teoretisk optimale målcelleværdi (den teoretisk optimale målcelleværdi er den optimale målværdi, der findes, når binære begrænsninger og heltalsbegrænsninger udelades). Ofte står vi over for et valg mellem at finde et svar inden for 10 procent af optimalt på 10 minutter eller at finde en optimal løsning på to ugers computertid! Standardtoleranceværdien er 0,05 %, hvilket betyder, at Problemløser stopper, når den finder en værdi i målcellen, som ligger inden for 0,05 % af den teoretisk optimale værdi i målcellen.
Problemer
- En virksomhed har ni projekter under overvejelse. NUTIDSVÆRDIEN, der tilføjes for hvert projekt, og den kapital, der kræves af hvert projekt i de næste to år, er vist i nedenstående tabel. (Alle tal er i millioner). Projekt 1 tilføjer f.eks. kr. 14 millioner i NUTIDSVÆRDI og kræver udgifter på kr. 12 millioner i år 1 og 3 millioner kroner i år 2. I år 1 er 50 millioner dollars i kapital tilgængelige for projekter, og 20 millioner dollars er tilgængelige i år 2.
| NUTIDSVÆRDI | Udgifter i år 1 | Udgifter i år 2 | |
|---|---|---|---|
| Projekt 1 | 14 | 12 | 3 |
| Projekt 2 | 17 | 54 | 7 |
| Projekt 3 | 17 | 6 | 6 |
| Projekt 4 | 15 | 6 | 2 |
| Projekt 5 | 40 | 30 | 35 |
| Projekt 6 | 12 | 6 | 6 |
| Projekt 7 | 14 | 48 | 4 |
| Project 8 | 10 | 36 | 3 |
| Projekt 9 | 12 | 18 | 3 |
- Hvis vi ikke kan påtage os en brøkdel af et projekt, men skal påtage os enten hele eller slet ikke et projekt, hvordan kan vi så maksimere NUVÆRDIVÆRDIEN?
- Antag, at hvis projekt 4 iværksættes, skal projekt 5 gennemføres. Hvordan kan vi maksimere NUTIDSVÆRDI?
Et forlag forsøger at finde ud af, hvilken af de 36 bøger det skal udgive i år. Filen Pressdata.xlsx giver følgende oplysninger om hver bog:
- Forventede indtægter og udviklingsomkostninger (i tusindvis af dollars)
- Sider i hver bog
- Om bogen er rettet mod et publikum af softwareudviklere (angivet med et 1 i kolonne E)
Et forlag kan udgive bøger på i alt op til 8500 sider i år og skal udgive mindst fire bøger rettet mod softwareudviklere. Hvordan kan virksomheden maksimere sin fortjeneste?
Om artiklen
Denne artikel er tilpasset fra Microsoft Office Excel 2007 Data Analysis and Business Modeling af Wayne L. Winston.
Denne bog i klasseværelsesstil blev udviklet ud fra en række præsentationer af Wayne Winston, en velkendt statistiker og forretningsprofessor, der har specialiseret sig i kreative, praktiske anvendelser af Excel.