Academic Year 2019/20
Module Code: BNM819
Module Name: Advanced
Spreadsheets and Databases
Module Leader: Dr
Advanced Spreadsheet Assignment
The assessment for BNM819
is made up of two components, as shown in the table below.
You will receive full details
of each assessment component during the lecture programme. You will also be
given additional information and guidance at group and individual level as the
Design and build simple custom/bespoke information
systems using a tool such as MS Excel in combination with other tools such as
Select and apply a range of techniques in order to
prepare data for analysis (data cleansing, import, data conversion, etc.)
Use sophisticated functions/features such as macros
and Visual Basic for Applications in order to enhance applications you create
Select and apply a wide range of techniques in order
to analyse a variety of business decisions
A small computer
manufacturing company has commissioned you to create a worksheet that can be
used to support financial decision making. You will design, create and test the
The worksheet will take the
form of a cash flow forecast and will be used to support what if? questioning,
such as the impact of a sudden increase in the cost of components.
In planning your application,
it is essential that you make your worksheet as flexible as possible. In order
to encourage good design, keep in mind that:
You will be given the information needed to
construct your worksheet as you work.
Some of the Information you are given may change
Before your work is submitted you will be given a
list of questions/problems you will need to solve using your worksheet.
Your target user is an
experienced manager who has very little knowledge of spreadsheets. This means
your application must be intuitive and very easy to use.
You are taking the role of
a developer, so you are expected to produce work of a professional standard.
This means your application must be robust and should follow any appropriate
conventions and/or standards.
Your application on disk or memory stick
2. An account of
how the application was designed and a guide to its key features. The account
should also include annotated screenshots to illustrate some of the features
you feel best demonstrate your skills.
3. Your responses
to the questions/problems given to you after the construction of the worksheet.
You must provide evidence (e.g.
screenshots) that your worksheet was used to determine your responses.
4. A test plan
Detailed test results
Your completed application
At least one fully working UserForm
Use of cell protection, data validation and
Clear documentation of VBA code; code must also be
Clear and uncluttered layout; user interface design
should cater for the target user
Use of appropriate instructions and error messages
Ability to import or update relevant data from a database
or web page.
Good use of Excel’s advanced features e.g. charts, formulas,
Good use of at least one advanced technique demonstrated
over the course of the module (e.g. Monte Carlo Simulation)
A dashboard of your own design – you will need to
justify your design and your choice of information to be displayed to users.
Marks will be awarded in
five main areas:
Fitness for purpose – does the application meet
Overall design - how well does the application
cater for the target user?
Robustness – how well does the application deal
with errors or problems e.g. wrong data entered?
Technical – does the application demonstrate
professionalism with regard to design, coding, testing, etc.?
Other – quality of documentation, test plan,
responses to questions/problems, etc.
Full marking guidelines are
included at the end of this document.
The assignment must be submitted
no later than 12pm on Tuesday 14/01/2020.
All assignments will be
checked using the TurnItIn system. Other measures are also taken to detect
collusion and/or plagiarism. Put simply, don't risk it.
At least one of your lectures will cover the
assignment in depth.
Each portfolio task covers knowledge and skills
that relate to the assignment
You can contact your tutor for information and
OI&M Group, ABS278, Ext. 5344
E-mail: p[email protected]
Office hours: see WASS
asking a question, please check to see if it has already been answered in the
module handbook or the module message board.