technical report

Spreadsheet and Word Processing

(Individual Assignment)

We want to construct a box with a height, 𝐻, and has a square base area with a base length 𝐿. The material used to build the top and bottom faces costs 0.2 $/m2 and the material used to build the sides costs 0.15 $/m2. In this assignment, you need to formulate the required equations and use Microsoft Excel to find an optimized solution for the box size and cost. Then use Microsoft Word to write a report about this optimization process and discuss your results.

A. Formulate the relation of the cos for different volumes (𝑉) and height to length (𝐻/𝐷) ratios.

B. For the various height to length Ratio, 𝐻/𝐷, and several can volumes, 𝑉, as shown in the table below. Calculate the box cost and fill this table automatically by using Microsoft Excel.

					Volume in Liter
		0.5	1	2	5	10	15	20	25
Height / Diameter	0.25	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	0.5	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	0.75	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	1	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	1.25	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	1.5	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	1.75	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	2	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	2.25	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	2.5	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	2.75	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	3	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	3.25	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	3.5	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	3.75	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost
	4	Cost	Cost	Cost	Cost	Cost	Cost	Cost	Cost

C. Plot the cost of each box volume as a function of height to length ratio, H/D in a single figure.

D. What is the best H/D ratio that give you the minimum cost? Check your results and write about the best ratio in your report.