In August of the current year, a car dealer istrying to evaluate the monthly profitability. Below is information the car dealer observed based on historical data: • The ordering cost of carsfollows a triangular distribution, with a minimum cost of $5,000, a maximum cost of $17,000, and most likely to be $11,000. • The monthly demand for the dealer’s cars is normally distributed with mean of 50 and standard deviation of 10 (use an excel function to round the sales quantity to the nearest integer). • Depending on the model and year, the dealer sets the selling price of each car following a uniform distribution between $8,000 and $30,000. Assume that there is no association between each car’s ordering cost and selling price. • In addition, the dealer expends $100,000 every month on the employees’ salary. (2) Build a profit model for this car dealer that incorporates the uncertainties in selling price, ordering cost, and sales quantity. (Hint: Use the Uncertain Profit example as a template, BUT there are not exactly the same! You need to use the corresponding formulas to simulate each variable.) (3) Use Data Table function to generate 100 trials of profit. Copy these 100 random values, and in another blank cell in the same worksheet, paste in the values only. (4) Use these 100 “frozen” values to report the Descriptive Statistics (output option: Summary Statistics), and percentiles. Provide a brief summary on the dealer’s prospective profit.
In August of the current year, a car dealer istrying to evaluate the monthly profitability. Below is
information the car dealer observed based on historical data:
• The ordering cost of carsfollows a triangular distribution, with a minimum cost of $5,000,
a maximum cost of $17,000, and most likely to be $11,000.
• The monthly demand for the dealer’s cars is
standard deviation of 10 (use an excel function to round the sales quantity to the nearest
integer).
• Depending on the model and year, the dealer sets the selling price of each car following
a uniform distribution between $8,000 and $30,000. Assume that there is no association
between each car’s ordering cost and selling price.
• In addition, the dealer expends $100,000 every month on the employees’ salary.
(2) Build a profit model for this car dealer that incorporates the uncertainties in selling price,
ordering cost, and sales quantity. (Hint: Use the Uncertain Profit example as a template,
BUT there are not exactly the same! You need to use the corresponding formulas to
simulate each variable.)
(3) Use Data Table function to generate 100 trials of profit. Copy these 100 random values, and
in another blank cell in the same worksheet, paste in the values only.
(4) Use these 100 “frozen” values to report the Descriptive Statistics (output option: Summary
Statistics), and percentiles. Provide a brief summary on the dealer’s prospective profit.
Step by step
Solved in 3 steps
