MN-3526: Spreadsheets and Databases for Business Analysis
Coursework Assignment 1
Submission:
Assignments must be submitted electronically by 2pm on Wednesday 8 May 2024 via Turnitin on Canvas (see detailed information regarding submission, attached to the email).
Word Limit: The maximum word limit for this Coursework Assignment is 1,500 words (excluding references, tables, contents page, footnotes, charts, graphs, figures but including in-text references). The word count must be stated on the assignment cover sheet.
Notes on style. and word count, together with submission information, can be found in the Module Handbook.
Coursework Assignment Details:
Individual Assignment Brief
Available marks: 100
20 marks are available for the organisation, the writing quality and the overall quality of the assignment (see Marking Criteria for details).
80 marks are available for the procedure, explanation, understanding and evaluation of each of the individual questions (see Marking Criteria for details).
A Microsoft Access database (SalesForecastDatabase.accdb) for a fictional company is provided. The database details the projected sales figures for 40 products over a period 3 years and the associated costs (operational costs, procurement costs and initial onboarding cost) for each of the products. The yearly procurement cost of each product is fixed for each of the 3 years. The initial onboarding cost is a “Year 0” cost, for which depreciation does not need to be considered. All revenue and cost figures are in thousands of pounds (£’000s).
Each product has an associated salesperson who covers a particular country (i.e. each product is sold in only one country).
The information relating to the product sales, costs, and the associated salesperson ID are stored in the table called ProductRevenueAndCosts. Details relating to each salesperson are stored in the table called SalesPeople.
You have been recruited as a business data analyst for the company. You have been told that, over time, entries will be added to the database, but the database structure will not change. Therefore, any operations or tasks that you perform. must be able to work when new data is added.
Answer the following three questions.
Question 1:
You have been asked to create a table in Excel which allows the finance team to enter a product and a particular year and to display the forecasted sales revenue for the entered product and year.
a. Firstly, select all the required data from the database by writing an SQL query that retrieves all product IDs and their associated sales revenue figures for years 1 to 3. Show your query. Then create and display a table in an Excel worksheet that contains the data retrieved from this query. (4 marks)
b. Now, write a suitable function (using VLOOKUP, HLOOKUP and/or MATCH) in the table below such that, when the Product ID and Year are entered, the entry for Sales Revenue is automatically completed. Ensure that if there are no entries in either the Product ID or Year cells, then the Sales Revenue cell remains blank. Create the table such that potential for user-related errors is minimised and explain and justify the functions and methods used. (17 marks)
|
Product IDa
|
Yearb
|
Sales Revenue
|
|
|
|
|
a Entry for Product ID b Entry for Year (1, 2 or 3)
Question 2:
a. The company is concerned with the total projected sales revenue for each year for each salesperson. Your manager wants you to produce a single chart that can be presented at an upcoming Board meeting. Construct an appropriate chart and briefly explain the steps that you went through to create it. (10 marks)
b. A senior data analyst in your team has asked you to identify whether there is any association between the initial onboarding cost of a product and the yearly procurement cost. Construct an appropriate chart and briefly explain the steps that you went through to create it. (10 marks)
Question 3:
The company wants to complete a financial analysis of all products that are projected to be sold within Italy.
a. Firstly, select all the required data from the database by writing an SQL query that retrieves all product IDs, associated sales revenue, operational costs, and procurement costs for years 1 to 3 for all projected sales within Italy. Show your query and provide an explanation of how it works. Then create and display a table in an Excel worksheet that contains the data retrieved from this query (12 marks).
b. Create and display a table in an Excel worksheet that contains the total sales of all products retrieved in 3(a). Display the data in a table of the form. shown below.
|
Product sales in Italy
|
Year 1
|
Year 2
|
Year 3
|
|
Sales Revenue
|
|
|
|
|
Costs:
|
|
|
|
|
Operational costs
|
|
|
|
|
Procurement costs
|
|
|
|
Determine the Total Costs, Trading Profit, Taxation Due, Taxation Paid and Net Profit for each of the 3 years of trading using a taxation rate of 20%. Also ascertain the Net Cash Flow, the Net Present Value and the Internal Rate of Return using a discount rate of 12%. Display the results in an appropriately formatted Excel table and clearly show and justify the functions/formulae used to perform. each of the calculations. (20 marks)
c. The company is considering setting a hurdle rate of 35% for the product under consideration. Explain what the hurdle rate is and whether setting such a rate would be a sensible investment decision. (7 marks)
Marking Criteria
Key marking criteria include:
· Procedure: Correctness of the data extraction and the Excel calculations
· Explanation: Quality of the explanations
· Understanding: Demonstration of understanding of the key topics
· Evaluation/analysis: Evidence of independent thinking and critical awareness
· Organisation: Clarity of structure and use of figures
· Writing: Readability and ability to convey ideas concisely and logically
· Overall Quality of Assignment