ACC ACF 2400 Building a Business Dashboard assignment代写

  • 100%原创包过,高质代写&免费提供Turnitin报告--24小时客服QQ&微信:120591129
  • ACC ACF 2400  Building a Business Dashboard assignment代写

     
    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 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, 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%
    Analysis of Sales Trends
    Allen
    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:

    ACC ACF 2400  Building a Business Dashboard assignment代写
    .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.
    4
    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.
    5
    Marking Rubric for Spreadsheet
    High Distinction
    Distinction
    Credit
    Pass  Unsatisfactory (Fail)
    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)
    High Distinction
    •  Professional quality.
    •  Concise, but well-
    explained.
    •  Error free (e.g.
    grammatical mistakes).
    •  Structure of the
    spreadsheet is very clear.
    •  Clear examples of special
    features, such as slicers,
    provided.
    All basic requirements,
    but few errors.
    Instructions mostly clear,
    and suitable for
    distribution to a
    professional audience.
    Examples provided for all
    features, but few
    problems to understand.
    All basic requirements but
    some errors.
    Instructions could be
    clearer, but the
    instructions are usable.
    Examples provided for
    some, but not all, special
    features (e.g. conditional
    formatting)
    Meets basic requirements,
    but some elements were
    not explained well.
    Should not be distributed
    without editing. E.g. some
    poor grammar, structure of
    workbook or some variable
    definitions not explained
    well.
    Does not meet basic
    requirements.
    Instructions missing or hard
    to understand.
    Document contains many
    errors.
    Cannot be distributed to a
    professional audience.
    6
    High Distinction
    Distinction
    Credit
    Pass  Unsatisfactory (Fail)
    Input  • 3 different rules to
    highlight invalid data on
    all four input sheets.
    • Each rule must apply to
    multiple cells, and overall,
    all data cells must be
    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.
    • 2 different rules to
    highlight invalid data
    on all four input
    sheets, OR
    • 3 rules but not all input
    sheets or not all data
    cells are covered.
    • One rule to highlight
    invalid data, OR
    • 2 rules, but not all input
    sheets or not all data
    cells are covered.
    • This is an advanced task
    for students attempting
    to obtain an overall
    grade of more than
    pass.
    • Input sheets are
    essentially the same as
    sheets downloaded.
    7
    High Distinction
    Distinction
    Credit
    Pass  Unsatisfactory (Fail)
    Calculations • Separate calculation
    sheet(s)
    • Values are from formulas
    or pivot tables, not hard-
    coded including full use of
    absolute and relative
    references (whenever
    necessary).
    • Compulsory ratios (2) are
    calculated correctly.
    • Separate calculation
    sheet(s)
    • Values are from
    formulas or pivot
    tables, not hard-coded
    including full use of
    absolute and relative
    references (whenever
    necessary).
    • One of the compulsory
    ratios is calculated
    correctly.
    • Separate calculation
    sheet(s)
    • Values are from
    formulas or pivot tables,
    not hard-coded
    including some use of
    absolute and relative
    references (whenever
    necessary).
    • Some data on
    calculations sheet is
    hard-coded (not from
    formulas or pivot
    tables).
    • No calculation sheets
    added.
    8
    High Distinction
    Distinction
    Credit
    Pass  Unsatisfactory (Fail)
    Report  • Meets Pass requirements
    • Creative and attractive
    dashboard design that is
    also functional (e.g. don’t
    use exotic and hard-to-
    interpret designs, such as
    3D charts unless you have
    a reason).
    Plus the following 3
    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.
    • Meets Pass
    requirements
    • Creative and attractive
    dashboard design
    • Plus 2 of the 3 High
    Distinction
    requirements
    (interactive, spark
    lines, conditional
    formatting).
    • Meets Pass
    requirements.
    • Creative and attractive
    dashboard design.
    • Plus 1 of the 3 High
    Distinction
    requirements
    (interactive pivot table,
    spark lines, conditional
    formatting)
    •  Dashboard fits within a
    widescreen display
    with a 1440 x 900
    resolution
    •  Four (4) or more ratios
    including sales growth
    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
    • Does not meet basic
    (Pass) requirements
    (e.g. less than 3 ratios
    shown, too large, not
    enough graphs/tables,
    or report based on hard
    coded numbers.
     
    ACC ACF 2400  Building a Business Dashboard assignment代写