Risolutore 01 - Ottimizzazione della produzione Stampa
Scritto da Mao   
Sabato 23 Febbraio 2008 18:37

Lo strumento risolutore e' un potente strumento di Excel poco conosciuto.
In questo primo articolo di una mini-serie si introdurranno i concetti base di questo add-in.

Prima di tutto bisogna controllare che lo strumento sia attivo. Per fare ciò, andare nel menù opzioni di Excel, componenti aggiuntivi e verificare componente aggiuntivo risolutore.





Il risolutore può risolvere molti problemi sia lineari che non di ottimizzazione, massimizzazione etc.
Per rendere questa mini-serie di articoli più interattiva, dopo i primi 3 appuntamenti in cui si proporranno esempi di base, vorrei cercare di proporre come sia possibile risolvere con solver alcuni problemi dei lettori (sperando di essere in grado di farlo).
Durante il primo esempio, verranno esposti i concetti di base del risolutore.


 

L'IMPRENDITORE E L'OTTIMIZZAZIONE DELLA PRODUZIONE


 

Il primo esempio è volutamente semplice e cerca di spiegare le basi del solver. Un imprenditore ha a disposizione una macchina che può lavorare 200 ore al mese e ha la possibilità di produrre 2 principali prodotti: CD con una cadenza di 100 pezzi all'ora e DVD con una cadenza di 50 pezzi all'ora. L'imprenditore vorrebbe massimizzare il suo profitto sapendo che il valore di mercato dei CD è di 1€/unità mentre dei DVD è di 3€/unità. Nel massimizzare il suo profitto, l'imprenditore deve anche tener conto di un vincolo di progettazione della macchina per cui non può adibirla per più di 120 ore alla produzione di DVD.

Prima di iniziare a lavorare su Excel bisogna strutturare il problema identificando:
- Variabili
- Funzione obiettivo
- Vincoli

Le variabili decisionali sono quelle decisioni che l'imprenditore può prendere per raggiungere il proprio obiettivo. In questo caso le variabili sono:
- Quantità di ore da adibire alla produzione di CD (X)
- Quantità di ore da adibire alla produzione di DVD (Y)

La funzione obiettivo rappresenta la funzione che descrive cosa l'imprenditore vuole massimizzare. In questo caso la funzione obiettivo è rappresentata da quanti soldi l'imprenditore riesce ad ottenere vendendo la produzione di CD e DVD che decide di produrre:
- Funzione obiettivo: soldi da CD + soldi da DVD = X ore * 100 unità/ora * 1 €/unità + Y ore * 50 unità/ora * 3 €/unità = 100 X + 150 Y

I vincoli rappresentano delle limitazioni alle variabile decisionali dell'imprenditore. In questa caso i 2 vincoli sono sul massimo ore a disposizione della macchina e sulla limitazione di ore da dedicare alla produzione di DVD
- Numero massimo ore: X ore +Y ore ≤ 200 ore
- Numero massimo ore DVD: Y ore ≤ 120 ore

Dopo l'analisi di variabili decisionali, funzione obiettivo e vincoli, siamo ora pronti per costruire il foglio Excel per risolvere il problema.

Come consiglio personale consiglio di organizzare le 3 parti come segue

Variabili: indicate nell'Excel dallo sfondo giallo sono nel nostro caso CD (X) e DVD (Y). La variabile ore totali è una variabile dipendente, somma delle due precedenti, e non è quindi considerata variabile decisionale.

Vincoli: indicati in verde in Excel si riferiscono alle variabili decisionali ed eventualmente a loro combinazioni – in questo caso solo una delle due variabili decisionali ha un vincolo (DVD ≤ 120 ore). Siccome i CD in questo caso non sono sottoposti a vincoli, si usa fittizziamente un valore molto elevato (9999). L'ultimo vincolo è sul numero massimo di ore X+Y ≤ 200 ore.

Funzione obiettivo: indicata nel file in azzurro, è rappresentato come una funzione delle variabili decisionali.

Risolutore: una volta impostato il file siamo pronti ad impostare il solver tramite il menù DATI -> ANALISI -> RISOLUTORE



In "imposta cella obiettivo" bisogna inserire la funzione obiettivo, in questo caso $K$6 e selezionare MAX siccome ci interessa massimizzare la variabile valore.

In "cambiando le celle" bisogna inserire le variabili decisionali, in questo caso $C$4:$C$5.

In "vincoli" bisogna inserire i vincoli, in questo caso $C$4:$C$6 ≤ $F$4:$F$6. In questo caso è possibile inserire tutti i vincoli assieme essendo lo stesso il segno di disuguaglianza per tutti i vincoli (≤ in questo caso).



Una volta compilati tutti i campi in "parametri del risolutore" siamo pronti a premere il pulsante "risolvi" per visualizzare la soluzione. (Approfondiremo le opzioni nei prossimi articoli)

Premendo su "mantieni la soluzione del Risolutore" si dovrebbe ottenere questa situazione:
- Variabili decisionali: CD X = 80, DVD Y = 120
-Funzione obiettivo pari a 26.000€
- Tutti i vincoli rispettati (Y ≤ 120, X+Y ≤ 200)



Nella figura sottostante ho cercato di illustrare lo spazio delle possibili soluzioni dati i vincoli e l'andamento delle funzioni obiettivo.



Lo spazio delle possibili soluzioni è identificato dall'area delimitata dai vincoli presenti nel problema
- X ≥ 0
- Y ≥ 0
- Y ≤ 120
- X + Y ≤ 200

I vincoli sono rappresentati dalle righe blu, e la risultante area delle possibili soluzioni è quella in azzurro
Le righe rosse rappresentano invece le soluzioni che generano lo stesso valore della funzione obiettivo (ad es. tutte le coppie di soluzioni che generano 10.000€, 20.000€ e 30.000€). Queste iso-curve servono ad indicare in che direzione la funzione obiettivo aumenta.

Come si vede dal grafico la soluzione trovata col solver (80,120) rappresenta il punto dell'area delle possibili soluzioni a cui corrisponde la iso-curva più alta -> abbiamo quindi la verifica che stiamo massimizzando la funzione obiettivo.
Nel prossimo articolo parleremo del problema dei trasporti

Tutti gli episodi della serie solver:

Risolutore 01 - Ottimizzazione della produzione 
Risolutore 02 - Problema dei trasporti
Risolutore 03 - Problema dei trasporti - Rapporto sensibilità
Risolutore 04 - Ritorno crescente o decrescente  

 


Sistema operativo: Win32
Applicazioni: Excel 2002/XP, Excel 2003, Excel 2007

 

Allegati:
FileDescrizioneDimensione del File
Scarica questo file (Solver01.xls)Solver01.xlsFile di esempio18 Kb
Ultimo aggiornamento Martedì 27 Ottobre 2009 09:57