need help
( Page | 1 )
Question 1:
Complete Problem 14.1 on page 290 of the course text (solution is on page 523). Next, work the problem again using the following variables: project yield annual net cash inflows are $10,500 for the next five years; interest rate of 16.5%, and the initial investment of $33,000. Calculate the net present value of the cash flows and the IRR for the project using the Excel spreadsheet formula. Explain the concept of Net Present Value.
Page 290
Question 14.1
The Whitton Co. has an opportunity to buy a computer now for £18,000 that will yield annual net cash inflows of £10,000 for the next three years, after which its resale value would be zero. Whitton’s cost of capital is 16%
Calculate the net present value of the cash flows for the computer using spreadsheet formula.
What is the IRR?
Solution for 14.1 (Page 523)
Formula for present value= +NPV (16%, C3:E3). The cash flows are entered in columns C (Year 1) to E (Year 3). See Table S 14.1.
Table S14.1
Col A Col B Col C Col D Col E
Year 0 Year 1 Year 2 Year 3
Cash flows 10,000 10,000 10,000
Present value 22,459
Initial investment -18,000
NPV £ 4,459
To calculate IRR using the spreadsheet function, a negative figure (the initial cash investment) must be part of the range of values.
Formula for IRR = + IRR (B3:E3).
Year 0 Year 1 Year 2 Year 3
Cash flows - 18,000 10,000 10,000 10,000
IBR 31%
Question 2:
Complete Problem 16.4 on page 329-330 of the course text (solution is on page 533-534 - Table S16.4). Next work the problem again using the following variables: The selling price is expected to be £350 per ton for the first three months and £360 per ton thereafter. Variable costs per ton are predicted as £100 in the first quarter, £120 in the second quarter, and £130 in the last two quarters; and salary and wages do not increase in the last two quarters. The rest of the assumptions are as listed on problem 16.4. What is the cumulative cash flow at the end of Quarter 4? Be prepared to paste your worksheet for this problem into the OAES
Page 329-330
Problem 16.4
Griffin Metals Co. has provided the following data:
Anticipated volumes (assumes production equals sales each quarter):
Quarter 1 100,000 tonnes
Quarter 2 110,000 tonnes
Quarter 3 105,000 tonnes
Quarter 4 120,000 tonnes
The selling price is expected to be £300 per tonne for the first six months and £310 per tonne thereafter. Variable costs per tonne are predicted as £120 in the first quarter, £125 in the second and third quarter, and £130 in the fourth quarter.
Fixed costs (in £ ‘000 per quarter) are estimated as follows:
Salaries and wages £3,000 for the first half year, increasing by 10 for the second half year
Maintenance £1,500
Council rates £400
Insurance £120
Electricity £1,000
Depreciation £5,400
Other costs £2,500 in the first and fourth quarters, £1,800 in the second and third quarters
Interest £600
Capital expenditure £6,500 in the first quarter, £2,000 in the second quarter, £1,000 in the third quarter
and £9,000 in the fourth quarter.
Dividend payment £10,000 in the third quarter
Debt repayments £1,000 in the first quarter, £5,000 in the second quarter, £4,000 in the third quarter
and £3,000 in the fourth quarter.
Griffin has asked you to produce a profit budget and a cash forecast for the year (in four quarters) using the above data.
Solution for 16.4 (Pages 533-534)
Budget Qtr 1 Qtr 2 Qtr 3 Qtr 4 Total
Volume (tonne) 100,000 110,000 105,000 120,000
Selling price (per tonne) 300 300 310 310
Variable (per tonne) 120 125 125 130
Budget in £’000
Sales and production 30,000 33,000 32,550 37,200 132,750
Variable costs (raw materials) 12,000 13,750 13,125 15,600 54,475
Contribution 18,000 19,250 19,425 21,600 78,275
Total fixed costs:
Salaries and wages 3,000 3,000 3,300 3,300 12,600
Maintenance 1,500 1,500 1,500 1,500 6,000
Council rates 400 400 400 400 1,600
Insurance 120 120 120 120 480
Electricity 1,000 1,000 1,000 1,000 4,000
Budget Qtr 1 Qtr 2 Qtr 3 Qtr 4 Total
Depreciation 5,400 5,400 5,400 5,400 21,600
Other costs 2,500 1,800 1,800 2,500 8,600
Total fixed costs 13,920 13,220 13,520 14,220 54,880
Operating profit 4,080 6,030 5,905 7,380 23,395
Interest expense 600 600 600 600 2,400
Profit after interest 3,480 5,430 5,305 6,780 20,995
Cash forecast
Profit after interest 3,480 5,430 5,305 6,780 20,995
Add back depreciation 5,400 5,400 5,400 5,400 21,600
8,880 10,830 10,705 12,180 42,595
Less capital expenditure 6,500 2,000 1,000 9,000 18,595
Less dividend 10,000 10,000
Less debt repayments 1,000 5,000 4,000 3,000 13,000
Net cash flow 1,380 3,830 -4,295 180 1,095
Cumulative cash flow 1,380 5,210 915 1,095