Assignment -40 | Financial Modeling

Excepted timing : 10 minutes

Sample Data

 

| Year | Revenue | Expenses | Profit |

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

| 2020 | 100000  | 80000    |        |

| 2021 | 120000  | 90000    |        |

| 2022 | 150000  | 100000   |        |

 


Task :
  1. Use FORECAST function to predict Revenue for the next two years based on existing data.
  2. Calculate the Return on Investment (ROI) for each year using formulas.
  3. Create a scenario manager to compare different revenue and expense scenarios.
  4. Use Goal Seek to determine the minimum revenue required to achieve a target profit.
  5. Build a sensitivity analysis table to analyze the impact of varying revenue and expense values on profit.

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

 

Steps to Perform:

  1. Use FORECAST Function to Predict Revenue for Next Two Years:
    • Assuming Year is in column A and Revenue in column B, use:
      • =FORECAST(A4+1, B2:B4, A2:A4) for predicting revenue for the next year (2023)
      • =FORECAST(A4+2, B2:B4, A2:A4) for predicting revenue for the year after (2024) Adjust the ranges and references based on your actual data setup.
  2. Calculate Return on Investment (ROI) for Each Year:
    • ROI can be calculated using the formula:
      • ROI = (Profit / Expenses) * 100 Apply this formula for each year where Profit and Expenses are given.
  3. Create a Scenario Manager to Compare Different Revenue and Expense Scenarios:
    • Go to Data > What-If Analysis > Scenario Manager.
    • Define different scenarios with varying revenue and expense values.
    • Compare the impact on Profit based on these scenarios.
  4. Use Goal Seek to Determine Minimum Revenue Required to Achieve Target Profit:
    • Go to Data > What-If Analysis > Goal Seek.
    • Set the cell to achieve a specific Profit by changing the Revenue cell.
    • Adjust parameters to determine the minimum revenue needed to reach the target profit.
  5. Build a Sensitivity Analysis Table to Analyze Impact of Varying Revenue and Expense Values on Profit:
    • Create a table where you vary Revenue and Expense values.
    • Calculate Profit for each combination of Revenue and Expense values.
    • Analyze how changes in Revenue and Expense affect Profit.