Project instruction (2)for excel assignment PDF

Preview

Summary

Project instruction (2)for excel assignment. making a deflection calculator with excel;...

Description

Subject No. 48221

ENGINEERING COMPUTATIONS

Autumn 2021

Instructions for the Individual Group Project This assessment focuses on individual and group work in which students must learn to collaborate with each other to achieve the tasks given. You will be provided with four activity sessions throughout the semester. These sessions will give you an opportunity to meet up in an allocated time for you to carry out your group tasks. The entire assessment is divided into two Projects running through the semester with two corresponding assessments. Students need to review hands-out notes and do some research to complete projects 1 and 2. The list of students of Group Project will be announced around week 6. The key dates are given as follows: Key Dates for Group Project Project tasks Project 1 - Excel (individual project) Project 2 - VBA (Group project)

Mark distribution

Project task release

Submission deadline

Peer-review deadline

30

Week 2

23:59pm 9 -April

-

40

Week 7

23:59pm on Friday of Week 11

23:59pm on Friday of Week 11

Project 1 is designed as individual work; each student needs to complete their own project 1 and submit it before the due date. Peer review within each group is required after project submission in project 2. A peer review of group contribution is required in project 2. There is a late submission penalty of 20% on project 1 and project 2

Instructions for the Project 1 (Individual Submission) Project 1: EXCEL (30 marks) You are required to create an Excel sheet that will allow a user to calculate the deflection of a fixedfixed end beam based on the inputs given by the user. The formula for beam deflection calculations can be found online, no prior knowledge of mechanics of solid required. For project 1 is an individual submission for all students.

Project 1 – PART 1 The initial part will require your Excel sheet to calculate the cross-sectional properties (i.e. Centroid, 2nd moment of area) of a beam based on its sectional information (dimensions and shapes) and inputs from a user. The user interface will allow the user to choose a cross-sectional shape from a cell (it has a drop-down menu). Cross-sectional shapes must include a circle, rectangle, tube etc. In addition, Isection beam, T-section beam and more can be considered. Once all the parameters of the cross-section properties are entered, Excel will automatically calculate all cross-sectional properties, including Centroid and 2 nd moment of area, which will be used for the 2nd part of this assignment.

Project 1 – PART 2 This part will require your Excel sheet to assign materials attribute to the beam . The user interface will allow the user to choose a type of material from a cell (it also has a drop-down menu). Examples should include steel and concrete. User will be able to define the properties of selected material such as the density of materials, modulus of elasticity, etc. from published books/journal or internet search engine. 1

Subject No. 48221

ENGINEERING COMPUTATIONS

Autumn 2021

Project 1 – PART 3 This part of the assignment builds on the information obtained from part 1 and part 2. Type of load configurations should be available for user are:   

Uniformly distributed load Point load at any given location Uniformly varying load

The user will also be able to define the length and loading position through Excel. Once all this information is entered, Excel will automatically compute the deflection along the fixed-fixed end beam and present the results of the maximum beam’s deflection and deflection at any point. Hint: Formulae for beam deflection http://home.eng.iastate.edu/~shermanp/STAT447/STAT %20Articles/Beam_Deflection_Formulae.pdf.

is

available

online

NEEDS to insert the link for the new equations file

Submission of the Project 1 project Students are required to submit their own individual work through UTSOnline before due date. The project must use the provided template (download from UTSOnline)

Marking Criteria Criteria

Mark distribution

Functionality

10/30

Innovation

10/30

Presentation

10/30

Remark Correct results, workable, debugged, robust, crashproof. For example, robust against incorrect input values, consistent unit calculations Demonstration of applying skills/knowledge innovatively, additional features. For example, deflection graphs, conditional cells/images Logical, clear, concise and user-friendly interface. For example, consistent unit labels, colour coded cells, tool tips et al

2

Subject No. 48221

ENGINEERING COMPUTATIONS

Autumn 2021

Instructions for Project 2 (Group Submission) Project 2: VISUAL BASIC (BASIC PROGRAMMING) (40 marks) Congratulations to you and your group for completing individual project one. However, your client soon came to realise that they want to continue the development work to create a powerful and sophisticated Excel spreadsheet utilising VBA programing and Userforms to accommodate the spreadsheet’s complicated and flexible configurations, allowing them to calculate the deflection and bending moment of a fixed-fixed beam. As the client now wants more features from the spreadsheet, in addition, to enhance the user interface, you must use both VBA and Userforms (You cannot do calculations on the spreadsheets, i.e. Calculations done within cells). Note, you can use Worksheet Function in VBA. Key Dates for Group Project Project tasks

