BISM7202 Information Systems for Management

Case Specification: MS Office Assignment – Office 365 Excel 2019 SEMESTER 2, 2023

Specification

Workbook

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).

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.

Document Control Sheet

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.

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. All data is fictious in the tables below.

You are required to complete the data entry of the table in the workbook.

Table 1: Employee Salary Table for 2022-23

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

Employee Superannuation Contribution Table

Employees at FHG 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 4.0%.

o Employees aged 40 and over have elected to contribute 4.5%.

o Employees aged 50 and over have elected to contribute 5.0%.

o Employees aged 60 and over have elected to contribute 5.5%.

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 FHG 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 4% 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 5% 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 6% 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 7% 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 8% bonus on their annual salary.

Beginning of Calendar Year

Use function to enter the first day of the 2023 calendar year (i.e., 01/01/2023).

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 2023/2024 financial year (i.e., 01/07/2023).

You are required to complete the data entry of the table in the workbook.

FHG Subscriptions Table

The subscription fee paid by customers varies according to the number of weeks they subscribe. Customers  pay  $90  per  week  for  a   14-week  subscription,  $75  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 FHG matures, Harvey 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 FHG  service in these Brisbane suburbs to each subscription type (14, 21, or 42 weeks).   This information is derived from extensive  and,  according  to  Harvey,  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:

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.