Question
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
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started