SCM566 – Demand Fulfillment Analytics
Problem Set 6
For each question, please put your answers in a tab in an excel spreadsheet. If you need to, submit it in a few excel spreadsheets, but to the extent possible, try to put all of your answers in as few Excel workbooks as possible with different questions on different tabs. Make it as easy to grade and find the answers as possible. Maybe even color code your excel sheet tabs to make it easier to see which ones correspond to which parts.
Part I (Put in an EXCEL File and Explain the answer in a text box)
A crossdock is a link in the transportation chain whereby we delay assigning products to regions until the last minute. For instance, if you order products from Singapore to the USA (Texas, Pennsylvania, and Seattle), you could either have direct orders from Singapore to each of these three locations, or you could ship it to Los Angeles, and then in Los Angeles decide how much of these items to ship where (at a later point than if you had to decide in Singapore).
There are currently 152 Veterans Affairs (VA) Medical Centers in the United States, offering post-war adjustment, counseling, and outreach services for veterans and their families. Aiyana is running the supply chain operations for the VA medical center (VA-MC) network, and it has the following structure:
Each VA-MC must hold its own safety stock according to: . (We are assuming continuous review, hence no R). Here, z=1.96 corresponds to a cycle service level of 97.5%, is the standard deviation of demand at one VA-MC, and L is the total lead time from Singapore to the VA-MCs in Texas, Pennsylvania, and Seattle. As a whole, if there are N VA-MCs, they must hold safety stock in total. (A) In this model, Aiyana must decide 10 weeks in advance (at the time the orders leave the distributor) how much to order for each VA-MC.
Aiyana is thinking of going to a design where she cross docks the inventory part way through the path from the distributor to the VA-MCs; the cross dock would be in Los Angeles. On the day inventory arrives into the cross-dock from the distributor, Aiyana can decide how to allocate that inventory among the VA-MCs. This cross-dock doesn’t hold any inventory: it merely allows Aiyana to decide at a later point how to allocate inventory among her VA-MCs:
For general lead times and standard deviation as such:
The equation for the total safety stock (SS) in the system across all the VA-MCs is:
Notice that when LA is zero and LB is 10, Aiyana has the same situation she started with: there is essentially no cross-dock and no pooling effect.
- Each year, it costs The VA $500 to hold an extra unit of inventory. (So if Aiyana can get the total safety stock (SS) from 100 to 98, she saves $1000 per year). How much does Aiyana’s holding costs go down with the cross dock right in the middle?
- The current situation has: LA=0, LB = 10, Cost of cross dock = 0
- The situation Aiyana is pondering has: LA=5, LB = 5.
- Validation: If LA=3, LB=9, =4, N=10, z=1.96, then the safety stock is 239.1 and the yearly holding cost is $119,544.
- (Put back the original values for lead times, number of buildings, and standard deviation after you validate your model).
- Please fill in the following matrix:
|Current situation (no cross dock)||With Cross Dock|
|Safety Stock required in System|
|Total Holding Costs per year|
Part II – Vanguard Retail Operations (Put the answers to the questions in the Excel Spreadsheet Labeled VanguardRetail and explain each answer in a text box. For Example:
For the following questions, assume that a full time employee (FTE) works 4.69 productive hours per day, 5 days a week, 260 days per year.
We are also under the regime that we want to commit to handle every call type in every week, and that our workforce will be fixed after we make our decisions (that is, we can’t hire or fire people throughout the year to deal with seasonality. We need enough folks to be able to handle the yearly peaks, even if there are troughs the rest of the year. Thus, we will be utilizing the “Maximum” work hours row a good deal.)
- Let’s examine the pooling effect of generalization:
- Go to exhibit 12. How many crew members would Vanguard need in a generalist model to ensure that 100% of all work can be completed each week in 2016?
- Assume that workers divide themselves into “groups” (A-H) as designated in the case. How many crew members would Vanguard need in a specialist model to ensure that 100% of all work can be completed in each week in 2016? (Assume that a crew member works only in their group, and cannot handle calls from any other group.)
- Is the pooling effect smaller or bigger than what you’d expect? Use the data analysis toolpak to create a correlation matrix among the weekly demand in groups A-H. In general, what would you say about the correlation coefficients between pairs of group’s weekly data?
- Look at the transaction “Change of ownership – Divorce.” Look only at exhibit 13 (it’s a different sample of data, so don’t mix your numbers and results with exhibit 12).
For “All work total” and for “group H only” calculate the following:
- The total hours of work needed to be done for the year
- The total number of FTEs required to handle the work (4.69 hours per day, 260 days per year)
- The total “Change of ownership – Divorce” calls handled per year per crew member (under generalist model, and under specialist model for someone working in Group H)
- How does this difference in “calls per year” drive what we see in Exhibit 8 “RIG Ops Crew Productivity” (this is probably a quick answer)
- In an effort maximize crew member utilization, Benchener and Billet decided to staff the eight specialist categories as follows:
|Number of Crewmembers||95||16||59||28||64||53||38||29|
- Calculate per-category per-week, the amount of hours a flex team would need to handle in order to address all call volume. Then sum this up over the categories so that you end with a per-week number of work-hours required for the flex team to handle.
- How many FTE do you need on the flex team?
- Summarize in a quick memo (or brief email, or 5-minute elevator pitch) to management the situation, what’s driving the situation, the action taken, why that action was taken, and the impact of that action. You can take on the role of the protagonist and laud the decision, or (if you want) you can take on the role of antagonist or questioner, and be more skeptical of the decision.
Part III – Hewlett Packard (Put the answers to the questions in the Excel Spreadsheet Labeled HPCase and explain each in a text box)
[To accommodate a busy week next week, I have filled out more of the Excel spreadsheet than I would have otherwise. There are fewer equations to enter, and more hints as to how to enter them.]
“Mass Customization at Hewlett-Packard: The Power of Postponement.” This problem focuses on the “DeskJet” printer. It is manufactured in Vancouver, Washington, USA. It is shipped to North America, Europe, and Asia. Each country within each region requires its own power supply, and its own manual in the correct language. Because the warehouses (distribution centers [DCs]) are set up only to store inventory, this customization/assembly (power supply and user manual) needs to be done at the factory in Vancouver. If a retail store in Japan requests a specific printer, the workers in the DC in Asia pull the specific boxes that have already have Japanese power supplies and user manuals and ship them to the customer. You are considering an approach where you “postpone” the assembly (power supply and user manual) until the warehouses within each region. Thus, Vancouver, Washington ships a partially assembled printer to the Asia DC. Then, if a retail store in Japan requests this printer, the workers at the Asia DC add the correct power supply and user manual at the last minute. (This “postponed” approach is the one discussed in the article.)
You basically want to know the benefit in terms of inventory if you do final assembly at the main factory in Vancouver, Washington, or if you do final assembly in the regions, by continent.
Please do the following to perform this analysis.
Go to the Excel file “HPCase_Data_PartialEquations.xlsx.” This file gives data on different models A, AA, AB, AQ, etc.) and the monthly demand for each model. It also reports on where each model is sold. I have also filled in for you the lead time from the factory to the continents (1.28 months) as well as the review period (1 month) and the cycle service level (95%). Even though some models in different regions have the same name, treat them as separate (this makes the problem easier).
What we will do with this spreadsheet is to calculate inventory levels (SS, PS, CS) assuming a 95% service level, managing to CSL, assuming a normal distribution (this is the easiest way to calculate safety stock, i.e., the CSL-Normal combination).
We will calculate these quantities under 2 conditions: CALCULATE ON TWO DIFFERENT TABS PLEASE
- Assuming that the factory in Vancouver, Washington does all the customization/assembly, and thus each region has to hold enough safety stock for A, AA, AS, …..
- Europe has to hold enough safety stock for A, AA, AB, AQ, …, AY individually
- Asia Pacific has to hold enough safety stock for A, AB, AG, …, AU individually
- America has to hold enough safety stock for A, AB, …, AU individually
- Assuming that customization/assembly is done at each region, in the Asia, North America, and Europe DCs
- Europe has to hold enough safety stock for A…AY in total
- …(same for Asia Pacific)
- …(same for N.. America)
To carry out the analysis, please do the following:
- Fill in the proper equations for all of the dark grey cells.
- The “InventoryAtFactory” tab represents the situation where we hold all inventory at the factory, and assemble finished products there.
- Fill in the “monthly mean” and “monthly standard deviation” (we will assume that our forecast is equal to the mean demand, so that monthly standard deviation is equivalent to standard deviation of forecast errors [this makes things easier])
- Fill in Safety stock (SS), Cycle stock (CS), and Pipeline stock (PS) using the equations you’ve learned. Specifically, for SS, by assuming this is normally distributed with no lead time variability, we should have a simple equation for safety stock. Assume a periodic review basestock policy, where the review period and lead time are given (in months).
- The “InventoryAtFactory” tab represents the situation where we hold all inventory at the factory, and assemble finished products there.
- Calculate the totals in cells X26:X29 (this should be easy; the sum of the SS, CS, PS).
- Note the SS reduction is automatically filled in once you have completed both sheet tabs
- The “InventoryByContinent” tab represents the situation where we hold all inventory at the continents, and don’t customize/assemble until the very end after we ship partially assembled inventory to each continent.
- Fill in cells B3:M3, B6:M6, B10:M10. These are the SUMs of the demand from the previous tab (so that B3 = SUM(InventoryAtFactory!B3:B8)).
- Then do all the steps you did for the other tab, filling in the other dark grey cells.
Questions for you to answer: (Indicate on the table in excel)
- Highlight the summary tables (SS, CS, PS vs. Eur, Asia, N. Amer, Total) with the answers for inventory at factory and inventory at continent (you should have 2 tables):
- What is the change in total safety stock across the world if assembly is done in each region versus at the factory?
Change in SS = (SafetyStockAssemblyInEurope – SafetyStockAssemblyInFactory)/SafetyStockAssemblyInFactory
- Summarize in a brief memo/email/elevator pitch what’s driving this safety stock reduction.
- If you were to move final assembly to the regions, what obstacles might you encounter?
Extra credit (15 points) USE the Spreadsheet in HPCase labeled Extra Credit)
Fatimah is running an omnichannel network. She has 10 fulfillment centers (FCs). She knows that she has the flexibility to send any unit from any FC to any customer (let’s assume her firm doesn’t offer same day), and she usually uses USPS or UPS or FedEx to do individual parcels.
Here’s Fatimah’s process:
- Calculate systemwide basestock level based on systemwide lead times, review period, mu_P and sigma_P managing to CSL.
- Each FC places its own vendor order. Thus, each FC will follow a basestock policy whereby its own individual B will be B_System / N, where N is the number of FCs (we assume each FC is identical and independent, i.e., no correlation). So if the systemwide basestock level were 50, then each FC would actually follow a basestock level with their own B’s equal to 5.
- Everything follows a normal distribution and she’ll manage service levels to CSL (this makes everything easier, as opposed to fill rate).
Here are the facts:
- R = 1
- L = 2
- System CSL goal = 95%
- Number of FCs = 10 (but please try to keep this as a variable so we can see changes)
- Mu_P for the system is 1000
- Sigma_P for the system is 200
Here is Fatimah’s task:
- Calculate the system-wide basestock level.
- Divide this basestock level among the N FCs.
- For each FC, calculate mu_P and sigma_P
- This is not trivial. We know that mu_system = N*mu_FC. But what is the relationship between sigma_system and sigma_FC? It’s not sigma_system = N * sigma_FC. Remember, the FCs are independent. How does variance and standard deviation sum up over independent entities?
- For each FC, calculate the resulting z, and then the resulting CSL.
Please answer these questions:
- What is the resulting CSL at each FC when there are 10 FCs? What are the operational implications of this fact? (this second part is important)
- How many FCs does Fatimah need to achieve a CSL at each FC of 75%? (You may want to use GoalSeek here.)
- In an online retail/omnichannel environment, reducing safety stock by spreading the firm’s safety stock out (as we do here) seems like a good idea. But it has a hidden cost as Fatimah just found: what are the real-life implications of the per-FC resulting CSL being lower than the centralized total CSL? (that is, each customer still sees a 95% CSL, but each FC’s CSL is different from this. Even though customer service remains the same, what are the system implications of this?)