Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit 

MN-3526:  Spreadsheets and Databases for Business Analysis

Coursework Assignment 1

Submission:

Assignments must be submitted electronically by 2pm on Wednesday 10 May 2023 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 fictional Microsoft Access vehicle dealership database (CarBusiness.accdb) is provided. The database details vehicle sales and servicing figures for a number of vehicles over a period of years. The business has several workshops where services are carried out (Shop 1, Shop 2 and Shop 3).

The information relating to the vehicle purchases and sales are stored in the table called Vehicles. Details relating to vehicle servicing are stored in the table called Services.

You have been recruited as a business data analyst for the dealership. You have been told that, over time, the database will evolve (i.e., it is not static).

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 vehicle licence plate number and for the most recent service date and invoice value of the vehicle to be displayed.

a. Construct a single SQL query that retrieves the licence plate number, service date and invoice value for all vehicles in the database (note that a single vehicle can be serviced on more than one occasion). Show and explain your query. Then create and display a table in an Excel worksheet that contains the data retrieved from this query. (15 marks)

b. Next, write a suitable function (using VLOOKUP, HLOOKUP and/or MATCH) for the table below such that, when the licence plate number is entered, the entry for the most recent service date and associated invoice value is automatically completed. Ensure that if there are no entries in the Licence Plate Number cell, then the Most Recent Service Date cell and Invoice Value cell remain blank. Create the table such that potential for user-related errors is minimised and explain and justify the functions and methods used. (15 marks)

^a Entry for Licence Plate Number

Question 2:

a. The dealership is concerned with the profit being made on car servicing at each workshop. Profit, in this context, is defined as the invoice value less the service expenses. You have been asked to compare the total profit for each workshop for each of the years 2020, 2021 and 2022. Your manager wants you to produce a single chart that can be presented at an upcoming Board meeting. Construct an appropriate chart and explain the steps that you went through to create it. (20 marks)

b. A senior data analyst in your team has asked you to identify for them whether there is any association between the mileage of a vehicle and the time it takes to sell. The time taken to sell is defined as the days elapsed from when the dealership purchased the vehicle (purchase date) to when it was sold (sold date). Construct an appropriate chart and explain the steps that you went through to create it. (10 marks)

Question 3:

a. The dealership wants to determine where the vehicle that had the highest sold mileage was last serviced (i.e., Shop 1, Shop 2 or Shop 3). Write an SQL query that retrieves the Make, Model, Licence Plate Number, Sold Mileage, Service Date and Workshop for the vehicle from the database. Show the query, the results of the query, and provide an explanation of how it works. (20 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