ELE3915 - T ake Home Exam - May 2023
This exam consists of two equally-weighted parts. Y our final submission (per group, via WiseFlow) should consist of two excel files (one for part 1; one for part 2) and a text file (e.g. .txt or .ts is fine) which contains the ExcelScript code you are asked to write in the final question of Part 2 below.
Part 2 The second part of the Exam consists of the following 5 tasks. The percentage of marks available for each sub-task 1) - 5) is noted in square brackets. E.g. Sub-task 1) is worth 5% of the total mark for this task. You work for a supermarket chain as a data analyst. Each quarter you receive an excel file from the accounts department which details the sales over the last quarter. The current data is contained in “exam_part2.xlsx” . Your boss has tasked you with preparing an interactive report (in excel) on the sales data. Y ou are tasked with analysing the sales data to inform the business leadership what product segments are doing well. Y ou need to: 1) [5%] Add columns which calculate the profit (total revenue less cost of goods sold) per invoice, the profit margin per invoice (profit as a percentage of total revenue) and the rank of each invoice according to profit margin. Then on a clean worksheet, create a report which 2) [20%] Finds the invoice numbers of the transactions with the 𝑛largest profit margins, where 𝑛is an integer between 1 and 20 which can be changed by the user of the report (𝑛should be subject to data validation such that no number outside of 1, 2, … , 20 can be inputted). 3) [15%] Displays a (stacked bar chart) breakdown of profit margin per product line per payment method. 2 4) [20%] Displays a line chart of total revenue and profit by date, with 7 day moving average trendlines for each and slicers for city and product line. The chart should visually emphasise the trendlines rather than the raw series. Make sure the graph is easily readable (e.g. use a secondary axis if needed, change line styles to not clutter the chart, etc.). Your boss mentions that she expects to circulate this report to higher management each quarter as soon as the data is available. As such you should 5) [40%] Write an ExcelScript which generates the report directly from the excel file given to you from the accounts department. The accounts department have committed to always providing you with an excel workbook with (only) one table which will always have the same headings.
