Ask Question, Ask an Expert

+1-415-315-9853

info@mywordsolution.com

Ask Financial Accounting Expert

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

Financial Accounting, Accounting

  • Category:- Financial Accounting
  • Reference No.:- M93123373
  • Price:- $370

Guranteed 48 Hours Delivery, In Price:- $370

Have any Question? 


Related Questions in Financial Accounting

Need slides need a one page executive summarybelow is the

Need slides. Need a one page executive summary. Below is the scenario: "Hi again. I've got news about our client. "ExxonMobil is looking to increase revenue by 10 percent and possibly reduce costs. Need an executive summ ...

What has been strides position on dividend payouts in the

What has been Strides' position on dividend payouts in the past (pattern, relationship with earnings, etc.)? What factors affected its dividend policy?

Accounting financial assignment -question - in recent years

Accounting Financial Assignment - Question - In recent years a number of companies have gone into liquidation (been 'wound up') because they have not been able to meet their liabilities when they fell due. In Australia, ...

An investment offers 6800 per year with the first payment

An investment offers $6,800 per year, with the first payment occurring one year from now. The required return is 7 percent. a. What would the value be today if the payments occurred for 20 years?  b. What would the value ...

Case study - the athletes storerequiredonce you have read

Case Study - The Athletes Store Required: Once you have read through the assignment complete the following tasks in order and produce the following reports Part 1 i. Enter the business information including name, address ...

The ipl just signed sachin to a contract consisting of

The IPL just signed Sachin to a contract consisting of eight, end-of-year payments worth $9 million each, with the first payment precisely one year from today. On the other hand, Dhoni recent deal calls for six annual pa ...

Part adbm financial solutionsyou are a financial consultant

Part A DBM Financial Solutions You are a financial consultant working with DBM Financial Solutions and have a portfolio of clients you work with in achieving financial management solutions. Client 1- Manhattan Limited Yo ...

In its first year of operations cullumber company

In its first year of operations, Cullumber Company recognized $31,800 in service revenue, $6,600 of which was on account and still outstanding at year-end. The remaining $25,200 was received in cash from customers. The c ...

Company a is a calendar year company that depreciates all

Company A is a calendar year company that depreciates all its machinery on a straight-line basis. On January 1, 2016, the company purchased machinery costing $100,000, with an estimated useful life of 10 years and a zero ...

Assessment 1develop complex spreadsheetsthis is an

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 re ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As