Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please provide step by step on how to solve in excel 3. (15 points) A consumer electronics firm produces a line of portable power banks

image text in transcribed

Please provide step by step on how to solve in excel

3. (15 points) A consumer electronics firm produces a line of portable power banks for mobile phones. The costs for production of this product line are as follows. There is a fixed cost that is normally distributed with a mean of $30,000 and a standard deviation of $5,000; and a per item (i.e., per power bank) production cost that is uniformly distributed with a minimum of $12 and a maximum of $15. The company sells the power bank at a (list) price of $28. The anticipated demand for the product follows the demand curve formula (10,000 250*price), and since they set the price they produce exactly this quantity. (a) Create an @RISK Simulation Model that will allow you to analyze the distribution of the profit associated with this product line. Run this simulation model for 1,000 iterations. What is the expected profit? What is the probability of making more than $5,000? (b) Marcus Boyd, Vice President for Sales and Marketing questions the assumptions of the simulation model you have just built. He contends that the actual selling price is not fixed at the "list price of $28. Rather, it varies because of various promotions between 80% of the list price and the actual list price. Hence he suggests that you use a triangular distribution to model the actual sales price with a minimum value of 80% of list price, most likely value of 90% of list price, and maximum value of actual list price. He also recognizes that actual demand (sales), may fall short of anticipated demand (which is calculated using the demand curve formula and the official list price of $28). Specifically, actual demand will follow a uniform distribution between 90% of anticipated demand and 100% of anticipated demand. So, the production quantity (and therefore production costs) will still be based on the anticipated demand (calculated based on the list price of $28), but revenue will be based on actual demand and the actual sales price. Create an @RISK Simulation Model that incorporates Mr. Boyd's assumptions, and use it to analyze the distribution of the profit associated with this product line. Run this simulation model for 1,000 iterations. What is the expected profit? What is the probability of making more than $5,000?. (c) What is the impact on the expected profit when Mr. Boyd's assumptions are incorpo- rated? What is the impact on the probability of making more than $5,000 when Mr. Boyd's assumptions are incorporated? Please make sure to submit your original @RISK simulation model(s) with the results saved along with your answers. After you run a simulation, when you save your @RISK file it will volunteer to save the @RISK simulation results and graph along with the model. Click yes to accept. 3. (15 points) A consumer electronics firm produces a line of portable power banks for mobile phones. The costs for production of this product line are as follows. There is a fixed cost that is normally distributed with a mean of $30,000 and a standard deviation of $5,000; and a per item (i.e., per power bank) production cost that is uniformly distributed with a minimum of $12 and a maximum of $15. The company sells the power bank at a (list) price of $28. The anticipated demand for the product follows the demand curve formula (10,000 250*price), and since they set the price they produce exactly this quantity. (a) Create an @RISK Simulation Model that will allow you to analyze the distribution of the profit associated with this product line. Run this simulation model for 1,000 iterations. What is the expected profit? What is the probability of making more than $5,000? (b) Marcus Boyd, Vice President for Sales and Marketing questions the assumptions of the simulation model you have just built. He contends that the actual selling price is not fixed at the "list price of $28. Rather, it varies because of various promotions between 80% of the list price and the actual list price. Hence he suggests that you use a triangular distribution to model the actual sales price with a minimum value of 80% of list price, most likely value of 90% of list price, and maximum value of actual list price. He also recognizes that actual demand (sales), may fall short of anticipated demand (which is calculated using the demand curve formula and the official list price of $28). Specifically, actual demand will follow a uniform distribution between 90% of anticipated demand and 100% of anticipated demand. So, the production quantity (and therefore production costs) will still be based on the anticipated demand (calculated based on the list price of $28), but revenue will be based on actual demand and the actual sales price. Create an @RISK Simulation Model that incorporates Mr. Boyd's assumptions, and use it to analyze the distribution of the profit associated with this product line. Run this simulation model for 1,000 iterations. What is the expected profit? What is the probability of making more than $5,000?. (c) What is the impact on the expected profit when Mr. Boyd's assumptions are incorpo- rated? What is the impact on the probability of making more than $5,000 when Mr. Boyd's assumptions are incorporated? Please make sure to submit your original @RISK simulation model(s) with the results saved along with your answers. After you run a simulation, when you save your @RISK file it will volunteer to save the @RISK simulation results and graph along with the model. Click yes to accept

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

Auditing That Matters Case Studies

Authors: Norman David Marks

1st Edition

B089J17FFW, 979-8650160410

More Books

Students also viewed these Accounting questions

Question

Solve the following 1,4 3 2TT 5x- 1+ (15 x) dx 5X

Answered: 1 week ago