MIS171 : Business Analytics Assessment 1 & 2

Dashboard Creation Notes 

Step 1: Create Tables

• Select the data set.

• Format as a table: Home > Styles > Format as Table.

• Name the created table: Table Design > Properties > Table Name.

• Repeat for other data sets (if needed).

Step 2: Create Pivot Column (or Bar) Chart

• Open a new worksheet and name the worksheet.

• Insert a pivot table in the new worksheet using the table name created in Step 1 (Insert > PivotTable).

• Name the new pivot table (PivotTable Analyze > PivotTable Name).

• Format the pivot table with key data for the table you want to create, for example:

  • Put horizontal axis labels in the Rows selection.

  • Put vertical axis data in the Values section. Specify the Value Field (e.g., average).

• Select the data set and insert (create) a pivot chart (PivotTable Analyze > PivotChart).

• Name the chart (PivotTable Analyze > PivotChart > Chart Name).

• Format the pivot chart: chart title, meaningful axes, include legend, format numbers (e.g., decimal points).

• Hide the field buttons:

  • Click on the chart > PivotChart Analyze > Field Buttons > Hide All.

  • Note: This may not work on Excel for Mac; use Excel on Windows if needed.

• Repeat all steps to create as many Column (or Bar) Charts as needed.

Step 3: Create a Pie Chart

• Open a new worksheet for each dashboard element and name it.

• Create a pivot table using the table name from Step 1 (Insert > PivotTable).

• Update pivot table fields based on your dashboard design plan.

• To get proportions: click on the respective column in the pivot table > right click > Show Values As > % of Column Total (or % of Grand Total).

• Name the pivot table.

• Insert a pie chart and format as above (chart title, axes, hide field buttons).

Summary of Assessment Requirements

The MIS171 – Business Analytics assessment focuses on creating a comprehensive dashboard using Excel. Students are required to:

• Organize and format datasets into tables for use in dashboards.

• Generate multiple PivotTables and PivotCharts, including Column (or Bar) Charts and Pie Charts, to represent different types of data.

• Format and customize charts and tables for readability and insight, including hiding unnecessary field buttons.

• Apply calculations such as averages, proportions (% of column or grand total), and create visualizations aligned with the dashboard design plan.

• Ensure that the final dashboard is clear, interactive, and reviewable on a single screen.

Key pointers to cover:

1. Table creation and proper naming conventions.

2. PivotTable generation for numerical and categorical data.

3. PivotChart creation for visual representation of data.

4. Pie charts for proportional data visualization.

5. Formatting charts and tables, including titles, axes, and legends.

6. Error handling for missing or filtered data.

7. Dashboard design principles such as layout, slicers, and data clarity.

Approach Guided by Academic Mentor

Step 1: Creating Tables

• The mentor first guided the student to select datasets and format them as Excel tables.

• Each table was named for easy reference in subsequent PivotTables.

• This ensured that the data was structured and ready for analysis.

Step 2: Pivot Column/Bar Charts

• The student was shown how to insert PivotTables on new worksheets.

• Mentor explained how to place horizontal axis labels in Rows and numeric data in Values.

• PivotCharts were created from these tables and formatted with chart titles, axes labels, and legends.

• Field buttons were hidden to make charts cleaner for the dashboard view.

Step 3: Pie Charts

• For proportional data, the mentor guided the student to create separate PivotTables for each dashboard element.

• Values were displayed as percentages of column or grand totals.

• Pie charts were inserted and formatted to enhance visual clarity.

Step 4: Dashboard Layout and Design

• Mentor explained best practices for dashboard layout: arranging elements on a single screen, adding slicers for interactivity, and combining charts and tables meaningfully.

• Emphasis was given to ensure the dashboard communicates insights clearly to decision-makers.

Outcome Achieved

By following the step-by-step guidance:

• The student successfully created multiple PivotTables and PivotCharts representing different data aspects.

• Pie charts accurately displayed proportions of categorical data.

• The final dashboard was visually organized, interactive, and ready for review at a glance.

• Students learned to handle errors in filtered or missing data, improving reliability of visualizations.

Learning Objectives Covered

1. Data organization and analysis – Structured data tables and generated PivotTables for insightful analysis.

2. Effective data visualization – Created and formatted Column, Bar, and Pie Charts for clear representation.

3. Dashboard design and communication – Developed a professional, interactive dashboard that communicates key insights.

4. Technical Excel skills – Applied formulas for error handling, calculated averages and percentages, and customized charts.

Unlock Your Academic Success with Expert Assignment Support

Looking for guidance on your assignments? You can download this sample solution to understand how a high-quality academic response is structured. This reference can help you organise your ideas, understand formatting, and see how to approach similar questions.

Important: This sample is for reference purposes only. Submitting it as your own work is considered plagiarism and may lead to serious academic consequences. Always ensure your submissions are original.

For a stress-free solution tailored to your specific requirements, consider ordering a fresh, plagiarism-free assignment written by professional academic writers. Our experts provide:

• Customised content aligned with your brief and academic standards

• 100% original work with proper citations and references

• Clear, structured, and well-researched solutions to save your time

• Timely delivery to meet your deadlines without compromise

Take control of your grades today choose the option that works best for you.

[Download Sample Solution]    [Order Fresh Assignment]