商科Excel/essay/report/assignment/papaer写作
BISM7202 Excel Assignment
WEIGHT 30%
DATE 25 May 2018 at 17:00 (5pm)
DELIVERABLES One electronic file must be submitted via the Blackboard
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 students 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.
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,
$37,001 - $87,000 $3,572 plus 32.5c for each $1 over $37,
$87,001 - $180,000 $19,822 plus 37c for each $1 over $87,
$180,001 and over $54,232 plus 45c for each $1 over $180,
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%
$7 2 ,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%
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.
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 $
1 $
2 $
3 $
4 $
5 $
6 Not Allowed
Saturday Penalty $
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 employees 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.
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.
- Weekday cost pre birthday = childs 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 childs 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.
- Saturday cost pre birthday = childs 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 C is calculated using NetWorkdays.INTL function, but make in mind that your weekend value of this function is no longer a weekday string. You will need to construct a manual weekday string which displays Saturday as the only working day. Hint the last two values of your seven-character weekend string will be 01. You can copy your Networkdays function from weekend cost pre birthday column, just make sure to replace weekend parameter with the manually set weekend string.
- Your post birthday costs will be calculated in a similar way you did in your above calculations. Just be careful of start and end dates of your NetWorkDays function. Your birthday value of the current year will be the start date and end date will be end of the year value. When calculating costs consider the fact that children are one year older now.
Using two named ranges ( Database and Criteria ), set up an advanced filtering section which uses AND based filtering for the filter criteria entered in the Criteria named range. Using the same named ranges use 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, have the criteria set to show all children who attend for 5 or more days. Have advanced filtering turned on based on these criteria.
Apply conditional formatting to the rows of children to highlight the entire row where a child is in the same room before and after their birthday. Highlight the row with a pale green background and green text.
9 Families Sheet
On the Families Sheet you will keep track of the different families that have children at Sunshine. This also calculates the childcare benefits and childcare rebate for the family.
Using conditional statistics functions calculate the number of children and weekly hours a family has children in childcare for per week. For each day, a child is in childcare, they are there for 10 hours. Calculate the annual cost of childcare for the family, along with the childcare benefit, childcare rebate and annual payable amount (annual cost less benefits less rebates) for childcare. Calculate these values based on the details in the Children Sheet and Constant Sheet. Childcare needs to be paid for the entire 52 weeks of a year even if the child is not there each week.
10 Children Analysis
Using the information on the Children Sheet create a PivotTable on a new sheet to analyse the number of male and female children in each room. Using the PivotTable, show the gender of the children verse the room and age that the children are cared for in at the beginning of the year.
Using the created PivotTable, create a PivotChart and place it on a new Chart Sheet. Format the chart as a clustered column chart.
11 2019 Planner Sheet
You are required to conduct a What-If analysis to predict the total income from childcare in 2019. You need to construct the necessary information to conduct the analysis on this sheet. Use the number of children in 2018 and their ages are the beginning of the year. Do not consider the age increase of children.
There are five scenarios to consider in the prediction. Save the results of the scenarios to a new sheet, which contains meaningful labels for each of the values. Based on current scenario fill empty cells and calculate the number of children before and after change, income (childcare cost per year) and total amount. For income calculation you will assume that the age of children does not change during the year. You may need to use Constant and Children Sheets.
11.1.1 Current Situation There is no change to the number of children. Children attend on average 3.5 weekdays per week. On average 60% of children attend childcare on Saturday.
11.1.2 Low Decline There is a decline of 20% to the number of children. Children attend on average 2.5 weekdays per week. On average 40% of children attend childcare on Saturday.
11.1.3 Mild Increase There is an increase of 10% to the number of children. Children attend on average 3.5 weekdays per week. On average 60% of children attend childcare on Saturday.
11.1.4 Medium Increase There is an increase of 20% to the number of children. Children attend on average 4 weekdays per week. On average 60% of children attend childcare on Saturday.
11.1.5 Major Increase There is an increase of 40% to the number of children. Children attend on average 4.5 weekdays per week. On average 70% of children attend childcare on Saturday.
12 Investment Planning Sheet
On the Investment Planning Sheet , you need to perform a Solver evaluation on an investment planning option for Sunshine.
Sunshine has $100,000 to invest and needs to determine the best way to invest to maximise the annual return. Consider the following investment options with finance companies around Brisbane.
Table 6: Brisbane Finance Firms Investment Table
Account Annual Return Maturity Risk Rating Tax-Free
Central Brisbane
Finance 8.0%^ Short^ Low^ Yes^
North Brisbane
Finance 9.0%^ Short^ High^ No^
East Brisbane Finance 9.0% Long Low No
South Brisbane
Finance
9.0% Long High Yes
West Brisbane Finance 9.5% Long High Yes
You are required to ensure that at least 50% of the money is invested in short-term accounts and no more than 50% in high-risk accounts. At least 30% of the funds should go in tax-free investments, and at least 30% of the total annual returns should be tax-free.
Save the results of Solver to an answer sheet and restore the original values before submitting.
13 Implementation Guidance, Formatting and Professionalism
You must use Microsoft Excel 2016 (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 machines if you have developed it on your own machine.
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 (excepted were 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 students self-learning ability with a software application, and thus, has not been included directly in a tutorial exercise.
Sunshine 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 ifs 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.
14 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 students 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.
15 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 and your surname is
Smith, the name of your files would be BISM7202_Smith_41724943.xlsx.
16 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 Contacts.
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 addresses have been advised in
tutorials and are available on the BISM7202 Blackboard site under Contacts. Tutors will endeavour to
respond to all questions within 2 business days.
17 Important Date
Submission Date 25 May 2018 at 17:00. (5pm).
18 Late Submissions Request for extension of the assignment due date will need to be done via the submission of an online application at this link: https://my.uq.edu.au/node/218/0# Neither course coordinators nor lecturers can grant assessment extensions to students.
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 moving house; 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.
(^) General What
- If Analysis
FormattingConditional
Solver
FunctionsAdvanced Database &
PivotChartsPivotTables &
FunctionsFormulas &
Criteria
(^8) 10 (^6) 14 22 12 28 Marks (100) 0
–
(^3) 0
–
(^4) 0
–
(^2) 0
–
(^6) 0
– 10
0
–
(^5) 0
– 13
(^) Poor (^) Many errors exist in solution. Follows unprofessional process in developing solution. Demonstrates a poor understanding of functionality relating to criteria in Microsoft Excel. Many errors exist in solution. 4
–
(^6) 5
–
(^7) 3
–
(^4) 7
– 10
11
– 16
6
–
(^9) 14
– 21 Satisfactory
Some errors exist in solution. Follows a satisfactory
process in developing solution.
Demonstrates a satisfactory understanding of functionality
relating to criteria in Microsoft Excel in line with tutorial
knowledge only. Some errors exist in solution.
7
–
(^8) 8
– 10
5
–
(^6) 11
– 14
17
– 22
10
– 12
22
– 28 Good
^
Few or no errors exist in solution. Follows an
excellent process in developing solution.
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.
BISM7202 Excel As
signment Specification
Semester 1, 2018
19
Marking
(^) Rubric 100 marks that are at the end scaled back to 30 marks. If you received 75 marks out of This assignment is worth 30 marks. The marking rubric below is designed to reflect a marking scheme of 100 marks on the mark would be 22.5 out of 30.above rubric. Your final mark out of 30 is calculated as follows: 75 / 100 x 30 = 22.5. Therefore, the final Version 27 Nov 2017 Page | 10