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

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.

• In the multifunction bar (Ribbon), click on the “Data” tab and select “Goal Seek” in the “Analysis Tools” group:

• 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.

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.

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

• Select the interval C8:L19.

• In the multifunction bar (Ribbon), click on the “Data” tab and select “Data Table” in the “Analysis Tools” group:

• 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.”

(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.

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.”

• 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".

• Let’s go to the multifunction bar (Ribbon) “Data” and click on “Solver” in the “Analysis” group.

• 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.

• 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.

• 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.

• 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.

• In the results dialog box, we click “Keep Solver Solution” (“Keep Solver Solution”) and then “OK” to apply the results to the worksheet.

• Repeat the “Solver” for the surface area in m² (C3), where the formula is:
  ((2PI()C1C1)+(2PI()C2C1)) / 10000

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.

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

• 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”

• 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

• 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“

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.

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.

• 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.”

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.

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)

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)

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.

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.”

• In cell Q11, enter the formula: “=C3*Price_Estimated_Metal”

• In cell Q12 enter the formula: “=Metal_Cost_Can*S6”

• Go to the “Data” tab in the multifunctional bar (Ribbon) and click on “Scenario Manager” in the “Forecast and Analysis” group (“Forecast”).

• In the “Scenario Manager” pane, click “Add” to create a new scenario.

• 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.

• 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.

• In the next window, enter the value 0.6 in the field corresponding to “Price_estimated_Metal” (Q10). Click “OK” to save the scenario.

• 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.

• In the “Scenario Summary” window, select “Cost_Total_Metal” (Q12) in the “Cell Result” field. Click “OK.”

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.