Excel for supply chain management

Excel Assignment #1
This assignment counts towards 15% of your final score. It is graded on a scale from 0 to 10 points, 2 points for each question. For all answers, you must write a description of your work. Often, there is more than one correct answer, and sometimes different or additional assumptions may be accepted. Also, partial credit is given for incorrect answers if enough effort and commentary is shown. Bonus credit will be added to all, so you do not need an immaculate submission for the ten points. Please, do not hesitate to email me questions. Asking is better than guessing. This is an individual assignment. Do not share your work, or points will be deducted at the grader's discretion. Follow Blackboard instructions to submit it.
Q1
For the next eight-week planning period, the daily demand is forecast to be exactly 200 unit with no uncertainty. Only for Q1, there are 80 units left over from the previous period, which ended on a Sunday. The supplier is willing to deliver a constant amount of units every Monday morning, on time. Management mandates a 100% target fill rate. Determine the weekly order quantity that that gives the highest inventory turns and explain in detail the method used to find it.
Q2
For Q2, the leftover inventory from the previous period is zero. Also, the daily demand is no longer constant, but normally distributed with a mean of 150 units and a standard deviation of 50 on weekdays. On weekends, the mean is 330 and the standard deviation is 70. In cell H10, you can find the weekday function to use throughout your worksheet with the appropriate parametes. Since the delivery quantity and times (every Monday morning) have to be the same as in Q1 and the demand is now random, the system may stock out. Only for Q2, when the system stocks out, any unsatisfied demand is backordered and satisfied as soon as new inventory comes in the next Monday. Simulate 500 eight-week runs and compute the average fill rate and average inventory turns. Just for this model, because of the backorders, the fill rate is defined as the number of on-time sales divided by the total demand. Show your work and, most importantly, describe in your own words.
sample mean 150
sample standard deviation 50
random demand 49
Q3
The daily demand and delivery time are the same as in Q2. The lead time is two days, so the orders are placed every Saturday morning. The retailer has the flexibility to change the order quantity as needed over time, so it decides to use the OUL system. Using a 1,000-run simulation, find the order-up-to level that meets a target service probability of 80% while minimizing the inventory turns. Comment.
Q4
The daily demand is uniformly distributed between 150 and 250, every day. The lead time is the same as in Q3. The retailer has the flexibility to order when needed, and it can check inventory regularly. However, it has to order always the same quantity from the supplier, a full truckload of 800 units. So, it decides to adopt the ROP system. Using a 1,000-run simulation, find the minimum reorder point that meets a target service probability of 80%. Comment.
Q5
Simulate only one eight-week-period run of a ROP system with the same constant ROP level found in Q4, and the daily demand distribution of Q2. Supply is ordered if the night before inventory reached or went below the ROP for the first time. Ask if in doubt about what Excel formula to use, IF alone may not suffice. Supply is delivered first thing in the morning two days later. Hence, it could be useful to have two separate columns for supply ordered and supply delivered. The order quantity should be that found in Q1. 500 units are left over from the last day of the previous period, which was a Sunday. Please, compute the fill rate and the inventory turns, and comment your work.