NUMERACY AND ANALYSIS FOR BUSINESS

This hand in date means you should avoid late hand in as you have time to attempt the work and seek guidance at the discretion of your tutor – he will not do the work for you.

Section 1

The finance manager of Grates plc is preparing a report for the next board meeting on the proposed purchase of a new machinery. The machine is a Turbo-jet printer Mark 4, a state0-of-the- art laser jet printer for business use. This project was sanctioned by the management of Grates plc prior to the take-over of the company by Smathes Holdings plc and it appears that little in the way of financial analysis of the project was carried out before the decision was taken. The machine will cost £500,000 and the money will have to be paid immediately in its entirety.

Net cash sales of £200,000 per year will be expected from this machine for the next three years, with an increase to £300,000 in the fourth year due to a contract that has been finalised by a loyal customer in that year. The Turbo-jet printer Mark 4 will be replaced in the fifth year by the more advanced Mark 5 model. No decision has been made as to where the Mark 5 model will be made and final details are yet to be given to Grates plc. 

At the end of the fourth year, the machine (Turbo-jet printer Mark 4) will have a disposable value of £100,000. Grates plc requires its financial department to evaluate new projects using its own overall required rate of return, which is 10%. Taxation may be ignored.

Required:

  1. Using an excel spreadsheet formulate equations (explain which cells you used in your calculations) that will enable you to calculate the Payback Period (to I decimal place) .(10 Marks)
  2. Using an Excel spreadsheet formulate equations that will enable you to work out the ARR (to 2 decimal places) in percentages. (10 Marks)
  3. Using Excel formulate equations that will give you the discount factors and subsequently calculate the NPV. (10 marks)
  4. Using Excel calculate the IRR, prepare an IRR schedule and graph. Advice Billingsgate on the IRR that you derive. (10 Marks)
  5. Based on your calculations above advice the company on what decisions should be made if the cut off period for payback is 3 years, the bench-mark for ARR is 50% and if they should proceed with the project based on the NPV calculations. (10 Marks)

Section 2

Mr Peter is working for a company called Regents Limited. He has been working for this company for 10 years. He has been depositing his savings as follows. The Bank offered 5% interest on the deposit for the first five years. The bank then offered 7.34% interest for the next 5 years. 

Year

Deposit amount

1

£5,000.00 

2

£5,000.00

3

£5,000.00

4

£5,000.00

5

£5,000.00

6

£6,800.00

7

£6,800.00

8

£6,800.00

9

£6,800.00

10

£6,800.00

Required:

  1. Using Excel functions calculate the total amount of interest received by Mr Peter during the 10 years duration using simple interest. In addition, calculate how much Mr Peter would receive in total including Principle and Interest after the 10 years. (20 Marks)

  1. If Mr Peter’s savings in total was £59,000 after 10 years, then how long would it take to receive double the amount of his saving at 6% interest using compound interest formula? Use an excel function to work this out. (10 Marks)

  1. If Mr Peter’s wants to receive double the amount of his total savings which is 

£1 18,000, what would be the interest on savings in 10 Years’ time using compound interest rate formula? Use an Excel function to work this out.  (10 Marks)

Section 3

Anna has invested £400,000 at 9% interest for 4 years. What is the final balance of this investment?

Assuming:

  1. Interest compounded annually
  2. Interest compounded semi-annually
  3. Interest compounded quarterly

Required:

  1. Create a table using Excel to calculate the balances above. (5 Marks)
  2. Construct a graph to show the balances above (you can either construct a horizontal or vertical bar graph. (5 Marks)
Order from us and get better grades. We are the service you have been looking for.