3. Assuming a service level of 99% for all parts, what will be the impact on the total inventory investment if the number of warehouses is reduced from 8 warehouses to 2? Reduced to 1? Assume that each of the 8 warehouse has roughly equal demand and, when consolidating the warehouses, the total demand across all 8 warehouses is equally divided between the remaining warehouses.
a. Using data from the case and Exhibit 3 (BELOW), complete the table below. Note that Exhibit 3 gives the standard deviation of the bi-weekly demand for 8, 2, and 1 warehouses respectively. Use these values to calculate the Stdev of weekly demand. (Hint: refer to the solved inventory problems document for additional examples) (2 points)
| Product | | Griffin Beaker | Erlenmeyer Flask |
| | # of Warehouses | 8 | 2 | 1 | 8 | 2 | 1 |
| | Z-Value | 2.33 | 2.33 | 2.33 | 2.33 | 2.33 | 2.33 |
| Calculations for 1 warehouse | Average Weekly Demand | | | | | | |
| Stdev of Weekly Demand | | | | | | |
| POU (weeks) | | | | | | |
| Q | | | | | | |
| Average Pipeline Inventory | | | | | | |
| Begin on hand | | | | | | |
| SS | | | | | | |
| Average On Hand | | | | | | |
| Average Inventory | | | | | | |
| | Total Average Inventory (all warehouses) | | | | | | |
- Calculate the percentage savings in total average Inventory investment from warehouse consolidation for each product. Assume lead time stays constant at 1 week:
- from 8 warehouses to 2 warehouses. (0.5 points)
- from 8 warehouses to 1 warehouse. (0.5 points)
- Take a simple average of the percent savings for each part and apply the resulting average percent savings to the entire North American finished goods (NA FG) inventory. Assume 2010 NA FG is $5 million. Calculate the total savings for the NA FG if:
- 8 warehouses are consolidated to 2 warehouses. (0.5 points0
- 8 warehouses are consolidated to 1 warehouse. (0.5 points)
- Would savings from consolidation compensate for the expected increase in inventory of $1.5 million if:
- 8 warehouses are consolidated to 2 warehouses? (0.5 points)
- 8 warehouses are consolidated to 1 warehouse? (0.5 points)
Exhibit 3
| Exhibit 3: Information on Representative Products | | |
| | | |
| | Griffin 500ml Beaker | Erlenmeyer 500ml Flask |
| 2009 units sold | 11,268 | 3,389 |
| Percent of all units sold | 0.1% | 0.03% |
| Annual carrying cost (%) | 14% | 14% |
| Unit price | $ 8.80 | $ 9.50 |
| Unit cost | $ 3.96 | $ 4.56 |
| Cost of underage | $ 0.48 | $ 0.49 |
| Cost of overage | $ 0.021 | $ 0.025 |
| Optimal service level | 95.8% | 95.3% |
| Average bi-weekly demand (8 warehouses) | 54.2 | 16.3 |
| Standard deviation of bi-weekly demand (8 warehouses) | 21.4 | 10.9 |
| Average bi-weekly demand (2 warehouses) | 216.7 | 65.2 |
| Standard deviation of bi-weekly demand (2 warehouses) | 38.3 | 19.5 |
| Average bi-weekly demand (1 warehouse) | 433.4 | 130.3 |
| Standard deviation of bi-weekly demand (1 warehouse) | 51.0 | 26.0 |
3. Assuming a service level of 99% for all parts, what will be the impact on the total inventory investment if the number of warehouses is reduced from 8 warehouses to 2? Reduced to 1? Assume that each of the 8 warehouse has roughly equal demand and, when consolidating the warehouses, the total demand across all 8 warehouses is equally divided between the remaining warehouses. TN-Exhibit 3 Forecast Income Statement and Balance Sheet Values ($ millions) a. Using data from the case and Exhibit 3, complete the table below. Note that Exhibit 3 gives the standard deviation of the bi-weekly demand for 8, 2, and 1 warehouses respectively. Use these values to calculate the Stdev of weekly demand. (Hint: refer to the solved inventory problems document for additional examples) (2 points) Income Statement Accounts Forecast Assuming No Improvement to Inventory Forrest 2010 Assumptions 103.6 20% growth Forecast Assuming Improvement to Inventory (Implement Options 1 and 5) Forecast 2010 Assumptions 103.6 2 growth Product Griffin Beaker Erlenmeyer Flask 46.7 16.8 204 3.1 # of Warehouses 46.6 Jos 29.4 31 8 2 1 8 Net sales Expenses Cost of goods sold Sales, general & administrative Research and development Depreciation Other expenses Operating expenses Interest expense Taxes Neteaming 2 1 same COGS /sales ratio as 2009 same SGA/ sales ratio rate as 2009 same R&D / sales ratio as 2009 flat depreciation same other expense/ sales ratio as 2009 savings from Options 1 & 5 TN Exhibit-5 same SGA / sales ratio rate as 2009 same R&D / sales ratio as 2009 flat depreciation same other expense / sales ratio as 2009 12 Z-Value 2.33 2.33 2.33 2.33 2.33 2.33 3 882 3.9 3.5 8.0 12% interest on 2010 end of year debe same tax / sales ratio as 2009 12% interest on 2010 end of year debt same tax / sales ratio as 2009 Average Weekly Demand 54 Balance Sheet Accounts 2010 2010 Stdev of Weekly Demand Assets Cash Receivables 3,6 5.3 36 53 w growth as 2005 to 2009 same growth as 2003 to 2009 additional inventory from IN Exhibit 2. Suate 18 same growth as 228 to 2009 POU (weeks) same growth as 2008 to 2009 same growth as 2008 to 2009 net inventory from sales growth, international expansion, and Options 1 & 5, TN Exhibit-5 same growth as 2008 to 2009 Q Inventory Prepaid expenses Current assets Plant, property, & equipment Other long-term assets Total long-term assets 12.7 78 294 39.8 68 46.6 SIOM Investment in plast me growth 2008 to 2009 10.3 78 27.0 39.8 68 46.6 510M investment in plant same growth as 2008 to 2009 Calculations for 1 warehouse Average Pipeline Inventory Total assets 261 72 Liabilities & Equity Short-term debt Accounts payable Accrued liabilities Current liabilities Long-term debt Owners equity Total capitalization same growth as 2008 to 2009 same growth as 2008 to 2009 same growth as 2008 to 2009 same growth as 2008 to 2009 same growth as 2008 to 2009 same growth as 2008 to 2009 Begin on hand 3.8 3.9 06 8.3 26.0 39.4 65.4 228 39 balancing account incremented by net earnings SS balancing account incremented by net earnings Total liabilities & equity 761 737 Average On Hand Months of inventory Cost of goods sold / sales Long-term debt / total capital 3.268 0.451 0425 2.656 0.450 0.398 Average Inventory Total Average Inventory (all warehouses) b. Calculate the percentage savings in total average Inventory investment from warehouse consolidation for each product. Assume lead time stays constant at 1 week: i. from 8 warehouses to 2 warehouses. (0.5 points) ii. from 8 warehouses to 1 warehouse. (0.5 points) 3. Assuming a service level of 99% for all parts, what will be the impact on the total inventory investment if the number of warehouses is reduced from 8 warehouses to 2? Reduced to 1? Assume that each of the 8 warehouse has roughly equal demand and, when consolidating the warehouses, the total demand across all 8 warehouses is equally divided between the remaining warehouses. TN-Exhibit 3 Forecast Income Statement and Balance Sheet Values ($ millions) a. Using data from the case and Exhibit 3, complete the table below. Note that Exhibit 3 gives the standard deviation of the bi-weekly demand for 8, 2, and 1 warehouses respectively. Use these values to calculate the Stdev of weekly demand. (Hint: refer to the solved inventory problems document for additional examples) (2 points) Income Statement Accounts Forecast Assuming No Improvement to Inventory Forrest 2010 Assumptions 103.6 20% growth Forecast Assuming Improvement to Inventory (Implement Options 1 and 5) Forecast 2010 Assumptions 103.6 2 growth Product Griffin Beaker Erlenmeyer Flask 46.7 16.8 204 3.1 # of Warehouses 46.6 Jos 29.4 31 8 2 1 8 Net sales Expenses Cost of goods sold Sales, general & administrative Research and development Depreciation Other expenses Operating expenses Interest expense Taxes Neteaming 2 1 same COGS /sales ratio as 2009 same SGA/ sales ratio rate as 2009 same R&D / sales ratio as 2009 flat depreciation same other expense/ sales ratio as 2009 savings from Options 1 & 5 TN Exhibit-5 same SGA / sales ratio rate as 2009 same R&D / sales ratio as 2009 flat depreciation same other expense / sales ratio as 2009 12 Z-Value 2.33 2.33 2.33 2.33 2.33 2.33 3 882 3.9 3.5 8.0 12% interest on 2010 end of year debe same tax / sales ratio as 2009 12% interest on 2010 end of year debt same tax / sales ratio as 2009 Average Weekly Demand 54 Balance Sheet Accounts 2010 2010 Stdev of Weekly Demand Assets Cash Receivables 3,6 5.3 36 53 w growth as 2005 to 2009 same growth as 2003 to 2009 additional inventory from IN Exhibit 2. Suate 18 same growth as 228 to 2009 POU (weeks) same growth as 2008 to 2009 same growth as 2008 to 2009 net inventory from sales growth, international expansion, and Options 1 & 5, TN Exhibit-5 same growth as 2008 to 2009 Q Inventory Prepaid expenses Current assets Plant, property, & equipment Other long-term assets Total long-term assets 12.7 78 294 39.8 68 46.6 SIOM Investment in plast me growth 2008 to 2009 10.3 78 27.0 39.8 68 46.6 510M investment in plant same growth as 2008 to 2009 Calculations for 1 warehouse Average Pipeline Inventory Total assets 261 72 Liabilities & Equity Short-term debt Accounts payable Accrued liabilities Current liabilities Long-term debt Owners equity Total capitalization same growth as 2008 to 2009 same growth as 2008 to 2009 same growth as 2008 to 2009 same growth as 2008 to 2009 same growth as 2008 to 2009 same growth as 2008 to 2009 Begin on hand 3.8 3.9 06 8.3 26.0 39.4 65.4 228 39 balancing account incremented by net earnings SS balancing account incremented by net earnings Total liabilities & equity 761 737 Average On Hand Months of inventory Cost of goods sold / sales Long-term debt / total capital 3.268 0.451 0425 2.656 0.450 0.398 Average Inventory Total Average Inventory (all warehouses) b. Calculate the percentage savings in total average Inventory investment from warehouse consolidation for each product. Assume lead time stays constant at 1 week: i. from 8 warehouses to 2 warehouses. (0.5 points) ii. from 8 warehouses to 1 warehouse. (0.5 points)
