SOLUTION: IMAT5264 Business Intelligence Application | My Assignment Tutor

Faculty of Computing, Engineering & Media (CEM)Coursework Brief 2020/21 Module name:Business Intelligence Application and DevelopmentModule code:IMAT5264Title of the Assignment:East Midlands Candy AssignmentThis coursework item is:SummativeThis summative coursework will be marked anonymouslyYesThe intended Learning Outcomes (LOs) that are assessed by this coursework are:LO1: Identify the required components of a BI system by systematic analysis of a perceivedproblem area, appraisal of available techniques and tools, and the critical evaluation ofdeveloped systems.LO2: Conceptual understanding of a range of predictive analysis techniques, critically evaluatingcurrent research and new insights.LO3: Design appropriate BI systems using appropriate BI approaches.This coursework is:IndividualThis coursework constitutes 100% to the overall module mark.Date Set:Thursday, 25th March 2021Date & Time Due:Wednesday, 26th May 2021, 12h00Your marked coursework and feedback will beavailable to you on:If for any reason this is not forthcoming by the due date yourmodule leader will let you know why and when it can beexpected. The Associate Professor Student Experience(CEMstudentexperience@dmu.ac.uk) should be informed of anyissues relating to the return of marked coursework andfeedback.Note that you should normally receive feedback on yourcoursework by no later than 20 University working days afterthe formal hand-in date, provided that you have met thesubmission deadline.Wednesday, 23rd June 2021When completed you are required to submit your summative coursework:1. As a Word or PDF Document via TurnitinIf for any reason Turnitin is not working at the date/time of the submission deadline, please provide it as anattachment to an email to the module leader by the specified deadline date/time and a subsequent Turnitinsubmission can be completed as soon as the facility is available again to use.If you need any support or advice on completing this coursework please visit the Student Matters tabon the Faculty of Computing, Engineering and Media Blackboard page.Late submission of coursework policy: Late submissions will be processed in accordance withcurrent University regulations which state:“the time period during which a student may submit a piece of work late without authorisation and have thework capped at 40% [50% at PG level] if passed is 14 calendar days. Work submitted unauthorised more than14 calendar days after the original submission date will receive a mark of 0%. These regulations apply to astudent’s first attempt at coursework. Work submitted late without authorisation which constitutes reassessmentof a previously failed piece of coursework will always receive a mark of 0%.” Academic Offences and Bad Academic Practices:These include plagiarism, cheating, collusion, copying work and reuse of your own work, poor referencing orthe passing off of somebody else’s ideas as your own. If you are in any doubt about what constitutes anacademic offence or bad academic practice you must check with your tutor. Further information and details ofhow DSU can support you, if needed, is available at:http://www.dmu.ac.uk/dmu-students/the-student-gateway/academic-support-office/academic-offences.aspx andhttp://www.dmu.ac.uk/dmu-students/the-student-gateway/academic-support-office/bad-academic-practice.aspxTasks to be undertaken: See belowDeliverables to be submitted for assessment: See below.How the work will be marked: See Appendix 3Module leader/tutor name:Caroline KheneContact details:caroline.khene@dmu.ac.uk Case Study: East Midlands CandyEast Midlands Candy (EMC) is a small local confectionary manufacturer founded in Leicester, UK in1850. Over the years, the organisation has evolved to produce a range of sweet products, includingchocolates, crisps and snacks, and a range of cakes supplied to sweet shops, vending machinecompanies, hotels, and cinemas. There is also a retail section which is offered through a network ofbranches found in towns, cities and in large suburbs throughout in the East Midlands.A new Chief executive has recently been appointed following the retirement of the previous CEO. Thenew senior management team is now keen to upgrade the organisations IT provision to become moremarket focused and efficient. As part of this initiative the management is considering setting up a newdepartment of ‘Business Understanding’. The purpose of this department is to assist management tounderstand customers better and to make justified decisions on operational management. Thisdepartment will undertake Business Intelligence related activities and in particular will be focused onusing techniques in forecasting, and linear programming in relation to the company’s activities.Your task is to prepare a management report in which to illustrate to management how the techniquesof forecasting and optimisation (linear programming), and Business Intelligence could be useful tothe organisation. As management knows little of these techniques, you need to demonstrate how theywill work in practice. For this purpose, data has been supplied for you to carry out some of thetechniques.Requirements and Instructions1. The report should be broken down into three parts (Part 1 – 3). Your report should include yourworkings and/or spreadsheets for the practical exercises and a reference list. We will have accessto your SAS BI work on the SAS server so you do not need to supply anything other than thescreen shots of your SAS BI work within your report.2. It is expected that you will prepare and submit a comprehensive word-processed, structured,logical, technical report in the correct format.3. The report is to be electronically submitted through the Turnitin link on the IMAT5264Blackboard shell by 12h00 noon, Wednesday 26th May 2021.4. A typical report structure includes: Title page, contents, introduction, and main body of report,conclusions, recommendations, and appendix (where appropriate).• Note: The title page should contain: Module code and your p-number (name optional),assignment title, deadline date, module tutor’s name. You should include your p-numberin the footer of every page along with page numbers (there should be no page numberdisplayed on the title page).5. Total word count: 3000 (±10%)6. As a guideline for report writing, please do the course on ‘Writing a Business Report’ that hasbeen placed in the IMAT5264 collection on LinkedIn (click here to access it).7. The marking criteria are supplied Appendix 3.If you have any questions, please direct these to Caroline Khene at caroline.khene@dmu.ac.ukPart 1 Decision Making Techniques (50%)1.1 Forecastinga) As a consultant at East Midlands Candy, you are required to introduce and motivate for theuse of Forecasting techniques by: i.ii.Providing an overview of how forecasting techniques could be useful.Discussing the challenges and/or implications that East Midlands Candy could face inthe application of forecasting approaches/processes?Discussing the four keys questions that management at East Midlands Candy shouldconsider when measuring the success of their forecasting techniques.iii. b) Use the following guidelines and instructions for the application of forecasting techniques:i. Review the data supplied in Appendix 1. ii.Use this data to demonstrate how the different products (A, B & C) show differentcharacteristics with regard to forecasting (Hint: identify and describe the patternusing a time series plot).Identify the most appropriate forecasting technique for each of the three productsgiven and justify your choice.Give forecasts for Year 4, for each product.Critically evaluate your results highlighting whether you consider the forecastingmodel to be good or not (Hint: consider forecast accuracy).iii.iv.v. (NB: Please use Microsoft Excel to support your calculations)(10 + 15 = 25 Marks)1.2 Linear Programming/Linear Optimisationa) Provide an overview of how optimisation and linear programming (LP) could be useful insupporting decision-making at East Midlands Candy.b) Discuss how optimisation mathematical modelling could be used in the area of theproduction/manufacturing of candy products (Hint: identify possible decision variables,results variables, and uncontrollable variables/parameters)c) Use the following guidelines and instructions for the application of linear programmingtechniques. i.Review Problem Case 1 (Production) and Problem Case 2 (Staff Allocation) inAppendix 2.Formulate a Linear Programming model for:ii. 1) Problem Case 1 (Production)2) Problem Case 2 (Staff Allocation)NB: There is no requirement to solve this problem or make calculations. A model isonly required, which includes decision variables, a result variable, objective, anduncontrollable variable.(5 + 5 + 15 = 25 Marks)Part 2 Business Intelligence Systems (30%)The owners of EMC have asked you to develop a Business Intelligence application for the Salesdepartment. The Sales Department is currently split into three groups, each of which has responsibilityfor one customer area (retail, wholesale and grocery). The members of each team are to be providedwith an Information Delivery Portal (IDP) page. The information displayed is to be relevant to eachsales group i.e. it is to be filtered to display the customer group they have a responsibility for. Allusers can be considered low level IT users.In Section 1 of the IDP page a web report is to be displayed showing the sales history for each productgroup (a tab page for each year) in table form. This table should show as a minimum the sales totalsfor each product group for that sales group. You may choose to add additional measures. Be awarethat as the designer you need to find an appropriate balance of detail for the user.In Section 2 of the IDP page, a web report is to be displayed in a graphical format. This should showsales to all customers in a selected product group. Refer to the data sets provided to identify thesegroups. The user should be prompted as to which product group and year they are interested in.Remember they should only see sales for their customer group. The graph format is your choice (piechart, bar chart etc.).In section 3 of the IDP page produce a dashboard showing total sales of each product group for thecurrent year which is 2003. Set the range based on:-Above target being +10% on 2002 salesOn Target being between 90% and 110% of 2002 sales Below target being less than 90% of 2002sales.Again the choice of gauge is yours; remember to limit the data to that sales group’s customers. Youwill need to manually calculate the value to enter for each range. There is no way for SAS to calculatethis requirement.The data is already registered in metadata within the SAS BI suite, within the shared folder.The files you will need include – Candy_Customer, Candy_Products, Candy_Sales_History,Candy_Time_Periods and Candy_Sales_Summary.You will need to create information maps, filters and prompts as necessary to complete the tasks.To achieve a pass you will need to demonstrate an understanding of SAS techniques as we havecovered in labs. To achieve a distinction level pass you will need to demonstrate your ability toproduce an application tailored to non-technical user needs. Refer to the marking sheet for more detailon the marking.Include samples of your work in your report and an evaluation of your system, identifying any areaswhere this system could be improved – for example highlight shortcomings in the software.Part 3: Compliance (legal), Ethics, and Privacy Issues in Business Intelligence (10%)Compile a brief overview of compliance, ethics, and privacy issues that may affect East MidlandsCandy and the proposed business intelligence systems. Support your overview with possible examplesthat apply to EMC, and the nature of its business.Report format and structure (10%)There are 10% of the marks allocated for the correct report structure and clarity of the supportingmaterial, including layout, clarity, grammar, spelling, referencing and appropriate depth of coverage.Submit your structured word processed report. Your report should include your workings and/orspreadsheets for the practical exercises and a reference list. These could include hand written andscanned LP formulations, excel spreadsheets and a list of references used.Refer to the marking grid provided in Appendix 3 for more details.Appendix 1Sales figures for three (3) productsSales of three (3) products have been selected for a trial of forecasting methods. Sales of the threeproducts (in 000s) are shown in the tables belowProduct A: Chocolate bar YearWinterSpringSummerAutumn19.59.39.49.629.89.79.810.539.99.79.69.6 Product B: Children’s treats YearWinterSpringSummerAutumn114.231.833.06.8215.434.836.27.4314.838.241.47.6 Product C: Adult Mint bar YearWinterSpringSummerAutumn111.412.613.012.8213.814.014.815.2315.615.816.216.6 Appendix 2Problem case 1: ProductionEMC produces two types of popcorn (standard and deluxe). The supply of sugar is limited to 40,000kg a week. The standard popcorn uses 300g of sugar whereas the deluxe version uses 400g of sugarper kg.EMC also has a commitment to supply 1,000 kg of the standard popcorn to a local cinema.A final constraint on production is that EMC only has 48 litres of colouring. The standardproduct requires 8 millilitres per kg and the deluxe product requires 15 millilitres per kilo.EMC sells popcorn to its customers at a profit of 50p per kg of standard and 80p per kg of the deluxe.Formulate a linear programming model to maximise profit from the production/sales of popcorn. Donot attempt to solve the problem.Problem case 2: Staff AllocationThe main factory at EMC operates 24 hours a day, with staff working 9-hour shifts. Notall production lines operate 24 hours. Some only operate for 9 hours and others for 18hours each day. The demand for staff during each 3-hour period of the day is givenbelow. Formulate a linear programming model to determine the minimum number ofstaff required in each 24 hour period. Do not attempt to solve the problem. Time PeriodNumber of Staff Required9:00-12:003212:00-15:002415:00-18:002018:00-21:002821:00-24:00120:00-3:0043:00-6:0026:00-9:0010 Appendix 3Marking Scheme 0-39 Fail40-49- Fail50-59 Pass60-69 Pass Merit70-79 PassDistinction80 +DistinctionForecastingTechniques(15%)No attemptOr incorrectapproachusedAn attempt toevaluate thegivendata.Inappropriatemethodology used.Possiblysome whollyincorrecttechniques usedinplacesAppropriate evaluationof time seriesAppropriatemethodology ForecastsproducedSome minor errorsGood evaluationof time seriesGood methodologyForecasts producedPossibly a minor errorGood evaluationof time series GoodmethodologyCorrectForecasts producedExcellent evaluation oftime series ExcellentmethodologyForecasts produced ata professional levelForecastingEvaluation(10%)No attempt orincorrectAn Attemptmadebut incorrect inseveral places orno application tothe case study.Evaluation showing anunderstanding of thearea of forecasting.Some errors ofunderstanding orminimal application tothe case studyGood evaluationshowing a goodunderstanding of thearea of forecasting.Possibly a few errors ofunderstanding orminimal application tothe case studyGood evaluationShowing real insightinto the techniqueapplied to the casestudy throughoutAs before but at aprofessional level.Difficult to faultLinearprogrammingTechniques(15%)No attemptOr incorrectapproachusedAn attempt toevaluate thegivenproblems.InappropriatemethodologyusedPossibly somewholly incorrecttechniques usedinplacesAppropriatemethodologyOptimisation modelscreated producedSome minor errorsGood methodologyOptimisation modelsproduced Possibly aminor errorGood methodologyCorrect LP modelsproduced. Very minorerror in formulationExcellentmethodology LPmodels produceddifficult to faultLinearProgrammingEvaluation(10%)No attempt orincorrectAn Attemptmadebut incorrect inseveral places orno application tothe case study.Evaluation showing anunderstanding of thearea of LP. Someerrors ofunderstanding orminimal application tothe case studyGood evaluationshowing a goodunderstanding of thearea of LP. Possiblya few errors ofunderstanding orminimal application tothe case studyGood evaluationShowing real insightinto the techniqueapplied to the casestudy throughoutAs before but at aprofessional level.Difficult to faultInformationMaps andFilters(5%)Not attemptedordemonstratesnounderstandingof theinformationmaps purposeAttempt madeto createinformationmaps butpossibly nofilters orineffectiveinformationmapsAppropriateInformation maps orFilters createdAppropriateInformation mapscreated with sensiblefilters but with minoromissionsExcellent Informationmaps with useful fullyfunctioning filters thatare appropriate.Information mapsproduced to aprofessional levelWeb reports(5%)Notattempted orwhollyinadequateAttempted butwith majorflawsReports produced withone possibly notmeeting the spec orwith obviousdeficiencies. Thiscould include overlycomplexAppropriate reportsproduced but possiblywith minor problemsExcellent reportsmeeting userrequirements at a veryhigh levelReports produced to aprofessionalleveldemonstrating asound understandingof user needsDashboards(5%)Notattempted orwhollyinadequateAttempted butwith majorflawsDashboard producedpossibly not fullymeeting the spec orwith obviousdeficienciesAppropriateDashboard producedbut with possibleminor problems.Static targetsExcellent Dashboardmeeting userrequirements at a veryhigh level. DynamictargetsDashboards producedto a professionalleveldemonstrating asound understandingof user needs Development ofIDP (5%)NotattemptedAttempted butnot correctCompleted butrequires moreattention to detail inlayout and headingsetc. May notunderstand differentuser group needs.Completed butpossible minor issuesin appearanceGood attention todetail in creating thepageExcellent attention todetail- extras added tomake it more userfriendlyEvaluation ofSystem (10%)NotattemptedAttempt madebut toosuperficial orshows nounderstandingof BI systemsA reasonable attemptmade to evaluate thesystem and software.One area is eithermissing or superficialA good attempt madeto evaluate the systemand software.Evaluation of someareas vague or briefbut most coveredadequately.An excellent attemptmade to evaluate thesystem and softwareAn excellent attemptmade to evaluate thesystem and software.Shows evidence of anin depth understandingof the issuesCompliance(10%)NotAttemptedAttempt madebut manycomplianceissues omittedSeveral complianceissues identified butnot completeMajor issues identifiedand key factorshighlighted in reportmaybe not fullyapplied to the casestudyMajor complianceissues identified andapplied to the casestudyAs before but shows alevel of understandingnot usually expectedfrom students at thislevelReportFormat andReferencing(10%)VeryPoorlyattemptPoorlywritten,unstructured, noillustration(s), none orentirelyinappropriatereferencingUnderstandable,little structure,no contents listand not spellchecked, no orquestionableillustration(s),adequatecoverage ofsources, lackingsufficient detailin referencingReasonablywritten,adequatestructure,contents list orspell checked,someillustration(s),adequatecoverage ofsources, lackingsufficient detailin referencingWell written,good structure,contents list andspell checked,appropriateillustration(s),Appendicesthroughcoverage ofsources withsufficient detailin referencingVery wellwritten,excellentstructure,contents list andspell checked,informativeillustration(s)withcomprehensive,informative andrelevantreferencing