PROBLEM
Using Microsoft Excel to mathematically design and model a working crane with the highest DPI (Design Performance Index) whilst supporting the maximum load with the minimum material and manufacturing cost.
With our engineering science knowledge and the parameters set by the image on the right, we had to determine the best choice of length, cross-sectional area and material for each of the 5 beam members.
Our manufactured crane was to be tested against our mathematical model on points such as; accuracy of manufacturing and a lifted load test.
Diagram of the crane parameters
Solution
Final proposed CAD design, modelled in Siemens NX11
After 20+ design iterations, our final proposed design is shown on the right. Microsoft Excel was used to calculate joint and stress calculations as well as optimising the cross-sectional area for each of the beam members.
This design gave us the highest DPI of 42.2 with a maximum lifted load of 72 kg and counterbalance of 40 kg, with a safety factor of 2.
We really didn't want to over-engineer our crane, so we designed with the maximum loads at the maximum distances along the beams.
MATHEMATICAL Modelling IN EXCEL
Important calculations for our final design in Microsoft Excel
Examples of stress calculations for the main beams
Cross-sectional areas, second moment of inertia and material properties were vital in these calculations.
The true statements indicate that the beams were safe under the load stresses.
Joint D design and bolt calculations
Joint E - adjustable pulley design and bolt calculations
Final Design Proposal
Fully assembly model of our final design in Siemens NX 11
Manufacture
A full manufacturing plan was created prior to the manufacturing stage. Many metal fabrication techniques were used such as the milling machine, the Band saw and the Linishing machine.
Attaching the crane onto the testing block
Using the Milling Machine to drill holes
The main crane structure is completed!
Using the Band saw to cut the lengths of the beams
Crane Testing
The crane was able to lift 40 kg with a 20 kg counterbalance without any deformation or failures. To the right is a video of us testing our crane.
MY TEAM AND I AT THE END OF THE PROJECT
