Ottimizzazione della produzione di lattine: un case study di Data Analysis in Excel

Una azienda di bevande produce lattine per confezionare le sue bevande. L’attuale stampo utilizzato per la parte superiore della lattina ha un raggio di 3,5 cm.

Per risolvere il problema dobbiamo utilizzare la funzione Goal Seek di Excel. L’obiettivo è trovare l’altezza ottimale della lattina (cilindro) che può contenere 375 ml di bevanda, avendo un raggio di 3,5 cm. Calcoleremo il volume della lattina utilizzando la formula del cilindro: pi * raggio^2 * altezza.

Seguire questi passaggi:

• Calcolare il volume desiderato in millilitri e convertirlo in centimetri cubi: 1 ml equivale a 1 cm³, quindi 375 ml sono uguali a 375 cm³.

• Nella cella C5, inserire la formula per il volume della lattina: “=PI()*C1*C1*C2“.

• La cella C1 contiene il raggio (3,5 cm) e la cella C2 conterrà l’altezza che stiamo cercando.

• Nella barra multifunzione (Ribbon), fare clic sulla scheda “Dati” e selezionare “Goal Seek” nel gruppo “Strumenti di analisi”:

• Nella finestra di dialogo “Goal Seek”, impostare i seguenti parametri:
  • “Imposta cella obiettivo” (“Set cell”): selezionare la cella C5, che contiene il volume della lattina.
  • “Uguale a” (“To value”): inserire il valore desiderato del volume, che è 375 cm³.
  • “Modificando la cella” (“By changing cell”): selezionare la cella C2, che contiene l’altezza della lattina.
  • Fare clic su “OK” per avviare Goal Seek.

Excel modificherà l’altezza nella cella C2 fino a raggiungere il volume desiderato di 375 cm³ nella cella C5. Una volta completato il processo di Goal Seek, l’altezza corretta della lattina verrà visualizzata nella cella C2. Questo valore rappresenta l’altezza necessaria per contenere 375 ml di bevanda con un raggio di 3,5 cm.

L’azienda vuole esaminare i vari casi in cui lattine di diverse dimensioni con raggio compreso tra 2 e 6 cm e altezze comprese tra 7 e 12 cm possono contenere. Considerare incrementi di 0,5 cm sia per il raggio che per l’altezza della lattina e costruire una Tabella dati.

con “Data Table”

Con l’obiettivo di esplorare vari casi di lattine di diverse dimensioni, costruiremo una tabella dati (Data Table) che consideri incrementi di 0,5 cm sia per il raggio che per l’altezza delle lattine.

• Nella cella C8, inserire il calcolo per il volume utilizzato nella cella C5:
  =PI()*C1*C1*C2

• Selezionare l’intervallo C8:L19.

• Nella barra multifunzione (Ribbon), fare clic sulla scheda “Dati” e selezionare “Data Table” nel gruppo “Strumenti di analisi”:

• Nella finestra di dialogo “Data Table“, impostare i seguenti parametri:
  • impostare con “$C$1” per “Row input cell” e “$C$2” per “Column input cell”.
  • fare click su “OK“.

(alternativa) senza “Data Table”

Per mostrare i vari casi di lattine con raggio compreso tra 2 e 6 cm e altezze tra 7 e 12 cm, con incrementi di 0,5 cm per entrambe le dimensioni, è necessario eseguire questi passaggi:

• Nel foglio di calcolo, sono già stati inseriti i valori del raggio nella riga 8 (dalle celle D8 a M8) e i valori dell’altezza nella colonna C (dalle celle C9 a C19).

• Ora, bisogna calcolare il volume per ogni combinazione di raggio e altezza. Inserire la formula del volume (pi * raggio^2 * altezza) nella cella D9. Utilizza la funzione PI() e i riferimenti assoluti e relativi appropriati per il raggio e l’altezza. La formula è la seguente:   =PI()*D$8*D$8*$C9

• Copiare la formula nella cella D9 e incollarla nell’intero intervallo di celle da D9 a M19. Excel calcolerà il volume per ogni combinazione di raggio e altezza nella tabella.

La tabella dati appena creata mostra il volume delle lattine con diversi raggi (2-6 cm) e altezze (7-12 cm) a incrementi di 0,5 cm. Questa tabella consente all’azienda di esaminare facilmente i vari casi per lattine di diverse dimensioni.

