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

Strumenti utilizzati:
Excel

Nel mondo degli affari, l’ottimizzazione dei processi produttivi è fondamentale per ridurre i costi e migliorare l’efficienza. In questo case study, analizzeremo come un’azienda produttrice di bevande possa ottimizzare la produzione di lattine utilizzando diverse funzioni di Excel.

Scopriremo come le competenze di Data Analysis possono aiutare a risolvere problemi complessi e ad apportare migliorie significative nel mondo reale.

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

N.B. Cliccare sui titoli nei riquadri in grigio scuro per accedere al percorso guidato su come risolvere i vari quesiti. Sotto ogni riquadro è presente l’mmagine del foglio Excel con applicate le istruzioni per quella specifica domanda (per ingrandire l’immagine, cliccarci sopra). Alla conclusione dell’articolo è possibile scaricare i file excel, quello vuoto di partenza e quello con tutti i passaggi eseguiti.

Analisi Obiettivo 1.7

Calcolo dell’altezza ottimale della lattina (Goal Seek)

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.
Goal Seek
  • Nella barra multifunzione (Ribbon), fare clic sulla scheda “Dati” e selezionare “Goal Seek” nel gruppo “Strumenti di analisi”:
Goal Seek
  • 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.
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.

Goal Seek
Calcolo dell’altezza ottimale della lattina (Goal Seek)
Analisi Obiettivo 2.7

Analisi delle combinazioni di dimensioni delle lattine (Data Table)

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
Analisi delle combinazioni di dimensioni delle lattine con Data Table
  • Selezionare l’intervallo C8:L19.
Analisi delle combinazioni di dimensioni delle lattine con Data Table
  • Nella barra multifunzione (Ribbon), fare clic sulla scheda “Dati” e selezionare “Data Table” nel gruppo “Strumenti di analisi”:
Analisi delle combinazioni di dimensioni delle lattine con Data Table
  • 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“.
Analisi delle combinazioni di dimensioni delle lattine con Data Table
Analisi delle combinazioni di dimensioni delle lattine con Data Table
Analisi delle combinazioni di dimensioni delle lattine (Data Table)
(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.

Analisi Obiettivo 3.7

Riduzione del costo di produzione minimizzando la superficie delle lattine (Solver)

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“.
riduzione del costo di produzione minimizzando la superficie delle lattine (Solver)
  • 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".
riduzione del costo di produzione minimizzando la superficie delle lattine (Solver)
  • Andiamo nella barra multifunzione (Ribbon) “Dati” e facciamo clic su “Solver” nel gruppo “Analisi”.
riduzione del costo di produzione minimizzando la superficie delle lattine (Solver)
  • 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.
riduzione del costo di produzione minimizzando la superficie delle lattine (Solver)
  • 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.
riduzione del costo di produzione minimizzando la superficie delle lattine (Solver)
  • 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.
riduzione del costo di produzione minimizzando la superficie delle lattine (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.
riduzione del costo di produzione minimizzando la superficie delle lattine (Solver)
  • 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.
riduzione del costo di produzione minimizzando la superficie delle lattine (Solver)
  • Ripetere il “Solver” per l’area superficiale in m2 (C3), dove è presente la formula:
    ((2PI()C1C1)+(2PI()C2C1)) / 10000
riduzione del costo di produzione minimizzando la superficie delle lattine (Solver)

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.

Riduzione del costo di produzione minimizzando la superficie delle lattine (Solver)
Analisi Obiettivo 4.7

Calcolo del numero di lattine in un contenitore cubico

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
Calcolo del numero di lattine in un contenitore cubico
  • 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
Calcolo del numero di lattine in un contenitore cubico
  • 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
Calcolo del numero di lattine in un contenitore cubico
  • 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
Calcolo del numero di lattine in un contenitore cubico

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.

Calcolo del numero di lattine in un contenitore cubico
Calcolo del numero di lattine in un contenitore cubico
Analisi Obiettivo 5.7

Minimizzazione degli sprechi nel contenitore cubico (Solver)

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.
Minimizzazione degli sprechi nel contenitore cubico (Solver)
  • 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”).
Minimizzazione degli sprechi nel contenitore cubico (Solver)

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.

Minimizzazione degli sprechi nel contenitore cubico (Solver)
Minimizzazione degli sprechi nel contenitore cubico (Solver)
Analisi Obiettivo 6.7

Stima del prezzo del metallo considerando diversi scenari (SUMPRODUCT)

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)
Stima del prezzo del metallo considerando diversi scenari (SUMPRODUCT)

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)

Stima del prezzo del metallo considerando diversi scenari (SUMPRODUCT)

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.

Stima del prezzo del metallo considerando diversi scenari (SUMPRODUCT)
Stima del prezzo del metallo considerando diversi scenari (SUMPRODUCT)
Analisi Obiettivo 7.7

Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)

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”.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • Nella cella Q11 inserire la formula: “=C3*Prezzo_Stimato_Metallo
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • Nella cella Q12 inserire la formula: “=Costo_Metallo_Lattina*S6
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • Andare alla scheda “Dati” nella barra multifunzionale (Ribbon) e fare clic su “Scenario Manager” nel gruppo “Previsioni e analisi” (“Forecast”).
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • Nel riquadro “Scenario Manager”, fare clic su “Aggiungi” (“Add”) per creare un nuovo scenario.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • 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.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • 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.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • Nella finestra successiva, inserire il valore 0,6 nel campo corrispondente a “Prezzo_Stimato_Metallo” (Q10). Fare clic su “OK” per salvare lo scenario.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • 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”.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • Nella finestra “Riepilogo dello scenario”, selezionare “Costo_Totale_Metallo” (Q12) nel campo “Risultato delle celle”. Fare clic su “OK”.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)

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.

Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)

Conclusione

In questo case study, abbiamo dimostrato come le competenze di Data Analysis in Excel possano essere utilizzate per risolvere problemi reali e ottimizzare i processi produttivi. Abbiamo analizzato vari aspetti della produzione di lattine, come il calcolo delle dimensioni ottimali, la minimizzazione degli sprechi e la stima del costo del metallo considerando diversi scenari di prezzo. Questo esempio dimostra l’importanza dell’analisi dei dati nel prendere decisioni informate e migliorare l’efficienza nell’ambito aziendale.

Se avete bisogno di assistenza per risolvere problemi analoghi nella vostra attività lavorativa o siete interessati ad approfondire le potenzialità di Data Analysis, non esitate a contattarmi. Sono a vostra disposizione per aiutarvi a trovare soluzioni ottimali e personalizzate per la vostra azienda.

Download file Excel

{{/if}}
{{else}}
{{ext}}
{{/if}}

{{{crop_title}}}

Size: {{bytesToSize size}}
Download{{#if openpdflink}}Preview {{else}}{{#if viewerlink}}Preview {{/if}}{{/if}}
{{/each}}
{{/if}}

Case Study Excel Lattine

FAQ


Spero che questo articolo abbia fornito una visione chiara delle potenzialità di Excel nell’ambito della Data Analysis e delle competenze che un Data Analyst può offrire per migliorare l’efficienza e la produttività nella vostra attività lavorativa. Ricordate che sono a vostra disposizione per offrirvi consulenza e supporto personalizzato, aiutandovi a prendere decisioni informate e a raggiungere i vostri obiettivi di business. Non esitate a contattarmi se avete domande o se desiderate ulteriori informazioni sulle mie competenze e i servizi offerti. Sarò lieto di aiutarvi a trovare le soluzioni più adatte alle vostre esigenze e di condividere con voi la mia esperienza nel campo della Data Analysis.


News tag:
Torna in alto