Optimizing can production: a case study of Data Analysis in Excel

Tools used in this project:
Excel

In business, optimization of production processes is critical to reducing costs and improving efficiency. This case study will examine how a beverage company can optimize can production using several Excel functions.

We will discover how Data Analysis skills can help solve complex problems and make meaningful improvements in the real world.

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

N.B. Click on the titles in the dark grey boxes to access the guided path on solving the various questions. Below each box is an image of the Excel sheet with the instructions for that specific question applied (to enlarge the image, click on it). After the article, you can download the Excel files, the empty starting one and the one with all the steps executed.

Analysis Objective 1.7

Calculation of optimal can height (Goal Seek)

A beverage company produces cans to package its beverages. The current mould used for the top of the can has a radius of 3.5 cm.

To solve this problem, we need to use Excel’s Goal Seek function. The goal is to find the optimal height of the can (cylinder) that can hold 375 ml of beverage, having a radius of 3.5 cm. We will calculate the volume of the can using the cylinder formula: pi * radius^2 * height.

Follow these steps:

  • Calculate the desired volume in millilitres and convert it to cubic centimetres: 1 ml equals 1 cm³, so 375 ml equals 375 cm³.
  • In cell C5, enter the formula for the volume of the can: “=PI()*C1*C1*C2“.
  • Cell C1 contains the radius (3.5 cm), and cell C2 will have the height we are looking for.
Goal Seek
  • In the multifunction bar (Ribbon), click on the “Data” tab and select “Goal Seek” in the “Analysis Tools” group:
Goal Seek
  • In the “Goal Seek” dialog box, set the following parameters:
    • “Set cell”: select cell C5, which contains the volume of the can.
    • “To value”: enter the desired value of the volume, which is 375 cm³.
    • “By changing cell”: select cell C2, which contains the height of the can.
    • Click “OK” to start Goal Seek.
Goal Seek

Excel will change the height in cell C2 until the desired volume of 375 cm³ is reached in cell C5. Once the Goal Seek process is completed, the correct can height will be displayed in cell C2. This value represents the height required to hold 375 ml of beverage with a radius of 3.5 cm.

Goal Seek
Calculation of optimal can height (Goal Seek).
YouTube video
Analysis Objective 2.7

Analysis of can size combinations (Data Table)

The company wants to examine the various cases where cans of different sizes with radii between 2 and 6 cm and heights between 7 and 12 cm can hold. Consider 0.5-cm increments for both the radius and height of the can and construct a Data Table.

with “Data Table”

To explore various cases of cans of different sizes, we will construct a data table (Data Table) that considers 0.5 cm increments for both the radius and height of the cans.

  • In cell C8, enter the calculation for the volume used in cell C5:
    =PI()*C1*C1*C2
Analisi delle combinazioni di dimensioni delle lattine con Data Table
  • Select the interval C8:L19.
Analisi delle combinazioni di dimensioni delle lattine con Data Table
  • In the multifunction bar (Ribbon), click on the “Data” tab and select “Data Table” in the “Analysis Tools” group:
Analisi delle combinazioni di dimensioni delle lattine con Data Table
  • In the “Data Table” dialog box, set the following parameters:
    • impostare con “$C$1” per “cella di input riga” e “$C$2” per “cella di input colonna”.
    • click “OK.”
Analisi delle combinazioni di dimensioni delle lattine con Data Table
Analisi delle combinazioni di dimensioni delle lattine con Data Table
Analysis of can size combinations (Data Table)
YouTube video
(alternative) without “Data Table”

To show the various cases of cans with radii between 2 and 6 cm and heights between 7 and 12 cm in 0.5 cm increments for both sizes, these steps must be performed:

  • In the spreadsheet, radius values have already been entered in row 8 (cells D8 to M8), and height values are in column C (cells C9 to C19).
  • Now, you need to calculate the volume for each combination of radius and height. Enter the volume formula (pi * radius^2 * height) in cell D9. It uses the PI() function and the appropriate absolute and relative references for radius and height. The formula is as follows: =PI()*D$8*D$8*$C9
  • Copy the formula to cell D9 and paste it into the entire cell range from D9 to M19. Excel will calculate the volume for each combination of radius and height in the table.

