Could you please help me with the assignment? The instructions, marking rubric and statistics are in the folders. Thank you.

ACC ACF 2400 - Semester 2, 2017 Individual Assignment 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 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. 1 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 Stevens Janet Sellers Allen Pearson - 500 1,000 1,500 Government Pass Percent Change 2% -4% 8% -12% 3% Year-to-Date Dollar Sales 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 Residential Red Classic River Magic Southern Waverly Charm White Pine Brick Styles 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? 2 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 A (instruction sheets) B (data input sheet) C (calculations) D (report) Total Marks 3 3 2 7 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. 3 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. 4 Marking Rubric for Spreadsheet High Distinction 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) Credit Pass All basic requirements but Meets basic requirements, some errors. but some elements were not explained well. Instructions mostly clear, Instructions could be and suitable for clearer, but the Should not be distributed distribution to a instructions are usable. without editing. E.g. some professional audience. poor grammar, structure of Examples provided for workbook or some variable Examples provided for all some, but not all, special definitions not explained features, but few features (e.g. conditional well. problems to understand. formatting) Distinction All basic requirements, but few errors. High Distinction Professional quality. Concise, but wellexplained. Error free (e.g. grammatical mistakes). Structure of the spreadsheet is very clear. Clear examples of special features, such as slicers, provided. 5 Unsatisfactory (Fail) Does not meet basic requirements. Instructions missing or hard to understand. Document contains many errors. Cannot be distributed to a professional audience. High Distinction Input Credit Distinction Pass Unsatisfactory (Fail) 2 different rules to This is an advanced task 3 different rules to One rule to highlight Input sheets are highlight invalid data for students attempting highlight invalid data on invalid data, OR essentially the same as on all four input to obtain an overall all four input sheets. sheets downloaded. 2 rules, but not all input sheets, OR grade of more than sheets or not all data Each rule must apply to 3 rules but not all input pass. cells are covered. multiple cells, and overall, sheets or not all data all data cells must be cells are covered. 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. 6 High Distinction Credit Distinction Pass Separate calculation Separate calculation Some data on Calculations Separate calculation sheet(s) sheet(s) calculations sheet is sheet(s) hard-coded (not from Values are from Values are from Values are from formulas formulas or pivot formulas or pivot formulas or pivot tables, or pivot tables, not hardtables). tables, not hard-coded not hard-coded coded including full use of including full use of including some use of absolute and relative absolute and relative absolute and relative references (whenever references (whenever references (whenever necessary). necessary). necessary). Compulsory ratios (2) are One of the compulsory calculated correctly. ratios is calculated correctly. 7 Unsatisfactory (Fail) No calculation sheets added. High Distinction Report Credit Distinction Meets Pass Meets Pass Meets Pass requirements requirements requirements. Creative and attractive Creative and attractive Creative and attractive dashboard design that is dashboard design dashboard design. also functional (e.g. don't Plus 1 of the 3 High use exotic and hard-to- Plus 2 of the 3 High Distinction Distinction interpret designs, such as requirements requirements 3D charts unless you have (interactive, spark (interactive pivot table, a reason). lines, conditional spark lines, conditional Plus the following 3 formatting). formatting) 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. 8 Pass Unsatisfactory (Fail) Dashboard fits within a Does not meet basic widescreen display (Pass) requirements with a 1440 x 900 (e.g. less than 3 ratios resolution shown, too large, not enough graphs/tables, Four (4) or more ratios or report based on hard including sales growth coded numbers. 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 Name Square D WL 500 VAC Square D WLM 115 VAC Square D D4V 250 VAC Square D D4A-2N 480 VAC Square D HL-5000 250 VAC Square D D4A-4N 125VAC Omron WL 500 VAC Omron WLM 115 VAC Omron D4V 250 VAC Omron D4A-2N 480 VAC Omron HL-5000 250 VAC Omron D4A-4N 125VAC 230/115 kV 1-3 Phase, 420 MVA 70kV 4-1 Phase (4X 60 MVA) 70kV 1-3 Phase (200 MVA) 115/60V 4-1 Phase (4X 30) 115/60V 1-3 Phase (200 MVA) Double Circuit, Strung on both sides, Lattice Tower 1km Double Circuit, Strung on one side, Lattice Tower 1km Single Circuit, Lattice Tower 1km Double Circuit, Strung on both sides, Tubular Steel Pole 1km Double Circuit, Strung on one side, Tubular Steel Pole 1km Single Circuit, Tubular Steel Pole 1km Type Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Circuit breaker Circuit breaker Circuit breaker Circuit breaker Circuit breaker Transmission line Transmission line Transmission line Transmission line Transmission line Transmission line Unit cost (Jan-Jul 2017) 3850 1575 2800 2100 700 3150 3855 1581 2796 2105 709 3148 10000 13000 9000 10000 8000 2330 1830 1450 2620 2160 1610 Jan-17 73 123 82 161 64 49 350 333 196 180 89 93 291 12 23 8 35 5 140 50 18 4 40 Feb-17 71 33 110 284 9 11 585 285 182 300 9 9 140 153 41 7 102 10 41 19 8 13 32 Mar-17 24 36 5 40 45 4 676 500 333 447 89 68 13 8 95 53 152 12 22 64 18 93 85 Apr-17 147 14 135 68 174 81 803 595 381 370 12 2 49 16 19 36 733 154 264 46 3 63 117 May-17 120 25 153 19 45 11 706 402 399 343 8 2 147 71 12 3 564 53 3 113 47 84 31 Jun-17 10 29 65 56 203 87 833 463 415 336 34 44 144 114 61 30 576 137 53 38 11 2 26 Jul-17 210 15 5 188 51 2 963 607 513 350 113 320 7 49 3 26 586 127 45 104 13 194 185 Name Square D WL 500 VAC Square D WLM 115 VAC Square D D4V 250 VAC Square D D4A-2N 480 VAC Square D HL-5000 250 VAC Square D D4A-4N 125VAC Omron WL 500 VAC Omron WLM 115 VAC Omron D4V 250 VAC Omron D4A-2N 480 VAC Omron HL-5000 250 VAC Omron D4A-4N 125VAC 230/115 kV 1-3 Phase, 420 MVA 70kV 4-1 Phase (4X 60 MVA) 70kV 1-3 Phase (200 MVA) 115/60V 4-1 Phase (4X 30) 115/60V 1-3 Phase (200 MVA) Double Circuit, Strung on both sides, Lattice Tower 1km Double Circuit, Strung on one side, Lattice Tower 1km Single Circuit, Lattice Tower 1km Double Circuit, Strung on both sides, Tubular Steel Pole 1km Double Circuit, Strung on one side, Tubular Steel Pole 1km Single Circuit, Tubular Steel Pole 1km Type Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Circuit breaker Circuit breaker Circuit breaker Circuit breaker Circuit breaker Transmission line Transmission line Transmission line Transmission line Transmission line Transmission line Unit cost (Jan-Jul 2017) 3850 1575 2800 2100 700 3150 3855 1581 2796 2105 709 3148 10000 13000 9000 10000 8000 2330 1830 1450 2620 2160 1610 Jan-17 70 120 80 160 60 50 300 330 200 200 90 90 290 10 20 10 40 10 140 50 20 0 40 Feb-17 0 14 186 301 0 92 290 310 180 190 216 107 169 226 172 14 170 167 408 15 1 92 70 Mar-17 37 73 114 48 93 89 280 290 180 175 234 117 169 8 155 163 170 72 408 202 60 239 167 Apr-17 203 137 198 79 315 253 270 270 170 180 109 3 202 32 28 20 710 308 424 132 105 151 99 May-17 152 138 211 185 22 205 280 250 160 165 143 3 191 227 59 61 170 204 190 170 109 207 65 Jun-17 82 101 12 212 300 258 290 230 150 150 83 111 170 175 174 121 170 233 231 16 175 230 142 Jul-17 339 56 0 323 0 0 300 210 140 135 191 452 0 40 8 62 170 142 91 175 194 306 254 Name Square D WL 500 VAC Square D WLM 115 VAC Square D D4V 250 VAC Square D D4A-2N 480 VAC Square D HL-5000 250 VAC Square D D4A-4N 125VAC Omron WL 500 VAC Omron WLM 115 VAC Omron D4V 250 VAC Omron D4A-2N 480 VAC Omron HL-5000 250 VAC Omron D4A-4N 125VAC 230/115 kV 1-3 Phase, 420 MVA 70kV 4-1 Phase (4X 60 MVA) 70kV 1-3 Phase (200 MVA) 115/60V 4-1 Phase (4X 30) 115/60V 1-3 Phase (200 MVA) Double Circuit, Strung on both sides, Lattice Tower 1km Double Circuit, Strung on one side, Lattice Tower 1km Single Circuit, Lattice Tower 1km Double Circuit, Strung on both sides, Tubular Steel Pole 1km Double Circuit, Strung on one side, Tubular Steel Pole 1km Single Circuit, Tubular Steel Pole 1km Type Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Circuit breaker Circuit breaker Circuit breaker Circuit breaker Circuit breaker Transmission line Transmission line Transmission line Transmission line Transmission line Transmission line Unit cost (JanJul 2017) 3850 1575 2800 2100 700 3150 3855 1581 2796 2105 709 3148 10000 13000 9000 10000 8000 2330 1830 1450 2620 2160 1610 Jan-17 633497 315955 196996 360745 61947 199144 508982 339645 610603 383077 127168 202830 1824221 224296 557174 1782756 1462992 199817 294973 204239 405221 248681 130857 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 7681 329913 314771 754750 744526 539282 174164 118285 257623 207065 158782 120577 472173 623163 205082 578694 285238 175093 375619 653331 111627 539076 389970 433576 41648 43271 142041 111564 100628 114650 418828 305091 588809 864375 626643 266079 223277 742198 596637 1446936 661860 651980 583593 118172 275677 763208 293488 113466 538256 83710 359253 431284 375614 119954 161609 63793 574399 417536 354115 255175 220156 120023 141911 109110 40552 87244 616538 189617 218950 9537 231007 606858 3368302 3107851 1680461 956122 1773520 1451999 1214587 2143629 328983 2432957 1826939 1383481 1396613 927493 951629 652735 1161886 618682 157394 1282864 386400 960949 981588 669109 852466 380535 1107680 2892974 1353930 1393891 407853 164699 402845 722770 367679 388268 1004174 824802 350083 880483 332772 193272 69940 228962 226620 160949 131399 156743 28936 137642 315896 183833 563750 530178 184955 351419 414515 433981 710652 244872 130206 185853 115677 262609 245332 152200 Name Square D WL 500 VAC Square D WLM 115 VAC Square D D4V 250 VAC Square D D4A-2N 480 VAC Square D HL-5000 250 VAC Square D D4A-4N 125VAC Omron WL 500 VAC Omron WLM 115 VAC Omron D4V 250 VAC Omron D4A-2N 480 VAC Omron HL-5000 250 VAC Omron D4A-4N 125VAC 230/115 kV 1-3 Phase, 420 MVA 70kV 4-1 Phase (4X 60 MVA) 70kV 1-3 Phase (200 MVA) 115/60V 4-1 Phase (4X 30) 115/60V 1-3 Phase (200 MVA) Double Circuit, Strung on both sides, Lattice Tower 1km Double Circuit, Strung on one side, Lattice Tower 1km Single Circuit, Lattice Tower 1km Double Circuit, Strung on both sides, Tubular Steel Pole 1km Double Circuit, Strung on one side, Tubular Steel Pole 1km Single Circuit, Tubular Steel Pole 1km Type Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Limit switch Circuit breaker Circuit breaker Circuit breaker Circuit breaker Circuit breaker Transmission line Transmission line Transmission line Transmission line Transmission line Transmission line Unit cost (Jan-Jul 2017) 3850 1575 2800 2100 700 3150 3855 1581 2796 2105 709 3148 10000 13000 9000 10000 8000 2330 1830 1450 2620 2160 1610 Jan-17 159 183 65 164 89 62 128 199 199 171 165 59 181 16 58 175 180 81 161 134 149 107 81 Feb-17 2 104 158 178 55 130 55 358 194 70 296 191 320 85 154 15 103 162 507 46 11 83 78 Mar-17 84 70 219 292 57 96 189 75 29 28 154 58 296 153 101 117 120 70 427 157 50 159 114 Apr-17 80 159 68 51 186 176 143 175 122 257 186 69 166 24 104 37 129 166 182 150 120 181 67 May-17 179 127 193 234 151 275 377 443 142 192 147 3 93 172 66 94 339 305 451 103 65 186 151 Jun-17 192 97 100 175 142 182 163 169 134 157 57 69 173 132 125 94 158 149 181 91 211 312 147 Jul-17 139 70 60 191 152 85 170 66 42 121 112 176 137 105 66 66 160 152 99 109 192 114 95