Reference no: EM132405386
Quantitative Methods for Business - Eynesbury College
Question 1 - Interest & loan
Sue will need $120,000 to refurbish her house at the corner of a main road into a cake shop in 5 years. She has a saving account which earn 3.47% p.a. compounding quarterly and she is able to deposit $800 into that account at the end of each month for 5 years.
a) Will Sue have enough money after 5 years? If not, how much is in short? Show all calculations.
b) Even if Sue may not have enough money, she will consider taking a 9-year loan for the required amount because she is keen to have her own business. She talked to the lenders from her local Bank and Credit Union regarding loan options.
(i) The local bank charges 4.35% per year compounding biannually and requires biannually repayments. Calculate the minimum biannually repayment for both principal and interest that Sue would have to make on this loan. If you wish, use EXCEL to calculate the biannually repayment.
EXCEL Instructions: Refer to Topic 4 in the EXCEL booklet for instructions on how to use financial functions to make annuity calculations. You need to show formulas used, do not simply type in values. Therefore, you need to show the formulas in your spreadsheet in this form B4=B3*$C$1.
(ii) Use EXCEL to set up an Amortisation Schedule for the loan. Include your completed EXCEL amortisation schedule. The schedule should include the amount of principal and the amount of interest that comprise each payment until the loan is paid off at the end of its term.
EXCEL Instructions: Refer to your amortisation notes from Week 3 and the corresponding EXCEL spreadsheet available on the course website, which you can modify to help you create the amortisation schedule for this question. Be sure to add your initials to all column names.
(iii) Use your amortisation schedule from part (ii) to calculate the total interest and the total amount paid over the life of the loan.
c) The Credit Union charges Sue 3.95 % p.a. compounding monthly and requires monthly repayments. Also, The Credit Union offers a two-year interest-only option with the 9-year loan. This means that for the first two- years, every month Sue would pay only interest on the amount borrowed. Loan repayments consisting of both interest and principal would then commence in year three and continue for 7 years.
(i) Calculate the monthly repayment that Sue would have to make starting in year three if she wants to pay off the loan in 9 years. If you wish, use EXCEL to calculate the monthly repayment. EXCEL Instructions: Refer to your own notes taken in class and Topic 4 in the EXCEL booklet for instructions on how to use financial functions to make annuity calculations.
(ii) Calculate the total interest paid on the loan with the two-year interest-only option.
d) Which option should Sue take? As part of your response you must explain why the option you select is the better of the two alternatives.
Question 2 - Superannuation
Tom White has just started work at 18 years of age and intends to retire at 55. He will be saving $150 per month during his working life. He invests his money in a superannuation fund paying 8.5% p.a. compounding half- yearly.
a) How much will he have in this fund when he retires? Provide a clear explanation of why you think this is a future/present value annunity question.
b) On retirement, Tom rolls this money over into an allocated pension paying 6.4% p.a. compounding fortnightly. Calculate how much per fortnight he could withdraw, if the pension were to last 15 years.
c) What is the outstandarding balance after 10 years?
d) If an emergency withdrawal of an extra $25,000 was needed at the end of the 5th year, what effect would that have on how long the annuity would last?
EXCEL Instructions: Topic 4 in the EXCEL booklet for instructions on how to use financial functions to make annuity calculations.
Question 3 - Break-Even Analysis
Cottage Industries Ltd makes baskets. The fixed costs of operating the workshop for a month total $1,500. Each basket requires materials which cost $6. Each basket takes two hours to make and the business pays the basket- makers $9 an hour. The basketmakers are all on contracts that specify that if they do not work, for any reason, they are not paid. The baskets are sold to a wholesaler for $30 each. In addition, there is a fixed handling fee to all the wholesaler of $55.
a) Break-even graph
(i) What is the break-even number of orders?
(ii) What is the corresponding break-even revenue?
(iii) Draw a Break-even graph using EXCEL. Present appropriate calculations plus a copy of your EXCEL graph. Indicate on your graph the region corresponding to profits and the region corresponding to losses.
EXCEL Instructions to produce a break-even graph: Create a column called Number of orders and in that column enter values from 0 to 400 in increments of 20. 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 units column. Highlight the resulting three sets of numbers and go to Insert → Charts → Scatter to obtain a graph. Make sure that your graph has been labelled appropriately (i.e. title, axis labels, legend) and that your chart title includes your student ID. Refer to Topic 3 in the EXCEL Booklet for further instructions on entering formulae and graphing in EXCEL.
Cottage Industries Ltd expects to sell 500 baskets a month. The business has the opportunity to rent a basket- making machine. Doing so would increase the total fixed costs of operating the workshop for a month to
$6,000. Using the machine would reduce the labour time to one hour per basket. The basket-makers would still be paid $9 an hour.
b) How much profit would the business make each month from selling baskets:
(i) Assuming the bussuness doest not rent the basket-making machine?
(ii) Assuming that it is rented?
c) What is the break-even point if the machine is rented?
d) What do you notice about the figures you calculated?
Question 4 - What is the best solution?
A small firm makes three similar animal feed mixes. Product A requires 5 hours of labour, 10 kg of raw material, 4 minutes of mixing using a mix-machine; product B requires 6 hours of labour, 8 kg of raw material, 2 minutes of mixing; and product C requires 4 hours of labour, 5 kg of raw material, 5 minutes of mixing. The firm has 210 hours available during the next period for labour, 200 kg of raw material, and 170 minutes for mixing. Product A contributes $28.40 per unit to profit, product B contributes $25.3 per unit, and product C contributes $27.4 per unit. Determine the optimal mix of products in terms of maximizing contribution to profits for the period.
Requirements:
a) Define the decision variables;
b) Show the business objective and objective function;
c) List all constraint equations;
d) Create an EXCEL working sheet for the linear programming model;
e) Provide both Answer and Sentivity reports;
f) Find the range of optimality for the profit coefficient of each variable using the reoprts generated.
Homework questions for annuity
Question 1
You are investing $1,000 at the end of each period for four periods, at 6% per period, compound interest. How much money will your savings amount to at the end of this time? Compute this answer by the following techniques:
(a) accumulate each individual payment and then add them all together
(b) use the formula Sn = R[(1+ i)n - 1]/i
(c) use the formula Sn = RSn-i
(d) use the Excel function for FV
Question 2
You have saved an amount of $2,429.74 by the end of 5 years, at compound interest of 2% per quarter. How much money did you put into your savings account each quarter?
Question 3
You are considering three alternative investments. Investment X offers a return of 1% per month compound interest. Investment Y offers a payoff of $989.75 after 4 years in return for savings deposits of $100 made at the end of each half-year. Investment Z requires you to deposit $629.00 now and also pays back $989.75 after four years.
Required: Suggest a suitable way of choosing between these investments and rank them from best to worst option for you.
Attachment:- Quantitative Methods for Business.rar