The newly created data table shows the volume of cans with different radii (2-6 cm) and heights (7-12 cm) in 0.5 cm increments. This table allows the company to quickly examine the various cases for cans of different sizes.

YouTube video
Analysis Objective 3.7

Reducing the cost of production by minimizing the surface area of cans (Solver)

The company is considering reducing the cost of can production, which means minimizing the surface area of the can. Because a smaller surface area implies a lower cost of metal, the company aims to minimize the surface area of the cans.

To reduce the cost of the metal used in can production, we will use the Solver to determine the size of the can (radius and height) to minimize the surface area while ensuring they can still hold 375 ml. We will calculate the surface area of the cans with the cylinder formula: (2 * pi * radius * height) + (2 * pi * radius^2).

  • Make sure that Solver is enabled on Excel: Let’s go to the tab “File” > “Options” > and “Additional Components” > We select “Excel Add-ins” in the drop-down menu, and we click on “Go…” > ensuring that “Solver Add-in” is set and we click on “OK.”
reducing the cost of production by minimizing the surface area of the cans (Solver)
  • In the worksheet, enter the formula for surface area in cell C4: “=(2*PI()*C1*C1)+(2*PI()*C2*C1). ” Also, in cell C5 make sure that the formula for the volume is present: “=PI()*C1*C1*C2".
reducing the cost of production by minimizing the surface area of the cans (Solver)
  • Let’s go to the multifunction bar (Ribbon) “Data” and click on “Solver” in the “Analysis” group.
reducing the cost of production by minimizing the surface area of the cans (Solver)
  • In the Solver dialog box, we set the following parameters:
    • Set Objective: we select cell C4 (Surface Area) to minimize the surface area.
    • Same as (To): we choose “Min” to minimize the surface area.
    • Changing Cells (By Changing Variable Cells): we select cells C1 and C2, as we want to find the optimal radius and height.
reducing the cost of production by minimizing the surface area of the cans (Solver)
  • Let’s click the “Add” button to set the constraints:
  • Constraint 1: We select cell C5 (Volume) for the first box, choose “=” in the middle box, and enter 375 in the third box. This constraint ensures that the can still contain 375 ml.
reducing the cost of production by minimizing the surface area of the cans (Solver)
  • Constraints 2 and 3 (optional but helpful): additional constraints for radius and height can be added if you wish to limit the values to reasonable ranges. For example, we might want to limit the radius (C1) and height (C2) to values greater than 0.
  • We click “OK” to close the constraints window and return to the Solver dialog box.
reducing the cost of production by minimizing the surface area of the cans (Solver)
  • We click on “Solve” to start Solver. Excel will find the optimal radius and height that minimizes the surface area of the can while meeting the constraints set.
reducing the cost of production by minimizing the surface area of the cans (Solver)
  • In the results dialog box, we click “Keep Solver Solution” (“Keep Solver Solution”) and then “OK” to apply the results to the worksheet.
reducing the cost of production by minimizing the surface area of the cans (Solver)
  • Repeat the “Solver” for the surface area in m2 (C3), where the formula is:
    ((2PI()C1C1)+(2PI()C2C1)) / 10000
reducing the cost of production by minimizing the surface area of the cans (Solver)

Using “Solver,” we found the optimal can size (radius and height) that minimizes the surface area while ensuring the can contains 375 ml of liquid.

Reducing the cost of production by minimizing the surface area of cans (Solver)
YouTube video
Analysis Objective 4.7

Calculating the number of cans in a cubic container

The company packs the cans into cubic containers measuring 120 cm x 120 cm x 120 cm. We will mathematically and practically calculate the number of cans that can fit in the container, rounding down the values obtained.

To solve the problem, it is necessary to follow these steps:

  • Calculate how many cans can be packed in the crate mathematically:
    • To do this, we will use the following formulas (cells R2, R3, and R4):
      • R2: “=P2/(2*$C$1)”- calculates the number of cans along the length of the crate
      • R3: “=P3/(2*$C$1)” – calculates the number of cans along the width of the case
      • R4: “=P4/C2” – calculates the number of cans along the height of the crate
Calcolo del numero di lattine in un contenitore cubico
  • Calculate the total number of cans from a mathematical point of view:
    • We will use the following formula (cell R6) to calculate the total number of cans from a mathematical point of view:
      • R6 (Total cans, mathematical): “=R2*R3*R4
