Hey there, I'm currently doing a budgeting plan for my group assignment and we've...

50.1K

Verified Solution

Question

Finance

Hey there,

I'm currently doing a budgeting plan for my group assignment and we've been provided some data to fill into an excel spreadsheet. It also involves a cash flow.

There are 3 parts we're having difficulty with which is the finishing of the cash flow, NPV and this question;

5. At 30 June 2017 PSC had a $5 million secured bank loan with a maturity of 30 June 2022 and an interest rate of 6% p.a. compounded quarterly. The scheduled repayments are $100,000 every three months with the initial $100,000 payment due on 30 June 2018 and the final $100,000 payment due on 30 June 2021. What is the amount of the final one-off repayment that is due on 30 June 2022 to fully pay off the loan? (3 marks)

I have provided all the data that we have been given and our current excel spreadsheet underneath;

1) Parramatta Scenic Cruises Pty Ltd (PSC) is a family-owned ferry business that operates on Sydneys Parramatta River. Jane Jetson founded the company when she arrived in Australia and remains the Chief Executive Officer. Janes two children, Judy and Elroy, occupy key management roles in PSC. Judy Jetson is the Chief Financial Officer and Elroy Jetson is the tax accountant. PSC reported sales of $11 million for the 2017 financial year.

2) PSC is investigating a proposal to renew part of their fleet that involves replacing an existing ferry with a new, faster, 330-seat ferry costing $3 million. Judy is concerned that the net profit of the new ferry wont generate a fast enough payback period. Therefore, she has discussed her concerns with Jane. Jane carefully explains to Judy the many reasons that profitability is not a good measure of financial success. Judy then prepares to conduct a rigorous cost-benefit analysis to ensure that the new ferry is financially viable.

3) Last month, Judy and Jane paid for a study by SeaWay Consulting P/L at a cost of $487,000 and the study concluded that the large and growing tourism market will generate sufficient demand for a new ferry. Today, PSC must decide if they will proceed with the investment in the new ferry and the associated sale of their existing ferry.

4) Elroy is really excited about the new ferry. It is a 34-metre, 119 tonnes displacement ferry capable of 35 knots with two cabins and four outside decks with a capacity for 330 passengers. According to the Australian Taxation Office (ATO) the new ferry has a sixteen-year life for taxation purposes.

5) NSW Maritime requires that all vessels have a Certificate of Operation that indicates that the vessel has been inspected and found to comply with the minimum standards set out in NSW maritime legislation. The compulsory certificate is required before PSC commences operations with the new ferry. Certification requires PSC to spend $200,000 on safety equipment. The certificate expires four years later at which time the ferry must be recertified and the safety equipment replaced at an estimated cost of $200,000. Recertification must occur every four years.

6) Because of limitations on the number of vessels at particular wharves on the Parramatta River the new ferry will replace an existing ferry. Even though the new ferry has an effective life of fifteen years, the Jetson family will operate the ferry for ten years only. Jane has arranged for the sale of the existing ferry for $300,000 today. If they dont proceed with the new ferry PSC will continue to operate the existing ferry for ten years. The existing ferry was purchased six years ago for $2 million. Elroy states that the annual depreciation expense of $200,000 per annum is based on the ten-year tax life at the time of purchase. The existing ferry has a current book value of $800,000.

7) Elroy has suggested that because the new ferry is analysed over a ten-year time period they need to ensure that they recover all the costs they have incurred to date. Therefore, he recommends the $487,000 SeaWay Consulting fee be allocated equally over the ten-year analysis period. 2

8) PSC will borrow $2 million using a secured ten-year interest-only loan at an interest rate of 5% per annum to partly finance the new ferry. The loan requires annual interest payments of $100,000 starting in one years time. Today, inventory will need to increase by $110,000 to $610,000. Accounts receivable will increase to $750,000 from the current figure of $660,000.

9) At the moment PSC is leasing their Harris Park wharf facility to an unrelated entity for $85,000 p.a. The introduction of the new ferry will require that PSC use the wharf on a full-time basis. In this case, PSC must terminate the lease agreement. There is debate among the family members if this lease agreement is an example of a sunk cost or not.

10) At the moment, the existing ferry generates annual cash sales of $1,400,000. This sales figure is predicted to remain constant for each of the next ten years. The new ferry is predicted to generate cash sales in year one of $1.8 million in year 1 and this sales forecast is anticipated to increase by 4% per annum for the foreseeable future.

11) Judy has gathered some information regarding current and expected costs. At the moment, fixed costs are $400,000 per annum. Fixed costs would rise to $500,000 in year one with the new ferry. PSC is confident that they can reduce the increase in fixed costs by 2% p.a. after the first year. Wages expense is currently $900,000 each year and is predicted to increase to $1.4 million with the introduction of the new ferry. Judy reminds the family about the importance of incremental cash flow items when performing a financial analysis.