L’azienda sta valutando di ridurre il costo di produzione delle lattine, il che significa minimizzare la superficie della lattina. Poiché una superficie più piccola implica un costo del metallo inferiore, l’azienda mira a minimizzare la superficie delle lattine.

Per ridurre il costo del metallo utilizzato nella produzione delle lattine, utilizzeremo il Solver (Risolutore) per determinare le dimensioni della lattina (raggio e altezza) al fine di minimizzare l’area superficiale, garantendo al contempo che la lattina contenga ancora 375 ml. Calcoleremo la superficie delle lattine con la formula del cilindro: (2 * pi * raggio * altezza) + (2 * pi * raggio^2).

• Assicurarsi che Solver sia abilitato su Excel: andiamo nella scheda “File” > “Opzioni” > “Componenti aggiuntivi” > selezioniamo “Excel Add-ins” nel menu a discesa e facciamo clic su “Vai…” > assicurandoci che “Solver Add-in” sia selezionato e facciamo clic su “OK“.

• Nel foglio di lavoro inserire la formula per l’area superficiale nella cella C4: “=(2*PI()*C1*C1)+(2*PI()*C2*C1)“. Inoltre, nella cella C5 assicurarsi che sia presente la formula per il volume: “=PI()*C1*C1*C2".

• Andiamo nella barra multifunzione (Ribbon) “Dati” e facciamo clic su “Solver” nel gruppo “Analisi”.

• Nella finestra di dialogo di Solver, impostiamo i seguenti parametri:
  • Imposta l’obiettivo (Set Objective): selezioniamo la cella C4 (Surface Area) poiché vogliamo minimizzare l’area superficiale.
  • Uguale a (To): scegliamo “Min” poiché vogliamo minimizzare l’area superficiale.
  • Modificando le celle (By Changing Variable Cells): selezioniamo le celle C1 e C2, poiché vogliamo trovare il raggio e l’altezza ottimali.

• Facciamo clic sul pulsante “Aggiungi” (Add) per impostare i vincoli:
• Vincolo 1: selezioniamo la cella C5 (Volume) per la prima casella, scegliamo “=” nella casella centrale e inseriamo 375 nella terza casella. Questo vincolo assicura che la lattina contenga ancora 375 ml.

• Vincolo 2 e 3 (opzionali ma utili): è possibile aggiungere ulteriori vincoli per il raggio e l’altezza se si desidera limitare i valori a intervalli ragionevoli. Ad esempio, potremmo voler limitare il raggio (C1) e l’altezza (C2) a valori maggiori di 0.
• Facciamo clic su “OK” per chiudere la finestra dei vincoli e tornare alla finestra di dialogo di Solver.

• Facciamo clic su “Risolvi” (“Solve”) per avviare Solver. Excel troverà il raggio e l’altezza ottimali che minimizzano l’area superficiale della lattina, rispettando i vincoli impostati.

• Nella finestra di dialogo dei risultati, facciamo clic su “Mantieni soluzione del Solver” (“Keep Solver Solution”) e poi su “OK” per applicare i risultati al foglio di lavoro.

• Ripetere il “Solver” per l’area superficiale in m² (C3), dove è presente la formula:
  ((2PI()C1C1)+(2PI()C2C1)) / 10000

Grazie a “Solver” abbiamo trovato le dimensioni ottimali della lattina (raggio e altezza) che minimizzano l’area superficiale pur garantendo che la lattina contenga ancora 375 ml di liquido.

L’azienda impacchetta le lattine in contenitori cubici di dimensioni 120 cm x 120 cm x 120 cm. Calcoleremo il numero di lattine che possono essere inserite nel contenitore sia dal punto di vista matematico che pratico, arrotondando verso il basso i valori ottenuti.

Per risolvere il problema è necessario seguire questi passaggi:

• Calcolare quante lattine possono essere impacchettate nella cassa dal punto di vista matematico:
  • Per fare questo, utilizzeremo le seguenti formule (celle R2, R3 e R4):
    • R2: “=P2/(2*$C$1)”- calcola il numero di lattine lungo la lunghezza della cassa
    • R3: “=P3/(2*$C$1)” – calcola il numero di lattine lungo la larghezza della cassa
    • R4: “=P4/C2” – calcola il numero di lattine lungo l’altezza della cassa

• Calcolare il totale delle lattine dal punto di vista matematico:
  • Utilizzeremo la seguente formula (cella R6) per calcolare il totale delle lattine dal punto di vista matematico:
    • R6 (Totale lattine, matematiche): “=R2*R3*R4”

