Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Can someone show me how to solve this problem in Excel? A B C D E F 1 Trance Electronics 2 3 Data 4 5

Can someone show me how to solve this problem in Excel?

A B C D E F
1 Trance Electronics
2
3 Data
4
5 Market size 20,000,000
6 Unit (monthly Rx) revenue $ 120.00
7 Unit (monthly Rx) cost $ 50.00
8 Discount rate 8%
9
10 Project costs
11 R&D $ 750,000,000
12 Clinical Trials $ 100,000,000
13 Total Project Costs
14
15 Model
16
17 Year 1 2 3 4 5
18 Market growth factor 4% 4% 4% 4%
19 Market size
20 Market share growth rate 18% 18% 18% 18%
21 Market share 7%
22 Sales
23
24 Annual revenue
25 Annual costs
26 Profit
27 Cumulative net profit
28
29 Net present value

Suppose that the project manager of Trance Electronics has identified the following uncertain variables in the model and the distributions and parameters that describe them, as follows: Market size: normal with mean of 20,000,000 units and standard deviation of 4,000,000 units. R&D costs: uniform between $600,000,000 and $800,000,000. Clinical trial costs: lognormal with mean of $150,000,000 and standard deviation $30,000,000. Annual market growth factor: triangular with minimum = 2%, maximum = 6%, and most likely = 3%. Annual market share growth rate: triangular with minimum = 15%, maximum = 25%, and most likely = 20%. The number of trials per simulation is equal to 10,000 at a Sim. Random Seed of 2. Run the simulation and answer the following questions using the Risk Solver Platform. What is the risk that the net present value over the 5 years will not be positive?

approximately 40%

approximately 77%

approximately 24%

approximately 57%

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Solomon Secret 7 Principles Of Financial Success From King Solomon Historys Wealthiest Man

Authors: Bruce Fleet, Alton Gansky

1st Edition

1585428183, 978-1585428182

More Books

Students also viewed these Finance questions

Question

6. How do marketing systems help avert channel conflict?

Answered: 1 week ago