Assignment: Building a business dashboard
Designing an inventory report dashboard.
Overview
A business dashboard 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. Many dashboards are interactive because it can be difficult to show every important detail at once.
This is an individual assignment in which you design a report to be used by managers involved in purchasing, sales, and inventory management. There is no fixed answer, so be creative! The spreadsheet must perform ratio analysis to show the current status of the inventory holdings of a company. 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 (2016 s2 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 by the Journal of Accountancy (https://www.journalofaccountancy.com/issues/2011/feb/20092427.html), but you don't have to build such a complex system if your spread sheeting 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 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.
Required-
a) Prepare an instruction sheet that explains how to use your spreadsheet. Instructions should be brief. Aim for around 300 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, form at the data input sheets (the 2016 s2 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).
Attachment:- Assignment.rar