Description

Coursework Title:

Advanced Spreadsheet Assignment

Introduction

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 module progresses.

Learning Objectives Tested

·         Design and build simple custom/bespoke information systems using a tool such as MS Excel in combination with other tools such as SQL Lite

·         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

The Task

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 worksheet.

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.

Design Issues

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 over time.

·         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.

Deliverables

1.     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

5.     Detailed test results

Required Features/Functionality

Your completed application must include:

·         At least one fully working UserForm

·         Use of cell protection, data validation and conditional formatting.

·         Clear documentation of VBA code; code must also be formatted correctly

·         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, macros, etc.

·         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.