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.