Preparation of a Budget [Total: 36 Marks] Completion of a Workbook of Spreadsheets The aim of this assignment is to produce a workbook of spreadsheets in which any changes in data in the Data Sheet will result in an update of the whole workbook. Therefore you must link the output with the Data Sheet and you should not enter numbers directly into any blue cells in the output areas (you will lose considerable marks for not using formulas). Marks will be awarded for the functionality and flexibility of your workbook. Please ensure you follow all instructions as there will be no compensation for loss of marks due to failure to follow instructions. Ignore interest calculations and GST. Relationship of Assignment 2 to Learning Outcomes (Refer Administration Guide). L.O.2. Analysing transactions and preparing financial statements should help you to understand the accounting process from the recording of transactions through to the summary of information provided in the various general purpose financial statements. L.O.3. Some of the transactions will help you understand the impact of accounting policies such as depreciation and accrual accounting with the latter especially evident when comparing the Income Statement and Cash Flow Statement. L.O.6. As the scenario is set in the future preparing the assignment should also help with understanding the planning process and the use of tools such as excel to look at different scenarios and see their impact on the profit, cash flow and resources of a business. Question: Ken Brolley trading as Brolley Enterprises Brolley Enterprises is a wholesaler selling high quality umbrellas to retail shops around New Zealand. The owner, Ken Brolley, runs the office in Levin while the sales woman, June Doon, travels around making contact with customers and collecting orders. Ken requires your help in preparing a budget for the quarter 1 October 2015 to 31 December 2015 and provides you with the following information. Sales Annual sales run at approximately 15,000 units per year. The monthly estimated sales of umbrellas are October 800; November 1,100; and December 3,200 units. The expected selling price is $45 per umbrella. Of the sales, 20% will be for cash with the balance on credit. Brolley’s terms are that credit sales must be paid for by the 20th of the month following purchase. During the last quarter of the year the firm concentrates on beach umbrellas and in December there is always a high demand for umbrellas as Christmas presents. Purchases Brolley Enterprises buys the umbrellas for $31 each which includes the cost of delivery to Brolley’s customers. Brolley doesn’t keep any inventories. The supplier is paid in the month Brolley buys the umbrellas. Current Liabilities Some Accounts Payable are due on the 20th of the month following purchase. As at 30 September they consisted of Accounts Payable - General expenses of $580. Motor Vehicle The sales person uses a company vehicle but the owner has agreed to sell the existing vehicle at its net book value of $4,000 to a friend at the beginning of October. This sale will take place the same day the company takes delivery of a new vehicle. The cost of the new vehicle will be $33,800 which will be paid to Studie Motors. The new vehicle is expected to have a useful life of 6 years and an estimated residual value of $5,000. The business is taking out a loan on October 1 from the ENDS Bank of $22,000 to help fund this. The first three months of this loan are interest free. Vehicle expenses are estimated at $750 per month payable in the month incurred. Other Expenses Each month Brolley incurs approximately $450 of advertising expenses through Print Media Ltd. Print Media’s terms are that advertising accounts are payable the day an advertisement appears in the media. The insurance premium for the business is $2,400 for the year. The annual premium for 1 October 2015 to 30 September 2016 is due for payment on 7 October 2015. Rent for the Levin Office is $1,100 a month payable on the first day of each month. General Expenses for running the office amount to $600 a month and these are normally paid the month after they are incurred. June Doon is paid $6,000 salary per month on the 20th of each month. Owners’ Equity The owner is not paid a salary but withdraws $10,000 a month from the business bank account. Opening Balances as at 1 October 2015. Cash at Bank $12,000 Accounts Receivable $43,100 Accounts Payable – General expenses $580 (Refer Current Liabilities page 7) Required: Workbook Spreadsheet Completion The aim of this assignment is to produce a workbook of spreadsheets in which any changes in data in the Data Sheet spreadsheet will result in an update of the whole workbook. Therefore you must link the output worksheets with the Data Sheet and you should not enter numbers directly into the output areas. Marks will be awarded for the functionality and flexibility of your Workbook. All entries should be made in blue shaded cells, though not all blue shaded cells require entries. Round all workings to the nearest dollar amount. (i) On the Data Sheet spreadsheet enter the given data which has not yet been entered. (ii) On the Sales & Other Budgets spreadsheet complete the tables given i.e: a. Sales, COGS and Collection budget for the quarter ended 31 December 2015. b. Depreciation table. c. Selling expenses budget for the quarter ended 31 December 2015. d. General and Administrative Budget for the quarter ended 31 December 2015. e. Annual Fixed Costs table. (iii) On the CVP spreadsheet complete the Cost-Volume-Profit analysis based on annual costs. Calculate the number of units and sales in dollars at the break-even point. (iv) On the Cash Flow spreadsheet complete the Projected Cash Flow Statement Activities for the each of the three months October, November and December and the totals for the quarter 1 October to 31 December 2015. (v) On the Income Statement spreadsheet complete the Projected Income Statement for the quarter ended 31 December 2015. N.B. A month by month Income Statement is not required. (Total 180 raw marks) (180/6 = 30 marks)
© 2015 MSA Homework Help All Rights Reserved
Disclaimer: MSA Homework Help provides reference papers to the student and we strongly recommend you not to submit the papers as it is. Please use our solutions as model answer to improve your skills.