Assignment 2 Instructions Rubric Dashboard ACC ACF 2400 s2 2018 Monash University PDF

Preview

Summary

Download Assignment 2 Instructions Rubric Dashboard ACC ACF 2400 s2 2018 Monash University PDF

Description

ACC ACF 2400 Semester 2, 2018 Individual Assignment: Creating a Business Dashboard Overview Dashboards (or enterprise dashboards) are an analytics tool used to visualise Big Data across all industries (Young, 2017). As explained in the lecture, dashboards provide critical reporting and metrics information and are important in business management. Business owners and managers benefit will benefit by using dashboard because they provide information on business performance that can be evaluated easily and will be useful to identify areas for improvement (Barned, 2011). Dashboard 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. One of the key challenges when developing dashboards is to choose what metrics to track (Lavinsky, 2013); however, the benefits outweigh the challenges. After all dashboards bring speed and versatility. They allow viewing the data the way that suits you best or the way that is best for sharing and storytelling to understand performance better in the organisation (Young, 2017).

Source: Forbes

Learning how to create dashboards is a valuable skill for accountants. Nowadays, dashboard skills are in great demand by employers.

Edward Tello Chief Examiner ACC ACF 2400 s2 2018

1

Learning objectives assesse assessed d The purpose of this assessment task is to give you practice in designing and developing a reporting system using good spreadsheet design practice. You would need to conduct some independent research to find out how to create different elements in your report, especially if you are attempting the high distinction requirements. Specific learning outcomes achieved with this assignment include: Objective 4:

Synthesise design principles to develop financial models that assist in decision making.

Objective 5:

Apply critical thinking, problem solving and communication skills to analyse, evaluate and interpret business processes and the accounting data that is generated.

This assessment task is connected to Internal Controls and the COSO framework. Recall from Week 4 that internal controls are methods and procedures designed to provide reasonable assurance that an entity operates effectively and efficiently, has a reliable financial reporting system and complies with applicable laws and regulations. The COSO Framework was designed to help businesses establish, assess and enhance their internal control. There are two types of COSO frameworks (the Internal Control framework and the Enterprise Risk Management (ERM) framework). This assessment task focuses on the monitoring component which is present in both COSO frameworks.

Monitoring activities comprise ongoing evaluations, separate evaluations, or some combination of the two and are used to ascertain whether each of the internal control components is present and functioning. Although they vary in scope and frequency, ‘the findings are evaluated against criteria established by regulators, standard-setting bodies, or management and the board of directors, and deficiencies are communicated to management and the board of directors as appropriate’ (Deloitte, n.d.). As mentioned in Edward Tello Chief Examiner ACC ACF 2400 s2 2018

2

Week 5 Lecture, dashboard reporting is related to principle 16 which states that the organisation selects, develops, and performs ongoing and or separate evaluations to ascertain whether the components of internal control are present and functioning. When evaluating the internal controls, management can use different mechanisms such as designing and implementing dashboard reporting. They could use dashboards, for example, for annually monitoring the key risk and performance indicators or communicating selected monthly key risk and performance indicators.

