Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Simulation Medictronix was happy with your original profit model. However, they have discovered that their distributors negotiate different prices per unit. The average- selling price
Simulation Medictronix was happy with your original profit model. However, they have discovered that their distributors negotiate different prices per unit. The average- selling price per unit is $2,750 with a standard deviation of 3.5%. Using the model you have previously constructed, integrate into your simulation such that Cell B4 is now randomly generated using a normal distribution. In addition, you should integrate that variation of the price into the model...with at least 2,000 simulations (10% of this HW). Parts costs are between $250 and $500. Demand is 25,000 with a standard deviation of 2,500. Administration and advertising cost will be $5.5M. Direct labor probability is as follows: 1,300 10% 1,400 21% 1,500 38% 1.600 21% 1,700 10% You will have to build this model from scratch...you need to incorporate the proper formulas for each requirement. 1. Selling price should be normal distribution 2. Parts cost should be uniform distribution 3. Direct Labor Cost is a probability cost using vlookup 4. Demand is Normal distribution. 5. You need to make sure you have a proper histogram for a chart. Note that you will have to rebuild the data table with the new random selling price/unit. Place the mean and standard deviation of the selling price into cells F7 and F8 respectively. Place at least 4 comments in this Model (10% of this HW) Download the blank from ExPrep.....upload your answer file to ExPrep. Even though ExPrep may give you a 100%, I will be looking to ensure you made proper data tables, 2,000 simulations, a proper histogram, comments, and that you have done the work correctly. Last homework!!! DP Medic Tronix Baseline Parameters Selling Price per Unit Administrative & Advertising Cost Direct Labor Cost Per Unit Parts Cost Per Unit B Demand 9 10 11 Profit Model Selling Price (Normal Distribution) Mean Standard Deviation 12 13 Direct Labor Cost Parts Cost (Uniform Distribution) 14 15 Lower led of Interval 0.0 Upper End of Interval Cost per Unit Probability Lower Bound Upper Bound 16 17 Demand (Normal Distribution) 10 Mean 19 1.00 Standard Deviation 20 21 Simulation Trial Direct Labor Cost Per Unit Parts Cost Per Unit Demand Profit 22 23 24 25 26 27 28 29 30 31 32 M 2 0.8 0.6 0.4 0.2 1.2 1 $8,000,000 $9,000,000 $10,000,000 Profit Summary Statistics Mean Standard Deviation Minimum Profit Maximum Profit P(Profit
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