engg help

CAN ANY OF YOU DO IT PERFECT ? PLEASE READ IT CAREFULLY AND ONLY MESSAGE ME IF YOU CAN DO IT PERFECT WITHIN MAX 15 HRS ?

EGR1013M Coursework 1 – Excel 2017-18

Page 1 of 4

STUDENT

NAME:

TUTOR NAME: Alex Borman

PROGRAMME: BEng/MEng (All Pathways)
MODULE CODE:
EGR1013M

MODULE

TITLE:
COMPUTING FOR ENGINEERS

SUBJECT: COURSEWORK 1: MICROSOFT EXCEL

COURSEWORK

TITLE:
MICROSOFT EXCEL SKILLS COURSEWORK

COURSEWORK

WEIGHTING (%):
50%

Issue Date:

04/12/2017
Due Date:

14/01/2018
Feedback Date:

2 weeks after hand-in

PERFORMANCE CRITERIA:

TARGETED LEARNING OUTCOMES

LO1 Use engineering software to model simple systems, and interpret the results
LO2 Design and write structured programs in a high-level language
LO3 Locate and use learning resources in the development of coding solutions
LO4 Manage their time effectively and work independently

Important Information – Please Read Before Completing Your Work

All students should submit their work by the date specified using the procedures specified in the Student Handbook. An
assessment that has been handed in after this deadline will be marked initially as if it had been handed in on time, but
the Board of Examiners will normally apply a lateness penalty.

Your attention is drawn to the Section on Academic Misconduct in the Student’s Handbook.

All work will be considered as individual unless collaboration is specifically requested, in which case this should be
explicitly acknowledged by the student within their submitted material.

Any queries that you may have on the requirements of this assessment should be e-mailed to aborman@lincoln.ac.uk. No
queries will be answered after respective submission dates.

You must ensure you retain a copy of your completed work prior to submission.

Page 2 of 4

COURSEWORK BRIEF:

This coursework will assess your abilities with Microsoft Excel, testing a range of skills developed during the
course of the lectures and supplementary tutorial activity.

This piece of work, being taught simultaneously with Statics for all full time students, will focus on an aspect of this
module and using Excel to take in data, process it and produces solutions to the question posed. For any students
not taking the Statics module this year, your ability to solve the statics problem is not being assessed and help will
be given in this task if difficulties are faced with this aspect of the coursework.

The specific question that will be considered is a Structural Analysis problem, as displayed below.

The Truss used to support a balcony is subjected to the loading shown. Using the method of joints, determine the
force in each member, stating also whether members are in tension or compression.

Test Data Set (these values will be varied when marked):

P1 = 60kN; P2 = 40kN; X1=4m; X2=4m; Y1=4m

Test data results:
FAD = 84.9kN (C); FAB = 60.0kN (T); FBD = 40kN (C); FBC = 60kN (T); FDC = 141 kN (T); FDE = 160kN (C)

Your task is to produce a spreadsheet capable of collecting values for the five variables and outputting the Forces
in each member, as well as whether they are in tension or compression.

Before you begin the work, place your student ID in cell A1 of sheet 1; the automated marking system can then
assign you the marks I give.

In order to assess your abilities in a variety of techniques, several specifications will need to be met.

1. A short introduction should greet the user with appropriate button(s) to respond/clear (i.e. “OK”, “Close”,
“Hide”, “Next”) and brief preliminary instructions on how to begin.

2. A region for data input produced that can be easily accessed to enter the five pieces of data. This should

allow:
a. Entry of data into a clear form every time it is begun
b. Correction of one piece of data without need to re-enter all of the others
c. Storage of data, automatic calculation of all forces without further intervention from the user and

closing/hiding/proceeding from the form when a button/link button is clicked.
3. Forces in all members should be calculated and these inserted into a clearly named sheet of your

workbook.
4. A data set (provided online) for extension vs load should be imported into Excel prior to submission and

hidden from users.
5. The imported data from step 4 should be plotted on an X-Y scatter graph, extension on the x axis and load

on the y axis. The graph should be clearly legible. No gridlines or legend should be present but an
appropriate trend line should be displayed. This should all be on a new sheet, not present when opening
the workbook and only displayed on the click of a button.

Page 3 of 4

On this same graph from step 5 should be displayed the extension taking place within member DC
according to the load being experienced at that time.

6. A method of projecting the effect of increased load on member CD should be provided (i.e. some method of
easily increasing the loads applied without overwriting the original data and reporting whether the
member has failed or not).

7. Also provided on the worksheet should be facility to erase all stored data (without removing any
formulae).

8. All input and calculated values should be exported into Word. Compressions or tensions in members
should be identified beneath the diagram in a simple table. The process of creating this should be
automated.
Note: captions for tables should be presented above the table; for figures captions should be presented
beneath.

9. No errors should be visible throughout the entire use of the workbook; the workbook should be designed
so that nothing vital such as formulae can be overwritten; the user can only input into the cells that you
intend. Workbook should have a professional feel.

