• 01 DEC 17
    • 0

    In your csv that you write, WHAT IS THE SUM OF ALL THE Q LEVELS. That is, 473 + 157 + 871 + ….. PUT ANSWER IN An Excel Tab (Hint: The sum of the digits within this number is 17.  For instance, if the answer were 1054, the sum of the digits would be 1 + 5 + 4 = 10).

    Part I – Reorder amount to achieve a given fill rate

    Fill rate has a slightly different meaning from that of cycle service level.  Cycle service level is the fraction of order cycles we are in stock.  But if we stock out during an order cycle, we have no idea how much demand was unfilled.  Thus, in comes fill rate.  Fill rate tells us what fraction of the demand we serve.  It’s usually the number that is more useful to business, however, it’s harder to deal with and do useful calculations with.

    Let’s say you want to achieve a 95% fill rate.  How much stock should you order?  There is actually no equation to tell us this.  However, if you know how much stock you have, then you can calculate the fill rate (if demand is normal).  That is, we can convert stock level (Q) to fill rate (F), but we cannot convert a fill rate to a stock level.  Here is a snippet from a text book that tells us how to calculate fill rate given a stock level:

    What we will be doing is writing code in R to calculate the desired stock level for each of the 100 SKUs we played with in class.  (Unlike in class, we will not use a period review policy.  Instead, we will use an R,Q policy.  When the inventory drops to R, we will order Q).  Assume we already know R, and just want to find Q for each SKU in order to achieve a fill rate target (so this is different from the Tesla problem where we wanted to achieve a cycle service level).

    You will need to do the following:

    1. Create a function that calculates the fill rate based on an order amount Q, mu, and sigma. To do this, go back to the lecture on Fill Rate.  We wrote R code to calculate a desired stock level to achieve a certain fill rate.  But this was not yet in a function.  Take this the next step and embed this in a function.  You will have the first function “calcFillRate.Norm” which is already written for you.  Below this (AFTER the end of this function), you will write a function (maybe called “calcQGivenFillRate.Norm”) which takes inputs of (target_fill_rate, mu_input, sigma_input), and spits back a level Q.  This function will “call” the function “calcFillRate.Norm”.
    2. Use this new function “calcQGivenFillRate.Norm” to calculate the order amount Q for each of the 100 SKUs in the file “SkuDemandData_Softlines.csv”. The class videos should help. A few things to note:
      1. The standard deviation and mean demand are OVER THE LEAD TIME. Thus, the “mu” in the fill rate function is “mu_day * lead_days” while the “sigma” is “stdev_day * sqrt(lead_days)”.
      2. This function “calcQGivenFillRate.Norm” will take the place of the “calcSafetyStock” function that we did in class in the file that read in the 100 SKUs in “SkuDemandData_Softlines.csv” and spit back the basestock levels.
    3. The overall fill rate will be 0.95 (set by the company)
    4. Note that the first 3 SKUs will have Q levels of: 473, 157, and 871. For the first SKU, the “mu” input for the calcQGivenFillRate.Norm function is 458.57 and the “sigma” input is 73.28.  (You’ll see this leads to Q of 473 when fill rate is 95%)



    1. In your csv that you write, WHAT IS THE SUM OF ALL THE Q LEVELS. That is, 473 + 157 + 871 + ….. PUT ANSWER IN An Excel Tab (Hint: The sum of the digits within this number is 17.  For instance, if the answer were 1054, the sum of the digits would be 1 + 5 + 4 = 10).
    2. Paste your code into an Excel tab using COURIER FONT as below into a text box.
    3. Also, paste your code in action (the output) into a Text Box in Excel using COURIER FONT, like below.


    Your general structure will be this:

    1. Define function that spits back fill rate given a Q.
    2. Define a function that spits back Q given a target fill rate (This will “call” previous function)
    3. Read in csv.
    4. Loop through 100 SKUs and calculate Q for each one of them.
    5. Write out this csv.


    Also, at some point I say “Turn this logic into a function”.  What do I mean by this?

    Look at this code:

    x <- 10

    x * x

    This is just a line of code that squares the value of x.  It’s the logic that squares a number.  Now, I want to embed this into a function.  Here’s how I would do that:

    squareMe <- function(x) {

    y <- x * x



    Now I can call this function:




    The same way I put “x*x” inside a function is how I want you to put the logic that “adds inventory until the desired target fill rate is achieved” inside a new function.

    Part II – Fill rate in R continued with simulation

    Here, we will continue our exploration of approximation methods to find a stock level which achieves a desired fill rate.

    Just above, we created a function that returned an integer stock level which achieved a target fill rate.  This included a while loop inside of it.

    There also exists a numerical approximation.  It is from a textbook (Silver, Pyke, and Peterson), and is also explained nicely here in this other professor’s lecture notes http://business.unr.edu/faculty/ronlembke/463/fillrate.pdf .  Instead of using a while loop, the numerical approximation returns the approximate stock level Q to achieve a desired fill rate when demand is assumed to be normally distributed.  It is not perfect (as we said before, no equation exists to calculate this exactly).  But it’s close (at least that’s what some professor said once….).

    The purpose of this homework problem is to determine the quality of this approximation.

    I have embedded this numerical approximation into a function.  Go to the R code “FillRateApproximation_Part_I.R”.  Except, oopsies, I’ve made a bunch of mistakes.  I’ve also left out some parts of the formula that you need to go to the above pdf to obtain.  Your task is to fix all the typos and errors, and fill in the missing information.  Make it so that when we select all and run the entire set of code (including the rm(list = ls()) line), it prints back the Q level for demand mean = 800, standard deviation = 200, and fill rate = 0.98.

    Once you get it working, what is the Q level for demand mean = 800, standard deviation = 200, and fill rate = 0.98?  (Hint: The sum of the first three numbers after the decimal point should be 10).

    List all the fixes you made.

    Paste your code in an Excel Tab

    How good is the above approximation?  Try the following.  For mean demand 1000 and standard deviation 200, find the difference between the old way in Part I (adding a unit until the fill rate is achieved) and the new way (the numerical approximation).  The new approximation may be a fraction, and that’s okay for now.  Fill in the following table (we’ve put some numbers here so that you can validate your model)(all the cells that are blank, you’ll need to fill in):

    Based on this table, what would you say to your boss about the quality of the numerical approximation?  The Part I takes a long time to calculate.  This part (the approximation) is very fast to calculate.  Is the quality of the Part I solution worth the extra computation it takes?  Write a short[1] email to your boss’ boss’ boss with your recommendation and the pluses and minuses.  Can you think of any other assumptions that both algorithms make that might not be true in the real world?

    Part III – Forecasting in R (Similar to NY Births example)

    You have data for a call center over 30 days.  It’s in a csv called “CallCenterVolume.csv”.  Each element is an observation of the number of calls during that hour (so there are 24 * 30 = 720 observations).  There may be trend and seasonality.  (Here, think about seasonality abstractly.  It’s not like winter/summer, but it means ‘pattern of variation’.  So we have intra-day seasonality, where 2pm every day may have the same level of volume vs. 10pm.)

    Analyze this data and forecast the next 7 days of call volume.


    • When you read the data in, use a statement like this: call_center_data <- csv(‘callCenterVolume.csv’)[,1]  .  I have the [,1] at the end to turn the data.frame into a vector that can be used for the time series analysis.
    • When you turn the data into a time series, think about what the frequency is (it’s not 12). How many datapoints are in a period? (what is a period here?)
    • When you turn your data into a time series, think about whether you need a “start = c(….” input. You may not.

    Paste the decomposition plot in an Excel tab (the one with 4 subplots of data, trend, seasonality, random).

    Paste the plot of the forecast into the future in an Excel Tab

    Write it to a csv and upload your csv to an Excel Tab

    Upload your R code to an Excel tab.  MAKE IT COURIER FONT (Like below).

    Now, create a plot (In R or in Excel) that shows the “seasonal” affect over the 24 hours of a single day (not over all 720 observations).  This should be a plot with “adjustment factor” across y axis and “hour of day” across x axis and have 24 points.

    Paste the plot to an Excel Tab

    Interpret the output

    Part IV – Lego extension

    Go back to the lego example.  Assume now that each piece of furniture also requires rivets.  A table requires 5 rivets and a chair requires 2 rivets.  Rivets costs $1 each.  You have 11 rivets.  Adjust the model to account for the rivets and determine the optimal number of tables and chairs to build.

    • How many constraints are there? (Including the non-negativity ones)
    • What would be the dimension of the polygon?
    • What is the optimal number of chairs and tables to produce? The profit?
    • Upload your model to an excel tab.

    Part V – Easy rider toys

    Easy Rider Toys (ERT) manufactures and markets toy cars.  This year ERT is planning to introduce several new product lines and wants to sell off existing inventories.  These inventories consist of toy cars and toy trucks, and can be sold in two different sets.

    • The Racer Set consists of seven cars and two trucks and 3 spare tires, and is sold for $34.99.
    • The Construction Set consists of three cars and twelve trucks and 6 spare tires, and is sold for $43.99.
    • The NASCAR set consists of 1 car, 1 truck, 8 spare tires, and sells for $5.99.

    Currently there are 10,000 cars and 12,000 trucks and 10,000 spare tires in inventory.

    ERT must manufacture at least 700 construction sets due to a contractual obligation with Zamazon.com.

    ERT figures that it cannot sell more than 1,000 units of the Racer Set and the Construction Set, and can’t sell more than 300 of the NASCAR set.

    Build the optimization model from scratch. (Don’t make any integer constraints)


    1. This has similar elements to Legos
    2. If you build 10 sets of each, you should make a profit of $850 (although this far from validates your model, it is a start)

    Turn in your.  Format your model using the cell styles we talked about in.

    Answer the following questions:

    1. What are the three parts of the optimization problem:
    2. The objective
    3. The decision variables
    4. The constraints
    5. What dimension is the corresponding polygon?
    6. A binding constraint is one in which the inequality is satisfied by equality (i.e., 5<=5 is a binding constraint, where as 3 <= 6 is not a binding constraint). Binding constraints are those that might “matter” and affect the shape of our polygon and as such might have an effect on our optimal solution.  For instance, in the legos example, labor was not a binding constraint, but small blocks and large blocks available were both binding constraints, b/c chaning each by even a little affected our optimal solution.  Which of the constraints are binding?
    7. Of what value (in dollars) would it be to do the following:
      1. Build 10 more cars?
      2. Build 10 more trucks?
      3. Reduce the tires in the NASCAR pack from 8 to 7?
      4. Work with your marketing department to increase demand for the Racers set by 10%?
      5. Work with your marketing department to increase demand for the NASCAR set by 10%?
      6. Renegotiate your contract with Zamazon.com?

    Upload your model

    [1] Short means write in clear language, but stick to main points.  Don’t spend 5 paragraphs talking about what you did.  Imagine Tim Cook needs to know which system to go with, and he has 20 seconds to read your email.  What would you put into it?  Try timing the 20 seconds to make sure you don’t make it too long.  SHORT AND CLEAR.



    Leave a reply →