Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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 andimage text in transcribed 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 + 1

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

Linking Auditing And Meta Evaluation Enhancing Quality In Applied Research

Authors: Thomas A. Schwandt, Edward S. Halpern

1st Edition

0803929684, 978-0803929685

More Books

Students also viewed these Accounting questions

Question

What is the meaning and definition of E-Business?

Answered: 1 week ago