Assessment 1
Develop Complex Spreadsheets
This is an assessment that may be worked on in study time and as homework.
Assessment presentation should be completed in a manner that is appropriate to professional business reporting.
Where necessary include forms, pictures, charts etc. may be added as attachments
Scenario
Breeze Appliances have three branches across Australia: Melbourne, Sydney and Brisbane. Every quarter, each branch manager is required to calculate the sales commission each sales person achieves each month and send this information to head office (see next page). At head office the sales information is collated into one spreadsheet for analysis.
A template will be required for the recording and calculation of sales and commission for each branch. Head office also requires a template to analyse the sales data received from each branch.
Requirements
Part 1
A workbook template will be created that will record and calculate the data for each branch shown on the next page. (The data for each branch must be recorded in a separate workbook.)
Completed
- Use the Spreadsheet Plan on page 4 to sketch this template.
- Import the text file called Commission Rates.txt into a new workbook. Name the worksheet Commission Rates.
- Create a named range for the commission rates data.
The Commission Rates data is used to look up the % commission each sales person will receive. This percentage will then be used to calculate the commission for each month, based on monthly sales.
- Insert a new sheet before the Commission Rates worksheet. Name the new sheet Sales.
- On the Sales worksheet, calculate the commission each sales person will receive each month.
- Each month if a sales person equals or exceeds a 12 % commission target, they receive an additional bonus of $1,250. Insert a column for each month to determine if the sales person will receive the bonus. (This can be achieved by combining an IF and a VLOOKUP statement.) The commission target and the amount of bonus on offer will vary every quarter. Ensure that this is taken into account when designing the spreadsheet by positioning the bonus amount and the % commission target in separate cells at the top of the worksheet data.
- Columns must be wide enough to display data properly.
- Include a header containing the file name of the workbook.
- Today's date and a Branch name area must appear on the worksheet. (The branch name will be filled in when the template is used.)
- Calculate the total sales for each month.
- Save the workbook file as a template with an appropriate file name in the correct templates folder. Note that macros are required in the template.
- A summary of the sales for each of the three months recorded must appear on the worksheet. This summary must include average, maximum and minimum calculations for each month. All figures must be rounded to the nearest $100.
- Format the spreadsheet so it is clear and easy to read. Use Calibri font in keeping with the organisation's house style. Format currency columns to two decimal places.
- Check all that formulae work correctly.
- Ensure that the workbook template is set up to automate all calculations so that user only needs to enter the sales data for each month.
- Ensure users cannot enter data in formula cells by protecting these cells.
- Develop a macro that will sort all sales persons into alphabetical order.
- Develop a macro that will print only the sales summary information.
- Assign these two macros to buttons at the top of the worksheet.
Part 2
- Using the template created in Part 1 produce a workbook for each branch using the data shown below.
- Ensure all data is checked to ensure accurate input.
- Each workbook should be saved with
Part 3
Create a workbook template that will display the total sales for each month from each branch.
Completed
- Use the Spreadsheet Plan on the next page to sketch this template.
- Columns must be wide enough to display data properly.
- Include a header containing the data (to the right) and footer with the file name in the lower right corner.
- Use Calibri font in keeping with the organisation's house style.
- Save the workbook file as a template with an appropriate file name in the correct templates folder.
- Total each column and row to determine the total sales for each month and each branch.
- Create a chart that displays the sales for each branch over the three month period. Use an appropriate chart type and add a title. Insert the chart on a new worksheet and rename the sheet appropriately.
- Create a chart that displays the total sales for each branch. Use an appropriate chart type that will show which branch has the larger percentage of sales against all other branches. Use appropriate data labels and add a chart title. Explode the largest segment. Insert the chart on a new worksheet and rename the sheet appropriately.
Part 4
- Using the template created in Part 3 produce a workbook using the data from the workbooks created in Part 2.
- The workbook should be saved with an appropriate file name in your working folder.
an appropriate file name in your working folder.
- Ensure the sales person data is sorted into alphabetical order and summary information is printed for each workbook.
Part 5
1. Explaining how you have checked formulas in the workbook template created in Part 1.
2. Write down the formulas used to calculate the following in the workbook template created in Part 1.
% commission determined for each sales person for the first month
The average sales for a month rounded to the nearest $100
3. Explain the steps you would take to edit the print range macro created in Part 1 to change the data range selected.
4. Fill in the table below indicating the file name and folder location you used to store spreadsheet files for this assessment.
5. Use manuals, user documentation and online help to look up the following topics. Record your findings in the spaces provided.
Explain what the PMT function is and when you would use it.
How would you split the screen so you could view the top and lower part of a worksheet?
Part 6
1. A sales person was omitted from the data for Brisbane; his details are shown below. Add the sales person's data to the Brisbane workbook (the Total Sales Workbook should automatically update). Sort the sales people.
Brisbane Branch
2. Print preview and print the three worksheets that detail the sales for each branch. Ensure all data is printed.
3. Print the template that each branch uses, and then print another copy showing the formulas.
4. Print the template that is used by head office, and then print another copy showing the formulas.
5. Print the entire workbook that head office has produced that displays the sales from all branches.
Assessment 2
Prepare tax documentation for individuals
Assessment Conditions
This is an assessment that may be worked on in study time and as homework.
Assessment should be completed in a manner that is appropriate to professional business reporting.
All sections and requirements of the assessment task must be included.
Feedback may be sought prior to submission.
Where necessary include forms, pictures, charts etc. may be added as attachments
Assessment Tasks
Procedure
Review the case studies supplied with this Assessment Task and complete the tasks:
1. Ryan God Ling, aged 30 and single, provided the following details for the year ended 30 June 2015. Ryan derives income from his employment as a bus driver and some investments. Ryan has no private health insurance
Receipts:
|
Fully franked dividend from F/S Ltd. - amount received
|
$3,300
|
|
70% franked dividend from FI Ltd - amount received
|
$2,900
|
|
Unfranked dividend from Back Ltd
|
$1,750
|
|
Gross Wages (PAYG withheld $20,500)
|
$80,800
|
|
Bank Interest (net of $98 TFN tax deducted)
|
$102
|
|
Honorarium for duties as president of the swim club
|
$350
|
|
Insurance lumpsum compensation (for damage to back sustained in car accident)
|
$12,000
|
|
Army Reserve Payment for part-time services
|
$1,200
|
Payments (all fully substantiated):
|
Purchase of compulsory uniform
|
$480
|
|
Laundry of uniform (estimate)
|
$75
|
|
Parking fees (Ryan drives his car to the bus depot)
|
$440
|
|
Sunglasses used for driving
|
$150
|
|
Investment Journals
|
$230
|
Required:
Calculate Ryan's taxable income and tax payable for the year ended 30 June 2015.
2. John Modra, a resident Australian taxpayer, has the following information in relation to the current year of income:
Personal details:
|
Name
|
John Modra
|
|
Tax File Number
|
365 254 789
|
|
Occupation
|
Train Driver
|
|
Date of Birth
|
28 December 1970
|
|
Postal Address
|
Address of tax agent
|
|
Home Address
|
15/40 Tennis Court, Melbourne 3000
|
|
Day Time Phone
|
(03) 9045 1555
|
|
EFT details
|
BSB 065000 A/c no. 987 654 879
|
|
Spouse
|
Toni Modra
|
Payment Summary details:
|
Employer
|
SA Line ABN: 13 143 583 611
|
|
Gross Wage
|
$62,171
|
|
Allowances:
|
Uniform allowance $265
|
|
PAYG withheld
|
$12,150.00
Reportable Fringe Benefits: $14,000
|
Other Income:
- Bank interest - $200 from account jointly held by John and Toni at ANZ Bank, Melbourne Branch
- Dividend from Westfarmers Ltd. - $140. The Dividend statement showed a $60 franking credit
- Distribution from the Safe-as-a-House property Investment trust - $539
- John sold his Westfarmers Ltd. Shares during the income year at a profit of $3,000. He elected to apply the 50% discount to this gain.
Expenditure:
- Maintenance and cleaning of uniform $147
- Union subscription $450 - Australian workers Union
- Donations to the Red Cross $15 and the Klemzig Soccer Club $200
- Your fee for preparing John's 2013/14 income tax return was $90.
Other information:
John fully maintained his wife Toni (date of birth: 22 June 1975) and dependent son, aged 13 years, for the entire year. Her taxable income was $38,000. John incurred net eligible medical deductions of $5,000 on behalf of himself and his family. He is eligible to claim the net medical expenses of tax offset in 2014/15.
Tax agent's details:
Agent reference number: 12 458 147
Tel (03) 9875 1458
Required:
- Complete the tax return for John Modra for income year 2014/15.
- Write an accompanying letter to your client, outlining how you have taken full advantage of the available benefits and allowances under tax legislation,
- Provide the completed form and cover letter to your client (your assessor) within agreed timeframes.
Specifications
You must provide:
1. a completed letter to your client, as outlined above
2. a completed tax return form
Your assessor will be looking for:
- evidence that you analysed and identified the needs of the case study
- evidence that you have successfully completed a tax return
- evidence that you are able to communicate effectively with your client through your written letter
Assessment - 3
1. What are the sources of Australian Taxation Law? Explain in detail the role of Australian Taxation office.
2. Explain Assessable Income. What are the methods of determining Assessable income?
3. In the context of Australian Taxation Law please explain the following terms:
a. Medicare Levy
b. MLS
c. Exempt income
d. Non-assessable, non-exempt income
4. Explain the term ‘Deductions'. What are the two categories of deductions? What factors generally determine whether or not a deduction is allowable?
5. How to tax offsets differ from deductions?
6. A new client has decided to use your professional services. Provide a list of questions you would ask them to determine and confirm the taxation preparation requirements?
7. What sources would you use to gather current data in relation to assessable income and allowable deductions?
8. What are the responsibilities of a tax agent in relation to identifying and resolving discrepancies?
9. A new client has approached you and asked for advice on what documents will be required for the preparation of their first tax return in Australia. What advice would you provide and what documents would be needed?
10. Scarf, a 23 year old single resident tax payer, conducts business as a commercial painter. He derived net business income of $114,270 and paid PAYG tax instalments of $24,590 during the 2014/15 tax year.
Scarf had no other income or deductions during the year and was covered by health insurance covering optical and dental benefits only.
Calculate Scarf's net tax payable or refundable for the 2014/15 tax year.
11. Josh Motlop ceased to be an Australian resident for tax purposes on 30 April 2015. Josh derived a taxable income of $36,000 during the portion of the 2014/15 tax year that he was a resident. You are required to:
a) Calculate Josh's tax free threshold
b) Calculate Josh's tax on taxable income
c) Calculate Josh's liability for the Medicare Levy
d) Calculate Josh's low income tax offset
e) Using the above responses, calculate the balance of tax payable for Josh
12. For an individual, when is their 2015 tax return due? (Assuming they are lodging themselves)? Describe some of the potential consequences of failing to submit an income tax return on time.
13. A taxpayer pays $1,800 for 6 months insurance. The premium is paid on January 31, 2015. Prepare the account to reflect this transaction and prepare the journal entries for the end of first month.
14. Describe the difference between legislative requirements and professional standards.
15. A client for whom you have prepared the current tax return approached you for advice and support for an audit covering the last 5 returns. You were not involved in the preparation of the previous four.
The client has the written notice from the ATO regarding the audit. What advice and support will you provide?
Attachment:- Assessment Project.zip