USA: +1(669)289-8683 
Sign in
Assignment Question
Total Marks 60. (This assignment contributes 25% to your final grade)
Question: Shirley Knot trading as Trump Trailers. (60 Marks)
Background
Shirley Knot established Trump Trailers in the Hawkes Bay a few years ago to sell trailers made in Auckland. The business is located on the outskirts of town on a spare piece of land with a porta-cabin and the rental of the site is $650 per month. Trump Trailers stocks just one type of trailer, the Tipex. Shirley has a good understanding of the business but she is not sure about the accounting side such as cash flow, budgeting and building up the wealth of the owner. She gives you a copy of the Balance Sheet as at 30 September 2013 (refer below) and asks if you will be able to help her.
Sales
With the arrival of spring sales of trailers usually pick up so Shirley estimates the sales as follows: October 50 trailers, November 55 and December 40. The current selling price is $1,750. Shirley estimates that 30% of cash will be collected in the month of sale and 70% in the month after.
Purchases and Inventories
The business obtains the trailers from a manufacturer in Auckland whose price of $1,400 includes delivery to the Trump Trailer yard in the Hawkes Bay. Payment is due 20th of the month after purchase. The business policy is to have ten trailers in stock at the end of each month, which is the number held at the end of September 2013.
Non-Current Assets
The only non-current asset on the balance sheet as at 30 September 2013 is the computer equipment which was bought a year ago. It has an expected useful life of three years (in total) and an estimated residual value of zero.
On 1 October 2013 Shirley intends to take delivery of a Ute (utility vehicle) for the business which will cost $35,000. Shirley intends to deposit $10,000 from her personal funds into the business bank account to help with the ute purchase for which payment is due on the 20th of October.
The business uses the straight line method of depreciation for all its assets.
It is estimated that the Ute will have a useful life of five years at the end of which it will be sold for $12,000.
Other
· Advertising in the local paper costs the business $250 per month, payable in the month it is incurred.
· General costs such as telephone, utilities, stationery work out at $500 a month and are paid for on the 20th of month following purchase.
· Shirley takes $6,000 a month out of the business bank account for personal use.
· An annual insurance premium of $2,400 has been agreed with the brokers for the year October to 30 September 2014. The premium is payable in full on 12 October 2013.
· The rent of $650 per month for the business property is payable on the first day of each month.
· Vehicle expenses for the new Ute are estimated at $850 per month, payable in the month incurred.
Opening Balance Sheet
Trump Trailers
Balance Sheet
As at 30 September 2013
Current Assets
Cash at Bank
3,000
Accounts Receivable
35,196
Inventories
14,000
52,196
Non Current Assets
Computer Equipment
6,000
Acc depreciation Comp Equip
2,000
4,000
Total Assets
56,196
Current Liabilities
Accounts Payable – Inventories
49,000
Accounts Payable – General Expenses
450
49,450
Total Liabilities
49,450
Owner’s Equity
6,746
Total Liabilities and Owners Equity
56,196
Part A: Workbook Spreadsheet Completion [Total: 25 marks]
The aim of this assignment is to produce a workbook of spreadsheets in which any changes in financial and numeric 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 the blue shaded output areas on the other worksheets. All entries should be made in either the pink or the blue shaded cells, though not all shaded cells require entries. Some entries have already been made for you. Round all workings to the nearest dollar amount. Six marks will be awarded for the functionality and flexibility of your spreadsheet (refer to Part B (ii)).
Required:
(i) On the Data Input Sheet enter the given data which has not yet been entered.
(ii) On the Sales & Other Budgets sheet in the templates provided complete the Sales, Purchases and the Annual Depreciation Budget for the quarter 1 October to 31December 2013.
(iii) On the Cash Flow Sheet complete the Projected Cash Flow Statement for the each of the three months October, November and December and the totals for the quarter 1 October to 31 December 2013.
(iv) On the Income Statement Sheet complete the Projected Income Statement for the quarter ended 31 December 2013. NB A month by month Income Statement is Not required.
Ignore interest calculations and tax implications. Do not attempt to convert Drawings into Salary.
Part B: Report to Shirley Knot [Total: 35 marks]
Required:
With reference to the budget financial reports generated in Part A, write a business report to Shirley Knot (refer to the attached guidelines for report writing). Use suitable headings for items (i) to (iii) below in the discussion section of the report.
(Report formatting 10 marks)
(i) Explain to Shirley what the Projected Cash Flow Statement and Income Statement reveal about the business. Offer any advice you see as relevant. (10 marks)
(ii) Explain to Shirley what sensitivity analysis involves and how a spreadsheet facilitates this. State in dollars the impact on the projected quarterly profit and the closing cash balance of:
a) changing the selling price to $1,995 and
b) changing suppliers to one who has quoted $1,300 as a cost price.
Answer a) and b) separately (i.e. do not combine the 2 scenarios). (9 marks)
(iii) Choosing
either
(ii) a) or (ii) b), discuss the change and the factors which should be taken into account before making the change. The factors could include both financial and non-financial items. (6 marks)
Complete Part B in a Word document file. (No template given.)
Answers should be well reasoned, comprehensive and include data from the financial reports. To answer your assignment you may need to look beyond the set readings and text.
Your answer to Part B should range from 500 to 1,000 words. The marker reserves the right to decline to mark answers over 1,200 words.
Please acknowledge sources used outside of the text book and course materials.
References
are not included in the word count.
Guidelines for Writing Reports
For some of the questions of the Assignment, you are required to submit a short report to the owner of the business. There are many options with regard to layout for such reports, and some guidelines are outlined below. However, any reasonable layout will be suitable. Remember that these reports are necessarily short, and thus need to be focussed, concise and summarised. Bullet points in relevant areas will be acceptable.
The following extract may be of assistance:
‘Writing reports gives practice applying theoretical concepts to a real-life context. If you are planning a career in an organisational context, it is likely that at various stages in your career writing reports will be required. Learning to write a report, therefore, is not just a method by which your lecturers evaluate you; it is also part of professional development, and a way of learning business principles, integrating them into your critical mind.
Organisations call for reports when they have difficult decisions to make. They therefore require the author to exhibit investigative skills, judgment and the ability to write persuasively. Writing persuasively for a report means that you need to appear to be objective. You are required to produce proof or evidence to support your ideas. It is not enough to recommend a course of action; you need to explain why this is the best solution, what its short and long term results will be, and explain the reasons.
Remember above all that a report is a practical project. A report assumes that someone has a problem and they want guidance on how to deal with the problem. If you are writing for a particular person, keep that person in mind. Focus on their needs and recommend a solution that can be implemented. Be specific: avoid generalisations…’
(Emerson, L. (2000). Writing Guidelines for Business Students. p. 32-33. Palmerston North, Dunmore Press.)
The following is an example of a basic report structure:
Title Page
Introduction
1. identify subject, context and specific problem.
2. define specific objectives
Discussion/Analysis
1. sub-divide into logical units
2. use headings and arrange into logical sequence
3. present evidence to support conclusions
4. focus on objectives (the specific problem)
Conclusions/Recommendations
1. relate to objectives
2. follow from facts
References
Note: you may use some or all of these features in Part B of your assignment.
Checklist
Make
sure you fill this in AFTER you have finished the assignment and BEFORE you submit it
I am aware of the Code of Student Conduct on the Massey University web site, clause 2 (f), where in it states [Students shall]
“act with honesty and integrity in submitting material or imparting information to the
university”. Assessment & Examination Regulations clause (6) clarifies further that “dishonesty” is a breach of the Code of Student Conduct and will be dealt with accordingly
Data
| ASSIGNMENT 1 | 3 | ||||||||||||||||
| INPUT DATA | |||||||||||||||||
| MONTHS COVERED BY: | |||||||||||||||||
| Quarter | months | ||||||||||||||||
| Year (annual) | 12 | ||||||||||||||||
| OPENING BALANCES 1/10/13 | |||||||||||||||||
| Cash at Bank | |||||||||||||||||
| Accounts Receivable | |||||||||||||||||
| Inventories | |||||||||||||||||
| Accounts Payable | |||||||||||||||||
| General | |||||||||||||||||
| SALES DATA | |||||||||||||||||
| Estimated Selling Price per trailer | |||||||||||||||||
| Collection period | |||||||||||||||||
| Percentage in month of sale | |||||||||||||||||
| Percentage in following month | |||||||||||||||||
| Estimated Monthly Sales Volume | |||||||||||||||||
| October | units | ||||||||||||||||
| November | |||||||||||||||||
| December | |||||||||||||||||
| INVENTORY DATA | |||||||||||||||||
| Estimated | Cost | ||||||||||||||||
| Required inventory level | |||||||||||||||||
| OTHER FORECAST PAYMENTS | |||||||||||||||||
| Advertising | per month | ||||||||||||||||
| General (Telephone, Utilities, Stationery, ISP etc) | |||||||||||||||||
| Owner’s personal withdrawals | |||||||||||||||||
| Insurance | per year | ||||||||||||||||
| Rent | |||||||||||||||||
| Vehicle expenses | |||||||||||||||||
| Ute | |||||||||||||||||
| NON-CURRENT ASSETS | |||||||||||||||||
| Straight line method is used for all non-current assets | Expected | Residual | |||||||||||||||
| $ | Life | Value $ | |||||||||||||||
| Computer Equipment | |||||||||||||||||
| FORECAST RECEIPTS | |||||||||||||||||
| From owner 1/10/13 |
Sales & other
| ASSIGNMENT 1301 | ||||
| SALES COLLECTION BUDGET & DEPRECIATION | ||||
| OUTPUT AREA | ||||
| SALES, COGS and COLLECTION BUDGET | ||||
| Sales | Revenue | |||
| Cost of Goods Sold | ||||
| Cash Collection | ||||
| Month of sale | ||||
| Month after | ||||
| Total | ||||
| PURCHASES BUDGET | Units | |||
| Budgeted total unit sales | ||||
| Add desired ending inventory in units | ||||
| Total required | ||||
| Less beginning inventory in units | ||||
| Budgeted purchases in units | ||||
| Cost of purchases in dollars | ||||
| Cash payments for purchases | ||||
| ANNUAL DEPRECIATION | ||||
| Total Depreciation |
Cash Flow
| ASSIGNMENT 1301 | ||
| CASH FLOW | ||
| Trump Trailers | ||
| Cash Flow Forecast | ||
| Cash Flow from Operating Activities | ||
| Inflow | ||
| Total Inflow | ||
| Outflow | ||
| Payments to Inventory Suppliers | ||
| Total outflow | ||
| Net Cash Inflow (outflow) from operations | ||
| Cash Flow Investing Activities | ||
| Net cash inflow (outflow) | ||
| Cash Flow Financing Activities | ||
| Total Outflow | ||
| Net cash inflow (outflow) from Financing | ||
| Beginning cash balance | ||
| Ending cash balance |
Income Statement
| INCOME STATEMENT |
| Projected Income Statement |
| Quarter ending 31 December 2013 |
| Less Cost of Goods Sold |
| Gross Profit |
| Less Expenses |
| Total expenses |
| Net Profit |
I declare that this is an original assignment and is entirely my own work.
Where I have made use of the ideas of other writers, I have acknowledged (referenced) the
source in every instance.
Where I have used any diagrams or visuals produced by others, I have acknowledged
(referenced) the source in every instance.
This assignment has been prepared exclusively by me for this paper and has not been and
will not be submitted as assessed work in any other academic paper.
I am aware of the Code of Student Conduct on the Massey University web site
http://calendar.massey.ac.nz/statutes/dr.htm , clause 2 (f), wherein it states [Students shall]
“act with honesty and integrity in submitting material or imparting information to the
university”. Assessment & Examination Regulations clause (6) clarifies further that
“dishonesty” is a breach of the Code of Student Conduct and will be dealt with accordingly.
I have run the checker macro (pressed Ctrl+k) and it accepts my spreadsheet as valid
I have run the checker macro (pressed Ctrl+k) and it accepts my spreadsheet as valid
