A new car has a sticker price (the full retail asking price) of $22,000 However, there are several customer chosen options (automatic transmission, upgraded speakers, fog lights, auto dimming rear view mirror, moonroof) that can add between $0 and $2000 to the selling price of the car depending on which options the customer chooses assume that the total cost of all options is distributed uniformly and continuously on that range Also, many buyers bargain down the price of the car assume that this bargain down amount is distributed uniformly and continuously between $0 and $3000, and is independent of the options The selling price of the car is $22,000 plus the total of the options cost, minus the bargain down amount Create an Excel spreadsheet simulation that will compute the actual selling price for the next 100 cars sold, using only the capabilities that come built in with Excel, i e , dont use RISK or any other third party add ins Find the sample mean, standard deviation, minimum, and maximum of your 100 selling prices Form a 95 confidence interval for the expected selling price of infinitely many future sales (not just the next 100), and do this within Excel ( check out the built in Excel function and be careful to understand its input parameters) Also, create a histogram of your 100 selling prices, either by mimicking what was done in Model 3 5, or by using the Data Analysis ToolPak that comes with Excel (you may need to install it via File Options Add Ins, then in the Manage window at the bottom select Excel Add ins, then the Go button just to the right, then check the box for Analysis ToolPak, then OK) and its Histogram capability (you may want to explore Excel help for this) Do everything within Excel, i e , no side hand calculator calculations or printed statistical tables, etc In a second sheet (tab) of your same Excel file, repeat part a) above, except now use RISK or another Excel add in for static spreadsheet simulation, including its built in variate generation functions rather than your own formulas to generate the uniform random variates needed also, dont compute the confidence interval on expected selling price Instead of 100 cars, do 10,000 cars to get both a better estimate of the expected selling price, and a smoother histogram (use the histograms that come with RISK) Again, do everything within Excel (other than using RISK)

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Mar 17, 2024

A new car has a sticker price (the full retail asking price) of $22,000. However, there are several customer-chosen options (automatic transmission, upgraded speakers, fog

A new car has a sticker price (the full retail asking price) of $22,000. However, there are several customer-chosen options (automatic transmission, upgraded speakers, fog lights, auto-dimming rear-view mirror, moonroof) that can add between $0 and $2000 to the selling price of the car depending on which options the customer chooses; assume that the total cost of all options is distributed uniformly and continuously on that range. Also, many buyers bargain down the price of the car; assume that this bargain-down amount is distributed uniformly and continuously between $0 and $3000, and is independent of the options. The selling price of the car is $22,000 plus the total of the options cost, minus the bargain-down amount.
- Create an Excel spreadsheet simulation that will compute the actual selling price for the next 100 cars sold, using only the capabilities that come built-in with Excel, i.e., don’t use @RISK or any other third-party add-ins. Find the sample mean, standard deviation, minimum, and maximum of your 100 selling prices. Form a 95% confidence interval for the expected selling price of infinitely many future sales (not just the next 100), and do this within Excel (: check out the built-in Excel function and be careful to understand its input parameters). Also, create a histogram of your 100 selling prices, either by mimicking what was done in Model 3-5, or by using the Data Analysis ToolPak that comes with Excel (you may need to install it via File $>$ Options $>$ Add-Ins, then in the Manage window at the bottom select Excel Add-ins, then the Go button just to the right, then check the box for Analysis ToolPak, then OK) and its Histogram capability (you may want to explore Excel help for this). Do everything within Excel, i.e., no side hand/calculator calculations or printed statistical tables, etc.
- In a second sheet (tab) of your same Excel file, repeat part a) above, except now use @RISK or another Excel add-in for static spreadsheet simulation, including its built-in variate-generation functions rather than your own formulas to generate the uniform random variates needed; also, don’t compute the confidence interval on expected selling price. Instead of 100 cars, do 10,000 cars to get both a better estimate of the expected selling price, and a smoother histogram (use the histograms that come with @RISK). Again, do everything within Excel (other than using @RISK).