BISM7202 Excel Assignment

Assignment Submission Tool consisting of a single Excel file.

1 Overview

This assignment required you to create a professional business application using Microsoft Excel 2016 / 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 student 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 and use these skills 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 30% 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.

2 Templates

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

3 Your Task

This assignment requires you to complete an Excel workbook file using Microsoft Excel 2016 / Microsoft Excel 365 based on the specification in this document. The Excel workbook contains several sheets you should develop.

4 Background and Scenario

Sunshine is a childcare centre located in Brisbane. It provides childcare for children aged between 0 and 6 years old. The Director, Claudia Philip, has asked you to improve their information system for managing their employees, families and children. She has provided you with a sample of the business files. She would like you to complete the workbook along with developing a future planning sheet for the following year and an investment portfolio.

5 Documentation Sheet

First enter your details: Student name and student number.

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.

Version 1 Feb 2018 Page | 1

6 Constants

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.

6.1 Annual Tax Table Tax is withheld using the following tax rates for 2017-18. This information has been entered for you in the Constants Sheet.

Table 1: Australian Taxable Income Table for 2017-18

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

6.2 HELP Repayment Table Employees with HELP debts have income withheld based on the following repayment rates for 2017-18. The income withheld is calculated based on their total taxable income. This information has been entered for you in the Constants Sheet.

Table 2: Help Repayment Rates for 2017-18

Taxable Income Repayment Rate Below $55,874 Nil $55,874 - $62,238 4.0% $62,239 - $68,602 4.5% $68,603 - $72,207 5.0% $72,208 - $77,618 5.5% $77,619 - $84,062 6.0% $84,063 - $88,486 6.5% $88,487 - $97,377 7.0% $97,378 - $103,765 7.5% $103,766 and above 8.0%

6.3 Employer Superannuation Table Employees of Sunshine are paid a different level of employer superannuation contribution depending on their position within the company. The superannuation is on top of their standard salary. The employer superannuation contribution is listed below. You are required to complete the table of information on the Constant Sheet.

Table 3: Employer Superannuation Contribution Table

Position Percentage Director 12.5% Deputy Director 12.0% Senior Accountant 11.5% Accountant 11.0% Chef 10.0% Assistant Chef 9.5% Child Care Leader 10.0% Child Care Worker 9.5% Junior Child Care Worker 9.5% Custodial Engineer 9.5%

Version 1 Feb 2018 Page | 2

6.4 Employee Superannuation Table Employees of Sunshine have collectively agreed to contribute a percentage of their post-tax annual salary to their superannuation fund based on their age at the beginning of the financial year. You are required to complete the data entry of the table in the workbook on the Constant Sheet.

o Employees aged 25 and over have elected to sacrifice 2.5%. o Employees aged 35 and over have elected to sacrifice 3%. o Employees aged 45 and over have elected to sacrifice 3.5%. o Employees aged 50 and over have elected to sacrifice 5%.

6.5 Child Care Rooms Table At Sunshine, there are several different rooms that are split up into different age groups. You are required to enter the details in the workbook on the Constant Sheet.

o Babies aged younger than 2 years are cared for in the Platypus Room. o Young children aged between 2 and 3 years are cared for in the Emu Room. o Children aged between 3 and 4 years are cared for in the Koala Room. o Older children aged between 4 and 5 years are cared for in the Kangaroo Room.

6.6 Family Child Care Info Families can receive several benefits and rebates from the government for having them in childcare while they work. The values for these have been entered for you in the Constant Sheet.

6.6.1 Childcare Benefit Maximum Hours Families can receive the childcare benefit for a max of 50 hours per child per week in childcare.

6.6.2 Childcare Benefit Rate Families can receive up to $0.719 per hour for each child. The rate is paid directly to Sunshine, it is not paid to the family nor does the family pay the value of the benefit. This rate can be reduced based on benefit threshold explained below.

6.6.3 Childcare Benefit Threshold Family income under $45,114 receives full Childcare Benefit. Otherwise, family income over $45,114 per year is subject to Childcare Benefit Threshold Modifier explained below. To sum up, families can earn up to $45,114 per year before deductions will be applied to the childcare benefit rate.

6.6.4 Childcare Benefit Threshold Modifier For every dollar over the threshold value that the family earns the childcare benefit rate is reduced by $0.0000065. To be more clear, Childcare Benefit Threshold Modifier is multiplied to the difference between family income and threshold value to reduce Childcare Benefit Rate.

Detailed explanation of childcare benefits: For example, if family income is $46,114 and they have two children and they spent 90 hrs in total in childcare centre then childcare benefit will be 90 hrs *52 weeks* (0.719-(46,114-45,114) *0.0000065). Keep in mind that childcare benefit cannot be a negative value. If it is negative, it is assumed that it is 0.

