Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Ben Johnson, CFO of Alter Enterprises is analyzing a new project to sell solar-powered batteries for cell phones. Ben has estimated the following probability distributions

Ben Johnson, CFO of Alter Enterprises is analyzing a new project to sell solar-powered batteries for cell phones. Ben has estimated the following probability distributions for the variables for the project. Probability 10% 30% 40% 20% Industry Demand 80,000,000 95,000,000 108,000,000 124,000,000 Probability 5% 20% 20% 25% 20% 10% Ulrich Market Share 1% 2% 3% 4% 5% 6% Probability 20% 70% 10% Initial Cost $60,000,000 $65,000,000 $72,000,000 Probability 20% 70% 10% Variable Cost/Unit $ 24.00 $ 26.00 $ 29.00 Probability 15% 25% 40% 20% Fixed Cost $ 20,000,000 $ 24,000,000 $ 27,000,000 $ 31,000,000 The unit price depends on the industry demand since greater demand will result in a higher price. Ben determines that the price per unit will be given by the equation: Price = Industry demand/2,000,000 +/-$2 The random +/- 2 term represents an increase or decreases in price according to the following distribution: Probability 45% 55% Price Randomness -$2 $2 The length of the project, tax rate, and the required return are as follows: Projected length (years) 6 Tax rate 40% The required rate of return 15% a. Create a Monte Carlo simulation for the project with 1,000 runs. Calculate the NPV for each run. b. Create bins and frequency distribution for these bins

c. Create a cumulative probability graph using Excels histogram function. d. Repeat a, b and c for IRR calculations. e. In a brief statement discuss the risk involved with this project as you see fit with these 1,000 simulations.

CAN THE EXECELL STEPS BE SHOWN WITH THE FORMULAS AND PICTURES OF THE SHEET AS THAT IS TRIPPING ME UP THANK YOU of the monte carlo i attempted a answer before but the steps did not work the vlookup would give n/a can i please get a detailed step by step help and pictures for this thank you.

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

Step: 3

blur-text-image

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

Practical Financial Management

Authors: William R. Lasher

7th edition

128560721X, 9781133593669, 1133593682, 9781285607214, 978-1133593683

More Books

Students also viewed these Finance questions

Question

What will be the FV of $200 in 5 years at interest rate of 8%?

Answered: 1 week ago