Uso del Risolutore per determinare la combinazione ottimale di prodotti

Come si determina la combinazione di prodotti mensili che massimizza la redditività?

Le aziende devono spesso determinare la quantità di ogni prodotto da produrre su base mensile. Nella sua forma più semplice, il problema della combinazione di prodotti include come determinare la quantità di ogni prodotto che deve essere prodotto durante un mese per massimizzare i profitti. La combinazione di prodotti deve in genere rispettare i vincoli seguenti:

• La combinazione di prodotti non può usare più risorse di quelle disponibili.

• Esiste una domanda limitata per ogni prodotto. Non è possibile produrre più prodotti durante un mese rispetto alla dettatura della domanda, perché la produzione in eccesso è sprecata (ad esempio, un farmaco deperibile).

Ora risolviamo l'esempio seguente del problema della combinazione di prodotti. È possibile trovare la soluzione a questo problema nel file Prodmix.xlsx, illustrato nella figura 27-1.

Supponiamo che lavoriamo per una società farmaceutica che produce sei prodotti diversi presso il loro impianto. La produzione di ogni prodotto richiede manodopera e materia prima. La riga 4 nella figura 27-1 Mostra le ore di manodopera necessarie per produrre una libbra di ogni prodotto e la riga 5 Mostra le libbre di materiale grezzo necessarie per produrre una libbra di ogni prodotto. Ad esempio, la produzione di una libbra di prodotto 1 richiede sei ore di lavoro e 3,2 libbre di materia prima. Per ogni farmaco, il prezzo per libbra è indicato nella riga 6, il costo unitario per libbra è indicato nella riga 7 e il contributo di profitto per libbra è indicato nella riga 9. Ad esempio, il prodotto 2 vende per $11,00 per libbra, comporta un costo unitario di $5,70 per libbra e contribuisce $5,30 profitto per libbra. La richiesta del mese per ogni farmaco è indicata nella riga 8. Ad esempio, la richiesta di prodotto 3 è di 1041 sterline. Questo mese sono disponibili 4500 ore di lavoro e 1600 libbre di materiale grezzo. In che modo questa società può massimizzare il profitto mensile?

Se non si conosce il Risolutore di Excel, si attaccherà questo problema costruendo un foglio di lavoro per tenere traccia dell'uso di profitti e risorse associato alla combinazione di prodotti. Useremo la versione di valutazione e l'errore per variare la combinazione di prodotti per ottimizzare il profitto senza usare più manodopera o materiale grezzo di quanto sia disponibile e senza produrre alcun farmaco in eccesso di domanda. Usiamo il Risolutore in questo processo solo alla fase di valutazione e errore. In sostanza, il Risolutore è un motore di ottimizzazione che esegue in modo impeccabile la ricerca con errori di valutazione.

Una chiave per risolvere il problema della combinazione di prodotti consiste nel calcolare in modo efficiente l'uso delle risorse e il profitto associato a qualsiasi combinazione di prodotti specificata. Uno strumento importante che possiamo usare per rendere questo calcolo è la funzione MATR. La funzione MATR moltiplica i valori corrispondenti negli intervalli di celle e restituisce la somma di tali valori. Ogni intervallo di celle usato in una valutazione matr deve avere le stesse dimensioni, il che significa che è possibile usare Matr con due righe o due colonne, ma non con una colonna e una riga.

Come esempio di come possiamo usare la funzione MATR nell'esempio di mix di prodotti, proviamo a calcolare l'uso delle risorse. Il nostro utilizzo del lavoro viene calcolato da

(Lavoro usato per libbra di farmaco 1) * (prodotto da 1 libbre) +
(Lavoro usato per libbra di farmaco 2) * (farmaco 2 libbre prodotto) +...
(Lavoro usato per libbra di farmaco 6) * (farmaco 6 libbre prodotto)

Potremmo calcolare l'uso del lavoro in modo più noioso come D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Analogamente, l'uso di materiale grezzo può essere calcolato come D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Tuttavia, l'immissione di queste formule in un foglio di lavoro per sei prodotti richiede molto tempo. Immaginate quanto tempo è necessario se si lavora con una società che ha prodotto, ad esempio, prodotti di 50 presso il proprio impianto. Un modo molto più semplice per calcolare l'uso del lavoro e della materia prima consiste nel copiare da D14 in D15 la formula matr ($D $2: $I $2, D4: I4). Questa formula calcola D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (che è il nostro uso del lavoro), ma è molto più facile da inserire. Si noti che si usa il simbolo $ con l'intervallo D2: i2 in modo che quando si copia la formula si acquisisca ancora il mix di prodotti dalla riga 2. La formula nella cella D15 calcola l'utilizzo della materia prima.

In modo simile, il nostro profitto è determinato da