6.6.5 Childcare Rebate Rate Families can receive a childcare rebate of 50% per dollar spent on annual childcare cost after childcare benefit is deducted.

6.6.6 Childcare Rebate Maximum Families can receive a rebate up to $7613 per child per year. The rebate is paid directly to Sunshine, it is not paid to the family nor does the family pay the value of the rebate.

Version 1 Feb 2018 Page | 3

6.7 Holidays Table There are several holidays in which Sunshine is closed. During these days families are not required to pay for childcare. These days have been entered for you in the Constant Sheet.

Table 4: Public Holidays in Australia during 2018

Holiday Day Holiday Date New Year's Day 1 January 18 Australia Day 26 January 18 Good Friday 30 March 18 Easter Saturday 31 March 18 Easter Sunday 1 April 18 Easter Monday 2 April 18 Anzac Day 25 April 18 Labour Day 7 May 18 Ekka Wednesday 15 August 18 Queen's Birthday 1 October 18 Christmas Day 25 December 18 Boxing Day 26 December 18

6.8 Child Care Costs Table Families need to pay for the childcare at Sunshine at a different rate depending on the age of the child. The below table details the pay rates for children. Children aged 6 and older cannot be cared for at Sunshine as they will be school aged. There is a penalty rate for childcare on a Saturday which is paid in addition to the age rate. This information has been entered for you in the Constant Sheet.

Table 5: Child Care Costs Table

Age of Child Day Rate 0 $120 1 $110 2 $105 3 $90 4 $85 5 $80 6 Not Allowed

Saturday Penalty $10

7 Employees & Volunteers Sheet

The Employees & Volunteers Sheet keeps track of the employees who currently work at Sunshine.

Your first task on this sheet is to insert a lookup based formula to calculate the employer and employee superannuation contributions. Please note that volunteer carers are not taxed or liable for superannuation. Employer superannuation is not included in the employee’s annual salary. Using a lookup based formula calculate the annual tax withheld from employees based on their salary. Many employees have a HELP debt; for these employees, calculate the HELP amount that is withheld from the employee salary. Finally calculate the net annual banked salary for each employee.

To easily identify the volunteer carers, apply a conditional formatting to the table to show the entire row of data for volunteer carers as pale red background with red text.

Version 1 Feb 2018 Page | 4

8 Children Sheet

On the Children Sheet you will calculate details surrounding the children in the Sunshine childcare centre.

First you need to calculate the age of the children at the beginning of the year. Using this determine the initial room that the child will be cared for in and the room that they will be in following their birthday during 2018. Using a formula determine the number of days that a child will be in childcare.

Using functions construct a weekday string (seven-character string containing binary values) to use in the NetWorkDays.INTL function. In the string have Saturday and Sunday as non-working days, even if child attends the childcare centre. Have the days the children are in childcare as working days. For instance, if a child attends childcare centre on Tuesday, Wednesday, and Saturday, the weekday string will be 1001111. The sequence of zeros and ones in the weekday string represents Monday, Tuesday, Wednesday, Thursdays, Friday, Saturday, and Sunday respectively. If during working business days (Monday to Friday) a child is in childcare centre, then those days must be assigned to be 0, otherwise 1. However, if a child is in the childcare centre during weekend (Saturday), the value should still be assigned to 1 regardless they attend childcare centre or not, because you will calculate Saturday cost separately. Therefore, weekend attendance should not impact your weekday string function. One more example, if attendance is on Wednesday, Thursday, and Friday, then the weekday string will be 1100011. You will need to use logical and string search and concatenation functions to complete this task. The reason behind this calculation is to be used as weekend parameter in NetWorkDays.INTL function.

Calculate the costs of childcare for the child separately for the weekday costs before their birthday, Saturday costs before their birthday, weekday costs after their birthday, and Saturday costs after their birthday. When calculating costs, take into consideration that Sunshine is closed on public holidays. Finally calculate the total annual cost.

1. Weekday cost pre birthday = child’s childcare attendance days between start of the calendar year and

a day before birthday excluding public holidays and weekends (Part A) * childcare cost for that age (Part B). Part A is calculated using NetWorkdays.INTL function. This function includes start date, end date, weekends and public holidays value. You already know start date, weekend dates (weekday string) and public holidays. The challenging part is the calculation of end date, which is a day before birthday. Considering the age of a child you can easily calculate it for the current year. So, in your calculation if child’s birthdate is 6/30/2013, then a day before birthday will be 6/29/2018 for the current year. Hint: you may need to use EDATE or MONTH function to calculate a day before birthday. 2. Saturday cost pre birthday = child’s childcare attendance on Saturdays between start of the calendar

year and a day before birthday excluding public holidays (Part C) * childcare cost for that age(Part D) with weekend penalty. Part