Prepare an “Instructions” worksheet that explains how to use your model. You should also
include your “Internal Memorandum” in the “Instruction” worksheet. The maximum total
words in the “Instruction” worksheet are 1,000. Your “Internal Memorandum” should be
copied and pasted in the “Instruction” worksheet.
“Instruction” should include the following information:
• Your name and name of the company
• Explain the content and objective of the dashboard you created and why you choose
those included in your dashboard report.
• Indicate location and explain purpose of keysales analysis for 2021 to be presented at
the senior management meeting (see “Calculations” below for more details):
1. Sales revenue growth ratio per branch (how you calculate and use cell “YYY”
from worksheet “XXX”)
Growth rate is difference between current period revenue and previous period reve
nue divided by previous period revenue.
2. Monthly sales ($) and gross profit % (how you calculate and use cell “YYY” from
worksheet “XXX”)
3. Monthly sales breakdown by customer type
4. Sales mix and gross profit mix. The mix is calculated based on figures from pivot
tables (how you calculate and use cell “YYY” from worksheet “XXX”)
5. Top 3 best-performing product line based on sales (how you calculate and use cell
“YYY” from worksheet “XXX”)
• You much include an “Internal Memorandum” (at least 700 words) to your boss
Financial Manager, Eddy Guy. In the “Internal Memorandum”, you have to
1. Identify at least one area of business improvement
2. Include at least one interesting finding that you discovered when analysing the
sales dataset
“Data Validation Input”
In the “Data Validation Input” worksheet, you have to create three data validation rules that
prevent invalid data to be entered. Such as:
1. Next month there are ten products to be imported from United States. You need to
create a data validation rule that sales price value that is not lower than the unit cost
value and provide a stop message indicating the error. User can enter but they must
enter a value that meets the criteria.
2. Insert a CUSTOM data validation rule on the “time” field to make sure that the time is
work time between 10:00:00 am and 21:00:00 pm. The stop message indicates the
error. User can enter but they must enter a value that meets the criteria.
3. Insert a CUSTOM data validation rule on the “Invoice ID” filed to prevent entering
data whose format is different from the one in the dataset. The stop message indicates
the error. User can enter but they must enter a value that meets the criteria.
5
“Calculations”
Data processing may include PivotTables, PivotCharts, Excel Table, Slicers, formula, etc.
You are required to perform all the necessary data processing for each of the key sales
analysis in the “Calculations” worksheet.
You need to decide on the appropriate type of PivotCharts, Charts to use, and Slicers
to include.
• Cal1_MthSalesGrowthRate: Sales revenue growth rate per branch. (Hint:
Sales growth rate is the difference between current period sales and previous period
sales divided by previous period sales.)
• Cal2_MthSales_GP%: Monthly sales ($) and gross profit %. (Hint:
You are not allowed to change, e.g. add column for calculation, the dataset.)
• Cal3_MthCustomerTypeSales: Monthly sales breakdown by customer type
• Cal4_SalesMix_GPMix: Sales mix and gross profit mix (Hint: The mix is calculated
based on figures from pivot tables)
• Cal5_Top3PdtLine: Top 3 best-performing product line based on sales
o Include gross profit % in your presentation
o You are not allowed to change (e.g. add column for calculation) the dataset
o Sort in descending order of sales
o Use the gross profit % created from Cal2_MthSales_GP%
“Dashboard Report”
You should construct a well-designed, interactive, professionally presented business
dashboard in the “Dashboard Report” worksheet (see marking rubric for more details). The
report must be fitted into one page.
Submission Date/Time
• Submission Date/Time: Sunday 16 October 2022, 4:30 pm. Penalties apply for late
submission.
• Submit the Excel file (.xls or .xlsx) via the Individual Assignment link under
Assessment Task 3: Individual Assignment section on Moodle.
• “Instructions” worksheet: Less than 1,000 words.
6
Marks
This assignment is worth 20% of the total marks for this unit. Please see Marking Rubric
below to understand how your assignment will be assessed.
Mark Breakdown per Task Tasks Marks (18)
(1) Instructions 25%
(2) Data Validation Input 15%
(3) Calculations 30%
(4) Dashboard Report 30%
Total 100%
Reference List
Few, S. (2013). Information dashboard design: Displaying data for at-a-glance monitoring (Vol. 5).
Burlingame, CA: Analytics Press.
Lavinsky,D. (2013). Executive Dashboards: What They Are And Why Every Business Needs One.
[Online]. Available at:http://www.forbes.com/ sites/davelavinsky/2013/09/06/executive-
dashboards-what-they
Sedrakyan, G., Mannens, E., and Verbert, K. (2019). Guiding the choice of learning dashboard
visualizations: Linking dashboard design and data visualization concepts. Journal of
Computer Languages, 50, 19-38.
7
Marking Rubric High Distinction
(HD)
Distinction (D) Credit (C) Pass (P) Unsatisfactory (F)
Instructions (25%) Meets All Basic
Requirements PLUS:
• Professional
quality: Concise,
but well-explained
• Error free (e.g. no
grammatical
mistakes)
• Structure of the
spreadsheet is very
clear (e.g.
headings)
• Followed all
instructions as
indicated above
Meets All Basic
Requirements, but
few errors:
• Instructions and
headings mostly
clear, and suitable
for distribution to
a professional
audience
• Followed most
instructions as
indicated above
Meets All Basic
Requirements but
some errors:
• Instructions and
headings could be
clearer, but the
instructions are
understandable
• Followed some
instructions as
indicated above
Meets Basic Requirements
(see below), but some
elements were not
explained well. Should not
be distributed
without editing (e.g. poor
grammar, structure of
workbook or some variable
definitions not explained
well)
1. Author & company
name
2. Objective of dashboard
3. Justified
recommendation
4. Location of specific
information (and other
requirements) as
indicated above
5. Explain the purpose of
each key analysis
Does not meet Basic
Requirements:
• Instructions
missing or hard to
understand,
AND/OR no
headings
• Document contains
too many errors
• Cannot be
distributed to a
professional
audience
• Did not follow the
instructions as
indicated above
8
High Distinction
(HD)
Distinction (D) Credit (C) Pass (P) Unsatisfactory (F)
Data Validation
Input (15%) • Three data
validation rules
are created as
indicated above
• Followed all
instructions as
indicated above
• Two data
validation rules
are created as
indicated above
• Followed all
instructions as
indicated above
• One data
validation rule is
created as
indicated above
• Followed more
than two
instructions as
indicated above
• One data validation
rule is created as
indicated above
• Followed only one
instruction as
indicated above
• Fail to exclude
invalid data
• Fail to follow
any instructions
as indicated
above
9
High Distinction (HD) Distinction (D) Credit (C) Pass (P) Unsatisfactory
(F)
Calculations
(30%)
• Values are from formulas
or pivot tables, not hard‐
coded including full use
of
absolute/relative/mixed
references (whenever
necessary).
• Used Pivot Tables
(included calculated field
where appropriate) and
applied proper formatting
• Professional layout of
calculations (e.g. easy to
follow), very‐well
presented
• All five key analyses
calculated correctly
• Good choice of
PivotCharts (include
combined charts) and/or
Charts and/or
sparklines/icons/databars
for all five key analyses.
• Good choice of slicers
and they are working
properly
• Values are from formulas
or pivot tables, not
hardcoded including full
use of
absolute/relative/mixed
references (whenever
necessary)
