Finance-AW-Q336

Finance-AW-Q336 Online Services

 

ASSIGNMENT 1501
DATA SHEET		INPUT DATA
	MONTHS COVERED BY:
	Quarter
	Year (Annual)
	OPENING BALANCES 1/10/15
	Cash at Bank
	Accounts Receivable
	Accounts Payable – General Expenses
	SALES DATA
	Estimated Selling Price per Umbrella
	Collection period
	Percentage in Month of Sale
	Percentage in Following Month
	Estimated Monthly Sales Volume (Units)
	October
	November
	December
	Estimated Annual Sales Volume
	PURCHASES DATA
	Estimated Cost Price per Umbrella
	OTHER FORECAST PAYMENTS
	Advertising
	General Expenses
	Owner’s Personal Withdrawals
	Insurance Premium p.a. (Payable 7 October)
	Rent
	Salaries
	Vehicle Expenses
	New Vehicle
	OTHER FORECAST RECEIPTS
	Sale of Old Vehicle
	Loan
	NON-CURRENT ASSETS
	(Straight line method is used for depreciating all non-current assets)
		Cost	Estimated Life	Residual Value
	Vehicle

 
You can read more about our case study assignment help services here.
 

 
How to Approach this Assignment
 
• Please read all instructions before starting the assignment.
• To complete the assignment you need access to a computer and a spreadsheet package. The recommended spreadsheet software is Microsoft Excel and the template provided is an Excel one.
• You should first study the relevant material in the text and make sure you understand the concepts covered.
• Contact a member of the Teaching Team if you have any problems with Microsoft Excel, or with downloading the files from Stream and submitting the assignment on Stream.
 
An Excel Tutorial
 
If you are unfamiliar with Excel or spreadsheets in general, there are excellent free interactive tutorials on the web but the one provided by itself will cover all you need to know. On Stream please check out Computer skills in the LEARNING GUIDES included in the block GUIDES. Most of the tutorial is relevant, but do not worry about the specific functions (formulas) except for SUM.
 
Downloading the Files from Stream
 
For Assignment 2 you need to download from Stream the following file (as well as this Assignment Booklet):
• 115.102 1501 Template A2.xls. This is an excel workbook containing a data input spreadsheet and output spreadsheets with the templates needed to complete this assignment.
 
Procedure to download files
 
a. Log on to Stream for paper 115.102.
b. In the “Activities Which Are Assessed” Block, scroll down to the Assignment 2 heading; click on the “Assignment 2 Files” folder, then on the relevant files to download one at a time. Please note that if you try and modify the excel file without downloading it first, your modifications will NOT be saved.
 
c. Choose Save and navigate to the destination where you want to keep each file. You can download the files to your computer, a disk or a USB drive. You must download the Microsoft Excel template to do the assignment.

 

 
Completing the Assignment
 
Entering Data
 
You will answer the assignment on the templates contained in the Excel file downloaded from Stream. As an Excel program will automatically mark your assignment, care must be taken in how you prepare your answer. If the following instructions are not followed you may lose marks.
 
The data sheet spreadsheet is where you are to type in the input data from the information you are given on pages 6 to 8 of this booklet. The other spreadsheets (output sheets) include templates that will contain the output of financial and numeric data. You should only use formulae in the output spreadsheets as this creates a workbook which is truly flexible. (Refer to page 6: Workbook Spreadsheet Completion.)
 
It is important to realise that the marking program operates on named cells contained within the template. The names of these cells must not be altered or relocated. To avoid this DO NOT use the cut and paste function, or drag and drop cells from one location to another. It is also not advisable to insert or delete rows or columns. Copying, pasting, and deleting cell contents is acceptable.
 
Backups
 
Make certain you keep backups of your work, stored separately from the file you are working on at any time.
 
You may download the template as often as you wish. Once you have started working on the excel file you should make backups.
 
It pays to rename different versions of your work. A simple way to do this is to use the Save as file instruction and add a number or letter to the end of the file name. (This allows you to go back to an earlier version.)
 
However, when you upload the file, make sure that the file you upload follows the naming convention outlined below.

  
Submission of the Assignment (Uploading your file to Stream)
 
To access the submission points you must have completed the Honesty Declaration Quiz. Most of you did for Assignment 1 and there is no need to repeat it.
 
The submission point for Assignment 2 is under “Activities which are Assessed”
 
Use the following naming convention for the files that you are submitting: use
your Last Name Student ID number as the name of the file. [Do not change the file extension or you (and the marker) will not be able to use the file]. For example, if your Last Name is Smith and your Student ID number is 88888888, your Excel file will be smith88888888.xls (or xlsx depending on the version of excel you are using).
 
a. In the “Activities which are Assessed’ section on Stream click on “Assignment 2 Submission.
b. A box with Submission Draft and no files uploaded yet will appear with Upload a file at the bottom of the page. Click on Upload a file.
 
c. Depending on your version of internet software you may be able to drag and drop the file from your computer to stream. If not, click on Add, then Upload the file, then click Browse, click on the file you wish to upload, then on Open, then on Upload this file. You will now see your uploaded file showing on the left side of the page.
d. Click Save changes. The submission is shown as Submission draft.
 
e. When you are satisfied that the correct file has been uploaded, click on Send for Marking under the heading Final submission for assignment marking. The screen will then show the following message: Once the assignment is sent for marking, you will no longer be able to delete or attach file(s). Do you want to continue? You can then choose Cancel or Continue.
 
f. Late submissions will be accepted by the system after the due date for a week only. For the purpose of determining if electronic assignments are handed in on time, STREAM records the submission date and time. See the Assessment Guide for policies re extensions and late submissions. Penalties will be applied to submissions received after the due date for which an extension has not been granted.
 
No assignments will be marked if received after the earlier time of
 
• When feedback and/or suggested solutions are posted on STREAM, and
• When assignments are returned to students.
 
 
Assignment Queries
 
Please feel free to keep in touch with the 115.102 team regarding this assignment, preferably through the forums. That way all students benefit and often we have found that the best way of learning is through discussion with your peers as well as teaching staff.
 
Please note that this assignment is at individual, not group level. Discussion is fine but do not post your answers to the assignment on STREAM through discussion forums, as that will lead to penalties. If you are uncertain, please contact the teaching staff.
 
There are a large number of students in this paper, but that does not mean that you are not each individually very important to us. We value each student and will try to provide appropriate guidance to the best of our ability.
 
Return of marked assignments
 
You will not receive hard copies of your marked Assignment 2 back by post. Instead electronic copies of your marked excel assignments will be available at the same point where you uploaded your assignments. Note that the normal turnaround of assignments is three working weeks. However we will be working to return the assignments much sooner than that.
 
Feedback
 
Individual feedback is included on all excel files in each marked cell. Apart from that, there will be feedback in the form of suggested solutions and overall comments by the (human) marker.
All the best with your assignment!
 
The 115.102 Teaching Team
  
ASSIGNMENT TWO
 
Please note that this assignment is due before Tuesday 19 May 2015,
12 Noon New Zealand Time
It will be marked out of 36 and contributes 15% to your final grade.
 

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)

Additional marks for Flexibility (6 marks)

[Total = 36 Marks]

 

Product Code-Finance-AW-Q336
 
Looking for best Finance-AW-Q336 online,please click here