Leilani Tuati is the owner and manager of a wholesale business called Lani Trading which sells bar stools to hospitality venues throughout New Zealand.  Her business is expanding, and a friend has informed her that she needs to prepare a budget before she applies to the bank for a loan.

You offer to prepare a three-month budget for the last quarter of the year so that she will be able to prepare a budget for the whole of the following year.

Estimated Sales (August and September are Actual)

Based on earlier experience, Leilani estimates that 20% of sales will be for cash with the remainder on credit. Of the credit sales, 85% will be received in the month after sale and 15% the second month after sale. The average selling price per stool is $45.00.

Purchases and Inventories

Leilani sources inventories from factories in the Waikato region and each month purchases sufficient stools to meet estimated sales for the following month. The average purchase price per stool is $23.00. Payment is made on the 20^th of the month following. (For the purposes of this assignment you may ignore any inventory held).

Other

· The business employs a salesperson and a part-time office worker. The monthly salaries bill is $7,500 a month. Staff are paid on the 20^th of each month for that month. (Ignore PAYE and other deductions).

· General costs (including utilities, telephone, stationary, ISP provider etc.) are estimated at $1,200 a month, paid on the 20^th of the month following purchase. Accounts Payable for General Costs as at 1 October are $1,120.

· The business operates from an office at the back of Leilani’s home. The business share of rates per quarter is $1,500 and these are payable on 1 December.

· The annual business insurance premium is $3,000 and was paid on 10 January 2020.

· The business currently spends $500 a month on Advertising, payable in the month the advertisements appear.

· The balance of Cash at Bank on 1 October 2020 is $11,700.

Non-Current Assets

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

· The office computer equipment which cost $10,000 has an expected life of 4 years with $2,000 residual value.  

· The work van cost $30,000 and it was estimated that the van will be suitable to operate for five years after which time it will have a resale value of $10,000.

· Running costs for the work van are estimated at $800 a month, payable in the month incurred.

Required:

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

(5 marks)

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

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

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

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

Read less