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

- Calculation of optimal can height (Goal Seek)
- Analysis of can size combinations (Data Table)
- Reducing the cost of production by minimizing the surface area of cans (Solver)
- Calculating the number of cans in a cubic container
- Minimization of waste in the cubic container (Solver)
- Metal price estimation considering different scenarios (SUMPRODUCT)
- Presentation of the total cost of metal under different price scenarios (Scenario Manager)
- Conclusion
- Download Excel file
- FAQ

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

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

- “Set cell”: select cell

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.

###### 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`

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

- impostare con “

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

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

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

- Set Objective: we select cell

- 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
^{2}(**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.

###### 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

- To do this, we will use the following formulas (cells R2, R3, and R4):

- 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`

”

- We will use the following formula (cell R6) to calculate the total number of cans from a mathematical point of view:

- 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

- To do this, we will use the following formulas (cells S2, S3, and S4):

- 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`

“

- We will use the following formula (cell S6) to calculate the total number of cans from a practical point of view:

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

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

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

- Set the target: select cell

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.

###### 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)

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.

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

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

### 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. They can help you find optimal and customized solutions for your business.

### Download Excel file

## Excel Case Study Cans

### Excel Case Study Cans start

### Excel Case Study Cans finish

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