Calcolo del numero di lattine in un contenitore cubico
  • Calculate how many cans can be packed in the crate from a practical point of view:
    • To do this, we will use the following formulas (cells S2, S3, and S4):
      • S2: “=FLOOR.MATH(R2)” – round down the number of cans along the length of the case
      • S3: “=FLOOR.MATH(R3)” – round down the number of cans along the width of the case
      • S4: “=FLOOR.MATH(R4)” – round down the number of cans along the height of the case
Calcolo del numero di lattine in un contenitore cubico
  • Calculate the total number of cans from a practical point of view:
    • We will use the following formula (cell S6) to calculate the total number of cans from a practical point of view:
      • S6 (Total cans, practical): “=S4*S3*S2
Calcolo del numero di lattine in un contenitore cubico

After entering the values of the case dimensions (120 cm * 120 cm * 120 cm) into the cells P2, P3 and P4 and the radius and height of the can in cells C1 and C2, we will get the full cans from the mathematical and practical point of view in cells R6 and S6.

Calcolo del numero di lattine in un contenitore cubico
Calculating the number of cans in a cubic container
YouTube video
Analysis Objective 5.7

Minimization of waste in the cubic container (Solver)

Using the Solver, we will determine the size of the cubic container that minimizes the difference between the mathematically calculated and actual number of cans (referred to as “potential cans”) to reduce waste.

To minimize the number of “potential cans” and reduce waste, we will again use Excel’s Solver (Solver). In this case, our goal will be to minimize the value in cell R7, which represents the difference between the total number of cans from a mathematical point of view and that from a practical point of view.

Here are the steps to follow:

  • Select “Data” in the multifunctional bar (Ribbon) and then click on “Solver” in the toolbar.
Minimizzazione degli sprechi nel contenitore cubico (Solver)
  • In the “Solver” dialog box, set the following parameters:
    • Set the target: select cell R7 (Potential Cans).
    • Same as: select “Min“.
    • Modifying cells: insert range P2:P4 (length, width, and height of boxes).
    • Click on “Add constraint” to add the following constraints:
      • P2 >= 1 (case length must be greater than or equal to 1)
      • P3 >= 1 (case width must be greater than or equal to 1)
      • P4 >= 1 (case height must be greater than or equal to 1)
    • Ensure the resolution method is set to “GRG Nonlinear” and click “Solve.”
Minimizzazione degli sprechi nel contenitore cubico (Solver)

Excel will find the optimal solution that minimizes the number of “potential cans.” Take note of the optimal values for Length (P2), Width (P3), and Height (P4). Remember that the results obtained may be fractional, so you may need to round them up or consider them appropriately depending on the practical needs of the actual situation.

Minimizzazione degli sprechi nel contenitore cubico (Solver)
Minimization of waste in the cubic container (Solver)
YouTube video
Analysis Objective 6.7

Metal price estimation considering different scenarios (SUMPRODUCT)

The company wishes to estimate the price of the metal used in cans by considering different probabilities associated with price changes. We will use the SUMPRODUCT function to calculate the estimated metal price under different scenarios.

To calculate the estimated metal price using SUMPRODUCT, we must first enter the different possible metal prices and their corresponding probabilities into the cells of the worksheet.

The metal’s current price is 0.8, yet there is only a 0.4 probability of it occurring.
There is a 0.1 probability that the metal price will double and a 0.1 probability that the price will halve.
There is also a 0.2 probability that the metal price is the average between the current and halved prices.
There is also a 0.2 probability that the metal price is the average between the current and doubled prices.

  • In cell Q17, enter the current metal price: 0.8.
  • In cells Q15, Q16, Q18, and Q19, enter the calculated price values based on the given probabilities:
    • Q15 (Very low price): “=Q17*0,5
    • Q16 (Low price): “=AVERAGE(Q15;Q17)
    • Q17 (current price): 0,8
    • Q18 (High price): “=AVERAGE(Q17;Q19)
    • Q19 (Very high price): “=Q17*2
  • Enter the corresponding probabilities in the cells R15, R16, R17, R18, and R19:
    • R15: 0.1 (probability of the price halved)
    • R16: 0.2 (probability of the average between the current price and the halved price)
    • R17: 0.4 (probability of current price)
    • R18: 0.2 (probability of the average between the current price and the doubled price)
    • R19: 0.1 (probability of the price doubled)