(Profitto della droga 1 per libbra) * (farmaco 1 libbre prodotto) +
(Profitto della droga 2 per libbra) * (farmaco 2 libbre prodotto) +...
(Drug 6 profit per libbra) * (prodotto da 6 libbre)

Il profitto viene calcolato facilmente nella cella D12 con la formula matr (D9: i9, $D $2: $I $2).

Ora possiamo identificare i tre componenti del modello di Risolutore di prodotti combinati.

• Cella di destinazione. Il nostro obiettivo è massimizzare i profitti (calcolati nella cella D12).

• Modifica delle celle. Numero di libbre prodotto per ogni prodotto (elencato nell'intervallo di celle D2: I2)

• Vincoli. Sono presenti i vincoli seguenti:

  • Non usare più manodopera o materiale grezzo di quanto sia disponibile. Ovvero i valori nelle celle D14: D15 (le risorse usate) devono essere inferiori o uguali ai valori nelle celle F14: F15 (le risorse disponibili).

  • Non produrre più di un farmaco rispetto a quanto richiesto. I valori nelle celle D2: i2 (libbre prodotte da ogni farmaco) devono essere inferiori o uguali alla domanda per ogni farmaco (elencato nelle celle D8: i8).

  • Non è possibile produrre una quantità negativa di qualsiasi farmaco.

Ti mostrerò come immettere la cella di destinazione, cambiare le celle e i vincoli nel Risolutore. Tutto quello che devi fare è fare clic sul pulsante Risolvi per trovare una combinazione di prodotti che massimizza il profitto.

Per iniziare, fare clic sulla scheda dati e quindi nel gruppo analisi fare clic su Risolutore.

Verrà visualizzata la finestra di dialogo parametri Risolutore, come illustrato nella figura 27-2.

Fare clic sulla casella Imposta cella di destinazione e quindi selezionare la cella di profitto (cella D12). Fare clic sulla casella modifica celle e quindi selezionare l'intervallo D2: i2, che contiene le libbre prodotte da ogni farmaco. La finestra di dialogo ora deve essere visualizzata nella figura 27-3.

Ora è possibile aggiungere vincoli al modello. Fare clic sul pulsante Aggiungi. Verrà visualizzata la finestra di dialogo Aggiungi vincolo, illustrata nella figura 27-4.

Per aggiungere i vincoli di utilizzo delle risorse, fare clic sulla casella riferimento di cella e quindi selezionare l'intervallo D14: D15. Selezionare <= dall'elenco centrale. Fare clic sulla casella vincolo e quindi selezionare l'intervallo di celle F14: F15. La finestra di dialogo Aggiungi vincolo dovrebbe essere simile alla figura 27-5.

Ora abbiamo garantito che quando il Risolutore Cerca valori diversi per le celle in modifica, solo le combinazioni che soddisfano sia D14<= F14 (lavoro usato è minore o uguale a manodopera disponibile) che D15<= F15 (il materiale grezzo usato è minore o uguale a materiale grezzo disponibile) verrà considerato. Fare clic su Aggiungi per immettere i vincoli della domanda. Compilare la finestra di dialogo Aggiungi vincolo come illustrato nella figura 27-6.

L'aggiunta di questi vincoli garantisce che quando il Risolutore cerca combinazioni diverse per i valori delle celle in modifica, verranno considerate solo le combinazioni che soddisfano i parametri seguenti:

• D2<= D8 (l'importo prodotto dal farmaco 1 è minore o uguale alla domanda di farmaco 1)

• E2<= E8 (la quantità di prodotto del farmaco 2 è minore o uguale alla domanda di farmaco 2)

• F2<= F8 (l'importo prodotto dal farmaco 3 realizzato è minore o uguale alla domanda di farmaco 3)

• G2<= G8 (l'importo prodotto dal farmaco 4 realizzato è minore o uguale alla domanda di farmaco 4)

• H2<= h8 (l'importo prodotto del farmaco 5 realizzato è minore o uguale alla domanda di farmaco 5)

• I2<= i8 (l'importo prodotto del farmaco 6 realizzato è minore o uguale alla domanda di farmaco 6)

Fare clic su OK nella finestra di dialogo Aggiungi vincolo. La finestra del Risolutore dovrebbe essere simile alla figura 27-7.

Immettiamo il vincolo per cui la modifica delle celle deve essere non negativa nella finestra di dialogo Opzioni Risolutore. Fare clic sul pulsante Opzioni nella finestra di dialogo parametri Risolutore. Selezionare la casella Presupponi modello lineare e la casella Presupponi non negativi, come illustrato nella figura 27-8 nella pagina successiva. Fare clic su OK.

Se si seleziona la casella Presupponi non negativi, il Risolutore considera solo le combinazioni di celle in cui ogni cella cambia presuppone un valore non negativo. È stata selezionata la casella Presupponi modello lineare perché il problema della combinazione di prodotti è uno speciale tipo di problema di Risolutore chiamato modello lineare. In sostanza, un modello di Risolutore è lineare nelle condizioni seguenti:

• La cella di destinazione viene calcolata aggiungendo insieme i termini della maschera (modifica della cella) * (costante).

• Ogni vincolo soddisfa il "requisito modello lineare". Questo significa che ogni vincolo viene valutato aggiungendo insieme i termini della maschera (cambiando cella) * (costante) e confrontando le somme in una costante.

Perché il problema del Risolutore è lineare? La cella di destinazione (profit) viene calcolata come

(Profitto della droga 1 per libbra) * (farmaco 1 libbre prodotto) +
(Profitto della droga 2 per libbra) * (farmaco 2 libbre prodotto) +...
(Drug 6 profit per libbra) * (prodotto da 6 libbre)

Questo calcolo segue un modello in cui il valore della cella di destinazione viene derivato aggiungendo insieme i termini della maschera (modifica della cella) * (costante).

Il nostro vincolo del lavoro viene valutato confrontando il valore derivato da (lavoro usato per libbra di farmaco 1) * (farmaco 1 libbre prodotto) + (lavoro usato per libbra di farmaco 2) * (farmaco 2 libbre prodotto) +... (Lavoro USed per libbra di droga 6) * (farmaco 6 libbre prodotto) per il lavoro disponibile.

Di conseguenza, il vincolo del lavoro viene valutato aggiungendo insieme i termini della maschera (cambiando cella) * (costante) e confrontando le somme in una costante. Sia il vincolo del lavoro che il vincolo della materia prima soddisfano il requisito del modello lineare.

I vincoli della domanda hanno la forma

(Farmaco 1 prodotto) <= (richiesta di farmaco 1)
(Farmaco 2 prodotto) <= (richiesta di farmaco 2)
§
(Farmaco 6 prodotto) <= (richiesta di farmaco 6)

Ogni vincolo della domanda soddisfa anche il requisito del modello lineare, perché ognuno viene valutato aggiungendo insieme i termini della maschera (cambiando cella) * (costante) e confrontando le somme in una costante.

Dopo aver dimostrato che il modello di combinazione di prodotti è un modello lineare, perché dovrebbe interessarci?

• Se un modello di Risolutore è lineare e si seleziona Presupponi modello lineare, il Risolutore è garantito per trovare la soluzione ottimale per il modello del Risolutore. Se un modello di Risolutore non è lineare, il Risolutore può o non può trovare la soluzione ottimale.

• Se un modello di Risolutore è lineare e si seleziona Presupponi modello lineare, il Risolutore usa un algoritmo molto efficiente (metodo simplex) per trovare la soluzione ottimale del modello. Se un modello di Risolutore è lineare e non si seleziona Presupponi modello lineare, il Risolutore usa un algoritmo molto inefficiente (il metodo GRG2) e potrebbe avere difficoltà a trovare la soluzione ottimale del modello.

Dopo aver fatto clic su OK nella finestra di dialogo Opzioni Risolutore, tornare alla finestra di dialogo Risolutore principale, visualizzata in precedenza nella figura 27-7. Quando si fa clic su Risolvi, il risolutore calcola una soluzione ottimale (se disponibile) per il modello di combinazione di prodotti. Come ho già detto nel capitolo 26, una soluzione ottimale per il modello di mix di prodotti sarebbe un set di valori delle celle che cambiano (libbre prodotte da ogni farmaco) che massimizza il profitto rispetto al set di tutte le soluzioni possibili. Anche in questo caso, una soluzione fattibile è un set di valori delle celle che soddisfano tutti i vincoli. I valori delle celle cambianti visualizzati nella figura 27-9 sono una soluzione fattibile perché tutti i livelli di produzione sono non negativi, i livelli di produzione non superano la domanda e l'utilizzo delle risorse non supera le risorse disponibili.

I valori delle celle cambianti visualizzati nella figura 27-10 nella pagina successiva rappresentano una soluzione non praticabile per i motivi seguenti:

• Produciamo più farmaci 5 che la domanda.

• Usiamo più manodopera di quella disponibile.

• Usiamo più materiale grezzo di quello che è disponibile.

Dopo aver fatto clic su Risolvi, il Risolutore trova rapidamente la soluzione ottimale illustrata nella figura 27-11. È necessario selezionare Mantieni soluzione del Risolutore per mantenere i valori ottimali della soluzione nel foglio di lavoro.

La nostra società farmaceutica può massimizzare il suo profitto mensile a un livello di $6.625,20 producendo 596,67 chili di droga da 4, 1084 libbre di droga 5 e nessuna delle altre droghe! Non è possibile determinare se si riesce a ottenere il profitto massimo di $6.625,20 in altri modi. Tutto quello che possiamo essere certi è che con le nostre limitate risorse e richieste, non c'è modo di fare più di $6.627,20 questo mese.