Excel Assignment

Overview

This assignment requires you to create a professional business application using Microsoft Excel 2019 / Microsoft Excel 365. 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 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 2019 / Microsoft Excel 365 based on the specification in this document. The Excel workbook contains several sheets you should develop.

 

 

Background

Yield Yards Ltd is a real estate agency specialising in property rentals in the western suburbs of Brisbane. Yield 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 Jing Wu, one of the owners of Yield 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 2023-24 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

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.

 

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 title. 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 2023-24

Job Title	Annual Salary	Employer Super
Accountant	$100,450	12.50%
Office Manager	$110,800	12.50%
Owner	$139,340	18%
Property Manager	$85,290	7.5%
Receptionist	$48,130	7.5%
Sales Officer	$89,690	11%
Marketing Manager	$86,650	7.5%
Senior Property Manager	$101,600	12.50%

Annual Tax Table

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

 

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

Taxable Income	Tax on this Income
0 – $18,200	Nil
$18,201 – $45,000	19 cents for each $1 over $18,200
$45,001 – $120,000	$5,092 plus 32.5 cents for each $1 over $45,000
$120,001 – $180,000	$29,467 plus 37 cents for each $1 over $120,000
$180,001 and over	$51,667 plus 45 cents for each $1 over $180,000

Employee Superannuation Table

Employees at Yield 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.

• Employees aged under 40 have elected to sacrifice 5%.
• Employees aged 40 and over (but younger than 50) have elected to sacrifice 4%.
• Employees aged 50 and over (but younger than 65) have elected to sacrifice 5%.
• Employees aged 65 and over have elected to sacrifice 5%.

 

Service Bonus Table

Employees at Yield 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.

• Employees who have been employed for at least 1 years at the beginning of the calendar year receive a 5% bonus on their annual salary.
• Employees who have been employed for at least 3 years at the beginning of the calendar year receive a 5% bonus on their annual salary.
• 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.
• 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.
• Employees who have been employed for at least 9 years at the beginning of the calendar year receive a 5% bonus on their annual

 

Beginning of the Financial Year

Please enter the first day of the financial year of 2023-24.

 

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 Yield 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 ID, total annual rent income and its percentage.

 

Properties Sheet

This sheet contains a basic setup to apply advanced filtering to a database.

 

You have been provided with a data file consisting of rental property information. You are required to import the details from the file into this sheet starting from row 12 of the spreadsheet. This will become the database for filtering. Make sure there’re no duplicated headings.

 

Using two named ranges (Database and Criteria), set up an AND-based filter for the criteria entered in the Criteria range and show all properties in St Lucia. Using the same named ranges, apply database functions to calculate the total, minimum, average and maximum values for the listed headings. The formulas should be robust and not display errors. When you submit your assignment, make sure the advanced filtering with the criteria “St Lucia” is active.

 

ABS Data – Median Weekly Rent

This sheet includes data from Australian Bureau of Statistics (ABS) Census data. The sheet shows median rent by Statistical Area 2 (SA2).

In major urban areas, SA2s often reflect one or more related suburbs. The SA2 is the smallest area for the release of many ABS statistics.

 

Suburb to SA2

This sheet includes a mapping of Suburbs (Localities) to Statistical Area 2 (SA2) codes. It also includes approximate geographical coordinates (longitude and latitude) for each suburb.

 

Benchmarking

Complete the Benchmarking sheet by including median weekly rent of properties managed by Yield Yards in each suburb vs median weekly rent of all of the properties in the same SA2 (as reflected in ABS census data).

Use conditional formatting (red fill colour) to highlight suburbs in which Yield Yards properties median weekly rent is smaller than the suburb median rent.

Based on this data, create a 3D Map with a Clustered Column Chart that visualises median weekly rent of properties managed by Yield Yards in each suburb vs median weekly rent of all of the properties in the same SA2. Include the map in the excel file. ALSO, add a new worksheet with the name “Benchmark Map Screenshot” and include a screenshot from your map.

Hint: you need to use the data from Suburb to SA2 Sheet. Note that geographical coordinates (longitude and latitude) are approximate. Often, different suburbs are given the same longitude and latitude values. Given the geographical proximity of these suburbs, it is okay to display the merged values on the same point. However, make sure the median weekly rent values that are visualised on the map are valid and meaningful (even if they represent merged values from multiple suburbs).

 

