# Simulation Modeling

#### xunda_gunda

##### New Member
I have one excel exercise to solve and want you help. Exercise is from this book: Managerial Decision Modeling (with spreadsheets) - Nagraj Balakrishnan, Barry Render, Ralph M. Stair, jr. - Chapter 10 - Simulation Modeling - Problems 10-33:

Erik Marshall owns and operates one of the largest BMW auto dealership in St. Louis. In the past 36 months his weekly sales of Z3 have ranged from a low of 6 to a high of 12 as reflected in the following table:

 Z3 Sales Per Week Frequency Probability Prob. Upper Limit 6 3 0.08 0 7 4 0.11 0.08 8 6 0.17 0.19 9 12 0.33 0.36 10 9 0.25 0.69 11 1 0.03 0.94 12 1 0.03 0.97

<tbody>
</tbody>

Sales Per Week and Frequency are given. Probability and Prob. Upper Limits have calculated myself.
Erik believes that sales will continue during the next 24 months at about the same rate and that delivery lead times also continue to follow this pace (stated in probability form):

 Delivery Time (Weeks) Probability Prob. Upper Limit 1 0.44 0.00 2 0.33 0.44 3 0.16 0.77 4 0.07 0.93

<tbody>
</tbody>

Delivery Time (Weeks) and Probability are given. Prob. Upper Limits have calculated myself.
Erik's current policy is to order 14 autos at a time (two full truckloads, with 7 autos on each truck) and to place a new order whenever the stock on hand reachs 12 autos. Beginning inventory is 14 autos. Eric establishes the following relevant costs:

1. The carrying cost per Z3 per week is \$ 400
2. the cost of a lost sale averages \$ 7,500
3. the cost placing an order is \$ 1,000

Questions:
A) Simulate Erik's Inventory Policy for the next two years. What is the total weekly cost of this policy? Also, what is the average number of stockouts per week. Use 300 replication for the model
B) Erik wish to evaluate several different ordering quantities - 12,14,16. Based on the total weekly cost what would yoe recommend. Set reorder point = 12 in each case

Please, find attached exercise in excel.

In Book I have found answers like that:
a) \$28,664 per month; 3.67 cars.
b) 20 cars.

I have done some calculations myself according similar case (I will attach this solution too), but I think it's not correct. Can anyone help me? Solve this exercise or even find solved one in Google ))))
Here is my solution: http://www.yourfilelink.com/get.php?fid=872274 (In this file cells with no fills are given, cells with light blue color are made by me)

Here is solved exercise (similar): http://www.yourfilelink.com/get.php?fid=872276

Last edited:

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,816
Messages
5,855,795
Members
431,764
Latest member
scottishbigyin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back