Building a Business Dashboard
1
Building a Business Dashboard
Overview
A business dashboard is ‘a style of reporting that depicts KPIs, operational or strategic information with intuitive and interactive displays’ (Turban et al., 2015 p. 380). It is a single screen snapshot of how a business, department, or process is performing. The design varies considerably from one application to another, and even between businesses, but a common feature of a dashboard is that it uses graphs, coloured text, and symbols to show the viewer, at a glance, the current status. A dashboard should only contain information that actually influences performance. Many dashboards are interactive because it can be difficult to show every important detail at once.
You are an employee at Australian Electronics Pty Ltd. You have been assigned the task of designing a report that will be used by managers involved in purchasing, sales, and inventory management. Your boss, Mary Smith, suggest including at least four (4) ratios. Regarding the ratios, Mary thinks that Inventory Turnover and Sales Growth are a must.
This is an individual assignment. There is no fixed answer, so be creative!! The spreadsheet must perform ratio analysis to show the current status of the inventory holdings and sales. Marks are awarded according to how well the dashboard meets the requirements specified in the rubric.
A data set is supplied with this guide in Moodle (ACC ACF 2400_s2 2017_Inventory Statistics.xlsx). The Inventory Statistics data set contains four sheets: sales value, sales quantity, the quantity of inventory on hand, and the quantity purchased. You should use all sheets in your calculations, but may need to restructure some data on a separate calculation sheet to ensure data is in the format you need.
Instructions on how to build a complex interactive scorecard have been published in different journals such as the Journal of Accountancy (http://www.journalofaccountancy.com/issues/2011/feb/20092427.html), but you do not have to build such a complex system if your spreadsheeting skills are not well developed.
The table below contrasts two different approaches. The example on the left shows 7 ratios in a non-interactive dashboard, with three graphs and one table of numbers. It is clearly not an inventory management dashboard, but if the design features included were tailored to the inventory management context, it would likely earn a pass (providing instructions, the input sheet, and the calculations sheet are acceptable).
The example on the right, however, is from the Dashboard your Scorecard article. It is also not an inventory management dashboard and does not show ratios, and so is not acceptable, but illustrates elements that will earn higher marks:
• It is interactive (note the drop-down box in the bottom right graph to select the person shown);
• It uses conditional formatting icons (arrows) in the top right table to indicate the direction of change and so makes the data easier to digest at a glance;
• It uses spark lines (within cell graphs) in the top right table to show historical changes.
2
Pass
High distinction
Learning objectives assessed:
The purpose of this assignment is to give you practice in designing and developing a reporting system
using good spreadsheet design practice. Some independent research will be required to find out how
to construct the elements of the report, particularly if you are attempting the high distinction
requirements.
In terms of the learning outcomes shown in the unit guide, this assessment task tests your achievement
of objective 4, synthesise design principles to develop financial models that assist in decision making,
and objective 5, apply critical thinking, problem solving and communication skills to analyse, evaluate
and interpret business processes and the accounting data that is generated.
In terms of the unit content, this assignment is based on a set of practice guidelines that are used
widely, usually referred to as the COSO ERM framework, and shown below. This assignment focuses
on the bottom two elements:
Information and communication: What
information is needed, and how should it be
communicated?
Monitoring: How will you monitor what is
happening?
Representative
Prior
Year
Units
Current
Year Units
Percent
Change
Year-to-Date Dollar
Sales
Allen Pearson 924 942 2%
Janet Sellers 1,053 1,015 -4%
Mark Stevens 481 522 8%
Mary Daniel 521 464 -12%
Scott Edwards 452 466 3%
Allen Analysis of Sales Trends
Pearson
28%
Janet
Sellers
30%
Mark
Stevens
15%
Mary
Daniel
13%
Scott
Edwards
14%
– 500 1,000 1,500
Allen Pearson
Janet Sellers
Mark Stevens
Mary Daniel
Scott Edwards
Unit Sales (000s)
Commercial Government Residential
New
England
Red
Classic
River
Magic
Southern
Charm
Waverly White
Pine
–
50
100
150
200
250
300
350
Brick Styles
Unit
Sales
(000s)
3
Marks
Criteria for marking: This assignment is worth 15% of your total marks. You will be assessed for the quality of your instructions on how to use the spreadsheet, the amount of analysis performed, and the usefulness and attractiveness of the output.
Mark breakdown per task
Task
Marks
A (instruction sheets)
3
B (data input sheet)
3
C (calculations)
2
D (report)
7
Total
15
Required
a) Prepare an instruction sheet that explains how to use your spreadsheet. Instructions should be brief. Aim for no more than 500 words.
The instruction sheet should include this information:
• Your name
• Purpose of spreadsheet
• Description of layout
• Where to enter data
• Which ratios are shown and how they are calculated
• A description of the information shown on the report
b) Assume that the data input sheets are imported from an Enterprise System database, and so no manual data entry is required. Sometimes, however, the import process fails, and incorrect data is assigned to each cell (e.g. numbers where product codes should be). To obtain a credit or above for this part of the task, format the data input sheets (the ACC ACF 2400_s2 2017_Inventory Statistics.xlsx file available on Moodle) to highlight invalid values.
c) Construct the data processing (calculation) sheet or sheets (see marking rubric for more details).
d) Construct an attractive report sheet (see marking rubric for more details) Submission:
Submission is via Moodle on Sunday 10 September 11.59pm Submission format:
.xls or .xlsx spreadsheet file. Assume that your tutor only has access to Microsoft Excel. You may develop your solution using another program, such as Open Office Calc, Google Sheets, Numbers, and so on, but must save and submit your work as an Excel sheet. Word limit:
Instruction sheet: No more than 500 words.
4
Resources:
The assignment is designed to be completed using Microsoft Excel, which is available in the computer laboratories. It can potentially also be completed using other spreadsheet platforms, such as Google Sheets or Open Office Calc; however, most of the teaching staff are only familiar with Excel and so may not be able to provide any meaningful assistance if you choose to attempt the assignment using a competing product.
A considerable amount of information about each command you are asked to use is available on the unit Moodle site and the Internet. For example, a simple Google search generated these results:
• Conditional formatting: 514,000 results for Excel, 64,900 results for Google sheets
• Conditional formatting icons: 50,800 results for Excel, feature not supported in Google Sheets and so has to be implemented via nested IFs or category-based lookup functions (e.g. LOOKUP, VLOOKUP, MATCH)
• Conditional formatting colour scales: 127,000 results for Excel, 2,210 results for Google Sheets
• Slicer: 408,000 results for Excel, feature not available in Google Sheets
• Excel Form controls/Active X controls: 160,000,000 results (a very popular topic)
Note: Excel’s form controls are available on the Developer tab, which is hidden until you right click on the top menu, select Customize the Ribbon, and the check the Developer option.
• Google sheets form controls/drop down lists: 79,000 results (limited functionality, but can be implemented with some effort)
• Spark lines: 307,000 results for Excel, 13,400 results for Google Sheets
This is a major assignment in which you are expected to put in a substantial amount of work to obtain higher grades. However, you could ask your tutor or one of the lecturers for some guidance about these commands. Although we recognise that some commands can be a little bit difficult to implement, you have access to plenty of resources such as learning materials and other web sources (as indicated above). Conditional formatting icons, in particular, are not easy to customise. Do not expect teaching staff to give you the answer directly, but we will try to help you to work out why your model is not working properly. Teaching staff will not be impressed if you ask a basic question, such as “What is conditional formatting?” or “Where can I find form controls in the menu?”. That type of question suggests a lazy intellectual approach that is not consistent with university-level study.
5
Marking Rubric for Spreadsheet
High Distinction
Distinction
Credit
Pass
Unsatisfactory (Fail)
Instructions
Basic requirements:
Shows
• Author
• Purpose of spreadsheet
• Description of layout
• Where/how to enter data
• Formula for each ratio used
• Examples of special features in report (e.g. conditional formatting)
High Distinction
• Professional quality.
• Concise, but well-explained.
• Error free (e.g. grammatical mistakes).
• Structure of the spreadsheet is very clear.
• Clear examples of special features, such as slicers, provided.
All basic requirements, but few errors.
Instructions mostly clear, and suitable for distribution to a professional audience.
Examples provided for all features, but few problems to understand.
All basic requirements but some errors.
Instructions could be clearer, but the instructions are usable.
Examples provided for some, but not all, special features (e.g. conditional formatting)
Meets basic requirements, but some elements were not explained well.
Should not be distributed without editing. E.g. some poor grammar, structure of workbook or some variable definitions not explained well.
Does not meet basic requirements.
Instructions missing or hard to understand.
Document contains many errors.
Cannot be distributed to a professional audience.
6
High Distinction
Distinction
Credit
Pass
Unsatisfactory (Fail)
Input
• 3 different rules to highlight invalid data on all four input sheets.
• Each rule must apply to multiple cells, and overall, all data cells must be tested (e.g. you could test whether cells contain the correct data type or that numbers are not too high or low.
• In Excel, this can be done via the Data Validation command with the circle invalid data option enabled, or via conditional formatting.
• Google Sheets does not have a circle invalid data option, so you have to use conditional formatting.
• Note that this is a challenging task that may require a formula-based rule to highlight cells that contain numbers instead of text.
• 2 different rules to highlight invalid data on all four input sheets, OR
• 3 rules but not all input sheets or not all data cells are covered.
• One rule to highlight invalid data, OR
• 2 rules, but not all input sheets or not all data cells are covered.
• This is an advanced task for students attempting to obtain an overall grade of more than pass.
• Input sheets are essentially the same as sheets downloaded.
7
High Distinction
Distinction
Credit
Pass
Unsatisfactory (Fail)
Calculations
• Separate calculation sheet(s)
• Values are from formulas or pivot tables, not hard-coded including full use of absolute and relative references (whenever necessary).
• Compulsory ratios (2) are calculated correctly.
• Separate calculation sheet(s)
• Values are from formulas or pivot tables, not hard-coded including full use of absolute and relative references (whenever necessary).
• One of the compulsory ratios is calculated correctly.
• Separate calculation sheet(s)
• Values are from formulas or pivot tables, not hard-coded including some use of absolute and relative references (whenever necessary).
• Some data on calculations sheet is hard-coded (not from formulas or pivot tables).
• No calculation sheets added.
8
High Distinction
Distinction
Credit
Pass
Unsatisfactory (Fail)
Report
• Meets Pass requirements
• Creative and attractive dashboard design that is also functional (e.g. don’t use exotic and hard-to-interpret designs, such as 3D charts unless you have a reason).
Plus the following 3 requirements:
• One graph or table is interactive. Use a slicer, a form control, an Active-X control, or similar. A pivot table on its own is not sufficiently interactive.
• Spark lines in a table
• Icons or a colour scale from conditional formatting used in a table. The icons or colours must be explained within the dashboard.
• Meets Pass requirements
• Creative and attractive dashboard design
• Plus 2 of the 3 High Distinction requirements (interactive, spark lines, conditional formatting).
• Meets Pass requirements.
• Creative and attractive dashboard design.
• Plus 1 of the 3 High Distinction requirements (interactive pivot table, spark lines, conditional formatting)
• Dashboard fits within a widescreen display with a 1440 x 900 resolution
• Four (4) or more ratios including sales growth ratio and inventory turnover ratio (these two ratios are compulsory). You have to choose the other 2 (or more) additional ratios
• 1-3 graphs and 1-2 tables, but no more than 4 graphs/tables
• Reports based on calculations, not hard coded numbers.
• Your name and date that updates automatically appear in the footer section of the page when printed
• Does not meet basic (Pass) requirements (e.g. less than 3 ratios shown, too large, not enough graphs/tables, or report based on hard coded numbers.