Property Analysis

Using the information on the Properties Sheet, create a PivotTable on a new sheet to analyse the locations and properties. On the PivotTable, show the label headings as dwelling type, broken down by the property suburb and grouped by the office which the property is managed by. Make sure the table displays the average weekly rent for the property suburb and dwelling type.

 

Using the created PivotTable, create a PivotChart and move it to a new sheet with a meaningful sheet name. Format the chart as a clustered column PivotChart with a meaningful chart title.

 

 

Office Redistribution Sheet

This sheet shows the current suburb allocation and number of properties in each suburb. Your task is to run a Solver to reallocate property suburbs to various offices to ensure the property managers do not have to travel too far from their offices to the properties. Using a mix of text and lookup formulas, retrieve the distances from the Distance Survey Tableon the Constant Sheet.

 

The redistribution has been discussed previously and the following requirements have been identified:

• Each suburb should be managed by only one single
• Each office should be responsible for a maximum of ten suburbs and a maximum of three hundred
• Properties should be managed by the closest office, where possible, to ensure the property managers and tenants do not need to travel too
• If a suburb has an office, it must be serviced by that

 

To easily identify the suburbs and offices which require changes, use conditional formatting to highlight (background) the suburb names in green if they stay the same and yellow if they change. Where changes exist, highlight (background) the old allocation in red and the new allocation in blue (both with white text). Save the results of Solver to an answer sheet and restore the original values before submitting. Make sure you save your work before running Solver. Also, be mindful when choosing solving method.

 

Short-Stay Holiday Rentals Sheet

Yield Yards Pty Ltd has recently expanded into providing short-stay holiday rentals in Brisbane in addition to long term rentals. You are required to perform a What-If Analysis using a number of scenarios to see what the possible revenue will be for the venture. You can leave Midweek information blank. But, insert proper formulas in the file (if the information is provided later on, the spreadsheet should still be functional). A basic structure has been provided for you to complete. perform a What-If Analysis using the following information:

 

Current Scenario

Yield Yards Pty Ltd has 10 properties which are rented out for three-night weekend getaways for $700 each. The properties have in the past year each been rented for 40 weekends. 100 of these bookings ordered the premium service for an additional $100. A single employee is responsible for managing the short stay rentals and is paid an annual salary of $44,007. Only 25% of the employee’s work is attributed to managing the holiday rentals. The business earns 40% of the rent on each property; the remaining 60% is the owners’ in its entirety. Cleaning costs of $120 need to be paid after each booking by the business. Sundry expenses of the business for the holiday rentals is a flat $5000 a year. Premium bookings include a bottle of wine for each night and chocolates on arrival. The wine costs $14.98 and chocolates cost $5.45.

 

Calculate the total income, total expenses and total profit values.

 

Scenarios to Consider (12 scenarios)

Bookings Variations – 4 Input Varieties

• Current bookings – As per current
• Lower bookings – Bookings decline to only 32 bookings for each property and premium bookings decline to 80
• Higher bookings – Bookings increase to 45 bookings for each property and premium bookings increase to 150
• Maximum bookings – Bookings increase to 52 bookings for each property and premium bookings to 200

 

Additional Costs – 3 Input Varieties

• No change to employee workload or cleaning costs – As per current
• Employee workload increase – Employee spends 50% of their time working on the weekend
• Cleaning costs increase – Cleaning costs increase to $140.

Save the results of the scenarios to a new sheet, which contains meaningful labels for each of the values.

 

 

Market Analysis

Each quarter, the Queensland RTA (Residential Tenancies Authority) analyses new rental bond lodgements to determine the median weekly rental prices for houses, townhouses and flats/units across Queensland. Download this data from: https://www.rta.qld.gov.au/forms-resources/median-rents- quick-finder/median-rents-quarterly-data

 

The “total bonds lodged with the RTA” represents the number of new rental bonds received by the RTA. The data is a general indicator of rental activity within the region for a given period.

 

