Question
Kindly show steps in excel format In August of the current year, a car dealer is trying to determine how many cars of the next
Kindly show steps in excel format
In August of the current year, a car dealer is trying to determine how many cars of the next model year to order. Each car ordered in August costs $20,000. The demand for the dealers next year models has the probability distribution shown in the file attached HW_08_S15_12.xlsx. Each car sells for $25,000. If demand for the next years cars exceeds the number of cars ordered in August, the dealer must reorder at a cost of $22,000 per car. Excess cars can be disposed of at $17,000 per car.
1. Use simulation to determine how many cars to order in August. For your optimal order quantity, find a 95% confidence interval for the order quantity of 25
2. Please show your working process instead of the final answer
3. Calculate the profit based on the simulated demand using the two-way data table and 95% confidence interval for the order quantity of 25.
AutoSave HW_08_515.12 (2: - Excel Seach Comelius Comelade 3 Filc Humne Insert Drav Page Layout Forrnila Review View Help: Share Comments K4 D C F G I K L Demand Distribution Demand 20 25 30 35 40 Probability 0.30 0.15 0.15 0.20 0.20 A D C 1 Simulation of a car dealer 2 3 Cost data 1 unit cost of initial order $20.000,00 5 unit cost of reorder $22,000.00 6 unit price $25,000.00 7 disposal value $17.000.00 33 9 10 Order Quantity 25 11 12 13 14 Simulation 15 Demand 16 Fullprice sales revenue 17 Disposal sales revenue 18 Initial order cost 19 Reorder cost 20 Profit 21 22 23 Summary measures from data table below 24 Order quantity 20 25 mean profit 26 Std ev of profit 27 28 95% confidence from mean profit for the optimal order quantity 29 Lower limit 30 Upper limit 31 32 33 Data table for replication of profit (replication along side, order quantity along top) 34 20 Sheet1 + 25 30 10 25 30 10 M + 1 AutoSave HW_08_515.12 (2: - Excel Seach Comelius Comelade 3 Filc Humne Insert Drav Page Layout Forrnila Review View Help: Share Comments K4 D C F G I K L Demand Distribution Demand 20 25 30 35 40 Probability 0.30 0.15 0.15 0.20 0.20 A D C 1 Simulation of a car dealer 2 3 Cost data 1 unit cost of initial order $20.000,00 5 unit cost of reorder $22,000.00 6 unit price $25,000.00 7 disposal value $17.000.00 33 9 10 Order Quantity 25 11 12 13 14 Simulation 15 Demand 16 Fullprice sales revenue 17 Disposal sales revenue 18 Initial order cost 19 Reorder cost 20 Profit 21 22 23 Summary measures from data table below 24 Order quantity 20 25 mean profit 26 Std ev of profit 27 28 95% confidence from mean profit for the optimal order quantity 29 Lower limit 30 Upper limit 31 32 33 Data table for replication of profit (replication along side, order quantity along top) 34 20 Sheet1 + 25 30 10 25 30 10 M + 1Step 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