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 noninteractive 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.