Microsoft Excel Analysis: Using the equations discussed in class answer to the below questions. Show your work by keeping your calculations in the workbook (i.e. do not copy/paste or paste/special...

Microsoft Excel Analysis: Using the equations discussed in class answer to the below questions. Show your work by keeping your calculations in the workbook (i.e. do not copy/paste or paste/special values). Format your worksheet so that the information is easy-to-read and conveys the appropriate information.

1. What is the current capacity utilization of the Toronto PDC?




2. What is the total number of new SKUs that will come in to the Toronto PDC over the next five years?




3. What is the number of additional bins needed over the next five years by type (small and large) and by marketing need (new and facelift)? Remember1 Bin = 1 SKU Type.




4. What is the total number of bins needed over the next five years? This will include legacy SKUs as well as new and facelift.
   
   




5. Calculate cumulative bins needed to store new and existing SKUs and determine if the existing Toronto PDC is able to meet this need.




6. If the Toronto PDC does not have adequate bin space, calculate the amount of additional cubic feet needed to store the additional bins. Add in a 20% factor for aisles and racking.




7. If the Toronto PDC is not able to meet the new capacity, provide a recommendation of how much additional space is required including a 7% honeycombing allowance.




8. If the Toronto PDC is not able to meet the new capacity requirements, identify the % of expansion required. (I.e. You want to be able to say that the warehouse will need to expand by XYZ%.)

SCM 125 - Part 3 Student SCM 125: Semester Project - Analysis Microsoft Excel Analysis: Using the equations discussed in class answer the below questions. Show your work by keeping your calculations in the workbook (i.e. do not copy/paste or paste/special values). Format your worksheet so that the information is easy-to-read and conveys the appropriate information. A Microsoft Excel template is available on Blackboard. Helpful Tips: 1) Areas where you must enter information that is found in the HBR case study OR where you must enter in the equation are marked in pale yellow. Example --> These cells have conditional formatting that will go away as you enter your values. You will know when you have complete the worksheet when all the questions are answered and the yellow has "gone away." 2) Some questions require answers from previous questions. To help reduce confusion, the answers are "linked" automatically for you via Microsoft Excel equations. These linkages are noted in green dark green font. Example --> Example Link Font 3) As agreed upon in class, remember that we are using the small bin to large bin ratio as laid out in Exhibit 5. a)      What is the current capacity utilization of the Toronto PDC? Total # of Small Bins in WarehouseWhere to find it hint: Total # of Large Bins in WarehouseCase Study, Page 3 Total Number of Bins in Warehouse # of Small Bins in UseWhere to find it hint: # of Large Bins in UseCase Study, Exhibit 5 Total Number of Bins in Use Current Capacity Utilization (%) Remember: Utilization is a numerical number detailing how much of your current space is in use (utilized) and how much is free and yet to be utilized b)      What is the total number of new SKUs that will come in to the Toronto PDC over the next five years? # of new SKUS for Each New Model Added # of New SKUS for Each Facelift AddedWhere to find it hint: Case Study, Page 3 Year 1Year 2Year 3Year 4Year 5Total New Model Launches0 New Facelifts0 # of new SKUs for Each New Model Added0 # of new SKUs for Each Facelift Added0 c)       What is the number of additional bins needed over the next five years by type (small and large) and by marketing need (new and facelift)? Remember 1 Bin = 1 SKU Type. Where to find it hint: Year 1Year 2Year 3Year 4Year 5TotalCase Study, Page 4 New Model Launches4440012 New Facelifts4440012 # of New Model Bins Required per Year000000 # of Facelift Bins Required per Year000000 Total # of new Bins Required for New SKUs000000 Where to find it hint: % Small SKUs Case Study, Exhibit 5 % Large SKUs Year 1Year 2Year 3Year 4Year 5Total # of Small Bins Required per Year000000 # of Large Bins Required per Year000000 Total # of New Bins Required per Year000000 d)      What is the total number of bins needed over the next five years? This will include legacy SKUs as well as new and facelift. Where to find it hint: Case Study, Page 4 Net New Bins Required for SKUs Added Each Year (New & Facelift) CurrentYear 1Year 2Year 3Year 4Year 5Total # of Small Bins Required Per Year0000000 # of Large Bins Required Per Year0000000 Total Number of Additional Bins Needed0000000 Cumulative Bins Needed to Store New & Existing SKUs Each Year CurrentYear 1Year 2Year 3Year 4Year 5 Small Bins Large Bins Total Cumulative Bins Required000000 e)      Calculate cumulative bins needed to store new and existing SKUs and determine if the existing Toronto PDC is able to meet this need. Where to find it hint: Case Study, Page 3 Cumulative Bins Needed to Store New & Existing SKUs Each Year CurrentYear 1Year 2Year 3Year 4Year 5 Small Bins000000 Large Bins000000 Total Cumulative Bins Required000000 Total Fixed Number of Bins in Warehouse at 100% CurrentYear 1Year 2Year 3Year 4Year 5 Small Bins Large Bins Total Number of Bins in Warehouse000000 Cumulative Net New Bins RequiredCurrentYear 1Year 2Year 3Year 4Year 5 Small Bins Large Bins Cumulative Net New Bins Required000000 f)       If the Toronto PDC does not have adequate bin space, calculate the amount of additional cubic feet needed to store the additional bins. Add in a 20% factor for aisles and racking (10% for aisles, 10% for racking). Standard Bin Sizes in Cubic FeetCurrentYear 1Year 2Year 3Year 4Year 5 Small Bin10----- Large Bin100----- Additional Space Needed Before Racking & Aisles CurrentYear 1Year 2Year 3Year 4Year 5 Cubic Feet of Small Bins000000 Cubic Feet of Large Bins000000 Additional Cubic Feet Required000000 Add Racking and AislesCurrentYear 1Year 2Year 3Year 4Year 5 10% Add'l Cubic Feet Required for Aisles 10% Add'l Cubic Feet Required for Racking Total Additional Cubic Feet Required000000 Where to find it hint: Empty Cubic Feet Still AvailableCase Study, Page 3 Additional Cubic Feet of Warehouse Required with Expansion 000000 g)      If the Toronto PDC is not able to meet the new capacity, provide a recommendation of how much additional space is required including a 7% honeycombing allowance. Standard Bin Sizes in Cubic FeetCurrentYear 1Year 2Year 3Year 4Year 5Where to find it hint: Small Bin10-----Textbook, Lecture Large Bin100----- Note: (Space Needs Per Bay) / [(1-Honeycombing Ratio%) (Cases per Bay)] Note: Cases per Bay = 1 Honeycombing RatioCurrentYear 1Year 2Year 3Year 4Year 5 Small Bin----- Large Bin----- Additional Space Needed Before Racking & Aisles CurrentYear 1Year 2Year 3Year 4Year 5 Cubic Feet of Small Bins00000 Cubic Feet of Large Bins00000 Additional Cubic Feet Required0000 Add Racking and AislesCurrentYear 1Year 2Year 3Year 4Year 5 10% Add'l Cubic Feet Required for Aisles0000 10% Add'l Cubic Feet Required for Racking0000 Total Additional Cubic Feet Required0000 Empty Cubic Feet Still AvailableWhere to find it hint: Case Study, Page 3 Additional Cubic Feet of Warehouse Required with Expansion w/ Honeycombing 000000 h)      If the Toronto PDC is not able to meet the new capacity requirements, identify the % of expansion required. (I.e. You want to be able to say that the warehouse will need to expand by XYZ%.) Current WarehouseLengthWidthHeightCubic FeetWhere to find it hint: Case Study, Page 3 CurrentYear 1Year 2Year 3Year 4Year 5 Current Cubic Feet of Warehouse000000 Additional Cubic Feet of WH Required with Expansion w/ Honeycombing000000 Total Cubic Feet Required000000 Total Square Cubic Feet of Warehouse Required Current Square Feet of Warehouse Net Square Feet of Expansion Required Additional % of Expansion Required &F&A&D