BISM7202 Excel Assignment

Semester 1, 2025

Overview

This assignment requires you to create a professional business application using Microsoft Excel365 (Excel 2019 or newer versions). The purpose of this assignment is to test the student’s ability to operate and manage business data in spreadsheets. The assignment requires no prior technical background. Moreover, it is designed for business students in general to appreciate basic IS applications. Prior familiarity with the software tool could be beneficial but will not guarantee a significant advantage or higher marks. Through tutorials, students are exposed to practical exercises like those in the assignment and develop the skills to manage business data in Excel to complete this assignment. It is essential that students carry out the required readings and preparation for each tutorial before attending/attempting each tutorial and this assignment.

The assignment is worth 45% of your grade in this course. This is an individual assignment — group work or any collaboration on the assignment is not permitted. This assignment consists of several tasks to be completed in Excel.

The Excel template of the expected worksheets is 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 (colour, fonts, data format presentation, etc) to provide a professional finished product, but nothing else, e.g. template structure, except when you are asked to do so.

Your Task

This assignment requires you to complete an Excel workbook file using Microsoft Excel 365 (Excel2019 or newer versions) based on the specifications in this document. The Excel workbook contains several sheets you should develop.

Background

Yuan Yards Ltd is a real estate agency specialising in property rentals in the western suburbs of Brisbane. Yuan Yards operates from five different office locations around the western suburbs to provide owners and tenants with the best possible service. A large number of property owners entrust their properties to the property managers. Tenants are able to lease properties for a period of 13, 26, or 52 weeks.

Ms Lina Zhang, one of the owners of Yuan Yards, has asked you to improve their information system for managing their rental properties. She has provided you with a sample of properties the business managed during the 2024-25 financial year. She would like you to complete the workbook containing multiple spreadsheets to analyse the business. Also, she asks if you spot anything that might put her in trouble with the law.

Documentation Sheet

First, enter your details: Student name and student number (8 digits without an ‘s’).

In addition, list any assumptions that you have made when you developed your assignment. The assumptions allow examiners to understand your work in context. If you do not make any assumptions, please leave the section empty. Assumptions to be considered when marking must be logical.

There is also a separate section for Reflections on Generative AI Use, which will be explained later in the assignment.

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, please make sure they are accessed using appropriate named ranges. In fact, appropriate named ranges should be used as much as possible throughout the whole workbook.

Employee Salary Table

Employees are paid at different rates based on their job titles. Each job comes with a different employer superannuation percentage rate. 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 2024-25

Annual Tax Table

Income Tax is withheld using the following tax rates for 2024-25. This information has been entered for you in the Constants Sheet.

Table 2: Australian Taxable Income Table for 2024-25 (available at https://www.ato.gov.au/tax-rates-and-codes/tax-rates-australian-residents)

Employee Superannuation Table

Employees at Yuan Yards have collectively agreed to contribute a percentage of their post-tax annual salary to their superannuation fund based on their age at the end of the financial year. You are required to complete the data entry of the table in the workbook.

o Employees aged under 40 have elected to sacrifice 4.5%.

o Employees aged 40 and over (but younger than 50) have elected to sacrifice 5.5%.

o Employees aged 50 and over (but younger than 65) have elected to sacrifice 6%.

o Employees aged 65 and over have elected to sacrifice 6.5%.

Service Bonus Table

Employees at Yuan Yards who have had extended service with the company are paid an annual bonus. You are required to complete the data entry of the table in the workbook.

o Employees who have been employed for at least 2 years at the beginning of the calendar year receive a 1.5% 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 2.5% bonus on their annual salary.

o Employees who have been employed for at least 8 years at the beginning of the calendar year receive a 3.5% 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 4.5% bonus on their annual salary.

o Employees who have been employed for at least 14 years at the beginning of the calendar year receive a 5% bonus on their annual salary.

Beginning of the Financial Year

Please enter the first day of the financial year of 2024-25.

Distance Survey Table

Each office manages different properties in various suburbs. Recently, a survey of distances between offices and suburbs was completed based on actual travel distance rather than physical distance. This table shows the distances from the office suburbs to the property suburbs. In each cell, the first suburb indicates the office location, and the second suburb indicates the suburb where the property is located.  This information has been entered for you.

Employees Sheet

The employee sheet keeps track of the employees currently employed at Yuan Yards. You’re required to complete this sheet using the information provided.

Your first task is to insert a lookup-based formula to retrieve the annual salary of the employee based on their jobs. Following this, insert formulas to calculate the employer and employee superannuation contributions. Please note that employer superannuation is not included in the employee’s annual salary. Next, insert a lookup-based formula to calculate the commission that a property manager earns based on the rent paid for the property he manages. The commission rate is 2% as per the Business Income Sheet. Also, insert a lookup-based formula to determine the service bonus. Then use a lookup-based formula to calculate the annual income tax based on their taxable income. Finally, insert a formula to determine the annual take-home balance, for each employee — this is how much is deposited into their bank accounts during the year.

Business Income Sheet

This sheet lists the property managers of the business. It shows the number of properties that an employee manages, along with the amount of rent the business received from those properties. From the rent to be received, the owner receives 90%, the business receives 8%, and the employee receives 2% as a commission. The commission should be calculated in the Employees Sheet.

Office Location Analysis

Using the information on the Employees Sheet and Business Income Sheet, create a new Data Model and generate a PivotTable, which shows the total annual rent paid to different offices. Make sure the PivotTable has meaningful label headers.

Using the created PivotTable, create a PivotChart and move it to a new sheet with a meaningful sheet name. Format the chart as a Pie Chart with a meaningful chart title to show each office’s location name, total annual rent income and its percentage.