## Utilizing Spreadsheet Solver Methods To Determine Optimal Beam Geometry

Conference

2003 Annual Conference

Location

Nashville, Tennessee

Publication Date

June 22, 2003

Start Date

June 22, 2003

End Date

June 25, 2003

ISSN

2153-5965

Conference Session

Integrating Math in Mechanical Engineering Curriculum

Page Count

14

Page Numbers

8.1272.1 - 8.1272.14

DOI

10.18260/1-2--12003

Permanent URL

https://peer.asee.org/12003

684

#### Abstract NOTE: The first page of text has been automatically extracted and included below in lieu of an abstract

Session 1365 Utilizing Spreadsheet Solver Methods to Determine Optimal Beam Geometry

William K. Szaroletta Purdue University, West Lafayette, Indiana

Abstract: The spreadsheet solver method has proven very successful as students at both upper and lower division are experiencing meaningful single-criterion optimization in addition to finding optimization applications in their other coursework. This paper will describe optimization implementation and present some sample optimization results at both levels using spreadsheet solver method to drive subsequent solid modeling and finite element analysis (FEA).

Introduction: The author previously described in the ASEE 2002 Conference Proceedings the introduction of a suite of four optimization techniques into mechanics classes. The spreadsheet solver method has proven very successful as students at both upper and lower division are experiencing meaningful single-criterion optimization and also finding optimization applications in their other courses.

In the lower division mechanics class, a simply supported, rectangular cross-section beam with a central load was first introduced and solved using conventional analysis methods. After the students explore and understand the varying stress profile in the beam, the concept of the optimization objective function is introduced. In this beam, the objective is to produce a constant stress state on the highest stressed portions of the part. A spreadsheet solver is used to meet this objective subject to constraints for beam base and width in two modes. The first mode is a constant height beam with a varying base and the second is a constant base beam with a varying height. The students utilize the results of the spreadsheet solver for both beams to produce solid models which can be readily visualized.

In the upper division mechanics class, the students initially perform the same steps as the lower division class as a refresher. Additionally, they explore the beam shapes required for different loadings, highlighting the effect load has on the stress state within the part and required beam geometry. Subsequently, these students utilize their configure solid models for the loading cases to prepare FEA models of four basic beam types. The FEA stress states are compared with the theoretical stress states for these configurations in report and class presentations.

This paper describes the optimization introduction and presents some sample optimization results at both levels using spreadsheet solver to drive subsequent solid modeling and finite element analysis (FEA).

Analysis of Simply Supported Beam with Central Load: In a simply supported beam with a central load, P, (see Figure 1) the reaction forces can be determined using a free body diagram (FBD) and freshman-level Statics to be of equal magnitude (e.g. P/2) and direction (opposite of P). This elementary analysis can be utilized and extended to produce the Loading, Shear and Moment diagrams shown below in Figure 2. Of interest is the Moment Diagram for this loading case which, due to the “tent” shape, produces a “tent” shape stress curve shown in Figure 4 for constant cross-section beam constructions shown in Figure 3.

Proceedings of the 2003 American Society for Engineering Education Annual Conference & Exposition Copyright © 2003, American Society for Engineering Education

Szaroletta, W. (2003, June), Utilizing Spreadsheet Solver Methods To Determine Optimal Beam Geometry Paper presented at 2003 Annual Conference, Nashville, Tennessee. 10.18260/1-2--12003

ASEE holds the copyright on this document. It may be read by the public free of charge. Authors may archive their work on personal websites or in institutional repositories with the following citation: © 2003 American Society for Engineering Education. Other scholars may excerpt or quote from these materials with the same citation. When excerpting or quoting from Conference Proceedings, authors should, in addition to noting the ASEE copyright, list all the original authors and their institutions and name the host city of the conference. - Last updated April 1, 2015