Hi , i have an assignment related to Operations Management , there a simulation tools to be used either excel solver or lingo?

1 Exercise: Simulation of a two -stage supply chain A two -stage supply chain consists of a manufacturer (M) and a retailer (R). The retailer faces an external demand D t for period t that varies over time partly due to seasonal effects and partly due to random variations. The demand for the periods 1, …, 60 is given in the table below. Manufacturer and retailer make their ordering decisions using independent reorder point systems with the following characteristics: • The order size is the demand forecast for two periods. • The demand forecast is made as follows: Manufacturer and retailer assume that the average demand is constant over the planning horizon and use moving average forecasting with a time window of four periods. That is, the forecast of the demand per period is the average of the demand per period of the last four periods. • The reorder point should be as low as possible in order to minimize inventory holding costs, but backordering is not allowed. Hence a reorder point that leads to negative inventory in any period is infeasible. • The reorder point (whic h of course can be different for manufacturer and retailer) is constant over the planning horizon of sixty periods. • The replenishment lead time (time from ordering until arrival of the material) is two periods for the retailer (delivery time from manufactu rer to retailer), four periods for the manufacturer (manufacturing flow time). • The initial inventory is 500 units for the manufacturer, 200 units for the retailer. There are no open orders at the start of the planning horizon. Assignment 1. Simulate the material flow for the planning horizon of 60 periods using reasonable values for the reorder points. How can you determine reasonable reorder points using standard inventory theory? 2. Try to minimize the reorder points by trial and error (or som e systematic procedure) subject to the constraint that no backordering is allowed. What is the lowest average inventory level you can achieve? 3. Assume that the manufacturer has access to the inventory level of the retailer and to final customer demand (seen by the retailer). Simulate the system assuming that the manufacturer uses an echelon stock system. What is the lowest average inventory level that you can achieve (no backordering)? 2 Table of demand per period Period Demand 1 58.0825 2 68.384 3 46.6683 4 48.7752 5 54.4536 6 68.5907 7 58.0985 8 52.2866 9 58.9457 10 69.1302 11 57.636 12 63.0137 13 50.5007 14 87.0349 15 69.9109 16 44.023 17 63.5624 18 46.4225 19 82.5537 20 59.2444 21 101.991 22 74.5317 23 83.0251 24 107.358 25 78.8001 26 130.592 27 84.1126 28 83.3776 29 87.5259 30 131.531 31 92.8618 32 80.8013 33 117.685 34 90.9919 35 63.9877 36 104.995 37 103.738 38 108.767 39 72.4438 40 68.5156 41 50.8524 42 7.25632 43 82.2543 3 44 67.3858 45 65.4804 46 45.5054 47 49.1457 48 45.0536 49 58.1532 50 48.6963 51 50.4394 52 70.142 53 42.6442 54 49.7853 55 56.6167 56 62.6561 57 36.9467 58 63.6858 59 63.597 60 58.1184