General Instructions In March 2018, you were hired to work as a junior accountant at Aussie Phones & Accessories Pty Ltd which is an Australian retail business that sells mobile phones and accessories. The company has 2 shops; one in Melbourne CBD (main) and one in St Kilda. After one of the bi-weekly meetings, Chief Executive Officer (CEO) Juan Palacios and Finance Manager Anna Cummings asked you to design a dashboard that would be used by senior management to assess performance. Juan and Anna are aware of your Excel skills so they are confident that you will do well. When asking advice to Ying Yue (Sales Manager) and Mark (Human Resources Manager), Mark suggested including 3 ratios and using 4 panels. What is the definition of a ratio? It is the quantitative relation between two amounts showing the number of times one value contains or is contained within the other. Ying Yue and Mark provided an Excel file with some spreadsheets that you might want to use. The file has 4 worksheets, namely, Employees, Products, Purchases, and Sales Quantity. You might need to investigate which ratios you can use with the data provided. Ying Yue also told you that, regardless of the cost, mark-up (profit) is always 30%. Juan and Anna’s request was broad which gives room to creativity so your first task is to decide how to define ‘performance.’ This is an individual assignment. There is no fixed answer, so be creative. 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 2018_Aussie Phones & Accessories Pty Ltd_data_Monash University.xlsx). You must use the data provided in your calculations; however, be careful because you may need to restructure some data on the Calculation sheet to ensure data is in the format you need. Instructions on how to build dashboards have been published in different journals and/or magazines and/or online resources. The Journal of Accountancy published an article that discusses how to create complex dashboard reports using Microsoft Excel 2007 and 2010. You can search this article in the EBSCOhost research databases (which is one of the databases available at the Monash Library website). If you are not sure about how to use the library databases, contact one of the librarians. However, you do not have to build such a complex system if your spreadsheet skills are not well developed. Details of the article are as follows: Lehman, M.W., Lehman, C.M. and Feazell, J. (2011) “Dashboard your scorecard”, Journal of Accountancy, Vol. 211 Issue 2, p20-27 Edward Tello Chief Examiner ACC ACF 2400 s2 2018

3

The pictures below contrast 2 different approaches. The example on the left shows 7 ratios in a non-interactive dashboard, with three graphs and one table of numbers. The ratios might not be connected to this assessment task; however, you must pay attention to the design features because it could earn a ‘Pass’ (provided the instructions, input, and calculation sheets are acceptable). The example on the right is from the “Dashboard your Scorecard” article (see above). Bear in mind that it does not show ratios so it is not acceptable. Nonetheless, it illustrates elements that will earn higher marks such as:   

Dashboard is interactive (note the drop-down box in the bottom right graph to select the person shown); Dashboard 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; Dashboard uses spark lines (within cell graphs) in the top right table to show historical changes.

Scott Edwards 14%

Analysis of Sales Trends

Allen Pearson 28%

Mary Daniel 13%

Mark Stevens 15%

Representative Allen Pearson Janet Sellers Mark Stevens Mary Daniel Scott Edwards

Janet Sellers 30%

Scott Edwards Mary Daniel Mark S tevens Janet Sellers

Allen Pearson -

500

1,000

Fail or Pass

Gov ernment

Residential

1,500

Percent Chan ge

Year-to-Date Dollar Sales

2% -4% 8% -12% 3%

350 300 250 Unit 200 Sales 150 (000s ) 100 50 New England

Unit Sales (000s) Commercial

Prior Year Current Units Year Units 924 942 1,053 1,015 481 522 521 464 452 466

Red Classic

River Southern Waverly Magic Charm

White Pine

Brick Styles

Distinction o orr High Distinction

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); 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. This is a major assessment task in which you are expected to put considerable amount of effort and time to obtain higher grades. You could ask your tutor for some guidance about the dashboard or some of the commands. Do not expect teaching staff to give you the answer directly. 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 (e.g. what is conditional formatting? OR what is custom data validation?). Asking those kind of questions suggests a lazy intellectual approach that is not consistent with university-level study.

Edward Tello Chief Examiner ACC ACF 2400 s2 2018

4

Required 





