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:
<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):
<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
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
Please help
Last edited:
