please see attachment
Quantitative Methods for Business S3 2018 Assignment 1 1. Please remember to fill out and attach a cover sheet. A copy of the cover sheet can be downloaded from the Course web page. 1. Please make sure that the pages of your submission are in the correct order and that they are stapled securely in the top left-hand corner only. Please make sure that each page shows your name and the page number. 1. Please – no plastic sleeves or any type of folder! 1. Please copy and paste any EXCEL printouts into your assignment 1. All graphs and tables must include your USER ID in the title 1. Show all your work! Just a final answer will get 0 mark. 1. Please use the formulations from the formula sheets. The assignment will be in two parts. · The first part will contain a summary of your answers to the five questions. Each summary will be one or two paragraphs.(20 marks) · Your calculations will be included in an appendix. Your answers in the appendix will follow the format as set out in this question sheet.(80 marks) The report will have a maximum of 4 marks for each question 1. ( 8 marks) An antique dealer charges the following commission (based on the selling price on any items sold on behalf of the client: · 5.0% on the first $50,000 · 3.0% on the next $50,000 · 2.0% on the amount exceeding $100,000 (a) Calculate the commission that will be charged on an item that sell for $80,000. (b) If the dealer wants to receive a commission of $5,200, for how much should the item sell? 2. (12 marks) (15 marks)A partnership agreement states that half of the annual profit be distributed in proportion to each partner’s investment in the partnership, and that the other half be distributed in proportion to the total number of hours that each partner worked in the business during the year. How should the most recent profit of $88,740 be allocated if the amounts invested by Doug, Bob and Sam are $82,500, $45,000, and $22,500, and their hours of work for the year were 427, 1709, and 1424, respectively? (Round final answer to nearest cents) 3. (15 marks) Daniel and Samuel were twin brothers who were each left a will inheritance of $2,000. Daniel invested his money for 3 years, at a simple interest rate of 7.8%. However, Samuel invested his money in an account for 3 years, at an annual interest rate of 8% compounded semi-annually. At the end of 3 years, they both agreed to pool their respective accumulated amounts into one account for another 2 years. The annual interest rate of this account is 5% compounded quarterly. (a) How much accumulated principal did each twin have after 3 years? (Hint: Round final answer to nearest cents) (b) What was the accumulated principal of their pooled money at the end of the 5-year period? (Hint: Round final answer to nearest cents) (c) The twins’ older brother Brian suggested an alternative investment strategy. Brian thinks they should immediately pool their money and invest the pooled amount in an account for 5 years, which pays interest at a rate of 4.8% per annum, compounded monthly. Daniel is arguing that 4.8% p.a. is a much lower interest rate compared to what they are currently offered. However, Samuel recalled their previous studies advocating on compound interest account. Write a short paragraph (5-6 sentences) advising the twin brothers their investment outcomes comparing both options. 4. (20 marks) Jim and Jane would like to add an outdoor entertainment area to their property. It has been determined that they will need $30,000 for this purpose. The couple is considering taking a 3-year personal loan for that amount. Their local bank charges 6% per year compounding quarterly and requires quarterly repayments (a) Prepare an EXCEL spreadsheet that shows the end-of-the quarter balance in Jim and Jane’s loan account over the next 3 years. In addition, use your amortisation schedule to calculate the total interest and the total amount paid over the life of the loan. Attach a copy your spreadsheet into your assignment submission. EXCEL Instructions: Your spreadsheet set-up could look something like this (you will need to use Excel fill in the missing): Refer to Topic 3 in the EXCEL Supplement for instructions on entering formulae in EXCEL. Refer to Topic 4 in the EXCEL Supplement for instructions on how to use financial functions to make annuity calculations. Refer to Car Loan Example (Lecture example 5) in the Week 3 Lecture Slides for formulae of Interest, Principal, Closing Balance and New Opening Balance. (b) A wealthy family friend nicknamed Pacino had offered to help Jim and Jane and has made an alternative offer for the couple to obtain the $30,000 loan. Pacino has proposed the following loan terms over a 12 month period. He claims this is a ‘cheaper and simple’ with everything paid off at the end of the renovation. Pacino will make two equal deposits of $15,000 in their bank account in the first two months of the loan period. However, from months 3-12, he will withdraw $3,200 from Jim and Jane’s bank account each month, until the end of the loan period. Pacino claims he is entitled to $2,000 in interest, which will be paid to him under his proposed conditions. Jim and Jane would like to know the interest rate Pacino is charging them and they would also like to verify the total amount of interest ($2,000) he claims he is entitled to. To answer this question, follow all EXCEL instructions below – including the instructions given in the two diagrams. Also show your calculation for the total interest Jim and Jane paid under this arrangement. EXCEL Instructions: you will need to set up a new Amortisation schedule, like the one shown below, for the 12-month loan. For full marks provide: (i) a copy of your Excel spreadsheet that looks similar to the one below and (ii) your calculation for the total interest to verify Pacino’s $2,000 claim. 1. Set up the amortisation schedule: Set up your amortisation schedule as shown below. Ensure you extend it to show all 12 months. Read the instructions in the boxes for more information. 2. Use Goal Seek to calculate the correct interest rate: Now use GoalSeek (Data Data Tools What-If Analysis in EXCEL 2007, Excel 2010 and EXCEL 2013 or Tools GoalSeek in EXCEL 2003) to find the Interest Rate (APR) that will balance the final entry in the spreadsheet to 0. To run Goal Seek, place any value in the Interest Rate cell (B1). When run Goal Seek, Excel will try different interest rates until the value in E18 equals 0. It doesn’t matter what interest rate you start with, as Excel will tell you the correct answer Provide a screenshot when using Goal Seek, similar to the one provided below. Also provide an ‘after’ shot of the full spreadsheet, showing the correct answers. Remember to show the total interest calculation as well! (c) Jim and Jane is seeking your advice in making their financial decision. Write a short summary (8-10 sentences, double spaced, at least 2cm margins, 12pt Times New Roman font or equivalent) for the couple explaining your findings. 5 (25 marks) Travis is considering the start-up of a removals service in his hometown. He purchased a used second-hand truck for $24,000. He will need to hire a helper to assist with loading and unloading. After conferring with another operator of a similar service in another town of a similar size, Travis feels he can charge $80 per delivery and that each delivery will require an average round-trip driving distance of 30km. He has estimated the following expenses: Truck insurance and registration $2,760 per year Diesel $60 every 300km General truck cleaning $20 every 1000 km Helper’s wages $2000 per month Travis’s wages $2500 per month Tyres $750 every 75,000 km Mobile Cost (on a 24-month contract) $70 per month Other truck repairs and maintenance $500 per 10,000 km Ignore all taxes and depreciation for the following questions. (a) If Travis charges $80 per delivery, what is the break-even in deliveries per month? Find the break-even point algebraically and by using an EXCEL graph. Attach the printout or copy your EXCEL graph into your assignment submission. EXCEL Instructions: Create a column called Number of Deliveries and in that column enter values from 0 to 100 in increments of 10. Then create two more columns, one for Total Cost and another for Total Revenue. Enter appropriate formulae in EXCEL to obtain the total cost and total revenue corresponding to each value in the Number of Deliveries column. Highlight the resulting three sets of numbers and go to the Insert tab (or Chart menu) to obtain an appropriate diagram. Make sure that your graph has been labelled appropriately (i.e. title, axis labels, legend). Refer to Topic 3 in the EXCEL Supplement for further instructions on entering formulae and graphing in EXCEL. (b) Travis is forecasting that he will make 85 deliveries per month. Based on your graph from part (a), will there be a net profit or net loss from the start-up at this volume of deliveries? How do you know? Calculate this net profit or loss amount. (c) The industry standard recommends driver to make 75 deliveries per month as a start-up for a sustainable business in long-run. At what unit price would Travis be just breaking even with the recommended number of deliveries per month? (d) If the average distance per delivery turns out to be 25 km, how does this impact Travis? Assuming the fixed costs and the amount charged per delivery remains as in (a), explain in a short paragraph (4-5 sentences) whether the number of deliveries required to break-even will increase or decrease. Do not re-calculate the break-even quantity, x, for this question however you may quote the break-even formula to aid your explanation.