Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

| Year | Revenue | Expenses | Profit |

|------|---------|----------|--------|

| 2020 | 100000  | 80000    |        |

| 2021 | 120000  | 60000    |        |

| 2022 | 250000  | 100000   |        |

| 2023 | 100000  | 60000    |        |

| 2023 | 220000  | 90000    |        |

| 2024 | 250000  | 100000   |        |

Task :

1. Calculate the profit for each year using a formula (Revenue - Expenses).
2. Use IF function to categorize each year as "Profitable" if Profit > 0, otherwise "Not Profitable".
3. Calculate the compound annual growth rate (CAGR) for Revenue over the three years.
4. Use NPV function to calculate the net present value of future cash flows.
5. Create a waterfall chart to visualize the yearly revenue, expenses, and profit.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

1. Calculate Profit for Each Year:
   • Use the formula: Profit = Revenue - Expenses
   • Apply this formula for each year (2020, 2021, 2022) using the given revenue and expenses data.
2. Categorize Profitability Using IF Function:
   • Use the IF function to categorize each year as "Profitable" if Profit > 0, otherwise "Not Profitable".
   •  (assuming Profit is in column D).
3. Calculate Compound Annual Growth Rate (CAGR) for Revenue:
   • CAGR formula: Calculate CAGR for Revenue over the three years (2020 to 2022).
4. Calculate Net Present Value (NPV) of Future Cash Flows:
   • Use the NPV function to calculate the net present value of future cash flows.
   • Example: =NPV(rate, cashflow_range)
   • where rate is the discount rate and cashflow_range is the range of future cash flows.
5. Create Waterfall Chart to Visualize Financial Performance:
   • Use Excel to create a waterfall chart that shows yearly revenue, expenses, and resulting profit.
   • Ensure to format the chart to clearly distinguish between revenue, expenses, and profit for each year.

Detailed Steps:

• Step 1: Calculate Profit for Each Year
  • In Excel, enter the formula =B2 - C2 in cell D2 to calculate profit for 2020.
  • Copy the formula for cells D3 and D4 for years 2021 and 2022 respectively.
• Step 2: Categorize Profitability Using IF Function
  • In a new column (e.g., E), use the formula =IF(D2 > 0, "Profitable", "Not Profitable") to categorize each year based on the calculated profit.
• Step 3: Calculate CAGR for Revenue
  • Determine the CAGR for revenue using the formula. For example, if revenue values are in cells B2, B3, and B4, use =((B4/B2)^(1/3))-1 to calculate the CAGR over the three years.
• Step 4: Calculate NPV of Future Cash Flows
  • Assuming future cash flows are in cells F2 to Fn, use the NPV function like =NPV(discount_rate, F2:Fn) to calculate the net present value.
• Step 5: Create Waterfall Chart
  • Select the data (including revenue, expenses, and profit for each year).
  • Insert a waterfall chart from the Insert menu in Excel.
  • Customize the chart to show revenue as positive values, expenses as negative values, and profit as the resulting bars, visually representing the financial performance over the years.