Sample Data
| Scenario | Revenue (Base Case) | Growth Rate (%) | Variable Cost (%) | Fixed Cost ($) |
|----------|---------------------|-----------------|-------------------|----------------|
| Scenario 1 | 100000 | 5 | 60 | 20000 |
| Scenario 2 | 120000 | 7 | 55 | 25000 |
To perform the tasks outlined in Assignment using Excel, follow these step-by-step instructions:
Step To Solve:
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.
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:
· Scenario 2:
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:
To visualize the distribution of projected revenues from Monte Carlo simulation, create a line chart:
This chart will show how projected revenues vary across different scenarios.
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.