ASSESSMENT 2
Course Name FNS60215
Advanced Diploma of Accounting
Subject/module Financial Performance
Assessment method Scenario
Weighting 500/0
Units of Competency FNSACC608
Evaluate Organisation's Financial Performance
Instructions
1 . Assessments should be completed as per your trainer's instructions.
2. Assessments must be submitted by the due date to avoid a late submission penalty.
3. Plagiarism is copying someone else's work and submitting it as your own. You must write your answers in your own words and include a reference list. A mark of zero will be given for any assessment or part of an assessment that has been plagiarised.
4. You may discuss your assessments with other students, but submitting identical answers to other students will result in a failing grade. Your answers must be yours alone.
5. Your trainer will advise whether the assessment should be digitally uploaded or submitted in hard copy. Assessments that are digitally uploaded should be saved in pdf format.
6. You must attempt all questions.
7. You must pass all assessments in order to pass the subject.
8. All assessments are to be completed in accordance with WHS regulatory requirements.
A Debit Ratios
Scenario 1 :
Refer to page 1 72 Financial Accounting and undertake this Assessment Task:
Goal: create an Excel Spreadsheet to calculate the current ratio and debt ratio for Woolworths based on their latest financial statements and use the results to answer the following questions about the company:
Scenario: Your client is considering investing $5,000 in a well-known Australian company Woolworths. You know that the current ratio and the debt ratio measure whether a business has the assets to cover its liabilities.
Your task is to create a simple spreadsheet to calculate the current ratio and the debt ratio for the company, data will be found in the Woolworths annual report http://www.woolworthslimited.com.au/when you have completed your analysis answer the following questions.
1. Does the company have an acceptable current ratio? How can you tell?
(5 marks)
2. Does the company have an acceptable debt ratio? How can you tell?
(5 marks)
3. What is the trend (up or down) for the ratios? Is the trend positive or negative? Why? (5 marks)
Step-by-Step
1. Locate the following current and prior year information for Woolworths (found in the consolidated balance sheet);
a. Current assets
b. Total Assets
c. Non-current Liabilities
d. Total Liabilities
e. Total Shareholder Investment (Equity)
f. Total Liabilities and Shareholder Investment (calculate on the spreadsheet)
2. Open a new excel spreadsheet
3. Create a bold faced heading for your spreadsheet that contains the following;
a. Debit Ratios - Scenario 1 b The current ratio and the debt ratio
c. Woolworths d Today's Date
4. Two rows down from your worksheet heading create a column heading titled 'Woolworths (in $m)' make it bold and underline.
5. One row down from your Woolworths column heading create a row with the following bold underlined column titles;
a. Account
b. FYxx - the most recent financial year for example, 12
c. FYyy - the prior financial year for example, 1 1
6. Starting with the 'Account' column heading, enter the data found in 1, above. You should have seven rows of data with descriptions for example 'Current Assets'. Format the columns as required.
7. Skip a row at the end of your data and then create a row title 'Current Ratio' and another row titled 'Debt Ratio'.
8. Enter the formula for each Ratio in the FYxx and FYyy columns. You should have four formulas. Make both rows bold.
9. Save your work and upload to Google Classrooms.
(5 marks)
Part A total Marks - 20
Notes:
B Profitability Patterns
Scenario 2:
Refer to page 581 Financial Accounting and undertake this Assessment Task:
How to calculate EPS?
Profit after income tax - Preference dividends EPS =
Weighted average number of ordinary shares issued
Goal: Create an Excel Spreadsheet that charts the earnings-per-share (EPS) trend of the company.
Scenario: Your client is considering investing $5,000 in a well-known Australian company Woolworths. You are to investigate the financial performance of this company prior to providing advice to the client; one measure of financial performance is earning (profit) per share. When you have finished your spreadsheet answer the following questions.
1. Why is EPS such a useful business statistic? (5 marks)
2. What is the trend in basic EPS? ( Based on the profit after tax for Woolworths)
(5 marks)
3. List the EPS components given in this chapter (Page 578) Fill in the amounts for Woolworths for the most recent year and calculate EPS. Explain any difference between the amount you calculate and the amount reporting in the financial statements.
(5 marks)
4. Beside EPS, what other financial measures should you examine prior to making an investment decision? (5 marks)
Step-by-Step:
1. Obtain a copy of the most recent annual report for Woolworths online hj p://www.woolworthslimited.com.au/ and locate the annual report through the Investors Centre and then the Five Year Summary in the latest Financial Report.
2. Open your new Excel spreadsheet
3. Create a bold-faced heading for your spreadsheet that contains the following:
a. Profitability Patterns Scenario 1 Excel Exercise
b. Woolworths Earnings - Per - Share Analysis
c. Today date
4. Move down 2 rows and enter the following title:
a. Woolworths
b. Earnings - per - share
c. For the year ended
5. Move down 2 rows and create column headings; 'Item' and the 5 years in Five Year Summary e.g.'2012', '201 1', '2010', '2009', '2008'.
6. Move down one row and enter the headings 'Normal basic EPS' followed by the EPS figures from the Five Year Summary for the five years
7. Highlight the two rows and click on the Chart Wizard. Select XY (Scatter) Chart type with lines connecting data points. Click 'Next' and select 'Series in: rows'. Then click 'Next'
8. Click on the 'Titles' tab. For the chart title, enter -Earning per Share-. For the category X axis, enter 'Year' and for category Y axis enter 'EPS'
9. Click on the 'Legend' tab and click to move the legend to the bottom of the chart. Click 'Finish'. Position your chart below the EPS data
10. Save your work and upload to Google Classrooms
(5 marks)
Part B total marks — 25
Notes:
C Investing - long term finance
Goal: To create an excel spreadsheet that calculated the profit from a proposed investment in equipment using different depreciations method.
Scenario 3:
The owner of the local Kwik Copy franchise is considering investing in a new colour copier. The following information is related to the acquisition and operation of the copier:
Initial investment $50,000
Salvage value $5,000
Useful life 6 years
Annual revenue attributable to the
Investment
Annual operating expenses, excluding $21 ,000
depreciation, associated with the Investment $7,000
The copier will be in use for:
f 2880 hours in year 1 f 2550 hours in each of years 2 and 4 f 2190 hours I year 5
1460 hours in each of years 3 and 6.
When you have finished your worksheet, answer the following questions
1. Which depreciation method produces the highest profit in year 1? In year 6?
If it's not the same method for each year why not? (5 marks)
2. Which method would the business prefer for tax purposes? Why? (5 marks)
3. Which method would the business prefer for reporting to bankers and creditors? Why? What if the business wanted to change methods after two years? (5 marks)
Step-by-Step:
1. Open a new Excel Spreadsheet
2. Prepare a bold faced heading with the following information: a Investments Scenario 3 Excel Exercise
b. Colour Copier Depreciation Schedule
c. Today's Date
3. Two rows under your heading prepare four sections in you worksheet: Section 1 : needs to contain the information listed in the table above plus a line item for the depreciation per unit.
Section 2: will calculate profit from the Investment using straight line depreciation calculated using the SLN spreadsheet function this sections will have the following format:
Section 2 Straight-line depreciation
Year
Revenue
Expenses
Depreciation profit
Section 3: will calculate profit from the investment using reducing - balance depreciation (at 1 .5 times the straight line rate). This section will have a format like that of section 2.
Section 4: will calculate profit from the investment using units - of production depreciation. (There is no Excel formula for this method.) This section will have a format like that of section 2, with the addition of a row for the annual units of production.
4. Format all cells appropriately. Save your work and upload to Google Classrooms
(10 marks)
Total Part C Marks - 25
Notes:
D Investing - Finance Options
Scenario 4:
The owner of the Kwik Copy franchise has reviewed the option for the outright purchase of the Photocopier and decides to get a quote on leasing a Konica Photocopier.
Goal: To create an excel spreadsheet that calculates the viability of the leasing proposal against the outright purchase option above.
f Total price of the Photocopier $50,000, monthly repayments are set at
$998.95 per month over 5 years f The machine produces Photocopies at 45 pages per minute f Based on the hours above include in Scenario 4 the price per Copy rates:
80% of the copy is Black and White 1 cent per copy
20% of the copy is Colour 7 cents per copy f The annual operating expenses are included in the price per copy therefore these at $0.00 f Balloon payment of 25% at the end of 5 years f Use the Straight-line method to calculate the estimated costs and profit of this option over 5 years
When you have finished your worksheet, answer the following question.
1. Based on your calculations, which finance option is the best for this company? (8 marks)
2. Format all cells appropriately. Save your work and upload to Google
Classrooms (7 marks)
Total Part D -15 marks
Total Marks, Assessment 2 — 85
Notes:
END