Reference no: EM132449260

Coursework  Assignment 1

Coursework Brief

A Profit and Loss statement based on Sales Revenue and Costs is provided. Your solution should be calculated in an Excel spreadsheet using appropriate Excel formulae or functions (such as NPV) based on the module contents on spreadsheets.

You should submit a Word processed report following a typical report structure, to show the calculations and your own understandings of the relevant topics, by giving a detailed account of the methods you have used, the reasons you have chosen those particular methods, the meaning of the values calculated, and the conclusions you have drawn. The Excel spreadsheet is NOT required for submission, but snapshots of your Excel worksheet, zooming different portions of the worksheet, should be included in the Word report to demonstrate the formulae/functions in your calculations, as well as the Excel worksheet design, and to facilitate the explanation and discussion.

You must complete a Profit and Loss statement in which the Sales Revenue and Costs in the categories of Labour, Material and overhead are listed in the following table (all quantities in thousands of pounds, £).

	Year 1	Year 2	Year 3	Year 4	Year 5
Sales Revenue	700	650	500	450	570
Costs:
Labour	50	80	40	60	70
Material	130	170	140	190	150
Overhead	20	20	20	20	20

There is also an initial plant cost in Year 0 of 1000 (=£1,000,000), which is equally spread over the five years, producing the same depreciation each year.

Part 1) You need to first work out Total Costs, Trading Profit, Taxation and Net Profit for each of the five trading years. Use a taxation rate of 20%. Then calculate the Net Cash Flow, the Net Present Value using a discount rate of 10%, and finally the Internal Rate of Return. Explain and justify the formulae/functions used in the calculations, with specific focus on those highlighted in bold.

Part 2) Create suitable Excel Charts for two of the calculated values in (a). Then explain and justify the Chart types and formats used, and discuss some of the information obtained from the Charts.

Part 3) Write suitable functions (using =VLOOKUP, HLOOKUP and/or MATCH) in the table below, so that when entries in columns Cost Category and Year are entered, column Cost Value is automatically filled out. Use =IF() function to ensure that the 6 cells remain blank if no Cost Category or Year is entered. Explain and justify the functions used in the column Cost Value.

Coursework Assignment2

A list of requirements of a database is provided. Your solution should be the creation of an entity relationship diagram using Chen's notation, and a table relationship diagram from Access, based on the module contents on databases. You should submit a Word processed report following a typical report structure, to show the diagrams and your own understanding of the relevant topics, by giving a detailed account of how the requirements have been translated into the diagrams, the reasons you have chosen those particular methods, the meanings of the diagram components used, and the conclusions you have drawn.

You are NOT required to discuss any general theory of database design and modelling, and should focus on the specific problem given below. The Access database file is NOT required for submission, but snapshots of your Access database should be included in the Word report to demonstrate the steps in creating the table relationship in Access, as well as those zooming different portions of the entity-relationship diagram, to facilitate the explanation and discussion.

You must design a database system to maintain data associated with a company. The fifteen sentences below have been produced from a requirements elicitation exercise.

1) There exist several factories and sales offices in the company.

2) Each factory includes several production departments.

3) In each production department, one manager is in charge of its overall operation.

4) The same manager also oversees all workers working in that production department.

5) Some production departments can only produce product parts

6) Some other production departments can only produce finished products.

7) One production department is able to produce multiple product parts or finished products

8) Multiple production departments can produce the same product part or finished product

9) All finished products are made of several product parts.

10) Each product part can be used to produce multiple finished products.

11) Each factory owns a number of warehouses.

12) All finished products are stored in the warehouses before sales.

13) One warehouse cannot be used to store more than one finished product.

14) Each finished product is only sold by one sales office.

15) One sales office sells at least one finished product.

Part 1) Produce an entity-relationship diagram, using Chen's notation, which represents the domain of the company, and explain and justify each entity in the diagram and its type, as well as each relationship and its cardinality and participation constraint. Create and assign up to three attributes to each entity in the domain, and choose one attribute from each attribute type created to explain

Part 2) Create two tables in Access for two suitable entities chosen from your entityrelationship diagram in (a), by setting suitable fields (including primary and foreign key) and their data types and field properties (no record needed). Then create a table relationship in Access between the two tables created, represented by a relationship line drawn between the two tables with 1 or ∞ on two sides. Explain the tables and table relationship diagram produced, and the steps of creating them.