In che modo un'azienda può utilizzare Solver per determinare quali progetti dovrebbe intraprendere?
Ogni anno, un'azienda come Eli Lilly deve determinare quali farmaci sviluppare; un'azienda come Microsoft, quali programmi software sviluppare; un'azienda come Proctor & Gamble, quali nuovi prodotti di consumo sviluppare. La funzionalità Risolutore in Excel può aiutare un'azienda a prendere queste decisioni.
In che modo un'azienda può utilizzare Solver per determinare quali progetti dovrebbe intraprendere?
La maggior parte delle aziende vuole intraprendere progetti che apportino il massimo valore attuale netto (VAN), soggetto a risorse limitate (di solito capitale e lavoro). Supponiamo che una società di sviluppo software stia cercando di determinare quale dei 20 progetti software dovrebbe intraprendere. Il VAN (in milioni di dollari) apportato da ciascun progetto, così come il capitale (in milioni di dollari) e il numero di programmatori necessari durante ciascuno dei prossimi tre anni sono indicati nel foglio di lavoro del modello di base nel file Capbudget.xlsx, che è mostrato nella Figura 30-1 nella pagina successiva. Ad esempio, Project 2 rende 908 milioni di dollari. Richiede $ 151 milioni durante l'anno 1, $ 269 milioni durante l'anno 2 e $ 248 milioni durante l'anno 3. Il progetto 2 richiede 139 programmatori durante l'anno 1, 86 programmatori durante l'anno 2 e 83 programmatori durante l'anno 3. Le celle E4:G4 mostrano il capitale (in milioni di dollari) disponibile durante ciascuno dei tre anni e le celle H4:J4 indicano quanti programmatori sono disponibili. Ad esempio, durante l'anno 1 sono disponibili fino a 2,5 miliardi di dollari di capitale e 900 programmatori.
L'azienda deve decidere se intraprendere ogni progetto. Supponiamo di non poter intraprendere una frazione di un progetto software; Se assegniamo lo 0,5 delle risorse necessarie, ad esempio, avremmo un programma non funzionante che ci porterebbe entrate pari a $ 0!
Il trucco per modellare le situazioni in cui si esegue o non si esegue un'operazione consiste nell'usare celle di modifica binarie. Una cella binaria che cambia è sempre uguale a 0 o 1. Quando una cella di modifica binaria che corrisponde a un progetto è uguale a 1, eseguiamo il progetto. Se una cella di modifica binaria che corrisponde a un progetto è uguale a 0, il progetto non viene eseguito. Impostare il Risolutore per l'utilizzo di un intervallo di celle di modifica binarie aggiungendo un vincolo, selezionare le celle di modifica che si desidera utilizzare e quindi scegliere Raccoglitore dall'elenco nella finestra di dialogo Aggiungi vincolo.
Con questo background, siamo pronti a risolvere il problema della selezione del progetto software. Come sempre con un modello Solver, iniziamo identificando la nostra cellula target, le celle che cambiano e i vincoli.
- Cella di destinazione. Massimizziamo il VAN generato dai progetti selezionati.
- Celle modificabili. Cerchiamo una cella di cambio binario 0 o 1 per ogni progetto. Ho individuato queste celle nell'intervallo A6:A25 (e ho chiamato l'intervallo doit). Ad esempio, un 1 nella cella A6 indica che stiamo intraprendendo il Progetto 1; uno 0 nella cella C6 indica che non stiamo intraprendendo il Progetto 1.
- Vincoli. Dobbiamo assicurarci che per ogni anno t (t=1, 2, 3), il capitale dell'anno t utilizzato sia minore o uguale al capitale disponibile dell'anno t e il lavoro dell'anno t utilizzato sia inferiore o uguale al lavoro dell'anno t disponibile.
Come puoi vedere, il nostro foglio di lavoro deve calcolare per qualsiasi selezione di progetti il VAN, il capitale utilizzato annualmente e i programmatori utilizzati ogni anno. Nella cella B2 utilizzo la formula SOMMA.PRODOTTO(annullamento,VAN) per calcolare il VAN totale generato dai progetti selezionati. (Il nome dell'intervallo NPV si riferisce all'intervallo C6:C25.) Per ogni progetto con 1 nella colonna A, questa formula rileva il VAN del progetto e per ogni progetto con 0 nella colonna A, questa formula non rileva il VAN del progetto. Pertanto, siamo in grado di calcolare il VAN di tutti i progetti e la nostra cella di destinazione è lineare perché viene calcolata sommando i termini che seguono la forma (cella modificabile)*(costante). In modo simile, calcolo il capitale utilizzato ogni anno e il lavoro utilizzato ogni anno copiando da E2 a F2:J2 la formula SOMMAPRODOTTO(doit,E6:E25).
A questo punto, compilare la finestra di dialogo Parametri risolutore come mostrato nella Figura 30-2.
Il nostro obiettivo è massimizzare il VAN dei progetti selezionati (cella B2). Le celle che cambiano (l'intervallo chiamato doit) sono le celle che cambiano binarie per ogni progetto. Il vincolo E2:J2<=E4:J4 assicura che durante ogni anno il capitale e il lavoro utilizzati siano minori o uguali al capitale e al lavoro disponibili. Per aggiungere il vincolo che rende binarie le celle modificate, fare clic su Aggiungi nella finestra di dialogo Parametri risolutore e quindi selezionare Bin dall'elenco al centro della finestra di dialogo. Viene visualizzata la finestra di dialogo Aggiungi vincolo come mostrato nella Figura 30-3.
Il nostro modello è lineare perché la cella di destinazione viene calcolata come somma di termini che hanno la forma (cella modifica)*(costante) e perché i vincoli di utilizzo delle risorse vengono calcolati confrontando la somma di (celle modificabili)*(costanti) con una costante.
Con la finestra di dialogo Parametri risolutore compilata, fare clic su Risolvi per visualizzare i risultati mostrati in precedenza nella Figura 30-1. L'azienda può ottenere un VAN massimo di 9.293 milioni di dollari (9,293 miliardi di dollari) scegliendo i progetti 2, 3, 6-10, 14-16, 19 e 20.
Gestione di altri vincoli
A volte i modelli di selezione del progetto hanno altri vincoli. Ad esempio, supponiamo che se selezioniamo il Progetto 3, dobbiamo selezionare anche il Progetto 4. Poiché la soluzione ottimale attuale seleziona il progetto 3 ma non il progetto 4, sappiamo che la soluzione attuale non può rimanere ottimale. Per risolvere questo problema, è sufficiente aggiungere il vincolo che indica che la cella di modifica binaria per Project 3 è minore o uguale alla cella di modifica binaria per Project 4.
Questo esempio si trova nel foglio di lavoro If 3 then 4 nel file Capbudget.xlsx, illustrato nella Figura 30-4. La cella L9 fa riferimento al valore binario relativo a Project 3 e la cella L12 al valore binario relativo a Project 4. Aggiungendo il vincolo L9<=L12, se scegliamo il Progetto 3, L9 è uguale a 1 e il nostro vincolo forza L12 (il binario del Progetto 4) a essere uguale a 1. Il nostro vincolo deve anche lasciare illimitato il valore binario nella cella che cambia del Progetto 4 se non selezioniamo il Progetto 3. Se non selezioniamo il Progetto 3, L9 è uguale a 0 e il nostro vincolo consente al binario del Progetto 4 di essere uguale a 0 o 1, che è quello che vogliamo. La nuova soluzione ottimale è mostrata nella Figura 30-4.
Viene calcolata una nuova soluzione ottimale se la selezione del Progetto 3 comporta la selezione anche del Progetto 4. Supponiamo ora di poter realizzare solo quattro progetti tra i progetti da 1 a 10. (Vedere il foglio di lavoro Al massimo 4 di P1-P10, mostrato nella Figura 30-5.) Nella cella L8 viene calcolata la somma dei valori binari associati ai progetti da 1 a 10 con la formula SOMMA(A6:A15). Quindi aggiungiamo il vincolo L8<=L10, che garantisce che, al massimo, vengano selezionati 4 dei primi 10 progetti. La nuova soluzione ottimale è mostrata nella Figura 30-5. Il VAN è sceso a 9,014 miliardi di dollari.
Risoluzione di problemi di programmazione binaria e intera
I modelli di risolutore lineare in cui alcune o tutte le celle mutevoli devono essere binarie o intere sono in genere più difficili da risolvere rispetto ai modelli lineari in cui tutte le celle mutevoli possono essere frazioni. Per questo motivo, spesso ci accontentiamo di una soluzione quasi ottimale a un problema di programmazione binaria o intera. Se il modello del Risolutore viene eseguito per molto tempo, è consigliabile modificare l'impostazione Tolleranza nella finestra di dialogo Opzioni Risolutore. (Vedi Figura 30-6.) Ad esempio, un'impostazione di tolleranza pari allo 0,5% significa che il Risolutore si fermerà la prima volta che trova una soluzione fattibile entro lo 0,5% del valore teorico della cella di destinazione ottimale (il valore teorico della cella di destinazione ottimale è il valore di destinazione ottimale trovato quando i vincoli binario e intero vengono omessi). Spesso ci troviamo di fronte a una scelta tra trovare una risposta entro il 10% dall'ottimale in 10 minuti o trovare una soluzione ottimale in due settimane di tempo al computer! Il valore di Tolleranza di default è 0,05%, il che significa che il Risolutore si arresta quando trova un valore di cella di destinazione entro lo 0,05% del valore teorico della cella di destinazione ottimale.
Problemi
- Una società ha nove progetti in esame. Il VAN aggiunto da ciascun progetto e il capitale richiesto da ciascun progetto nei due anni successivi sono indicati nella tabella seguente. (Tutti i numeri sono in milioni.) Ad esempio, il progetto 1 aggiungerà 14 milioni di dollari in VAN e richiederà spese di 12 milioni di dollari durante l'anno 1 e 3 milioni di dollari durante l'anno 2. Durante l'anno 1, 50 milioni di dollari di capitale sono disponibili per i progetti e 20 milioni di dollari sono disponibili durante l'anno 2.
| VAN | Spesa anno 1 | Spese dell'anno 2 | |
|---|---|---|---|
| Progetto 1 | 14 | 12 | 3 |
| Progetto 2 | 17 | 54 | 7 |
| Progetto 3 | 17 | 6 | 6 |
| Progetto 4 | 15 | 6 | 2 |
| Progetto 5 | 40 | 30 | 35 |
| Progetto 6 | 12 | 6 | 6 |
| Progetto 7 | 14 | 48 | 4 |
| Progetto 8 | 10 | 36 | 3 |
| Progetto 9 | 12 | 18 | 3 |
- Se non possiamo intraprendere una frazione di un progetto ma dobbiamo intraprendere tutto o nessuno di un progetto, come possiamo massimizzare il VAN?
- Supponiamo che se il Progetto 4 viene intrapreso, il Progetto 5 deve essere intrapreso. Come possiamo massimizzare il VAN?
Una casa editrice sta cercando di determinare quale dei 36 libri dovrebbe pubblicare quest'anno. Il file Pressdata.xlsx fornisce le seguenti informazioni su ogni libro:
- Entrate previste e costi di sviluppo (in migliaia di dollari)
- Pagine di ogni libro
- Se il libro è rivolto a un pubblico di sviluppatori di software (indicato da un 1 nella colonna E)
Una casa editrice può pubblicare libri per un totale di 8500 pagine quest'anno e deve pubblicare almeno quattro libri orientati agli sviluppatori di software. Come può l'azienda massimizzare il proprio profitto?
Informazioni sull'articolo
Questo articolo è stato adattato da Microsoft Office Excel 2007 Data Analysis and Business Modeling di Wayne L. Winston.
Questo libro in stile aula è stato sviluppato da una serie di presentazioni di Wayne Winston, un noto statistico e professore di economia specializzato in applicazioni pratiche e creative di Excel.