Dumoor Appliance company manufactures and sells several brand of major appliances, among which microwave takes a large portion of the sales. Facing the upcoming sales season, the company wants to estimate their profit situation. The following market scenarios are considered in the estimation: Depending on the component suppliers, the unit production cost for each microwave follows a normal distribution with a mean of $36 and a standard deviation of $3. The firm is undergoing a negotiation process with two competing production-line manufacturer to purchase a new production equipment. There is 50% chance to purchase the equipment from manufacturer A for $10,000, another 50% chance to purchase from Manufacturer B for $12,000. The equipment procurement expense will incur regardless of how many microwaves to produce thus it counts as the fixed cost. 2 The marketing department predicts that the sales quantity will follow a uniform distribution between 1000 and 1500. The marketing team is also in the process of deciding on the sales price, which can be as low as $75, as high as $125, most likely $100 (i.e., following a symmetric triangular distribution) (1) Build a profit model that incorporates the uncertainties in sales price, unit cost, fixed cost, and sales quantity. Note that sales volume must be rounded to the neatest integer. (Hint: Use the Uncertain Profit example as a template, BUT they are not exactly the same! You need to use the corresponding formulas to simulate each variable.) (2) Use Data Table function to generate 100 trials of profit. Copy these 100 random values, and in other blank cells in the same worksheet, paste in the values only. (3) Use these 100 "frozen" values to report the Descriptive Statistics (output option: Summary Statistics), and percentiles. Provide a brief summary on the firm's prospective profit. MUST retain all the formulas in the worksheet. If I cannot verify the formulas used and see static results only, a minimum 50% penalty will be applied. Dumoor Appliance company manufactures and sells several brand of major appliances, among which microwave takes a large portion of the sales. Facing the upcoming sales season, the company wants to estimate their profit situation. The following market scenarios are considered in the estimation: Depending on the component suppliers, the unit production cost for each microwave follows a normal distribution with a mean of $36 and a standard deviation of $3. The firm is undergoing a negotiation process with two competing production-line manufacturer to purchase a new production equipment. There is 50% chance to purchase the equipment from manufacturer A for $10,000, another 50% chance to purchase from Manufacturer B for $12,000. The equipment procurement expense will incur regardless of how many microwaves to produce thus it counts as the fixed cost. 2 The marketing department predicts that the sales quantity will follow a uniform distribution between 1000 and 1500. The marketing team is also in the process of deciding on the sales price, which can be as low as $75, as high as $125, most likely $100 (i.e., following a symmetric triangular distribution) (1) Build a profit model that incorporates the uncertainties in sales price, unit cost, fixed cost, and sales quantity. Note that sales volume must be rounded to the neatest integer. (Hint: Use the Uncertain Profit example as a template, BUT they are not exactly the same! You need to use the corresponding formulas to simulate each variable.) (2) Use Data Table function to generate 100 trials of profit. Copy these 100 random values, and in other blank cells in the same worksheet, paste in the values only. (3) Use these 100 "frozen" values to report the Descriptive Statistics (output option: Summary Statistics), and percentiles. Provide a brief summary on the firm's prospective profit. MUST retain all the formulas in the worksheet. If I cannot verify the formulas used and see static results only, a minimum 50% penalty will be applied
