ACF2400Building a Business Dashboard assignment 代写

ACF2400Individual Assignment
Building a Business Dashboard Trimester A2017
Designing a Reporting System 15%

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. You must design a spreadsheet to be used by managers involved in purchasing, sales and inventory management. There is no fixed answer, so be creative! The spreadsheet must performratio analysisto 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 (see pages 5-7).
A data set is supplied with this guide in Moodle (ACF2400 2017 TriAInventory 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 (http://www.journalofaccountancy.com/issues/2011/feb/20092427.html), but you don’t 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 six ratios in a non-interactive dashboard,with three graphs and one panel of numbers. This 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 the instructions sheet, 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 grades:
·         It is interactive (note the drop-down box in the bottom right panel to select the person shown);
·         It uses conditional formatting icons (arrows) in the top right panel to indicate the direction of change and so makes the data easier to digest at a glance;
·         It uses spark linesin the top right panel (within cell graphs) to show historical changes.
 

Pass– Credit (Non interactive)	High distinction (Interactive)

 

Learning objectives assessed

The purpose of this assignment is to give you practice indesigning 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 problem solving and communication skills to analyse, evaluate and interpret accounting data.
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 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?

 

Marks

Criteria for marking: This assignment is worth 15% of your total marks. You will be assessed forthe 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 sheet)	4
B (data input sheets)	3
C (calculations sheet(s))	1
D (report sheet - dashboard)	7
Total	15

   
 

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 shouldinclude this information:
·         Your name
·         Purpose of spreadsheet
·         Description of layout
·         Where and how 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, formatthe data input sheets (the ACF2400 2017 TriA Inventory Statistics.xlsx file available on Moodle) to highlight invalid values.
 
c)       Construct the data processing(calculation)sheet or sheets.This is where ratios are calculated and intermediate pivot tables assembled (see marking rubric for more details). It may be useful to separate the different types of calculations into different sheets so that they may be used as the basis for the tables and graphs on your dashboard (the report sheet).
 
d)      Construct an attractive report sheet (see marking rubric for more details).

 

Submission

Submission is via Moodle on Thursday 13 April, 5:00pm
 

Submission format

.xls or .xlsx spreadsheet file. Assume that your lecturer 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

Instructionsheet: Around 300 words.
 

Resources

The assignment is designed to be completed using Microsoft Excel. It can potentially also be completed using other spreadsheet platforms, such as Google Sheets or Open Office Calc; however, your lecturer is 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: 489,000 results for Excel, 111,000 results for Google sheets
·         Conditional formatting icons: 159,000 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: 185,000 results for Excel, 16,700 results for Google Sheets
Please ask your lecturer for help in implementing these commands if you are having trouble getting them to work. This is a major assignment in which you are expected to put in a substantial amount of work to obtain higher grades, but we recognise that the commands can be difficult to implement. Conditional formatting icons, in particular, can be very difficult to customise. Do not expect your lecturer to give you the answer directly, but he will try to help you to work out why your model is not working properly. Your lecturer 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.

Marking Rubrics