Stima del prezzo del metallo considerando diversi scenari (SUMPRODUCT)

Use the SUMPRODUCT function to calculate the estimated metal price by multiplying each price by its corresponding probability and summing the results. Enter the following formula in the cell Q10: =SUMPRODUCT(Q15:Q19;R15:R19)

Stima del prezzo del metallo considerando diversi scenari (SUMPRODUCT)

This formula multiplies each metal price by its corresponding probability and sums the results to obtain the estimated cost. The resulting value in cell Q10 represents the estimated price of the metal, taking into account the different probabilities given.

Stima del prezzo del metallo considerando diversi scenari (SUMPRODUCT)
Metal price estimation considering different scenarios (SUMPRODUCT)
YouTube video
Analysis Objective 7.7

Presentation of the total cost of metal under different price scenarios (Scenario Manager)

Finally, we will use the Scenario Manager to present the total cost of the metal to management, considering different price scenarios such as low (0.6), high (1.2), and very high (1.6).

Company management would like to assess the impact of the total cost of metal under different metal price scenarios. Use the Manager Scenario to present the total cost of metal (Q12) to management, including the low = 0.6, high = 1.2, and very high = 1.6 price scenarios.
When finished, create a summary report that presents all three scenarios in a new sheet.

To use the Scenario Manager to present the total cost of metal (Q12) with the low (0.6), high (1.2), and very high (1.6) price scenarios, follow these steps:

  • Assign cell Q10 the name “Price_Estimated_Metal,” Q11 “Cost_Metal_Can,” and Q12 “Cost_Total_Metal.”
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • In cell Q11, enter the formula: “=C3*Price_Estimated_Metal
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • In cell Q12 enter the formula: “=Metal_Cost_Can*S6
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • Go to the “Data” tab in the multifunctional bar (Ribbon) and click on “Scenario Manager” in the “Forecast and Analysis” group (“Forecast”).
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • In the “Scenario Manager” pane, click “Add” to create a new scenario.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • In the “Add Scenario” window, enter the scenario’s name, such as “Low Metal Price.” In the “Variable Cells” field, enter the reference to the metal price cell, i.e., “Estimated_Metal_Price” (Q10), and click OK.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • In the following warning, press OK: It is indicated that the formula in cell Q10 (Estimated Metal Price) will be removed, so remember this change should you need to reuse the Excel sheet for future processing.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • In the next window, enter the value 0.6 in the field corresponding to “Price_estimated_Metal” (Q10). Click “OK” to save the scenario.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • Repeat the last two steps to create the “High Metal Price” and “Very High Metal Price” scenarios with the value of “Estimated_Metal_Price” (Q10) at 1.2 and 1.6, respectively.
  • Now that we have created both scenarios click “Scenario Summary” in the “Scenario Manager” pane.
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
  • In the “Scenario Summary” window, select “Cost_Total_Metal” (Q12) in the “Cell Result” field. Click “OK.”
Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)

Thus, a summary report was created with Scenario Manager that presents the total cost of metal for low, high, and very high price scenarios, allowing easy analysis and comparison.

Presentazione del costo totale del metallo in base a diversi scenari di prezzo (Scenario Manager)
Presentation of the total cost of metal under different price scenarios (Scenario Manager)
YouTube video

Conclusion

In this case study, we demonstrated how Data Analysis skills in Excel can be used to solve real problems and optimize production processes. We analyzed various aspects of can production, such as calculating the optimal size, minimizing waste, and estimating the cost of metal by considering different price scenarios. This example demonstrates the importance of data analysis in making informed decisions and improving efficiency in the business environment.

If you need assistance in solving similar problems in your business or are interested in learning more about the potential of Data Analysis, please feel free to contact me. I can help you find optimal and customized solutions for your business.

Download Excel file

Excel Case Study Cans

FAQ


I hope this article has provided a clear view of Excel‘s potential in Data Analysis and the skills a Data Analyst can offer to improve efficiency and productivity in your business. Remember that they are there to offer you personalized advice and support, helping you make informed decisions and achieve your business goals. Please get in touch with me if you have any questions or want more information about my expertise and services. I will be glad to help you find solutions that best suit your needs and share my experience in the field of Data Analysis.


News tag:
Scroll to Top