You must use different worksheets which must be grouped into four (4) groups, namely, Instructions, Data Input, Calculation, and Report. Each group of worksheets (when applicable) must be labelled with a different tab colour [HINT: Use right-click on the tab and move your mouse over the ‘Tab Colour’ option]: 1) The first group (Instructions) must have only 1 worksheet (your ‘internal memorandum’ must be copied and pasted into the ‘Instructions’ worksheet); 2) The second group (Data Input) must have 4 worksheets. You are required to copy the tables into the ‘Data Input’ sheets. Use one worksheet for each table provided in the file ACC ACF 2400 s2 2018_Aussie Phones & Accessories Pty Ltd_data_Monash University.xlsx. Tables provided are as follows: ‘Employees’, ‘Products’, ‘Purchases’, and ‘Sales Quantity.’ Please copy and paste ‘Monash Copyright’ logo as well (first table ‘Employees’). 3) The third group (Calculation) must have 4 worksheets (one worksheet to calculate each ratio – 3 in total: Calculation 1, Calculation 2, and Calculation 3 – AND one worksheet labelled ‘Additional Calculations’ – which should contain calculations used in the 4th dashboard panel); and 4) The fourth group (Report) must have only 1 worksheet. Prepare an ‘Instructions’ sheet that provides general details about your spreadsheet. In your ‘Instructions’ sheet, you must also include an Internal Memorandum to the CEO Juan Palacios (see details below). Aim for approximately 1,200 words (including internal memorandum) (+/- 10%). The ‘Instructions’ sheet must include the following information: 1) Your name and the name of the company. 2) Explain how you inserted the Data Validation rules created and the logic behind the first Data Validation rule (see below) AND the reason why you chose the 3 ratios and how you calculated them. In terms of the first data validation rule, you must also explain the logic behind it. Please note that to get marks for the first data validation rule, it must work and it must be logical. 3) Explain the logic behind the dashboard you created [HINT: You might want to explain why you choose to include that information in your dashboard AND/OR why you decided to present it that way]. 4) Internal Memorandum - You MUST include an internal memorandum that will be sent to the CEO Juan Palacios (aim for approximately 500 words).  In your internal memo, you MUST include 2 interesting findings that you discovered when analysing the data (and also include the reference; for example, see Panel XX dashboard OR Cells XX:XX Worksheet ‘aaaa’, etc.).  Additionally, you MUST include 2 recommendations to the CEO based on your analysis of the data. Although the internal memorandum

Edward Tello Chief Examiner ACC ACF 2400 s2 2018

5





 

must be pasted into the ‘Instructions’ sheet, it will be marked separately (see marking rubric). 5) Please refer to Chapter 16 of ‘Model business letters, emails and other business documents’ by Taylor (2015) when preparing the internal memorandum. In Chapter 16 Taylor (2015) provides guidance about how to write an internal memorandum. A link to the e-book has been included in the ‘Reading List’ (see Moodle under ‘Individual Assignment – Dashboard’). Your ‘Data Input’ worksheets MUST include the tables provided (one worksheet per table). Additionally, you MUST create three (3) Data Validation (DV) rules that prevent invalid data to be entered. The Data Validation rules must be as follows: 1) Create a DV rule that prevents users entering invalid data and provides an error message guiding the user [HINT 1: Users could retry, but they must enter a value that meets the validation criteria] [HINT 2: Apply this DV rule to a field in any of the tables. Once you choose the field, add 5 rows to that table and then insert the DV rule in those cells]. 2) Insert a CUSTOM DV rule on the 'Product ID' field that prevents entering a duplicated Product ID. In other words, with this DV rule, Product ID should not be duplicated [HINT: Add 5 rows to the 'Product' table and then insert the CUSTOM DV rule in those cells]. 3) Insert a CUSTOM DV rule that prevents entering more than 5 digit numbers [HINT: You need to make sure that each cell only allows 5 characters and those characters must be numbers] [HINT 2: Add another 5 rows to the 'Product' table and then insert the CUSTOM DV rule in those cells]. The ratios have to be calculated on the ‘Calculation’ sheets (as per above) using the correct cell referencing. Detailed explanations should be provided in the ‘Instructions’ sheet. Although you can use EITHER Excel tables or Pivot Tables (and/or Pivot Charts), you MUST use at least one Pivot Table (and/or Pivot Chart). Construct a well, professionally presented Dashboard which must be inserted into the ‘Report’ sheet (see marking rubric for more details).

Marks Criteria for marking: This assignment is worth 18% 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.

Edward Tello Chief Examiner ACC ACF 2400 s2 2018

6

Mark breakdown per task Task

Marks

A (Instruction sheet excluding Internal Memorandum)

2.5

B (Internal Memorandum)

2.5

C (Data Input sheet)

3

D (Calculations)

2

