INFO6090 - Business Intelligence for the Enterprise
ASSIGNMENT 2 - INSTRUCTIONS
Worth:
25% of your course final mark (Report = 20%, Video Presentation = 5%)
Assignment:
This assignment builds upon the concepts and analyses from Assignment 1 and focuses on a broader perspective of the EPA’s regional offices. It requires students to demonstrate their ability to integrate data, perform. predictive analysis, and provide strategic recommendations at an organizational level.
The key outcomes to be delivered are:
• Data Modelling and ETL Process: Design a star schema and integrate data from
multiple offices. Document the ETL process and note any quality assurance measures undertaken.
• Predictive Analysis and Reporting: Create a predictive model to forecast
compliance trends and evaluate the performance of each office. Provide SQL queries (or Excel screenshots) and evidence for your analysis.
• Business Recommendations: Based on the analysis, recommend strategic actions. These should clearly address the client's question and provide evidence-based suggestions.
• Dashboard Creation: Create an interactive dashboard using Excel or Power BI that visualizes the performance of the offices.
• Presentation: Present your findings and recommendations in a formal presentation to senior management.
Assignment Question:
The current CEO of the NSW Environment Protection Authority (EPA) is Tony Chappel. He has been leading the EPA since August 2022. He has been speaking to his senior executives about your previous analysis at the Newcastle office. He was impressed with the insights on performance and compliance monitoring, and particularly your recommendations for improving efficiency through targeted incentives and resource optimization. Given the positive impact of your work, the CEO now wants to expand the scope of the analysis to all regional offices in Australia.
“I’ve been informed about the great work you did at the Newcastle branch to analyse compliance activities and recommend ways to optimize resources. Your report suggested that we could streamline operations by consolidating some activities, which resulted in a more efficient use of the team’s time and a noticeable increase in compliance rates. However, with ongoing budget constraints and pressure from the board to reduce operational costs, we need to take more drastic measures. I would like to understand the performance of our various regional offices in detail. I want to identify which offices are performing well, which ones are underperforming, and where we can optimize our operations.
We are considering restructuring, which might involve consolidating or even closing underperforming offices. This decision will be difficult and sensitive, and we need comprehensive data and analysis to support our choices. I would like you to perform a thorough evaluation of all our regional offices, including analysing their compliance rates, community feedback, and resource utilization. Please also project compliance trends for the upcoming year and analyse how our current incentive program is impacting performance. Additionally, consider the following scenario: What would happen if we introduced a new incentive structure? For example, if we tied incentive payments directly to achieving a certain compliance threshold, how would that affect performance and morale? Could we save costs while maintaining or even improving compliance? Please explore these possibilities in your analysis.
Finally, as this is ahighly sensitive issue, I need a comprehensive report with evidence-based recommendations on which offices we should potentially downsize or close. Please create a ranking of the offices based on your findings and ensure that your recommendations are supported by a dashboard visualization that we can use to present this information to the board. ”
Assignment Deliverables:
Using the data file provided in Excel and notes about the data (INFO6090 Assignment 2 - Dataset.xlsx), you are required to complete the following elements as part of the assignment:
• Data Model/Data Load Process
o Data Model, appropriate data attributes and cleaned data.
o Provide an overview of the data model & ETL process completed to get the data ready for analysis (including identification of any anomalies found, methods of cleaning them and the overall effect of cleaning on the final business analysis)
o Ensure you record any assumptions you have made as part of this component, and your reasoning behind the assumption.
• Analysis including any predictive work undertaken and recommendations to Management:
o In your report, provide the SQL and raw output of your base analysis.
o Provide workings of the predictive work you completed.
o Ensure you record any assumptions you have made as part of this component, and your reasoning behind the assumptions. This includes answers to any relevant questions put to management (your lecturer) during workshops.
• Business Report (not to exceed 1.5 pages)
o Your report should include
Executive Summary. Your Executive report should include a short
Executive brief/summary that presents a clear concise response back to the CEO questions
Business recommendations to management (with justifications) based on your analysis. This can also include business initiatives that are backed up by benefits and costings.
Business analysis and recommendations derived from your Core Analysis work.
• A dashboard that allows quick comparisons between the sites to be undertaken as well as containing at least one element of ‘predictive’ analysis.
o You may use either Excel and pivot tables to provide a dynamic view of the dashboard, or you can use Power BI to construct your dashboard.
o You should present your dashboard in your presentation and display its dynamic nature.
• Record an individual presentation as if it is to be consumed in a formal boardroom meeting.
o Your presentation is worth 5% of the assignment mark. Remember to present to what is relevant to your audience (Senior Management) and not technical specialists. Use graphs where appropriate and only tables for detail.
o Use your dashboard to answer some of the business questions.
o You should time your presentation to be between 3-5 minutes (timing will be assessed so learn to present relevant material only).
Note: As part of your response, you should also specifically include any assumptions/external information you have made/used throughout the process as well as any quality processes/checking you have completed or limitations you discovered. The assumptions will not be included in the marking but may allow us to understand and (perhaps) moderate our interpretation of your results, even if they are not correct.
Submission Instructions
Similarly to Assignment 1 (Part A), you are required to upload all files in a single zip file (including the presentation) to the Assignment 2 Canvas folder and the Word Report to TurnItIn in Week 12 (see more details on Canvas). Work will not be marked if you have not included in a Team Cover Sheet. A detailed marking guide is available on Canvas.