Completion of a Workbook of Budget Spreadsheets

Completion of a Workbook of Budget Spreadsheets

Sally and Bill Foo own and operate a business called Foo Cars which hires out electric cars for children. There are ten cars representing a variety of vehicles such as police cars, ambulances and jeeps. All cars can be self-driven or operated by remote control.

Revenue: There are two main sources of revenue.

Waterfront: On weekends and public holidays they operate the business at a parkon the Metroland Waterfront and cars are hired out in varying lengths of time. Past experience has shown that each day on the waterfront generates about $1,500 in revenue. Waterfront sales are all for cash.

The table below gives the number of days (weekend-days and public holidays) when Foo Cars will operate on the waterfront.

MonthWaterfront Days
October9
November8
December14

Party-hire: Bill takes the cars to private parties where, for 2 hours, the children have access to all the cars. Demand for this service is very high because there are limited days on which Bill is free and so customers are required to book well ahead and pay for the parties one month before the date of the party. Parties are charged out at $300 each.

MonthParties
October5
November5
December10

Revenue received in advance as at 1 October 2018, was $1,500.No parties are scheduled for January, as Bill and Sally will be on holiday.

Non-Current Assets

  • It is business policy to use the straight-line method of depreciation for all non-current assets.
  • The ten cars were bought as a job lot for $4,600 and they are expected to have a useful life of six years with a residual value of $1,000
  • As people carry very little cash, payment EFTPOS equipment including a laptop were acquired. The laptop is also used for keeping the accounts. This equipment was bought for $2,500 with an expected life span of four years and nil residual value.
  • Bill bought an old horse float which attaches to his truck (not part of the business). The float is used to store the cars and transport them from place to place. The float cost $7,000 and is expectedto last for six years with a residual value of $1,000.

Other

  • The batteries are re-charged every evening and the power bill averages at $420, payable the month after it is incurred. The Accounts Payable for power at 1 October was $405.
  • Some of the hirers are quite rough and others are not such good drivers. Repairs and maintenance work out at about $100 a month, payable in the month incurred.
  • Another of Bill’s companies provides the truck and charges a flat fee of $360 a month for motor vehicle expenses including fuel. This is due on the 10th of the month it relates to.
  • General costs (including telephone, stationery, Xero and accountants fees, ISP provider etc.) are estimated at $200a month. It is paidin the month the costs are incurred.
  • The annual insurance premium covering all assets, public liability and loss of business is $3,600 and was paid on 1 July 2018.
  • A license fee of $100 per month is charged by the Metroland City Counicl. The $300 payable for the quarter commencing 1 October 2018 is payable to the Metroland City Council on 1 November 2018.
  • The balance of Cash at Bank on 1 October 2018 is $5,350.

Required

  • On the Data Input Sheet, enter the given data, which has not yet been entered
  • On the Revenue & NCA sheet,complete the revenue, cash collections, and depreciation calculationsfor the quarter 1 October to 31December 2018.
  • 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 2018. Note: Some cash flow rows have been named, but you will need to enter the remaining cash flow names yourself.
  • On the Income Statement Sheet, complete the Projected Income Statement for the quarter ended 31 December 2018. A month by month Income Statement is not required. Note:Some expenses have been identified, but you need to enter the remaining expenses yourself.
  • Bill is considering changing the power supplier, which will reduce the current estimated power bill from $420 to $350 per month. Enter $350 on the Data Sheet andenter the resulting Net Profit and ending Cash Balance in the boxes provided on the Income Statement Sheet. (Remember to change the Powerback to the original $420 before submitting your assignment).

Please download template to fill from here: Template