• Use this data to develop a chart that represents two trends from March 2012 to December 2023:
  1. The median value of weekly rent paid for new tenancies commencing in each quarter (for all dwellings)
  2. Number of new bonds lodged with the RTA during each quarter (for all dwellings) (hint: use a “combo” chart that combine two line charts using a secondary axis)

 

• Calculate the AVERAGE value of weekly rent paid for new tenancies commencing in the fourth quarter of 2023 (Dec 2023) by postcode for all available postcodes (hint: You can use a PivotTable and copy & paste the results to the template).

 

• Use Excel to develop a map that highlights the MEDIAN value of weekly rent paid for new tenancies commencing in the fourth quarter of 2023 (Dec 2023) by postcode (hint: use a “region” visualisation). Include the map in the excel ALSO, add a new worksheet with the name “Postcode Map Screenshot” and include a screenshot from your map. In your “Insights to Jing” specify a few postcodes outside the Brisbane Metropolitan Area that have high rent prices.

 

Note: For this section you can add up to four new worksheets to the assignment template. Add them at the end of the existing worksheets. Do not copy whole worksheets from the external data – only include the necessary elements.

 

Insights to Jing

After completing the excel workbook, provide insightful discussion in regard to the weakness and the strength of the business analysis model (i.e., this workbook). Provide suggestions to Jing if there is room for improvement. Length requirement is a 5 -6 sentences’ paragraph.

 

 

Implementation Guidance, Formatting and Professionalism

You must use Microsoft Excel 2019 (or Microsoft Excel 365) for this assignment. Any of the previous Microsoft Excel versions (e.g. Microsoft Excel 2007) might cause some unnecessary problems. It is highly recommended that, prior to assignment submission, you check that your solution works on the university computers if you have developed it on your own device.

 

 

Please develop your solution based on the provided files. In general, you are not allowed to insert anyother columns or tables. If you modify any existing features (except where explicitly instructed), please specify and explain them in the assumption section on the student details sheet. 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. Aspects of the assignment have purposefully been designed to train and test a student’s self-learning ability with a software application, and thus, has not been included directly in a tutorial exercise.

 

Yield Yards Pty Ltd is operated in a professional manner and it is expected that your Excel workbook will be used by other staff, and potentially updated in the future by others. Therefore, you would be well advised to make your work of the highest quality (e.g. apply screen freezing to long pages, use name references where appropriate, use lookup functions instead of nested If()swhere a data table exists, do not hardcode changeable data, use appropriate fonts and colours, graph axes and titles, etc). Keep in mind, however, that your work will be judged primarily on the quality of your solution, less on their appearance.

 

Marking Rubric

 

Criteria	Marks (100)	Poor		Satisfactory		Good
Formulas & Functions	35	0 – 17	Demonstrates a poor understanding of functionality relating to criteria in Excel. Many errors exist in solution.	17 – 28	Demonstrates a satisfactory understanding of functionality relating to criteria in Excel in line with tutorial knowledge only. Some errors exist in solution.	28 – 35	Demonstrates a good understanding of functionality relating to criteria in line with tutorial knowledge/self- learning. Few or no errors exist in solution.
PivotTables, PivotCharts, Charts, and Maps	15	0 – 7		7 – 12		12 – 15
Database & Advanced Functions	10	0 – 5		5 – 8		8 – 10
What-If Analysis	10	0 – 5		5 – 8		8 – 10
Solver	10	0 – 5		5 – 8		8 – 10
General	10	0 – 5	Many errors exist in solution. Follows unprofessional process in developing solution.	5 – 8	Some errors exist in solution. Follows a	8 – 10	Few or no errors exist in solution. Follows

 

 

Criteria	Marks (100)	Poor		Satisfactory		Good
Reflections and recommendations	10	0 – 5	Limited guidance to decision makers. Little understanding of weaknesses of developed model shown. .	5 – 8	Some useful guidance to decision makers. Some understanding of weaknesses of developed model shown.	8 – 10	Very useful guidance to decision makers. Excellent understanding of weaknesses of developed model shown.

BISM7202-Template-Sem1-2024-updated-18-March-2024 Excel-Assignment-Semester-1-2024-with-minor-update-on-18-March-2024-2