Objectives
At the completion of this unit, the HI graduate student will be able to
Apply BI concepts gained from the HCBI Course
Apply design best practice techniques
Browse “raw” data and make data manipulations
Apply formulas and other functions
Create a prototype business intelligence solution
Assignment
Your BI solution and recommendation has been accepted. The Board of Directors would like to see a dashboard prototype created in Microsoft Excel to provide their BI team for duplication in the new system. Your firm, along with a few others, will be submitting a proof of concept to the CIO. Based off this submission, the organization will select a consulting firm for phase II (implementation) of the project.
Create a prototype dashboard using Microsoft Excel. You are open to your own interpretation, but below are minimum guidelines that you must adhere to. In the resources folder, you will find some sample data and below is the link for the template file you will use for the submission. You do not have to use all the sample data. Download the files, and select which are appropriate for you. The data is obtained from data.gov (open use) and is the only source of data for this project. The files may need some data manipulation on your part, but all of them should have usable data within them. You are free to add data to the existing data files, but ensure you include that dataset as a tab in the final deliverable. The limitation on if you add data is that it must be integrated with one of the existing data files. No brand new data sources are accepted. You must work with only the files posted in the resource link below. You may pose questions in the “Questions for the Instructor” forum for clarification on data elements and/or any project related items. Below is the minimum required.
Assumptions/Minimum Requirements:
The dashboard must at least contain four charts and one summarized table (tabular data)
The dashboard must be on one page (Excel tab) only
The CIO is looking for trends in terms of growth/loss financials, providers specific information, clinical outcomes, etc. in the dashboard (where applicable)
The CIO would also like one chart dedicated to contain advanced statistics. Preferably, a frequency distribution chart on one of the subject matters (your choice), on one data element (your choice). However, this can get much more advanced based on your comfort level of statistics (regression, etc.) and the dataset
The CIO is looking for a brief introduction on the data you included in the dashboard and an overall summary of the dashboard (enter summary in Cover tab)
The CIO is also looking for a KPI recommendation from you based on what you are seeing in the data. What would you set as a KPI and why? (enter in Cover tab)
The CIO wants a consistent, formatted, and a visually appealing dashboard
The CIO understands some of the subject matter may not be related, but for this proof of concept it does not have to be
Setting the print areas is optional but not necessary
Ensure the dashboard is linked. No images of charts/tables. This should be an actual chart/table created by you. If you need to manipulate the existing dataset you may. You must include that manipulated data set in the final submission and label the tab accordingly. Take a copy of the data and place it in a new tab where you are free to edit/omit etc.
You may get creative in your approach (logos, drop-downs, pivot tables etc.) and provide as advanced of a dashboard as you would like
Use this file as the template for submission Unit 8 Assignment.xlsx
1. Please upload your dashboard (.xlsx) file to the file submission area below. Ensure you attach the file. You have multiple attempts, if you are not sure it went through, please send me an email before the due date.