• Calcolare quante lattine possono essere impacchettate nella cassa dal punto di vista pratico:
  • Per fare questo, utilizzeremo le seguenti formule (celle S2, S3 e S4):
    • S2: “=FLOOR.MATH(R2)” – arrotonda verso il basso il numero di lattine lungo la lunghezza della cassa
    • S3: “=FLOOR.MATH(R3)” – arrotonda verso il basso il numero di lattine lungo la larghezza della cassa
    • S4: “=FLOOR.MATH(R4)” – arrotonda verso il basso il numero di lattine lungo l’altezza della cassa

• Calcolare il totale delle lattine dal punto di vista pratico:
  • Utilizzeremo la seguente formula (cella S6) per calcolare il totale delle lattine dal punto di vista pratico:
    • S6 (Totale lattine, pratiche): “=S4*S3*S2“

Dopo aver inserito i valori delle dimensioni della cassa (120 cm * 120 cm * 120 cm) nelle celle P2, P3 e P4 e il raggio e l’altezza della lattina nelle celle C1 e C2, otterremo il totale delle lattine dal punto di vista matematico e pratico rispettivamente nelle celle R6 e S6.

Utilizzando il Solver, determineremo le dimensioni del contenitore cubico che minimizzano la differenza tra il numero di lattine calcolate matematicamente e quelle reali (definite come “lattine potenziali”) per ridurre gli sprechi.

Per minimizzare il numero di “lattine potenziali” e ridurre gli sprechi, utilizzeremo di nuovo il Solver (Risolutore) di Excel. In questo caso, il nostro obiettivo sarà minimizzare il valore nella cella R7, che rappresenta la differenza tra il numero totale di lattine da un punto di vista matematico e quello da un punto di vista pratico.

Ecco i passaggi da seguire:

• Selezionare “Dati” nella barra multifunzionale (Ribbon) e poi cliccare su “Solver” nella barra degli strumenti.

• Nella finestra di dialogo del “Solver”, impostare i seguenti parametri:
  • Imposta l’obiettivo: seleziona la cella R7 (Lattine Potenziali).
  • Uguale a: seleziona “Min“.
  • Modificando le celle: inserisci il range P2:P4 (lunghezza, larghezza e altezza delle casse).
  • Cliccare su “Aggiungi vincolo” per aggiungere i seguenti vincoli:
    • P2 >= 1 (la lunghezza della cassa deve essere maggiore o uguale a 1)
    • P3 >= 1 (la larghezza della cassa deve essere maggiore o uguale a 1)
    • P4 >= 1 (l’altezza della cassa deve essere maggiore o uguale a 1)
  • Assicurarsi che il metodo di risoluzione sia impostato su “GRG Nonlinear” e poi cliccare su “Risolvi” (“Solve”).

Excel troverà la soluzione ottimale che minimizza il numero di “lattine potenziali”. Prendere nota dei valori ottimali per Lunghezza (P2), Larghezza (P3) e Altezza (P4).Tenere presente che i risultati ottenuti potrebbero essere frazionari, quindi potrebbe essere necessario arrotondarli o considerarli in modo appropriato a seconda delle esigenze pratiche della situazione reale.

L’azienda desidera stimare il prezzo del metallo utilizzato nelle lattine considerando diverse probabilità associate a variazioni di prezzo. Utilizzeremo la funzione SUMPRODUCT per calcolare il prezzo stimato del metallo in base a diversi scenari.

Per calcolare il prezzo stimato del metallo utilizzando SUMPRODUCT, dobbiamo prima inserire i diversi prezzi possibili del metallo e le loro probabilità corrispondenti nelle celle del foglio di lavoro.

Il prezzo attuale del metallo è 0,8, tuttavia c’è solo una probabilità dello 0,4 che si verifichi.
C’è una probabilità dello 0,1 che il prezzo del metallo raddoppi e una probabilità dello 0,1 che il prezzo del metallo si dimezzi.
C’è anche una probabilità dello 0,2 che il prezzo del metallo sia la media tra il prezzo attuale e il prezzo dimezzato.
C’è anche una probabilità dello 0,2 che il prezzo del metallo sia la media tra il prezzo attuale e il prezzo raddoppiato.

• Nella cella Q17, inserire il prezzo attuale del metallo: 0,8.