Naming your files:

Submit your work as a .zip or .rar file with your, student number, the module code and Excel as the folder name
(i.e. 00001234EGR1013MEXCEL.rar).

Name the spreadsheet part of the submission as “Structural Analysis Calculator.xlsb” (Note: take care to save the
file as a binary workbook, otherwise your work will not be able to be seen or marked. .xlsb also enables macro
enabled workbooks to be used in a mail merge in conjunction with MS Word).

The filename of the Word report does not matter, as long as it is present within the same .rar or .zip file.

MARKING CRITERIA:

COURSEWORK WILL BE MARKED ACCORDING TO THE FOLLOWING UNIVERSITY CRITERIA.

90-100%: a range of marks consistent with a first where the work is exceptional in all areas;

80-89%: a range of marks consistent with a first where the work is exceptional in most areas.

70-79%: a range of marks consistent with a first. Work which shows excellent content, organisation and
presentation, reasoning and originality; evidence of independent reading and thinking and a clear and
authoritative grasp of theoretical positions; ability to sustain an argument, to think analytically and/or critically
and to synthesise material effectively.

60-69%: a range of marks consistent with an upper second. Well-organised and lucid coverage of the main points
in an answer; intelligent interpretation and confident use of evidence, examples and references; clear evidence of
critical judgement in selecting, ordering and analysing content; demonstrates some ability to synthesise material
and to construct responses, which reveal insight and may offer some originality.

50-59%: a range of marks consistent with lower second; shows a grasp of the main issues and uses relevant
materials in a generally business-like approach, restricted evidence of additional reading; possible unevenness in
structure of answers and failure to understand the more subtle points: some critical analysis and a modest degree
of insight should be present.

40-49%: a range of marks which is consistent with third class; demonstrates limited understanding with no
enrichment of the basic course material presented in classes; superficial lines of argument and muddled
presentation; little or no attempt to relate issues to a broader framework; lower end of the range equates to a
minimum or threshold pass.

35-39%: achieves many of the learning outcomes required for a mark of 40% but falls short in one or more areas.

30-34%: a fail; may achieve some learning outcomes but falls short in most areas; shows considerable lack of
understanding of basic course material and little evidence of research.

0-29%: a fail; basic factual errors of considerable magnitude showing little understanding of basic course material;
falls substantially short of the learning outcomes for compensation.

Page 4 of 4

When marking this work, the following criteria will be considered:

1. Introductory Message
Appropriate information provided? 4 Marks
Interactive response 2 Marks
6 Marks Total

2. User form
Ease of use 3 Marks
Clear of data every time? 3 Marks
Correct data requested? 5 Marks
Submits/stores data correctly? 3 Marks
Submits/stores individual variables without overwriting others? 2 Marks
Can be cancelled/hidden? 2 Marks
18 Marks Total

3. Calculations
No User intervention required 2 Marks
Calculations are all performed correctly? 9 Marks
11 Marks Total

4. Data Import
.csv file has been correctly imported and contents are hidden from user. 4 Marks
4 Marks Total

5. Graphing
Graph of imported data presented 3 Marks
Graph creation automated 4 Marks
Graph presented appropriately and as instructed 9 Marks
Point included to represent Member CD 3 Marks
19 Marks Total

6. Maximum Load Calculations
A method for varying loads is included, without losing the original user input data 5 Marks
The status (i.e. “OK” or “Fail”) of member CD is presented 3 Marks
8 Marks Total

7. Clear function
Automated clearing of stored data at the click of a button/link 3 Marks
3 Marks Total

8. Report
Contains labelled diagram of scenario with all results labelled (magnitude only,
not directions)?

7 Marks

Compressions/Tensions identified clearly. 4 Marks
Captions 3 Marks
Report generation is automated 2 Marks
16 Marks Total

9. Presentation
No errors should be visible 4 Marks
Limited user input to essential areas of workbook 4 Marks
Appropriate GUI & efficient methodology 7 Marks
15 Marks Total

Total Marks Available 100

Structural Analysis Solution

60 kN, P2 = 40 kN.

m

m m

60 kN

4 0 kN

60 kN

4 0 kN
84.853 kN

60 = 0

84.853 = 84.9 kN Ans.

60 kN(T)

84.853

4 0 = 0

40 kN(C )

60 = 0

60 kN(T)

40 84.853sin 45 0− =

141.42 kN 141 kN

84.853cos45 141.42cos45 0
OE
F+ − =

160 kN (C )

.

.

.

.

.

141.42 kN 141 kN

84.853 = 84.9 kN

60 kN(T) 60 kN(T)

40 kN(C ) 40 kN(C )

60 kN(T) 60 kN(T)

160 kN 160 kN (C )

SteelData.xlsx

Steel Data

Load / kN
Extension / mm

0
0

10
0.05

17
0.08

25
0.11

30
0.14

34
0.2

37.5
0.4

38.5
0.6

36
0.9

figure