Retails and costs the company produce and deliver

Coursework Title:

Advanced Spreadsheet Assignment

Module Learning Outcomes Assessed:

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

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.

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

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

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

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

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

• 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.?

Assignment Support

• At least one of your lectures will cover the assignment in depth.

E-mail: p.petridis@aston.ac.uk

Office hours: see WASS (wass.aston.ac.uk)

3. Remember to carry out tasks in stages and show any intermediate steps when carrying out complex operations.

4. The assignment is based around the notion of a manufacturing company (MegaWidget International) that supplies precision metal components to European clients. As well as producing its own products, the company also imports components from China and other nations for resale.

4. Expand all Street data and correct errors e.g. “St” becomes “Street”

5. Correct all entries for Gender. Males should be coded “M” and females should be coded “F”.

Fig 1: Sales Data Table Layout

MegaWidget International is considering building a new facility so that it can manufacture more of the products it sells. The company has created designs for its own versions of product codes B, D and E.

Before the company makes a final decision about the new factory, they need to develop a financial model to determine if such a project is likely to be financially viable. You are required to build a cash flow forecast for the company that takes into account the information given in this document. You will also need to use the model to answer some basic questions the company’s managers have. The information you should include in the module is this:

• A government grant will contribute a lump sum of £45,000 in the first month of operation, then £4,000 per month for the rest of the first year.

• Sales are expected to follow the pattern seen in 2020.

If the cash flow demonstrates that the new facility is not financially viable, what changes could be made to make the project worthwhile. As an example, would changing the term of the business loan make a difference, or would a price increase help?

Interrogating The Data

Carry out some basic testing of your application and document the tests.

ADDENDUM

5. You can earn a little additional credit by producing and including a house style. Obviously, the house style must have been used when creating the worksheet.

6. In order to give you more guidance, a copy of the marking scheme that will be used is attached.

• How well does the application fit the user’s needs? As an example, does the user interface match how the user works or does it match how you work?

BNM819 - Advanced Spreadsheets