• Nelle celle Q15, Q16, Q18 e Q19, inserire i valori di prezzo calcolati basati sulle probabilità fornite:
  • Q15 (Prezzo molto basso): “=Q17*0,5“
  • Q16 (Prezzo basso): “=AVERAGE(Q15;Q17)“
  • Q17 (prezzo corrente): 0,8 
  • Q18 (Prezzo alto): “=AVERAGE(Q17;Q19)“
  • Q19 (Prezzo molto alto): “=Q17*2“

• Inserire le probabilità corrispondenti nelle celle R15, R16, R17, R18 e R19:
  • R15: 0,1 (probabilità del prezzo dimezzato)
  • R16: 0,2 (probabilità della media tra il prezzo corrente e il prezzo dimezzato)
  • R17: 0,4 (probabilità del prezzo corrente)
  • R18: 0,2 (probabilità della media tra il prezzo corrente e il prezzo raddoppiato)
  • R19: 0,1 (probabilità del prezzo raddoppiato)

Usare la funzione SUMPRODUCT per calcolare il prezzo stimato del metallo moltiplicando ciascun prezzo del metallo per la sua probabilità corrispondente e sommando i risultati. Inserire la seguente formula nella cella Q10: =SUMPRODUCT(Q15:Q19;R15:R19)

Questa formula moltiplica ogni prezzo del metallo per la sua probabilità corrispondente e somma i risultati per ottenere il prezzo stimato del metallo. Il valore risultante nella cella Q10 rappresenta il prezzo stimato del metallo, tenendo conto delle diverse probabilità fornite.

Infine, utilizzeremo lo Scenario Manager per presentare alla direzione il costo totale del metallo, considerando diversi scenari di prezzo, quali basso (0,6), alto (1,2) e molto alto (1,6).

La direzione dell’azienda desidera valutare l’impatto del costo totale del metallo in base a diversi scenari di prezzo del metallo.Utilizzare lo Scenario Manager per presentare il costo totale del metallo (Q12) alla direzione, includendo gli scenari di prezzo basso = 0,6, alto = 1,2 e molto alto = 1,6..
Al termine creare un report di sintesi che presenti tutti e tre gli scenari in un nuovo foglio.

Per utilizzare lo Scenario Manager per presentare il costo totale del metallo (Q12) con gli scenari di prezzo basso (0,6), alto (1,2) e molto alto (1,6), seguire questi passaggi:

• Assegnare alla cella Q10 il nome “Prezzo_Stimato_Metallo”, Q11 “Costo_Metallo_Lattina” e Q12 ”Costo_Totale_Metallo”.

• Nella cella Q11 inserire la formula: “=C3*Prezzo_Stimato_Metallo”

• Nella cella Q12 inserire la formula: “=Costo_Metallo_Lattina*S6”

• Andare alla scheda “Dati” nella barra multifunzionale (Ribbon) e fare clic su “Scenario Manager” nel gruppo “Previsioni e analisi” (“Forecast”).

• Nel riquadro “Scenario Manager”, fare clic su “Aggiungi” (“Add”) per creare un nuovo scenario.

• Nella finestra “Aggiungi scenario”, inserire il nome dello scenario, ad esempio “Prezzo Metallo Basso”. Nel campo “Celle variabili”, inserire il riferimento alla cella del prezzo del metallo, ovvero “Prezzo_Stimato_Metallo” (Q10) e fare click su OK.

• Nell’avviso seguente premere OK: viene indicato che la formula nella cella Q10 (Prezzo Stimato del Metallo) verrà rimossa, quindi ricordarsi di tale modifica qualora fosse necessario riutilizzare il foglio Excel per future elaborazioni.

• Nella finestra successiva, inserire il valore 0,6 nel campo corrispondente a “Prezzo_Stimato_Metallo” (Q10). Fare clic su “OK” per salvare lo scenario.

• Ripetere gli ultimi due passaggi per creare gli scenari “Prezzo Metallo Alto” e “Prezzo Metallo Molto Alto” con il valore di “Prezzo_Stimato_Metallo” (Q10) rispettivamente a 1,2 e 1,6.
• Ora che abbiamo creato entrambi gli scenari, fare clic su “Riepilogo dello scenario” (Summary) nel riquadro “Scenario Manager”.

• Nella finestra “Riepilogo dello scenario”, selezionare “Costo_Totale_Metallo” (Q12) nel campo “Risultato delle celle”. Fare clic su “OK”.

In questo modo, è stato creato un report di sintesi con Scenario Manager che presenta il costo totale del metallo per gli scenari di prezzo basso, alto e molto alto, permettendo una facile analisi e confronto.