Leilani Ma'a owns a car hire business called Ma'a Motors. Leilani comes to you asking for assistance in preparing budgets for the quarter 1 October to 31 December 2020. Following discussion you have noted down the following data.

Revenue

There are ten cars and these are available for hire for each month for the given number of days:

Month

Days

September

30

October

31

November

30

December

31

The average hire price is $80 per day giving $800 daily revenue if all ten cars are hired out.

Although all the cars are available every day, on average they are only hired for 85% of the time.

Tourists pay when they collect the car, but business travellers use the credit facility and pay on the 20^th of the month following their hire of the car. Based on earlier experience 40% of the hirers are business travellers and the rest (60%) are tourists.

Non-Current Assets

It is business policy to use the straight line method of depreciation for all non-current assets.

The cars when bought two years ago had a cost price of $300,000 with an estimated useful life of five years and a residual value of $100,000

The office equipment which cost $20,000 has an expected life of 6 years with $2,000 residual value.

Other

The business has a contract with Habib Garages to clean the cars and service them. The monthly fee for these services is $3,000, paid in the month incurred.

General costs (including telephone, stationary, ISP provider etc.) are estimated at $400 a month, paid in the month they are incurred.

Leilani hires Cheng Xu at $5,000 a month and this is paid on the twentieth of the month in which it is earned. (Ignore PAYE and any other deductions).

The depot is leased for a monthly fee of $4,000 and is payable on the first of each month

The advertising is estimated at $500 a month and is payable by the 20^th of the following month.

The annual insurance premium is $15,000 and was paid on 1 June 2020.

The balance of Cash at Bank on 1 October 2020,is $11,750

The Balance of Accounts Payable on 1 October 2020 relating to Advertising was $525

Required:

(i)On the Data Input Sheet, enter the given data which has not yet been entered.

(10 marks)

(ii)On Revenue & NCA sheet, complete the revenue, cash collections and depreciation calculations for the quarter 1 October to 31 December 2020

(25 marks)

(iii)On the Cash Flow Sheet, complete the Projected Cash Flow from Operations for each of the three months October, November and December and the totals for the quarter 1 October to 31 December 2020. Note some cash flow rows have been named, but you will need to enter the remaining cash flow names yourself.

(30 marks)

(iv)On the Income Statement Sheet, complete the Projected Income Statement for the quarter ended 31 December 2020. A month by month Income Statement is not required. Note that some expenses have been identified but you need to enter the remaining expenses yourself.

(15 marks)

(v)Check the flexibility of your workbook by changing the estimated Advertising on the Data Sheet from $500 to $450. Enter the resulting Net Profit and ending Cash Balance in the boxes provided on the Income Statement Sheet (remember to change the Advertising back to the original $500 before submitting your assignment).

(20 marks)

Ignore interest calculations and GST.