1. Homepage
2. Exam
3. ELE3915 Excel Programming and Automation - Take Home Exam Part 2: Sales data analysis

# ELE3915 Excel Programming and Automation - Take Home Exam Part 2: Sales data analysis

This question has been solved

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.

## Get the Solution to This Question

WeChat
WhatsApp
Norwegian Business School代写,Norway代写,ELE3915代写,Excel Programming and Automation代写,ExcelScript代写,SUMIF代写,Norwegian Business School代编,Norway代编,ELE3915代编,Excel Programming and Automation代编,ExcelScript代编,SUMIF代编,Norwegian Business School代考,Norway代考,ELE3915代考,Excel Programming and Automation代考,ExcelScript代考,SUMIF代考,Norwegian Business Schoolhelp,Norwayhelp,ELE3915help,Excel Programming and Automationhelp,ExcelScripthelp,SUMIFhelp,Norwegian Business School作业代写,Norway作业代写,ELE3915作业代写,Excel Programming and Automation作业代写,ExcelScript作业代写,SUMIF作业代写,Norwegian Business School编程代写,Norway编程代写,ELE3915编程代写,Excel Programming and Automation编程代写,ExcelScript编程代写,SUMIF编程代写,Norwegian Business Schoolprogramming help,Norwayprogramming help,ELE3915programming help,Excel Programming and Automationprogramming help,ExcelScriptprogramming help,SUMIFprogramming help,Norwegian Business Schoolassignment help,Norwayassignment help,ELE3915assignment help,Excel Programming and Automationassignment help,ExcelScriptassignment help,SUMIFassignment help,Norwegian Business Schoolsolution,Norwaysolution,ELE3915solution,Excel Programming and Automationsolution,ExcelScriptsolution,SUMIFsolution,