Question

from your own experience, describe three spreadsheet functions that you have used and found them useful. In your answers:

• Describe how each function is designed or formulated
• Use Excel spreadsheet to provide examples for each function. Show your demonstration in both normal and formula views with row and column headings.
• Explain why they are useful to you.

Answer 1

1. The Net present value function -

The NPV function in Excel provides the net present value of an investment.

It is formulated in such a manner that it uses the applicable discount rate and yearly cash flows during the project life to give the net present value of the investment.

Example -

For example, a project requires an outflow of $ 15,000 at the end of year 1 and it shall provide an inflow of $ 4,000, $5,000, $2,000, $10,000, $11,000 at the end of year 2,3,4,5,6 respectively. So, its net present value is calculated in the following image -

Normal view -

Formula view -

Usefulness -

It is quite useful is it tells us whether the investment is profitable or not. It also takes into consideration the time value of money. Net Present Value function basically tells us the profit or loss expected from the project adjusted for time value of money.

2. The Internal Rate of Return function -

The IRR function in Excel provides the Internal Rate of Return of a project.

It is formulated in such a manner that it uses the applicable annual cash flows during the project life and a guessed percentage (it can be any random percent that we expect the IRR to be) to give us the Internal Rate of Return of the project.

Example -

For example, a project requires an outflow of $ 10,000 at the end of year 1 and it shall provide an inflow of $2,000, $2,800, $3,500, $4,500 at the end of year 2,3,4,5 respectively. Then, its Internal Rate of Return is calculated in the following image -

Normal view -

Formula view -

C2 f year year cash flow 10000 2000 2800 3500 4500 4 Internal rate of return IRR(D3:07,10%) 10 12

Usefulness -

The IRR function provides a rate at the present value of cash inflows and outflows shall be equal. i,e. it also helps in deciding whether to undertake a project or not. If the entity's required return from the project is less than the calculated IRR, then the project should be undertaken.

3. The IF function -

The IF function applies a logical test on a set of cells and provides a value based on whether the test result is true or false.

It uses the logical test data, the data in the cells on which the logic is applied, the value to be displayed is the test result is true and the value to be displayed is the test result is false.

Example -

For example, we have 8 employees and we need to decide which employee is eligible for promotion and the eligibility criteria for promotion is selling a minimum of 150 units.

This problem can be solved by IF function in the image given below -

Normal view -

Formula view -

C3 x : employee code employee code units sold by them #11 #12 #13 #14 #15 #16 #17 #18 Promotion IF(D4>150,"YES", "NO") IF(D5 150,"YES", "NO") IF(D6 150,"YES", "NO") IF(D7 150, "YES" "NO") IF(D8>150,"YES", "NO") IF(D9丬50,"YES","NO") IF(D10 150,"YES", "NO") IF(D11 150,"YES", "NO" 4 190 140 110 260 10 160 12

Usefulness -

By using IF function, we were able to find which employees are eligible for promotion. We were not required to check manually for each employee whether that employee is eligible for promotion or not. So, IF function is useful is the data in case the data has multiple entries.