ACC_ACF2400_Individual_Assignment
会计信息系统作业代写 Overview Business dashboard is an analytical tool that provide at-a-glance visual of business performance. The design varies considerably from
Overview
Business dashboard is an analytical tool that provide at-a-glance visual of business performance. 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 highlight key information of the business such as trends, risks, performance, analyses. Dashboards are designed to be interactive to present clear and relevant information at management meetings, enable navigation of detailed information when required. A non-interactive dashboard can be cluttered making it difficult to show the important information or not possible to show every important information at once, not able to drill down for detail.
When designing a business dashboard, start with the end in mind. In doing so, you must identify the information you wish to present on it. Think from a business perspective. For example, what relevant information do you need to know to understand your current business status? What information do you need to strategise for improvement of business performance? Once you have outlined what you want to know, then you can work on the data required and the formulae needed for calculation, which will be useful in building your business dashboard. Below is an example of an interactive dashboard for Sales Analysis.
Learning Objectives Assessed 会计信息系统作业代写
The purpose of this individual assessment is to help you practice designing and developing a management reporting system by using good spreadsheet design practice. You will need to conduct some independent research to find out how to create features (not taught in this unit) of your dashboard, particularly if you are attempting to achieve upper High Distinction (HD) requirements. The following learning outcomes are included in this individual assessment:
- Examine the role of business processes and the use accounting information systems have in capturing and managing information which supports the needs of stakeholders.
- Synthesise design principles to develop financial models that assist in decision-making.
- Apply critical thinking, problem solving and communication skills to analyse, evaluate and interpret business processes and the accounting data that is generated.
Instructions 会计信息系统作业代写
This individual assessment is designed to test your Excel skills on the content covered in this unit. A dataset is provided in Moodle (see Excel file: ACC_ACF2400_Ind_Assignment.xlsx in the Individual Assignment folder under Assessment Task 3: Individual Assignment section).
Apart from the adding data validation rules, you are NOT allowed to change (e.g. add column for calculation) the dataset in the SalesData worksheet.
This assignment is designed to be completed using Microsoft Excel for Windows. If you use Mac, ensure all features included in your dashboard work properly in Microsoft Excel for Windows before submitting your assignment because the teaching team will be using Microsoft Excel for Windows to mark the assignment.
You have been hired as a graduate accountant at NorthField Group, Australia after a competitive selection process. One of your duties is to work with the Business Controller, Cooper Filan. Cooper handed you an Excel workbook with the company’s sales data from December 2020 to December 2021 (ACC_ACF2400_Ind_Assignment.xlsx). Cooper asked you to prepare a business dashboard that presents sales and profitability analysis for 2021. He also asked you to create data validation rules in the SalesData worksheet in the workbook.
Submission Date/Time & Procedures 会计信息系统作业代写
Submission Date/Time: Sunday 22 May 2022, 4.30pm. Penalties apply for late submission (10% per day including weekends).
Submit the Excel file via the Individual Assignment Submission link under Assessment Task 3: Individual Assignment section on Moodle.
Marks
The assignment is worth 15% of the total marks for this unit (see Marking Rubric below to understand how your assignment will be assessed).
Required
Save your file as 2400_<StudentName_StudentID>_Dashboard.xlsx. The completed assignment will contain the following nine (9) worksheets:
- Documentation (note 1)
- Dashboard (note 2)
- SalesData (note 3)
- Six Calculation worksheets (note 4)
Notes:
1.Prepare a Documentation worksheet with good navigation (see marking rubric for more details). Include the following information:
- Your name and name of the company
- Dashboard objective: Explain the purpose of the dashboard you created.
- Explain the findings of each key sales and profitability analysis for 2021 to be presented atthe management meeting:
Key Analysis 1: Monthly sales ($) and gross profit margin (%) for 2021 会计信息系统作业代写
- Filter by branch and product category
- You are not allowed to change (e.g. add column for calculation) the dataset in the SalesData worksheet
- Hint 1: Include only sales data from 2021
- Hint 2: Gross profit margin is a calculated field in the pivot table
- Hint 3: Gross profit margin (%) is calculated by subtracting cost of goods sold (COGS) from sales. That difference is then divided by sales.
- Hint 4: Filter using Slicer (or other features/tools)
Key Analysis 2: Monthly sales growth rate (%) by branch
- Filter by loyalty program
- Hint 1: Sales growth rate is calculated by subtracting previous period sales from current period sales. That difference is then divided by previous period sales.
- Hint 2: You may need to perform additional calculations using data from the pivot table(s) you created
- Hint 3: Filter using Slicer (or other features/tools)
Key Analysis 3: Sales by loyalty program by branch
- Sort in ascending order of sales within branch
- Filter by product category
- Hint 1: Include only sales data from 2021
- Hint 2: Filter using Slicer (or other features/tools)
Key Analysis 4: Sales mix by product category 会计信息系统作业代写
- Filter by branch
- Hint 1: Include only sales data from 2021
- Hint 2: Sales mix is the proportion of each product category sales relative to the total sales
- Hint 3: Filter using Slicer (or other features/tools)
Key Analysis 5: Gross profit mix by product category
- Filter by branch
- You are not allowed to change (e.g. add column for calculation) the dataset in the SalesData worksheet
- Hint 1: Include only sales data from 2021
- Hint 2: Gross profit is a calculated field in the pivot table
- Hint 3: Gross profit mix is the proportion of each product category gross profit relative to total gross profit
- Hint 4: Filter using Slicer (or other features/tools)
Key Analysis 6: Worst performing product category based on sales
- Filter by branch
- Hint 1: Include only sales data from 2021
- Hint 2: Use Value Filters>Top 10
- Hint 3: Filter using Slicer (or other features/tools)
2.
Construct an interactive professional dashboard in the Dashboard worksheet (see marking rubric for more details).
- Ensure dashboard prints legible on one page
- Hint 1: PivotCharts and slicers are copied from respective Calculation worksheets while tables, data bars and sparklines are generally created in the Dashboard worksheet
- Hint 2: Check out Page Setup for print setting
3. 会计信息系统作业代写
Create the following data validation rules in the SalesData worksheet (see marking rubric for more details):
- PaymentMethod field (E2:E1088) accept only: Cash, Credit card, Debit card and Mobile payment
- QtySold field (H2:H1088) accepts values between 0 to 1000
- UnitCost field (J2:J1088) accepts only numeric values
4.
Perform all the necessary data processing for each of the key analyses in the Calculation worksheets provided in the workbook (see below). Data processing may include PivotTables, PivotCharts, tables, formulae, and so on. You need to decide on the appropriate type of PivotCharts, tables and slicers to include and what to copy and paste to the Dashboard worksheet.
- Cal1_Mthly_Sales_GPM% àKey Analysis 1
- Cal2_MthlySalesGrowthRate àKey Analysis 2
- Cal3_LoyaltyProg àKey Analysis 3
- Cal4_SalesMix àKey Analysis 4
- Cal5_ GPMix àKey Analysis 5
- Cal6_BottomPdtCat àKey Analysis 6