12) The current annual maintenance cost of the existing ferry is $63,000. The new ferry will require no maintenance in the first three years of its life because it is covered by a manufacturers three-year warranty. However, after the warranty expires in year 4 the annual maintenance expense will be $87,000. Jane has advised that PSC has an insurance policy that will insure any number of the companys vessels at a fixed annual fee of $145,000.

13) It costs $175,000 a year to operate PSCs head office and marina on the Parramatta River at Harris Park. With careful management PSC believes they will not require any additional personnel in headquarters if they purchase the new ferry. In any case, the annual head office operating expense will increase by just 2% each year.

14) The ATO classifies the safety equipment required for the Certificate of Operation as a business expense, and that expenses incurred in running PSC are tax deductible in the year the expense is incurred.

15) SeaWay Consultings report estimates that the new ferry will have a market value of $1 million in ten years time. The existing ferry has a book value of $800,000 today and can be sold for $300,000 today. PSC will use these sale proceeds to distribute a $300,000 dividend to its shareholders today. SeaWay Consulting advises that in ten years time the existing ferry would be worthless.

16) The company tax rate is 30% and the required rate of return is 12%

Now I will paste the Excel spreadsheet we have so far -

image

image

TEAM LEADER NAME: TUTOR NAME TUTORIAL DAY AND TIME: Year 3 Year 4 Year 5 Year 6 1. Cash Flows at the start Capital cost of new ferry Sale of old ferry Tax saving: (800K-300K) x 30% Year o 3,000.000 300,000 150,000 110,000 90,000 200000 60000 STUDENT NUMBER TEAM MEMBER NAME (TEAM LEADER) Increased accounts receivable Tax saving on certificate 2890000 2. Cash Flows over the life Sales Increased fixed Costs ear 1 1800000 8720001946880 2024755 2105745 2189975 2277574 2368677 2463424 2561961 ear 0 ear ear 5 ear 6 ear 10 98000 500000 -85000 0 96040 90392 86813 85076 83375 100000 -50000 500000-500000 85000 87000 0200000 60000 88584 500000-500000 85000 -87000 -50000 500000500000 lease of pier 85000 85000 -87000 0-200000 60000 -85000 Fo Repairs and Maintenance 85000 -87000 85000 87000 -87000 -87000 Tax saving on certificate 11150001189 334500 780500 000 356700 832300 1265840 379752 886088 118636 1341509 335591 427583 1516990 455097 999308 1061893 Pre-tax cashflow 1469 865 17063481806586 541976 1194444 1264611 402453 428275 440959 511904 ax After-tax cashflow Depr. tax shield- new ferry Foregone depr. tax shield- old 780500 832300 886088 783045 999308 1061893 1028905 1194444 1264611 3. Cash flows at the end Sale of new ferry Tax expense: (0-1,000,000) x 30% ear ear 4 ear 5 ear ear 1000000 300000 110000 ear 0 ear 3 receivable r 90000 TEAM LEADER NAME: TUTOR NAME TUTORIAL DAY AND TIME: Year 3 Year 4 Year 5 Year 6 1. Cash Flows at the start Capital cost of new ferry Sale of old ferry Tax saving: (800K-300K) x 30% Year o 3,000.000 300,000 150,000 110,000 90,000 200000 60000 STUDENT NUMBER TEAM MEMBER NAME (TEAM LEADER) Increased accounts receivable Tax saving on certificate 2890000 2. Cash Flows over the life Sales Increased fixed Costs ear 1 1800000 8720001946880 2024755 2105745 2189975 2277574 2368677 2463424 2561961 ear 0 ear ear 5 ear 6 ear 10 98000 500000 -85000 0 96040 90392 86813 85076 83375 100000 -50000 500000-500000 85000 87000 0200000 60000 88584 500000-500000 85000 -87000 -50000 500000500000 lease of pier 85000 85000 -87000 0-200000 60000 -85000 Fo Repairs and Maintenance 85000 -87000 85000 87000 -87000 -87000 Tax saving on certificate 11150001189 334500 780500 000 356700 832300 1265840 379752 886088 118636 1341509 335591 427583 1516990 455097 999308 1061893 Pre-tax cashflow 1469 865 17063481806586 541976 1194444 1264611 402453 428275 440959 511904 ax After-tax cashflow Depr. tax shield- new ferry Foregone depr. tax shield- old 780500 832300 886088 783045 999308 1061893 1028905 1194444 1264611 3. Cash flows at the end Sale of new ferry Tax expense: (0-1,000,000) x 30% ear ear 4 ear 5 ear ear 1000000 300000 110000 ear 0 ear 3 receivable r 90000

Answer & Explanation Solved by verified expert
Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:
  • Unlimited Question Access with detailed Answers
  • Zin AI - 3 Million Words
  • 10 Dall-E 3 Images
  • 20 Plot Generations
  • Conversation with Dialogue Memory
  • No Ads, Ever!
  • Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!
Become a Member

Other questions asked by students