please put answer on tellow box with clear excel function or/and explanation
Zeiss Selling Price $ 13 Q3. (40 points) Zeiss is one of the world's leading makers of camera lenses. For a new line of lenses for smartphones, the profit is computed as: Answers (2 points) (a) ) Fixed Cost (Discrete distribution) Lower Upper Probability Random No. Random No. Cost 0.20 $150,000 0.35 $170,000 0.25 $200,000 0.20 $250,000 Demand (Normal Distribution) Mean 60,000 Standard Dev 5,000 Profit = (selling price )"demand - (variable cost/unit) demand - fixed cost. (2 points) (a) Variable Cost (Discrete distribution) Lower Upper Probability Random No. Random No Cost 0.10 $6 0.30 $7 0.30 $8 0.30 $10 The demand follows a normal distribution with mean = 60,000 and standard deviation = 5,000. As shown, both the variable cost/unit and the fixed cost follows a discrete distribution- . (a) Fill in the random number interval blanks (lower and upper random numbers) in the variable cost and fixed cost tables, (b) Simulate 150 profits in the given table and find the median profit. (c) What is the value at risk at 2%? (d) What is the probability of making between 190,000 and $250,000 in profits? (e) What is the probability of losing more than $25,000? Simulation Trials (4 ponits) (4 ponits) (4 ponits) Demand (4 ponits) Profit Trial Var cost/unit Fixed Cost Answer: (5 points) (b) Median profit (5 points) (c) VaR-2% (5 points) (d) Prob. of $190k-$250k profit (5 points) (e) Prob. of losing more than 25k 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Random Number 0.541 0.465 0.277 0.010 0.988 0.913 0.014 0.670 0.679 0.981 0.301 0.647 0.902 0.432 0.893 0.569 0.559 0.477 0.016 0.672 0.871 0.753 0.881 Random Number 0.519 0.220 0.538 0.472 0.860 0.929 0.262 0.353 0.161 0.702 0.641 0.804 0.037 0.119 0.172 0.425 0.063 0.060 0.014 0.220 0.331 0.926 0.117 Random Number 0.342 0.590 0.228 0.248 0.608 0.623 0.462 0.394 0.659 0.776 0.271 0.819 0.834 0.490 0.899 0.208 0.042 0.528 0.240 0.581 0.507 0.725 0.938 Zeiss Selling Price $ 13 Q3. (40 points) Zeiss is one of the world's leading makers of camera lenses. For a new line of lenses for smartphones, the profit is computed as: Answers (2 points) (a) ) Fixed Cost (Discrete distribution) Lower Upper Probability Random No. Random No. Cost 0.20 $150,000 0.35 $170,000 0.25 $200,000 0.20 $250,000 Demand (Normal Distribution) Mean 60,000 Standard Dev 5,000 Profit = (selling price )"demand - (variable cost/unit) demand - fixed cost. (2 points) (a) Variable Cost (Discrete distribution) Lower Upper Probability Random No. Random No Cost 0.10 $6 0.30 $7 0.30 $8 0.30 $10 The demand follows a normal distribution with mean = 60,000 and standard deviation = 5,000. As shown, both the variable cost/unit and the fixed cost follows a discrete distribution- . (a) Fill in the random number interval blanks (lower and upper random numbers) in the variable cost and fixed cost tables, (b) Simulate 150 profits in the given table and find the median profit. (c) What is the value at risk at 2%? (d) What is the probability of making between 190,000 and $250,000 in profits? (e) What is the probability of losing more than $25,000? Simulation Trials (4 ponits) (4 ponits) (4 ponits) Demand (4 ponits) Profit Trial Var cost/unit Fixed Cost Answer: (5 points) (b) Median profit (5 points) (c) VaR-2% (5 points) (d) Prob. of $190k-$250k profit (5 points) (e) Prob. of losing more than 25k 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Random Number 0.541 0.465 0.277 0.010 0.988 0.913 0.014 0.670 0.679 0.981 0.301 0.647 0.902 0.432 0.893 0.569 0.559 0.477 0.016 0.672 0.871 0.753 0.881 Random Number 0.519 0.220 0.538 0.472 0.860 0.929 0.262 0.353 0.161 0.702 0.641 0.804 0.037 0.119 0.172 0.425 0.063 0.060 0.014 0.220 0.331 0.926 0.117 Random Number 0.342 0.590 0.228 0.248 0.608 0.623 0.462 0.394 0.659 0.776 0.271 0.819 0.834 0.490 0.899 0.208 0.042 0.528 0.240 0.581 0.507 0.725 0.938
