This assignment follows problem $11$ from Chapter $15.$ Consider the following case: In August $,$ 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 dealer$$s next year models comes from the probability distribution

shown in the Excel file provided in Canvas. Each car sells for $$25,000.$ If demand for next year$$s 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. Use simulation to determine how many cars to order

in August.

Objective: In this exercise you will use a variety of Excel tools to create simulations using discrete and

normal probability distributions. Simulation modeling allows businesses to collect information to make

decisions in the face of uncertainty. Using a simulation model, a business can review the results of

hundreds of possible outcomes to evaluate the expected return and risk, along with best case and worst

case scenarios.

What you need:

$1.$ You must have a computer with a working version of Microsoft Excel.

$2.$ You will use the Excel file $$Sim Assignment$$ posted in Canvas.

Assignment$/$Activity:

Part A: Create a Spreadsheet Model to calculate Profit

$$ The file posted in Canvas contains basic inputs of this problem. Start in the worksheet $$Discrete

demand$.$ This worksheet lists the parameters of the expected discrete demand distribution.

$$ You need to create formulas that calculate the variables that affect profit $$ including the quantities

that will be re$-$ordered and disposed at lower price, the revenue and cost amounts.

$$ Try to change the decision variable $($order quantity$)$ and uncertain variable $($demand$)$ to various

values to make sure your formulas work correctly.

$$ When the model is finished, copy the formulas to the $$Normal Dist Demand$$ worksheet

Part B: Create a Basic Simulation Model and a Summary of the Results

$$ In the $$Discrete demand$$ worksheet, assume your demand comes from the provided discrete

distribution. Create a list of cumulative probability values in the distribution table.

$$ Create a simulation model with $500$ random values. In each replication, demand will be randomly

drawn from the discrete probability distribution provided and profit must be calculated. $($You may

decide to also calculate revenue, cost, or any other variable of interest from the model.$)$

$$ HINT: You should use a one$-$way data table to generate the target variables for each replication of

the simulation.

$$ OPTIONAL: You may $$freeze$$ the random values by copying the random formulas and pasting values

$($similar to class example$)$ to stop the table from re$-$calculating if you prefer.

$$ Report in a summary table the mean, median, standard deviation, max, and min of the values of

profit for your simulation.