BISM7202 – Information Systems for Management
Information Systems for Management
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
BISM7202 – Information Systems for Management
for Management
Case Specification: MS Office Assignment
– Office 365 Excel 2019
This specification document is a companion document to the Assessment Guideline.
The Excel template of the expected worksheets are available on the BISM7202 Blackboard site.
The Excel template provided must be used as the basis for the assignment.
You may change the visual formatting (correct typographical errors, change colour, fonts, data
format presentation, column widths, etc) of the worksheets to provide a professional finished
product, and will need to define named ranges, but nothing else unless asked to do so. For
example, do not add or remove rows or columns.
You would be well advised to make your work of the highest presentation quality (e.g. freeze
panes to long pages, use named ranges where appropriate, use lookup functions instead
of nested IFs where appropriate, use hard-coding only where appropriate, use
appropriate fonts and colours, graph axes and titles, etc).
Note: Entering your student name and student number in the Document Control Sheet
should be the first step that you do in undertaking this assignment.
When you develop your solution, you should use (but are not limited to) the functions and
features you were taught in the tutorials. If you need functions or techniques that are not
addressed explicitly in tutorial exercises, you should explore your pre-tutorial reading materials
and preparation exercises or refer to the help component of Excel (or conduct a web search).
Aspects of the assignment have purposefully been designed to train and test a student’s self-
learning ability with a software application, and thus it is possible that not all the functions you
need to use have been directly addressed in a tutorial exercise.
This assignment requires you to complete an Excel workbook file using Microsoft 365 Excel /
Excel 2019 based on the specification in this document. The Excel workbook contains several
sheets you should develop. These sheets are described as follows.
It is highly recommended that, prior to assignment submission, you check that your
solution works on the university machines if you have developed it on your own machine.
Saving your files using the default Excel filetype (i.e. .xslx) is mandatory. Files saved in other
formats, e.g. xlsm, xslb, xls, calc, google sheets, etc., will not be opened
Background and Scenario
An innovative, fairly new, company called Henry's Farm Market (HFM) has been set up that
provides a door-to-door grocery delivery service to the inner-city suburbs of Brisbane. HFM
is set up as a franchise, which means that there is a ‘head’ franchisor with several franchisees
who operate ‘under license’ from the franchisor. HFM has four franchisees in Brisbane. Each
franchisee is a local grocery shop.
As franchisees of HFM, they are licenced to deliver daily groceries by the box to homes and
businesses in nearby suburbs (their ‘franchise area’). Over its two years of operation, HFM has
built up a trustworthy reputation among its customer base. HFM customers receive a ‘set
grocery’ box of groceries each week via their membership subscription program. The length
of membership varies in terms of 7, 21, or 42 weeks, and subscription fees vary by membership
terms.
Henry Thomas, the owner of HFM, has asked you to develop a spreadsheet that will help
refine the franchise area and lower the distance travelled. Henry is very environmentally
conscious and does not want to damage the planet to deliver groceries. He wants you to:
(1) Develop a schedule of employee budgeted salary costs according to his specifications
and build a summary table using database functions;
(2) Undertake a Solver analysis on the business franchise areas to determine a reallocation
of franchise areas by distance from the store;
(3) Undertake a scenario analysis on saving monthly/fortnightly for building a new store
that will cost a ‘What If’ certain amount of money in a few years;
(4) Provide some business-focused comments to Henry relating to this MS Excel solution.
List of Sheets in Excel Workbook
When submitted, your final solution will have the following sheets:
• Document Control
• Constant
• Employees
• Payroll Summary
• Pivot Table
• Pivot Chart
• Current Franchise Distribution
• Franchise Redistribution
• Solver Analysis Answer Report
• Pivot Table
• Pivot Chart
• New Store Investment
• Scenario Summary
• Comments to Henry
Sheets in italics need to be created by following instructions in this document, as they are not
in the template file.
Document Control Sheet
Hint: Throughout the spreadsheet, cells with a light shaded blue background require you
to enter a value or a formula in them or take some actions with them.
Cells with a light-orange background are to be populated by either the Solver or Scenario
Manager tools, not you.
Cells with no colour background should not be edited or changed by you unless explicitly
directed to do so in this specification document.
First enter your details: Student name and student number.
In addition, you should list any assumptions that you have made when you developed your
assignment on this sheet. The assumptions allow examiners to understand your work in context.
You should use these assumptions to resolve any ambiguities you might identify in this Case
Specification.
The assumptions you make must be logical and consistent with the scenario provided in this
Case Specification.
If you do not make any assumptions, please leave this section empty.
Constant Sheet
This sheet contains all the lookup tables that you will need to use in the assignment. When
using lookup tables in your formulas from the Constant sheet, make sure they are
accessed using appropriate Named Ranges.
You should also edit some tables which are empty and coloured in blue and then format this
sheet professionally.
Note: Throughout this assignment, you must use a Named Range whenever referring
to a range or a cell in writing formulas/functions to ensure that whoever reviews your
spreadsheet can understand it, especially when the range or cell is from another
worksheet. You can also use Named Ranges to refer to a range or a cell in the same
worksheet, but this is optional.
MARKS ARE ALLOCATED IN THE MARKING RUBRIC FOR USING NAMED
RANGES TO REFER TO RANGES OR CELLS IN A DIFFERENT WORKSHEET
There are 8 lookup tables or values contained in this Constant Sheet. You are to complete these
as directed below.
Employee Salary Table
Employees are paid at different rates based on their job title. Each job comes with a different
employer superannuation percentage rate and different email domain. The details of the
different job descriptions are presented below.
You are required to complete the data entry of the table in the workbook.
Table 1: Employee Salary Table for 2022-23
Job Title Annual Salary Employer Super Department
Accountant $63,743 10.5% Accounting
Operation Manager $65,824 12% Operation
Owner $130,765 17% Executives
Delivery Service Manager $69,684 11% Operation
IT Manager $75,356 13.5% IT
Franchisee Manager $98,889 17% Operation
Senior Delivery Service
Manager $78,345 13%
Operation
Annual Tax Table
Tax is withheld using the following tax rates for 2022-23. This information has been entered
for you in the Constants Sheet.
Table 2: Australian Taxable Income Table for 2022-23
Taxable Income Tax on this Income
$0 - $10,275 Nil
$10,276 - $41,775 12c for each $1 over $10,275
$41,776 - $89,075 5,092 plus 22c for each $1 over $41,775
$89,076 - $170,050 29,467 plus 24c for each $1 over $89,075
Employee Superannuation Contribution Table
Employees at HFM have collectively agreed to contribute a percentage of their annual salary
to their superannuation fund based on their age at the beginning of the financial year as a
post-tax contribution (‘non-concessional contributions’). In Australia, a superannuation fund
receives a percentage of every salary to employees to invest on their behalf so that they can
draw on it when they retire.
You are required to complete the data entry of the table in the workbook.
o Employees aged 30 and over have elected to contribute 3.5%.
o Employees aged 40 and over have elected to contribute 4.0%.
o Employees aged 50 and over have elected to contribute 4.5%.
o Employees aged 60 and over have elected to contribute 5.0%.
Note: In Australia, the financial year is for the period 1 July to 30 June, which is different to
the calendar year, which is for the period 1 January to 31 December.
Christmas Bonus Rates Table
Employees at HFM who have had extended service with the company are paid an annual
Christmas bonus at the end of each calendar year. You are required to complete the data
entry of the table in the workbook.
o Employees who have been employed for at least 3 years at the beginning of the calendar
year receive a 3% bonus on their annual salary.
o Employees who have been employed for at least 5 years at the beginning of the calendar
year receive a 4% bonus on their annual salary.
o Employees who have been employed for at least 7 years at the beginning of the calendar
year receive a 5% bonus on their annual salary.
o Employees who have been employed for at least 9 years at the beginning of the calendar
year receive a 6% bonus on their annual salary.
o Employees who have been employed for at least 11 years at the beginning of the
calendar year receive a 7% bonus on their annual salary.
Beginning of Calendar Year
Use function to enter the first day of the 2022 calendar year (i.e., 01/01/2022).
You are required to complete the data entry of the table in the workbook.
Beginning of Financial Year
Use function to enter the first day of the 2022/2023 financial year (i.e., 01/07/2022).
You are required to complete the data entry of the table in the workbook.
HFM Subscriptions Table
The subscription fee paid by customers varies according to the number of weeks they subscribe.
Customers pay $75 per week for a 7-week subscription, $60 per week for a 21-week
subscription, and $50 per week for a 42-week subscription. This information has been entered
for you in the Constants Sheet.
Distance Survey and Suburb Profile Table
Previously, franchise areas were allocated according to a rule of thumb (‘whatever worked’) at
the time the franchise was allocated. As HFM matures, Henry now wants to consider allocating
franchise areas based on the average actual travel distance from the shop to the suburbs that
they service.
This table is central to those calculations.
Each row in this table is an inner-city suburb in Brisbane that is within 6kms or so of the
Brisbane CBD. The latitude and longitude of an ‘average’ (centroid) point for each suburb is
provided. You are to use this information to determine distance for franchise areas.
Each row also indicates the prospective subscribers to the HFM service in these Brisbane
suburbs to each subscription type (7, 21, or 42 weeks). This information is derived from
extensive and, according to Henry, infallible, market research 1 . Prospective subscribers
contain the number of potential subscribers in each suburb, as discovered through market
research. The role of prospective subscribers versus actual subscribers is discussed below in
the Current Franchise Distribution section.
Each column in this table represents the four (4) current franchisee stores in Brisbane (Brisbane
City, South Brisbane, Milton, and Fortitude Valley).
In this table, you are to calculate the distance from each franchisee store to each suburb using
the latitude and longitude. To do this, use the latitude and longitude of each location according
to the following formula:
= ACOS(COS( RADIANS(90 − 1) ) × COS(RADIANS(90 − 2))
+ SIN (RADIANS(90 − 1))
× SIN(RADIANS(90 − 2)) × COS(RADIANS(1 − 2)))
× 6371
This Haversine formula uses a widely-accepted method of calculating distances across the
surface of the earth (which looks quite complicated because the earth is not flat). Do not worry
about the calculation method. Just insert the latitude and longitude of each suburb and each
franchisee store respectively into your formula as follows.
Note: The Shop Code at the top of this table relates each Shop Code to the suburb in
which it is located.
Hint: You may wish to check your calculations of distance between suburbs using Google
Earth to measure point-to-point distance. Google Maps would provide travel distance
using roads, which would be longer than the Haversine formula (great circle method)
above.
1 You protest to Henry that no market research is ever infallible. Henry tells you ‘Don’t you worry about that!’
Note: the distance between a franchisee store in a suburb and the suburb in which it is
located will be 0. For example, the distance between the suburb of Milton in the row of
this matrix and the suburb of Milton in the column of this matrix should be 0. If it is not
zero – then your formula is definitely wrong!
Note: Format this table appropriately.
Employees Sheet
Mainly refer to tutorial 2/7/8/9/11
Note: Please note that your formulas should be efficient and not hardcoded. You can use
a Lookup and Reference function to achieve this.
When using lookup tables in your formulas for values in the Employees Sheet, make sure
they are accessed using appropriate named ranges.
You should also format this sheet professionally and meaningfully.
The employee sheet keeps track of HFM’s employees. Your tasks are to:
Suggested order to complete tasks: Employee Budgeted Salary Costs -> Employee
Personal Details -> Database Functions
Employee Budgeted Salary Costs
(1) Column M: Insert a formula to retrieve the annual salary of the employee from the
Constant Sheet based on employee’s Job Title.
(2) Column N & O: Following this, insert formulas to calculate the employer and
employee superannuation contributions.
Note: Please note that employer super contribution rates are in the salary table. The
employer superannuation contribution rates must be multiplied by annual salary.
Hint: You will need to use the employee’s birthday in relation to the first day of the
financial year to calculate their employee superannuation contributions. The employee
superannuation contribution is post-tax contribution. Employees younger than 30 would
not pay any extra contribution.
Take a look at the date and time functions to best understand how to calculate accurate
durations between dates, dividing number of days by 365 is not accurate enough.
(3) Column P: Insert a formula to determine the Christmas bonus employees receive in
addition to their annual salary.
Hint: You will need to use the employee’s first working day in this formula in relation to
the beginning of Calendar Year. Take a look at the date and time functions.
(4) Column Q: Using a formula calculate the annual income tax & Medicare levy withheld
from employees based on their salary.
Note: In your solution, assume that all employees pay the Medicare levy of 3% (that is,
assume all employees earn more than the threshold for low-income earners, and no
Medicare Levy Surcharge applies). The taxable income should include Christmas bonus.
Hint: Use the Annual Tax Table to calculate Income Tax from all income figures. For
example, an employee whose salary is $68,720 who has been at the store for three years
would receive ($68,720+ (0.03 x $68,720)) = $70,781.6 in taxable income.
On this taxable income, the accountant would pay income tax of ($5,092 + ($70,781.6 –
41,775) x 0.22) = $11,473.452. The Medicare Levy of 3% also applies and so Income Tax
& Medicare Levy would be $11,473.452 + ($70,781.6 x 0.03) = $13,596.9.
(5) Column R: Finally, insert a formula to determine the annual take home balance for
each employee – this is each employee’s total income less tax paid less any employee
contributions to superannuation.
Employee Personal Details
(1) Email Address (Column K): Insert a formula to form out email address for each
employee. All employees share the same company domain, which is “hfm.com.au”, but
different subdomains for each department.
Hint: Derive this column from ‘Given Name’ (Column C), ‘Surname’ (Column E), and
‘Employee Salary Table’ on the Constant sheet. You may need to look up the department
name and then connect with the company domain. For example, the email address for
Anna B Bishop who is delivery service manager should be
[email protected]
(2) Residential Suburb (Column L): Insert a formula to determine the employee’s
residential suburb (i.e. Milton).
Hint: You will need to search for the suburb name in the employee’s address to determine
the suburb of their home address. Here you are allowed to explore some Text Functions
which are not covered in tutorial.
Database Functions
You then need to complete the HFM Summary Table at the top of the sheet using Database
Functions. Please use the criteria range (A2:R3) and database range (A16:R94) by assigning
the named ranges “Criteria” and “Database” respectively, apply database functions to
calculate the minimum, maximum, average, and total values for the listed headings (M-
R), so that changing the criteria in row 3 results in changes to the summary table values in rows
9-12. You should also use a database function to count the number of records in the
schedule that meet the criteria for cell M7.
Hint: Database functions begin with a “D” and rely on criteria set out in a range that you
identify and populate with your criteria.
The formulas should be robust to control error with IFERROR.
When submitting the spreadsheet, set the criteria for summary table database functions so
that the Summary Table displays data relating to only the Job Title of “Delivery Service
Manager”.
Payroll Summary Sheet
Mainly refer to tutorial 11
Payroll Summary sheet records the actual payment across 12 months for each employee in
2022 (Please note that the calculations in Employees Sheet are budgeted take-home payment
not the actual payment employees received)
Using the information from the Employees Sheet and the Payroll Summary Sheet, create
Relationship through Power Pivot and generate a PivotTable to compare the budgeted take-
home payment versus actual payment based on the Job Title. Calculated fields in PivotTable
are not required but please make sure the PivotTable has meaningful label headers. To make
it easier for other users to view the movement of the salaries, visualise the PivotTable by
inserting a Clustered Column PivotChart. The PivotChart must have a meaningful chart title.
You should put the Pivot Table and the Pivot Chart in two separate worksheets and give each
of the two worksheets a meaningful sheet name.
Hint: You need to import the data from Payroll Summary 2022 Sheet. Please clean the
dataset before loading into the sheet. PivotTable and Pivot Chart worksheets do not exist
in the template. ‘Job Title’ should be displayed in Rows.