Simulation Modeling

xunda_gunda

New Member
Joined
Aug 17, 2012
Messages
22
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 WeekFrequencyProbabilityProb. Upper Limit
630.080
740.110.08
860.170.19
9120.330.36
1090.250.69
1110.030.94
1210.030.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)ProbabilityProb. Upper Limit
10.440.00
20.330.44
30.160.77
40.070.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

Please help :)


 
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top