Create an Excel formula to calculate Working Capital

Use the "Q1-9" worksheet to answer questions 1 to 9.

1.            Create an Excel formula to calculate Working Capital. (1 mark)

Working Capital = Current Assets − Current Liabilities. 

2.            Create an Excel formula to calculate the Current Ratio. (1 mark)

Current Ratio =    Current Assets  .Current Liabilities

3.            Create an Excel formula to calculate Quick Ratio. (2 marks)

Quick Ratio = Cash +Short Term Investments + Accounts Receivable

Current Liabilities

4.            Create an Excel formula to calculate 𝑥, 𝑥 = y ∗ (1 + r)n. 

5.            Create an Excel formula to calculate 𝑥, 𝑥 = ( 1+ r)n. (2 marks)

6.            Create an Excel formula to calculate IRR, IRR = (𝐹𝑉 𝑃𝑉 1/𝑛)− 1. (2 marks)

7.            If the Internal Rate of Return (IRR) is expected to be 20%, what will the number of years (n) be? Use the Solver function on Q6 to determine the new number of years (n). (2 marks)

Hint: We have created the formula in Q6 to calculate IRR, use that formula with the solver function to calculate a new number of years (n).

1)    Open the solver function. Data > Analysis > Solver

2)    In Set Objective box: type E11 because we want to find that IRR to equal to a specific value (we set the Value in the next step)

3)    In To box: we choose Value of, then enter 20%. This is the Value we want E11 (IRR=20%)

4)    In Changing Variable Cells box, type E8. We want the Excel Solver function to adjust this cell, then the Value on the E11 cell will equal 20%.

a ∗ b + c

8.            Create an Excel formula to calculate 𝑥, 𝑥 = a∗( 1−d + e)2. (3 marks)