E (Report)

8

Total

18

Submission: Submission is via Moodle on Wednesday 10 October 11.55pm (Week 11) (penalties apply for late submission – see Unit Guide for more details).

Submission format and Wor Word d Limit:  

.xls or .xlsx spreadsheet file. Assume that your tutor only has access to Microsoft Excel so you must save and submit your work as an Excel file. Instructions sheet: Approximately 1,200 words (+/- 10%)

Edward Tello Chief Examiner ACC ACF 2400 s2 2018

7

Marking Rubric for Assessment Task High Distinction Instructions Meets Basic Requirem Requirements ents PLUS:  Professional quality  Concise, but wellexplained  Error free (e.g. grammatical mistakes)  Structure of the spreadsheet is very clear (e.g. headings)  Clear examples of special features (such as slicers or data validation rules) provided (e.g. using screenshots)

Edward Tello Chief Examiner ACC ACF 2400 s2 2018

Distinction All Basic Requirements, but few errors

Credit All Basic Requirements but some errors

Pass Meets Basic Requirements (see below), but some Instructions and Instructions and headings elements were not headings mostly clear, could be clearer, but the explained well and suitable for instructions are usable distribution to a Should not be distributed professional audience Examples provided for without editing (e.g. poor some, but not all, special grammar, structure of Examples provided for all features (e.g. conditional workbook or some features, but few formatting) variable definitions not problems to understand. explained well) Basic requirements Shows:  Author & company name  Explanation of information included in dashboard and the logic behind as well as data validation rules  Formula of each ratio used as well as justification of ratios

8

Unsatisfactory Does not meet basic requirements Instructions missing or hard to understand AND/OR no headings Document contains many errors Cannot be distributed to a professional audience

High Distinction Data Input

Calculation

  

Distinction

Credit

Pass

All validation rules are created as indicated above (see ‘required’ section) = 3 marks (1 mark each) Two (2) data validation rules are created as indicated = 2 marks One (1) data validation rule is created as indicated = 1 mark

  Values are from formulas or pivot tables, not hardcoded including full use of absolute/relative/mixed references (whenever necessary).   Used Excel Tables and/or Pivot Tables AND at least 1 Pivot Table (or Pivot Chart)   Professional layout of calculations (e.g. easy to follow), very-well  presented  All ratios are calculated correctly

Edward Tello Chief Examiner ACC ACF 2400 s2 2018

Values are from formulas or pivot tables, not hard-coded including full use of absolute/relative/mixe d references (whenever necessary) Used Excel Tables and/or Pivot Tables AND at least 1 Pivot Table (or Pivot Chart) Good layout of calculations (e.g. easy to follow) All ratios are calculated correctly

 Values are from  formulas or pivot tables, not hard-coded including some use of absolute/relative/mixe d references (whenever  necessary)   Used Excel Tables and/or Pivot Tables AND at least 1 Pivot  Table (or Pivot Chart)  Reasonable layout of calculations (e.g. easy to follow)  One or two ratios (is)are calculated correctly

9

Unsatisfactory  Fail to exclude invalid data

Some data on  No calculation sheets calculations sheet is added hard-coded (not from formulas or pivot tables) Used Excel Tables only. Reasonable layout of calculations (e.g. easy to follow) One or two ratios (is)are calculated correctly

Dashboard Report

High Distinction Credit Distinction  Meets Pass  Meets Pass  Meets Pass  requirements requirements. requirements  Creative and  Somewhat creative and  Plus all (3) Credit attractive dashboard attractive dashboard requirements (e.g.  design design. interactive, spark lines, 2 of the 3 Credit 1 of the 3 Plus Plus   conditional formatting) requirements (e.g. following requirements:  Highly creative and interactive, spark  One graph or table is attractive dashboard interactive. Use a lines, conditional design that is also slicer or a form  formatting) functional (e.g. don’t use control or similar. A exotic and hard-topivot table on its  interpret designs, such own is not as 3D charts unless you sufficiently have a reason) interactive  Spark lines i...