Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

Sample Data

| Scenario | Revenue (Base Case) | Growth Rate (%) | Variable Cost (%) | Fixed Cost ($) |

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

| Scenario 1 | 100000             | 5               | 60                | 20000          |

| Scenario 2 | 120000             | 7               | 55                | 25000          |

 

Task :

1. Use RAND function to generate random values for Growth Rate and Variable Cost for Monte Carlo simulation.
2. Calculate the projected revenue for each scenario using formulas (Revenue = Base Revenue * (1 + Growth Rate/100)).
3. Use NPV function to calculate the net present value for each scenario considering Fixed Cost.
4. Create a line chart to visualize the distribution of projected revenues from Monte Carlo simulation.
5. Use COUNTIF function to count the number of scenarios where NPV is positive.

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

 

Step To Solve:

1. Generating Random Values for Growth Rate and Variable Cost

To simulate Monte Carlo scenarios, use Excel's RAND function to generate random values for Growth Rate and Variable Cost:

·         Growth Rate: Generate a random number between 0% and 10% (adjust based on your expected range):

=RAND() * 10

·         Variable Cost (%): Generate a random number between 50% and 70% (adjust based on your expected range):

=RAND() * 20 + 50

Place these formulas in cells where you want to simulate these values for each scenario.

2. Calculate Projected Revenue for Each Scenario

To calculate projected revenue for each scenario based on the generated Growth Rate:

·         Assume the base revenue is in cell B2, Growth Rate in C2, and Variable Cost % in D2.

excel

Copy code

=B2 * (1 + C2/100)

Apply this formula for each scenario where:

·         Scenario 1:

• Base Revenue: $100,000
• Growth Rate: 5%
• Projected Revenue = $100,000 * (1 + 5/100) = $105,000

·         Scenario 2:

• Base Revenue: $120,000
• Growth Rate: 7%
• Projected Revenue = $120,000 * (1 + 7/100) = $128,400

3. Calculate NPV for Each Scenario Considering Fixed Cost

To calculate the Net Present Value (NPV) for each scenario considering Fixed Cost:

·         Assume Fixed Cost is in E2.

=NPV(Discount Rate, -E2, (Projected Revenue Year 1 / (1 + Discount Rate)^1), (Projected Revenue Year 2 / (1 + Discount Rate)^2), ...)

For example:

• Scenario 1:
  • Fixed Cost: $20,000
  • Projected Revenue Year 1: $105,000
  • Projected Revenue Year 2: $105,000 * (1 + Growth Rate/100)
  • NPV = NPV(Discount Rate, -E2, -E2 + (Projected Revenue Year 1 / (1 + Discount Rate)^1), -E2 + (Projected Revenue Year 2 / (1 + Discount Rate)^2))

4. Create a Line Chart to Visualize Distribution of Projected Revenues

To visualize the distribution of projected revenues from Monte Carlo simulation, create a line chart:

• Set the X-axis to scenarios (e.g., Scenario 1, Scenario 2).
• Set the Y-axis to Projected Revenues calculated in step 2.

This chart will show how projected revenues vary across different scenarios.

5. Use COUNTIF Function to Count Scenarios with Positive NPV

To count the number of scenarios where NPV is positive:

·         Assuming NPV values are calculated in F2 (for Scenario 1 and Scenario 2):

=COUNTIF(F2:F3, ">0")

This formula will count how many scenarios have a positive NPV, indicating profitable outcomes.