Mark distribution

Project task release

Submission deadline

Peer-review deadline

Project 2 - VBA (Group project)

40

Week 7

23:59 on Friday of Week 11

23:59 on Friday of Week 12

For the group project 2, students must work in a team to complete this task; good communication between team members is important. You must complete an individual peer review using the provided online sheet . The maximum contribution reported should not be more than 30% more than the average percentage breakdown of the group (i.e. 80% to 20% contribution), except students who failed to contribute to the project by the submission date and receive 0%. The group project is valued at 30% and the peer-review assessment of valued 10%. Note: Each group is allowed to submit one project only.

Project 2 – PART 1 As a group of engineers with basic Visual Basic skills, you are asked to provide your client with a user-friendly macro-enabled Excel spreadsheet that can be used to calculate the deflection and bending moment of a fixed-fixed beam. The spreadsheet will provide a user-friendly interface to allow the client to: i) ii) iii)

iv)

define the type of beam: fixed-fixed beam define cross-sectional properties of a given beam structure: for example, rectangular, triangle, round hollow tube, circular, I-section, T-section, triangle, et al define a complex cross-sectional shape. e.g. combining multiple standard cross-section shapes in part ii) to create new cross-section with the corresponding second moment of inertia and centroid. Select from standard material parameters or define their own material parameters (modulus of elasticity, density, Poisson’s ratio)

Once the entire cross-section has been defined, the Excel spreadsheet will calculate the overall second moment of inertia via a button. Challenges (Optional):  

An image will automatically be produced when the various shapes are added. More than one material can be added to the shape in this Excel sheet.

Project 2 – PART 2 (Maximum Bending Moment and Deflection) 3

Subject No. 48221

ENGINEERING COMPUTATIONS

Autumn 2021

Similar to part 1 of the group project, this part builds on the information obtained from part 1. Your clients are bridge engineers who want to calculate the maximum deflection and bending moment of their beams. A button on the main page will allow the users to open a new interface to define the load and properties of the beam so that they can calculate the deflection and bending moment of the beam. The user will be given the option to choose any of the following load configurations on the beam.   

Concentrated point load Uniformly distributed load Uniformly varying load

Once all this information is entered, the Userform program will automatically compute and present the results of the beam’s deflection and bending moment. Hint: Formulae for fixed-fixed beam and cantilever beam can be found online. Challenge (Optional): Plot the shear force diagram and the bending moment diagram. Contributes to innovation marks

Project 2 – PART 3 (Generate Report) Once the cross-section and the maximum bending moment and deflection of the beam have been defined, the user will be given a choice to press a button to automatically generate and save an A4 printable report (PDF) based on the input and calculations produced in this Excel sheet. This report will summarise the parameters that the user has put into Excel and display the results in a simple way for the client to interpret.

Limitations You must not use the excel spreadsheet (i.e. any cells on the spreadsheet) to do any of the calculations (i.e. no formulas in cells).

Submission of the group project At the end of part 2 of the group project needs to submit a project report for assessment. Each group submits only one report (softcopy) at each stage of the project through UTSOnline. A report template (in Excel format) is provided (downloadable from UTSOnline) and must be used for the report submission (or the submission is invalid). All group members must complete individual peer review within each group. Any group member/s fails to contact and contribute to the project may receive 0% in project 2 of the group project. Group members need to provide brief feedback in the comments cell of the peer review sheet to describe the issue.

Assessment of the group project 2 1. Teaching academics will assess the group project and give a group mark. 2. Mark of an individual member is to be decided by their own group mark, and the contribution factor derived from the peer-review system using the online peer-review form. 3. All individuals in a group must use the provided online sheet to assess their group members. The group project is valued at 30% and the peer-review assessment of valued 10%. Within a group, a student who did not complete individual peer review will get 25% penalty for project 2 , and, all other members will only get 80% of the group mark for their individual mark.

4

Subject No. 48221

ENGINEERING COMPUTATIONS

Autumn 2021

Marking Criteria Criteria

Mark distribution

Remark

Functionality

10/40

Correct results, workable, debugged, robust, crash-proof

Innovation

10/40

Demonstration of applying skills/knowledge innovatively, additional features

Presentation

10/40

Logical, clear, concise and user friendly interface

Peer-Review

10/40

Peer-review assessment sheet available online

5...