BISM7202 Excel Assignment Specification Semester 1, 2020 BISM7202 Excel Assignment
WEIGHT DUE DATE DELIVERABLES
Overview
40%
29 May 2020 at 14:00
One electronic file must be submitted via the Blackboard Assignment Submission Tool consisting of a single Excel file.
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 assignment is worth 40% 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 2019 / Microsoft Excel 365 based on the specification in this document. The Excel workbook contains several sheets you should develop.
Background
57 Promenade Property Pty Ltd is a real estate agency specialising in property rentals in the western suburbs of Brisbane. 57 Promenade 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.
Isabelle van Hooijdonk, one of the owners of 57 Promenade, 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 2019-2020 financial year. She would like you to complete the workbook containing multiple spreadsheets to analyse the business.
Page | 1
BISM7202 Excel Assignment Specification Semester 1, 2020
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.
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 of annual salary (excluding employer superannuation) based on their job title. The information has been entered for you. Your task is to enter the base rate of employer superannuation – 9.5%. Moreover, based on the Company’s Enterprise Bargaining Awards, 57 Promenade Pty Ltd agrees to match the salary sacrifice of Employee Superannuation as additional employer superannuation contribution. This will need to be taken into consideration when calculating employer superannuation in the Salary Budget Sheet.
Annual Tax Table
Income Tax is withheld using the following tax rates for 2019-20. Please enter the information according to Table 1 below. Taxable Income is the base to calculate income tax. Note that taxable income includes all income prior to paying employee superannuation.
Table 1: Australian Taxable Income Table for 2019-20
Employee Superannuation Table
Employees at 57 Promenade 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 30 and over (but younger than 40) have elected to sacrifice 2%. o Employees aged 40 and over (but younger than 50) have elected to sacrifice 3%. o Employees aged 50 and over (but younger than 60) have elected to sacrifice 3.5%. o Employees aged 60 and over have elected to sacrifice 4%.
Service Bonus Table
Employees at 57 Promenade 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 3% bonus on their annual salary.
o Employees who have been employed for at least 3 years at the beginning of the calendar year receive a 3.5% bonus on their annual salary.
Taxable Income
Tax on this Income
$0 – $18,200
Nil
$18,201 – $37,000
19c for each $1 over $18,200
$37,001 – $87,000
$3,572 plus 32.5c for each $1 over $37,000
$87,001 – $180,000
$19,822 plus 37c for each $1 over $87,000
$180,001 and over
$54,232 plus 45c for each $1 over $180,000
Page | 2
BISM7202 Excel Assignment Specification Semester 1, 2020
o Employees who have been employed for at least 5 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 7 years at the beginning of the calendar year receive a 6.25% bonus on their annual salary.
o Employees who have been employed for at least 10 years at the beginning of the calendar year receive an 8% bonus on their annual salary.
Beginning of the Calendar Year
Please enter the first day of the calendar year of 2020.
Beginning of the Financial Year
Please enter the first day of the financial year of 2020.
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.
Salary Budget Sheet
The employee sheet keeps track of the employees currently employed at 57 Promenade. 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.
Actual Salary 2019 Sheet
This sheet is to be populated by importing the information from the Payroll Summary file. This file contains the actual salary paid during the 2019 financial year.
Salary Budget Analysis
Using the information from the Salary Budget Sheet and the Actual Salary Sheet, create a Data Model and generate a PivotTable to compare the budgeted take-home payment versus actual payment based on the job titles. Calculated fields 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 and must be moved to a separate sheet with a meaningful sheet name.
Page | 3
BISM7202 Excel Assignment Specification Semester 1, 2020
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 89%; the business receives 9%; and the employee receives 2% as a commission. The commission should be calculated in the Salary Budget Sheet.
Office Location Analysis
Using the information on the Salary Budget 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 and contains offices’ suburbs instead of location IDs.
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 suburb, 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.
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 Table on the Constant Sheet.
The redistribution has been discussed previously and the following requirements have been identified: o Each suburb should be managed by only one single office.
o Each office should be responsible for a maximum of ten suburbs and a maximum of three
hundred properties.
Page | 4
BISM7202 Excel Assignment Specification Semester 1, 2020
o Properties should be managed by the closest office, where possible, to ensure the property managers and tenants do not need to travel too far.
o If a suburb has an office, it must be serviced by that office.
Using Solver, work out a possible redistribution plan to fulfil all the requirements and generate a professionally formatted report on a new sheet. Make sure you save your work before running Solver. Also, be mindful when choosing solving method.
Short-Stay Holiday Rentals Sheet
57 Promenade Pty Ltd has recently expanded into providing short-stay holiday rentals in Brisbane in addition to long term rentals. You are required to populate the Input area on the template according to the information provided in Current Scenario, and calculate the total income, total expenses and total profit values in the Output area. Using the 12 scenarios described in Scenarios to Consider, perform a What-If Analysis to generate a Scenario Summary with the total income, total expenses and total profit values as outputs. Make sure the summary report is formatted professionally.
Current Scenario
57 Promenade Pty Ltd has 10 properties which are rented out for three-night weekend getaways for $1,000 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 $500. A single employee is responsible for managing the short stay rentals and is paid an annual salary of $49,000. However, only 30% of the employee’s work is attributed to managing the holiday rentals. The business earns 47.5% of the rent on each property; the remaining 52.5% is the owners’ in its entirety. Cleaning costs of $300 need to be paid after each booking. Sundry expenses of the business for the holiday rentals is a flat $7,500 a year. Premium bookings include a bottle of wine for each night and chocolates on arrival. The wine costs $19.98 and chocolates cost $14.45.
Scenarios to Consider (12 scenarios)
Bookings Variations – 4 Input Varieties
o Current bookings – As per current scenario used in Current Scenario.
o Lower bookings – Bookings decline to only 32 bookings for each property and premium
bookings decline to 80 bookings.
o Higher bookings – Bookings increase to 45 bookings for each property and premium bookings
increase to 150 bookings.
o Maximum bookings – Bookings increase to 52 bookings for each property and premium
bookings to 200 bookings.
Additional Costs – 3 Input Varieties
o No change to employee workload or cleaning costs – As per current scenario used in Current Scenario.
o Employee workload increase – Employee spends 50% of their time working on the weekend rentals.
o Cleaning costs increase – Cleaning costs increase to $400. Insight to Isabelle
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 Isabelle if there is room for improvement. Length requirement is a 5 -6 sentences’ paragraph.
Page | 5
BISM7202 Excel Assignment Specification Semester 1, 2020
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 any other 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.
57 Promenade 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()s where 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.
Plagiarism
It is understandable that students talk with each other regularly, and discuss problems and potential solutions. However, it is expected that the submitted assignment is a unique document – all parts of the assignment are to be completed solely by the individual student. The best practice to avoid misconduct is to not look at another student’s file and not show your solution to another student. In case where an assignment is perceived to not be a unique work, a loss of marks and other implications can result. For further information about academic integrity, plagiarism and consequences, please visit http://ppl.app.uq.edu.au/content/3.60.04-student-integrity-and-misconduct.
Submission
To be done through Blackboard Assignment Submission. Your Excel Workbook file MUST be named in the format of BISM7202_StudentLastName_StudentID.xlsx. If your ID is 41724943 (8 digits) and your last name is Smith, the name of your files would be BISM7202_Smith_41724943.xlsx.
Consultation Sessions
To ensure that an equal and sufficient amount of time is allocated for every student who attends consultation sessions regarding the practical aspects of BISM7202, the average consultation time (during busy consultation times) will be limited to 5 minutes per student. The main aim of this restriction during busy periods is to ensure equality to students and minimise waiting time. However, in circumstances where no other students are waiting, longer consultation times will be provided. Tutors have advised you of their consultation times during tutorials – these details are also available on the BISM7202 Blackboard site under “Course Staff”.
Page | 6
BISM7202 Excel Assignment Specification Semester 1, 2020
Please note that course staff are not allowed to look at your assignment files to provide feedback or answer questions. Questions regarding your assignment can be answered if they are related to the understanding of the concepts and/or techniques of Excel.
For convenience, you may email the tutors with questions. Tutor email address has been advised in tutorials and is available on the BISM7202 Blackboard site under “Course Staff”. Tutors will endeavour to respond to all questions within 2 business days.
Late Submissions
Students must fill in an Application for Extension of Assessment Due Date and provide supporting documentation to the Course Coordinator, Dr Saeed Akhlaghpour, at least 24 hours prior to the submission due date. Applications directly through emails will not be processed. The extension application instruction can be found on the website https://my.uq.edu.au/information-and- services/manage-my-program/exams-and-assessment/applying-extension.
o Supporting documentation such as a medical certificate, funeral notice etc. must be provided. Scanned or photographed copies should be attached to your email.
o For an application on medical grounds, the medical practitioner must not be a near relative or close associate. Examples of near relatives are partner, child, brother, sister, parent. Examples of close associates are close friends, neighbours and partners or children of colleagues.
o Extension criteria are applied consistently for equity reasons.
o You may discuss your situation with your course coordinator, but you still need to make a
formal extension request using this form.
o Applications on medical grounds will be approved for the number of calendar days the
medical certificate indicates you were unfit for study. You are expected to act in a timely manner and must make an appointment as soon as your condition impacts on your ability to study.
o If you have a continuing condition you should contact Student Services to arrange a Student Access Plan (Disability) [SAPD]. You must still submit the application form.
Non-permissible circumstances
Extensions will not be granted where the School is not satisfied you took reasonable measures to avoid the circumstances that contributed to you not submitting by the due date. The following are not grounds for an extension:
o holiday arrangements (including overseas travel); o misreading a due date;
o social and leisure events;
o movinghouse;
o pressure of work/competing deadlines; o computer issues.
You will incur penalties if your work is submitted late (i.e. after the due date and without an approved extension).
Assignments (for which no extension has been granted) submitted after the due date and time, incur a late submission penalty. The penalty is at the rate of 5% of the total available marks for that particular piece of assessment, for each calendar day or part thereof that the item is overdue. The penalty once calculated is deducted from the marks awarded for the assessment. Assessment submitted more than 10 days after the due date will receive zero marks.
Page | 7
BISM7202 Excel Assignment Specification Semester 1, 2020
Marking Rubric
The marking rubric below is designed to reflect a marking scheme of 100 marks that are at the end scaled back to 40 marks. If you received 75 out of 100 marks, your final mark out of 40 is calculated as follows: 75 / 100 x 40 = 30. Therefore, the final mark would be 30 out of 40.
Criteria
Marks (100)
Poor
Satisfactory
Good
Formulas & Functions
20
0–7
7 – 13
14 – 20
PivotTables & PivotCharts
20
0–7
7 – 13
14 – 20
Data Models
10
0–4
5–7
8 – 10
Database & Advanced Functions
10
0–4
5–7
8 – 10
Solver
20
0–7
7 – 13
14 – 20
What-If Analysis
10
0–4
5–7
8 – 10
Analytical Review
5
0–1
2–3
4–5
General
5
0–1
2–3
4–5
Page | 8
Many errors exist in solution. Follows unprofessional process in developing solution.
Provides poor insight to Isabelle as the user of the workbook.
Demonstrates a poor understanding of functionality relating to criteria in Microsoft Excel. Many errors exist in solution.
Some errors exist in solution. Follows a satisfactory process in developing solution.
Provides sufficient insight to Isabelle as the user of the workbook.
Demonstrates a satisfactory understanding of functionality relating to criteria in Microsoft Excel in line with tutorial knowledge only. Some errors exist in solution.
Few or no errors exist in solution. Follows an excellent process in developing solution.
Provides meaningful and insightful analytics to Isabelle.
Demonstrates a good understanding of functionality relating to criteria in Microsoft Excel in line with tutorial knowledge and self-learning. Few or no errors exist in solution.